如何用好MySQL索引

发布时间：2023-12-31 18:10:48 所属栏目：MySql教程来源：DaWei

导读： 本篇内容主要讲解“怎么用好MySQL索引”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么用好MySQL索引”吧!

为了

本篇内容主要讲解“怎么用好MySQL索引”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让小编来带大家学习“怎么用好MySQL索引”吧!

为了更好地进行解释，我创建了一个存储引擎为InnoDB的表user_innodb，并批量初始化了500W+条数据。包含主键id、姓名字段（name）、性别字段（gender，用0，1表示不同性别）、手机号字段（phone），并为name和phone字段创建了联合索引。

CREATE TABLE `user_innodb` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`gender` tinyint(1) DEFAULT NULL,
`phone` varchar(11) DEFAULT NULL,
PRIMARY KEY (`id`)，
INDEX IDX_NAME_PHONE (name, phone)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

1. 索引的代价
索引可以非常有效地提升查询效率，既然这么好，我给每个字段都创建一个索引行不行？我劝你不要冲动。

任何事情都有两面，索引也不例外。过度使用索引，我们在空间和时间上都会付出相应的代价。

1.1 空间上的代价
索引就是一棵B+数，每创建一个索引都需要创建一棵B+树，每一棵B+树的节点都是一个数据页，每一个数据页默认会占用16KB的磁盘空间，每一棵B+树又会包含许许多多的数据页。所以，大量创建索引，你的磁盘空间会被迅速消耗。

1.2 时间上的代价
空间上的代价你可以使用“钞能力”来解决，但时间上的代价我们可能就束手无策了。

我以主键索引为例举个例子，主键索引的B+树的每一个节点内的记录都是按照主键值由小到大的顺序，采用单向链表的方式进行连接的。如下图所示：

如果我现在要删除主键id为1的记录，会破坏3个数据页内的记录排序，需要对这3个数据页内的记录进行重排列，插入和修改操作也是同理。

注：这里给大家提一嘴，其实删除操作并不会立即进行数据页内记录的重排列，而是会给被删除的记录打上一个删除的标识，等到合适的时候，再把记录从链表中移除，但是总归需要涉及到排序的维护，势必要消耗性能。

假如这张表有12个字段，我们为这张表的12个字段都设置了索引，我们删除1条记录，需要涉及到12棵B+树的N个数据页内记录的排序维护。

更糟糕的是，你增删改记录的时候，还可能会触发数据页的回收和分裂。还是以上图为例，假如我删除了id为13的记录，那么数据页124就没有存在的必要了，会被InnoDB存储引擎回收；我插入一条id为12的记录，如果数据页32的空间不足以存储该记录，InnoDB又需要进行页面分裂。我们不需要知道页面回收和页面分裂的细节，但是能够想象到这个操作会有多复杂。

执行查询语句之前，MySQL查询优化器会基于cost成本对一条查询语句进行优化，并生成一个执行计划。如果创建的索引太多，优化器会计算每个索引的搜索成本，导致在分析过程中耗时太多，最终影响查询语句的执行效率。

2. 回表的代价
2.1 什么是回表
我再乱槐槭裁词腔乇恚颐强梢酝ü端饕业B+树中的叶子结点，但是二级索引的叶子节点的内容并不全，只有索引列的值和主键值。我们需要拿着主键值再去聚簇索引（主键索引）的叶子节点中去拿到完整的用户记录，这个过程叫做回表。

2.2 回表的代价
我们根据name字段查找二级索引的叶子节点的代价还是比较小的，原因有二：

叶子节点所在的页通过双向链表进行关联，遍历的速度比较快；

MySQL会尽量让同一个索引的叶子节点的数据页在磁盘空间中相邻，尽力避免随机IO。

（编辑：常州站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!