重点参考:

一定要仔细看其中讲的索引原理!!!本文中都是简单的总结。

SRE实战 互联网时代守护先锋,助力企业售后服务体系运筹帷幄!一键直达领取阿里云限量特价优惠。

参考:

1. 重点知识概括

1.1索引类型

  • Clustered Index(聚簇索引 或 聚集索引)
  • Secondary Index(非聚簇索引 或 辅助索引 或 二级索引,一般指的都是 单列)
  • 联合索引,多列二级索引
  • 前缀索引,二级索引只截取前N个字符作为索引
  • Covering Index(覆盖索引)

1.2 相关原理

  • B+树
  • 最左前缀匹配原则
  • 联合索引的最左前缀匹配原则
  • Index Condition Pushdown (ICP), 索引下推

1.3 使用索引的疑问或总结

2. 索引类型

总体来说,索引类型只存在:聚簇索引 和 非聚簇索引(二级索引)。
联合索引前缀索引都是非聚簇索引中的更明确分类。
覆盖索引(个人觉得)并不算一种索引类型,而是基于非聚簇索引的原理对查询的一种优化方式。

“回表查询”:
回到聚簇索引取行数据。1次回表查询需要2次B+树的遍历查找,所以应该尽量避免回表(不要刻意避免,以免得不偿失)。

2.1 Clustered Index(聚簇索引 或 聚集索引)

  • 键值的逻辑顺序决定了表中相应行的物理顺序
  • 叶子节点中存放了该索引对应的行记录的完整数据(重点)
  • InnoDB有且只有一个聚簇索引(一般都是PK,MyISAM中都是非聚簇索引)
  • 聚簇索引可以包含多个列(联合索引),但使用的列越少越好
  1. 为什么InnoDB只有一个聚簇索引,而不将所有索引都使用聚簇索引?
    因为“叶子节点中存放了该索引对应的行记录的完整数据”,如果所有索引都是聚簇索引,意味着每个叶子节点都保存一份数据,会造成数据的冗余和资源的浪费。

  2. 哪些列索引可以是聚簇索引?
    InnoDB中一般都是PK;
    如果不存在PK,则会选择唯一非空索引代替。
    如果不存在唯一非空索引,则会隐式定义一个PK来作为聚集索引。

  3. 建议向聚簇索引中插入有序的值
    例如,聚簇索引列是pk,建议选择int, auto_increment,而避免使用无序的UUID
    a)无序的pk使数据存储稀疏,这就会出现聚簇索引有可能有比全表扫面更慢
    b)无序的pk新插入数据时,可能需要插入到某些列的中间,这可能导致数据页分裂,从而移动行数据。
    c)有序的pk值很好的避免了上述无序的pk带来的问题。

2.2 Secondary Index(非聚簇索引 或 辅助索引 或 二级索引)

(一般都指的是 单列索引,相对 联合索引 而言)

  • 叶子节点不包含完整的行数据
  • 叶子节点除包含键值以外,还包含一个pointer(或者bookmark)用于告诉InnoDB哪里可以找到与索引相对应的行数据(即需要回表查询,也增加了IO次数)
  • 非聚簇索引 要远小于 聚簇索引 (mysql基于此特性,会优化一些sql,例如count(*))
  1. 为什么叫二级索引的一种解释
    二级索引需要两次B+树的遍历查找才能取到数据。
    第一次通过二级索引找到索引的叶子节点,从而找到数据的主键(或者其聚簇索引的索引值),然后用该主键去聚簇索引中再次通过B+树查找到完整的行数据。所以,“回表”会有2次B+树的查找过程。

  2. 为什么辅助索引使用“聚簇索引的索引值”作为pointer,而不是使用"地址值"作为pointer?
    使用"地址值"带来的好处:
    1)"地址值"比"聚簇索引的索引值"占用更少的空间
    2)减少了1次B+树查找的过程。
    但是,相应的需要维护辅助索引,这是一个相当困难的维护工作。
    使用“聚簇索引的索引值”作为pointer时,当出现行移动或者数据页分裂时,辅助索引不受影响(即不需要维护 辅助索引)

  3. 辅助索引中的最左前缀匹配原则
    单列辅助索引遇到<, <=, =, >, >=, between, like(右边模糊)可以用到索引。
    假设存在索引(col_1),例如liek 'xxx%'是可以用到辅助索引的。

2.3 联合索引

属于辅助索引,只是:将多列作为索引,默认多列往右匹配。

  1. 联合索引中的最左前缀匹配原则
    联合索引遇到范围查询时就停止匹配。(待商榷)
    假设存在索引(a, b, c, d),那么where a =1 and b = 2 and c > 3 and d = 4中,a, b可以用到联合索引。此时,创建(a, b, d, c)索引更合适,并且由于查询优化器的优化 where中 a,b,d可以任意顺序。
    (扩展疑问:以上联合索引中,c能否用到索引?参考后面提到的索引下推

  2. 优化器对单列辅助索引联合索引的选择
    例如存在单列辅助索引(col_1) 和 联合索引(col_1, col_2),在执行查询时,优化器是选择 单列辅助索引 还是 联合索引,主要还是需要结合实际SQL。
    where col_1=xxx,可能会选择 单列辅助索引。(不确定,具体还是看 explain)
    where col_1=xxx order by col_2,选择 联合索引,因为col_2是在col_1的基础上排序,避免了进行1次filesort

2.4 前缀索引

前缀索引能有效减小索引文件的大小,提高索引的速度。
但是前缀索引也有它的坏处:
1)不能在 ORDER BY 或 GROUP BY 中使用前缀索引
2)也不能把它们用作覆盖索引(Covering Index)。

针对2)的个人理解,前缀索引的叶子节点记录的也只是"主键"和"前缀值",需要回表才能拿到完整的值。

例如,假设需要创建 article_title列的索引,但是 article_title 可能很长(索引占用空间多),那么可以只取article_title的前N个字符作为 前缀索引。
语法:CREATE INDEX index_name ON table_name(column_name(length));

2.5 Covering Index(覆盖索引)

InnoDB存储引擎支持覆盖索引,即从辅助索引中就可以得到查询的记录,而不需要查询聚集索引中的记录。因此:
1) 使用覆盖索引可以避免回表查询(减少了大量的IO操作)
例如,假设存在索引(col_1, col_2, col_3),现有查询SQL select * from table where col_1 = xx。如果在需求满足的情况下,可以有效利用覆盖索引来优化查询SQL select col_1, col_2, col_3 from table where col_1 = xx

2) 有助于统计
例如,假设存在非聚簇索引(name)和聚簇索引(id),在执行统计查询select count(*)时,查询优化器可能会选择使用 非聚簇索引。因为,非聚簇索引 要远小于 聚簇索引。

暂时还无法理解2),特别是 聚集索引、辅助索引、覆盖索引、联合索引 中基于 联合索引 & count 的示例更不理解~~~

student表:PRIMARY KEY (id), KEY idx_name (name), KEY idx_school_age (school,age)`

执行sql:select count(*) from student
优化器会选用 idx_name 这个辅助索引。(具体看 explain)

执行SQL:select count(*) from student where age > 10 and age < 15
优化器会选用 idx_school_age 这个辅助索引。(具体看 explain)

3. 索引中的原理

3.1 B+树

3.2 (单列辅助索引的)最左前缀匹配原则

忽略。

3.3 联合索引的最左前缀匹配原则

相比单例辅助索引的最左前缀匹配原则,联合索引 是从左往右依次比较列。
例如col_1, col_2, col_3, col_4,先比较col_1,再比较col_2,以此类推。

3.4 Index Condition Pushdown (ICP), 索引下推

参考:

在前面提到了一个疑问:
where a = 1 and b = 2 and c > 3 and d = 4在已有联合索引(a, b, c, d)时,c/d能否用到联合索引?

在主要阅读的的2篇文章(美团大佬、java知音)都说的是:

最左前缀匹配原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。
比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的
如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

其中并未提到c,而且个人觉得 c&d 都可以用到索引(只是不知道其性能如何)。
针对这疑问,我看到了索引下推

例如以上SQL可能有2种执行可能:
1)假设 c&d 都没用到索引,根据联合索引查询到满足 a&b 的条件,然后就回表找到所有行数据,再进行遍历筛选出c > 3 and d = 4的数据行。

2)假设 c&d 都用到了索引,那么最后回表的数据行 一定小于等于 1)中回表的数据行,这就是mysql的索引下推

mysql默认启用索引下推,可以通过变量来修改:
SET optimizer_switch = 'index_condition_pushdown=off';

注意:

a) 索引下推只能用于二级索引。(聚簇索引包含了行数据,这时候索引下推并不会起到减少回表操作的效果)

b) 索引下推一般可用于所求查询字段(select列)不是/不全是联合索引的字段,查询条件为多条件查询且查询条件子句(where/order by)字段全是联合索引。(没理解~~)

备注:
个人并不确定是 c&d 都用到索引,还是只有 c 用到索引,d未用到索引。

4. 索引使用中的疑问总结

(ps: cnblog的markdown对于 1.-的解析貌似有错误,导致下面的序号是乱的)

  1. 索引不一定能提高查询速度,甚至可能比不存在索引时更慢!

  2. 一次查询只能用到1个索引
    如果多列查询存在多个索引,查询优化器一般选择区分度高的索引列。

  3. 区分度,简单公式:count(distinct col) / count(*)。
    意味着通过索引列可以返回更少的rows(回表查询的行数更少)
    具体需要看实际数据,比如假设is_download只存在true/false,当下载完成后将false改为true。
    此时实际业务数据是很少存在false,当存在大量查询false的时候,可以创建索引。

  4. 覆盖索引拥有更高效率和性能

  5. 无法使用索引的情况
  • <>, !=, not in
  • 对字段进行函数运算
  • 索引字段存在null
  • 字符串不加单引号,例如phone是varchar类型但sql是where phone=13800010002
  1. 创建索引的原则
  • 最左前缀匹配原则
  • 区分度高的列(美团文章提到)一般需要join的字段都要求是0.1以上。
  • 尽量的扩展索引,不要新建索引。
    联合索引 & 最左前缀匹配原则的优化,当存在(col_a, col_b)的联合索引后,大多情况下不需要再创建a索引
  • 例如书 “SQL Tuning“,如果选择性超过 20% 那么全表扫描比使用索引性能更优。
  1. 基于 新增/修改索引 来优化查询时,不能只看到当前需要优化的SQL,还需要结合该表的其余查询SQL来综合分析。
    例如,当前待优化sql创建了联合索引(col_1, col_2, col_3, col_4),但是可能另外一条sql可能需要联合索引(col_1, col_2, col_4)。所以,最终联合索引(col_1, col_2, col_4, col_3)更适合。

  2. 联合索引,如何决定其col的顺序?
    最左前匹配原则&列的区分度 的理解运用,当然还要结合实际SQL。

  3. 范围查询是否会使用索引(例如 like、between-and、in)?
    可以使用到索引(但具体还是要看写法)。

  4. 性别字段是否需要创建索引(十万级以上的表,只有男/女)?
    为什么重复值高的字段不能建索引
    mysql千万级大表,关于性别及年龄字段是否需要加索引?
    没有绝对,要根据实际的数据。
    例如1亿的数据,其中只有10万的"男"数据,并且总是查询少的那部分数据,那么存在索引的效果更好。

(ps:整理完一看,并没有写或整理出多少东西...但磨磨唧唧也花费了蛮多时间)

扫码关注我们
微信号:SRE实战
拒绝背锅 运筹帷幄