Mysql索引底层结构解析

我们可以看到这有一个87万的表 没加索引之间执行 SELECt * from t_program WHERe code = ‘1a5712ef6a864ccca9313aedab1d8f01’ 需要5秒左右

但是加上索引之后执行 SELECt * from t_program WHERe code = ‘1a5712ef6a864ccca9313aedab1d8f01’ 速度优化了很多

在揭秘索引的原理之前，我们先来了解什么是索引： 官方定义是： 索引（Index）是帮助MySQL高效获取数据的数据结构，简 单来说，索引是一种数据结构。 按照生活场景来理解，我们以一本中华字典为例，这本字典非常厚，如果我 们要从字典里面找一个汉字是非常耗时的，所以在字典的首页，提供了根据 拼音和偏旁来查找，这个拼音或者偏旁就是这本字典的索引。 除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录 等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出 最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过 同一种查找方式来锁定数据。

其实，数据结构的选择，无非就是时间复杂度的选择。 简单来说，就是通过通过最少的IO次数，从磁盘中检索到目标数据！所以索 引的实现本质上就是一个查找算法。 那么，我们需要找到一个最合适的数据结构来实现快速查找功能，最基本的 查询算法当然是顺序查找（linear search），这种复杂度为O(n)的算法在数 据量很大时显然是不合适的，除了顺序查找，在数据结构中还有很多选择 比如： 1.二叉查找树 2. 平衡二叉查找树 3. 红黑树 4.多路平衡查找树(B树)

基于上述分析的问题，其实有一个核心的点在于树的深度。 有没有其他的数据结构能够减少树的深度？从而降低和磁盘IO的次数呢？ 于是，我们找到另外一种多路平衡查找树，也就是我们熟知的B Tree（B代 表平衡）

B Tree的特点

分叉数（路数）永远比关键字数多1。比如我们画的这 棵树，每个节点存储两个关键字，那么就会有三个指针指向三个子节点（当 然肯定不只存3个这么少）！ 分叉数越多，树的深度就会更少，这样一来，原本高高瘦瘦的二叉树就变成 了矮矮胖胖的样子，如下图所示。

B Tree的查找规则

比如我们要在这张表里面查找15。

1. 15小于17，走左边。
2. 15大于12，走右边。
3. 在磁盘块7里面就找到了15。 20条数据，由于多路数减少了树的深度，所以只有了3次磁盘IO就找到了， 极大的减少了磁盘交互次数

B Tree的工作原理

插入场景

假设在Max Degree(路数)是3的的B Tree中，意味着一个节点只能有3个指 针，所以一个节点只能存储2个key。 假设我们插入1、2、3这三个key，那么这个时候需要通过分裂的方式来保持 B Tree的平衡，那么它的演变过程如下。

节点的排列规则需要满足平衡二叉树的基本规则（左子节点小于父节点，右 子节点大于父节点）！

B Tree的优点和缺点

我们上面描述的B Tree中的节点，在Mysql中代表的是Page页。 前面我们说过，Mysql为了更好的利用磁盘的预读能力，把一个Page页的大 小设置为16k，也就是一个节点（磁盘块）的大小是16K。也就是说在进行一 次磁盘IO时，会把一个节点（16K）的索引加载到内存中。 假设我们设置的一个表的索引字段类型是int，也就是4个字节，如果每个关 键字对应的数据区(data)也是4个字节 在不考虑子节点引用的情况下，那么在B Tree中，每个阶段大概能存储的关 键字数量是： ( 16 * 1024 ) / 4+4 = 2048 个关键字 在B Tree中，一共有2049路。 对于二叉树来说，三层高度最多可以保存7个 关键字，而对于这种2001路的B树，三层高度能够保存的关键字数远远大于 二叉树，因此B Tree用来存储索引非常合适。

缺点

B Tree在做检索时，检索效率非常高，但是在做数据插入和删除时，会破坏 B Three本身的行知，所以为了保持B Tree的平衡，需要对节点进行分裂、 合并、转移等操作，而这个操作在节点数量较多的情况下性能影响较大。 所以这也是为什么我们在做索引的创建和索引更改时，性能较慢的原因！

在Mysql的InnoDB引擎中，并没有使用B Tree作为索引的存储结构，而是使 用B+Tree！它的存储结构如下图所示。

B+Tree的优化

1. B树的路数和关键字的个数的关系不再成立了，数据检索规则采用的是 左闭合区间，路数和关键个数关系为1比1
2. B+Tree的根节点和枝节点中都不会存储数据，只有叶子节点才存储数 据，并且每个叶子节点都会增加一个指针指向响铃的叶子节点，形成一 个有序链表结构。 在这样的几个B+ Tree结构下，假设我们要检索x=1的数据，那么检索的规 则是：
3. 取出跟磁盘块， 加载1/26/66三个关键字。
4. x<=1， 取出磁盘块2，加载1/10/20三个关键字。
5. x<=1， 取出叶子节点，加载1/8/9三个关键字。此时已经达到了叶子 节点，命中1， 所以只需要加载对应1的数据内容（或者内容地址）即 可！

B+Tree和Tree的区别

1. B+Tree 关键字的搜索采用的是左闭合区间，之所以采用左闭合区间是 因为他要最好的去支持自增id，这也是mysql的设计初衷。即，如果id = 1命中，会继续往下查找，直到找到叶子节点中的1。
2. B+Tree 根节点和支节点没有数据区，关键字对应的数据只保存在叶子 节点中。即只有叶子节点中的关键字数据区才会保存真正的数据内容或 者是内容的地址。而在B树种，如果根节点命中，则会直接返回数据。
3. 在B+Tree中，叶子节点不会去保存子节点的引用。
4. B+Tree叶子节点是顺序排列的，并且相邻的节点具有顺序引用的关系， 如上图中叶子节点之间有指针相连接。

Mysql为什么选择B+Tree

1. 它是B Tree的变种，B Tree能解决的问题，它都能解决。B Tree解决的 两大问题是什么？（每个节点存储更多关键字；路数更多）
2. 扫库、扫表能力更强（如果我们要对表进行全表扫描，只需要遍历叶子 节点就可以了，不需要遍历整棵B+Tree拿到所有的数据）
3. B+Tree的磁盘读写能力相对于B Tree来说更强（根节点和枝节点不保存 数据区，所以一个节点可以保存更多的关键字，一次磁盘加载的关键字 更多）
4. 排序能力更强（因为叶子节点上有下一个数据区的指针，数据形成了链 表）
5. 效率更加稳定（B+Tree永远是在叶子节点拿到数据，所以IO次数是稳定 的）

B+Tree作为索引的数据结构带来的好处

由于在B+ Tree中，每个节点不存存储数据区，只需要存储键值+指针，使得 B+ Tree在每个节点存储的路数更多。 假设索引字段+指针大小一共是16个字节，那么一个Page页（一个节点）能 存储1000个这样的单元（键值+指针）。 假设一条记录是16bytes，一个叶子节点（一页）可以存储10条记录！ 当数的深度是2的时候，就有1000^2个叶子节点，可存储的数据为1000 x 1000 x 10 =10000000（千万） 也就是说，在InnoDB中B+树的深度在3层左右，就能满足千万级别的数据存 储。

所谓的聚簇索引，就是只索引键值的逻辑顺序和表数据行的物理存储顺序一 致。只有聚簇索引才会在叶子节点缓存表中的数据。 在InnoDB中，组织数据的方式就是用聚簇索引组织表（Clustered Index Organize Table），所以一张表创建了主键索引，那么这个主键索引就是聚 集索引。

除了主键索引以外，其他索引均属于非聚簇索引，非聚簇索引的叶子节点不 会存储表数据，那么在这种情况下。 如果要查询一个非聚簇索引的字段，怎么去获取到数据的值呢？我们来看一 下非聚簇索引的存储结构。 从上面这个图可以看到，真正的数据仍然是保存到主键索引的叶子节点（这 也就是为什么InnoDB表必须要有主键的原因），而辅助索引的叶子节点的 数据区保存的是主键索引的关键字的值（非主键索引叶子节点的逻辑顺序和 磁盘顺序不一致）。 当我们要查询where name = '王五’时，先通过二级索引去B + Tree中找到 王五的叶子节点，拿到对应的主键值，也就是id=15。 接着在根据这个条件去主键索引中查找到叶子节点拿到数据（这里不能存储 磁盘地址，因为在数据insert和delete时，B+Tree的结构会发生变化）。 所以，从这个角度来说，因为主键索引比二级索引少扫描了一棵B+Tree（这 个动作叫做回表），它的速度相对会快一些。

如果语句是select * from T where k=5，即普通索引查询方式，则需要先搜索k索引树，得到ID 的值为500，再到ID索引树搜索一次。这个过程称为回表 这样会多扫描一颗B+树

索引上的信息足够满足查询请求不需要在回到主键索引上去取数据这种情况可以避免回表提升查询效率 如以下这种案例: select k from T where k=5 此时假如 k是普通索引或者联合索引的话 k能直接获取到数据 就不需要在回表查询

ALTER TABLE t_program ADD INDEX cpid_name_index(cp_id,name);

1.比如name上建立索引 age上没所有 如果where age = and name = 索引会失效 2.遇到范围查询会失效 3.根据 名称和 age 建立索引 ，只要跟在 顺序调换了没有问题 如果只根据名称也没问题 但是如果只根据age 缺少名称就有问题了 4.分组和排序 最好也根据 联合索引的最左前缀

https://blog.csdn.net/taoshihan/article/details/110945156 比如有个联合索引 (b,c,d) 在索引的排序上 , 是先按b排序 , 再按c排序 , 再按d排序

比如有如下数据:

a 是主键 , b c d创建了联合索引 生成的索引结构为: 看最后的叶子节点数据的排序 , 先按第一行b 排序 , 再按第二行 c 排序 , 最后按第三行 d排序 , 紫色部分是主键

查询的时候 , 先按a字段的查询 a相同的再按b的查询 b也相同的再按c的查询

这样的索引构建方式及存储结构，所以联合索引只能从多列索引的第一列开始查找。所以如果你的查找条件不包含b列如(c,d)、©、(d)是无法应用索引的，以及跨列也是无法完全用到索引如(b,d)，只会用到b列索引。

1.如果通过调整顺序 可以少维护一个索引 那么这个顺序往往就是需要优先考虑使用的 2.那么，如果既有联合查询，又有基于a、b各自的查询呢？查询条件里面只有b的语句，是无法使 用(a,b)这个联合索引的，这时候你不得不维护另外一个索引，也就是说你需要同时维护(a,b)、 (b) 这两个索引。

只适应二级索引 减少了访问表的行数 ICP默认开启针对二级索引 只能能把条件下推给存储引擎就会自动下推 在索引遍历过程中，对索引中包含的字段先做判断，过滤掉不符合条件的记录，减少回表次数。

因为索引对于改善查询性能的作用是巨大的，所以我们的目标是尽量使用索 引。

1、在用于where判断order排序和join的（on）、group by的字段上创建索 引 2、索引的个数不要过多。——浪费空间，更新变慢。 3、过长的字段，建立前缀索引 4、区分度低的字段，例如性别，不要建索引。——离散度太低，导致扫描行数过多。 5、频繁更新的值，不要作为主键或者索引。——B+树的平衡导致 页分裂 ，影响效率！ 6、随机无序的值，不建议作为索引，例如身份证、UUID。——无序，分裂 7、组合索引把散列性高（区分度高）的值放在前面 8、创建复合索引，而不是修改单列索引！

1.出现隐式转换

比如数据类型存储类型是varchar 查询的时候却是比如where code =1这样而不是 where = ‘1’

2.计算

比如where code = 1+2 这种情况

3. like %wang%查询

那为什么like wang%这种写法会执行索引呢？ 首先，字符串的匹配是基于最左匹配规则来执行的，为什么是最左匹 配，其实是和 B+Tree 有些关系，索引树从左到右都是有顺序的。对于 索引中的关键字进行对比的时候，一定是从左往右以此对比，且不可跳 过。 为什么是最左匹配原则？这个其实很好理解。比如，我们要比较一个字 符串。michael 与 mic，我们肯定是先从第一个字符开始比较吧，第一 个相同后，再比较第二个字符，以此类推。所以要从左边开始，并且是 不能跳过的。SQL 索引也是这样的。 –创建索引 ALTER TABLE user_innodb DROp INDEX comidx_name_phone; ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone); –针对字符串列的where条件，分别添加引号和不添加引号 explain SELECT * FROM where name = 136; explain SELECt * FROM where name = ‘136’; explain select *from user_innodb where name like ‘%wang%’; explain select *from user_innodb where name like ‘%wang’; like %%这个要怎么优化才能走索引？ MySQL使用正则表达会使用索引吗 联合索引最左匹配原则那个例子，把name放到phone后面，查看 下，会不会走索引？ 然后我们再来分析%，% 在前，就代表，我前面的内容不确定，对于不 确定的数据，索引无法比较，所以只能一个个的去比，这就相当于是全 表扫描了，全表扫描就没必要走索引，因此%写在前面是一定不会走索 引的。 如果%写在后面，由于前面的字符是确定的，所以可以通过索引来进行 匹配！

4.not like

explain select *from employees where last_name not like’wang’

5. 出现 != ,not in（）

在某些情况下可以使用到索引 某些情况下不会

在前面一条select语句执行过程中有自己去看

id

查询的序列编号 由大到小排列 id 越大越先执行 子查询最里层的子最先执行 然后依次执行 外层 关联查询时id 相同 则按照顺序从上到下执行

select_type

对应的查询类型 PRIMARY 主查询 SUBQUERY 子查询 UNIOn 连接 UNIOn RESULT 合并结果集 SIMPLE 单表查询 DERIVER 派生 最终查询结果之前的临时结果

type

级别 System 特殊的 const类型 针对于 MyISAM存储引擎的单表单记录查询 const 主键 或者唯一索引的等值查询 eq_ref 多表查询中 被驱动表通过唯一主键索引查询 ref 普通索引或者二级索引 联合索引符合最左前缀，唯一索引 弄了个 isnotnull

renge 基于索引的范围查询 ref_on_all where name = x or name is null

index 遍历二级索引拿到数据

all 全表扫描

possible_keys

确认type 的访问方式 有哪些索引可供选择

key

实际用到的索引

key_len

用到索引长度

ref

跟索引匹配的信息

rows

要扫描多少行才能返回

filtered

返回结果行数占需要结果行数的百分比 只对 index和all有效

Extra

https://blog.csdn.net/ymb615ymb/article/details/123396914 Using index 没有回表 Using where 没有用B+树直接定位结果 Using index&Using where 没有回表且没有用B+树直接定位结果 Using index condition 部分where条件用B+树定位并过滤 Null 回表了且没用where条件过滤（可能是用B+树定位了，也可能是全表搜了）

本文地址：http://sicmodule.glev.cn/quote/8330.html    歌乐夫 http://sicmodule.glev.cn/ , 查看更多