(转) Mysql 索引

索引为什么采用 B+ tree

1. Hash不支持范围查询

2. 二叉树树高很高，只有B树跟B+有的一比。

3. B树一个节点可以存储多个元素，相对于完全平衡二叉树整体的树高降低了，磁盘IO效率提高了。

4. 而B+树是B树的升级版，只是把非叶子节点冗余一下，这么做的好处是为了提高范围查找的效率。提高了的原因也无非是会有指针指向下一个节点的叶子节点。

explain

索引命中

id is null 和 id = 1 都能命中索引 （除此之外的大多都不能命中索引，比如 负向 != 比较 > < 范围 between 等） 但是 id is null or id = 1 还是触发 全表扫描(type=all
) 但如果 or 改为 union 那么又能命中索引了

各字段解释

1. explain: type (the join type) 常见值

   • system：系统表或常量表
     • 比如从系统库mysql的系统表time_zone里查询数据，这些数据已经加载到内存里，不需要进行磁盘IO；
     • explain select * from (select * from user where id=1) tmp; 以及这种 子层 const 返回后对外层而言也是 system
   • const：常量连接；
     • 命中主键(primary key)或者唯一(unique)索引；
     • 被连接的部分是一个常量(const)值；
   • eq_ref：主键索引(primary key)或者非空唯一索引(unique not null)等值扫描；
   • ref：非主键非唯一索引等值扫描；
   • range：范围扫描；
   • index：索引树扫描；
   • all：全表扫描(full table scan)；

2. B + 索引如何加速查询

   1. 表在创建时一定会组织一次聚簇索引，可能时根据主键，没有的话看唯一键，还没有的话就自己生成个rowid，无论如何都会组织聚簇索引（其B+ 的叶子上对应着行的完整数据），这是其他索引关联全量查的基本。

   2. 现在假设说根据其他字段键一个索引，那么就会基于该索引字段创建一个新的B+树，其非叶子节点上仅包含索引字段，而其叶子节点上会包索引字段和前面提到的能够关联聚簇索引的字段，这样走完其他索引却也想要查全量数据的时候就会先去基于这个仅包含索引字段与关联字段数据（行信息不完整）的叶子节点定位一次，然后关联到聚簇索引上拿到全量的数据。这种索引称为二级索引，也叫辅助索引。

   3. 复合索引，索引涉及多个字段，则会按照索引建立时字段的顺序进行先后排序，也就是前面的字段相同时，会按照后面的字段进行排序，其他和上面大致相同，也就是非叶子节点包含索引字段，叶子节点包含索引字段和聚簇索引的关联字段。

      

      • 这里来推下最左前缀的原理：有 index(a,b)， sql select * from t where b = 3，显然不走索引，因为按照 a 先排序后的 b是分散在不同的索引节点上的，只能跑全索引(type=index)搜。

      • 其实这还能解释为啥 like % 前缀不行，因为排序是按前缀进行字典序排序的，% 不放在前面才能按序查找

      • 这样看来其实只将B+树看作一种加快搜索的二分算法，那么索引其实还是相当于一个组织多字段排序数组的结果。

索引基本数据结构

页

1. 页可以组织成双向链表
2. 每个页中的记录可以组织成单向链表
3. 记录有对应的页目录（二分查找）作为索引，方便加快速度定位
4. 以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录

索引使用

什么是三星索引

第一颗星：WHERE 后面参与查询的列可以组成了单列索引或联合索引

第二颗星：避免排序，即如果 SQL 语句中出现 order by colulmn，那么取出的结果集就已经是按照 column 排序好的，不需要再生成临时表

第三颗星：SELECT 对应的列应该尽量是索引列，即尽量避免回表查询。