MySQL索引篇

索引合并：

1.intersection(交集索引合并)

select * from table where index1_column = 'a' and index2_column='b';

这条sql可能的执行方式有三种：

· 通过index1_column列查到主键后回表，再通过index2_column='b'的过滤条件筛选

· 通过index2_column列查到主键后回表，再通过index1_column='b'的过滤条件筛选

· 通过index1_column列和index2_column列查主键，因为二级索引列等值情况下主键是有序的，所以两个索引查询出来的结果直接取交集，再用交集的结果回表查数据

2.union(并集索引合并)

select * from table where index1_column = 'a' or index2_column = 'b';

对于这样一条SQL， 在5.0版本以前是只能走全表扫描的，除非写成下面这样，分成两个索引查询，再取结果集的并集作为返回结果。

select * from table where index1_column = 'a' 
union
select * from table where index2_column= 'b';

在5.0之后，提出了union索引合并的概念，就不再需要写union语句也可以走索引查询了。

SQL执行方式为：通过index1_column列和index2_column列查主键，因为二级索引列等值情况下主键是有序的，所以两个索引查询出来的结果直接取并集，再用并集的结果回表查数据。

 3.sort-union(排序后并集索引合并)

select * from table where index1_column > 'a' or index2_column < 'b';

对于这样的查询条件，查出来的主键只是局部有序的，无法做到全局有序。所以相较于union方式的索引合并，在做主键的并集操作前会多一个排序操作，保证回表时的查询主键是有序的。

为什么索引合并对主键一定要求是有序的呢？

因为这样可以确定回表时的扫描区间的大小和顺序，尽可能多的使用顺序IO去查询数据。由此引入扫描区间的概念。

扫描区间：

扫描区间在我的理解中是一个数学里的概念。由于一个select语句在执行过程中一般最多只能使用一个二级索引，除非发生了索引合并，而索引合并本质上也是多个单索引查询的结果集。所以接下来只取最小单元，对单索引查询做分析。

一个查询中的where条件约束，其实是等价于数学里的一个一元不等式。M有SQL的查询优化就像是一元不等式的化简过程。优化出的结果就像不等式化简后的结果，就是针对一个未知数的取值范围的界定，这样就能在一个负无穷到正无穷的横轴上清晰的标注出可能的取值范围，这个取值范围就是扫描区间。在这个横轴上每一个区间的左值就是一次随机IO，区间内的值就是顺序IO。单点的情况就是一次随机IO。

索引的创建策略

1.索引列的类型尽量小。

· 所占的空间越小，在CPU运行计算时的消耗就小

· B+树的叶节点就是MySQL的页，所占空间越小，单页能存放的数据就越多。而MySQL查询的最小单元就是页，也就是说，单次查询的数据量更大，缓存的数据量也更大，加快读写效率。

如果需要索引一个很长的字符列，可以通过模拟哈希索引和前/后缀索引的方法来优化。

2.索引列的离散性

由于B+树是通过索引列的值来构建的，高离散性的索引列可以帮助MySQL在查找时过滤更多的行。如果离散性不高，极端点举例，这个列的所有值都相等，那该列其实就完全没有建索引的必要。

3.索引列的选择

索引列的选择一定发生在where语句后或者group by子句或者order by子句。这本身就是对B+树这种结构的一个充分利用。

三星索引

1.索引走where子句的查询时能尽可能多的走顺序查询，而非随机查询

2.索引中的数据顺序和查找中的排列顺序一致

3.这是一个覆盖索引

第三颗星的重要程度最高，不回表能够显著的减少聚簇索引的随机读。

索引的代价

1.一个索引就是一颗B+树，存储空间的消耗是比较大的。

2.每次表中的数据发生了增删改操作，需要对该表的每个索引都进行相应的更新，用于维护B+树的稳定性。这个维护过程必然会对性能和时间造成影响。

索引下推(Index Condition Pushdown，ICP)

在5.6版本后，对于二级索引提出了索引下推的概念，用于回表的优化，通过减少回表的数据量减少随机IO的次数。之所以不包含聚簇索引，是因为聚簇索引没有回表这一说法。

释义：

index(index_column1, index_column2, index_column3)
select * from table where 
index_column1 = 'a' and index_column2 > 10 and index_column3 = 'b;

比如这条SQL，假设这条SQL不会走聚簇索引而是二级索引。

如果没有使用索引下推的话，执行方式是：先通过这个复合索引的前两列找到主键，再回表查询数据，再根据查出来的数据做index_column3='b'的判断做过滤。

如果使用索引下推的话，执行方式是：先通过这个复合索引的前两列查询数据，再根据查出来的数据做index_column3='b'的判断将不符合条件的主键过滤掉，再回表查询数据。

触发：

普通二级索引或者联合二级索引都有可能触发索引下推优化。只要查询语句对该索引的检索方法是range、ref、eq_ref、req_or_null，且where子句中的所有条件筛选列是该索引持有的列，就满足了索引下推的触发条件。检索方法给到range级别已经很低了，也就是说，只要对索引的检索方法仍是我们常说的使用到了索引，而不是对该索引的全表扫描，就满足了前置条件。