1、filesort优化算法.
在mysql version()<4.1之前,优化器采用的是filesort第一种优化算法,先提取键值和指针,排序后再去提取数据,前后要搜索数据两次,第 一次若能使用索引则使用,第二次是随机读(当然不同引擎也不同)。mysql version()>=4.1,更新了一个新算法,就是在第一次读的时候也把selcet的列也读出来,然后在sort_buffer_size中 排序(不够大则建临时表保存排序顺序),这算法只需要一次读取数据。所以有这个广为人传的一个优化方法,那就是增大sort_buffer_size。 Filesort第二种算法要用到更的空间,sort_buffer_size不够大反而会影响速度,所以mysql开发团队定了个变量 max_length_for_sort_data,当算法中读出来的需要列的数据的大小超过该变量的值才使用,所以一般性能分析的时候会尝试把 max_length_for_sort_data改小。
2、单独order by 用不了索引,索引考虑加where 或加limit
先建一个索引(last_login),建的过程就不给出了
mysql> explain select * from one order by last_login desc;
开始没limit查询是遍历表的,加了limit后,索引可以使用,看key_len 和key
3、where + orerby 类型,where满足最左前缀原则,且orderby的列和where子句用到的索引的列的子集。即是(a,b,c)索引,where满足最左前缀原则且order by中列a、b、c的任意组合
mysql> explain select * from one where username=’abgvwfnt’ and password =’123456
上面两条语句明显的区别是多了一个非索引列level的排序,在extra这列对了Using filesort
笔者测试结果:where满足最左前缀且order by中的列是该多列索引的子集时(也就是说orerby中没最左前缀原则限制),不管是否有asc ,desc混合出现,都能用索引来满足order by。
笔者测试过,因为篇幅比较大,这里就不一一列出。
Ps:很优化博文都说order by中的列要where中出现的列(是索引)的顺序一致,笔者认为不够严谨。
4、 where + orerby+limit
这个其实也差不多,只要where最左前缀,orderby也正确,limit在此影响不大
5、如何考虑order by来建索引
这个回归到创建索引的问题来,在比较常用的oder by的列和where中常用的列建立多列索引,这样优化起来的广度和扩张性都比较好,当然如果要考虑UNION、JOIN、COUNT、IN等进来就复杂很多了