MySql索引

发表于 更新于 分类于 ， 置顶 精品
阅读次数： 0 本文字数： 9.7k 阅读时长 ≈ 35 分钟

摘要

常见面试题

索引相关

B+Tree(B-Tree变种)

  • 非叶子节点不存储data，只存储索引(冗余)，目的是为了放更多的索引，减少树的高度，提高查询效率，非叶子结点由主键值和一个指向下一层的地址的指针组成。

  • 叶子节点包含所有索引字段，聚集索引包含全部字段，非聚集索引包含索引中的字段，叶子结点中由一组键值对和一个指向该层下一页的指针组成，键值对存储的主键值和数据

  • 叶节点之间通过双向链表链接，提高区间访问的性能

  • 在B+树中，一个结点就是一页，MySQL中InnoDB页的大小默认是16k，Innodb的所有数据文件（后缀为 ibd 的文件），其大小始终都是 16384（16k）的整数倍。

1
2
3
4
5
6
mysql> SHOW VARIABLES LIKE 'innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+

计算机在存储数据的时候，最小存储单元是扇区，一个扇区的大小是 512 字节，而文件系统（例如 XFS/EXT4）最小单元是块，一个块的大小是 4KB。InnoDB 引擎存储数据的时候，是以页为单位的，每个数据页的大小默认是 16KB，即四个块。

B+Tree可以存放多少条数据，为什么是2000万？

  • 指针在InnoDB中为6字节，设主键的类型是bigint，占8字节。一组就是14字节。

  • 计算出一个非叶子结点可以存储16 * 1024 / 14 = 1170个索引指针。

  • 假设一条数据的大小是1KB，那么一个叶子结点可以存储16条数据。

  • 两层B+树可以存储1170 x 16 = 18720条数据。

  • 三层B+树可以存储1170 x 1170 x 16 = 21902400条数据，约为2000万。

  • 四层B+树可以存储1170 x 1170 x 1170 x 16 = 25625808000条数据，约为256亿。

  • 如不考虑磁盘IO，B+树的查找与其层数（树的高度）和阶数（节点的最大分支数）有关，因为数据都在叶子节点，所以查找数据必须从树的根节点开始，通过2分法定位其所在的分支，一层一层的查找（每层都是2分法定位其所在的分支），直到最后一层的叶子节点定位数据。

  • 在查找不超过3层的B+Tree中的数据时，一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

  • 一般不建议单表的数据量超过2000万，因为每查找一个页，都要进行一次IO，而磁盘的速度相比内存而言是非常的慢的，比如常见的7200RPM的硬盘，摇臂转一圈需要60/7200≈8.33ms,换句话说，让磁盘完整的旋转一圈找到所需要的数据需要8.33ms,这比内存常见的100ns慢100000倍左右，这还不包括移动摇臂的时间。所以在这里制约查找速度的不是比较次数，而是IO操作的次数。

B+Tree查找的时间复杂度（比较次数）计算方法

  • B+Tree的查找时间复杂度的计算方法是每层通过2分法查找的次数M * 树的高度H

  • 假设B+Tree中总的数据量为N，阶数为R，则 M = log2R，H = logRN，则M * H = log2R * logRN = log2N

  • 举例，比如有一课B+Tree的总的数据量是65536，最大分支为16，则N=65536，R=16，M = log2R = log216 = 4，H = logRN = log1665536 = 4，则 M * H = log2N = log265536 = 16，即最多查找16次就可以找到对应的数据

  • 注意这里仅是计算内存中查找的比较次数，而没有考虑每次加载数据页到内存的IO成本，而实际上IO成本才是制约mysql查找快慢的关键因素，所以mysql每次IO都会将查询页附近的几个页一并加载到内存，以此减少IO次数

为什么MySql默认使用 B+Tree 而不是 B Tree/Hash/二叉树?

  • 先明确前提：索引文件存在磁盘上，一次查询的耗时主要花在磁盘IO次数上，而不是内存中的比较次数（磁盘一次寻道约8.33ms，内存一次访问约100ns，差了10万倍）。而InnoDB读写磁盘的最小单位是16KB的页，一次IO就是一页。所以索引结构的选型标准是：树要足够矮（IO次数少）、单次IO要尽可能多带回有效信息（扇出大）、要天然支持范围查询与排序（SQL的高频需求）。B+Tree是这三点同时满足得最好的结构。

  • 为什么不用二叉查找树(BST)？

    • 二叉树每个节点只有2个分支，扇出为2，树高为log2N。2000万数据的树高约为24层，即最坏需要24次IO，而3层B+Tree只需要1-3次。
    • 一个节点远小于16KB，一次IO读回一整页却只能拿到1个键值，磁盘预读的收益几乎被浪费。
    • 更致命的是BST不保证平衡，而主键往往是自增的，顺序插入会让BST退化成一条单向链表，查询复杂度从O(log2N)退化为O(N)，等同于全表扫描。

  • 为什么不用平衡二叉树(AVL树/红黑树)？

    • 它们通过旋转解决了退化成链表的问题，查询复杂度稳定在O(log2N)，这在内存中是很优秀的结构（Java的TreeMap用的就是红黑树）。
    • 但本质仍是二叉，扇出还是2，树高问题一点没解决：数据量越大树越高，IO次数越多。红黑树只是近似平衡，最长路径可达最短路径的2倍，高度还会更差一些。
    • 另外自增主键的顺序插入会频繁触发旋转，维护成本也不低。
    • 结论：AVL/红黑树是面向内存的结构，B树系是面向磁盘的结构，两者的优化目标不同。

  • 为什么不用B Tree？

    • B Tree已经是多叉平衡树，树高问题基本解决了，B+Tree也正是它的变种。差别在于：B Tree的每个节点（包括非叶子节点）都要存data。
    • 页大小固定为16KB，节点里存了data就放不下多少键值，扇出被大幅拉低，树又变高了。以上文的计算为例：非叶子节点只存主键(8字节)+指针(6字节)时扇出是1170，如果每条记录带1KB的data，扇出就只剩16左右，同样存2000万数据，B+Tree需要3层，B Tree则需要6层以上。
    • B Tree的叶子节点之间没有指针相连，做范围查询（between、>、order by、分页）时必须中序遍历，不断回溯父节点再向下，产生大量随机IO；而B+Tree数据全在叶子层，且叶子间是双向链表，只需定位到起点然后顺序扫描链表即可，是顺序IO。
    • B Tree的数据可能出现在任意层，查询性能不稳定（命中根节点很快，命中叶子很慢）；B+Tree所有查询都要走到叶子节点，路径长度一致，性能稳定可预测，便于优化器估算成本。

  • 为什么不用Hash索引？

    • Hash的等值查询理论上是O(1)，比B+Tree还快，但它的缺陷让它无法作为通用索引：
    • 不支持范围查询：hash运算会打乱数据的顺序，>、<、between都只能退化成全表扫描。
    • 不支持排序：order by、group by无法利用索引，需要额外的排序（filesort）。
    • 不支持最左前缀匹配：联合索引的hash值是所有字段一起算出来的，无法只用前面几个字段查询。
    • 不支持模糊匹配：like 'abc%'这种前缀匹配用不上。
    • 存在hash冲突：数据倾斜严重时，冲突链会很长，极端情况下退化为O(N)；且要维护冲突链和扩容rehash。
    • 所以Hash索引只适合等值查询且值唯一的场景（如Memory引擎默认使用hash索引）。但InnoDB并没有完全抛弃它，而是用自适应哈希索引(AHI)在内存中对热点索引页自动建hash，作为B+Tree的补充而非替代。

  • 汇总对比

数据结构 树高/扇出 等值查询 范围查询/排序 稳定性 是否适合磁盘
二叉查找树 扇出2，高度大 O(log2N)，可能退化为O(N) 支持但需中序遍历回溯 差，顺序插入退化为链表 否
AVL树/红黑树 扇出2，高度大 稳定O(log2N) 支持但需中序遍历回溯 好 否，面向内存
B Tree 多叉，但节点存data导致扇出小、树偏高 快，但层数不定 支持，但叶子无链表，随机IO多 一般，命中层数不同耗时不同 一般
Hash 无树高概念 O(1)最快 不支持 一般，受hash冲突影响 一般，仅作辅助
B+Tree 非叶子只存索引，扇出约1170，3层可存2000万 1-3次IO 叶子双向链表，顺序IO，天然有序 好，所有查询路径等长 是

一句话总结：B+Tree把非叶子节点腾空只放索引换来了极大的扇出和极矮的树，从而把IO次数压到1-3次；又用叶子节点双向链表把范围查询和排序变成了顺序扫描；再配合聚簇索引让叶子节点直接就是数据页，完美贴合磁盘按页读取的特性。这是二叉树（树太高）、B Tree（扇出小、无链表）、Hash（不支持范围和排序）都做不到的。

索引分类

  • 聚集索引/聚簇索引/密集索引
    InnoDB中使用了聚集索引，就是将表的主键用来构造一棵B+树，并且将整张表的行记录数据存放在该B+树的叶子节点中。也就是所谓的索引即数据，数据即索引。
    由于聚集索引是利用表的主键构建的，所以每张表只能拥有一个聚集索引。
    聚集索引的叶子节点就是数据页。换句话说，数据页上存放的是完整的每行记录。
    因此聚集索引的一个优点就是：通过聚集索引能获取完整的整行数据。
    另一个优点是：对于主键的排序查找和范围查找速度非常快。
    如果我们没有定义主键呢？MySQL会使用唯一性索引，没有唯一性索引，MySQL也会创建一个隐含列RowID来做主键，然后用这个主键来建立聚集索引。

  • 辅助索引/二级索引/非聚集索引/稀疏索引
    上边介绍的聚簇索引只能在搜索条件是主键值时才能发挥作用，因为B+树中的数据都是按照主键进行排序的,那如果我们想以别的列作为搜索条件怎么办？
    我们一般会建立多个索引，这些索引被称为辅助索引/二级索引，辅助索引也是一颗B+树。
    对于辅助索引(Secondary Index，也称二级索引、非聚集索引)，叶子节点并不包含行记录的全部数据。
    叶子节点除了包含键值以外，每个叶子节点中的索引行中还包含了相应行数据的聚集索引主键，用于回表查询。
    辅助索引的存在并不影响数据在聚集索引中的组织，因此每张表上可以有多个辅助索引，有几个辅助索引就会创建几颗B+树。
    MyISAM存储引擎，不管是主键索引，唯一键索引还是普通索引都是非聚集索引。

当通过辅助索引来寻找数据时，InnoDB存储引擎会遍历辅助索引并通过叶级别的指针获得指向主键索引的主键，然后再通过主键索引（聚集索引）来找到一个完整的行记录。这个过程也被称为回表。
也就是根据辅助索引的值查询一条完整的用户记录需要使用到2棵B+树–一次辅助索引，一次聚集索引。

  • 联合索引/复合索引
    前面我们对索引的描述，隐含了一个条件，那就是构建索引的字段只有一个，但实践工作中构建索引的完全可以是多个字段。
    所以，将表上的多个列组合起来进行索引我们称之为联合索引或者复合索引，比如index(a,b)就是将a,b两个列组合起来构成一个索引。
    联合索引只会建立1棵B+树。

  • 自适应哈希索引(Adaptive Hash Index,AHI)
    由mysql自己维护，对于经常被访问的索引，mysql会创建一个hash索引，下次查询这个索引时直接定位到记录的地址，而不需要去B+树中查询。
    AHI默认开启，由innodb_adaptive_hash_index变量控制，默认8个分区,最大设置为512。

1
2
3
4
5
6
7
8
9
10
11
12
mysql> show variables like 'innodb_adaptive_hash_index';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON    |
+----------------------------+-------+
mysql> show variables like 'innodb_adaptive_hash_index_parts';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| innodb_adaptive_hash_index_parts | 8     |
+----------------------------------+-------+

通过show engine innodb status\G命令可以查看AHI的使用情况

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
mysql> show engine innodb status\G

-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
 insert 0, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 4 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 0 buffer(s)
Hash table size 276707, node heap has 1 buffer(s)
Hash table size 276707, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

  • 全文检索之倒排索引(FULLTEXT)
    数据类型为 char、varchar、text 及其系列才可以建全文索引。
    每张表只能有一个全文检索的索引
    不支持没有单词界定符(delimiter）的语言，如中文、日语、韩语等。
    由于mysql的全文索引功能很弱，这里不做详细介绍，推荐使用ES等专业的搜索引擎。

MySQL有哪些索引类型?

  • 从数据结构角度可分为B+树索引、哈希索引、以及FULLTEXT索引（现在MyISAM和InnoDB 引擎都支持了）和R-Tree索引（用于对GIS数据类型创建SPATIAL索引）；
  • 从物理存储角度可分为聚集索引（clustered index）、非聚集索引（non-clustered index）；
  • 从逻辑角度可分为主键索引、普通索引，或者单列索引、多列索引、唯一索引、非唯一索引等等。

覆盖索引/索引覆盖

  • InnoDB存储引擎支持覆盖索引(covering index，或称索引覆盖)，即从辅助索引中就可以得到查询的记录，而不需要查询聚集索引中的记录。

  • 使用覆盖索引的一个好处是辅助索引不包含整行记录的所有信息，故其大小要远小于聚集索引，因此可以减少大量的IO操作。

  • 覆盖索引可以视为索引优化的一种方式，而并不是索引类型的一种。

  • 除了覆盖索引这个概念外，在索引优化的范围内，还有前缀索引、三星索引等。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
> show create table employees\G
***************************[ 1. row ]***************************
Table        | employees
Create Table | CREATE TABLE `employees` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb3 COMMENT='员工记录表'

> EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';
+----+-------------+-----------+------------+------+-----------------------+--------+---------+--------+-------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys         | key    | key_len | ref    | rows  | filtered | Extra       |
+----+-------------+-----------+------------+------+-----------------------+--------+---------+--------+-------+----------+-------------+
| 1  | SIMPLE      | employees | <null>     | ALL  | idx_name_age_position | <null> | <null>  | <null> | 92796 | 0.5      | Using where |
+----+-------------+-----------+------------+------+-----------------------+--------+---------+--------+-------+----------+-------------+

> EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+--------------------------+
| id | select_type | table     | partitions | type  | possible_keys         | key                   | key_len | ref    | rows  | filtered | Extra                    |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+--------------------------+
| 1  | SIMPLE      | employees | <null>     | range | idx_name_age_position | idx_name_age_position | 74      | <null> | 46398 | 1.0      | Using where; Using index |
+----+-------------+-----------+------------+-------+-----------------------+-----------------------+---------+--------+-------+----------+--------------------------+

前缀索引

如果索引的字段类型很长，如varchar(255)，此时创建的索引就会非常大，而且维护起来也非常慢，此时建议使用前缀索引，就是只对该字段的前面一些字符进行索引。
阿里的Java编程规范中也提到，在varchar上建立索引时，必须指定索引长度，没必要对全字段建立索引，建议索引的长度不超过20。
可以使用select count(distinct left(列名, 索引长度))/count(*) from tableName的区分度来确定，一般大于90%即可。

VARCHAR(50) 和 VARCHAR(500) 的区别是什么？

  • 先破一个常见误区：InnoDB 里 VARCHAR 是变长存储，括号里的数字是字符数上限，不是预分配的固定宽度（那是 CHAR）。同样写入 'hello'，VARCHAR(50) 和 VARCHAR(500) 在磁盘上几乎一样大，并不是后者多占 10 倍空间。utf8mb4 下一个汉字、一个字母、一个 emoji 都算 1 个字符。

  • 真正的差别有这些：

    • 约束不同：50 最多存 50 个字符，500 最多 500 个。超出在严格 SQL 模式下会报错，而不是默默截断。
    • 长度前缀不同：InnoDB 用 1 或 2 字节记录实际长度。最大可能字节数 ≤255 用 1 字节，否则用 2 字节。utf8mb4 下 VARCHAR(50) 最多 50 × 4 = 200 字节，1 字节前缀；VARCHAR(500) 最多 2000 字节，2 字节前缀。所以同样存 'hello'，后者只多 1 个长度字节。
    • 对索引的影响才是这篇文档真正该关心的：二级索引叶子存的是「索引列实际值 + 主键」，非叶子存「键 + 6 字节指针」。键越长，一页装得越少，扇出越小，树越高，IO 越多。

  • 用上文 B+Tree 的扇出公式对比一下（页 16KB，utf8mb4，键打满时）：

    • 主键 bigint：8 + 6 = 14 字节，扇出约 1170，三层能存约 2000 万。
    • VARCHAR(50) 打满：约 50 × 4 + 1 = 201 字节，一页大概 80 个键。
    • VARCHAR(500) 打满：约 500 × 4 + 2 = 2002 字节，一页只能装 8 个键左右。这种扇出下，树会迅速变高，前面「三层 2000 万」的估算完全不成立。
    • 实际字符串很短时，两种声明的索引体积接近；一旦业务真的写入长串，VARCHAR(500) 的索引会迅速膨胀。这就是上一段「varchar 上必须指定索引长度、建议不超过 20」的原因。

  • 另外两个和索引相关的硬限制：

    • InnoDB 8.0 默认 DYNAMIC、16KB 页，单列索引键上限是 3072 字节。utf8mb4 下 VARCHAR(768) 刚好打满（768 × 4 = 3072），VARCHAR(500) 单列还能建全字段索引；但联合索引里多个长 VARCHAR 很容易超限，只能改前缀索引。
    • EXPLAIN 的 key_len 按声明的最大长度估算，不是按行里实际字符串。utf8mb4 非空 VARCHAR(n) 大约是 n × 4 + 2，所以 VARCHAR(500) 的 key_len 会到 2002，优化器会觉得这个索引更「重」。

  • 两个延伸坑，面试里偶尔会追问：

    • 排序/临时表：老版本 MEMORY 引擎会把 VARCHAR 升成 CHAR(声明长度)，utf8mb4 的 VARCHAR(500) 每行按 2000 字节算，filesort / join 更吃内存。MySQL 8.0 默认 TempTable 引擎已按变长处理，这个坑小了很多。
    • DDL：utf8mb4 下 VARCHAR(50) → VARCHAR(500) 会跨过 255 字节边界（200 → 2000），长度前缀从 1 字节变成 2 字节，不能 INPLACE / INSTANT，要 COPY 重建表。同侧扩容（都不跨 255 字节，比如 VARCHAR(10) → VARCHAR(50)）才是原地修改。

一句话：存短字符串时磁盘差不多；真正贵的是「允许写更长」之后索引键变长、扇出下降。能 VARCHAR(50) 就不要开到 500，长字段用前缀索引。

三星索引

  • 一星(缩小查询范围): 索引将相关的记录放到一起则获得一星，即索引的扫描范围越小越好；

  • 二星(排序): 如果索引中的数据顺序和查找中的排列顺序一致则获得二星，即当查询需要排序，group by、 order by，查询所需的顺序与索引是一致的（索引本身是有序的）；

  • 三星(覆盖索引): 如果索引中的列包含了查询中需要的全部列则获得三星，即索引中所包含了这个查询所需的所有列（包括 where 子句 和 select 子句中所需的列，也就是覆盖索引）。

注意

  • 一个索引就是一个B+树，索引让我们的查询可以快速定位和扫描到我们需要的数据记录上，加快查询的速度。

  • 一个select查询语句在执行过程中一般最多能使用一个二级索引来加快查询，即使在where条件中用了多个二级索引。

索引的代价

  • 空间上的代价
    这个是显而易见的，每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成会占据很多的存储空间。

  • 时间上的代价
    每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。B+树每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。
    不论是叶子节点中的记录，还是非叶子内节点中的记录都是按照索引列的值从小到大的顺序而形成了一个单向链表。
    而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收的操作来维护好节点和记录的排序。
    如果我们建了许多索引，每个索引对应的B+树都要进行相关的维护操作，这必然会对性能造成影响。

所以，索引虽然可以加快我们的查询效率，但也不是创建的越多越好，一般来说，一张表不要超过7个索引为宜。

一张表最多支持多少个索引？

  • 硬上限和推荐值要分开说。官方文档（InnoDB Limits）写的是：一张 InnoDB 表最多 64 个二级索引。聚簇索引（主键）另算，InnoDB 每张表都有且只有一棵聚簇索引树。超限会报 ERROR 1069 (42000): Too many keys specified; max 64 keys allowed。
  • 服务层还有一个编译常量 MAX_INDEXES，官方二进制默认也是 64，并且把 PRIMARY KEY、唯一索引、普通索引、全文、空间索引都算进去。所以日常 SHOW INDEX 里看到的条数（按 Key_name 去重）通常到不了 65。没有显式主键时，InnoDB 会藏一个 GEN_CLUST_INDEX，这个不占用户索引名额。
  • 几个常被一起追问的上限（mysql-8.0.30 / 默认 16KB 页 / DYNAMIC）：
    • 单个索引最多 16 列（ERROR 1070: Too many key parts specified; max 16 parts allowed）
    • 单个索引键最长 3072 字节（utf8mb4 下全字段大约 VARCHAR(768)），COMPACT/REDUNDANT 是 767 字节
    • 全文索引每张表只能有一个，见上文「全文检索」
    • 函数索引会占一个普通索引名额，同时也占一列隐藏虚拟列，受表 1017 列上限约束
  • 能建 64 个不代表该建 64 个。每多一个索引就是多一棵 B+树：占磁盘、拖慢 INSERT/UPDATE/DELETE，优化器选索引也更慢。上文「索引的代价」里写的 一张表不要超过 7 个为宜 才是工程答案；线上宁可少建、用联合索引覆盖多条 SQL，也不要按列无脑各建一个。

面试可以这么答：InnoDB 硬上限是 64 个二级索引，单个索引最多 16 列、键长 3072 字节；真正该遵守的是每张表尽量不超过 7 个，因为维护成本才是瓶颈，不是这个数字。

索引的创建与删除

  • 查看索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
mysql> desc actor;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| id          | int         | NO   | PRI | NULL    |       |
| name        | varchar(45) | YES  |     | NULL    |       |
| update_time | datetime    | YES  | MUL | NULL    |       |
+-------------+-------------+------+-----+---------+-------+

mysql> show index from actor;
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table | Non_unique | Key_name               | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| actor | 0          | PRIMARY                | 1            | id          | A         | 2           | <null>   | <null> |      | BTREE      |         |               | YES     | <null>     |
| actor | 0          | unique_name_time_order | 1            | name        | D         | 3           | 15       | <null> | YES  | BTREE      |         |               | YES     | <null>     |
| actor | 0          | unique_name_time_order | 2            | update_time | A         | 3           | <null>   | <null> | YES  | BTREE      |         |               | YES     | <null>     |
| actor | 1          | index_update_time      | 1            | update_time | A         | 1           | <null>   | <null> | YES  | BTREE      |         |               | YES     | <null>     |
| actor | 1          | index_name             | 1            | name        | A         | 3           | 15       | <null> | YES  | BTREE      |         |               | YES     | <null>     |
| actor | 1          | index_name_desc        | 1            | name        | D         | 3           | 15       | <null> | YES  | BTREE      |         |               | YES     | <null>     |
+-------+------------+------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
参数说明：
Table: 表名称
Non_unique: 是否为唯一索引，0表示唯一索引，1表示非唯一索引
Key_name: 索引名称
Seq_in_index: 联合索引中的字段顺序，从1开始计算
Column_name: 字段名称
Collation: 表示索引是升序还是降序，默认创建索引是升序A，降序为D

  • 已有表索引维护

创建索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
# 方式1
create index index_name on actor (name(15));
# 创建降序索引，默认升序
create index index_name_desc on actor (name(15) desc);
create unique index unique_name on actor (name(15));
create unique index unique_name_time on actor (name(15),update_time);
create unique index unique_name_time_order on actor (name(15) desc,update_time asc);

# 方式2
alter table actor add index index_name(name(15));
alter table actor add index index_name(name(15) desc);
alter table actor add unique index unique_name(name(15));
alter table actor add unique index unique_name_time(name(15),update_time);
alter table actor add unique index unique_name_time_order(name(15) desc,update_time asc);

删除索引

1
2
3
4
5
6
7
8
9
10
11
# 方式1
drop index index_name on actor;
drop index unique_name on actor;
drop index unique_name_time on actor;

# 方式2
alter table actor drop index index_name;
alter table actor drop index unique_name;
alter table actor drop index unique_name_time;
# 同时删除多个索引
alter table actor drop index index1,drop index index2,drop index index3;

函数索引

  • mysql8.0.13及以后的版本开始支持函数式索引，即创建索引的时候可以使用mysql提供的函数（不支持自定义函数）

1
2
3
4
5
6
7
8
9
10
# 注意，创建函数索引时，要在外层有一对括号，表示表达式
alter table actor add index index1((upper(name)));
# 前缀
alter table actor add index index1((upper(left(name,15))));
# 排序
alter table actor add index index2((upper(name)) desc);
# 联合索引，函数索引+普通索引
alter table actor add index index3((upper(name)) desc,update_time asc);
# 联合索引，函数索引+函数索引
alter table actor add index index4((upper(name)) desc,(year(update_time)) asc);

注意查询时也要使用函数才能使用索引

1
2
3
4
5
6
explain select * from actor where upper(name) = 'A';
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+--------+
| 1  | SIMPLE      | actor | <null>     | ref  | index3        | index3 | 138     | const | 1    | 100.0    | <null> |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+--------+

函数索引的限制条件

  • 函数索引实际上是作为一个隐藏的虚拟列实现的，因此其很多限制与虚拟列相同，如下：
  • 函数索引的字段数量受到表的字段总数限制
  • 函数索引能够使用的函数与虚拟列上能够使用的函数相同
  • 子查询，参数，变量，存储过程，用户定义的函数不允许在函数索引上使用
  • 虚拟列本身不需要存储，函数索引和其他索引一样需要占用存储空间
  • 函数索引可以使用 UNIQUE 标识，但是主键不能使用函数索引，主键要求被存储，但是函数索引由于其使用的虚拟列不能被存储，因此主键不能使用函数索引
  • 如果表中没有主键，那么 InnoDB 将会使其非空的唯一索引作为主键，因此该唯一索引不能定义为函数索引
  • 函数索引不允许在外键中使用
  • 空间索引和全文索引不能定义为函数索引
  • 对于非函数的索引，如果创建相同的索引，将会有一个告警信息，而函数索引则不会
  • 如果一个字段被用于函数索引，那么删除该字段前，需要先删除该函数索引，否则删除该字段会报错

  • 函数索引实际上就是mysql帮我们在表上创建了一个隐藏的虚拟列，我们也可以通过自建虚拟列，然后在该虚拟列上创建普通索引来实现相同的效果

1
2
3
4
5
6
7
8
9
10
11
12
ALTER TABLE actor ADD COLUMN upper_name varchar(15) GENERATED ALWAYS AS ((upper(left(name,15)))) VIRTUAL;
alter table actor add index virtual_upper(upper_name desc);
explain select * from actor where upper_name = 'A';
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+--------+
| id | select_type | table | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra  |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+--------+
| 1  | SIMPLE      | actor | <null>     | ref  | virtual_upper | virtual_upper | 48      | const | 1    | 100.0    | <null> |
+----+-------------+-------+------------+------+---------------+---------------+---------+-------+------+----------+--------+

# 删除虚拟列前要先删除其对应的索引
ALTER TABLE actor DROP INDEX virtual_upper;
ALTER TABLE actor DROP COLUMN upper_name;

索引条件下推

什么是索引条件下推，这里举例说明:
SELECT * FROM s1 WHERE order_no > 'z' AND order_no LIKE '%a';
其中的order_no > 'z'可以使用到索引，但是 order_no LIKE '%a'却无法使用到索引

  • 在MySQL5.6之前的版本中，是按照下边步骤来执行这个查询的：
    1、先根据 order_no> 'z'这个条件，从二级索引 idx_order_no 中获取到对应的二级索引记录。
    2、根据上一步骤得到的二级索引记录中的主键值进行回表(因为是 select *)，找到完整的用户记录再检测该记录是否符合 key1 LIKE '%a'这个条件，将符合条件的记录加入到最后的结果集。

  • MySQL5.6之后的版本开始支持索引下推，其执行步骤如下：
    1、先根据 order_no> 'z'这个条件，定位到二级索引 idx_order_no 中对应的二级索引记录。
    2、对于指定的二级索引记录，先不着急回表，而是先检测一下该记录是否满足 order_no LIKE '%a'这个条件，如果这个条件不满足，则该二级索引记录压根儿就没必要回表。
    3、对于满足 order_no LIKE '%a'这个条件的二级索引记录执行回表操作。

  • 回表操作其实是一个随机 IO，比较耗时，所以上述修改可以省去很多回表操作的成本。这个改进称之为索引条件下推（英文名：ICP ，Index Condition Pushdown）。

  • 如果在查询语句的执行过程中将要使用索引条件下推这个特性，在执行计划的 Extra 列中将会显示Using index condition

索引合并

过MySQL在一般情况下执行一个查询时最多只会用到单个二级索引，但存在有特殊情况下也可能在一个查询中使用到多个二级索引，MySQL中这种使用到多个索引来完成一次查询的执行方法称之为：索引合并/index merge。

  • 索引合并算法有如下三种：

    • 1.Intersection合并: 将从多个二级索引中查询到的结果取交集，某些特定的情况下才可能会使用到Intersection索引合并

      • 情况一：等值匹配
      • 情况二：主键列可以是范围匹配

    • 2.Union合并: 使用不同索引的搜索条件之间使用OR连接起来的情况，某些特定的情况下才可能会使用到Union索引合并

      • 情况一：等值匹配
      • 情况二：主键列可以是范围匹配
      • 情况三：使用Intersection索引合并的搜索条件

      就是搜索条件的某些部分使用Intersection索引合并的方式得到的主键集合和其他方式得到的主键集合取交集，比方说这个查询： SELECT * FROM order_exp WHERE insert_time = ‘a’ AND order_status = ‘b’ AND expire_time = ‘c’ OR (order_no = ‘a’ AND expire_time = ‘b’);

    • 3.Sort-Union合并: 先按照二级索引记录的主键值进行排序，之后按照Union索引合并方式执行的方式称之为Sort-Union索引合并，很显然，这种Sort-Union索引合并比单纯的Union索引合并多了一步对二级索引记录的主键值排序的过程

MySql索引失效的主要场景是什么？怎么解决？

  • 先说清楚：「失效」多数时候不是索引坏了，而是优化器算完成本后决定不用，EXPLAIN 里 key 为 NULL 或 type=ALL。少数是 B+Tree 结构上根本走不了。排查一律先 EXPLAIN，8.0 可以用 EXPLAIN ANALYZE，流程见 MySql–慢查询。下文以 KEY idx_name_age_position (name, age, position) 为例。

  • 1.联合索引不满足最左前缀

    • WHERE age = 22、WHERE position = 'manager' 都跳过了最左列 name，普通情况下走不了这个联合索引。
    • 解决：条件里带上最左列；或按查询单独建索引。MySQL 8.0.13 起有 Index Skip Scan，跳过最左列偶尔也能用上，但依赖最左列基数很小，不能当常态。
    • 最左前缀是「从左连续用」，不是「必须用等于」：WHERE name = 'LiLei' AND position = 'manager' 能用 name，age 断了，position 用不上。

  • 2.范围条件截断后续列

    • WHERE name = 'LiLei' AND age > 20 AND position = 'manager'：name、age 能用，age 已经是范围，position 无法继续走索引（ICP 仍可能在索引里过滤 position，但不是按 position 定位）。
    • 解决：等值列放联合索引左边，范围列放右边；区分度高、经常等值的列更靠前。IN 在 8.0 里常被当成多个等值，不一定截断后续列，以 EXPLAIN 的 key_len 为准。

  • 3.索引列上套了函数或做了运算

    • WHERE YEAR(hire_time) = 2022、WHERE UPPER(name) = 'LILEI'、WHERE age + 1 = 23，优化器无法按 B+Tree 有序定位。
    • 解决：改写成范围，如 WHERE hire_time >= '2022-01-01' AND hire_time < '2023-01-01'；必须按函数查时，用 8.0.13 的函数索引，见上文「函数索引」。查询条件要和函数索引表达式一致。

  • 4.隐式类型转换

    • 规则是：转换发生在列上就会丢掉索引。name 是 varchar 时 WHERE name = 123 会变成 CAST(name AS 数字) = 123，索引失效；反过来 age 是 int 时 WHERE age = '22'，是把常量转成 int，一般还能用索引。
    • JOIN 两边类型不同同理，例如 INT = VARCHAR。
    • 解决：列类型和传入值一致，JOIN 两边类型、长度一致，应用层不要把数字当字符串乱传。

  • 5.字符集 / 排序规则不一致

    • 两表 JOIN 时 utf8mb3 对 utf8mb4，或 utf8mb4_general_ci 对 utf8mb4_unicode_ci，比较前要转换其中一列，索引失效。
    • 解决：库、表、列、连接字符集统一用 utf8mb4，排序规则也统一。

  • 6.LIKE 左模糊

    • WHERE name LIKE 'Li%' 能走索引；WHERE name LIKE '%Lei'、LIKE '%Lei%' 走不了 B+Tree（不知道从哪开始搜）。
    • 解决：能右模糊就右模糊；必须左右模糊用 ES / 全文索引，不要指望普通 B+Tree。前缀索引对左模糊同样无效。

  • 7.优化器主动放弃（不是结构失效）

    • 表很小、列区分度很低（如性别）、统计信息过期，优化器会认为全表扫描更便宜。!=、<>、NOT IN、NOT LIKE、IS NOT NULL 也常落在这类：不是绝对不能用索引，而是优化器觉得用了更亏。
    • OR 同理：两侧都能走索引时可能 index merge（见上文「索引合并」）；有一侧走不了，就容易全表扫描。
    • IS NULL 在 InnoDB 里是可以用索引的，NULL 有独立排序位置，不要背成「IS NULL 一定失效」。
    • 解决：ANALYZE TABLE t; 刷新统计信息；小基数列不要单独建索引；OR 改成 UNION ALL；!= 尽量改成范围。FORCE INDEX 只做临时对照，不要当常规手段。

  • 8.SELECT * 本身不会让索引「失效」，但会逼着回表

    • 二级索引叶子没有完整行，SELECT * 必须回表，数据量大时优化器可能直接放弃二级索引改全表扫。
    • 解决：只查需要的列，让查询变成覆盖索引（Extra 出现 Using index）。

  • 对照与写法

场景 典型 SQL 处理
最左前缀 WHERE age = 22 条件带上 name，或另建索引
范围截断 name=? AND age>? AND position=? 等值列在左，范围列在右
函数/运算 YEAR(hire_time)=2022 改成范围，或建函数索引
隐式转换 varchar_col = 123 类型对齐，转换常量不要转换列
字符集 JOIN 两边 utf8 / utf8mb4 统一 utf8mb4 和 collation
左模糊 LIKE '%xx' 右模糊 / ES / 全文
优化器放弃 小表、低基数、统计过期 ANALYZE TABLE，别滥用 FORCE
回表太贵 SELECT * 覆盖索引

一句话：让比较发生在「列的原始值」上，联合索引从左连续用，等值在前范围在后。拿不准就看 EXPLAIN 的 key、key_len、type、Extra。

索引设计原则

  • 代码先行，索引后上

    • 一般应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立索引

  • 联合索引尽量覆盖条件

    • 比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的 where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则

  • 不要在小基数字段上建立索引

    • 比如性别字段，其值不是男就是女，那么该字段的基数就是2，对这种小基数字段建立索引的话，还不如全表扫描

  • 长字符串可以采用前缀索引

    • 但是要注意，order by和group by时没办法使用前缀索引

  • where与order by冲突时优先where

    • where可以缩小查询范围，会使排序的成本会小很多

  • 基于慢sql查询做优化

    • 线上系统一定要开启慢sql，然后定期对慢sql就行索引优化
    • 完整排查流程见 MySql--慢查询