MySQL索引与优化

最新推荐文章于 2025-01-13 05:32:30 发布

阿东编程之路

最新推荐文章于 2025-01-13 05:32:30 发布

阅读量272

点赞数 1

文章标签： mysql 数据库 b+树

本文链接：https://blog.youkuaiyun.com/weixin_46017166/article/details/124529495

版权

一. 什么是索引？

索引，就像书的目录一样，我们看书的时候会根据一些标题找目录对应具体的页面。如果没有目录会怎么样？我们肯定会去一页一页的翻书找我们想看的内容，效率肯定特别低，对于数据库也是一样，没有索引，数据库就要去磁盘里一行一行遍历去找。

所以索引是为了提高查询效率的。

二. 索引的实现及原理

索引的实现有很多种，有适合等值查询的hash表，还有适合范围查询的有序数组，我们在MySQL中一般都使用InnoDB引擎，那InnoDB引擎用的索引的数据结构是什么呢？

可以用hash表吗？

我们都知道，hash表是以键-值（key-value）的数据结构，hash的原理就是根据key的hash值换成的一个具体的索引位置index，然后将value放到一个数组的index位置，后面查询就可以通过的计算key的hash值从value的数组中直接查找到。
但是hash适合MySQL吗，我们在使用MySQL时候经常查询的是一个范围，hash只适合等值查询比如一些非关系型数据库，所以不会使用hash。

那可以用有序数组吗？

有序数组的范围查询和等值查询确实都比较适合，但是我们一般使用关系型数据库的写入都非常频繁，数组的数据结构如果有增删都会将后面所有的数据进行移动，成本是非常高的。

MySQL的innodb引擎用的索引数据结构是？

大家都都知道二叉树的特性是父节点左子树所有的节点的值都小于父节点的值，右子树所有的节点的值都大于父节点的值，而平衡的二叉树就是在二叉树的基础上加了一个特性：左右子树高度差的绝对值不超过1。

具体树的原理的大家可以去了解下。

但是可以用平衡二叉树作为MySQL innodb的索引会有什么问题吗？

如果我们的数据量很大，树的高度还是比较高的，就比如存储100个节点的平衡二叉树就大概高20，对于mysql来讲，增加一个高度就意味着多一次磁盘io，所以一次查询可能需要和磁盘进行20次的交互，所以我们不能用平衡二叉树，得用平衡n叉树。
平衡n叉树你就可以理解为每个节点有n个子节点，子节点保证从左到右大小递增。这就是MySQL的InnoDB引擎使用的b+树索引结构。
MySQL中不同数据结构的索引B+树的n也不一样，比如整型大概是1200，1200叉树的高度为4时就可以存17亿的数据了，高度降低了不少吧，io磁盘次数也减少了很多。

在MySQL的Innodb引擎中，一个索引就是一棵B+树。对于在innodb中索引类型又有两种，聚簇索引和非聚簇索引。

聚簇索引也就是主键索引，主键索引的叶子节点存的是整个记录。
非聚簇缩索引也叫二级索引，所有非主键的索引都是二级索引，二级索引叶子节点存的是主键id。

那根据的主键索引和二级索引查询的区别是什么？

CREATE TABLE `student` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `idno` varchar(32) DEFAULT NULL comment "身份证号", 
  `name` varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT '' comment"姓名",
  `gender` int NOT NULL DEFAULT '1' comment"性别，1:男，2女",
  PRIMARY KEY (`id`),
  KEY `idx_idno` (`idno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into student (idno, name, gender) 
values ("111", "张三", 1),  
("222", "李四", 1),  
("333", "王二", 2),  
("444", "赵六", 2),  
("555", "孙七", 1);

以上的表id是主键，idno是普通索引，那么在磁盘中就会有一个聚簇索引（id）和一个非聚簇索引（idno）。

那么我们看下以下两个查询语句在索引查找层面有什么区别：

-- 语句1
select * from student where id = 5;
-- 语句2
select * from student where idno = "555";

语句1：通过搜索聚簇索引（id）直接找到这条记录返回。
语句2：先搜索非聚簇索引（idno），找到二级索引树上存的id记录，然后拿着id记录去聚簇索引（id）上再搜索一次，这个操作叫做回表。

那毋庸置疑，根据二级索引的查询比根据主键索引查询的磁盘操作要多，所以主键索引的查询比根据二级索引查询的快。

三. 索引相关的优化

我们知道，对于数据量较大的表，我们可以通过加索引来提高查询效率。

那哪些情况适合不适合建索引呢？

1. 数据量小，数据量可能只有几百。

2. 离散度不高，像类型、性别这些字段。

3. 不经常用来做条件查询或排序条件的字段。

以上的情况增加索引的索引反而有维护成本，上面讲到新建一个索引就会有一棵索引树生成，每次增删改到索引字段的时候都需要维护索引树（这块MySQL会有个change buffer的优化，在下面会分析）。

下面来讲下索引的一些优化

覆盖索引

select id from student where idno = "555";

这个语句我们是通过二级索引查的是id，所以只需要搜索idno的这棵索引树就可以拿到id字段直接返回，像这种通过查找二级索引而不需要回表的操作叫做覆盖索引，由于覆盖索引减少了树的搜索次数，显著提高了查询性能，所以使用覆盖索引是个常用的优化手段。
比如下面这个场景：需要通过idno查找学生的姓名，这个时候我们可以建个联合索引：

alter table student add key idx_idno_name (idno, name);

但是需要把idno放在联合索引的最左边才可以走到索引，这个是mysql索引的最左前缀原则。

2. 联合索引

联合索引也算一种优化手段，比如我们查询的条件比较多，但是每个条件都建索引的话底层都会新增一棵索引树，所以这个时候我们可以根据多个条件字段建一个联合索引（一个联合索引在底层只维护一棵索引树），但是要注意的是，我们查询条件的顺序需要按照联合索引字段的顺序进行排列，否则是不会走索引的，这个就叫索引的最左前缀原则。

比如下面的语句：

-- 走索引的情况
select * from student where idno = "555";
select * from student where name = "孙七" and idno = "555";
select * from student where idno like "555%"; -- 模糊查询的最左前缀原则
-- 不走索引的情况
select id from student where name = "孙七";
select id from student where name = "孙七" and idno = "555";
select * from student where idno like "%555"; -- 模糊查询的最左前缀原则

模糊查询也同样满足最左前缀原则，%不在前面就可以走到索引，其实最左前缀原则出现的原因也和b+树的数据结构相关，不走索引的情况也有很多种（类型转换、字段函数操作，优化器错误分析，不等于条件查询等）。

3. 索引下推

看下面这个语句:

select * from student where idno like "555%" and name = "孙七";

我们知道了最左前缀原则，这个语句的idno like "555%"可以通过索引查找，但是后面的name呢？

在MySQL 5.6之前，只能根据id回表到主键索引，拿到整条数据去判断name是否等于“孙七”，而在MySQL 5.6以后，引入索引下推的优化，这个优化就是在idno like "555%"匹配到数据后直接和联合索引的name进行比较，无需回表比较，这也算MySQL自身的一种优化。

4. change buffer

MySQL的InnoDB引擎中是有buffer pool的内存空间的，buffer pool的原理及作用我们在后面的文章会详细讲，在这里为简单说一下，就是我们在增删改的时候会把数据先写到内存中，等到后面机器空闲、redo log写满（redo log在后面的文章也会讲，现在你可以理解为持久化所有的写操作保证机器崩溃后重启去恢复buffer pool中还没刷盘的脏页）或机器正常关闭会去刷入磁盘。

比如现在插入一条数据；

update student set idno = "666", name = "吴八", gender  = 2 where id = 5;

按照我们上面的说法是，简单说一下流程应该是这样的：

这个只是主键索引的操作，我们还有一个二级索引idno，你认为还是和上面的操作一样吗？

MySQL对索引的写操作有一个优化：二级索引的修改直接的写入change buffer中，不需要再将数据从磁盘中查出再修改，下次如果要再次查询这条记录的时候就直接从磁盘中查到缓存并执行change buffer里的操作（merge操作）来保证内存和磁盘数据的一致性，除了查询的merge，还会有后台线程定期merge。

这个优化是减少对普通索引操作时的随机读io。

但是唯一索引会用到change buffer的优化的吗？

唯一索引用不到change buffer的，因为唯一索引的特殊性需要检查值是否唯一，所以也要进行随机io查询判断值是否唯一。
所以我们如果能在应用中保证唯一的话就可以使用普通索引而不是唯一索引。刚才上面说了在写入change buffer中后，读会导致merge操作，所以在写后立刻读的场景不适合启用change buffer，这种情景不但没有减少随机io操作，反而还需要维护change buffer，造成资源浪费。

四. 总结

今天我们简单了解了MySQL Innodb引擎索引的实现及原理，以及一些索引相关的优化：覆盖索引、联合索引、索引下推、change buffer，后面这个系列会讲下MySQL中比较重要的日志：实现mvcc多版本以及事务回滚的undo log、防止数据库崩溃内存脏页丢失的redo log、实现主从同步及复制的bin log和relay log，以及数据库中的各种锁原理等。

大家感兴趣的话可以关注下这个系列和我的公众号“阿东编程之路”。