Mysql innodb索引与查询优化
一、 引言
概要
(1) innodb索引类型与B+树
(2) 索引使用注意事项
(3) 车点评业务表索引优化案例
问题
(1) InnoDb索引为什么要用b+树？
(2) 聚集索引与普通索引有什么区别？
(3) 为什么主键最好是自增？
(4) 组合索引有什么优势？
(5) 有哪些实际业务场景索引搞不定？
二、 innodb索引类型与B+树
INNODB主要有几种索引：B+树索引，自适应哈希索引，全文索引 ，覆盖索引。
B+树索引是一种多叉平衡查找树。
自适应哈希索引是mysql对于频繁查询的数据采取的hash存储优化，用户无法选择设置。
全文索引主要用于全文检索使用。
覆盖索引说的是查询的字段都属于同一个索引字段，这样效率非常高。
查找树
查找树主要有：二叉查找树（Binary Search Tree），平衡二叉查找树（Balanced Binary Search Tree），红黑树(Red-Black Tree )，B-tree/B+-tree/ B*-tree (B~Tree)。前三者是典型的二叉查找树结构，其查找的时间复杂度O(log2N)与树的深度相关，那么降低树的深度自然会提高查找效率。
B树/B+树/B*树
B树
B+树
所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接。
数据库索引采用B+树的主要原因是: B树在提高了磁盘IO性能的同时并没有解决元素遍历的效率低下的问题。正是为了解决这个问题，B+树应运而生。B+树只要遍历叶子节点就可以实现整棵树的遍历。而且在数据库中基于范围的查询是非常频繁的，而B树不支持这样的操作（或者说效率太低）。
B*树
和B+树的主要区别：
1、B*树中非根和非叶子结点都有指向兄弟的指针；
2、B*树定义了叶子结点关键字个数至少为(2/3)*M，即块的最低使用率为2/3（代替B+树的1/2）
聚集索引和普通索引
1、主键和普通索引都是B+树索引
2、主键的叶子节点是：数据；普通索引的叶子节点是：主键的值
3、普通索引的检索需要经过两次B+树查找：
I）通过普通索引，找到：主键key
II）通过主键key，查找到元素
自适应hash索引
Innodb存储引擎会监控对表上二级索引的查找，如果发现某二级索引被频繁访问，二级索引成为热数据，建立哈希索引可以带来速度的提升。经常访问的二级索引数据会自动被生成到hash索引里面去(最近连续被访问三次的数据)，自适应哈希索引通过缓冲池的B+树构造而来，因此建立的速度很快。
自适应hash索引只适用于等值查询。
INNODB如何建立聚集索引
1）有主键，则INNODB使用它作为聚集索引。
2）未定义主键，INNODB选第一个非NULL的唯一索引列，使用它作为聚集索引。
3）如果1）、2）都没有，Mysql自动添加一个不可见不可引用的6byte大小的rowid作为聚集索引.
主键必须是自增
如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页
如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。
普通索引的叶子节点内容
存储的是主键的值。好处如下
辅助索引使用主键作为"指针" 而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作，使用主键值当作指针会让辅助索引占用更多的空间，换来的好处是InnoDB在移动行时无须更新辅助索引中的这个"指针"。
普通索引的叶子节点空洞
B*树定义了叶子结点关键字个数至少为(2/3)*M，即块的最低使用率为2/3
B+树的最低使用率是1/2,这是由树的分裂算法决定的。
B+树的分裂：当一个结点满时，分配一个新的结点，并将原结点中1/2的数据复制到新结点，最后在父结点中增加新结点的指针；
B*树的分裂：当一个结点满时，如果它的下一个兄弟结点未满，那么将一部分数据移到兄弟结点中，再在原结点插入关键字，最后修改父结点中兄弟结点的关键字(因为兄弟结点的关键字范围改变了）；
如果兄弟也满了，则在原结点与兄弟结点之间增加新结点，并各复制1/3的数据到新结点，最后在父结点增加新结点的指针；
所以，B*树分配新结点的概率比B+树要低，空间使用率更高；
每一次数据页分裂，都会导致叶子节点空洞的产生。
COUNT查询较慢原因以及优化
count指令实现上采用实时统计方式，要么通过聚集索引统计，要么通过二级索引统计
在无可用的二级索引情况下，执行count会使MySQL扫描全表数据，当数据中存在大字段或字段较多时候，其效率非常低下（每个页只能包含较少的数据条数，需要访问的物理页较多）
二级索引存储的数据为指定字段的值与主键值。当我们通过二级索引统计数据的时候，无需扫描数据文件；
所以，可以建立合适的单子段普通索引，提高COUNT统计效率
组合索引优势
(1) 满足最左前缀的查询，都可以用到索引。
(2) 覆盖索引查询，效率更快。
三、 索引使用注意事项
(1) 尽量选择区分度高的列作为索引。
区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1
(2) 选用自增ID作为主键。
(3) 组合索引的建立需要进行仔细分析；
1)、正确选择组合索引中的主列字段，一般是选择性较好的字段；
2)、组合索引的几个字段是否经常同时以AND方式出现在Where子句中？
如果是，则可以建立复合索引；否则考虑单字段索引；
3)、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
4)、如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
(4) 频繁进行数据操作的表，不要建立太多的索引；
(5) 删除无用的索引，避免对执行计划造成负面影响；
(6) 应尽量避免在WHERE子句中对字段进行NULL值判断，否则将导致引擎放弃使用索引而进行全表扫描。
(7) 字符字段只建前缀索引, 最好不要做主键。
(8) 使用同类型进行比较, 否则不会用到索引。
(9) 尽量避免在WHERE子句中使用!= 或 <>，not like 操作符，否则将引擎放弃使用索引而进行全表扫描。
(10) 索引不会包含有NULL值的列。
(11) 单表索引建议控制在5个以内。
(12) 什么时候不要使用索引？
经常增删改的列不要建立索引.
有大量重复的列不建立索引.
表记录太少不要建立索引；
...
