# 单表、两表、三表的优化案例
如何建立索引并优化 - 在explain的基础上进行优化：
## 建表
create table article (
id int(10) unsigned not null primary key auto_increment,
author_id int(10) unsigned not null,
category_id int(10) unsigned not null,
views int(10) unsigned not null,
comments int(10) unsigned not null,
title varbinary(255) not null,
content text not null
) engine=innodb charset utf8;
insert into article (author_id,category_id,views,comments,title,content) values (1,1,1,1,'1','1'),(2,2,2,2,'2','4'),(1,1,3,3,'3','3');
select * from article;
## 单表查询的情况
1、查询category_id为1且comments大于1的情况，views最多的article_id;
未建索引的情况：全表扫描并用到了文件排序（type = all，extra存在using filesort）
2、如何建索引：
方法1：alter table article add index article_ccv(category_id,comments,views) 或者 create index article_ccv on article(category_id,comments,views)
加索引的效果：使用到了索引，解决了全表扫描的问题
索引失效：说明范围查找后面导致了索引失效了
说明当前建的索引不适合我们的需求，所以只能删除当前建的索引了；
drop index article_ccv on article;
方法2：alter table article add index article_ccv(category_id,views) 或者 create index article_ccv on article(category_id,views)
去除了using filesort
综上得范围查询会让其后面的索引失效
## 两表的情况
1、建表
create table book (
bid int(10) unsigned not null primary key auto_increment,
card int(10) unsigned not null,
bname varchar(50) not null
) engine=innodb charset utf8;
create table class (
id int(10) unsigned not null primary key auto_increment,
card int(10) unsigned not null,
name varchar(50) not null
)engine=innodb charset utf8;
insert into book(card,bname) values ('1', '小说1');
insert into book(card,bname) values ('1', '小说2');
insert into book(card,bname) values ('1', '小说3');
insert into book(card,bname) values ('2', '军事1');
insert into book(card,bname) values ('2', '军事2');
insert into book(card,bname) values ('3', '搞笑1');
insert into book(card,bname) values ('4', 'php');
insert into book(card,bname) values ('4', 'go');
insert into book(card,bname) values ('4', 'java');
insert into class(card,name) values (1,'小说');
insert into class(card,name) values (2,'军事');
insert into class(card,name) values (3,'搞笑');
insert into class(card,name) values (4,'技术');
insert into class(card,name) values (5,'视频');
select * from class, book;
情况1：explain select * from class left join book on class.card = book.card;
查询type：ALL
优化1：book中加索引 - alter table book add index card(card);
由上面可看出，book表在加card为索引时，左右连接使用索引的情况都是不一样的
drop index card on book;
优化2：class 表中加索引 - alter table class add index card(card);
从type可看出，左链接时：class表中的card加索引，和未加索引没什么区别！右连接时：class表中的card加索引就起到作用了
优化3：两个表同时加上索引
综上得：左连接加右表的索引、右连接加左表的索引
总结：
尽可能减少join语句中的nesteloop的循环总次数：“永远用小结果集驱动大的结果集”
有限优化nestedloop的内存循环，保证join语句中被驱动表上join条件字段已经被索引
当无法保证join语句中被驱动表上join条件字段被索引且内存资源充足的前提下，不要太吝惜joinbuffer的设置
# 索引优化
## 建表
create table staffs(
id int primary key auto_increment,
name varchar(24) not null default '' comment '姓名',
age int not null default 0 comment '年龄',
pos varchar(20) not null default '' comment '职位',
created_at timestamp not null default CURRENT_TIMESTAMP comment '入职时间'
)engine=innodb charset utf8 comment '员工记录表';
insert into staffs (name, age,pos,created_at) values ('z3', 22,'manager', now());
insert into staffs (name, age,pos,created_at) values ('july', 23,'dev', now());
insert into staffs (name, age,pos,created_at) values ('2000', 23,'manager', now());
select * from staffs;
alter table staffs add index staffs_nameAgePos(name, age, pos);
show index from staffs;
情况1：使用name、name和age、name和age和pos都可以看出都用到了索引，只要不丢失name就可以
情况2：只用name和pos - 中间的条件不能断。中间索引丢失，只有开头索引和结尾索引，只用到了name索引
情况3：索引列上做计算了
情况4：age是范围查询。可得出范围之后，索引全失效
情况5：用到了using index，是查询列能被索引列覆盖
age做范围查询
情况6：!= 或 <>也会导致索引失效
情况7：is null 和is not null 也会到是索引失效
情况8：like后面以%开头的条件会导致索引失效，右边%号不会导致索引失效
如果非要用到%在左的查询，如何优化尼？可以通过建立覆盖索引达到使用索引的情况
情况9：name是字符串，但是不加索引会导致索引失效。（涉及到类型转换）。mysql自动将2000做类型转换了
情况10：or会导致索引失效
其他测试：
发现like查询时，%在左边的情况需要注意字段的类型，是字符串类型就会有效，整型就会失效 - 说明进行了类型转换
总结：
1、全值匹配我最爱
2、最左前缀原则
3、不在索引列上做任何操作（计算、函数、类型转换），会导致索引失效而转向全表扫描
4、存储引擎不能使用索引中范围条件右边的列
5、尽量使用覆盖索引（只访问索引的查询 - 索引列和查询列一致），减少select *
6、mysql在使用不等于（!= 或者<>）的时候无法使用索引会导致全表扫描
7、is null、is not nulll 也无法使用索引
8、like以通配符%开头时，mysql索引失效会变成全表扫描
9、字符串不加单引号索引失效
10、少用or，用它来连接时会索引失效
# 查询优化
## in和exists
小表驱动大表，即小的数据集驱动大的数据集
案例：
select * from A where id in (select id from B)
等价于：
for select id from B
for select * from A where A.id = B.id
当B的数据集必须小于A的数据集时，in优于exists
select * from A where exists (select 1 from B where B.id=A.id)
等价于
for select * from A
for select * from B where B.id = A.id
当A表的数据集小于B表的数据集时，exists优于in
exists
语法：select * from table where exists (subquery)
可理解为：将主查询的语句，放到子查询语句中做条件验证，根据验证结果（True或False）来决定主查询的数据结果是否得以保留
提示：
exists(subquery)只返回true或false，因此子查询中的select * 也可以是select 1/X
## order by的排序优化
order by子句尽量使用index方式排序，避免使用filesort方式排序
MySQL支持两种方式的排序，filesort和index， index效率高
尽可能在索引列上完成排序方式，最早索引建立的最左前缀
order by满足两情况，会使用index方式排序：orderby语句使用索引最左前列；使用where子句与order by子句条件列组合满足索引最左前列
如果不在索引列上，filesort有两种算法
双路排序：读取行指针及order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取数据
从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段
单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对他们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它需要把每一行都保存在内存中
单路存在的问题：在sort_buffer中，单路比双路占用的空间要多，因为单路把所有字段都取出来，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序，排完序再取sort_buffer容量大小，再排，从而多次I/O
优化策略：增加sort_buffer_size参数的设置，增大max_length_for_sort_data参数的设置
order by使用和不使用索引的情况：
使用索引的情况：
key a_b_c(a,b,c);
order by 使用索引最左前缀
order by a
order by a,b
order by a,b,c
order by a desc,b desc,c desc
如果where使用最左前缀定义为常量，则order by使用索引
where a=const order by b,c
where a=const and b=const order by b
where a=const and b>const order b,c
不能使用索引进行排序的情况：
order by a asc,b desc,c desc # 排序不一致
where g=const order by b,c # 丢失a索引
where a=const order by c # 丢失b索引
where a=const order by a,d # d不是索引的一部分
where a in (......) order by b,c # 对于排序来说，多个相等条件也是范围查询
## group by优化
和order by类似，都是基于索引排好序进行优化
group by是指是先排序后进行优化，遵照索引键的最左前缀
where高于having，能写在where限定的条件就不要去having限定了
网友评论