MySQL优化
一、单表、双表、三表优化
1、单表
首先结论就是，range类型查询字段后面的索引全都无效
（1）建表
create table if not exists article(
id int primary key auto_increment,
author_id int not null,
category_id int not null,
views int not null,
comments int not null,
title varchar(255) not null,
content text not null
);
insert into article values(null,1,1,1,1,'','');
insert into article values(null,2,2,2,2,'','');
insert into article values(null,1,1,3,3,'','');
（2）未创建索引查询
explain select id,author_id,views from article
where category_id = 1
and comments > 1
order by views desc limit 1;
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| 1 | SIMPLE | article | ALL | NULL | NULL | NULL | NULL | 3 | Using where; Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
-- 总结上面出现的情况:type=all,产生了全表扫描, 并且出现了Using filesort,使用了外部的索引排序,所以优化是必须的
（3）创建category_id,comments,views复合索引
create index ind_article_ccv on article(category_id,comments,views); --再次执行如下指令:
explain select id,author_id,views from article
where category_id = 1
and comments > 1
order by views desc limit 1;
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+
| 1 | SIMPLE | article | range | ind_article_ccv | ind_article_ccv | 8 | NULL | 1 | Using index condition; Using filesort |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+
--创建索引之后type=range, 但是Using filesort 依然存在.
索引创建了,为什么在排序的时候没有生效?
这是因为先排序category_id, 如果遇到相同的category_id,则再排序comments, 如果遇到相同的comments则再排序views,
当comments字段在联合索引处于中间位置时,因为comments>1条件是一个范围值,所以type=range,mysql无法再利用索引对后面的views部分进行检索.
即range类型查询字段后面的索引全都无效
(4)、创建category_id,views复合索引
-- 删除上面创建的索引:
drop index ind_article_ccv on article;
-- 重新创建索引:
create index ind_art_cb on article(category_id,views);
-- 重新执行如下代码
explain select id,author_id,views from article
where category_id = 1
and comments > 1
order by views desc limit 1;
+----+-------------+---------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------------+---------+-------+------+-------------+
| 1 | SIMPLE | article | ref | ind_art_cb | ind_art_cb | 4 | const | 2 | Using where |
+----+-------------+---------+------+---------------+------------+---------+-------+------+-------------+
--完美！
2、两张表
记住一句话：左连接索引建在右表，右连接索引做在左表
select * from class left join book on class.card = book.card; --这是左连接，那么索引应该建在book表中的card（create index X on book(card);）
3、三张表
select * from class left join book on class.card = book.card left join phone on book.card = phone.card;
--这里就可以见两个索引，一个在book表，一个在phone表中
-- 创建索引:
create index Y on book(card);
create index Z on phone(card);
二、经典案例分析
1、建表，插数据，建索引
--建表
create table test3(
id int primary key auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10)
);
--插入数据
insert into test3 values(null,'a1','a2','a3','a4','a5');
insert into test3 values(null,'b1','b2','b3','b4','b5');
insert into test3 values(null,'c1','c2','c3','c4','c5');
insert into test3 values(null,'d1','d2','d3','d4','d5');
insert into test3 values(null,'e1','e2','e3','e4','e5');
--查看数据
select * from test3;
-- 创建索引
create index ind_test3_c1234 on test3(c1,c2,c3,c4);
show index from test3;
2、分析下面索引情况
explain select * from test3 where c1='a1';
explain select * from test3 where c1='a1' and c2='a2';
explain select * from test3 where c1='a1' and c2='a2' and c3='a3';
explain select * from test3 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
explain select * from test3 where c1='a1' and c2='a2' and c3='a3' and c4='a4' and c5='a5';
--上面五条所以肯定都有用，而且越到下面越精准 -- 请执行如下问题SQL,分析会出现的问题
--(1)
explain select * from test3 where c1='a1' and c2='a2' and c4='a4' and c3='a3' ;
--四个字段索引都有效 -- (2)
explain select * from test3 where c4='a1' and c3='a2' and c2='a4' and c1='a3' ;
--四个字段索引都有效 -- (3)
explain select * from test3 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
--c1、c2、c3索引有效， -- (4)
explain select * from test3 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
--四个字段索引都有效 -- (5)
explain select * from test3 where c1='a1' and c2='a2' and c4='a4' order by c3;
--c1、c2有效，c3仅对排序有效 -- (6)
explain select * from test3 where c1='a1' and c2='a2' order by c3;
--c1、c2有效，c3仅对排序有效 -- (7)
explain select * from test3 where c1='a1' and c2='a2' order by c4;
--c1、c2有效，因为中间缺少c3,所以索引排序无效。会报：Using filesort -- (8)
explain select * from test3 where c1='a1' and c5='a5' order by c2,c3;
--c1有效，但是c2,c3用于排序,无filesort --(9)
explain select * from test3 where c1='a1' and c5='a5' order by c3,c2;
-- 只用了c1这个字段索引,但是由于c3,c2顺序颠倒了,所以无法使用索引排序,出现filesort --(10)
explain select * from test3 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;
--因为排序字段c2已经是一个常量了，所以对于后面c2排序其实没有啥意义，所以不会出现filesort --(11)
explain select * from test3 where c1='a1' and c4='c4' group by c2,c3;
--用到了c1字段索引,但用到c2、c3排序 --(12)
explain select * from test3 where c1='a1' and c4='c4' group by c3,c2;
-- 索引字段顺序不正确，出现了Using temporary; Using filesort
三、in和exists区别
1、 优化规则: 小表驱动大表,即小的数据集驱动大的数据集
如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。 所以如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in。 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。
另外IN时不对NULL进行处理。
-- 当A的数据集大于B的数据集时, 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; -- 当A的数据集小于B的数据集时, exists优于in
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
2、not in 和not exists
如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。
四、Order by 排序优化
1、单路排序、排序
order by 子句,尽量使用index方式排序,避免使用Filesort方式排序，尽可能再索引列上完成排序操作,遵照索引建的最佳左前缀， 如果不在索引列上。filesort算法:双路排序,单路排序。
(1)双路排序: mysql4.1之前使用的双路排序,字面意思就是扫描两次磁盘,从而得到最终的数据,读取行指针和order by 列,对它们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出. 从磁盘取排序字段,在buffer进行排序,再从磁盘取其它字段
(2)单路排序:从磁盘读取查询需要的所有列,按照order by列在buffer对它们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据.并且把随机IO变成了顺序IO,但是它会使用更多的空间,因为它把每一行都保存到内存中
2、结论:
(1)单路排序,优于双路排序
(2)在sort_buffer中,单路要比双路占用更多的空间,因为单路是把所有的字段都取出来,有可能导致取出的数据总大小超出了sort_buffer的容量,导致每次只能取sort_buffer容量大小的数据,进行排序(创建tmp,多路合并),排完再取sort_buffer容量大小,再排…从而导致了多次IO. (本来想节省一次IO操作,反而导致了大量的IO操作,得不偿失)
3、优化策略
(1)增大sort_buffer_size参数设置
(2)增大max_length_for_sort_data参数设置
4、why
(1)order by时,select * 时一个大忌,只查询需要的字段,这点非常重要。当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时,会用单路排序,否则会用多路排序
两种算法的数据都有可能超过sort_buffer的容量,超出之后,会创建tmp文件进行合并排序,导致多次IO,但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size
(2)尝试调高sort_buffer_size: 不管使用哪种算法,提高这个参数都会提高效率,当然,要根据系统的能力去调高,因为这个参数是针对每个进程的
(3)尝试调高max_length_for_sort_data: 调高这个参数,会增加使用单路算法的概率,但是如果设置太高,数据总容量超出sort_buffer_size的概率就增大,明显症状是高的磁盘IO活动和低的处理器使用率
5、总结
--mysql有两种排序方式: 文件排序和扫描有序索引排序
-- mysql能为排序和查询使用相同的索引 index abc(a,b,c)
order by --能使用最左前缀
order by a
order by 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 c
where a=const order by b,c
where a=const and b > const order by 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 /*对于排序来说,in 相当于是范围查询*/
```
参考
1.索引优化
想太多，做太少，中间的落差就是烦恼。想没有烦恼，要么别想，要么多做。上尉【16】