MYSQL:基础——索引原理
MYSQL:基础——索引原理
什么是索引
B-树
数据库索引是存储在磁盘上的，当数据量比较大的时候，索引的大小将会达到几个G。当我们利用索引查询的时候，无法把整个索引全部加载到内存中。而是逐一加载每一个磁盘页，这里的磁盘页对应索引树的结点。
B-树，一种平衡多路查找树。适用于查找磁盘中的大量数据。为了减少IO次数，B树最明显的特征是“矮胖的”，即深度较小。初次之外，还有如下特征：
- B树每个节点可以有多个子树，M阶B树表示该数每个节点最多有M个子树。
- 根节点至少有两个子树；中间节点都包含k-1个关键字，和k个子树，其中（M/2<=K<=M）。
- 所有的叶子节点都在同一层。
- 每个节点中的元素从小达到排序，节点当中k-1个关键字正好被k个子树包含的元素的值域分划。
注：B-树（中间的不是减），B是Balance的意思。
3阶B-树
如下图所示是一个3阶的B树。
当单一节点中的关键字更多时，查询的比较次数会更多，但是可以减少IO读写次数。在这里我们需要知道的是在内存中的比较耗时机会可以忽略，IO次数足够小，就可以提升查找性能。
B+树
B+树是基于B-树的一种变体，有着比B-树更高的查询性能。B+树具有如下特征：
- 有k个子树的中间节点包含有k个关键字（B树中是k-1个关键字），每个关键字不保存数据，只用来索引，所有数据都保存在叶子节点。
- 所有的叶子结点中包含了全部关键字的信息，及指向含这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。
- 所有的中间节点关键字都同时存在于子节点，在子节点关键字中是最大（或最小）元素。
概念有点晦涩，我们看一个例子。
最明显的特点就是中间节点的关键字全部都会包含在在叶子节点中，所以中间节点的关键字只是做索引，不保存数据。每一个叶子节点都带有指向下一个节点的指针，形成一个有序链表。
快在哪里
B+树的好处主要体现在查询性能上。由于中间节点不保存数据，所以同样大小的磁盘页可容纳更多的关键字。这意味着，数据量相同的情况下，B+树比B树更矮胖，IO查询次数越少。同时B+树查询性能更加稳定，因为B树匹配的关键字可能在叶子节点也可能在中间节点，而B+树一定在叶子节点上。其次在范围查找上，由于叶子节点之间形成有序链表所以速度更快！
通过索引优化慢查询
建索引的原则
- 最左前缀匹配原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
- =和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
- 尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
- 尽量选择区分度高的列作为索引。
- 索引列不能参与计算，保持列“干净”。
慢查询优化实例
如下查询语句
select
count(*)
from
task
where
status=2
and operator_id=20839
and operate_time>1371169729
and operate_time<1371174603
and type=2;
根据最左匹配原则，最开始的sql语句的索引应该是status、operator_id、type、operate_time的联合索引；其中status、operator_id、type的顺序可以颠倒。
但是这只是一种语句，我们其实需要把这个表所有查询都找到，进行综合定夺。
