MySQL-基础5-索引与算法

怎么判断索引是否属于高选择性呢，可以用show index结果中的Cardinality列来观察：Cardinality表示索引中不重复记录数量的预估值，Cardinality/n_rows_in_table应尽可能接近1，就代表数据量虽然大，但里面的值普遍具有唯一性，具有类似于主键的效果，因此在访问高选择性属性的字段并从表中取出很少一部分数据时，对这个字段添加B+树索引是有必要的；

InnoDB引擎的Cardinality统计

MySQL有各种不同的存储引擎，不同存储引擎对B+树索引的底层实现各不相同，因此Carinality统计是存储引擎层面上实现的；

如果每进行一次insert就统计一次Cardinality明显不太可能；等数据表达到一定量后再去Cardinality也不太可能，因此，对于Cardinality统计都是通过采样方法来完成；

InnoDB引擎中，当insert和update时就会进行Cardinality统计，但不是每次insert和update都会统计，理由上面也说了，具体Cardinality策略为：

①表中1/16的数据已发生变化：自从上次Cardinality统计信息后，表中1/16的数据量已经发生过变化，这时再去更新Cardinality信息；

②stat_modified_counter>2000000000：如果对表中某一行数据频繁update操作，虽然表中数据量没变，但实际还是发生了某一行变化，此刻第1种策略就无法适应，所以InnoDB内部有一个计数器stat_modified_counter来表示发生变化的次数，当该计数器大于2000000000时同样也更新Cardinality信息；

那InnoDB是如何进行Cardinality信息统计和更新的呢，采样过程如下：

①默认InnoDB对随机8个叶子节点进行采样；

②取得B+树索引中，叶子节点的数量，记为A；

③随机取得B+树索引中的8个叶子节点，统计每个页不同记录的个数，分别记为P1-P8；（每个索引中随机抽8个叶子节点）

④根据采样信息给出Cardinality的预估值：Cardinality=(P1+P2....+P8) *A/8；

InnoDB 1.2之前，innodb_stats_sample_pages设置Cardinality每次采样页的数量，默认值为8，innodb_stats_method设置对待null值的处理方法；

执行analyze table/show table status/show index/访问information_schema.tables/访问information_schema.statistics时会导致InnoDB重新采样Cardinality值，因此，如果表中数据量非常大且存在多个辅助索引时执行上述操作会变得非常慢；

InnoDB 1.2版本开始提供了更多参数来对Cardinality进行更细致设置，例如：

innodb_stats_persistent:是否将命令analyze table计算的Cardinality值存储到磁盘，如果是则可以减少重新计算每个索引的Cardinality值，默认值OFF；

innodb_stats_on_metadata:通过show table status/show index/访问information_schema.tables/访问information_schema.statistics时，是否重新计算索引的Cardinality值；

innodb_stats_persistent_sample_pages:若innodb_stats_persistent=ON,该参数表示analyze table更新Cardinality值时每次采样的页数量，默认值为20；

innodb_stats_transient_sample_pages:取代之前innodb_stats_sample_pages，表示每次采样页的数量，默认值为8；

B+树索引的使用

联合索引

联合索引指的是对表上的多个列进行索引，创建联合索引与单个索引方法一致，不同之处在于有多个索引列；

联合索引也是一颗B+树，只不过里面的键值数量大于等于2，联合索引的另外好处是已经对第二个键值进行了排序处理；

覆盖索引

从辅助索引中就得到查询的记录，不需要查询聚集索引中的记录，使用覆盖索引的一个好处就是辅助索引不包含整行记录的所有信息，因此空间使用上小于聚集索引，减少IO操作；

优化器选择不使用索引的情况

MySQL优化器没有选择索引，而是通过扫描聚集索引，即全表扫描来得到数据，这种情况多发生于：范围查找、join连接等操作；

select * from users where age > 25 and age < 35;

这里用的是select *，因此，你通过age这个辅助索引无法得到所有的列，只能用辅助索引找到聚集索引，再从聚集索引的bookmark定位到整行数据，虽然索引中数据是顺序存放的，但bookmark中的磁盘地址则是无序的，需要到磁盘进行离散读操作，当select *的数据量达到表中20%的时候，优化器会选择通过聚集索引来查找数据，即全表扫描，因为这20%的数据量的顺序读要快于离散读；

对于某些列没有添加索引，但select的时候用到，如果你有足够自信确认使用辅助索引能带来更好的性能，则可以使用force index来强制使用某个索引；

索引提示

MySQL支持索引提示index hint来显式告诉优化器使用哪个索引，可以考虑在以下2种情况下使用index hint：

①MySQL数据库的优化器错误的选择了某个索引导致SQL语句运行慢，虽然这种情况非常少见；

②某SQL语句可以选择的索引非常多，多到优化器执行计划时间开销大于SQL语句本身；

Multi-range read优化

MySQL 5.6开始支持multi-range read(MRR)优化，来减少磁盘的随机访问，并且将随机访问转化为较为顺序的数据访问，对于io-bound类型的SQL语句带来性能提升，其适用于range，ref，eq_ref类型的查询；

是否启用MRR，可以通过optimizer_switch中的flag来控制，当mrr=on时表示启动MRR，mrr_cost_based表示是否通过cost_based方式来选择是否启用mrr，如果mrr设为on，mrr_cost_base=off，则总是启动MRR优化；

Index condition pushdown(ICP)优化

与MRR一样，ICP也是MySQL 5.6开始支持的一种根据索引进行查询的优化方式；

优化之前，当进行索引查询时，首先根据索引来查找记录，然后再根据where条件来过滤记录，当使用ICP之后，MySQL取出索引的同时，判断是否可以进行where条件的过滤，即把where部分过滤操作直接放到存储引擎层上，从而减少SQL层对记录的索取，从而提高效率；

ICP支持range/ref/eq_ref/ref_or_null类型查询，当优化器选择ICP优化时，在执行计划的extra列能看到using index condition提示；

全文检索

概述

全文检索是将存储于数据库中的整本书或整篇文章中的任意内容信息查找出来的技术，它可以根据需要获得全文中有关章、节、段、句、词等信息，也可以进行各种统计和分析；

从InnoDB 1.2.x版本开始，InnoDB引擎正式开始支持全文检索；MySQL全文检索的语法为：match

扩展：AVL树

定义

它或者是一颗空树，或者具有以下性质的二叉排序树：它的左子树和右子树的深度之差（平衡因子）的绝对值不超过1，并且它的左子树和右子树都是一颗平衡二叉树；

通俗来讲就2个要点：

要点1：必须是二叉查找树

要点2：每个节点的左子树与右子树的高度差，不超过1

AVL树相关概念

平衡因子：将二叉树上节点的左子树高度减去右子树高度的值，称为该节点的平衡因子balance factor简称BF；

注意：必须是左减去右，并且BF取值范围在[-1,1]之间，否则就要调整下树；

最小不平衡子树：距离插入节点最近，且平衡因子的绝对值大于1的节点为根的子树；通俗来讲就是：插入操作完毕后，距离插入节点最近，且与插入节点的BF绝对值大于1的节点，那么以该节点为根的数，就是最小不平衡子树；

AVL树的平衡调整

插入元素后，如果出现不平衡，则需要根据新插入的节点与最低不平衡节点的位置关系，进行相应调整，对应的场景分为：LL、RR、LR、RL；

LL型调整（左左）

场景：LL指的是在根节点的左孩子(L)的左子树(L)插入新节点，导致原来的平衡二叉树变得不平衡；

解决方法：

①将A的左孩子B提升为新的根节点；

②将原来的根节点A降为B的右孩子；

③各个子树按大小关系连接，BL和AR不变，BR调整为A的左子树，这是因为BR肯定小于B，B肯定小于A，所以，BR会在A的左侧；

RR型调整（右右）

场景：RR指的是在根节点的右孩子(R)的右子树(R)插入新节点，导致原来的平衡二叉树变得不平衡；

解决方法：

①将A的右孩子B提升为新的根节点；

②将原先的根节点A降为B的左孩子；

③各个子树按大小关系连接，其中AL和BR不变，BL调整为A的右子树，这是因为A肯定小于B，所以，BL会在A的右侧；

LR型调整（左右）

场景：LR指的是在根节点的左孩子(L)的右子树(R)插入新节点，导致原来的平衡二叉树变得不平衡；

解决方法：

①将B的右子树C提升为新的根节点；

②将原来的根节点A降为C的右孩子；

③各个子树按大小关系连接，BL和AR不变，CL为B的右子树，CR为A的左子树，这是因为CL肯定大于B，因为CL > C > B，而CR必须小于A；

RL型调整（右左）

场景：RL指的是在根节点的右孩子(R)的左子树(L)插入新节点，导致原来的平衡二叉树变得不平衡；

解决方法：

①将B的左子树C提升为新的根节点；

②原先的根节点A降为C的左孩子；

③各个子树按大小关系连接，AL和BR不变，CL为A的右子树，CR为B的左子树；

总结

LL是原先根节点向右移动；

RR是原先根节点向左移动；

LR和RL是把新插入的节点，放到原先根节点与左孩子/右孩子之间；

扩展：B树

概念

B树和平衡二叉树稍有不同的是B树属于多叉树又名平衡多路查找树（查找路径不只两个），数据库索引技术里大量使用者B树和B+树的数据结构；

特点

①树中的每个节点最多拥有m个子节点且m>=2,空树除外；

②除根节点外每个节点的关键字数量大于等于ceil(m/2)-1个小于等于m-1个;（注：ceil()是个朝正无穷方向取整的函数如ceil(1.1)结果为2)

③所有叶子节点均在同一层、叶子节点除了包含了关键字和关键字记录的指针外，也有指向其子节点的指针，只不过其指针地址都为null对应最后一层节点的空格子

④如果一个非叶节点有N个子节点，则该节点的关键字数等于N-1;

⑤所有节点关键字是按递增次序排列，并遵循左小右大原则；

总结

B树相对于平衡二叉树的不同是，每个节点包含的关键字增多了，特别是在B树应用到数据库中的时候，数据库充分利用了磁盘块的原理（磁盘数据存储是采用块的形式存储的，每个块的大小一般为4K，每次IO进行数据读取时，同一个磁盘块的数据可以一次性读取出来）把节点大小限制和充分使用在磁盘快大小范围；把树的节点关键字增多后树的层级比原来的二叉树少了，减少数据查找的次数和复杂度;