MySQL查询优化

 

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