这是我参与8月更文挑战的第22天，活动详情查看：8月更文挑战
索引提高查询速度
跟索引相关的一些算法
平衡二叉树
满足二叉查找树的定义，另外必须满足任何节点的两个子树的高度差最大为 1。
显然平衡二叉查找树的平均查找速度比顺序查找更快。
但是平衡二叉树有个缺点就是，每个节点最多只有两个分支，如果数据量比较大，要经历多层节点才能查询在叶子节点的数据。
如果在平衡二叉树的基础上，每个节点可以有多个分支，那即使在叶子节点的数据，是不是查询效率也比较高呢？
这就引出了 B 树结构。
B树
B 树可以理解为一个节点可以拥有多于 2 个子节点的多叉查树。
B 树中同一键值不会出现多次，要么在叶子节点，要么在内节点上。
与平衡二叉树相比，B 树利用多个分支（平衡二叉树只有两个分支）节点，减少获取记录时所经历的节点数。
B 树也是有缺点的，因为每个节点都包含 key 值和 data 值，因此如果 data 比较大时，每一页存储的 key 会比较少；当数据比较多时，同样会有：“要经历多层节点才能查询在叶子节点的数据”的问题。这时，B+ 树站了出来。
B+树
B+ 树是 B 树的变体，定义基本与 B 树一致，与 B 树的不同点：
- 所有叶子节点中包含了全部关键字的信息
- 各叶子节点用指针进行连接
- 非叶子节点上只存储 key 的信息，这样相对 B 树，可以增加每一页中存储 key 的数量。
- B 树是纵向扩展，最终变成一个“瘦高个”，而 B+ 树是横向扩展的，最终会变成一个“矮胖子”。
B 树的结构最大的区别就是： 它的键一定会出现在叶子节点上，同时也有可能在非叶子节点中重复出现。而 B 树中同一键值不会出现多次。
B+树索引
B+ 树索引就是基于 B+ 树发展而来的。在数据库中，B+ 树的高度一般都在 2 ~ 4 层，所以查找某一行数据最多只需要 2 到 4 次 IO。而没索引的情况，需要逐行扫描，明显效率低很多，这也就是为什么添加索引能提高查询速度。
B+ 树索引并不能找到一个给定键值的具体行，B+ 树索引能找到的只是被查找数据行所在的页。然后数据库通过把页读入到缓冲池（buffer pool）中，在内存中通过二分查找法进行查找，得到需要的数据。
聚集索引
InnoDB 的数据是按照主键顺序存放的，而聚集索引就是按照每张表的主键构造一颗 B+ 树，它的叶子节点存放的是整行数据。
InnoDB 的主键一定是聚集索引。如果没有定义主键，聚集索引可能是第一个不允许为 null 的唯一索引，也有可能是 row id。
由于实际的数据页只能按照一颗 B+ 树进行排序，因此每张表只能有一个聚集索引（TokuDB 引擎除外）。查询优化器倾向于采用聚集索引，因为聚集索引能够在 B+ 树索引的叶子节点上直接找到数据。
聚集索引对于主键的排序查找和范围查找速度非常快。
辅助索引
我们现在知道了聚集索引的叶子节点存放了整行数据，而 InnoDB 存储引擎辅助索引的叶子节点并不会放整行数据，而存放的是键值和主键 ID。
当通过辅助索引来寻找数据时，InnoDB 存储引擎会遍历辅助索引树查找到对应记录的主键，然后通过主键索引来找到对应的行数据。
比如一颗高度为 3 的辅助索引树中查找数据，那需要对这颗辅助索引树遍历 3 次找到指定主键，如果聚集索引树的高度也为 3，那么还需要对聚集索引树进行 3 次查找，最终找到一个完整的行数据所在的页，因此获取数据一共需要6次逻辑 IO 访问。
我们能看出辅助索引的查询比主键查询多扫描一颗索引树，所以，我们应该尽量使用主键做为条件进行查询。
哪些情况需要添加索引？
目前比较常见需要创建索引的场景有：数据检索时在条件字段添加索引、聚合函数对聚合字段添加索引、对排序字段添加索引、为了防止回表添加索引、关联查询在关联字段添加索引等。
数据索引
因此建议数据检索时，在条件字段添加索引。
聚合函数
显然索引能提升 max() 函数的效率，同理也能提升 min()函数的效率。
因此索引对聚合函数 count(*) 也有优化作用。
排序
如果对单个字段排序，则可以在这个排序字段上添加索引来优化排序语句；
如果是多个字段排序，可以在多个排序字段上添加联合索引来优化排序语句；
如果是先等值查询再排序，可以通过在条件字段和排序字段添加联合索引来优化排序语句。
避免回表
像这种索引就已经覆盖了我们的查询需求的场景，我们称为：覆盖索引。
可直接通过联合索引 idx_b_c 找到 b、c 的值。
所以可以通过添加覆盖索引让 SQL 不需要回表，从而减少树的搜索次数，让查询更快地返回结果。
关联查询
通过在关联字段添加索引。
普通索引和唯一索引
- 有普通索引的字段可以写入重复的值，而有唯一索引的字段不可以写入重复的值。
- 数据修改时，普通索引优于唯一索引，因为普通索引可以用 Change Buffer，并且 RR 隔离级别下，出现死锁的概率比唯一索引低。
- 查询数据时，两者性能差别不大。
联合索引
key_len可以查用到的索引长度
联合索引的键值数量大于 1（比如上图中有 a 和 b 两个键值），与单个键值的 B+ 树一样，也是按照键值排序的。
联合索引的建议：
- where 条件中，经常同时出现的列放在联合索引中。
- 把选择性最大的列放在联合索引的最左边。
联合索引使用分析
可以完整用到联合索引的情况：
a,b,c联合索引
- c=1 and b=1 and a=1
- a=2 and b in (1,2) and c=2
- a = 1 and b =2 order by c
- a = 1 order by b,c
- order by a,b,c
只能使用部分联合索引的情况
a = 1 and b = 1
a = 1 and c = 1
联合索引 idx_a_b_c(a,b,c) 相当于 (a) 、(a,b) 、(a,b,c) 三种索引，称为联合索引的最左原则。
a =2 and b in (3,4) order by c
当联合索引前面的字段使用了范围查询，对后面的字段排序使用不了索引排序，也就是只能用到联合索引前面两个字段 a 和 b 的索引。
可以用到覆盖索引的情况
什么是覆盖索引？ 从辅助索引中就可以查询到结果，不需要回表查询聚集索引中的记录。
使用覆盖索引的优势：因为不需要扫描聚集索引，因此可以减少 SQL 执行过程的 IO 次数。
select b,c from t11 where a=3;
select c from t11 where a=1 and b=1 ;
select id from t11 where a=1 and b=1 and c=1;
不能使用联合索引的情况
select * from t11 where b=1;
select * from t11 order by b;
select * from t11 where b=1 and c=1;
explain select * from t11 where a=1 and b>1 and c=1; 走索引，走前两个
explain select * from t11 where a>1 and b>1 and c=1; 不走索引
explain select * from t11 where a<1 and b>2 and c=1; 走索引，走第一个
为什么MySQL会选错索引
show index 的使用
Cardinality 取值
Cardinality 表示该索引不重复记录数量的预估值。如果该值比较小，那就应该考虑是否还有必要创建这个索引。比如性别这种类型的字段，即使加了索引，Cardinality 值比较小，使用性别做条件查询数据时，可能根本用不到已经添加的索引（可以参考第 3 节的第 4 部分：范围查询）。
统计信息不准确导致选错索引
在 MySQL 中，优化器控制着索引的选择。一般情况下，优化器会考虑扫描行数、是否使用临时表、是否排序等因素，然后选择一个最优方案去执行 SQL 语句。
而 MySQL 中扫描行数并不会每次执行语句都去计算一次，因为每次都去计算，数据库压力太大了。实际情况是通过统计信息来预估扫描行数。这个统计信息就可以看成 show index 中的 Cardinality。
而从上面说到 Cardinality 的更新原理可以看出，它的值不一定准确的，因此有时可能就是因为它的值不精准导致选错了索引。这种情况可以使用下面的命令重新统计信息：
analyze table t13;
单次选取的数据量过大导致选错索引
因此这条 sql 可以使用 force index 来强制走索引 key_b，sql 如下：
select a from t13 force index(idx_a) where a>70000 limit 1000;
参考资料：
- 《高性能 MySQL》
