【mysql】索引相关的个人总结，

【mysql】索引相关的个人总结，

重点参考：

• MySQL索引原理及慢查询优化 （美团技术分享网站）：原理、示例优化都写的很好。
  
• 索引很难么？带你从头到尾捋一遍MySQL索引结构，不信你学不会！：原理写的很好。
  
• 【从入门到入土】令人脱发的数据库底层设计：很详细的底层原理

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

参考：

• 为什么用了索引，查询还是慢？
  
• MySQL 索引必须了解的几个重要问题
  

• 数据库中查询记录时是否每次只能使用一个索引？

• 聚簇索引、非聚簇索引、回表：聚簇索引和非聚簇索引讲的很详细。
  

• 聚集索引、辅助索引、覆盖索引、联合索引

• MySQL索引选择及规则整理：仔细看里面提到的“前缀索引”

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中都是非聚簇索引）
• 聚簇索引可以包含多个列（联合索引），但使用的列越少越好

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

（一般都指的是 单列索引，相对 联合索引 而言）

• 叶子节点不包含完整的行数据
• 叶子节点除包含键值以外，还包含一个pointer（或者bookmark）用于告诉InnoDB哪里可以找到与索引相对应的行数据（即需要回表查询，也增加了IO次数）
• 非聚簇索引 要远小于 聚簇索引 （mysql基于此特性，会优化一些sql，例如count(*)）

2.3 联合索引

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

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+树

• MySQL索引原理及慢查询优化 （美团技术分享网站）：原理、示例优化都写的很好。
  
• 索引很难么？带你从头到尾捋一遍MySQL索引结构，不信你学不会！：原理写的很好。
  
• 【从入门到入土】令人脱发的数据库底层设计：很详细的底层原理
  再次，再次，再次通过这3篇大神的文章简单理解就好。如果想深入理解，再baidu/google。

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

忽略。

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

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

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

参考：

• 索引下推（5.6版本+）
• MySQL--索引条件下推优化
• mysql索引篇之覆盖索引、联合索引、索引下推

在前面提到了一个疑问：
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.和-的解析貌似有错误，导致下面的序号是乱的)

• <>, !=, not in
• 对字段进行函数运算
• 索引字段存在null
• 字符串不加单引号，例如phone是varchar类型但sql是where phone=13800010002

• 最左前缀匹配原则
• 区分度高的列（美团文章提到）一般需要join的字段都要求是0.1以上。
• 尽量的扩展索引，不要新建索引。
  联合索引 & 最左前缀匹配原则的优化，当存在(col_a, col_b)的联合索引后，大多情况下不需要再创建a索引
• 例如书 “SQL Tuning“，如果选择性超过 20% 那么全表扫描比使用索引性能更优。

（ps：整理完一看，并没有写或整理出多少东西...但磨磨唧唧也花费了蛮多时间）