MySQL查询优化可以从3方面入手
1.用小表驱动大表
2.order by关键字优化
3.group by关键字优化
一,小表驱动大表
用数据量小的表驱动数据量大的表。为什么要用小表驱动大表?假设A表数据是1000条,B表20条,如果用A表驱动要循环1000次(请求和断开)才能查询出来,用B表则只需要20次
用for循环和SQL语句说下怎么用小表驱动大表:
1.外层循环有5条数据,相当于数据量比较少的驱动表,内层有1000条数据,相当于大表
2.使用子查询小表驱动大表说明
select * from A where id in (select id from B) //B表数据少于A表时,用in优于exists,这里B表应为驱动表
select * from A where exists(select 1 from B where A.id=B.id) //当A表数据少于B表时,用exists优于in,这里A表应为驱动表
注:AB表关联的ID字段应建立索引
3.使用join关键字的小表驱动大表
select * from A a left join B b a.id=b.id //此时A应为驱动表
exists可能都用的比较少,这里解析下:将主查询的数据,放到子查询中做条件验证,根据验证结果(true或false)来决定主查询的结果是否得以保留。
因为exists返回true或false,官方说法是实际执行时会忽略select清单,所以上面子查询中我使用了 selece 1,如果你喜欢用select *,select 'X'等等也是可以的。exists主要看的是where后面的条件是否成立
二、order by关键字优化
1.order by子句尽量使用index方式排序,避免使用FileSort方式排序。MySQL支持两种方式排序,FileSort和Index,Index效率高,它指MySQL扫描索引本身完成排序。FileSort效率较低,它是指MySQL把数据存到内存中,然后通过相应的排序算法,将取得的数据在内存中进行排序
2.order by满足两种情况会使用index方式排序:1.order by语句使用索引最左前缀法则。2.使用where子句与order by子句条件列组合满足索引最左前缀法则
3.尽可能在索引列上完成排序操作,遵照索引建的最左前缀法则。如果不在索引列上,filesort有两种算法:双路排序和单路排序
双路排序:MySQL4.1之前是使用双路排序,字面意思就是两次扫描磁盘,最终得到数据,第一次io读取行指针和order by列,对他们进行排序,然后第二次io扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出。
单路排序:从磁盘读取查询需要的所有列,按照order by列在buffer(缓冲区)对它们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了二次io。并且把随机IO变成了顺序IO,因为把每一行都保存在内存中,所以会使用更多的空间。 这样就会出现一个sort_buffer容量不足的问题。
因为在sort_buffer中,单路排序比双路排序要多占用很多空间,因为单路排序是一次性把所有字段取出放到buffer中,所以有可能去除的数据总大小超出了sort_buffer的容量,导致每次只能取soer_buffer容量大小的数据进行排序(创建tmp文件,多路合并),排序完再取sort_buffer容量大小的数据,再排序,这样重复多次,从而会有多次I\O
如果无法避免出现filesort那么我们就要考虑下优化策略来提高order by的速度
1.Order by时select * 是一个大忌,只查询需要的字段就够了,这点很重要。在这里的影响是:
1.1当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时,会用单路排序,否则用双路
1.2两种算法的数据都有可能超出sort_buffer的容量,超出之后,会创建tmp文件进行黑帮排序,导致多次I\O,但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size
2.尝试提高sort_buffer_size,不管用哪种算法,提高这个参数都会提高效率,当然要根据系统的能力取提高,因为这个参数是针对每个进程的
3.尝试提高max_length_for_sort_data,提高这个参数,会增加用单路排序的概率。但是如果设的太高,数据总容量超出sort_buffer_size的概率就增大,明显症状是高的磁盘I\O活动和低的处理器使用率
下面演示下避免出现FileSort
为了更好演示实例,先创建好索引
ALTER TABLE users ADD INDEX idx_users_accountSexUsername(account,sex,userName);
创建的索引顺序是account→sex→userName,所以order by后面的索引顺序必须是account→sex→userName,当然也可以不使用sex或者sex和userName。
如果在where后面使用了account作为条件,那么order by后面可以只用 sex,userName或者只用sex,当然也可以用account,但是必须要根据索引创建时的顺序进行排序
首先看看正确的例子,没有出现FileSort的
还有一个弄不明白的情况,当where使用了account和sex。order by里account和sex使用了升序排序,userName使用了降序,这样没有出现filesort,就是说where里用到的索引字段,在order by后面可以反过来排序。这个不知道为啥,知道的大神麻烦解科普下
看看错误的例子,出现了FileSort
1.没有根据索引创建的顺序直接使用了userName,而没有用上sex字段
2.如果在where后面加上sex作为条件就不会出现filesort,至于为什么暂时还没弄清楚咋回事
三、groud by关键字优化
参考上面order by