MySQL 索引

0. 前言

生产上为了高效地查询数据库中的数据，我们常常会给表中的字段添加索引。那么如何添加索引才能使索引更高效：

添加的索引是越多越好吗？
索引有哪些类型？
为啥有时候明明添加了索引却不生效？
如何评判一个索引设计的好坏？

针对上述问题，本文将会从以下几个方面来讲述索引的相关知识：

1. 什么是索引，索引的作用

索引是对数据库表中一列或多列的值进行排序的一种数据结构，好比是一本书前面的目录，可以增加对特定信息的查询速度。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

1.1 索引能极大地减少扫描行数

当我们要在新华字典里查某个字（如「先」）具体含义的时候，通常都会拿起一本新华字典来查，你可以先从头到尾查询每一页是否有「先」这个字，这样做（对应数据库中的全表扫描）确实能找到，但效率无疑是非常低下的，更高效的方法相信大家也都知道，就是在首页的索引里先查找「先」对应的页数，然后直接跳到相应的页面查找，这样查询时间大大减少了，可以是 O(1)。

数据库中的索引也是类似的，通过索引定位到要读取的页，大大减少了需要扫描的行数，能极大地提升效率。简而言之，索引主要有以下几个作用：

即上述所说，索引能极大地减少扫描行数。
索引可以帮助服务器避免排序和临时表。
索引可以将随机 IO 变成顺序 IO。

第一点上文已经解释了，我们来看下第二点和第三点。

1.2 索引可以帮助服务器避免排序和临时表

先来看第二点，假设我们不用索引，试想运行如下语句：

select * from user order by age desc;

MySQL 的执行流程是这样的：扫描所有行，把所有行加载到内存后，按 age 排序生成一张临时表，再把这表结果返回给客户端。更糟的情况是，如果这张临时表的大小大于 tmp_table_size 的值（默认为 16 M），内存临时表会转为磁盘临时表，性能会更差。

如果加了索引，因为索引本身是有序的，所以从磁盘读的行数据本身就是按 age 排序好的，也就不会生成临时表（空间消耗）和额外排序（CPU 消耗），无疑提升了性能。

1.3 索引可以将随机 I/O 变成顺序 I/O

再来看随机 I/O 和顺序 I/O，先来解释下这两个概念。相信不少人应该吃过旋转火锅，服务员把一盘盘的菜放在旋转传输带上，然后等到这些菜转到我们面前，我们就可以拿到菜了。假设转一圈需要 4 分钟，则最短等待时间是 0（即菜就在你跟前），最长等待时间是 4 分钟（菜刚好在你跟前错过），那么平均等待时间即为 2 分钟。假设我们现在要拿四盘菜，这四盘菜随机分配在传输带上，则可知拿到这四盘菜的平均等待时间是 8 分钟（随机 I/O），如果这四盘菜刚好紧邻着排在一起，则等待时间只需 2 分钟（顺序 I/O）。

上述中传输带就类比磁道，磁道上的菜就类比扇区（sector）中的信息，扇区是硬盘读写的基本单位；而磁盘块（block）是由多个相邻的扇区组成的，是操作系统读取的最小单元。这样如果信息能以 block 的形式聚集在一起，就能极大减少磁盘 I/O 时间，这就是顺序 I/O 带来的性能提升，下文中我们将会看到 B+ 树索引就起到这样的作用。

如上图所示：多个扇区组成了一个 block，如果要读的信息都在这个 block 中，则只需一次 I/O 读

而如果信息在一个磁道中分散地分布在各个扇区中，或者分布在不同磁道的扇区上（寻道时间是随机 I/O 主要瓶颈所在），将会造成随机 I/O，影响性能。

我们来看一下一个随机 I/O 的时间分布：

Seek Time（寻道时间）：磁头移动到扇区所在的磁道。
Rotational Latency（旋转时延）：完成步骤 1 后，磁头移动到同一磁道扇区对应的位置所需求时间。
Transfer Time（传输时间）：从磁盘读取信息传入内存时间。

MySQL 的数据是一行行存储在磁盘上的，并且这些数据并非物理连续地存储，这样的话要查找数据就无法避免随机在磁盘上读取和写入数据。对于 MySQL 来说，当出现大量磁盘随机 I/O 时，大部分时间都被浪费到寻道上（大概占据随机 I/O 时间的 40%）。

随机 I/O 和顺序 I/O 大概相差百倍 (随机 I/O：10 ms/ page，顺序 I/O：0.1ms / page)，可见顺序 I/O 性能之高，索引带来的性能提升显而易见！

由 MySQL 优化器做选择

注意，即使 SQL 完全符合索引生效的场景，考虑到实际数据量等原因，最终是否使用索引还要看 MySQL 优化器判断全表扫描和走索引的成本哪个更低（当然也可以在 SQL 中写明强制走某个索引）。

建立索引也有不好之处

索引需要占用物理空间，因此也增加了磁盘存储空间。
索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，MySQL 不仅要保存数据，还要保存或者更新对应的索引文件。

也就是说创建索引和维护索引要耗费空间和时间，这种耗费随着数据量的增加而增加，因此索引也不是越多越好，在数据量小的情况下则没必要建索引。

索引不适合的场景

数据量少
数据更新频繁
区分度低的字段（如性别）

2. 索引的类型

索引类型

单列索引：即一个索引只包含单个列。一个表可以有多个单列索引，但这不是组合索引。
联合索引：即一个索引包含多个列。
普通索引/二级索引（INDEX/KEY）：最基本的索引，没有任何限制。
唯一索引（UNIQUE）：与“普通索引”类似，不同的是：索引列的值必须唯一，但允许有 NULL。
主键索引（PRIMARY）：一种特殊的唯一索引，不允许为 NULL。
全文索引（FULLTEXT）：仅可用于 MyISAM 表，主要用于在长篇文章中检索关键字信息。针对较大的数据，生成全文索引很耗时好空间。

Mysql 索引语法

创建索引

CREATE TABLE table_name[col_name data type][unique|fulltext][index|key][index_name](col_name[length])[asc|desc]

参数介绍：

unique|fulltext 为可选参数，分别表示唯一索引、全文索引。
index 和 key 为同义词，两者作用相同，用来指定创建索引。
col_name 为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择。
index_name 指定索引的名称，为可选参数，如果不指定，默认 col_name 为索引值。
length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度。
asc 或 desc 指定升序或降序的索引值存储。

查看索引

-- 方式1 
show index from `table_name`; 

-- 方式2 
show keys from `table_name`;

查询结果字段解释：

Table：表的名称。
Non_unique：如果索引不能包括重复词，则为 0；如果可以，则为 1。
Key_name：索引的名称。
Seq_in_index：索引中的列序号，从 1 开始。
Column_name：列名称。
Collation：列以什么方式存储在索引中。在 MySQL 中，有“A”（升序）或“NULL”（无分类）。
Cardinality：索引中唯一值的数目的估计值。通过运行 ANALYZE TABLE 或 myisamchk -a 可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大。
Sub_part：如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为 NULL。
Packed：指示关键字如何被压缩。如果没有被压缩，则为 NULL。
Null：如果列含有 NULL，则含有 YES；如果没有，则该列含有 NO。
Index_type：用过的索引方法（BTREE、FULLTEXT、HASH、RTREE）。

删除索引

-- 方式1
ALTER TABLE `table_name` DROP INDEX index_name;

-- 方式2
drop INDEX indexname on `table_name`;

索引类型详解

普通索引/二级索引（INDEX/KEY）

普通索引是最基本的索引，它没有任何限制，允许被索引的数据列包含重复的值。

普通索引的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHERE column =）或排序条件（ORDER BY column）中的数据列创建索引。

只要有可能，就应该选择一个数据最整齐、最紧凑的数据列（如一个整数类型的数据列）来创建索引。

普通索引有以下几种创建方式：

-- 方式1：直接创建索引
CREATE INDEX index_name ON table(column(length));

-- 方式2：修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name (column(length));

-- 方式3：创建表的时候同时创建索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` char(255) NOT NULL,
    `content` text,
    `time` int(10) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX index_name (title(length))  -- INDEX 还可以替换为 KEY
);

唯一索引（UNIQUE）

与前面的普通索引类似，不同的就是：索引列的值必须唯一（包括空字符串），但允许有空值（NULL）。

唯一索引可以有多个，如果是组合索引，则列值的组合必须唯一。

如果在一个列上同时建唯一索引和普通索引的话，Mysql 会自动选择唯一索引。

MySQL 会在有新记录插入数据表时，会自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过；如果是，MySQL 将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

唯一索引和普通索引使用的结构都是 B-tree，执行时间复杂度都是 O(logn)。

唯一索引有以下几种创建方式：

-- 方式1：创建唯一索引
CREATE UNIQUE INDEX indexName ON table(column(length));
‍
-- 方式2：修改表结构
ALTER TABLE table_name ADD UNIQUE indexName ON (column(length));
‍
-- 方式3：创建表的时候直接指定
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` char(255) CHARACTER NOT NULL,
    `content` text CHARACTER NULL,
    `time` int(10) NULL DEFAULT NULL,
    UNIQUE indexName (title(length))
);

主键（PRIMARY）

是一种特殊的唯一索引，一个表只能有一个主键，主键列不允许有空值（NULL）。

主键可作外键，唯一索引不可。
主键不可为空（NULL），唯一索引可以。

主键也可以包含个字段（这叫联合主键），所以可以在主键的其中一个字段建唯一索引。

主键的优势：

表数据的存储在磁盘中是按照主键顺序存放的，所以使用主键查询数据时速度最快。
主键具有约束性，可以作为一定业务场景的数据校验约束。
建议：建表时一定要带有主键，后续优化效果最好。

在定义主键时会自动创建唯一索引：

CREATE TABLE `table` (
    `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    `title` char(255) NOT NULL 
);

联合索引

联合索引指一个索引中包含了多个字段。

使用联合索引时遵循最左匹配原则：当创建 (a,b,c) 联合索引时，相当于创建了 (a) 单列索引、(a,b) 联合索引以及 (a,b,c) 联合索引。要想索引生效的话，只能使用 a 和 a,b 和 a,b,c 这三种组合（a,c 组合也可以，但实际上只用到了 a 的单列索引，c 并没有用到）。

通俗理解：联合索引的结构与电话簿类似，人名由姓和名构成，电话簿首先按姓氏对进行排序，然后按名字对有相同姓氏的人进行排序。如果您知道姓，电话簿将非常有用；如果您知道姓和名，电话簿则更为有用，但如果只知道名不知道姓，电话簿将没有用处。

所以说创建联合索引时，应该仔细考虑列的顺序。对索引中的所有列执行搜索或仅对前几列执行搜索时，联合索引非常有用；仅对后面的任意列执行搜索时，联合索引则没有用处。

示例：

创建一个表，包括 c1,c2,c3,c4,c5 字段。

创建联合索引 (c1,c2,c3,c4)。

1）只有 where 的情况：遵从最左原则，条件必须有左边的字段，才会用到索引，中间如果断开了，则后面的索引都不会用到。

-- 这种情况因为 c3 没有在条件中，所以只会用到 c1,c2 索引
where c1 = '1' and c2 = '1' and c4 = '1'

特殊情况：使用范围条件或 like 时，也会使用到该处的索引，但后面的索引都不会用到。

-- 这种情况从 c2 处已经断开，会使用到 c1,c2 索引，不会再使用到后面的 c3,c4 索引
where c1 = '1' and c2 > '1' and c3 = '1'

2）group by 和 order by 也遵从最左原则，可以看做继承 where 的条件顺序，但需要 where 作为基础铺垫，即如果没有 where 语句，单纯的 group by 或 order by 也是不会使用任何索引的，并且需要和联合索引顺序一致才会使用索引。

select ... group | order by c1  -- 由于没有 where 的铺垫，不使用任何索引

select ... where c1 = '1' group | order by c2  -- 使用 c1,c2 索引

select ... where c1 = '1' group | order by c2, c3  -- 使用 c1,c2,c3 索引

select ... where c1 = '1' group | order by c3  -- 只使用 c1 索引

select ... where c1 = '1' group | order by c3, c2  -- 只使用 c1 索引

select ... where c1 > '1' group | order by c2  -- 范围搜索会断掉连接，所以也只会使用 c1 索引

select ... where c1 = '1' group by c2 order by c3  -- 使用 c1,c2 索引

联合索引的优化建议：

多个单列索引在（简单查询中）多条件查询时只会生效其中一个索引，所以多条件联合查询时最好建联合索引；并且联合索引相比多个单列索引，更节省索引的存储空间（索引建立得越多就越占磁盘空间，在更新数据的时候速度会更慢）。
在创建联合索引的时候因该把频繁使用、区分度高的列放在前面，频繁使用代表索引的利用率高，区分度高代表数据量的筛选力度大。
如果 WHERE 条件均为联合索引的字段，也要尽量按照联合索引的顺序来。如果不按照顺序来，索引也同样会用到，但是在执行前，SQL 优化器也会将条件调整为联合索引的顺序，既然可以直接避免这种情况，就没必要再让 SQL 优化器去处理，毕竟处理也是有开销的。
当前索引存在频繁使用作为返回字段的列，这个时候就可以考虑当前列是否可以加入到当前已经存在索引上，使其查询语句可以使用到覆盖索引。

联合索引的创建方式：

-- 方式1：直接创建索引
CREATE INDEX index_name ON table(column1(length), column2(length), column3(length));

-- 方式2：修改表结构的方式添加索引
ALTER TABLE table_name ADD INDEX index_name (column1(length), column2(length), column3(length));

-- 方式3：创建表的时候同时创建索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `title` char(255) NOT NULL,
    `content` text,
    `time` int(10) DEFAULT NULL,
    PRIMARY KEY (`id`),
    INDEX index_name (column1(length), column2(length), column3(length))  -- INDEX 还可以替换为 KEY
);

全文索引（FULLTEXT）

主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

fulltext 索引跟其它索引大不相同，它更像是一个搜索引擎，主要为了解决 like %keyword% 这类查询的匹配问题。

它可以在 create table、alter table、create index 时使用，不过目前只有 char、varchar、（tiny、medium、long）text 列上可以创建全文索引。

值得一提的是，在数据量较大时候，先将数据放入一个没有全文索引的表中，然后再用 CREATE index 创建 fulltext 索引，要比先为一张表建立 fulltext 索引然后再将数据写入的速度快很多。

如下图所示，fulltext 索引一般配合 match() against() 操作使用：

存储引擎：

MyISAM 支持 FULLTEXT 类型的全文索引。
Innodb 不支持 FULLTEXT 类型的全文索引，但是 Innodb 可以使用 Sphinx 插件支持全文索引，并且效果更好（Sphinx 是一个开源软件，提供多种语言的 API 接口，可以优化 Mysql 的各种查询）。

全文索引有以下几种创建方式：

--方式1：创建表的适合添加全文索引
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`),
    FULLTEXT (content)
);

--方式2：修改表结构添加全文索引
ALTER TABLE article ADD FULLTEXT index_content(content);

--方式3：直接创建索引
CREATE FULLTEXT INDEX index_content ON article(content);

3. 索引的数据结构

索引一般需要解决下面两个问题：

等值查询，比如 select * from user where id=1234
范围查询，比如 select * from user where id > 1234 and id < 2345

哈希表

在 Java 中的 HashMap、TreeMap 就是 Hash 表结构，以键值对的方式存储数据。

假设我们对名字建立了哈希索引，则查找过程如下图所示：

对于每一行数据，存储引擎都会对所有的索引列（上图中的 name 列）计算一个哈希值（上图散列表的位置），散列表里的每个元素指向数据行的指针。由于索引自身只存储对应的哈希值，所以索引的结构十分紧凑，这让哈希索引查找速度非常快！

当然了，哈希表的劣势也是比较明显的，不支持区间查找（需要挨个数据遍历，效率低），所以更多的时候哈希表是与 B+ 树等一起使用的。在 InnoDB 引擎中就有一种名为「自适应哈希索引」的特殊索引，当 InnoDB 注意到某些索引值使用非常频繁时，就会在内存中基于 B+ 树索引之上再创建哈希索引，这样也就让 B+ 树索引也有了哈希索引的快速查找等优点，这是完全自动的内部的行为，用户无法控制或配置，不过如果有必要，可以关闭该功能。

显然，单纯的哈希索引并不适合作为经常需要范围查找的数据库使用。

B+ 树

为什么 B+ 树成为了数据库索引的主流实现？我们可以结合下述各种树结构的优劣势进行分析推导。

二叉查找树（BST）

二叉查找树（BST）是综合了顺序表（查找效率高）和链表（增删效率高）优势的折中方案，其特点是：每个节点最多有 2 个子节点，且左子树和右子树数据顺序是左小右大。

这个特点就是为了保证每次查找都可以这折半而减少 I/O 次数，但是二叉树就很考验第一个根节点的取值，因为很容易在这个特点下出现我们并发想发生的情况“树不分叉了”，这就很难受很不稳定。

显然这种不稳定的情况，是需要我们在选择设计上必然要避免的。

平衡二叉查找树（AVL）

平衡二叉查找树是采用二分法思维，平衡二叉查找树除了具备二叉查找树的特点，最主要的特征是树的左右两个子树的层级最多相差 1。在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高、右子树很矮的情况。

使用平衡二叉查找树查询的性能接近于二分查找法，时间复杂度是 O(logn)。如下图所示，查询 id=6 时，只需要两次 I/O。

就这个特点来看，可能觉得这就很好了，可以达到二叉树的理想的情况。然而依然存在一些问题：

时间复杂度和树高相关。树有多高就需要检索多少次，每个节点的读取，都对应一次磁盘 IO 操作。即树的高度等于每次查询数据时磁盘 I/O 操作的次数。磁盘每次寻道时间为 10ms，在表数据量大时，查询性能就会很差。（一百万的数据量，logn 约等于 20 次磁盘 I/O，时间为 20*10=0.2s）

平衡二叉树不支持范围查询的快速查找，范围查询时需要从根节点多次遍历，查询效率不高。

B 树：改造二叉树

MySQL 的数据是存储在磁盘文件中的，查询处理数据时，需要先把磁盘中的数据加载到内存中，磁盘 I/O 操作非常耗时，所以我们优化的重点就是尽量减少磁盘 I/O 操作。访问二叉树的每个节点就会发生一次 I/O，如果想要减少磁盘 I/O 操作，就需要尽量降低树的高度。那如何降低树的高度呢？

假如 key 为 bigint = 8 字节，每个节点有两个指针，每个指针为 4 个字节，一个节点占用的空间 16 个字节（8+4*2=16）。

因为在 MySQL 的 InnoDB 存储引擎一次 I/O 会读取的一页（默认一页为 16K）的数据量，而二叉树一次 I/O 有效数据量只有 16 字节，空间利用率极低。为了最大化利用一次 I/O 空间，一个简单的想法是在每个节点存储多个元素，在每个节点尽可能多的存储数据。

当每个节点可以存储 1000 个索引（16k/16=1000）时，这样就可以将二叉树改造成多叉树，通过增加树的叉树，将树从高瘦变为矮胖。此时构建一百万条数据，树的高度只需要 2 层就可以（1000*1000=一百万），也就是说只需要 2 次磁盘 I/O 就可以查询到数据。磁盘 I/O 次数变少了，查询数据的效率也就提高了。

B 树结构

这种数据结构我们称为 B 树，B 树是一种多叉平衡查找树，如下图主要特点：

B 树的节点中存储着多个元素，每个节点有多个分叉。
节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都储存数据。
父节点当中的元素不会出现在子节点中。
所有的叶子节点都位于同一层，且叶子节点之间没有指针连接。

在 B 树中查询数据

假如我们查询值等于 10 的数据。则查询路径为磁盘块 1 -> 磁盘块 2 -> 磁盘块 5。

第一次磁盘 I/O：将磁盘块1加载到内存中，在内存中从头遍历比较，10 < 15，走左路，到磁盘寻址磁盘块 2。
第二次磁盘 I/O：将磁盘块2加载到内存中，在内存中从头遍历比较，7 < 10，到磁盘中寻址定位到磁盘块 5。
第三次磁盘 I/O：将磁盘块5加载到内存中，在内存中从头遍历比较，10 = 10，找到 10，取出 data，如果 data 存储的是行记录，取出 data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。

相比二叉平衡查找树，在整个查找过程中，虽然数据的比较次数并没有明显减少，但是磁盘 I/O 次数会大大减少。同时，由于我们的比较是在内存中进行的，比较的耗时可以忽略不计。

看到这里一定觉得 B 树就很理想了，但是前辈们会告诉你依然存在可以优化的地方：

B 树也不支持范围查询的快速查找，你想想这么一个情况如果我们想要查找 10 和 35 之间的数据，查找到 15 之后，还需要多次回到根节点重新遍历查找剩余数据，其查询效率有待提高。

如果 data 存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘 I/O 次数就会变大。

B+ 树：改造 B 树

MySQL 在 B 树的基础上继续改造，使用 B+ 树构建索引。作为 B 树的升级版，B+ 树和 B 树最主要的区别在于非叶子节点是否存储数据的问题。

B 树：非叶子节点和叶子节点都会存储数据。

B+ 树：只有叶子节点才会存储数据，非叶子节点至存储键值。叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

B+ 树的最底层叶子节点包含了所有的索引项。从图上可以看到，B+ 树在查找数据的时候，由于数据都存放在最底层的叶子节点上，所以每次查找都需要检索到叶子节点才能查询到数据。

所以在需要查询数据的情况下每次的磁盘的 I/O 跟树高有直接的关系，但是从另一方面来说，由于数据都被放到了叶子节点，因此非叶子节点的磁盘块所存放的索引数量是会跟着增加的。相对于 B 树来说，B+ 树的树高理论上情况下是比 B 树要矮的。

也存在索引覆盖查询（后文会讲述）的情况，在索引中数据满足了当前查询语句所需要的全部数据，此时只需要找到索引即可立刻返回，不需要检索到最底层的叶子节点。

示例 1：等值查询

假如我们查询值等于 9 的数据，则查询路径为磁盘块 1 -> 磁盘块 2 -> 磁盘块 6。

第一次磁盘 I/O：将磁盘块 1 加载到内存中，在内存中从头遍历比较， 9 < 15，走左路，到磁盘寻址磁盘块 2。
第二次磁盘 I/O：将磁盘块 2 加载到内存中，在内存中从头遍历比较， 7 < 9 < 12，到磁盘中寻址定位到磁盘块 6。
第三次磁盘 I/O：将磁盘块 6 加载到内存中，在内存中从头遍历比较，在第三个索引中找到 9，取出 data，如果 data 存储的行记录，取出 data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。（这里需要区分的是在 InnoDB 中 data 存储的为行数据，而 MyIsam 中存储的是磁盘地址。）

示例 2：范围查询:

假如我们想要查找 9 和 26 之间的数据。则查找路径为磁盘块 1 -> 磁盘块 2 -> 磁盘块 6 -> 磁盘块 7。

首先查找值等于 9 的数据，将值等于 9 的数据缓存到结果集。这一步和前面等值查询流程一样，发生了三次磁盘 I/O。查找到 9 之后，底层的叶子节点是一个有序列表，我们从磁盘块 6，键值 9 开始向后遍历筛选所有符合筛选条件的数据。
第四次磁盘I/O：根据磁盘 6 后继指针到磁盘中寻址定位到磁盘块 7，将磁盘 7 加载到内存中，在内存中从头遍历比较，9 < 25 < 26，9 < 26 <= 26，将 data 缓存到结果集。由于主键具备唯一性（后面不会有 <= 26的数据），不需再向后查找，查询终止。将结果集返回给用户。

可以看到 B+ 树可以保证等值和范围查询的快速查找，因此 MySQL 的索引就采用了 B+ 树的数据结构。

一般情况下，3-4 层的 B+ 树足以支撑千万行的数据量存储，当数据量再往上时就已经需要考虑分库分表了。

4. MySQL 的索引实现

介绍完了索引数据结构，那肯定是要带入到 MySQL 里面看看真实的使用场景的，所以这里分析 MySQL 的两种存储引擎的索引实现：MyISAM 索引和 InnoDB 索引。

MyISAM 索引

以一个简单的 user 表为例。user 表存在两个索引，id 列为主键索引，age 列为普通索引：

CREATE TABLE `user`
(
  `id`       int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `age`      int(11)     DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE
) ENGINE = MyISAM
  AUTO_INCREMENT = 1
  DEFAULT CHARSET = utf8;

MyISAM 的数据文件和索引文件是分开存储的。MyISAM 使用 B+ 树构建索引树时，叶子节点中存储的键值为索引列的值，数据为索引所在行的磁盘地址。

主键索引

表 user 的索引存储在索引文件 user.MYI 中，数据文件存储在数据文件 user.MYD 中。

下面简单分析下查询时的磁盘 I/O 情况。

根据主键等值查询数据

select * from user where id = 28;

先在主键树中从根节点开始检索，将根节点加载到内存，比较 28 < 75，走左路。（1 次磁盘 I/O）
将左子树节点加载到内存中，比较 16 < 28 < 47，向下检索。（1 次磁盘 I/O）
检索到叶节点，将节点加载到内存中遍历，比较 16 < 28、18 < 28、28 = 28，查找到值等于 30 的索引项。（1 次磁盘 I/O）
从索引项中获取磁盘地址，然后到数据文件 user.MYD 中获取对应整行记录。（1 次磁盘 I/O）
将记录返给客户端。

磁盘 I/O 次数 = 3 次（索引检索） + 1 次（记录数据检索）

根据主键范围查询数据

select * from user where id between 28 and 47;

先在主键树中从根节点开始检索，将根节点加载到内存，比较 28 < 75，走左路。（1 次磁盘 I/O）
将左子树节点加载到内存中，比较 16 < 28 < 47，向下检索。（1 次磁盘 I/O）
检索到叶节点，将节点加载到内存中遍历比较 16 < 28、18 < 28、28 = 28 < 47。查找到值等于 28 的索引项。根据磁盘地址从数据文件中获取行记录缓存到结果集中。（1 次磁盘 I/O）。由于我们的查询语句是范围查找，因此需要向后遍历底层叶子链表，直至到达最后一个不满足筛选条件。
向后遍历底层叶子链表，将下一个节点加载到内存中，遍历比较，28 < 47 = 47，根据磁盘地址从数据文件中获取行记录缓存到结果集中。（1 次磁盘 I/O）
最后得到两条符合筛选条件，将查询结果集返给客户端。

磁盘 I/O 次数 = 4 次（索引检索）+1 次（记录数据检索）

PS：以上分析仅供参考，MyISAM 在查询时，会将索引节点缓存在 MySQL 缓存中，而数据缓存依赖于操作系统自身的缓存，所以并不是每次都是走的磁盘，这里只是为了分析索引的使用过程。

辅助索引（二级索引）

在 MyISAM 中，辅助索引和主键索引的结构是一样的，没有任何区别，叶子节点的数据存储的都是行记录的磁盘地址。只是主键索引的键值是唯一的，而辅助索引的键值可以重复。

由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据。

InnoDB 索引

聚簇索引（主键索引）

每个 InnoDB 表都有一个聚簇索引，聚簇索引使用 B+ 树构建，叶子节点存储的数据是整行记录。

一般情况下，聚簇索引等同于主键索引，当一个表没有创建主键索引时，InnoDB 会自动创建一个 ROWID 字段来构建聚簇索引。InnoDB 创建索引的具体规则如下：

在表上定义主键 PRIMARY KEY，InnoDB 将主键索引用作聚簇索引。
如果表没有定义主键，InnoDB 会选择第一个不为 NULL 的唯一索引列用作聚簇索引。
如果以上两个都没有，InnoDB 会使用一个 6 字节长整型的隐式字段 ROWID 字段构建聚簇索引，且该 ROWID 字段会在插入新行时自动递增。

除聚簇索引之外的所有索引都称为辅助索引（二级索引）。在 InnoDB 中，辅助索引中的叶子节点存储的数据是该行的主键值。在检索时，InnoDB 使用此主键值在聚簇索引中搜索行记录。

聚簇索引中主键的逻辑顺序决定了表记录的物理存储顺序；而辅助索引的逻辑顺序与索引值的物理存储顺序不同。

这里以 user_innodb 为例，user_innodb 的 id 列为主键，age 列为普通索引：

CREATE TABLE `user_innodb`
(
  `id`       int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) DEFAULT NULL,
  `age`      int(11)     DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_age` (`age`) USING BTREE
) ENGINE = InnoDB;

InnoDB 的数据和索引存储在一个文件 t_user_innodb.ibd 中。InnoDB 的数据组织方式是聚簇索引。

主键索引的叶子节点会存储数据行，辅助索引只会存储主键值。

等值查询数据

select * from user_innodb where id = 28;

先在主键树中从根节点开始检索，将根节点加载到内存，比较 28<75，走左路。（1 次磁盘 I/O）
将左子树节点加载到内存中，比较 16 < 28 < 47，向下检索。（1 次磁盘 I/O）
检索到叶节点，将节点加载到内存中遍历，比较 16 < 28、18 < 28、28 = 28。查找到值等于 28 的索引项，直接可以获取整行数据。将改记录返回给客户端。（1 次磁盘 I/O）

磁盘 I/O 次数：3 次

非聚簇索引（辅助索引）

除聚簇索引之外的所有索引都称为辅助索引，InnoDB 辅助索引的叶子节点只会存储主键值而非数据行。

由于辅助索引的键值不唯一，可能存在多个拥有相同的记录，所以即使是等值查询，也需要按照范围查询的方式在辅助索引树中检索数据。

以表 user_innodb 的 age 列为例，age 索引的索引结果如下图：

底层叶子节点的按照 (age, id) 的顺序排序，先按照 age 列从小到大排序，age 列相同时按照 id 列从小到大排序。

使用辅助索引需要检索两遍索引：首先检索辅助索引获得主键，然后使用主键到聚簇索引中检索获得记录。

等值查询数据

select * from t_user_innodb where age=19;

根据在辅助索引树中获取的主键 id，到聚簇索引树中检索数据的过程称为回表查询。

磁盘 I/O 次数：辅助索引 3 次 + 获取记录回表 3 次 = 6 次

组合索引

以表 abc_innodb 为例，id 为主键索引，创建了一个联合索引 idx_abc(a, b, c)：

CREATE TABLE `abc_innodb`
(
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a`  int(11)     DEFAULT NULL,
  `b`  int(11)     DEFAULT NULL,
  `c`  varchar(10) DEFAULT NULL,
  `d`  varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE,
  KEY `idx_abc` (`a`, `b`, `c`)
) ENGINE = InnoDB;

组合索引的数据结构

组合索引的查询过程

select * from abc_innodb where a = 13 and b = 16 and c = 4;

最左匹配原则

最左前缀匹配原则和组合索引的索引存储结构和检索方式是有关系的。

在组合索引树中，最底层的叶子节点按照第一列 a 列从左到右递增排列，但是 b 列和 c 列是无序的，b 列只有在 a 列值相等的情况下小范围内递增有序，而 c 列只能在 a、b 两列相等的情况下小范围内递增有序。

就像上面的查询，B+ 树会先比较 a 列来确定下一步应该搜索的方向，往左还是往右。如果 a 列相同再比较 b 列。但是如果查询条件没有 a 列，B+ 树就不知道第一步应该从哪个节点查起。

可以说创建的 idx_abc(a,b,c) 索引，相当于创建了 (a)、(a, b)、(a, b, c) 三个单列索引。

注意，在使用组合索引查询时，MySQL 会一直向右匹配，直至遇到范围查询（>、<、between、like）就停止匹配。

覆盖索引

覆盖索引并不是索引结构，而是一种很常用的优化手段。

因为在使用辅助索引的时候，我们只可以拿到主键值，相当于获取到数据后还需要再根据主键查询聚簇索引才获取到最终结果数据。但是试想下这么一种情况，在上面 abc_innodb 表中的组合索引查询时，如果我只需要 a、b、c 三个字段，那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了，而不需要回表。这种情况就是覆盖索引。

使用到覆盖索引的标识：

未使用到覆盖索引：

优化示例：使用覆盖索引，避免回表

在 InnoDB 的存储引擎中，使用辅助索引查询的时候，因为辅助索引叶子节点保存的数据不是当前记录的数据而是当前记录的主键索引，索引如果需要获取当前记录完整数据就必然需要根据主键值从主键索引继续查询。这个过程我们称为回表查询。回表必然是会消耗性能影响性能。

那如何避免呢？使用索引覆盖。

举个例子：现有 User 表( id(PK), name(key), sex, address, hobby...)

select id, name, sex from user where name = 'zhangsan';

如果上述语句在业务上频繁使用到，而 user 表的其他字段使用频率远低于它，在这种情况下，如果我们在建立 name 字段的索引的时候，不应使用单一索引，而应使用联合索引(name, sex)。这样的话在执行这个查询语句时，是不是直接根据辅助索引查询到的结果就可以获取当前语句的完整数据了，也就可以有效地避免了回表再获取 sex 的数据。

这就是一个典型的使用覆盖索引的优化策略减少回表的情况。

5. 高性能索引策略

不同的索引设计选择能对性能产生很大的影响，有人可能会发现生产中明明加了索引却不生效，有时候加了虽然生效但对搜索性能并没有提升多少，对于多列联合索引，哪列在前，哪列在后也是有讲究的，我们一起来看看。

加了索引，为何却不生效？

加了索引却不生效可能会有以下几种原因：

1）索引列是表达式或函数的一部分

如下 SQL：

SELECT book_id FROM BOOK WHERE book_id + 1 = 5;

或者

SELECT book_id FROM BOOK WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(gmt_create) <= 10;

上述两个 SQL 虽然在列 book_id 和 gmt_create 设置了索引，但由于它们是表达式或函数的一部分，导致索引无法生效，最终全表扫描。

2）隐式类型转换

以上两种情况相信不少人都知道索引不能生效，但下面这种隐式类型转换估计会让不少人栽跟头，来看下面这个例子。

假设有以下表：

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;

执行 SQL：

SELECT * FROM tradelog WHERE tradeid=110717;

交易编号 tradeid 上有索引，但用 EXPLAIN 执行却发现使用了全表扫描，为啥呢？因为 tradeid 的类型是 varchar(32)，而此 SQL 用 tradeid 与一个数字类型进行比较，发生了隐式转换，即会隐式地将字符串转成整型，如下：

SELECT * FROM tradelog WHERE CAST(tradid AS signed int) = 110717;

这样也就触发了上文中第一条的规则，即：索引列不能是函数的一部分。

3）隐式编码转换

这种情况非常隐蔽，来看下面这个例子。

CREATE TABLE `trade_detail` ( 
 `id` int(11) NOT NULL, 
 `tradeid` varchar(32) DEFAULT NULL, 
 `trade_step` int(11) DEFAULT NULL, /*操作步骤*/ 
 `step_info` varchar(32) DEFAULT NULL, /*步骤信息*/ 
   PRIMARY KEY (`id`), KEY `tradeid` (`tradeid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

trade_detail 是交易详情， tradelog 是操作此交易详情的记录，现在要查询 id=2 的交易的所有操作步骤信息，则我们会采用如下方式：

SELECT d.* FROM tradelog l, trade_detail d WHERE d.tradeid=l.tradeid AND l.id=2;

由于 tradelog 与 trade_detail 这两个表的字符集不同，且 tradelog 的字符集是 utf8mb4，而 trade_detail 字符集是 utf8，utf8mb4 是 utf8 的超集，所以会自动将 utf8 转成 utf8mb4。即上述语句会发生如下转换：

SELECT d.* FROM tradelog l, trade_detail d WHERE (CONVERT(d.traideid USING utf8mb4)))=l.tradeid AND l.id=2;

自然也就触碰了「索引列不能是函数的一部分」这条规则。怎么解决呢？第一种方案当然是把两个表的字符集改成一样，如果业务量比较大，生产上不方便改的话，还有一种方案是手动用函数把 utf8mb4 转成 utf8（当然从超集向子集转换是有数据截断风险的），如下：

SELECT d.* FROM tradelog l, trade_detail d WHERE d.tradeid=CONVERT(l.tradeid USING utf8) AND l.id=2;

这样索引列就生效了。

4）使用 order by 造成的全表扫描

SELECT * FROM user ORDER BY age DESC;

上述语句在 age 上加了索引，但依然造成了全表扫描，这是因为我们使用了 SELECT * 导致回表查询，MySQL 认为回表的代价比全表扫描更大，所以不选择使用索引。

如果想使用到 age 的索引，我们可以用覆盖索引来代替：

SELECT age FROM user ORDER BY age DESC;

或者加上 limit 的条件（条数较少）：

SELECT * FROM user ORDER BY age DESC limit 10;

这样就能利用到索引。

5）like 通配符可能会导致索引失效

当 like 查询以 % 开头时，会导致索引失效。解决办法有两种：

将 % 移到后面，如：

select * from `user` where `name` like '李%';

利用覆盖索引来命中索引：

select name from `user` where `name` like '%李%';

6）where 语句中包含 or 时，可能会导致索引失效

使用 or 并不是一定会使索引失效，你需要看 or 左右两边的查询列是否有创建索引。

假设 user 表中的 user_id 列为主键，age 列没有索引。

索引有效（可以使用 explain 验证下）：

select * from `user` where user_id = 1 or user_id = 2;

and 和 or 的区别：

select * from `user` where user_id = 1 and age = 20;  -- 走 user_id 主键索引
select * from `user` where user_id = 1 or age = 20;  -- 索引失效

若为 age 列加上普通索引的话：

select * from `user` where user_id = 1 and age = 20;  -- 走 user_id 主键索引
select * from `user` where user_id = 1 or age = 20;  -- 走 age 普通索引

因此建议：

尽量避免使用 or 语句，可以根据情况使用 union all 或者 in 来代替，这两个语句的执行效率也比 or 好些。
为 or 左右的列建索引。

无法避免对索引列使用函数时，怎么使用索引？

有时候我们无法避免对索引列使用函数，但这样做会导致全表索引，是否有更好的方式呢？

比如我现在就是想记录 2016 ~ 2018 所有年份 7月份的交易记录总数：

SELECT count(*) FROM tradelog WHERE month(t_modified)=7;

由于索引列是函数的参数，所以显然无法用到索引，我们可以将它改造成基本字段区间的查找：

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');

前缀索引与索引选择性

之前我们说过，对于长字符串的字段（如 url），我们可以用伪哈希索引的形式来创建索引，以避免索引变得既大又慢，除此之外其实还可以用前缀索引（字符串的部分字符）的形式来达到我们的目的。那么这个前缀索引应该如何选取呢？这就涉及到一个叫索引选择性的概念。

索引选择性：不重复的索引值总数（也称为基数——cardinality）和数据表的记录总数的比值。比值越高，代表索引的选择性越好。

索引的选择性越高，则查询效率越高，因为选择性高的索引可以让 MySQL 在查询时过滤掉更多的行。
主键或唯一索引的选择性是 1，这是最好的索引选择性，性能也是最好的。

举例说明：假如有一张一万行记录的表，其中有一个性别列 sex，这个列的包含选项就两个：男、女。那么为该列创建索引的话，其索引的选择性为万分之二，此时在性别这一列创建索引是没有啥意义的。再假设个极端情况，列内的数据都是女，那么索引的选择性为万分之一，其效率还不如直接进行全表扫描。

我们可以通过 SHOW INDEXES FROM table 来查看每个索引 cardinality 的值以评估索引设计的合理性。

怎么选择这个比例呢，我们可以分别取前 3，4，5，6，7 的前缀索引，然后再比较下选择这几个前缀索引的选择性，执行以下语句：

SELECT 
 COUNT(DISTINCT LEFT(city,3))/COUNT(*) as sel3,
 COUNT(DISTINCT LEFT(city,4))/COUNT(*) as sel4,
 COUNT(DISTINCT LEFT(city,5))/COUNT(*) as sel5,
 COUNT(DISTINCT LEFT(city,6))/COUNT(*) as sel6,
 COUNT(DISTINCT LEFT(city,7))/COUNT(*) as sel7
FROM city_demo;

获得结果如下：

sel3	sel4	sel5	sel6	sel7
0.0239	0.0293	0.0305	0.0309	0.0310

可以看到当前缀长度为 7 时，索引选择性提升的比例已经很小了，也就是说应该选择 city 的前六个字符作为前缀索引，如下：

ALTER TABLE city_demo ADD KEY(city(6));

我们当前是以平均选择性为指标的，有时候这样是不够的，还得考虑最坏情况下的选择性，以这个 demo 为例，可能一些人看到选择 4，5 的前缀索引与选择 6，7 的选择性相差不大，那就得看下选择 4，5 的前缀索引分布是否均匀了：

SELECT 
    COUNT(*) AS  cnt, 
    LEFT(city, 4) AS pref
  FROM city_demo GROUP BY pref ORDER BY cnt DESC LIMIT 5;

可能会出现以下结果：

cnt	pref
305	Sant
200	Toul
90	Chic
20	Chan

可以看到分布极不均匀，以 Sant、Toul 为前缀索引的数量极多，这两者的选择性都不是很理想，所以要选择前缀索引时也要考虑最差的选择性的情况。

前缀索引虽然能实现索引占用空间小且快的效果，但它也有明显的弱点，MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY ，而且也无法使用前缀索引做覆盖扫描，前缀索引也有可能增加扫描行数。

假设有以下表数据及要执行的 SQL：

id	email
1	zhangssxyz@163.com
2	zhangs1@163.com
3	zhangs1@163.com
4	zhangs1@163.com

SELECT id,email FROM user WHERE email = 'zhangssxyz@xxx.com';

如果我们针对 email 设置的是整个字段的索引，则上表中根据「zhangssxyz@163.com」查询到相关记录后，再查询此记录的下一条记录，发现没有，停止扫描，此时可知只扫描一行记录。如果我们以前六个字符（即 email(6)）作为前缀索引，则显然要扫描四行记录，并且获得行记录后不得不回到主键索引再判断 email 字段的值，所以使用前缀索引要评估它带来的这些开销。

另外有一种情况我们可能需要考虑一下，如果前缀基本都是相同的该怎么办，比如现在我们为某市的市民建立一个人口信息表，则这个市人口的身份证虽然不同，但身份证前面的几位数都是相同的，这种情况该怎么建立前缀索引呢？

一种方式就是我们上文说的，针对身份证建立哈希索引，另一种方式比较巧妙，将身份证倒序存储，查的时候可以按如下方式查询：

SELECT field_list FROM t WHERE id_card = reverse('input_id_card_string');

这样就可以用身份证的后六位作前缀索引了，是不是很巧妙。

实际上，上文所述的索引选择性同样适用于联合索引的设计，如果没有特殊情况，我们一般建议在建立组合索引时，把选择性最高的列放在最前面，比如，对于以下语句：

SELECT * FROM payment WHERE staff_id = xxx AND customer_id = xxx;

单就这个语句而言， (staff_id，customer_id) 和 (customer_id，staff_id) 这两个联合索引我们应该建哪一个，可以统计下这两者的选择性。

SELECT 
 COUNT(DISTINCT staff_id)/COUNT(*) as staff_id_selectivity,
 COUNT(DISTINCT customer_id)/COUNT(*) as customer_id_selectivity,
 COUNT(*)
FROM payment;

结果为：

staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
COUNT(*): 16049

从中可以看出 customer_id 的选择性更高，所以应该选择 customer_id 作为第一列。

优化建议：

建议在建立联合索引时，把选择性最高的列放在最前面。
索引应尽可能少地占用存储空间。
不建议在更新频繁、区分度不高的属性上建立索引。
- 为更新频繁的字段建立索引会频繁更变 B+ 树，导致大大降低数据库性能。
- 像“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似。

6. 索引设计准则：三星索引

上文我们得出了一个索引列顺序的经验法则：将选择性最高的列放在联合索引的最前列，这种建立在某些场景可能有用，但通常不如避免随机 IO 和排序那么重要，这里引入索引设计中非常著名的一个准则：三星索引。

如果一个查询满足三星索引中三颗星的所有索引条件，理论上可以认为我们设计的索引是最好的索引。那么什么是三星索引：

第一颗星：参与条件查询（where、join、order by、group by）的列可以组成单列索引或联合索引。
第二颗星：避免排序，如果 SQL 语句中出现 order by column，那么取出的结果集就应该已经是按照 column 排序好的，而不需要再进行排序生成临时表。
第三颗星：SELECT 的列应尽量都是索引列，即尽量使用覆盖索引，避免回表查询。

所以对于如下语句：

SELECT age, name, city where age = xxx and name = xxx order by age;

设计的索引应该是 (age, name, city) 或 (name, age, city)。

当然了，三星索引是一个比较理想化的标准，实际操作往往只能满足期望中的一颗或两颗星，考虑如下语句：

SELECT age, name, city where age >= 10 and age <= 20 and city = xxx order by name desc;

假设我们分别为这三列建了联合索引（不限顺序），则显然都符合第三颗星（使用了覆盖索引）；
如果索引是 (city, age, name)，则虽然满足了第一颗星，但排序无法用到索引，不满足第二颗星；
如果索引是 (city, name, age)，则第二颗星满足了，但此时 age 在 WHERE 中的搜索条件又无法满足第一星。

另外第三颗星（尽量使用覆盖索引）也无法完全满足，试想我要 SELECT 多列，要把这么多列都设置为联合索引吗，这对索引的维护是个问题，因为每一次表的 CURD 都伴随着索引的更新，很可能频繁伴随着页分裂与页合并。

综上所述，三星索引只是给我们构建索引提供了一个参考，索引设计应该尽量靠近三星索引的标准，但实际场景我们一般无法同时满足三星索引，一般我们会优先选择满足第三颗星（因为回表代价较大），至于第一，二颗星就要依赖于实际的成本及实际的业务场景考虑。

7. 思考题

现有一张数据库表，表里的字段只有主键索引 (id) 和联合索引 (a, b, c)，然后执行 select * from t where c = 0; 发现这条语句发现走的是索引，在此产生了两点疑惑：

where c 这个条件并不符合组合索引的最左匹配原则，怎么查询的时候走了组合索引呢？
在这个数据表加了非索引字段，执行同样的查询语句后，怎么变成走全表扫描呢？

问题一解答：

首先，这张表的字段没有「非索引」字段，所以「select *」相当于「select id, a, b, c」，由于这个查询的内容和条件都在组合索引树里，因为联合索引树的叶子节点包含「索引列+主键」，所以查组合索引树就能查到全部结果了，即覆盖索引。

但是执行计划里的 type 是 index，这代表着是通过全扫描组合索引树的方式查询到数据的，这是因为 where c 并不符合联合索引最左匹配原则。而如果写了个符合最左原则的 select 语句，那么 type 就是 ref，这个效率就比 index 全扫描要高一些。

那为什么选择全扫描组合索引树，而不扫描全表（聚簇索引树）呢？

因为组合索引树的记录比聚簇索引树的记录要少得多，而且这个 select * 不用执行回表操作，所以直接遍历组合索引树的代价要比遍历聚集索引树要小得多，因此 MySQL 执行器选择了全扫描组合索引树。

问题二解答：

因为加了其他字段后，select * from t where c = 0; 查询的内容无法直接在组合索引树里找到，且查询条件也不符合最左匹配原则，这样既不能覆盖索引也不能执行回表操作，所以这时只能通过扫描全表（type 为 all，key 为 null）来查询到所有的数据。

posted @ 2021-06-10 10:09 Juno3550 阅读(527) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

0. 前言

1. 什么是索引，索引的作用

1.1 索引能极大地减少扫描行数

1.2 索引可以帮助服务器避免排序和临时表

1.3 索引可以将随机 I/O 变成顺序 I/O

由 MySQL 优化器做选择

建立索引也有不好之处

索引不适合的场景

2. 索引的类型

索引类型

Mysql 索引语法

创建索引

查看索引

删除索引

索引类型详解

普通索引/二级索引（INDEX/KEY）

唯一索引（UNIQUE）

主键（PRIMARY）

联合索引

全文索引（FULLTEXT）

3. 索引的数据结构

哈希表

B+ 树

二叉查找树（BST）

平衡二叉查找树（AVL）

B 树：改造二叉树

B 树结构

在 B 树中查询数据

B+ 树：改造 B 树

示例 1：等值查询

示例 2：范围查询:

4. MySQL 的索引实现

MyISAM 索引

主键索引

根据主键等值查询数据

根据主键范围查询数据

辅助索引（二级索引）

InnoDB 索引

聚簇索引（主键索引）

等值查询数据

非聚簇索引（辅助索引）

等值查询数据

组合索引

以表 abc_innodb 为例，id 为主键索引，创建了一个联合索引 idx_abc(a, b, c)：

组合索引的数据结构

组合索引的查询过程

最左匹配原则

覆盖索引

优化示例：使用覆盖索引，避免回表

5. 高性能索引策略

加了索引，为何却不生效？

1）索引列是表达式或函数的一部分

2）隐式类型转换

3）隐式编码转换

4）使用 order by 造成的全表扫描

5）like 通配符可能会导致索引失效

6）where 语句中包含 or 时，可能会导致索引失效

无法避免对索引列使用函数时，怎么使用索引？

前缀索引与索引选择性

6. 索引设计准则：三星索引

7. 思考题

问题一 解答：

问题二 解答：

公告

问题一解答：

问题二解答：