从根上开始学习MySql索引（二）

最新推荐文章于 2024-07-25 13:36:08 发布

泗水长流

最新推荐文章于 2024-07-25 13:36:08 发布

阅读量107

点赞数

分类专栏： MySql数据库数据结构文章标签：索引 innodb mysql 数据库树结构

本文链接：https://blog.csdn.net/lvxinchun/article/details/107901822

版权

MySql数据库同时被 2 个专栏收录

12 篇文章 0 订阅

订阅专栏

数据结构

9 篇文章 0 订阅

订阅专栏

前言

上一篇博客，我们用一定的篇幅说了一下MySql（InnoDB）底层采用的数据结构为B+Tree。在 MySQL 中，索引是在存储引擎层实现的，由于 InnoDB 存储引擎在 MySQL 数据库中使用最为广泛，所以下面所说的索引，都是基于InnoDB这个存储引擎来说的，这一点还请注意。

InnoDB存储引擎的索引

1.怎么理解索引

在InnoDB中，表都是根据主键的顺序以索引的方式来存在的，所有的数据都存放在B+Tree中，每一个索引都对应一个B+Tree。我们以一张表来说，存在多少个索引，就有多少个B+Tree。即便我们一张表中没有手动建立过一个索引，系统会默认给一个ROW_ID作为”主键“。
下面我们建立一张student表，以ID为主键索引，age为非主键索引：

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL,
  `name` varchar(20) DEFAULT '' COMMENT '姓名',
  `age` int(11) DEFAULT '0' COMMENT '年龄',
  PRIMARY KEY (`id`),
  KEY `index_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表中插入数据如下：
在这里插入图片描述
那么根据上面的索引的知识，这张表对应两个B+Tree,如下：

2.索引的类型

1.主键索引和非主键索引

从上面的图（模拟B+Tree）中可以看到，索引分为主键索引和非主键索引。

主键索引（primary key）：也称为聚簇索引（clustered index），它的叶子节点存存储的是整行数据；
非主键索引：也称为二级索引（secondary index），它的叶子节点内容存储的是主键的值。
我们看下面两条语句：

select * from student where id =3;
select * from student where age =3;

第一条语句是以主键id来查询，由于主键索引的叶子节点存储的是整行，那么就直接搜索ID主键索引的这颗树；
第二条语句是以非主键索引（age）来查询，由于非主键索引的叶子节点存储的是主键的id，那么就要先搜索age索引的这颗树,找到id的值为3，然后再到id主键索引树根据id再搜索一次，比第一条语句多了一下树的搜索操作，多出来的这个操作过程，被称作回表。

2.为什么建议使用自增主键

首先我们要先说一个知识点，那就是在MySql中，数据是以页为最小单位存储的，而不是存储的单条记录。即：Innodb B+树主键索引的叶子节点存的页（page）,一个页可以存多条数据。这就像我们的磁盘一样，每个扇区是512byte,我们的文件系统基本是以4k为一个block,即你要拿一个数据，其实拿到的是一块数据（不仅仅是你需要那一个数据，还包括它周围的其他数据）。MySql一个页的默认值为16k,超过这个大小，就要新起一个页来存放数据。这里我突然想到一个问题，就是既然一个页能够存储多条数据，那么这多条数据是怎么再页内存储的？其实在每个页的内部是放了一个有序数组，我们可以利用二分法来进行查找。
建立索引，能够提高我们查询（搜索）的效率，但同时为了维护B+Tree的有序性，在插入新的记录的时候，就要进行一定的维护。拿上图的学上表来说，加入插入一个id=7的学生，那么主键索引只需要在S5后面插入一条记录就可以了；但如我们要插入的是id=4的学生，就需要挪动后面的后面的数据（只是逻辑上的，改变链表的指针），可如果S5所在的页数据满了呢？那么这时候就需要申请新的页，把部分数据挪过去，从而产生页分裂，进而影响了性能。
除此之外，假如我们用uuid作为主键，那么一个uuid字符串的大小基本是36个字节，而即使我们使用bigint,也不过就8个字节，而我们的非主键索引的叶子节点存储的就是主键ID,而一个页的大小又是有限的，造成空间的极度浪费，即主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。此外，uuid本省就是个无序的，从而也减低了性能（维护索引）。
由此我们得出结论：从性能和存储空间方面考量，自增主键往往是更合理的选择。
当然使用自增主键并不一定就好，它也会产生其他问题，这个我们以后找机会再讨论。

3.怎么重建索引

1.为什么需要重建索引

因为索引有可能被删除（比如我们根据Id大量删除数据），插入的过程页可能产生页的分裂，从而会导致数据页存在空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

2.如何重建索引

还是以上面的学生表：

1.重建非主键索引

，如果我们重建age的索引，只需要如下操作：

alter table student drop index index_age;-- 删除索引
alter table student add index index_age (age); -- 创建索引

2.重建主键索引

alter table student drop primary key;-- 删除主键索引
alter table student add primary key(id);-- 创建主键索引

但是这样的作法是不恰当的，特别是对于数据量较大的表来说，更不合适。
删除主键索引，那么这时候就会重建一个内部的以RowId（对外不可见）的索引，同时页要重建非主建索引，因为非主键索引叶子节点存储的是主键；
同理，创建主键索引，会先删除内部的以RowId（对外不可见）的索引，然后再重建以id为主键的索引，然后还要重建所有的非主键索引。
从而，我们看出这样做是非常不恰当的，严重影响性能。更好的做法是如下的语句：

alter table student engine=InnoDB;

4.索引的优化

1.覆盖索引

假如我们要查询年龄（age）介于3岁到5岁之间的学生，那我们的sql语句很简单：

select * from student where age BETWEEN 3 and 5;

在这里插入图片描述
它的执行过程如下：

从上面的过程我们看到，这个过程中去id索引树中查询了两次，我们称这个过程为回表。如下：

为什么回表呢？因为age索引树的页子节点中只存有age和主键id,而没有存整条记录，所以去需要去主键索引上去找到文章的记录。所以我们要想提高查询效率，那么避免或者减少回表的过程，就是一个不错的方法。下面就是我们所说的覆盖索引。
覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。
假如我们上面的sql语句改变一下：

select id from student where age BETWEEN 3 and 5;

我们只查询id的值，不查询整条记录。这时候因为id已经在age索引树上存在了，直接就能得到查询结果，不需要去id索引树上再查询一次，即不需要回表过程，即索引age已经“覆盖了”我们的请求，这就是覆盖索引。覆盖索引可以减少树的搜索次数，显著提升查询性能，可以作为我们一个性能优化的手段。

2.最左匹配原则

1.概念

熟悉MySql数据库的同学都知道最左原则（最左匹配原则）这个定律，在建立联合索引的时候，这是我们必须要考虑的。至于它的定义，其实就是说：MySQL 建立联合索引的规则是这样的，它会首先根据联合索引中**最左边的、也就是第一个字段进行排序，在第一个字段排序的基础上，再对联合索引中后面的第二个字段进行排序，依此类推。

2.深入理解最左匹配原则

为什么会有最左匹配原则？为什么不是最右或者是其他方向的匹配原则呢？这还是要从我们的索引树说起。
我们先来明确一个概念，那就是联合索引。联合索引就是指，由两个或以上的字段共同构成一个索引。之前我们举的例子都是单个字段的索引，那么多个字段的联合索引是什么样的，也就是联合索引的索引树是什么样的呢？是一棵树还是多棵树呢？答案是***一棵B+Tree***。
我们以学生成绩表来举例说明。

CREATE TABLE `sutdent_score` (
  `id` bigint(20) NOT NULL,
  `student_id` int(11) DEFAULT '0' COMMENT '学生id',
  `score` int(11) DEFAULT '0' COMMENT '成绩',
  PRIMARY KEY (`id`),
  KEY `index_student_and_score` (`student_id`,`score`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

表中插入数据如下：
在这里插入图片描述
这张表对应两个B+Tree,如下：

从这张图我们看出，联合索引的索引树是以***左边第一个字段***作为非叶子节点，按照顺序进行放置，与单字段的非叶子几点是一致的，只是在叶子节点上有区别：对于相同的student_id,它会按照score来排序，如果还有第三个字段，那么score相同数据，就会再按照第三个字段排序，以此类推。所以不管是在非叶子节点还是叶子节点（页）上，都是按照从左边到右边的大小顺序来排列的。有了这个理解，那我们在设计联合索引的时候，就能游刃有余了。

3.最左匹配原则的误区

在工作中，有很多同事对最左匹配原则存在误区，他认为最左匹配原则是只写sql语句的时候，where条件要按照联合索引的顺序去写，不能颠倒,如下面两个语句：

select * from student_score where student_id =6 and score=86;
select * from student_score where score=86 and student_id =6;

他认为第一条语句走联合索引，而第二条语句不走，其实这是错误的，两条语句几乎没有区别，都会走联合索引的。我们说的最左匹配原则，其实还是从索引树的角度来说的，不是sql语句的顺序。当然，我们确实鼓励按照第一条语句的写法来写sql。

4.最左前缀匹配原则

上面我们说了最左匹配原则，其实更严格来说，应该是最左前缀匹配原则。可以通过以下这几个特性来理解：

对于联合索引，MySQL 会一直向右匹配直到遇到范围查询（> ， < ，between，like）就停止匹配。比如 a = 3 and b = 4 and c > 5 and d = 6，如果建立的是（a,b,c,d）这种顺序的索引，那么 d 是用不到索引的，但是如果建立的是（a,b,d,c）这种顺序的索引的话，那么就没问题，而且 a，b，d 的顺序可以随意调换。
= 和 in 可以乱序，比如 a = 3 and b = 4 and c = 5 建立（a，b，c）索引可以任意顺序。
如果建立的索引顺序是（a，b）那么直接采用 where b = 5 这种查询条件是无法利用到索引的，这一条最能体现最左匹配的特性。

5.索引下推

以上面4中最左前缀匹配原则的例子，如果索引是（a,b,c,d），我们where后面是 a like ‘中’ and b=4的时候，我们知道，b=4是不走索引的，我们要从a的范围中取出id,去id主键索引中去查询b,看是否符合b=4这个条件，这就要涉及到回表。在MySql5.6之前，确实是需要这么去判断的，但是从MySql5.6开始，就不需要再回表验证b=4这分条件了，可以在索引遍历过程中，对索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数。这就是索引下推优化（index condition pushdown）。

4.普通索引OR唯一索引

ALTER TABLE table_name ADD INDEX index_name (column_list);-- 创建普通索引
ALTER TABLE table_name ADD UNIQUE index_name (column_list);-- 创建唯一索引

上面两个语句分别是创建普通索引和唯一索引的sql,关于他们的概念，我再这里就不再说了，我主要想比较一下这个两个索引。
要比较索引，我们就要从两个方面来比较：查询性能和更新性能。
为了方便说明，我们还是以上面的student为例来说明，只不过增加以id_card(身份证号)的字段，并且设置为唯一索引。

CREATE TABLE `student` (
  `id` bigint(20) NOT NULL,
  `id_card` varchar(18) NOT NULL COMMENT '身份证号',
  `name` varchar(20) DEFAULT '' COMMENT '姓名',
  `age` int(11) DEFAULT '0' COMMENT '年龄',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_id_card` (`id_card`) USING BTREE,
  KEY `index_age` (`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `student` VALUES ('1', '111111111111111111', 'jane', '1');
INSERT INTO `student` VALUES ('2', '222222222222222222', 'bob', '2');
INSERT INTO `student` VALUES ('3', '33333333333333333X', 'blake', '3');
INSERT INTO `student` VALUES ('5', '444444444444444444', 'bill', '5');
INSERT INTO `student` VALUES ('6', '555555555555555555', 'bnen', '6');

在这里插入图片描述
下面我们根据id_card来查询：

select id from student where id_card ='33333333333333333X';

1.查询性能比较

假如id_card上是普通索引，查找到第一个记录（3）后，需要继续查找下一个记录，依次判断id_card是否为33333333333333333X，直到碰到第一个不满足id_card=33333333333333333X条件的记录，执行完成；
假如id_card上是为唯一索引，由于具有唯一性，查找到第一额记录（3）后，就停止执行了，任务结束。
从上面的分析来看，唯一索引要比普通索引的性能要好，但其实最终的差距并不是很大，几乎可以忽略。这就涉及到我们之前说的，InnoDB的数据是按照页为最小单位来存储的，我们虽然是查询一条记录，但并不是只将这一条记录从磁盘取出来，而是以页为单位，作为整体载入内存,所以对于普通索引来说，大部分情况只要多做一次“查询和判断下一条的记录就行了”，极端情况，如果id=3这条记录正好是数据页的最后一个记录，那么要读取下一个记录，就要取下一个页，这样可能会复杂些，但这个几率不高。

2 .更新性能比较

MySql的更新过程，会有一个chang buffer的缓存。我们先说一下chang buffer.
当更新一个数据时，如果更新的页在内存中，那就直接更新内存；如果没在内存中，InnoDB会将这些更新操作缓存在一个叫做chang buffer的缓存中，这样就不需要立马进行IO操作来读取这个页到缓存中，在下次查询访问这个数据页的时候，将数据页载入内存，然后执行change buffer 中与这个页有关的操作，从而在逻辑上保证数据的准确性。
那么在InnoDB中，更新的过程是怎样的呢？

如果要更新的数据页就就在内存中，那么就按照相应的位置，插入到内存中就可以，只不过唯一索引多了一步是否存在判断而已，二者的差异并不是很大；
如果要更新的数据页不在内存中，由于唯一索引需要判断唯一性，它就必须从磁盘中load相应的数据页到内存中，这样就增加了IO的操作，而对于普通索引就直接数据更新到chang buffer就行了，执行结束。，
即：一索引的更新就不能使用 change buffer，只有普通索引可以使用。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size 来动态设置。这个参数设置为 50 的时候，表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

但是这个chang buffer最终还是要刷到磁盘中的，那什么时候刷新到磁盘呢？
将 change buffer 中的操作应用到原数据页，得到最新结果的过程称为 merge。除了访问这个数据页会触发 merge 外，系统有后台线程会定期 merge。在数据库正常关闭（shutdown）的过程中，也会执行 merge 操作。

结论：由于唯一索引用不上 change buffer 的优化机制，因此如果业务可以接受，从性能角度出发我建议你优先考虑非唯一索引。

泗水长流

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
从根上开始学习MySql索引（二）

我心目中的MySql索引（二）前言InnoDB存储引擎的索引1.怎么理解索引2.索引的三级目录前言上一篇博客，我们用一定的篇幅说了一下MySql（InnoDB）底层采用的数据结构为B+Tree。在 MySQL 中，索引是在存储引擎层实现的，由于 InnoDB 存储引擎在 MySQL 数据库中使用最为广泛，所以下面所说的索引，都是基于InnoDB这个存储引擎来说的，这一点还请注意。InnoDB存储引擎的索引1.怎么理解索引在InnoDB中，表都是根据主键的顺序以索引的方式来存在的，所有的数据都存放在
复制链接

扫一扫

专栏目录