究极无敌细节版 Mysql索引( 五 ) _生活百科

文章插图
mysql会根据联合索引中记录的age对数据进行过滤，这时候age不等于10的数据将不会回表，将回表次数从4优化到了2，这就是索引下推。

如何安排联合索引的顺序
第一原则是，如果通过调整顺序，可以少维护一个索引，那么这个顺序往往就是需要优先考虑采用的，比如业务中存在两个高频查询，根据name ，以及根据name查询后根据age排序，这个时候我们应该建立name age的联合索引，上面我们说过name,age的所有其中name是有序的，age只在name相同的情况下才是有序的，这样可以减少建立name的普通索引，并且优化排序，甚至利用索引下推减少回表。如果还存在根据age进行的查询，那么需要单独维护一个age的普通索引

六丶索引与排序和分组1.索引用于排序假设我们有一张表存储id，姓名，年龄以及城市，我们在城市字段上建立索引
执行select city,name,age from t where city='杭州' order by name limit 1000 ;city上具备索引，那么可以通过city字段拿到符合要求的数据

文章插图
拿到城市和主键的信息之后，还需要回表，来到主键索引上查询到需要的列，接下来需要排序

如果sort_buffer(MySQL 会给每个线程分配一块内存用于排序，称为 sort_buffer)可以容纳下目标记录，那么mysql会使用sort_buffer进行快速排序，这个过程叫做全字段排序(全部的字段都在sort_buffer中)

文章插图
如果sort_buffer无法容纳下这么多记录，将使用外部文件排序，mysql把需要排序的数据分为多个文件，分别快排然后合并
如果mysql认为行太长，那么会使用row_id排序——从city索引找到一条数据，回表拿到索引需要排序的字段以及主键id ，在sort_buffer中只存储需要排序的字段和主键，然后排序后，再次回表查询全部需要的列，组成结构集返回

文章插图
如果直到的limit 比较小，比如limit 3 ，也许mysql会维护一个大小为3的堆，进行排序获得前3条

上面讲了mysql是如何排序的，可以看到上述的排序方式，都需要利用内存或者利用磁盘文件进行排序，总体来说是浪费空间以及效率不高的，那么如何可以让order by更快昵——创建一个 city 和 name 的联合索引

文章插图
有了这个联合索引， mysql可以找到城市为杭州的数据，然后回标查询需要的字段，然后向右取下一条，并不需要排序，因为city=杭州的数据name自然是有序的。这就是索引对排序的优化

联合索引排序顺序需要符合最左前缀原则
联合索引排序，不能将ACS和DESC混合使用（mysql8降序索引似乎可以解决这个问题）
如果形成扫描区间的列和排序的列不是同一个索引，可能也不能使用到索引优化排序
select * from key1 = a oder by key2 key1 ， key2不是联合索引，各自包含一个索引，那么mysql选择key1索引数据。
排序列如何使用了函数，那么不能排序，函数也许会改变索引的单调性

2.索引用于分组select key1,key2,key3 ,count(*) from table group by key1,key2,key3 如果key1 ， key2 ， key3没有建立联合索引，那么需要建立用于统计的临时表，将扫描的数据加入到临时表进行统计，但是如果我们按照 key1,key2,key3的顺序建立了联合索引，那么索引中的主键自然就是分好组的。索引用于分组的注意事项基本上和排序相同，这里不做过多赘述
七丶索引建立和使用原则1.为搜索，排序，分组的列建立索引一般只为出现在where后面的列，连接子句中的列，出现在order by,或者group by的列进创建索引。不要无脑建立索引，索引是需要存储在磁盘上的，占用空间，并且在新增，删除，修改的时候还需要维护索引，是需要时间的。