究极无敌细节版 Mysql索引( 六 )

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字段拿到符合要求的数据

究极无敌细节版 Mysql索引

文章插图
拿到城市和主键的信息之后,还需要回表,来到主键索引上查询到需要的列,接下来需要排序
  • 如果sort_buffer(MySQL 会给每个线程分配一块内存用于排序,称为 sort_buffer)可以容纳下目标记录,那么mysql会使用sort_buffer进行快速排序,这个过程叫做全字段排序(全部的字段都在sort_buffer中)
    究极无敌细节版 Mysql索引

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

    文章插图
  • 如果直到的limit 比较小,比如limit 3,也许mysql会维护一个大小为3的堆,进行排序获得前3条
上面讲了mysql是如何排序的,可以看到上述的排序方式,都需要利用内存或者利用磁盘文件进行排序,总体来说是浪费空间以及效率不高的,那么如何可以让order by更快昵——创建一个 city 和 name 的联合索引
究极无敌细节版 Mysql索引

文章插图
有了这个联合索引,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

经验总结扩展阅读