使我日渐消瘦的MySQL索引

若不是生活所迫，我怎会把自己弄得一身才华。

数据结构索引之杀死攻略（一）

mysql 索引

0 那些年我们听过的索引优化手段

• 不用 select *，写明具体查询字段
• 避免在 where 索引字段中使用  != 或 <> 操作符
• 模糊匹配 like %_%，like %_ 可能用不到索引
• 索引并非越多越好
• ......

为了成为一只知其然知其所以然的闲狗，我开始了下面的探索之路。

1 索引可以大幅提升查询效率？

先来个小测试，我先挖来一张有 100 0000 条记录的用户表 user

这张表目前只在 id 上有一个主键索引，其他字段都没有索引。下面在一个没有索引的字段 account_name 做一个查询操作

给这个字段创建一个普通索引，耗时 1.960000s

ALTER TABLE t_user ADD INDEX idx_nick_name(nick_name);

然后再执行相同的查询操作，神速啊 !

2 索引到底是个啥呢？

索引（indexing）：是一种数据结构，是把一个个关键码与它对应的数据记录位置相关联的过程，用来协助快速查询，更新数据库表中数据。

数据结构：计算机存储，组织数据的一种方式。直白地理解,就是研究数据的逻辑结构和存储结构。

索引的出现其实就是为了提高数据查询的效率，就像书的目录一样。一本 500 页的书，如果你想快速找到其中的某一个知识点，在不借助目录的情况下，估计得找一会儿。同样，对于数据库的表而言，索引其实就是它的“目录”。

既然索引是一种数据结构，那它也会以一定的方式来存储自己的数据，并且存储在磁盘上。（索引的缺点）

好比书籍的目录也占用了纸张。

查看表的对象信息，会发现索引也占用大量的存储空间，所以不适合放在内存。

原来没有索引的时候，检索数据时需要遍历整张表的数据，根据每条数据在磁盘存储的顺序，一行一行去对比。

自从有了索引，一切都不一样了。如果在 id 字段建立了索引，id字段的值会被加到索引里，其他字段同理。然后检索的时候，只需要根据 id 的值去检索索引，得到数据的磁盘地址，根据磁盘地址就可以快速地获取到数据。（索引的优点）

3 索引类型

3.1 Normal 普通索引

基本的索引类型，没有唯一性的限制，允许为 NULL 值。

3.2 Unique 唯一索引

索引字段的值必须唯一，但允许有一个 NULL 值。

验证：login_name 为唯一索引，设置两个 NIULL 值会报错。

缺点：唯一索引在插入之前要先遍历一遍数据，保证没有重复，所以会导致插入速度变慢。

3.3 主键索引

3.4 复合索引

复合索引指在多个列上建立索引，复合索引可以替代多个单一索引，相比多个单一索引，复合索引开销更小。

索引同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超过2列的索引，能用窄索引就不用宽索引，因为窄索引往往比组合索引更有效。

3.5 Full text 全文索引

select * from user where match(name) against('aaa');

select * from user where match(name) against('a*' in boolean mode);

4.1 Hash 结构

散列表的检索

Hash 底层通过 Hash 表来实现，通过 <key, value> 存储数据，适用于根据 key 查找 value，但不适用于范围查询，范围查询需要全表扫描。

show engine innodb status; 
show variables like '%innodb_adaptive%';
#关闭
set global innodb_adaptive_hash_index=0;

猜数字游戏：给定一个1～100的自然数，给你5次机会，你能猜中这个数字吗？你会从多少开始猜？

二分查找

上面猜数字的方法可以利用到二分查找思想，而二分查找通常采用二叉树作为逻辑结构，采用顺序存储结构存储在磁盘。

4.2.1 二叉搜索树（Binary Search Tree）

二叉搜索树

BST的中序遍历序列，必然单调非降。

查找的效率决定于树的高度，若树的高度是h，那么查找过程的时间复杂度就是O(h)，等同于二分查找。

极端情况下，时间复杂度会退化到O(n)。如：顺序插入。 BST演示

采用这样的数据结构作为索引的逻辑结构就太恐怖了，pass！

4.2.2 平衡二叉树（AVL Tree - Balance Binary Search Tree）

AVL（平衡二叉搜索）树学习笔记

AVL 演示

特点：左右子树深度差的绝对值不能超过 1。主要通过旋转来维持平衡。

灵魂拷问：用 AVL 作为索引存储数据的逻辑结构可行吗？

先瞅一眼 AVL 的数据存储

每一个节点占用一个磁盘块，存放键值，完整数据所在磁盘块的磁盘地址，子节点的引用。

查找 id = 23 的过程

• 把 id = 26 的结点从磁盘加载到内存，对比键值，26 > 23
• 把 id = 19 的结点从磁盘加载到内存，对比键值，19 < 23
• 把 id = 23 的结点从磁盘加载到内存，对比键值，23 == 23，匹配，通过结点存储的完整数据的磁盘地址，读取对应的数据。

InnoDB 操作磁盘的最小单位是 Page，默认为 16KB（16*1024=16384 B）。即进行一次 I/O 会把 16 KB 的数据从磁盘加载到内存。即每个二叉树结点占用的磁盘大小为 16 KB。16 KB 的结点只存一个键值是不是太浪费了！！！如上图，才 6 条数，查找 id = 23 时就进行了 3 次 I/O 操作。

怎么解决好呢？

难得去一趟磁盘，是不是得多取点数据，即每个结点多存点数据。然后，诞生了 B 树。

4.2.3 多路平衡查找树（Balanced Tree B Tree）

B树学习笔记之B树的查找

动态索引之B树

B树演示

4.2.4 加强版多路平衡查找树（B+ Tree）

动态索引之B+树

B+ 树是 B 树的升级版

（1）非叶子节点不存储数据，只存储索引值，这样便于存储更多的索引值。所以索引字段越小越好，这样一次IO操作获取的数据就越多。

假设 1 条记录 1 KB 大小，一个结点 16KB（16*1024=16384 B）

树高为 3 的 B+ 树，即可以存放 2190 2400条记录。

（2）叶子节点包含了所有的索引值和数据

（3）叶子节点用指针连接，提高区间的访问性能

相比 B 树，B+ 树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可，不用回到根结点。而 B 树需要遍历范围内所有的节点和数据，显然 B+Tree 效率更高。

5 存储引擎的索引实现

5.1 基本概念

官方文档

存储引擎（表类型，表处理器）：负责 MySQL 中数据的存储，提供读写接口。由于数据的存储方式，存储格式和使用方式不同，就会对应产生不同的存储引擎。5.5 版本后默认每张表的存储引擎为 InnoDB。

（1）数据存储方式：有的数据比较稳定，然后存在磁盘；有的数据经常要被访问，需要放在内存。

（2）数据存储格式：经常访问的数据，用 B+ 树的结构去存储；不常访问的数据进行压缩处理，节省空间；有的数据想要看起来比较直观，便于不同系统的交互，就采用 csv 的格式去存储。

（3）数据使用方式不同：有的数据需要有较高的一致性，就需要存储引擎支持事务；有的不需要事务，但需要更高的访问速度，这就需要存储引擎支持 B+ 树。

下面探索一下数据在磁盘以什么样的形式体现

• 创建两张除了存储引擎不同，其它字段都相同的两张表

CREATE TABLE `user_innodb` (
  `Id` int NOT NULL AUTO_INCREMENT,
  `user_id` bigint DEFAULT NULL COMMENT '用户Id',
  `account_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `email` varchar(64) DEFAULT NULL,
  `phone_num` varchar(16) DEFAULT NULL,
  `password` varchar(40) DEFAULT NULL,
  `nick_name` varchar(32) DEFAULT NULL COMMENT '昵称',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

CREATE TABLE `user_myisam` (
  `Id` int NOT NULL AUTO_INCREMENT,
  `user_id` bigint DEFAULT NULL COMMENT '用户Id',
  `account_name` varchar(32) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
  `email` varchar(64) DEFAULT NULL,
  `phone_num` varchar(16) DEFAULT NULL,
  `password` varchar(40) DEFAULT NULL,
  `nick_name` varchar(32) DEFAULT NULL COMMENT '昵称',
  PRIMARY KEY (`Id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

查看 mysql 数据文件存放目录

SHOW VARIABLES LIKE 'datadir';

进入相应的目录，找到以数据库名命名的目录，我的是 test

然后发现，每张表都是以文件的形式存在磁盘。innodb的表有一个文件，而myisam的表有两个文件。

MySQL 8.0 以前，每张表都有一个 frm 文件，用来存放表的元数据（meta），包括表结构的定义等。MySQL 8.0 以后，元数据都存在系统表空间里。

5.2 MyISAM

MyISAM 是 MySQL 自带的存储引擎，但是 MyISAM 没有 crash-safe 的能力，不支持事务。

MyISAM 数据表的索引文件和数据文件是分开存储的，MyISAM 的主键索引与辅助索引没有区别，都存在 .MYI 文件里。

​

5.3 InnoDB

InnoDB 是另一个公司以插件形式引入 MySQL 的，支持事务，有crash-safe 能力，，可以保证即使数据库发生异常重启，之前提交的记录都不会丢失。

5.3.1 索引模型

1）主键索引

聚集索引文档

​

完整数据存放在叶子结点，数据与索引在同一棵树，同一个文件里。完整数据的物理存放顺序与主键一致，这样的索引也称为聚簇索引。聚簇索引决定了数据物理存放顺序。如果一张表没有主键索引，InnoDB 内部会优先去查找没有空值的唯一索引，如果也没有InnoDB 内部会为每一行数据都生成一个 rowID，rowID 构建的索引作为聚簇索引，决定数据的物理顺序。

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index named GEN_CLUST_INDEX on a synthetic column containing row ID values. The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion orde.       

查询下 rowid，是真的有诶！

   ​

2） 辅助索引（普通索引）

​

完整数据只会存放在主键索引的叶子结点，辅助索引的叶子结点存放对应的字段值和主键值。

InnoDB 的辅助索引会比主键索引多扫描一棵 B+ 树

5.4 索引维护 

B+ 树为了维护索引有序性，在插入新值的时候需要做必要的维护，进行一定的分裂或合并，会带来一定的性能问题。

为什么有点规范里要求建表语句里一定要有自增主键？

1）性能层面

自增主键是指自增列上定义的主键，

 NOT NULL PRIMARY KEY AUTO_INCREMENT

插入新记录的时候可以不指定 ID 的值，系统会获取当前 ID 最大值加 1 作为下一条记录的 ID 值。

即每次插入一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂。

而有业务逻辑的字段做主键，则往往不容易保证有序插入，这样写数据成本相对较高。在分布式系统里，不采用 UUID 作为分布式 ID，也是由于 UUID 是无序的，会引起索引节点的频繁变动，影响性能。 

2）存储层面

假设你的表中确实有一个唯一字段，比如字符串类型的身份证号，那应该用身份证号做主键，还是用自增字段做主键呢？

由于每个非主键索引的叶子节点上都是主键的值。如果用身份证号做主键，那么每个二级索引的叶子节点占用约 20 个字节，而如果用整型做主键，则只要 4 个字节，如果是长整型（bigint）则是 8 个字节。显然，主键长度越小，辅助索引的叶子节点就越小，辅助索引占用的空间也就越小。

MySQL官方也有明确的建议主键要尽量越短越好

All indexes other than the clustered index are known as secondary indexes. In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. InnoDB uses this primary key value to search for the row in the clustered index.If the primary key is long, the secondary indexes use more space, so it is advantageous to have a short primary key.

6 索引分析与优化

6.1 EXPLAIN

CREATE TABLE `t_resume` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `address` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `phone` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_address` (`address`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

EXPLAIN SELECT * from t_resume WHERE address = "3";

6.2 列的离散度

离散度公式：count(distinct(column_name)):count(*)  某列不重复值的个数/总数。

实战一下

​

gender 的离散度要小于 phone

（1）在 gender 字段创建索引

​

​

将近扫描了一半的数据。

检索数据时，因为重复值多，所以扫描的行数也多。如果在 B+Tree 里面的重复值太多，MySQL 的优化器发现走索引跟使用全表扫描差不多的时候，就算建了索引，也不一定会走索引。

（2）在 phone 字段创建索引

​

只需扫描一行。

闲狗言：

优先选择离散度高的字段建立索引

6.3 回表与覆盖索引

6.3.1 回表

mysql> create table T (
ID int primary key,
k int NOT NULL DEFAULT 0, 
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');

思考一下：执行 select * from T where k between 3 and 5，需要执行几次树的搜索操作，会扫描多少行？

​

执行流程：

• 在 k 索引树上找到 k=3 的记录，取得 ID = 300；
• 再到 ID 索引树查到 ID=300 对应的 R3；
• 在 k 索引树取下一个值 k=5，取得 ID=500；
• 再回到 ID 索引树查到 ID=500 对应的 R4；
• 在 k 索引树取下一个值 k=6，不满足条件，循环结束。

在这个过程中，回到主键索引树搜索的过程，称为回表。

InnoDB 的完整数据都在存放在主键索引的叶子节点的，查询主键以外的字段时，需要先查询辅助索引，然后再查询主键索引获取完整数据，比直接查询主键索引多扫描了一棵 B+ 树。

如何避免回表？

6.3.2 覆盖索引

如果执行的语句是 select ID from T where k between 3 and 5，这时只需要查 ID 的值，而 ID 的值已经在 k 索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引 k 已经“覆盖了”我们的查询需求，称为覆盖索引。实现索引覆盖最常见的方法就是将被查询的字段，建立到组合索引。

实战一下

ALTER TABLE t_user ADD INDEX idx_name_phone(name,phone);

当 select name,phone 时，不需要主键索引里的完整数据，直接查 name 和 phone 的辅助索引就可以获取到 name 和 phone 的值，不用回表。

当 extra 出现 Using index 则表示用到了覆盖索引

（2）

explain select * from t_user where name="tanjinhai";

​

（3）

explain select name,phone from t_user where name="屿昔" and phone="15505688900";

​

（4）

explain select phone from t_user where phone="17665453887";

​

要查的 phone 虽然在辅助索引里，但是违背了联合索引最左匹配原则。MySQL 优化器判断了下回表的代价和直接在辅助索引中过滤数据的代价，还是选择了不回表。

6.4 索引重建优化

对于上面例子中的 InnoDB 表 T，索引可能因为删除，或者页分裂等原因，导致数据页有空洞，重建索引的过程会创建一个新的索引，把数据按顺序插入，这样页面的利用率最高，也就是索引更紧凑、更省空间。

如果要重建索引 k，你的两个 SQL 语句可以这么写：

alter table T drop index k;
alter table T add index(k);

如果你要重建主键索引，也可以这么写：

alter table T drop primary key;
alter table T add primary key(id);

通过两个 alter 语句重建索引 k，以及通过两个 alter 语句重建主键索引是否合理？

重建索引 k 的做法是合理的，可以达到省空间的目的。但是，重建主键的过程不合理。不论是删除主键还是创建主键，都会将整个表重建。所以连着执行这两个语句的话，第一个语句就白做了。这两个语句，可以用这个语句代替 ： alter table T engine=InnoDB。

6.5 最左匹配原则

​

ALTER TABLE t_user ADD INDEX idx_name_phone(name,phone);

​

（2）

explain select * from t_user where name="tanjinhai" and phone="15920690159";

​

（3）

explain select * from t_user where phone="15920690159" and name="tanjinhai";

​

居然还能用到索引，这不是违背了联合索引最左匹配原则吗？我跟你港，其实 MySQL 内部有一个查询优化器 Optimizer， 它会自动帮我们调整下查询条件的顺序。

（4）

explain select * from t_user where name="tanjinhai";

​

（5）

explain select * from t_user where phone="15920690159";

​

6.5 LIKE 查询

（1）索引失效

EXPLAIN SELECT * from t_resume WHERE phone like "%176%";

​

（2）索引失效

EXPLAIN SELECT * from t_resume WHERE phone like "%176";

​

（3）索引有效

EXPLAIN SELECT * from t_resume WHERE phone like "176%";

​

查找到第一个符合条件的记录后，通过指针向后遍历，直到不满足条件为止。

6.6 NULL 查询

select length(NULL), length(''), Length(' ');

​

​

指定列为 NOT NULL，除非你想存储 NULL。在 MySQL 中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。应该使用-1，0 等特殊值去替代空值，如果是datetime类型，也可以设置系统当前时间或某个固定的特殊值，例如 '1970-01-01 00:00:00'。

If an indexed column cannot contain any NULL values, declare it as NOT NULL when you create the table. The optimizer can better determine which index is most effective to use for a query, when it knows whether each column contains NULL values.

官网文档

6.7 排序

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

mysql> CREATE TABLE `tradelog` (
  `id` int(11) NOT NULL,
  `tradeid` varchar(32) DEFAULT NULL,
  `operator` int(11) DEFAULT NULL,
  `t_modified` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `tradeid` (`tradeid`),
  KEY `t_modified` (`t_modified`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

mysql> select count(*) from tradelog where month(t_modified)=7;

mysql> select count(*) from tradelog where
    -> (t_modified >= '2016-7-1' and t_modified<'2016-8-1') or
    -> (t_modified >= '2017-7-1' and t_modified<'2017-8-1') or 
    -> (t_modified >= '2018-7-1' and t_modified<'2018-8-1');

6.8 索引并非越多越好

• 大部分情况下，索引能大幅度提高查询效率，但是数据的变更（增删改）都需要维护索引，更多的索引意味着更多的维护成本，更多的索引也意味着需要更多的空间。好比，一本 100 页的书，却有 50 页目录？
• 过小的表，建立索引后查询效率可能更低。好比，读个 2 页的宣传手册，你还要先去查目录？​