共计 6971 个字符，预计需要花费 18 分钟才能阅读完成。
今天丸趣 TV 小编给大家分享一下 Mysql 索引底层及优化方法是什么的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解一下吧。
一. 首先我们说下什么是索引，为什么要用索引
索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL 必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL 能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。
二. 索引类型分为两类：1.hash 索引 2.bTree 三. 下面我们简单分析一下 hash 索引和 bTree 索引。
1. 哈希表是一种以键 – 值（key-value）存储数据的结构，我们只要输入待查找的键即 key，就可以找到其对应的值即 Value。哈希的思路很简单，把值放在数组里，用一个哈希函数把 key 换算成一个确定的位置，然后把 value 放在数组的这个位置。
不可避免地，多个 key 值经过哈希函数的换算，会出现同一个值的情况。处理这种情况的一种方法是，拉出一个链表。
2. 说到 bTree，就不得不提二叉树，二叉树分为很多，例：二叉查找树，平衡二叉树等。当然还有重点红黑树。
1）二叉查找树的特点是：父节点左子树所有节点的值小于父节点的值。右子树所有节点的值大于父节点的值。下面以一张图为例来体现二叉查找树。
IDname5 张五 6 张六 7 张七 2 张二 1 张一 4 张四 3 张三
有一个需求，查找张三，如果不使用二叉查找树那么我们需要查找 7 次，使用二叉查找树我们只需要查找 4 次就可以找到我们想要的值。
根据上面说的使用二叉查找树的确可以减少查询次数，但是大家有没有想过，如果数据库的数据是 1，2，3，4，5，6，7 这样依次递增的数据呢，继续使用二叉查找树就会变成一个链表了。那这样如果我们想要查找 7 那么需要查找 7 次，扫描表也是需要 7 次。这样跟没有建立索引没有区别，这也是弊端之一。下图为例说明。
2）平衡二叉树：又被称为 AVL 树，它的左右两个子树的高度差的绝对值不超过 1，并且左右两个子树都是一棵平衡二叉树，AVL 树是最早发明的自平衡二叉查找树。在 AVL 树中，任何节点的两个子树的高度最大差别只能为 1，所以它又被称为高度平衡树。查询、增加和删除在平均和最坏情况下都是 O(log n)。增加和删除会需要通过一次或多次树旋转来重新平衡这个树。
我们引入二叉树的目的是为了提高二叉树的搜索的效率, 从而减少树的平均搜索长度，为此, 就必须在每颗二叉树插入一个结点时调整树的结构, 让二叉树搜索能够保持平衡, 从而可能降低树的高度, 减少的平均树的搜索长度。
平衡二叉树特点如下：
1. 它的左子树和右子树都是 AVL 树
2. 左子树和右子树的高度差不能超过 1
例图：
3）红黑树：可以理解为红黑树是凌驾于平衡二叉树之上的一棵树，红黑树不会追求“完全平衡”，它只会求部分达到平衡要求，降低了对旋转的要求，从而提高性能。此外，由于它的设计，所有不平衡都能够在三次旋转之内解决。在红黑树中，它的算法时间复杂度与 AVL 相同，并且统计性能会逼 AVL 树更高。所以红黑树相对于平衡二叉树来说，不是严格意义上的平衡二叉树，红黑树插入和删除效率更高一些，查询的效率比平衡二叉树来说相对低一些，但是二者查询效率差值做对比，基本可以忽略不计。红黑树特点如下：
1. 节点是红色或黑色。
2. 根节点是黑色。
3. 每个红色节点的两个子节点都是黑色。（红色节点的子节点必须是黑色节点）
4. 从任一节点到其每个叶子的所有路径都包含相同数目的黑色节点。
故红黑树是黑色平衡的树，左子树与右子树高度差不会超过 2。红节点的父节点、子节点只能是黑节点。
例图：
4）BTree（B 树）：当然上面说到了红黑树，性能非常高。以上图为例，树的高度最高才为 4，共 9 条数据，但是对于 Mysql 数据库，动则几百万条数据，几千万条数据，那树的高度就不可估量了，比如说上百万条数据需要经过 30-50 次磁盘 IO 才能查询到数据，甚至更多的次数，显然不能满足 Mysql 索引高效的查询效率。那如果我们控制树的高度呢，那这样就会极大减少了请求磁盘 IO 的请求次数，如果高度控制在 4，那只需要经过 4 次磁盘 IO 就可以查询到数据。
但是怎么样控制树的高度呢，红黑树是每个节点只存储一个元素，如果每个节点存多个元素呢，这样就可以解决高度问题了，肯定有同学有疑问，把所有的元素都放到一个节点上，那高度值就是 1 了，不是更快吗？这样想肯定是错的，Mysql 每一次跟磁盘 IO 打交道是有大小限制的，Mysql 限制每一个节点的大小是 16K。想查看自己 Mysql 限制节点大小的同学可以执行下面的 sql。
show global status like‘Innodb_page_size’
下面以图为例体现 BTree
BTree 特点如下：
1. 所有索引元素不重复
2. 节点的数据索引从左到右依次递增
3. 叶节点具有相同的深度，叶节点的指针为空
4. 叶子节点和非叶子结点都存储索引和数据
5）B+ 树：上面说到了 BTree 控制了树的高度的问题，可以满足 Mysql 对于索引的需求，但是最终 Mysq 索引实现不是 BTree 而是 B + 树，Mysql 对 B 树做了一点点改造，得到了 B + 树，也可以理解为 B + 树是 B 树的升级版。
下面以图为例说明：
从这张图可以看到，我们的非叶子节点只存储了索引并没有存储 data，而且叶子节点间用指针相连。B 树的叶子节点和非叶子节点都存储了索引和数据，而且叶子结点的指针为空，B+ 树把数据放在了叶子节点上，这样非叶子节点就可以存放更多的索引，每次从磁盘 IO 也能获取更多的索引。
B+ 树特点如下：
1. 非叶子节点不存储 data，只存储索引 (冗余) 和下层指针，可以放更多的索引
2. 叶子节点包含所有索引字段，和数据
3. 叶子节点用双指针连接，提高区间访问的性能
在百度上和很多博客上画的 B + 树是错误的哦，一定要避坑哦。
有兴趣看 Mysql 官方对 B + 树的解释的可以去看看。
链接: Mysql 官网.
四. 索引分类
1. 按照索引的存储关联分类：分为两大类
1.）聚集索义（聚簇索引）：叶节点包含了完整的数据记录，不需要回表。
2.）非聚集索引：需要回表，二次查树，影响性能。
1.1）大家都知道 Mysql 常用的存储引擎有两种 MyISAM 和 InnoDB，但是大家实际了解过两种存储引擎底层的数据存储结构吗？
下面以图为例为大家说明：
其中 test.myisam 表是 MyISAM 存储引擎，actor 表是 InnoDB 存储引擎，可以看到 MyISAM 存储引擎有三个文件，分别是 frm、MYD、MYI，很容易理解 frm-frame 的简称，存的是表的结构，MYD-MYData 存的是数据，MYI-MYIndex 存的是索引，索引和数据是分开存储的，再看 InnoDB 只有 frm、IBD，其中 frm 一样也是存的表的结构，IBD 文件存的是索引和数据，这点 InnoDB 和 MyISAM 不一样。
下面以图为例说明 MyISAM 存储引擎主键索引是需要回表操作（非聚集索引）
其中 15 存的是主键索引，0x07 存的是 15 所在行记录的磁盘文件地址指针，比如我们想找到 15 的数据，那首先应该先通过主键索引树，找到 15 所对应的指针，然后找到了这个指针再去 MyD 文件中找具体的数据，需要进行二次查找，这个过程称为回表操作。
2.1）下面以图为例说明 InnoDB 存储引擎主键索引不需要进行回表操作。（聚集索引）
InnoDB 存储引擎子节点首先 15 那一行存放的是索引，15 下面的那一列存放的是索引所在行的其他所有字段，如果我们想要查 15 的数据，直接就可以找到，不需要在经过二次查树。
2. 按照功能分类：主要分为五大类
2.1 主键索引：InnoDB 主键索引不需要回表操作
2.2 普通索引（二级索引）：InnoDB 普通索引需要回表操作，对于二级索引，会默认和主键做联合索引。
2.3 唯一索引
2.4 全文索引
2.5 联合索引：需要满足最左前缀原则
3. 在 2.2 中提到了普通索引需要回表操作，那有没有不需要回表的普通索引呢，答案是有的，在某个查询里面，索引已经覆盖了我们的查询需求，我们称为覆盖索引。这时是不需要回表操作的。
由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。
举个例子：下面是这个表的初始化语句。
mysql create table T (
ID int primary key,
k int NOT NULL DEFAULT 0,
s varchar(16) NOT NULL DEFAULT ,
index k(k))
engine=InnoDB;
insert into T values(100,1, aa),(200,2, bb),
(300,3, cc),(500,5, ee),(600,6, ff),(700,7, gg在上面这个表 T 中，如果我执行 select * from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？
现在，我们一起来看看这条 SQL 查询语句的执行流程。看下图。
1.）在 k 索引树上找到 k=3 的记录，取得 ID = 300；
2.）再到 ID 索引树查到 ID=300 对应的 R3；
3.）在 k 索引树取下一个值 k=5，取得 ID=500；
4.）再回到 ID 索引树查到 ID=500 对应的 R4；
5.）在 k 索引树取下一个值 k=6，不满足条件，循环结束。
在这个过程中，回到主键索引树搜索的过程，我们称为回表。可以看到，这个查询过程读了 k 索引树的 3 条记录（步骤 1、3 和 5），回表了两次（步骤 2 和 4）。
在这个例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。
如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，我们称为覆盖索引。
在 InnoDB 中，表都是根据主键顺序以索引的形式存放的，这种存储方式的表称为索引组织表。又因为前面我们提到的，InnoDB 使用了 B+ 树索引模型，所以数据都是存储在 B+ 树中的。每一个索引在 InnoDB 里面对应一棵 B+ 树。
五. 索引优化 1. 上面描述了索引基本概念、分类以及底层的基本结构相关知识。下面聊一聊索引优化的相关知识吧。
1.）当组合索引中只要有一列含有 null 值，索引失效
2.）在列上做计算索引失效，范围之后的索引全部失效
3.）在查询条件上使用函数会造成索引失效
4.）在 where 字句中使用 != 或 操作符，导致索引失效
5.）避免使用 or，导致索引失效
6.）使用模糊查询也会造成索引失效，可以用 like‘a%’而不是 like‘%a%’
7.）尽量使用覆盖索引，减少 select * 语句
8.）满足最左前缀法则，最左前列开始并且不跳过索引中的列
9.）字符串不加单引号索引失效
2. 下面以实战说明索引优化。
新创建一个员工表，有 5 个属性，如下。
create table employees(
id int primary key auto_increment comment 主键自增 ,
name varchar(30) not null default comment 名字 ,
age int not null default 1 comment 年龄 ,
id_card varchar(40) not null default comment 身份证号 ,
position varchar(40) not null default comment 位置
-- 创建联合索引
create index name_index on employees (name,age,position);
-- 插入一条数据
insert into employees(name,age,id_card,position) values( 张三 ,15,
201124199011035321 , 北京
-- 下面以 10 条 sql 测试，注意建立的联合索引顺序是 name，age，position
1.explain select * from employees where age=15 and position= 北京 and name= 张三
2.explain select * from employees where name= 张三 and age=15 and position= 北京
3.explain select * from employees where age=15 and name= 张三
4.explain select * from employees where position= 北京 and name= 张三
5.explain select * from employees where position= 北京 and age=15;
6.explain select * from employees where position= 北京 and age 15 and name= 张三
7.explain select * from employees where position= 北京
8.explain select * from employees where age=15;
9.explain select * from employees where name= 张三
10.explain select * from employees where name != 张三以上 10 条 sql 有哪些是索引失效，有哪些是索引没有失效的呢？相信同学们已经有了答案，但是答案对不对呢，下面我们一起分析下。首先说第 1 条，查询条件把 3 个索引全部用上了，但是索引的顺序有变化，由 name，age，position 变成
了 age，position，name，想到这里肯定有很多同学给出的答案就是索引失效，但是事实证明这个结果
是错的，索引生效，肯定有很多同学疑惑，为什么呢，这条 sql 不满足最左原则法则呀，这就要涉及到 sql
的执行流程了，这里博主简单说下，sql 执行有 1 个优化器的过程，优化器的作用之一就是索引的选择优化，所以优化器帮我们把索引的顺序变成正确的了，所以索引生效。下面是第 1 条按照索引顺序 sql 和第 2 条没有按照索引顺序 sql 的执行结果。执行结果入下图：可以发现全部生效。第 1 条 sql type 的值为 ref、字节是 288 并且 ref 有 3 个 const，全部生效。
第 2 条 sql type 的值为 ref、字节是 288 并且 ref 有 3 个 const，全部生效。
想学习 sql 的执行流程的可以看博主的另一篇关于 sql 执行流程的文章哦。有的同学有疑问了，那最左原则没有用了吗？答案：有用的。
现在我们说下第 3、4、5 条 sql
explain select * from employees where age=15 and name= 张三
sql 在执行的时候，优化器替我们把索引的顺序优化了，由 age - name 变成 name - age，这时
索引是生效的。explain select * from employees where position= 北京 and name= 张三
索引顺序优化为 name - position, 但是这时索引只有 name 索引生效，position 没有生效，因为我
们建立的索引顺序是 name - age - position，你会发现跳过了 age，索引本质也是一棵树，少
了一个节点，下面的索引当然不会生效了，这就没有满足最左原则法则。explain select * from employees where position= 北京 and age=15;
这就和第 4 条 sql 一样的道理了，第一个索引都不见了，后面的不可能生效。执行结果如下：可以发现第 3 条 sql type 的值为 ref、字节是 126 并且 ref 有 2 个 const，全部生效。
第 4 条 sql 只有 122 字节并且 ref 只有 1 个 const，只有 name 索引生效。
第 5 条 sql type 的值为 all，字节和 ref 都是空，全部失效。
下面说第 6 条 sql，剩下的 sql 都是和之前的 sql 一样的道理。explain select * from employees where position= 北京 and age 15 and name= 张三
这条 sql 我们会发现，把索引字段全部使用了并且当作条件查询，不一样的是 age 是范围查找，优化器替我
们把索引顺序优化成 name - age - position ，按照我们索引优化第 2 条：在列上做计算索引失效，范围之后的索引全部失效，想必答案同学们都知道了。执行结果如下：
第 6 条 sql 只有 126 字节并且 type 的值为 range，范围查找，只有 name 和 age 索引生效。
以上就是“Mysql 索引底层及优化方法是什么”这篇文章的所有内容，感谢各位的阅读！相信大家阅读完这篇文章都有很大的收获，丸趣 TV 小编每天都会为大家更新不同的知识，如果还想学习更多的知识，请关注丸趣 TV 行业资讯频道。
