MySQL基础 - B+树索引的使用
几个必须了解的点：
1. 每个索引都有一棵B+树。
2. InnoDB存储引擎会自动为主键建立聚簇索引(如果没有显示指定主键，则会使用不允许存NULL的唯一键，否则会自动生成ROW_ID主键)
3. 二级索引目录项(非叶子节点)存储的是 索引列值+主键+页号。
4. B+树每层节点都按照索引列的值从小到大的顺序排序组成双向链表，而且每个页内的记录都按照索引列的值从小到大排序组成一个单项链表。如果是联合索引则页面和记录先按照索引列前面的列的值排序，如果该列的值相同，再按照后面的列的值排序。
5. 通过索引查找时，从根节点开始查找，由于每个页中的记录都划分成了若干个组，每个组索引列最大的记录再页内的偏移量会被当作槽依次存放在页目录中，因此可以在页目录通过二分查找快速定位到索引列等于某个值的记录。
索引的代价
1. 空间上的代价：因为每建立一个索引就需要为其生成一棵B+树，每棵B+树的节点都是一个数据页(16K)，这将占用很大的存储空间。
2. 时间上的代价：
a. 增删改操作都需要修改各个B+树索引，B+树每层节点都按从小到大的顺序排序组成双向链表，节点内的记录都按照从小到大的顺序组成了单向链表。而增删改会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行页面分裂、页面回收等操作，以维护节点和记录的排序，从而造成性能消耗。
b. 还有一点就是执行计划，一般情况下一次查询最多使用一个二级索引（特例：索引合并）在执行计划时需要使用不通索引执行查询时的所需成本，最后选取成本最低的索引进行查询。如果建立了太多索引，会导致成本分析过程耗时太多，从而影响查询语句的性能。
所以建立的索引越多，占用的空间也就越多，在增删改或者生成执行计划时性能越差。
应用B+树索引
扫描区间和边界条件
select * from single_table where id>=2 and id<=100;
比如上面语句中，与扫描全部的聚簇索引相比，扫描id值在[2,100]区间中中的记录数据已经很大程度上减少了需要扫描的数量，所以提高了查询效率。
我们把只包含一个值的扫描区间称为单点扫描区间。
包含多个值的扫描区间称为范围扫描区间。
如果想使用某个索引来执行查询，但是又无法通过搜索条件形成合适的扫描区间来减少需要扫描的数量的时，则不考虑使用这个索引执行查询 。
对于B+树索引来说，只要索引列和常数使用=、<=>、IN、NOT IN、IS NULL、IS NOT NULL、>、<、>=、<=、BETWEEN、!=或者LIKE操作符连接起来，就可以产生所谓的扫描区间。不过有些需要注意：
- IN操作符的语义与若干个等值匹配操作符（=）之间用OR连接起来的语义是一样的
SELECT * FROM single_table WHERE key2 IN (1,2); SELECT * FROM single_table WHERE key2 = 1 OR key2 = 2;
- !=产生的扫描区间容易被忽略。比如：
select * from single_table where id != 2 ; 此时的扫描区间是（-∞，2）和（2，+∞）。
- LIKE操作符只有在匹配完整的字符串或者匹配的字符串的前缀才产生合适的扫描区间。
先比较字符串的第一个字符，第一个字符小的哪个字符串就比较小。
如果两个字符串的第一个字符相同，再比较第二个字符；第二个字符比较小的那个字符串就比较小。
反正就是一直向右比较。假如是ab% 就一直比较到前缀为ab的第一条记录，接下来就沿着单向链表向后扫描，直到前缀不为ab。
使用联合索引执行查询时对应的扫描区间
假设为表中的key_part1,key_part2,key_part3列建立idx_key_part二级索引。
先按着key_part1列的值排序，在key_part1列的值相同的情况下，再按照key_part2列的值排序，在key_part1列的值和key_part2列的值相同的情况下，再按照key_part3列的值排序。
对于查询语句：
select * from single_table where key_part1 = 'a' and key_part3 = 'c';
对于符合key_part1 = 'a'条件的二级索引记录，并不是直接按照key_part3列的值进行排序的。我们若想使用idx_key_part索引。可以定位到key_part1 = 'a'的第一条记录，然后沿着记录所在链表向后扫描。所以这个扫描区间是['a','a']。
对于查询语句：
select * from single_table where key_part1 < 'b' and key_part2 = 'a';
对于符合key_part1 < 'b'条件的二级索引记录，并不是直接按照key_part2列的值进行排序的。我们不能根据key_part2 = 'a'进一步减少需要扫描的记录数量。所以这个扫描区间是（-∞，'b'）。
对于查询语句：
select * from single_table where key_part1 <= 'b' and key_part2 = 'a';
对于符合 key_part1 < 'b'条件的二级索引记录，并不是直接按照key_part2列的值进行排序的。
但是对于符合 key_part1 = 'b'条件的二级索引记录时，是按照key_part2列的值排序的。当扫描到第一条不符合key_part1 <= 'b' and key_part2 = 'a'条件的第一条记录时，就可以结束扫描。
所以这个扫描 区间是[(-∞,-∞),('b','a')]。
索引用于排序
1. 使用联合索引是，ORDER BY子句后面的列顺序也必须按照索引列的顺序给出。
2. 对于联合索引进行排序的场景，要求各个排序列的排列规则是一致的，要么是按照ASC(升序)，要么是按照DESC(降序)排列规则。
3. 使用ORDER BY进行降序排列是可以使用索引的。
4. 排序列包含非同一个索引的列是不能使用索引的。
5. 排序列是某个联合索引的索引列，但是排序列在联合索引中并不连续不能使用索引。
6. 排序列不是以单独列名出现在ORDER BY子句中（可能是函数或者表达式修饰过的形式）不能使用索引。
索引用于分组
与B+树索引排序差不多，分组列的顺序也需要和索引列的值排序一致；也可以只使用索引列左边连续的列进行分组。
设key1|key2|key3为一个联合索引idx_key_part
select key1,key2,key3,count(*) from single_table group by key1,key2,key3;
分组操作：
先按照key1分组，key1相同的分为一组。
再把key1相同的每个分组按照key2分组，把key2相同的放到一个小分组中。
再把key2相同的小分组按照key3分为更小的分组，组成小小分组。
然后对这些分组进行统计，统计小小分组的数量。
PS: 如果没有idx_key_part索引，就需要建立一个用于统计的临时表，在扫描聚簇索引的记录时将统计结果填入这个临时表。
如果有idx_key_part索引，恰巧group顺序和联合索引列顺序一致，可以直接使用idx_key_part进行分组。
回表的代价
select * from single_table where key1>'a' and key1<'c';
先补补课：随机IO是指读写操作时间连续，但访问地址不连续，随机分布在磁盘的地址空间中。随机I/O需要花费昂贵的磁头旋转和定位来查找。
1. 以全表扫描的方式执行查询，直接扫描全部的聚簇索引，针对每一条聚簇索引记录，都判断条件是否成立，如果成立则发送到客户端，否则跳过该记录。
2. 使用key1索引执行：
a. 首先根据搜索条件确定查询区间为（‘a’,'c'）然后扫描所有该区间的二级索引记录。因为查询内容是* 拿到二级索引记录还要获取二级索引主键对应的聚簇索引记录，这就是回表。
b. 对于InnoDB来讲，索引中的数据页都是存放在磁盘上的，需要时再加载到内存。
c. 虽然二级索引是排好序的，但是读出来的主键ID可能是无序的，从而回表会产生大量随机IO。
d. 如果该范围数据量较大，需要回表的次数过多，还不如执行全表扫描来的快。查询优化器会做出决策到底是 二级索引+回表还是全表扫描来执行。
e. 因为查询优化器会按照扫描数量来决定，所以使用limit来限制返回条数可以让查询优化器更倾向于使用二级索引+回表。
创建和使用索引需要注意：
- 只为用于搜索、排序或分组的列创建索引。
- 当列中不重复值的个数在总记录条数中的占比很大时，才为该列创建索引。
- 数据占比越大，回表代价越大，性能损耗越高。
- 索引列的值类型尽量小。
- 节约空间。
- 可以只为索引列的前缀创建索引，以减少索引占用的存储空间。
- 尽量使用覆盖索引进行查询，以避免回表带来的性能损耗。
- 查询结果只包含索引列，就不需要回表了。
- 排序也是直接用索引排序，就不需要回表排序了。
- 让索引以列名的形式单独出现在搜索条件中，别搞表达式和函数，mysql 不会给你简化
- 为了尽可能少的让聚簇索引发生页面分裂的情况，建议让主键拥有AUTO_INCREMENT属性。
- 定位删除表中冗余和重复索引。
