一、 引言
概要
(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) 什么时候不要使用索引?
经常增删改的列不要建立索引.
有大量重复的列不建立索引.
表记录太少不要建立索引;
...