MySql 技术内幕 (第9章 索引)
9.1 缓冲池、顺序读取与随机读取
数据库一般需要持久化，持久化就要和磁盘打交道，因此就会出现访问磁盘的操作。随机访问磁盘是比较慢的，顺序访问会快很多。所以现在基本都有缓冲池的存在。
缓冲池作为内存和磁盘的中间件，主要是缓存以页的方式缓存数据页，查询和修改时会先在缓存查看有没有，有就命中，效率就很高了。或者没有的话，先把数据页调出到缓存池中，再修改数据页，然后异步写入磁盘持久化。
顺序读取: 顺序地读取磁盘的页;
随机读取: 访问的页是不连续的,需要磁头不断移动;
9.2 数据结构与算法
B+树索引最为常见;
9.2.1 二分查找法
B+树索引只能找到某条记录所在的页,需要根据二分查找法来进一步找到记录所在页的具体位置;
9.2.2 二叉查找树和平衡二叉树
二叉查找树 中序遍历得到值是排序的;
平衡二叉树查找效率高(任意节点两棵子树节点高度最大差为1);
9.3 B+树
B+树的精简介绍: B+树中,所有记录节点都是按键值的大小顺序存放在同一层的叶子节点,各个子节点通过指针进行链接。
9.3.1 B+树的插入操作
对mysql的B+数的插入和删除操作会引起分裂和合并的产生，主要是为了维护B+索引树的平衡，不过有些时候可以通过左旋右旋来避免分裂合并。
B+树保持平衡,插入新值后可能要做大量的拆分页操作(磁盘操作), 为此B+树提供了旋转功能;
9.3.2 B+树的删除操作
使用填充因子控制树的删除变化,最小值是50%;
9.4 B+树索引
索引是存储引擎层面实现的，所以不同的存储引擎底层的数据结构可能是不一样的。
b+树索引在数据中一个特点就是高扇出性,所以查找速度是很快的。,
例如Innodb中,每个页的大小为16KB;
数据库中,B+树高度一般都在2~4层, 这意味着查找某一键值最多只需要2-4次IO操作;
9.4.1 InnoDB B+树索引
Innodb会自动创建一个6字节的列作为主键;
B+树索引分为：聚集索引和辅助索引。区别在于存放的数据内容。
聚集索引：根据主键创建的B+树，聚集索引的叶子存放的是表中的真实数据。非叶子节点存放的是目录项数据（页号等）。
辅助索引：根据索引创建的B+树。叶子节点只存放索引键值和其指向的主键。根据辅助索引查找时需要回表操作。可以存放更多键值，高度一般小于聚集索引。 (书签查找)
9.4.2 MyISAM B+树索引
所有行数据存放在MYD文件中,
其B+树索引都是辅助索引,存放于MYI文件中;
primary key 索引唯一且不能为null,其索引页的大小默认为1KB, 同样不可以进行调整;
与innodb不同,因为没有聚集索引,其索引叶节点存放的键值不是主键值,而是MYD文件中的物理位置;
9.5 Cardinality
9.5.1 什么是Cardinality
什么时候需要添加B+树索引，一般经验是在访问表很少部分行时使用才有意义。高选择性的字段才有意义。 比如性别,地区字段,类型字段,可取值范围小,称为低选择性;
通过show index； 查看Cardinality列的值来判断是否要加对这一列加索引。
9.5.2 InnoDB存储引擎怎样统计Cardinality
Cardinality的统计是在存储引擎层实现的。因为每个存储引擎对B+树的实现是不同的。
Cardinality的统计一般是通过采样完成的。因为Cardinality的统计发生在insert和update操作，然而这两个操作是很频繁的，所以不会实时的更新的。
Innodb对于更新Cardinality信息的策略为:
-
表中1/16的数据已发生变化
-
stat_modified_counter >2 000 000 000
stat_modified_counter 是Innodb内部的计数器,表示发生变化的次数;
采样过程：
InnoDB存储引擎每次随机选择8个叶节点进行采样。所以每次查看Cardinality值可能是不相同的。
-
取得b+树索引中叶节点的数量,即为 A
-
随机取B+树索引中的8个叶节点, 统计每个页不同记录的个数, 即P1 ,P2 ....P8
-
根据采样信息给出Cardinality预估值: Cardinality = (P1+P2+...+P8) * A / 8
9.6 B+树索引的使用
9.6.1 不同应用中B+树索引的使用
别盲目听从，研究业务确定是否需要索引，对哪些列做索引。
9.6.2 联合索引
联合索引的键数量是多个，不是一个。
联合索引本质上还是一颗B+树;
比如联合索引(a,b) ,
如果查询条件 a=xxx and b=xxx 可以使用这个索引;
查询条件 a= xxx 也可以使用这个索引;
查询条件 b=xxx 不可以使用B+树索引,因为叶子节点上,1,2,1,4,1,2 不是顺序的;
联合索引的另一个好处是可以对第二个键的排列(无需再对第二列做一次额外的排序操作)，所以有时候可以提高查询效率。
9.6.3 覆盖索引
覆盖索引：从辅助索引就可以等到查询的记录，而不需要回表操作。大量减少了IO操作。
一般来说，对于诸如（a，b）这类联合索引，一般不可以选择b列来进行查询，但是在统计操作，如果是覆盖索引，优化器会优先选择。
9.6.4 优化器选择不使用索引的情况
某些情况优化器并没有选择索引去查找数据,而是全表扫描聚集索引,也就是全表扫描来得到数据, 这种情况多发生于范围查找,join操作;
原因： 选取的是整行信息，而覆盖索引是没有包含全部数据信息的，所以只能走全表扫描了。
如果确认使用辅助索引可以带来更好的性能可以使用 FORCE INDEX 。
9.6.5 INDEX HINT
mysql支持INDEX HINT（显式告诉优化选择指定索引）。两种情况需要：
-
mysql数据库的优化器错误地选择了某个索引,导致SQL语句运行得很慢;
-
某个SQL语句可以选择的索引非常多,这时优化器选择执行计划时间的开销可能会大于SQL语句本身;
DBA或者开发人员分析最优的索引选择,通过INDEX HINT来强制优化器不进行各种执行路径的成本分析,直接执行选择指定的索引来完成查询;
explain select * from t2 USE INDEX(a) where a=1 and b=2 ; explain select * from t2 FORCE INDEX(a) where a=1 and b=2 ;
-
USING INDEX 只是告诉优化器选择指定的索引，优化器不一定真的会选择。
-
FORCE INDEX 是强制优化器选择指定的索引。
9.7 Multi-Range Read (MRR优化)
mysql5.6后才有的。
MRR优化的目的是: 减少磁盘的随机访问，并且将随机访问转化为顺序访问。
MRR优化的好处:
-
使得数据访问变为顺序; 在查询辅助索引时,先对得到的查询结果按照主键进行排序,并按照主键排列的顺序进行书签查找;
-
减少缓冲池页被替换的次数 ;
-
批量处理对键值的查询操作
对于InnoDB和MyISAM的范围查询和联接查询, MRR工作方式如下:
-
将查询得到的辅助索引键值存放于一个缓存中,这是缓存中的数据是根据辅助索引键值排序;
-
将缓存中的键值根据ROWID进行排序
-
根据rowId的排序顺序来访问实际的数据文件
MRR参数 : optimizer_switch ;
键值缓冲区大小参数 : read_rnd_buffer_size ;
9.8 Index Condition Pushdown（ICP）优化
mysql5.6后才有的。
ICP是一种根据索引来查询的优化方式;
支持ICP后,mysql会取出索引同时,判断是否可以进行where 条件过滤, 即 将where的部分过滤操作放在存储引擎层; (推送的索引条件)
在某些查询过程中,ICP 会大大减少上层SQL层对于记录的索取(fetch),从而提高数据库的整体性能;
优化器使用ICP时，server层将会把能够通过使用索引进行评估的where条件下推到storage engine层
1) storage engine从索引中读取下一条索引元组。
2) storage engine使用索引元组评估下推的索引条件。如果没有满足wehere条件，storage engine将会处理下一条索引元组（回到上一步）。只有当索引元组满足下推的索引条件的时候，才会继续去基表中读取数据
3) 如果满足下推的索引条件，storage engine通过索引元组定位基表的行和读取整行数据并返回给server层。
9.9 T树索引
9.9.1 T树概述
从mysql5.1开始 NDB Cluster开始使用T树。
T树不将真实数据放在节点，只是存放数据的指针。
T树节点由3个指针,一个有序数组, 以及控制信息组成;
3个指针分别只想指向父节点和左右子树的指针 ;
有序数组保存的是数据指针,而非实际数据;
数组中的第一个元素数据 称为最小元素,最后一个数据称为最大元素;
控制信息中存放了关于该T树节点的一些额外信息;
在T树中,含有左右子树 的节点称为内部节点;
没有子树的节点称为 叶节点 ;
仅有一个子树的 称为 半叶节点 ;
每个内部节点存在一个相对应的叶节点或者半叶节点存放内部节点的最小值,称为最大下界;
同时存在一个用于存放内部节点的最大值称为,最小上界;
9.9.2 T树的查找、插入和删除操作
T树的查找和二叉查找树类似,其查找算法为 :
-
从根节点root 开始查找,比较节点中的最大值和最小值,如果查找的值在边界内,则利用二叉法查找T树 中的数组;
-
若查找的值比根节点的最小值小,则递归查找左子树
-
若查找的值比根节点的最大值大,则递归查找右子树
-
若不存在该值,返回null
T树的插入操作:
先通过查找来定位边界页;
新插入的值被插入边界页后需要对T树进行检查是否平衡;
如果不平衡 ,则需要通过旋转来保证T树的平衡;
由于T树的节点可以存放多个值,因此其旋转的次数, 比平衡二叉树又减少了很多;
T树的插入算法:
-
查找边界页
-
如果查到边界页,则判断是否有空间插入新的值, 如果有直接插入,插入操作完成;
如果没有空间,则删除该节点的最小值,插入新值;
接着查询原插入值最大下界所在的节点,将删除的值插入该节点,并成为新的最大下界值;
-
如果没有查到边界页, 那么在最后查询的节点中尝试插入记录;
若该节点有空间可以插入,则直接插入 ,并且该记录成为该节点的最小或最大记录值 ;
若没有空间插入,则分配一个叶节点进行插入;
-
若果分配了一个叶节点,则需要检查T树是否平衡 ;
如果不平衡则需要进行与平衡二叉树同样的旋转操作, 以保证T树重新回到平衡状态, 然后此次操作完成;
T树的删除类似插入操作:
首先需要找到边界页,然后完成删除操作,最后判断是否需要旋转来完成平衡操作;
-
查找删除记录所在边界页, 如果查找失败,则报告错误停止操作;
-
如果删除不会引起下溢出问题,则删除该记录后,节点中的记录数＞ 所要求该节点的最小记录数,那么直接删除记录;
否则,如果节点是内部节点,那么删除该记录,同时将该节点的最大下界值放回该内部节点中;
如果节点是叶节点或半叶节点,则直接删除记录(叶节点允许下溢出,半叶节点还需要进行下一步操作)
-
如果是半叶节点,则观察半叶节点是否可以和叶节点进行合并,如果可以则强制将两个节点合并为一个节点并删除半叶节点
-
观察删除后的节点是否是空节点,若是 则删除该节点
-
观察T树是否平衡,若不平衡则需要通过旋转操作使其重新平衡;
9.9.3 T树的旋转
插入操作,只需要进行一次旋转操作 , 对于删除操作,可能需要进行多次的旋转操作;
T树有一种特殊的旋转....
9.10 哈希索引
InnoDB存储引擎只支持自适应哈希索引（不可以人工干预，对字典类型的查找速度超快），Memory存储引擎支持哈希索引。
哈希函数是关键。
发生碰撞，解决办法：
-
链接法
-
开放式向前探索法（1步跳，迭代n步跳）
查看自适应哈希索引的情况：
9.10.1 散列表
由直接寻址表改进而来;
全域 U = {0, 1, ....m-1} 中存关键字;
用一个数组(即直接寻址表) T [0....m-1] 表示动态集合, 其中每个位置(称槽或桶) 对应全域U中一个关键字 ;
槽K指向 集合中一个关键字为K的元素,如果没有则T[K]=NULL;
利用散列函数 h , 根据关键字K计算出槽的位置;
不过这样有一个问题就是 两个关键字可能映射到同一个槽上(碰撞) ;
数据库中一般采用最简单的解决方法,即链接法;
链接法中,将同一个槽中的所有元素放在一个链表中;
9.10.2 InnoDB存储引擎中的散列算法
Innodb使用散列算法对字典进行查找;
冲突机制采用链表方式;
散列函数采用除法散列方式;
对于缓冲池页的散列来说,
缓冲池中一个page页都有一个chain指针,指向相同散列函数值的页;
比如当前参数innodb_buffer_pool_size 大小为10MB,则 共有 640x16KB 的页, 对于缓冲池页内存的散列表来说,则需要分配 640*2=1280 个槽,(略大于2倍缓冲页数量的质数), 取比1280略大的质数1399, 则启动时会分配1399个槽的散列表,用来散列查询所在缓冲池中的页;
将要查找的页转换成自然数;
Innodb的表空间都有一个space号, 我们需要查找的应该是某个表空间的某个联系的16Kb的页, 即偏移量offset, Innodb将space左移20位,然后加上这个space和offset,
即 关键字 K=space <<20 +space +offset 然后通过除法散列到各个槽中去;
9.10.3 自适应哈希索引
有innodb自己控制,不受DBA或开发人员控制; 不过可以通过参数开启或禁用此特性;
查看自适应哈希索引的情况：
show engine innodb status ;
自适应哈希索引经散列函数映射到一个散列表中, 对于 where index_col = 'xxx' 这种字典类型查找非常快速;
哈希索引只能是等值查询。因为哈希函数映射后就是一个值，就是通过比较值来得到对应的槽。
范围查找是不能使用自适应哈希索引;
9.11 小结
其他
mysql -- show index from tablename 各列解释
show index from table_name
这个命令有助于诊断性能低下的查询，尤其是查询是否使用了可用的索引。
下面介绍下 这个命令显示的结果列的含义：
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
1.Table 表的名称。
2.Non_unique 如果索引不能包括重复词，则为0。如果可以，则为1。
3.Key_name 索引的名称。
4.Seq_in_index 索引中的列序列号，从1开始。
5.Column_name 列名称。
6.Collation 列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。
7.Cardinality
索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
8.Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
9.Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。
10.Null 如果列含有NULL，则含有YES。如果没有，则该列含有NO。
11.Index_type 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
12.Comment 多种评注。
参考: https://blog.csdn.net/javamoo/article/details/70184088
