avatar

Mysql 索引进阶

索引使用场景

建议创建索引场景

  1. 主键自动创建唯一索引
  2. 频繁作为查询条件的字段应该创建索引where
  3. 多表关联中,关联字段应该创建索引ON两边都要创建索引
    SELECT * from user left join order on user.id = order.userid
  4. 查询中排序的字段,应该创建B+ tree索引
  5. 覆盖索引。 不需要回表查询
    eg: user表 创建组合索引(name,age)
    SELECT * FROM user — 全表扫描,没有使用索引
    SELECT name,age FROM user —索引覆盖,不需要回表查询,在索引树上就能直接得到结果
  6. 统计或者分组字段,应该创建索引

不建议创建索引场景

  1. 表数据太少
  2. 频繁更新的数据(索引是需要维护的)
  3. 查询字段使用频率不高

组合索引

由多个字段组成的索引

1
ALTER TABLE table_nanme ADD INDEX index_name(col1,col2,col3)

为什么使用组合索引

  • 一个顶N个”。eg:建一个(a,b,c)的组合索引,那么实际上等于建了(a),(a,b),(a,b,c)三个索引。
    1. 每个索引在数据变更后是需要维护,使用组合索引就只需要维护一个
    2. 减少磁盘空间开销,索引建立索引树是需要磁盘空间的
  • 覆盖索引。 eg:同样有组合索引(a,b,c),如果有以下SQL:SELECT a,b,c FROM table WHERE a=1 and b=2 and c=3,那么Mysql可以直接从索引树取得数据,而无需回表查询,这减少了很多的随机IO操作。
  • Mysql每次查询都只会使用一个索引。索引列越多,通过索引筛选出的数据越少。eg:有1000W条数据饿表,有以下SQL:SELECT a,b,c FROM table WHERE a=1 and b=2 and c=3,假设每个条件可以过滤出10%的数据。
    1. 单列索引(a),那么通过索引筛选出1000W*10%=100W,然后再回表100W条数据中找出符合b=2 and c=3 的数据
    2. 组合索引(a,b,c),那么通过索引筛选出的结果是1000W*10%*10%*10% = 1W

组合索引的存储结构

Mysql_zuhe_index

组合索引bcd,在索引树的样子如图,在比较过程中,先判断b再判断c然后是d。

  • 下面举例详细说明

首先,表T1有字段a,b,c,d,e,其中a是主键,除e为varchar其余为int类型,并创建了一个联合索引idx_t1_bcd(b,c,d),然后b、c、d三列作为联合索引,在B+树上的结构正如上图所示。联合索引的所有索引列都出现在索引数上,并依次比较三列的大小。上图树高只有两层不容易理解,下面是假设的表数据以及我对其联合索引在B+树上的结构图的改进。PS:基于InnoDB存储引擎。

Mysql_zuhe_index

  • bcd联合索引在B+树上的结构图

对于联合索引来说只不过比单值索引多了几列,而这些索引列全都出现在索引树上。对于联合索引,存储引擎会首先根据第一个索引列排序,如上图我们可以单看第一个索引列,如,1 1 5 12 13…他是单调递增的;如果第一列相等则再根据第二列排序,依次类推就构成了上图的索引树,上图中的1 1 4 ,1 1 5以及13 12 4,13 16 1,13 16 5就可以说明这种情况

Mysql_zuhe_index

  • 联合索引的查找方式

当我们的SQL语言可以应用到索引的时候,比如 select * from T1 where b = 12 and c = 14 and d = 3; 也就是T1表中a列为4的这条记录。存储引擎首先从根节点(一般常驻内存)开始查找,第一个索引的第一个索引列为1,12大于1,第二个索引的第一个索引列为56,12小于56,于是从这俩索引的中间读到下一个节点的磁盘文件地址,从磁盘上Load这个节点,通常伴随一次磁盘IO,然后在内存里去查找。当Load叶子节点的第二个节点时又是一次磁盘IO,比较第一个元素,b=12,c=14,d=3完全符合,于是找到该索引下的data元素即ID值,再从主键索引树上找到最终数据。

Mysql_zuhe_index

  • 最左前缀匹配原则

之所以会有最左前缀匹配原则和联合索引的索引构建方式及存储结构是有关系的。

首先我们创建的idx_t1_bcd(b,c,d)索引,相当于创建了(b)、(b、c)(b、c、d)三个索引,看完下面你就知道为什么相当于创建了三个索引。

我们看,联合索引是首先使用多列索引的第一列构建的索引树,用上面idx_t1_bcd(b,c,d)的例子就是优先使用b列构建,当b列值相等时再以c列排序,若c列的值也相等则以d列排序。我们可以取出索引树的叶子节点看一下。

Mysql_zuhe_index

索引的第一列也就是b列可以说是从左到右单调递增的,但我们看c列和d列并没有这个特性,它们只能在b列值相等的情况下这个小范围内递增,如第一叶子节点的第1、2个元素和第二个叶子节点的后三个元素。 ​
由于联合索引是上述那样的索引构建方式及存储结构,所以联合索引只能从多列索引的第一列开始查找。所以如果你的查找条件不包含b列如(c,d)、(c)、(d)是无法应用缓存的,以及跨列也是无法完全用到索引如(b,d),只会用到b列索引。

组合索引使用规范

explain(执行计划)

索引失效分析

文章作者:
文章链接: https://www.fundodoo.com/zh-CN/2020/04/13/24.html
版权声明: 本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 醉探索戈壁
打赏
  • 微信
    微信
  • 支付寶
    支付寶

评论