MySQL(3)---MySQL优化
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,'1','1'); insert into article values(null,2,2,2,2,'2','2'); insert into article values(null,1,1,3,3,'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】
