【MySql进阶及常见方案】mysql索引底层原理和优化手段

徐徐柒柒

已于 2024-10-28 17:45:56 修改

阅读量991

点赞数 41

分类专栏：数据库专题-进阶及常见方案文章标签： mysql 数据库后端

于 2024-10-28 17:40:33 首次发布

本文链接：https://blog.csdn.net/qq_40023049/article/details/143291485

版权

数据库专题-进阶及常见方案专栏收录该内容

5 篇文章

订阅专栏

mysql索引底层原理和优化手段

不管业务怎么发展，数据最终都要存储到数据库中，所以数据库是必不可少的一个环节。而随着业务逐渐壮大，并发越来越高，数据库很容易成为整个链路的短板。而调优的第一步，都是从sql语句、索引入手。先得保证单个数据库执行没问题，才会有更高层次的分库分表、弹性、容灾等等。
所以这一趴我们先大致聊聊索引是怎么回事。
说明：全专栏均以InnoDB存储引擎为基准，数据库版本为：5.7及以上

1. 数据怎么被存储

首先我们来看，这是一张Mysql数据存储的架构图：
在这里插入图片描述
数据被分了多个部分：由行数据组成页，由页组成区块，由区块组成段，由段组成表空间，一级一级从小变到大。其中页是MySQL和磁盘交互的最小单位。
InnoDB存储引擎表是Index organized的（数据即索引，索引即数据），他们都维护在一个B+树上，数据段就是叶子节点，索引段就是非叶子节点；
而我们划分的段、区块的目的也都是为了更好的利用操作系统的资源（比如每次从磁盘加载到内存的数据大小按区块来约定等等），用以达到更高效读写和逻辑划分。
那我们怎么从页找到行，又从页怎么聚合到区块、到段再到空间呢。

1.1 数据记录最小单位 – 行

我们先看看一条记录的结构，如下图(其实就是上图左下角部分)：

我们可以看到，记录头中除了行号，还有下一条记录的标识next_record，所以，我们可以通过next_record将记录连接起来，以单向链表的形式，所以这就决定了，当我们在记录链中寻找某记录时，只能顺序遍历，这也决定了一条数据链不会太长。
我们可以从官方资料中得知，一个页默认大小是16K，加上行溢出等处理，一页最多存放7992行记录，这么多的记录，必须顺序遍历么？当然不需要，让我看看页是怎么组织记录行的。

2. 与磁盘最小交互单位-- 页

作为与磁盘交互的最小单位(默认16Kb)，是用来存放实际数据的(页类型是b-tree Node存真实数据,还有其他类型如索引目录页等用来加速查询)从上面的大图中可以大致看到一个页的整体结构：

来看几个关键的字段参数：

File Header 文件头：决定页和页之间的关联关系。
记录本页的一些通用信息，主要包含【本页页号、上一页、下一页、页类型、所属表空间】，其实很多时候我们也可以猜到，不包含这些就无法关联了。
通过页号来找到本页、通过上下页进行双向链表串联、通过类型判断是索引页还是数据页。
Page Header决定页的层级
存储当前页的数据信息，主要包含【本页记录数量、在B+树中的层级、归属的索引ID、插入方向、最大事务ID】
Page Directory 决定着记录项在页内的查询效率；
为了更快速的查询，页目录存储的本页的数据目录(槽)，包含最大最小记录和分组数据链的最大记录的偏移量。方便使用二分法快速查找数据，不需要再从最小值开始遍历，如下图：

页目录存储的是槽，槽相当于分组记录的索引。我们通过槽查找记录，实际上就是在做二分查找。二分查找就不详细讲了，如不清楚的可以自己去搜相关资料，挺简单的一个算法。每个槽位记录最大ID和最小ID，这样，想查的数据只需要去通过二分查找找到对应的槽位，然后去遍历该槽位下所有的记录即可。

以上对页的数据结构进行了拆解，清晰的描述了页的组成，那我们怎么利用这些结构来实现的数据快速查询呢？

2. 索引

其实，在叙述行记录结构的时候，我们就看到，数据行的结构中，除了实际业务数据外，还有很多额外空间。
如record_type用来表示该记录的类型是数据还是索引。正是这些额外的空间的设计，给InnoDB以更加适合的方式组织索引提供了支持。
mysql的索引有很多种，接下来我们逐个分析一下。

2.1 MySQL主键索引结构

MySQL索引是如何运用B+树的呢？
在这里插入图片描述
我们知道，MySQL数据是以页为单位，存储在B+树节点上，每一页默认大小16kb，每页上存储了若干记录数据，记录与记录之间是单项链表结构，页与页之间是双向链表结构。
我们简单看下主键数据页的结构，如下图所示：
在这里插入图片描述
接下来我们举个例子，更好的了解主键索引下的页结构

初始时，某张表里有ID=1,3,5三条数据，这张表的数据存储在页10；
接下来插入一条ID=4的数据；

此时主键值为1,3,5,4，明显5,4顺序反了，此时MySQL就会调整单链表记录数据，改成1,3,4,5
在这里插入图片描述
我们上面介绍的，其实就是B+树最底层的结构，因为B+树是只有最底层才会存储数据，其他层存储的都是索引目录信息，如下图所示：

从上图的B+树我们可以看出，不论是存放用户记录的数据页，还是存放目录项记录的数据页，我们都把它们存放到B+树这个数据结构中了，所以我们也称这些数据页为节点。从图中可以看出来，我们的实际用户记录其实都存放在B+树的最底层的节点上，这些节点也被称为叶子节点，其余用来存放目录项的节点称为非叶子节点。
从图中可以看出来，一个B+树的节点其实可以分成好多层，规定最下边的那层，也就是存放我们用户记录的那层为第0层，之后依次往上加。
假设所有存放用户记录的叶子节点代表的数据页可以存放100条用户记录，所有存放目录项记录的内节点代表的数据页可以存放1000条目录项记录，那么：
如果B+树只有1层，也就是只有1个用于存放用户记录的节点，最多能存放100条记录。
如果B+树有2层，最多能存放1000×100=100000条记录。
如果B+树有3层，最多能存放1000×1000×100=100000000条记录。
如果B+树有4层，最多能存放1000×1000×1000×100=100000000000条记录。
你的表里能存放100000000000条记录么？所以一般情况下，我们用到的B+树都不会超过4层。

2.2 二级索引、联合索引结构

要明确知道的一点，只要是B+数，那对于key的排序就是确定的。
区别仅仅在于主键索引的key是ID，而二级索引的key则是那个字段的顺序，而联合索引的key则是那多个字段合并起来的顺序(准确来讲，会先按照A列的值进行排序，如果记录的A列相同，则按照B列的值进行排序，依次类推)；子节点也没有存储全部数据，只是存储了主键id，所以我们通过二级索引查询到主键id之后，还需要通过id再次查询主键B+树，获取完整记录信息，也就是回表；

2.3 索引的运用

CREATE TABLE person_info(
    id INT NOT NULL auto_increment,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_name_birthday_phone_number (name, birthday, phone_number)
);

通过上面的学习，我们知道这个表会创建2个B+树，一个是主键B+树，一个是联合索引B+树，联合索引B+树需要注意的是记录排序会先按照name列的值进行排序，如果name列的值相同，则按照birthday列的值进行排序，如果birthday列的值也相同，则按照phone_number的值进行排序。
在这里插入图片描述
我们依次来看各种索引的查询：精确匹配，左字段匹配，右模糊匹配，范围值匹配，排序，分组，覆盖索引等操作

全值精准匹配

SELECT * FROM person_info WHERE name = 'Ashburn' 
AND birthday = '1990-09-27' AND phone_number = '15123983239';

这个相信大家都知道，这个查询过程：

因为B+树的数据页和记录先是按照name列的值进行排序的，所以先可以很快定位name列的值是Ashburn的记录位置。
在name列相同的记录里又是按照birthday列的值进行排序的，所以在name列的值是Ashburn的记录里又可以快速定位birthday列的值是’1990-09-27’的记录。
如果name和birthday列的值都是相同的，那记录是按照phone_number列的值排序的，所以联合索引中的三个列都可能被用到。

匹配左边的列

因为我们强调过数据是先按照name列的值进行排序，name列，相同才会按照birthday列的值排序，所以如果我们直接查birthday，会用不到联合索引树。

SELECT * FROM person_info WHERE birthday = '1990-09-27'; -- 不走联合索引
SELECT * FROM person_info WHERE name = 'Ashburn';        -- 走联合索引

为什么单查birthday不走索引？我们想象一下，在一个排好序的10位长度的文本里，如果我们要查4-6位是123的数据，我们是不是要把整个文本重头到位都找一遍？索引也是如此。

匹配列前缀

同 匹配左边的列

匹配范围值、排序和分组

不管是前缀范围值还是列范围值，只要符合从左到右匹配的规则，都是可以使用到索引的。本质原因是因为索引是有序的。

索引覆盖

什么是索引覆盖？不用再次回表的查询，我们称之为索引覆盖。那怎样才能不回表呢？数据是不是就在二级索引或者联合索引上就不用再回到主键索引上查了？
我们看看以下几个SQL，并同时回答以下3个问题：

查联合索引吗？
回表吗？
效率如何？

如果把上面3个问题搞懂了，索引的使用就基本没有问题了。

-- 1. 查，2. 不回表，3.低，全表扫描
SELECT name, birthday FROM person_info WHERE birthday = '1990-09-27' ORDER BY name, phone_number;
-- 1. 不查，2. 不回表，3.低，全表扫描
SELECT * FROM person_info WHERE birthday = '1990-09-27' ORDER BY name, phone_number;
-- 1. 查，2. 回表，3.高
SELECT * FROM person_info WHERE name = 'Ashburn' ORDER BY name, phone_number;
-- 1. 查，2. 不回表，3.高
SELECT name, birthday FROM person_info WHERE name = 'Ashburn';

2.4 生产实践

索引优化

select * from task 
where status=x 
and   operator_id=xxxx 
and   operate_time>xxxxxxxx01
and   operate_time<xxxxxxxx99
and   type=x;

开发发现此sql运行的越来越慢，希望给每个字段加二级索引，被优化师叫停，而是考虑的该表所有查询方式后，创建了一个联合索引：
(status,operator_id,type,operate_time)
为什么不建多个的二级索引？为什么范围查询的字段要放在最后？
分析：

从前面的原理部分我们知道，索引是要占内存的，不是越多越好，能起作用就行。
用于范围匹配的字段的索引位置要严谨。因为创建索引的时候，根据索引字段的顺序来进行排序，如果把time字段放在type字段前面建索引，在查询时，因为time是一个范围值，那么多个time值延续到type字段，整体是无序的，无法用到type索引。

阿里开发手册中几条典型的规范

【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。
原理关联：字段越长，索引占内存越多，只要其长度可以保证区分度即可
【强制】字符搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。
原理关联：左模糊的字段不是有序的，无法用到索引
【推荐】如果有 order by 的场景，请注意利用索引的有序性。order by 最后的字段是组合索引的一部分，并且放在索引组合顺序的最后，避免出现 file_sort 的情况，影响查询性能。
原理关联：如果条件中有范围查询，则后续字段是无序的，order by时无法用到索引
【推荐】建组合索引的时候，区分度最高的在最左边。
原理关联：区分度越高，查询路径越短，效率越高
等等，参见阿里Java开发手册