索引

聚集索引(clustered index) : id为PK，聚集索引，叶子节点存储行记录；

辅助索引(secondary index) : name为KEY，普通索引，叶子节点存储PK值，即id

回表：通过辅助索引查询数据，需要扫描两遍索引树：

• 先通过普通索引定位到主键值id=5
• 在通过聚集索引定位到行记录

B+树索引

可用查询类型,假设存在索引 (a,b,c),最左前缀匹配原则,想要所有列都使用索引，筛选条件必须按照索引字段从左到右的顺序匹配，并且向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如

• 全值匹配 select * from table where a=xxx and b=xxx and c=xxx
• 匹配最左前辍 select * from table where a=xxx 或者select * from table where a=xxx b=xxx
• 匹配范围值为最右列 select * from table where a>xxx 或者 select * from table where a>xxx and b>xxx

比如以下情况无法使用索引（或者部分条件无法使用索引）：

• 非匹配最左前辍 select * from table where b=xxx
• 部分列最左前辍 select * from table where a=xxx and c=xxx 只能使用索引的a列
• 中间存在范围匹配 select * from table where a=xxx and b>xxx and c=xxx 只能使用索引的a,b列

这些原则的本质原因在于索引 (a,b,c)在索引树的最底层上是按照a,b,c的顺序进行排序的。索引顺序必须和查找的顺序一致

三星索引（如何评价一个索引）：

• 将相关记录放置到一起（获得一星）
• 索引中数据的顺序与查找的顺序一致（获得两星）
• 索引的列包含了查询中需要的全部列，即索引覆盖 （获得三星）

前辍索引

字符串太大，只索引其前辍以节省索引空间。一般前辍越长选择性越高，但索引空间消耗越多。 缺点在于无法使用前辍索引进行order by 和 group by，也无法进行索引覆盖

联合索引的顺序

具体情况具体分析，一般按照where条件中选择性高的放在前面

索引合并

todo

聚集索引

InnoDB引擎通过主键聚集数据。聚集索引的叶子页存放着数据行，数据行在物理上是按照聚集索引键的顺序进行存储的

优点：

• 相同（或相邻）主键的数据存放一起。一次磁盘IO就可读取。具有空间局部性
• 数据访问快。聚集索引的叶子页就存放着数据行

缺点：

• 插入数据依赖插入顺序，按照主键的顺序的插入是最快的方式，非主键顺序插入空间局部性低，不利于缓冲池的工作
• 更新聚集索引代价高，因为要移动之前插入的数据行
• 非主键顺序插入，更慢，以及更大的索引空间,因为页分裂和碎片

页分裂与合并

https://zhuanlan.zhihu.com/p/98818611

页分裂会导致聚集索引存储在物理上的不连续

索引覆盖

索引列包含了查询的所有列，使得所有信息可以从索引中获取，不需要回表查询 比如存在联合索引(a,b,c)

select a,b,c from table where a=xxx and b=xxx

索引条件下推 Index Condition Pushdown (ICP)

引用官方文档

ICP的目的：

For InnoDB tables, ICP is used only for secondary indexes. (InnoDB中，ICP只适用于辅助（非聚集）索引)

The goal of ICP is to reduce the number of full-row reads and thereby reduce I/O operations. (ICP的目的在于降低整个数据行读取的次数，以此来降低IO)

For InnoDB clustered indexes, the complete record is already read into the InnoDB buffer. Using ICP in this case does not reduce I/O. (对于聚集索引，(索引扫描时)完整的数据行已经被读入InnoDB缓冲中，此时使用ICP已无意义，达不到降低IO的作用)

To understand how this optimization works, first consider how an index scan proceeds when Index Condition Pushdown is not used: (为了理解ICP的优化是如何进行的，先让我们考虑一下，如果没有开启ICP，索引扫描是如何处理的)

• Get the next row, first by reading the index tuple, and then by using the index tuple to locate and read the full table row. (当获取下一行数据时，首先读取索引元组，通过索引元组定位并读取完整的数据行)

• Test the part of the WHERE condition that applies to this table. Accept or reject the row based on the test result. (测试数据行是否满足where中的条件，如果满足，加入结果集中)

Using Index Condition Pushdown, the scan proceeds like this instead: (如果使用ICP，扫描过程会如下所示)

• Get the next row’s index tuple (but not the full table row). (获取下一个索引元组，而不是完整的数据行)

• Test the part of the WHERE condition that applies to this table and can be checked using only index columns. If the condition is not satisfied, proceed to the index tuple for the next row. (通过索引元组中的字段信息，查看是否满足where条件。如果不满足，移动至下一个索引元组进行处理)

• If the condition is satisfied, use the index tuple to locate and read the full table row. （如果满足where条件，使用索引元组定位并读取完整的数据行）

• Test the remaining part of the WHERE condition that applies to this table. Accept or reject the row based on the test result. (测试剩余字段(索引未覆盖的字段)是否满足where条件，如果满足，加入结果集中)

相关资料：

https://dev.mysql.com/doc/refman/57/en/index-condition-pushdown-optimization.html https://www.cnblogs.com/zengkefu/p/5684101.html

例子： 存在索引 (a,b,c)

select * from table where a = xxx and b > xxx and c = xxx

索引匹配至b > xxx 时就会停止，如果未开启ICP，索引中的c列信息并未使用到。如果开启ICP，则可以利用索引中列的信息。explain分析中，Extra输出Using index condition

Explain

Extra

Using index

• 查询的列被索引覆盖，并且where筛选条件是索引的是前导列，Extra中为Using index

Using where Using index

• 查询的列被索引覆盖，并且where筛选条件是索引列之一但是不是索引的不是前导列，Extra中为Using where; Using index，意味着无法直接通过索引查找来查询到符合条件的数据
• 查询的列被索引覆盖，并且where筛选条件是索引列前导列的一个范围，同样意味着无法直接通过索引查找查询到符合条件的数据

Using where

• 查询的列未被索引覆盖，where筛选条件非索引的前导列，Extra中为Using where

Using index condition

• 查询的列不全在索引中，where条件中是一个前导列的范围
• 查询列不完全被索引覆盖，查询条件完全可以使用到索引（进行索引查找）