Mysql索引底层及优化方法是什么

今天小编给大家分享一下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 intNOT NULL DEFAULT0,  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 onemployees (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 * fromemployeeswhere position=北京 and name=张三; 5.explain select * from employees where position=北京 and age=15; 6.explainselect * 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 第3条： explainselect * from employees where age=15 and name=张三; sql在执行的时候，优化器替我们把索引的顺序优化了，由 age -> name 变成 name -> age，这时 索引是生效的。 第4条： explain select * from employees whereposition=北京 and name=张三; 索引顺序优化为name - > position,但是这时索引只有name索引生效，position没有生效，因为我 们建立的索引顺序是 name  -> age - > position，你会发现跳过了age，索引本质也是一棵树，少 了一个节点，下面的索引当然不会生效了，这就没有满足最左原则法则。 第5条： 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索引底层及优化方法是什么”这篇文章的所有内容，感谢各位的阅读！相信大家阅读完这篇文章都有很大的收获，小编每天都会为大家更新不同的知识，如果还想学习更多的知识，请关注行业资讯频道。