mysql调优-排序

mysql调优-排序优化

第一章、mysql索引排序

---

---

mysql的两种排序方式

方式A:filesort(文件排序)

filesort排序:先把结果查询出来,然后在缓存或粗盘中进行排序操作,效率较低;

双路查找(旧)

原理: 需要进行2次磁盘扫描,第一次把需要排序的字段读取出来,然后排序;第二次在去读取其他字段;

单路查找(新)

原理:从磁盘中查询所索要的数据列,然后在内存中完成排序并返回结果;

如果数据量大,导致超出sort_buffer缓存,会导致多次读取磁盘操作,此时mysql会将数据分批放入内存/缓存中并建立临时表,直到数据处理完并返回结果
注意:如果遇到此情况,实际上就已经不是单路查找了,因为多次的读取进行磁盘I/O实际上已经变成了"多路"查找,效率远不如index排序来的快

解决方案:(核心思想就是避免读取的数据超出sort_buffer缓存)

1. 少使用select * 获取非必要的字段;
2. 将sort_buffer_size的和max_length_for_sort_data容量增大,避免数据把容量占满;
   

方式B:index(索引排序)

index排序:利用索引自动排序,无需另做排序操作,效率较高;

如何查看当前Sql排序语句使用的是那种排序机制?

使用mysql自带的EXPLAIN解释工具进行查看
例如：

• 索引排序

  EXPLAIN SELECT id FROM art_works ORDER BY id ASC LIMIT 100 ;
  

  图1- filesoer文件排序

  EXPLAIN SELECT id FROM art_works ORDER BY artist_id ASC LIMIT 100 ;
  

  图2

那些情况时可以用到index排序索引的?

• 情况1: id有主键索引,或者id+其他字段设有联合索引(id,work_name);

  EXPLAIN SELECT id FROM art_works ORDER BY id ASC LIMIT 100 ;
  

  效果如上"图1"
  索引对应(id),(id,work_name)满足最左前缀原则

• 情况2：where查询+order排序索引字段列组合成符合联合索引最左前缀原则

  EXPLAIN SELECT id FROM art_works WHERE artist_id = 5 ORDER BY work_auctionHouse ASC LIMIT 100 ;
  

  

什么情况下即使字段有索引,也无法触发index排序?

• 情况1:where条件+order by条件的索引列满足联合索引最左前缀原则,但where条件使用范围搜索(>,<)

  EXPLAIN SELECT id FROM art_works WHERE artist_id > 5 ORDER BY work_auctionHouse ASC LIMIT 100 ;
  

  

• 情况2 order by条件满足最左前缀原则,但orderby排序条件不一致

  EXPLAIN SELECT id FROM art_works WHERE id > 5 ORDER BY artist_id DESC , work_auctionHouse ASC LIMIT 100 ;
  

  

• 情况3:使用2个单列索引进行排序

  EXPLAIN SELECT id FROM art_works ORDER BY artist_id , work_auctionHouse LIMIT 100 ;
  

    ![在这里插入图片描述](https://img-blog.csdnimg.cn/46b4930007d44680a3222d95325c970f.png?x-oss-process=image/watermark,type_d3F5LXplbmhlaQ,shadow_50,text_Q1NETiBA54m55Yir5YmR,size_20,color_FFFFFF,t_70,g_se,x_16)
  

• 情况4:还是2和索引字段,where使用其中一个,order by使用另一个索引字段;

  EXPLAIN SELECT id FROM art_works WHERE updated_by = 1 ORDER BY artist_id;
  

  

• 情况5:where条件 或 order by 条件使用了函数

  EXPLAIN SELECT id FROM art_works ORDER BY ABS(artist_id);
  

  

• 情况6:索引字段设置了索引长度

  EXPLAIN SELECT id FROM art_works ORDER BY work_currency;
  

  

总结

Explain解释器中的Extra属性值

Using index表示： 表示覆盖索引、也表示所有操作在索引上完成、也可以使用index排序方式建议如果条件允许,尽量使用覆盖索引;

Using filesort表示： 使用了filesort文件排序需要优化;

End

更新不易,如果本篇文章对你有所帮助,请点赞支持,你的支持是对我最大的鼓励,文章如有不严谨/错误的地方请留言批评指正,谢谢!