高性能MYSQL（二）索引基础、高性能策略、失效情形、索引性能测试

晓风残月淡

693人浏览 · 2026-01-16 07:10:03

晓风残月淡 · 2026-01-16 07:10:03 发布

索引，在MySQL中也叫作键(key)，是存储引擎用于快速找到记录的一种数据结构。

要想获得好的性能，索引至关重要。尤其是当表中的数据量越来越大时，索引对性能的影响愈发重要。

在数据量较小且负载较低时，缺少合适的索引对性能的影响可能还不明显，但当数据量逐渐增大时，性能会急剧下降。

索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高几个数量级，“最优”的索引有时比一个“好的”索引性能要好两个数量级。

创建一个真正“最优”的索引经常需要重写查询。

一、索引基础

如果想在一本书中找到某个特定主题，一般会先看书的“索引”，找到对应的页码。

在MySQL中，存储引擎用类似的方法使用索引，其先在索引中找到对应值，然后根据匹配的索引记录找到对应的数据行。

索引可以包含一列或多列的值。

如果索引包含多列，那么列的顺序也十分重要，因为MySQL只能有效地使用索引的最左前缀列。

创建一个包含两列的索引，和创建两个只包含一列的索引是大不相同的。

（一）索引的类型

索引有很多种类型，可以为不同的场景提供更好的性能。在MySQL中，索引是在存储引擎层而不是服务器层实现的。

所以，并没有统一的索引标准：不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

索引是存储引擎用于快速查找记录的一种数据结构。

例如，以下查询语句。若没有索引，数据库就只能扫描整个员工表，然后依次判断每个数据记录中的员工编号是否等于5并且返回满足条件的数据。这种查找数据的方法被称为全表扫描。

//查找id为5的员工信息
select * from employee where id=5;

1. B-索引

当人们谈论索引的时候，如果没有特别指明类型，那多半说的是B-Tree索引，它使用B-Tree数据结构来存储数据。

B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。
在这里插入图片描述

在这里插入图片描述

（1）索引加快数据访问的理由

B-tree索引能够加快数据访问的速度，这是因为有了索引，在查询某些条件的数据时，存储引擎不再需要进行全表扫描。

而是从索引的根节点（图中并未画出）开始进行搜索，根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。

通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么找到对应的值，要么该记录不存在。

（2）`B-tree`适合按范围查询

B-tree是按照索引列中的数据大小顺序存储的，所以很适合按照范围来查询。

例如，在一个基于文本列的索引树上遍历，按字母顺序传递连续的值进行范围查找是非常合适的，所以，像“找出所有以I到K开头的名字”这样的查找效率会非常高。

假设有如下数据表：

CREATE TABLE People (
    last_name varchar(50) not null,
    first_name varchar(50) not null,
    dob date not null,
    gender enum('m', 'f') not null,
    key(last_name, first_name, dob)
);

对于表中的每一行数据，索引中包含了last_name、first_name和dob列的值。（注意国外last_name是姓，这里为符合习惯放前面了）
在这里插入图片描述

请注意，索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序。看一下最后两个条目，两个人的姓和名都一样，这时，则根据他们的出生日期来排列。

（3）可以使用B-Tree索引的查询类型

B-tree索引适用于全键值、键值范围或键前缀查找。

其中键前缀查找只适用于根据最左前缀的查找。前面所述的索引对如下类型的查询有效。

全值匹配

全值匹配指的是和索引中的所有列进行匹配，例如前面提到的索引可用于查找姓名为Cuba Allen、出生于1960-01-01的人。

匹配最左前缀

前面提到的索引可用于查找所有姓为Allen的人，即只使用索引的第一列。

匹配列前缀

也可以只匹配某一列的值的开头部分。例如前面提到的索引可用于查找所有以J开头的姓的人。这里也只使用了索引的第一列。

匹配范围值

例如前面提到的索引可用于查找姓在Allen和Barrymore之间的人。这里也只使用了索引的第一列。

精确匹配某一列并范围匹配另外一列

前面提到的索引也可用于查找**所有姓为Allen，并且名字是字母K开头（比如Kim、Karl等）**的人。即第一列last_name全匹配，第二列first_name范围匹配。

只访问索引的查询

B-Tree通常可以支持“只访问索引的查询”，即查询只需要访问索引，而无须访问数据行。后面我们将单独讨论这种“覆盖索引”的优化。

（4）关于排序

因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY操作（按顺序查找）。

一般来说，如果B-Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序。所以，如果ORDER BY子句满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求。

（5）B-Tree索引的限制

如果不是按照索引的最左列开始查找，则无法使用索引。

例如上面例子中的索引无法用于查找名字为Bill的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。类似地，也无法查找姓氏以某个字母结尾的人。

不能跳过索引中的列。

也就是说，前面所述的索引无法用于查找姓为Smith并且在某个特定日期出生的人。如果不指定名(first_name)，则MySQL只能使用索引的第一列。

如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

例如有查询WHERE last_name=＇Smith＇ AND first_name LIKE ＇J%＇ AND dob = ＇1976-12-23＇，这个查询只能使用索引的前两列，因为这里LIKE是一个范围条件（但是服务器可以把其余列用于其他目的）。

如果范围查询列值的数量有限，那么可以通过使用多个等于条件来代替范围条件。

到这里读者应该可以明白，前面提到的索引列的顺序是多么的重要：这些限制都和索引列的顺序有关。在优化性能的时候，可能需要使用相同的列但顺序不同的索引来满足不同类型的查询需求。

2.哈希索引

哈希索引(hash index)基于哈希表实现，只有精确匹配索引所有列的查询才有效。

对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码(hash code)，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。

哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。

因为索引自身只需存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。

（2）哈希索引的限制

哈希索引只包含哈希值和行指针，而不存储字段值

所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。

哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。

哈希索引也不支持部分索引列匹配查找

因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列(A,B)上建立哈希索引，如果查询只有数据列A，则无法使用该索引。

哈希索引只支持等值比较查询

包括=、IN()、＜=＞（注意<>和<=>是不同的操作）。也不支持任何范围查询，例如 WHERE price ＞ 100。

访问哈希索引的数据非常快

除非有很多哈希冲突（不同的索引列值却有相同的哈希值）。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行。

如果哈希冲突很多的话，一些索引维护操作的代价也会很高。

例如，如果在某个选择性很低（哈希冲突很多）的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

因为这些限制，哈希索引只适用于某些特定的场合。而一旦适合哈希索引，则它带来的性能提升将非常显著。

举个例子，在数据仓库应用中有一种经典的**“星型”schema**，需要关联很多查找表，哈希索引就非常适合查找表的需求。

（3）自适应哈希索引

InnoDB引擎有一个特殊的功能叫做“自适应哈希索引(adaptive hash index)”。

当InnoDB注意到某些索引值被使用得非常频繁时，它会在内存中基于B-Tree索引之上再创建一个哈希索引，这样就让B-Tree索引也具有哈希索引的一些优点，比如快速的哈希查找。

这是一个完全自动的、内部的行为，用户无法控制或者配置，不过如果有必要，完全可以关闭该功能。

（4）创建自定义哈希索引的案例

如果存储引擎不支持哈希索引，则可以模拟像InnoDB一样创建哈希索引，这可以享受一些哈希索引的便利，例如只需要很小的索引就可以为超长的键创建索引。

思路很简单：在B-Tree基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事，因为还是使用B-Tree进行查找，但是它使用哈希值而不是键本身进行索引查找。你需要做的就是在查询的WHERE子句中手动指定使用哈希函数。

下面是一个实例，例如需要存储大量的URL，并需要根据URL进行搜索查找。如果使用B-Tree来存储URL，存储的内容就会很大，因为URL本身都很长。正常情况下会有如下查询：

mysql> SELECT id FROM url WHERE url="http://www.mysql.com";

若删除原来URL列上的索引，而新增一个被索引的url_crc列，使用CRC32做哈希，就可以使用下面的方式查询：

mysql> SELECT id FROM url WHERE url="http://www.mysql.com"
    -> AND url_crc=CRC32("http://www.mysql.com");

这样做的性能会非常高，因为MySQL优化器会使用这个选择性很高而体积很小的基于url_crc列的索引来完成查找（在上面的案例中，索引值为1560514994）。

即使有多个记录有相同的索引值，查找仍然很快，只需要根据哈希值做快速的整数比较就能找到索引条目，然后一一比较返回对应的行。另外一种方式就是对完整的URL字符串做索引，那样会非常慢。

这样实现的缺陷是需要维护哈希值。可以手动维护，也可以使用触发器实现。

（5）哈希函数的选择

如果采用触发器的方式，记住不要使用SHA1()和MD5()作为哈希函数。

因为这两个函数计算出来的哈希值是非常长的字符串，会浪费大量空间，比较时也会更慢。

SHA1()和MD5()是强加密函数，设计目标是最大限度消除冲突，但这里并不需要这样高的要求。简单哈希函数的冲突在一个可以接受的范围，同时又能够提供更好的性能。

如果数据表非常大，CRC32()会出现大量的哈希冲突，则可以考虑自己实现一个简单的64位哈希函数。这个自定义函数要返回整数，而不是字符串。

一个简单的办法可以使用MD5()函数返回值的一部分来作为自定义哈希函数。
在这里插入图片描述

（6）处理哈希冲突

当使用哈希索引进行查询的时候，必须在WHERE子句中包含常量值：

mysql> SELECT id FROM url WHERE url_crc=CRC32("http://www.mysql.com")
    -> AND url="http://www.mysql.com";

一旦出现哈希冲突，另一个字符串的哈希值也恰好是1560514994，则下面的查询是无法正确工作的。

mysql> SELECT id FROM url WHERE url_crc=CRC32("http://www.mysql.com");

CRC32()返回的是32位的整数，当索引有93 000条记录时出现冲突的概率是1%。

要避免冲突问题，必须在WHERE条件中带入哈希值和对应列值。

如果不是想查询具体值，例如只是统计记录数（不精确的），则可以不带入列值，直接使用CRC32()的哈希值查询即可。还可以使用如FNV64()函数作为哈希函数，哈希值为64位，速度快，且冲突比CRC32()要少很多。

3.空间数据索引(R-Tree)

MyISAM表支持空间索引，可以用作地理数据存储。

和B-Tree索引不同，这类索引无须前缀查询。空间索引会从所有维度来索引数据。

查询时，可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数如MBRCONTAINS()等来维护数据。

MySQL的GIS支持并不完善，所以大部分人都不会使用这个特性。开源关系数据库系统中对GIS的解决方案做得比较好的是PostgreSQL的PostGIS。

4.全文索引

全文索引是一种特殊类型的索引，它查找的是文本中的关键词，而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。

它有许多需要注意的细节，如停用词、词干和复数、布尔搜索等。

全文索引更类似于搜索引擎做的事情，而不是简单的WHERE条件匹配。

在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突，全文索引适用于MATCH AGAINST操作，而不是普通的WHERE条件操作。

对于现代应用开发，可以了解但不必深入，建议优先掌握Elasticsearch等专门搜索技术。不过理解其原理和使用场景仍有价值。

-- 1. 分词限制（特别是中文）
-- MySQL 5.7+支持ngram分词，但效果有限
CREATE TABLE articles (
    id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(200),
    content TEXT,
    FULLTEXT(title, content) WITH PARSER ngram
);

-- 搜索"中国"可能返回"中国人民"、"中国制造"
-- 但无法进行更智能的分词（如实体识别、同义词）

-- 2. 性能问题（数据量超过百万后性能下降明显）
-- 3. 功能有限：缺乏相关性评分调优、近义词扩展等功能
-- 4. 配置复杂：需要调整停用词、最小词长等参数
-- 5. 不支持分布式：无法横向扩展

（二）索引的优点

1.索引的优点

索引可以让服务器快速地定位到表的指定位置。

但是这并不是索引的唯一作用，到目前为止可以看到，根据创建索引的数据结构不同，索引也有一些其他的附加作用。

最常见的B-tree索引，按照顺序存储数据，所以MySQL可以用来做ORDER BY和GROUP BY操作。

因为数据是有序的，所以B-tree也就会将相关的列值都存储在一起。

最后，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询。

据此特性，总结下来索引有如下三个优点：

索引大大减少了服务器需要扫描的数据量。
索引可以帮助服务器避免排序和临时表。
索引可以将随机I/O变为顺序I/O。

“索引”这个主题完全值得单独写一本书，如果想深入理解这部分内容，强烈建议阅读由Tapio Lahdenmaki和Mike Leach编写的《Relational Database Index Design and the Optimizers》（Wiley出版社）一书，该书详细介绍了如何计算索引的成本和作用、如何评估查询速度、如何分析索引维护的代价和其带来的好处等。

Lahdenmaki和Leach在书中介绍了如何评价一个索引是否适合某个查询的“三星系统”(three-star system)：

索引将相关的记录放到一起则获得一星；
如果索引中的数据顺序和查找中的排列顺序一致则获得二星；
如果索引中的列包含了查询中需要的全部列则获得“三星”。

2.索引的局限

索引是最好的解决方案吗？索引并不总是最好的工具。

总的来说，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。

对于非常小的表，大部分情况下简单的全表扫描更高效。
对于中到大型的表，索引就非常有效。
但对于特大型的表，建立和使用索引的代价将随之增长。

这种情况下，则需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录地匹配。

如果表的数量特别多，可以建立一个元数据信息表，用来查询需要用到的某些特性。

例如执行那些需要聚合多个应用分布在多个表的数据的查询，则需要记录“哪个用户的信息存储在哪个表中”的元数据，这样在查询时就可以直接忽略那些不包含指定用户信息的表。

对于大型系统，这是一个常用的技巧。事实上，Infobright就是使用类似的实现。

对于TB级别的数据，定位单条记录的意义不大，所以经常会使用块级别元数据技术来替代索引。

二、高性能的索引策略

正确地创建和使用索引是实现高性能查询的基础。前面已经介绍了各种类型的索引及其对应的优缺点。

现在我们一起来看看如何真正地发挥这些索引的优势。高效地选择和使用索引有很多种方式，其中有些是针对特殊案例的优化方法，有些则是针对特定行为的优化。

使用哪个索引，以及如何评估选择不同索引的性能影响的技巧，则需要持续不断地学习。接下来的几个小节将帮助读者理解如何高效地使用索引。

（一）独立的列

我们通常会看到一些查询不当地使用索引，或者使得MySQL无法使用已有的索引。如果查询中的列不是独立的，则MySQL就不会使用索引。

“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。

例如，下面这个查询无法使用actor_id列的索引：

mysql> SELECT actor_id FROM sakila.actor WHERE actor_id + 1 = 5;

凭肉眼很容易看出WHERE中的表达式其实等价于actor_id = 4，但是MySQL无法自动解析这个方程式。

这完全是用户行为。我们应该养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一侧。

下面是另一个常见的错误：

mysql> SELECT ... WHERE TO_DAYS(CURRENT_DATE) - TO_DAYS(date_col) <= 10;

（二）前缀索引和索引选择性

有时候需要索引很长的字符列，这会让索引变得大且慢。

一个策略是前面提到过的模拟哈希索引。但有时候这样做还不够，还可以做些什么呢？

通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。

1.索引的选择性的定义

索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数(#T)的比值，范围从1/#T到1之间。

索引的选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。

唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

假设我们有一个用户表 users，其中 email 字段存储电子邮件地址（平均长度约20-30字符）。

该表有100万条记录，我们想在 email 列上创建索引以提高查询效率，但完整的索引会占用较大空间。我们需要确定合适的前缀长度。

首先计算完整 email 列完整列索引的选择性，作为基准：

-- 计算完整列的选择性
SELECT 
    COUNT(DISTINCT email) / COUNT(*) AS full_selectivity,
    COUNT(DISTINCT email) AS distinct_emails,
    COUNT(*) AS total_rows
FROM users;
-- 假设返回结果：
-- total_rows = 1,000,000
-- distinct_emails = 950,000
-- full_selectivity = 0.95

所以这里完整列索引的选择性为0.95。

2.前缀索引的选择性

对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为MySQL不允许索引这些列的完整长度。

诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长（以便节约空间）。

前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的“基数”应该接近于完整列的“基数”。

为了决定前缀的合适长度，需要找到最常见的值的列表，然后和最常见的前缀列表进行比较。

接下来测试不同前缀长度的选择性，找到接近完整列选择性的最短前缀：

-- 测试5到12个字符前缀的选择性
SELECT
    COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) AS pref5,
    COUNT(DISTINCT LEFT(email, 6)) / COUNT(*) AS pref6,
    COUNT(DISTINCT LEFT(email, 7)) / COUNT(*) AS pref7,
    COUNT(DISTINCT LEFT(email, 8)) / COUNT(*) AS pref8,
    COUNT(DISTINCT LEFT(email, 9)) / COUNT(*) AS pref9,
    COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) AS pref10,
    COUNT(DISTINCT LEFT(email, 11)) / COUNT(*) AS pref11,
    COUNT(DISTINCT LEFT(email, 12)) / COUNT(*) AS pref12
FROM users;

-- 假设结果如下：
前缀长度	选择性	差异（与0.95比较）
5	0.65	-0.30
6	0.78	-0.17
7	0.85	-0.10
8	0.90	-0.05
9	0.92	-0.03
10	0.94	-0.01
11	0.945	-0.005
12	0.948	-0.002

选择标准：在选择性接近完整列（0.95）的前提下，选择最短的前缀以节省空间。

分析结果：

前缀长度8：选择性0.90，差异0.05，索引空间较小
前缀长度10：选择性0.94，差异0.01，已非常接近完整列
前缀长度12：选择性0.948，差异几乎可忽略

考虑到性能和空间的平衡：

如果存储空间紧张，选择前缀长度8（损失5%的选择性，但空间节省明显）
如果追求更高查询性能，选择前缀长度10（仅损失1%的选择性）

为确保前缀能有效区分数据，可检查最常见的前缀是否冲突：

-- 查看最常见的10个完整email
SELECT email, COUNT(*) AS cnt
FROM users
GROUP BY email
ORDER BY cnt DESC
LIMIT 10;

-- 查看这些email的前8个字符是否重复
SELECT LEFT(email, 8) AS prefix, COUNT(DISTINCT email) AS distinct_emails
FROM users
GROUP BY LEFT(email, 8)
HAVING COUNT(DISTINCT email) > 1
ORDER BY COUNT(DISTINCT email) DESC
LIMIT 10;

如果大多数前缀对应唯一的完整email，则前缀长度合适。

3.创建前缀索引

在上面的示例中，已经找到了合适的前缀长度，下面演示一下如何创建前缀索引：

-- 假设我们选择前缀长度10
ALTER TABLE users ADD INDEX idx_email_prefix (email(10));

前缀索引是一种能使索引更小、更快的有效办法.

但另一方面也有其缺点：MySQL无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。

一个常见的场景是针对很长的十六进制唯一ID使用前缀索引。

在前面的章节中已经讨论了很多有效的技术来存储这类ID信息，但如果使用的是打包过的解决方案，因而无法修改存储结构，那该怎么办？

例如使用基于MySQL的应用在存储网站的会话(SESSION)时，需要在一个很长的十六进制字符串上创建索引。

-- 会话表示例
CREATE TABLE sessions (
    session_id CHAR(32) NOT NULL,
    user_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 选择前8个字符作为前缀（16^8 ≈ 43亿种可能）
ALTER TABLE sessions ADD INDEX idx_session_id_prefix (session_id(8));

此时如果采用长度为8的前缀索引通常能显著地提升性能，并且这种方法对上层应用完全透明。

(三）多列索引

多人对多列索引的理解都不够。一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。

1.为每个列建立独立索引的问题

先来看第一个问题，为每个列创建独立的索引，从SHOW CREATE TABLE中很容易看到这种情况：

CREATE TABLE t (
    c1 INT,
    c2 INT,
    c3 INT,
    KEY(c1),
    KEY(c2),
    KEY(c3)
);

这种索引策略，一般是由于人们听到一些专家诸如“把WHERE条件里面的列都建上索引”这样模糊的建议导致的。

实际上这个建议是非常错误的。这样一来最好的情况下也只能是“一星”索引，其性能比起真正最优的索引可能差几个数量级。

有时如果无法设计一个“三星”索引，那么不如忽略掉WHERE子句，集中精力优化索引列的顺序，或者创建一个全覆盖索引。

2.索引合并的策略

在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL引入了一种叫“索引合并”(index merge)的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。

这种算法有三个变种：OR条件的联合(union)，AND条件的相交(intersection)，组合前两种情况的联合及相交。

例如，表film_actor在字段film_id和actor_id上各有一个单列索引。

mysql> SELECT film_id, actor_id FROM sakila.film_actor
    -> WHERE actor_id = 1 OR film_id = 1;

在老的MySQL版本中，MySQL对这个查询会使用全表扫描。除非改写成如下的两个查询UNION的方式：

mysql> SELECT film_id, actor_id FROM sakila.film_actor WHERE actor_id = 1
    -> UNION ALL
    -> SELECT film_id, actor_id FROM sakila.film_actor WHERE film_id = 1
    ->   AND actor_id <> 1;

下面的查询就使用了两个索引扫描的联合，通过EXPLAIN中的Extra列可以看到这点：
在这里插入图片描述

MySQL会使用这类技术来优化复杂查询，所以在某些语句的Extra列中还可以看到嵌套操作。

索引合并策略有时候效果非常不错，但更多的时候，它说明了表中的索引建得很糟糕：

当优化器需要对多个索引做相交（相交操作是使用“索引合并”的一种情况，另一种是做联合操作）操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
当优化器需要对多个索引做联合操作时（通常有多个OR条件），通常需要在算法的缓存、排序和合并操作上耗费大量CPU和内存资源，尤其是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
更重要的是，优化器不会把这些操作计算到“查询成本”(cost)中，优化器只关心随机页面读取。这会使得查询的成本被“低估”，导致该执行计划还不如直接进行全表扫描。这样做不但会消耗更多的CPU和内存资源，还可能会影响并发的查询，但如果单独运行这样的查询则往往会忽略对并发性的影响。通常来说，使用UNION改写查询，往往是最好的办法。

如果在EXPLAIN中看到有索引合并，那么就应该好好检查一下查询语句的写法和表的结构，看是不是已经是最优的。

也可以通过参数optimizer_switch来关闭索引合并功能，还可以使用IGNORE INDEX语法让优化器强制忽略掉某些索引，从而避免优化器使用包含索引合并的执行计划。

(四）选择合适的索引列顺序

最容易让人感到困惑的问题之一就是索引列的顺序。

正确的顺序依赖于使用该索引的查询语句，同时，还需要考虑如何更好地满足排序和分组操作的需要。

在一个多列B-tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。

所以索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCT等子句的查询需求。

所以，多列索引的列顺序至关重要。

在Lahdenmaki和Leach的“三星索引”系统中，列顺序还决定了一个索引是否能够成为一个真正的“三星索引”。

1.如何选择索引的列顺序

对于如何选择索引的列顺序有一个重要的经验法则：将选择性最高的列放到索引最前列。

这个建议准确吗？在很多场景中可能有帮助，但是要全面地考虑各种场景的话，考虑如何避免大量随机I/O和排序可能更重要。

（场景不同则选择不同，没有一个放之四海皆准的法则。这里只是说明，这个经验法则可能没有你想象中那么重要。）

当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。

这时索引的作用只是优化查询语句中的WHERE条件。

在这种情况下，按这个原则设计的索引确实能够最快地过滤出需要的行，对于在WHERE子句中只使用了索引部分前缀列的查询来说，选择性也更高。

2.性能与查询条件

然而，性能不只依赖于所有索引列的选择性（整体基数），也和查询条件的具体值有关，也就是和值的分布有关。

这和前面介绍的选择前缀的长度需要考虑的因素一样。可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。

以下面的查询为例：

SELECT * FROM payment WHERE staff_ id = 2 AND customer_ id = 584;

是应该创建一个(staff_id、customer_id)索引还是应该颠倒一下顺序？

这时，可以通过运行某些查询来确定在这个表中值的分布情况，并确定哪列的选择性更高。先用下面的查询预测一下，看看各个WHERE条件的分支对应的数据基数有多大：

mysql> SELECT SUM(staff_id = 2), SUM(customer_id = 584) FROM payment\G
*************************** 1. row ***************************
SUM(staff_id = 2): 7992
SUM(customer_id = 584): 30

根据前面的经验法则，应该将索引列customer_id放到前面，因为对应条件值的customer_id数量更小。我们再来看看对于这个customer_id的条件值，对应的staff_id列的选择性如何：

mysql> SELECT SUM(staff_id = 2) FROM payment WHERE customer_id = 584\G
*************************** 1. row ***************************
SUM(staff_id = 2): 17

这样做有一个地方需要注意，查询的结果非常依赖于选定的具体值。如果按上述办法优化，可能对其他一些条件值的查询不公平，服务器的整体性能可能会变得更糟，或者其他某些查询的运行变得不如预期。

如果是从诸如pt-query-digest这样的工具的报告中提取“最差”查询，那么再按上述办法选定索引顺序，往往可以获得更好的性能。如果没有运行类似的查询来确认实际的情况，那么最好还是按经验法则来做，因为经验法则考虑的是全局基数和选择性，而不是某个具体查询：

mysql> SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,
    ->   COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,
    ->   COUNT(*)
    -> FROM payment\G
*************************** 1. row ***************************
staff_id_selectivity: 0.0001
customer_id_selectivity: 0.0373
COUNT(*): 16049

customer_id的选择性更高，所以答案是将其作为索引的第一列：

ALTER TABLE payment ADD KEY(customer_id, staff_id);

3.特殊用户和组

和前缀索引例子中描述的情况一样，当使用前缀索引的时候，在某些条件值的基数比正常值高的时候，问题就来了。

例如，在某些应用程序中，对于没有登录的用户，都将其用户名记录为“guest”，在记录用户行为的会话(session)表和其他记录用户活动的表中，“guest”就成为一个特殊用户ID，一旦查询涉及这个用户，那么和对于正常用户的查询就大不相同了，因为通常有很多会话都是没有登录的。

有些系统账号也会导致类似的问题。一个应用通常都有一个特殊的管理员账号，和普通账号不同，它并不是一个具体的用户，系统中所有的其他用户都是这个用户的好友，所以系统往往通过它向网站的所有用户发送状态通知和其他消息。这个账号的巨大的好友列表很容易导致网站出现服务器性能问题。

这在实际中是一个非常典型的问题。对于任何异常用户，不仅那些用于管理应用的设计糟糕的账号会有同样的问题，那些拥有大量好友、图片、状态、收藏的用户，也会有前面提到的与系统账号一样的问题。

下面是一个我们遇到的真实案例。在一个商品购买和经验分享的用户论坛系统中，这个特殊表中的查询运行得非常慢：

SELECT COUNT(DISTINCT threadId) AS COUNT_VALUE
FROM Message
WHERE (groupId = 10137) 
  AND (userId = 1288826) 
  AND (anonymous = 0)
ORDER BY priority DESC, modifiedDate DESC;

这个查询看似没有建立合适的索引，所以客户咨询我们是否可以优化。EXPLAIN的结果如下：
在这里插入图片描述
MySQL为这个查询选择了索引(groupId，userId)，如果不考虑列的基数，这看起来是一个非常合理的选择。但如果考虑一下user ID和group ID条件匹配的行数，你可能就会有不同的想法了：

mysql> SELECT COUNT(*), SUM(groupId = 10137),
    ->   SUM(userId = 1288826), SUM(anonymous = 0)
    -> FROM Message\G
*************************** 1. row ***************************
count(*): 4142217
sum(groupId = 10137): 4092654
sum(userId = 1288826): 1288496
sum(anonymous = 0): 4141934

从上面的结果来看，符合组(groupId)条件的几乎满足表中的所有行，符合用户(userId)条件的有130万条记录，也就是说，索引基本上没什么用。

因为这些数据是从其他应用中迁移过来的，迁移的时候把所有的消息都赋予了管理员组的用户。

这个案例的解决办法是修改应用程序代码，区分这类特殊用户和组，禁止针对这类用户和组执行这个查询。

（五）聚簇索引

聚簇索引（Clustered Index）并不是一种单独的索引类型，而是一种数据存储方式。

想象一本书的结构

聚簇索引：就像一本按拼音顺序排列的电话簿

内容（姓名+电话）按拼音顺序直接存储
要找"张三"，直接翻到"Z"开头的部分
数据本身就是索引，索引就是数据

非聚簇索引（二级索引）：就像一本书的目录

目录页上写着"第三章…第45页"
你需要：1.查目录 2.翻到第45页 3.看内容
索引和数据是分开存储的

具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-tree索引和数据行。

聚簇索引B+树结构：
       ┌─────────────────┐
       │   根节点        │
       │  [10, 20, 30]   │ ← 存储主键值和子节点指针
       └─────────────────┘
              │
    ┌─────────┼─────────┐
    ▼         ▼         ▼
┌──────┐  ┌──────┐  ┌──────┐
│叶节点│  │叶节点│  │叶节点│ ← 叶子节点就是数据页
│id=10 │  │id=20 │  │id=30 │    存储完整的行数据
│name  │  │name  │  │name  │
│email │  │email │  │email │
│age   │  │age   │  │age   │
└──────┘  └──────┘  └──────┘

当表有聚簇索引时，它的数据行实际上存放在索引的叶子页(leaf page)中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。

因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。

因为是存储引擎负责实现索引，因此，不是所有的存储引擎都支持聚簇索引。

1.聚簇索引中的记录存放

我们主要关注InnoDB，但是这里讨论的原理对于任何支持聚簇索引的存储引擎都是适用的。图展示了聚簇索引中的记录是如何存放的。
在这里插入图片描述

注意，叶子页包含了一条记录的全部数据，但是节点页只包含了索引列。在这个案例中，索引列包含的是整数值。

-- 创建一个简单的表
CREATE TABLE users (
    id INT PRIMARY KEY,          -- 聚簇索引键
    name VARCHAR(50),
    email VARCHAR(100),
    age INT
) ENGINE=InnoDB;

-- 数据在磁盘上的存储方式：
-- 页（Page，默认16KB）是基本存储单位
-- 每个页存储多个数据行，按主键顺序排列

有些数据库服务器允许你选择用于聚簇的索引，但是MySQL内置的存储引擎都不支持这个特性。

如果你没有定义主键，InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB会隐式定义一个主键来作为聚簇索引。

这样做的缺点在于，所有需要使用这种隐藏主键的表都依赖一个单点的“自增值”，这可能会导致非常高的锁竞争，从而出现性能问题。

2.聚集数据的优缺点

(1)聚集数据的优点

聚集的数据有一些重要的优点：

你可以把相互关联的数据保存在一起。例如，在实现电子邮箱应用时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O。
数据访问更快。聚簇索引将索引和数据保存在同一个B-tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

如果在设计表和查询时能充分利用上面的优点，那么就能极大地提升性能。

(2)聚簇索引的缺点

同时，聚簇索引也有一些缺点：

聚簇数据最大限度地提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引也就没什么优势了。
插入速度严重依赖于插入顺序。按照主键的顺序插入行是将数据加载到InnoDB表中最快的方式。但如果不是按照主键的顺序加载数据，那么在加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。
更新聚簇索引列的代价很高，因为它会强制InnoDB将每个被更新的行移动到新的位置。
基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临页分裂(page split)的问题。当行的主键值要求必须将这一行插入某个已满的页中时，存储引擎会将该页分裂成两个页面来容纳该行，这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。

-- 自增主键（顺序写入）
-- 页1满了 → 开新页2 → 继续顺序写入
-- 效率高，页利用率高

-- UUID主键（随机写入）
-- 页1有空间但id值不连续 → 找合适位置插入
-- 导致：页分裂、碎片、随机I/O

-- 查看表碎片情况
SELECT 
    table_name,
    data_length,
    index_length,
    data_free
FROM information_schema.tables 
WHERE table_name = 'users';

-- 定期优化表（整理碎片）
OPTIMIZE TABLE users;  -- 注意：锁表操作

(3)二级索引

二级索引（非聚簇索引）可能比想象中的要更大，因为二级索引的叶子节点包含了引用行的主键列。

二级索引B+树（idx_email）：
       ┌─────────────────┐
       │     根节点      │
       │ [a@, m@, z@]    │
       └─────────────────┘
              │
    ┌─────────┼─────────┐
    ▼         ▼         ▼
┌──────┐  ┌──────┐  ┌──────┐
│叶节点│  │叶节点│  │叶节点│
│email │  │email │  │email │ ← 存储email值和对应的主键id
│id=   │  │id=   │  │id=   │
└──────┘  └──────┘  └──────┘

二级索引访问需要两次索引查找，而不是一次。

最后一点可能会让人有些疑惑，为什么二级索引需要两次索引查找？答案是，二级索引中保存的是“行指针”。

聚簇索引 vs 二级索引:

-- 创建二级索引
CREATE INDEX idx_email ON users(email);

-- 查询流程对比：
-- 场景1：使用聚簇索引（主键查询）
SELECT * FROM users WHERE id = 10;
-- 步骤：直接在聚簇索引B+树中找到id=10的叶子节点 → 返回数据

-- 场景2：使用二级索引（email查询）
SELECT * FROM users WHERE email = 'zhangsan@email.com';
-- 步骤：
-- 1. 在idx_email索引树中查找email → 得到主键id=5
-- 2. 用id=5回聚簇索引树查找 → 得到完整数据行
-- 这就是"回表"操作

要记住，二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。这意味着通过二级索引查找行，存储引擎需要找到二级索引的叶子节点，以获得对应的主键值，然后根据这个值去聚簇索引中查找对应的行。

这里做了双倍工作：两次B-tree查找而不是一次。对于InnoDB，自适应哈希索引能够减少这样的重复工作。

3.InnoDB的数据分布

为了更好地理解聚簇索引，我们来看看下表的InnoDB的布局：

CREATE TABLE layout_test (
    col1 int NOT NULL,
    col2 int NOT NULL,

    PRIMARY KEY(col1),
    KEY(col2)
);

假设该表的主键取值为1～10000，按照随机顺序插入并使用OPTIMIZE TABLE命令做了优化。

换句话说，数据在磁盘上的存储方式已经最优，但行的顺序是随机的。列col2的值是从1～100之间随机赋值的，所以有很多重复的值。

InnoDB以如图所示的方式存储数据。

在这里插入图片描述
聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针，以及所有的剩余列（在这个例子中是col2）。

如果主键是一个列前缀索引，InnoDB也会包含完整的主键列和剩下的其他列。

InnoDB的二级索引的叶子节点中存储的是主键值，并以此作为指向行的“指针”。这样的策略减少了当出现行移动或者数据页分裂时二级索引的维护工作。

使用主键值作为指针会让二级索引占用更多的空间，换来的好处是，InnoDB在移动行时无须更新二级索引中的这个“指针”。

图中展示了示例表中的col2索引。每一个叶子节点都包含了索引列（这里是col2），紧接着是主键值(col1)。

图中展示了B-tree的叶子节点结构，但这里故意省略了非叶子节点这样的细节。InnoDB的非叶子节点包含了索引列和一个指向下级节点的指针（下一级节点可以是非叶子节点，也可以是叶子节点）。这对聚簇索引和二级索引都适用。
在这里插入图片描述

（六）覆盖索引

大家通常都会根据查询的WHERE条件来创建合适的索引，不过这只是索引优化的一个方面。

设计优秀的索引应该考虑到整个查询，而不单是WHERE条件部分。

索引的确是一种高效找到数据的方式，但是如果MySQL还可以使用索引直接获取列的数据，这样就不再需要读取数据行了。

如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回表查询呢？

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为覆盖索引。

需要注意的是，只有B-tree索引可以用于覆盖索引。

覆盖索引是非常有用的工具，能够极大地提高性能。试想一下，如果查询只需要扫描索引而无须回表，会带来多少好处：

索引条目通常远小于数据行大小，所以如果只需要读取索引，那么MySQL就会极大地减少数据访问量。这对缓存型的应用负载非常重要，因为在这种情况下，响应时间大部分花费在数据拷贝上。覆盖索引对于I/O密集型的应用也有帮助，因为索引比数据更小，更容易全部放入内存中。
因为索引是按照列值的顺序存储的（至少在单页内如此），所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。可以通过OPTIMIZE命令使得索引完全实现顺序排列，这让简单的范围查询能使用完全顺序的索引访问。
由于InnoDB的聚簇索引的特点，覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了记录的主键值，所以如果二级索引能够覆盖查询，则可以避免对主键索引的二次查询。

在所有这些场景中，在索引中满足查询的成本一般比查询记录本身要小得多。

当执行一个被索引覆盖的查询（也叫作索引覆盖查询）时，在EXPLAIN的Extra列可以看到“Using index”的信息。

例如，表sakila.inventory有一个多列索引(store_id，film_id)。MySQL如果只需访问这两列，就可以使用这个索引做覆盖索引，如下所示：

mysql> EXPLAIN SELECT store_id, film_id FROM sakila.inventory\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: inventory
partitions: NULL
type: index
possible_keys: NULL
key: idx_store_id_film_id
key_len: 3
ref: NULL
rows: 4581
filtered: 100.00
Extra: Using index

在大多数存储引擎中，索引只能覆盖那些只访问索引中部分列的查询，不过，可以更进一步优化InnoDB。

回想一下，InnoDB的二级索引的叶子节点都包含了主键的值，这意味着InnoDB的二级索引可以有效地利用这些“额外”的主键列来覆盖查询。

mysql> EXPLAIN SELECT actor_id, last_name FROM sakila.actor WHERE last_name = 'HOPPER'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: actor
partitions: NULL
type: ref
possible_keys: idx_actor_last_name
key: idx_actor_last_name
key_len: 182
ref: const
rows: 2
filtered: 100.00
Extra: Using index

（七）使用索引扫描来做排序

1. 生成有序结果的两种方式

MySQL有两种方式可以生成有序的结果：通过排序操作，或者按索引顺序扫描。

如果在EXPLAIN的输出结果中，type列的值为“index”，则说明MySQL使用了索引扫描来做排序（注意，不要和Extra列的“Using index”搞混）。

(1) 排序操作（Using filesort）

-- 没有使用索引排序，需要内存排序
EXPLAIN SELECT * FROM users ORDER BY created_at DESC;
-- 输出：type=ALL, Extra=Using filesort

(2)索引顺序扫描（Using index scan）

-- 使用索引扫描直接得到有序结果
EXPLAIN SELECT id FROM users ORDER BY id;
-- 输出：type=index, Extra=Using index

重要区分：

type=index：使用索引扫描（可能用于排序）
Extra=Using index：使用覆盖索引（不需要回表）

2.索引排序的性能陷阱

扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那么就不得不每扫描一条索引记录都回表查询一次对应的记录。

这基本上都是随机I/O，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢，尤其是在I/O密集型的应用负载上。

-- 假设有索引 idx_created (created_at)
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    amount DECIMAL(10,2),
    created_at DATETIME,
    INDEX idx_created (created_at)
);

-- 查询1：仅使用索引列（覆盖索引）
EXPLAIN SELECT created_at FROM orders ORDER BY created_at;
-- 结果：type=index, Extra=Using index
-- 性能：快！只扫描索引树，不需要回表

-- 查询2：使用索引排序但需要其他列
EXPLAIN SELECT * FROM orders ORDER BY created_at;
-- 结果：type=index, Extra=NULL（可能需要Using filesort）
-- 实际执行步骤：
-- 1. 按created_at顺序扫描索引
-- 2. 对每个索引条目，通过主键回表获取完整行
-- 3. 返回结果
-- 性能：慢！随机I/O，每条记录都要回表

3.理想情况：索引同时满足查找和排序

MySQL可以使用同一个索引既满足排序，又用于查找行。因此，如果可能，设计索引时应该尽可能地同时满足这两项任务，这样是最好的。

示例1：完美匹配的索引

-- 创建复合索引
CREATE INDEX idx_user_created ON orders(user_id, created_at);

-- 查询：同时用于查找和排序
EXPLAIN SELECT * FROM orders 
WHERE user_id = 100 
ORDER BY created_at DESC;
-- 结果：type=ref, Extra=Using where
-- 说明：索引既过滤了user_id=100，又按created_at排序
-- 性能：优秀！

示例2：无法同时满足的情况

-- 假设索引 idx_created_amount (created_at, amount)

-- 查询：过滤条件不在索引最左
EXPLAIN SELECT * FROM orders 
WHERE amount > 1000 
ORDER BY created_at;
-- 结果：type=ALL, Extra=Using filesort
-- 说明：索引无法同时满足WHERE和ORDER BY
-- 解决方案：可能需要额外索引

4.索引排序的严格条件

只有当索引的顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能使用索引来对结果做排序。

条件1：顺序完全一致，方向相同

-- 索引：idx_a_b_c (a, b, c)

--  情况1：完全匹配
ORDER BY a, b, c
ORDER BY a DESC, b DESC, c DESC

--  情况2：顺序不匹配
ORDER BY b, a, c  -- 打乱顺序
ORDER BY a, b     -- 缺少c
ORDER BY a, b, c, d  -- 超出索引

--  情况3：方向不一致
ORDER BY a ASC, b DESC, c ASC  -- 混合方向

如果查询需要联接多张表，则只有当ORDER BY子句引用的字段全部在第一个表中时，才能使用索引做排序。

条件2：多表联接时的限制

-- 两张表关联
SELECT o.*, u.name 
FROM orders o
JOIN users u ON o.user_id = u.id

-- ✅ 可以使用索引排序：ORDER BY引用的列都在orders表
ORDER BY o.created_at

-- ❌ 不能使用索引排序：ORDER BY引用了第二张表的列
ORDER BY u.name

-- 注意：必须全部列都在第一张表才能用索引排序

5.ORDER BY与最左前缀原则

ORDER BY子句和查找型查询的限制是一样的：需要满足索引的最左前缀的要求，否则，MySQL需要执行排序操作，而无法利用索引排序。

原则：ORDER BY必须满足索引的最左前缀

-- 索引：idx_user_status_created (user_id, status, created_at)

-- ✅ 情况1：满足最左前缀
ORDER BY user_id
ORDER BY user_id, status
ORDER BY user_id, status, created_at

-- ❌ 情况2：不满足最左前缀（无法利用索引排序）
ORDER BY status           -- 缺少user_id
ORDER BY created_at       -- 缺少user_id和status
ORDER BY status, created_at  -- 缺少user_id

有一种特殊情况，如果前导列为常量的时候，ORDER BY子句中的列也可以不满足索引的最左前缀的要求。

如果在WHERE子句或者JOIN子句中将这些列指定为了常量，就可以“填补”索引字段的间隙了。

例外：前导列为常量时可以"跳跃"

-- 索引：idx_user_status_created (user_id, status, created_at)

-- ✅ 特殊情况：WHERE中指定了前导列为常量
SELECT * FROM orders 
WHERE user_id = 100 AND status = 1 
ORDER BY created_at;

-- 解析：
-- WHERE user_id = 100 AND status = 1  → 前两列被固定为常量
-- ORDER BY created_at  → 实际相当于在(user_id, status, created_at)上排序
-- 可以充分利用索引！

-- ✅ 另一个例子：部分前导列为范围条件
SELECT * FROM orders 
WHERE user_id = 100 AND status IN (1, 2) 
ORDER BY created_at;
-- 如果status IN (1, 2) 优化为多个等值条件，仍可能使用索引排序

6.实际示例解析

示例1：电商订单查询优化

-- 原始表结构
CREATE TABLE orders (
    id INT PRIMARY KEY,
    user_id INT,
    product_id INT,
    amount DECIMAL(10,2),
    status TINYINT,
    created_at DATETIME,
    INDEX idx_user_created (user_id, created_at)
);

-- 查询：用户订单列表，按时间倒序
EXPLAIN SELECT * FROM orders 
WHERE user_id = 100 
ORDER BY created_at DESC;

-- 结果：type=ref, Extra=Using where
-- 说明：完美使用索引，先通过user_id=100过滤，再按created_at排序

示例2：需要额外排序的情况

-- 查询：不同用户的最新订单
EXPLAIN SELECT user_id, MAX(created_at) 
FROM orders 
GROUP BY user_id 
ORDER BY MAX(created_at) DESC;

-- 结果：type=index, Extra=Using temporary; Using filesort
-- 说明：
-- 1. 扫描整个索引获取数据
-- 2. 对结果进行临时表分组
-- 3. 对分组结果进行文件排序
-- 无法直接利用索引排序

示例3：复杂查询优化

-- 索引设计：考虑查询模式
CREATE INDEX idx_status_created ON orders(status, created_at);

-- 查询：待处理订单，按时间升序处理
EXPLAIN SELECT * FROM orders 
WHERE status = 'pending' 
ORDER BY created_at ASC;

-- 查询：已处理订单，按时间倒序查看
EXPLAIN SELECT * FROM orders 
WHERE status = 'completed' 
ORDER BY created_at DESC;

-- 问题：DESC排序是否能用索引？
-- 答案：对于InnoDB，索引可以反向扫描，但性能可能略差

7.性能对比测试

-- 测试1：覆盖索引 vs 回表查询
-- 创建测试数据（100万行）
INSERT INTO orders (user_id, amount, created_at)
SELECT 
    FLOOR(RAND() * 1000), 
    RAND() * 1000,
    DATE_SUB(NOW(), INTERVAL FLOOR(RAND() * 365) DAY)
FROM numbers; -- 假设numbers是一个数字表

-- 查询A：仅索引列（覆盖索引）
SELECT created_at FROM orders ORDER BY created_at LIMIT 1000;
-- 时间：~50ms

-- 查询B：需要回表
SELECT * FROM orders ORDER BY created_at LIMIT 1000;
-- 时间：~500ms（10倍！）

-- 测试2：索引排序 vs 文件排序
-- 禁用索引强制文件排序
SELECT SQL_NO_CACHE * FROM orders 
ORDER BY created_at LIMIT 1000;
-- 时间：~800ms

-- 启用索引
SELECT SQL_NO_CACHE * FROM orders 
USE INDEX(idx_created) 
ORDER BY created_at LIMIT 1000;
-- 时间：~500ms

8.最佳实践建议

索引设计策略

-- 设计同时支持WHERE和ORDER BY的索引
CREATE INDEX idx_where_order ON table(where_column, order_column);

-- 示例：用户订单查询
CREATE INDEX idx_user_status_created ON orders(user_id, status, created_at);
-- 支持：WHERE user_id=? AND status=? ORDER BY created_at

查询优化技巧

-- 技巧1：使用覆盖索引避免回表
SELECT user_id, created_at FROM orders ORDER BY created_at;

-- 技巧2：分页查询优化（深分页问题）
-- 低效：SELECT * FROM orders ORDER BY created_at LIMIT 10000, 20;
-- 高效：先查主键，再回表
SELECT * FROM orders o
JOIN (
    SELECT id FROM orders 
    ORDER BY created_at 
    LIMIT 10000, 20
) tmp ON o.id = tmp.id;

监控与诊断

-- 查看排序状态
SHOW SESSION STATUS LIKE 'Sort%';
-- Sort_merge_passes: 归并排序次数
-- Sort_range: 范围排序次数
-- Sort_rows: 排序行数
-- Sort_scan: 全表扫描排序次数

-- 调整排序缓冲区
SET sort_buffer_size = 64*1024*1024;  -- 64MB

（八）压缩（前缀压缩）索引

MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。默认只压缩字符串，但通过参数设置也可以对整数做压缩。

MyISAM压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。

例如，索引块中的第一个值是“perform”，第二个值是“performance”，那么第二个值的前缀压缩后存储的是类似“7,ance”这样的形式。MyISAM对行指针也采用类似的前缀压缩方式。

压缩块使用更少的空间，代价是某些操作可能更慢。

因为每个值的压缩前缀都依赖前面的值，所以MyISAM查找时无法在索引块使用二分查找而只能从头开始扫描。

正序的扫描速度还不错，但是如果是倒序扫描——例如ORDER BY DESC——就不是很好了。所有在块中查找某一行的操作平均都需要扫描半个索引块。

测试表明，对于CPU密集型应用，因为扫描需要随机查找，压缩索引使得MyISAM在索引查找上要慢好几倍。

压缩索引的倒序扫描就更慢了。压缩索引需要在CPU内存资源与磁盘之间做权衡。

压缩索引可能只需要十分之一大小的磁盘空间，如果是I/O密集型应用，对某些查询带来的好处会比成本多很多。

可以在CREATE TABLE语句中指定PACK_KEYS参数来控制索引压缩的方式。

（九）冗余和重复索引

MySQL允许在相同列上创建多个索引，无论是有意的还是无意的。

1.重复索引

MySQL需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后也应该立即移除。

有时会在不经意间创建了重复索引，例如下面的代码：

CREATE TABLE test (
    ID INT NOT NULL PRIMARY KEY,
    A INT NOT NULL,
    B INT NOT NULL,
    UNIQUE(ID),
    INDEX(ID)
) ENGINE=InnoDB;

一个经验不足的用户可能是想创建一个主键，先加上唯一限制，然后再加上索引以供查询使用。事实上，MySQL的唯一限制和主键限制都是通过索引实现的，因此，上面的写法实际上在相同的列上创建了三个重复的索引。

通常并没有理由这样做，除非是在同一列上创建不同类型的索引来满足不同的查询需求。

2.冗余索引

冗余索引和重复索引有一些不同。如果创建了索引(A,B)，再创建索引(A)就是冗余索引，因为这只是前一个索引的前缀索引。

因此索引(A,B)也可以当作索引(A)来使用（这种冗余只是对B-Tree索引来说的）。

但是如果再创建索引(B,A)，则不是冗余索引，索引(B)也不是，因为B不是索引(A,B)的最左前缀列。

另外，其他不同类型的索引（例如哈希索引或者全文索引）也不会是B-Tree索引的冗余索引，而无论覆盖的索引列是什么。

冗余索引通常发生在为表添加新索引的时候。例如，有人可能会增加一个新的索引(A,B)而不是扩展已有的索引(A)。

还有一种情况是将一个索引扩展为(A,ID)，其中ID是主键，对于InnoDB来说主键列已经包含在二级索引中了，所以这也是冗余的。

大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。

但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。

例如，如果在整数列上有一个索引，现在需要额外增加一个很长的VARCHAR列来扩展该索引，那性能可能会急剧下降。

特别是有查询把这个索引当作覆盖索引，或者这是MyISAM表并且有很多范围查询（由于MyISAM的前缀压缩）的时候。

考虑一下前面“在InnoDB中按主键顺序插入行”一节提到的userinfo表。

CREATE TABLE userinfo (
    id int unsigned NOT NULL AUTO_INCREMENT,
    name varchar(64) NOT NULL DEFAULT '',
    email varchar(64) NOT NULL DEFAULT '',
    password varchar(64) NOT NULL DEFAULT '',
    dob date DEFAULT NULL,
    address varchar(255) NOT NULL DEFAULT '',
    city varchar(64) NOT NULL DEFAULT '',
    state_id tinyint unsigned NOT NULL DEFAULT '0',
    zip varchar(8) NOT NULL DEFAULT '',
    country_id smallint unsigned NOT NULL DEFAULT '0',
    account_type varchar(32) NOT NULL DEFAULT '',
    verified tinyint NOT NULL DEFAULT '0',
    allow_mail tinyint unsigned NOT NULL DEFAULT '0',
    parent_account int unsigned NOT NULL DEFAULT '0',
    closest_airport varchar(3) NOT NULL DEFAULT '',
    PRIMARY KEY (id),
    UNIQUE KEY email (email),
    KEY country_id (country_id),
    KEY state_id (state_id)
) ENGINE=InnoDB;

这个表有1000 000行，对每个state_id值大概有20 000条记录。在state_id列有一个索引对下面的查询有用，假设查询名为Q1：

SELECT count(*) FROM userinfo WHERE state_ id=5;

一个简单的测试表明该查询的执行速度大概是每秒115次(QPS)。还有一个相关查询需要检索几个列的值，而不是只统计行数，假设名为Q2：

SELECT state_id, city, address FRoM userinfo WHERE state_id=5;

对于这个查询，测试结果QPS小于10。提升该查询性能的最简单办法就是扩展索引为(state_id，city，address)，让索引能覆盖查询：

ALTER TABLE userinfo DROP KEY state_id,
ADD KEY state_id_2 (state_id, city, address);

索引扩展后，Q2运行得更快了，但是Q1却变慢了。如果我们想让两个查询都变得更快，就需要两个索引，但这样一来原来的单列索引就是冗余的了。
在这里插入图片描述

建两个索引的缺点是，会带来一定的维护成本。表7-3展示了向表中插入100万行数据所需要的时间。

在这里插入图片描述

可以看到，表中的索引越多，插入的速度越慢。一般来说，增加新索引会导致INSERT、UPDATE、DELETE等操作的速度变慢，特别是当新增索引后达到了内存瓶颈的时候。

3.找出冗余索引和重复索引

解决冗余索引和重复索引的方法很简单，删除这些索引就可以了，但首先要做的是找出这样的索引。

可以针对INFORMATION_SCHEMA表编写各种复杂的查询来识别这类索引，也有更简单的技术，比如可以使用Percona工具箱中的pt-duplicate-key-checker，该工具通过分析表结构来找出冗余和重复索引。

在删除或扩展索引的时候要非常小心。回忆一下，在前面的InnoDB的示例表中，因为二级索引的叶子节点包含了主键值，所以在列(A)上的索引就相当于在(A，ID)上的索引。

如果有像WHERE A=5 ORDER BY ID这样的查询，这个索引会很有用。

但如果将索引扩展为(A，B)，则实际上就变成了(A，B，ID)，那么上面查询的ORDER BY子句就无法使用该索引做排序，而只能用文件排序了。

所以，建议使用Percona工具箱中的pt-upgrade工具来仔细检查计划中的索引变更。

对于上述的两种情况，都可以考虑使用MySQL 8.0的不可见索引特性，而不是直接删除索引。

要使用这个特性，可以通过ALTER TABLE语句，改变索引的一个标志位，使得优化器在确定执行计划时，忽略该索引。

如果你发现计划删除的索引依旧有非常重要的作用，可以直接把索引改成可见，而不需要重新构建该索引。

（十）未使用的索引

除了冗余索引和重复索引，可能还会有一些服务器永远不用的索引。这样的索引完全是累赘，建议删除。
找到未使用索引的最好办法就是使用系统数据库performance_schema和sys。

在sys数据库中，在table_io_waits_summary_by_index_usage视图中可以非常简单地知道哪些索引从来没有被使用过：

select * from sys.schema_unused_indexes;

在这里插入图片描述

(十一）索引和锁

索引可以让查询锁定更少的行。如果你的查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看这对性能都有好处。首先，虽然InnoDB的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外开销；其次，锁定超过需要的行会增加锁争用并减少并发性。

InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。

但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么在InnoDB检索到数据并返回给服务器层以后， MySQL服务器才能应用WHERE子句[插图]。这时已经无法避免锁定行了：InnoDB已经锁住了这些行，到适当的时候才释放。

在MySQL 5.1和更新的版本中，InnoDB可以在服务器端过滤掉行后就释放锁，但是在早期的MySQL版本中，InnoDB只有在事务提交后才能释放锁。

三、维护索引和表

即使用正确的类型创建了表并加上了合适的索引，工作也没有结束：还需要维护表和索引来确保它们都正常工作。维护表有三个主要的目的：找到并修复损坏的表，维护准确的索引统计信息，减少碎片。

（一）找到并修复损坏的表

1.表损坏的常见原因

表损坏(corruption)是很糟糕的事情。对于MyISAM存储引擎，表损坏通常是系统崩溃导致的。其他的引擎也会由于硬件问题、MySQL本身的缺陷或者操作系统的问题导致索引损坏。

原因	说明	示例
系统崩溃	尤其影响 MyISAM 引擎，未正常关闭 MySQL 或操作系统崩溃可能导致表结构损坏。	服务器突然断电，MyISAM 表可能出现索引错乱。
硬件故障	磁盘坏道、内存错误等硬件问题可能影响任何存储引擎。	磁盘写入过程中发生错误，导致 InnoDB 页数据损坏。
MySQL/操作系统缺陷	软件层面的 bug 可能在特定操作下引发数据错误。	某版本 MySQL 在特定查询条件下出现索引损坏（罕见但可能）。
外部操作数据文件	在 MySQL 运行期间直接操作数据文件（如复制、rsync）。	使用 `rsync` 直接复制正在使用的 InnoDB 文件，可能导致文件不一致。

2.表损坏的常见表现

损坏的索引会导致查询返回错误的结果或者莫须有的主键冲突等问题，严重时甚至还会导致数据库的崩溃。

查询返回错误或异常结果（例如本应返回 10 行，却只返回 5 行）。
出现不应发生的主键冲突错误。
数据库服务崩溃或无法正常启动。
执行 SELECT 时提示“表不存在”或“文件损坏”类错误。

3.如何检测表损坏

如果你遇到了古怪的问题—例如一些不应该发生的错误—可以尝试运行CHECK TABLE来检查是否发生了表损坏（注意有些存储引擎不支持该命令；而有些引擎则支持以不同的选项来控制完全检查表的方式）。CHECK TABLE通常能够找出大多数的表和索引的错误。

使用 CHECK TABLE 命令
该命令可检查表结构和索引是否正常（注意：并非所有存储引擎都支持，且支持的程度可能不同）。

-- 检查表状态
CHECK TABLE your_table_name;

输出示例：

+---------------------+-------+----------+----------+
| Table               | Op    | Msg_type | Msg_text |
+---------------------+-------+----------+----------+
| test.your_table_name| check | status   | OK       |
+---------------------+-------+----------+----------+

如果提示 error 或 warning，则很可能已损坏。

4.如何修复表损坏

可以使用REPAIR TABLE命令来修复损坏的表，但同样不是所有的存储引擎都支持该命令。如果存储引擎不支持，也可通过一个不做任何操作(no-op)的ALTER操作来重建表，例如修改表的存储引擎为当前的引擎。

ALTER TABLE <table> ENGINE=INNODB;

使用 REPAIR TABLE（如果引擎支持）

REPAIR TABLE your_table_name;

使用 ALTER TABLE 重建表（通用方法）
适用于 InnoDB 等引擎，通过原地重建来修复索引和数据错误：

ALTER TABLE your_table_name ENGINE=INNODB;

这相当于在不改变引擎的情况下重建整个表，可修复多数逻辑损坏。

使用离线工具（如 MyISAM）
此外，也可以使用一些存储引擎相关的离线工具，例如myisamchk；或者将数据导出一份，然后再重新导入。

对于 MyISAM 表，可使用 myisamchk 工具（需停止 MySQL 或锁定表）：

myisamchk --recover /path/to/table.MYI

从备份恢复

不过，如果损坏的是系统区域，或者是表的“行数据”区域，而不是索引，那么上面的办法就没有用了。在这种情况下，可以从备份中恢复表，或者尝试从损坏的数据文件中尽可能地恢复数据。

如果损坏发生在系统区域或行数据区域（而非仅索引），则上述方法可能无效，此时应从备份恢复数据。

5.特别注意：InnoDB 表损坏

InnoDB 设计上具有较强的抗损坏能力，一般不会轻易发生损坏。若出现 InnoDB 表损坏，通常意味着：

硬件故障（如内存错误、磁盘坏道）。
管理员误操作（例如在 MySQL 运行时直接复制数据文件）。
极罕见的 MySQL/InnoDB bug。

重要提示：

不要在运行中用 rsync 等工具直接复制 InnoDB 文件（应使用官方备份工具如 mysqldump、Percona XtraBackup 等）。
如果查询导致 InnoDB 数据损坏，那几乎可以肯定是遇到了 bug，应及时上报官方。

6.建议的操作流程（遇到疑似损坏时）

立即备份当前状态（如果有物理备份工具，先备份文件）。
运行 CHECK TABLE 确认损坏情况。
尝试 REPAIR TABLE 或 ALTER TABLE ... ENGINE=... 进行修复。
若修复失败，从最近备份恢复，并尝试从损坏文件中导出尚可读取的数据（如使用 SELECT ... INTO OUTFILE 分批导出）。
记录日志并分析原因，避免同样问题再次发生。

(二）更新索引统计信息

MySQL的查询优化器会通过两个API来了解存储引擎的索引值的分布信息，以决定如何使用索引。第一个API是records_in_range()，通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录。

对于某些存储引擎，该接口返回精确值，例如MyISAM；但对于另一些存储引擎则是一个估算值，例如InnoDB。第二个API是info()，该接口返回各种类型的数据，包括索引的基数（每个键值有多少条记录）。

如果存储引擎向优化器提供的扫描行数信息是不准确的数据，或者执行计划本身太复杂以致无法准确地获取各个阶段匹配的行数，那么优化器会使用索引统计信息来估算扫描行数。

MySQL优化器使用的是基于成本的模型，而衡量成本的主要指标就是一个查询需要扫描多少行。

如果表没有统计信息，或者统计信息不准确，优化器就很有可能做出错误的决定。可以通过运行ANALYZE TABLE来重新生成统计信息解决这个问题。

每种存储引擎实现索引统计信息的方式不同，所以需要进行ANALYZE TABLE的频率也因不同的引擎而不同，每次运行的成本也不同：

可以使用 SHOW INDEX FROM命令来查看索引的基数(Cardinality)。例如：

mysql> SHOW INDEX FROM sakila.actor\G
*************************** 1. row ***************************
Table: actor
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: actor_id
Collation: A
Cardinality: 200
Sub_part: NULL
Packed: NULL
Null: 
Index_type: BTREE
Comment: 
*************************** 2. row ***************************
Table: actor
Non_unique: 1
Key_name: idx_actor_last_name
Seq_in_index: 1
Column_name: last_name
Collation: A
Cardinality: 200
Sub_part: NULL
Packed: NULL
Null: 
Index_type: BTREE
Comment:

这个命令输出了很多关于索引的信息，在MySQL手册中对上面每个字段的含义都有详细的解释。这里需要特别提及的是索引列的基数，其显示了存储引擎估算索引列有多少个不同的取值。还可以通过INFORMATION_SCHEMA.STATISTICS表很方便地查询到这些信息。

例如，基于INFORMATION_SCHEMA的表，可以编写一个查询给出当前选择性比较低的索引。需要注意的是，如果服务器上的库表非常多，则从这里获取元数据的速度可能会非常慢，而且会给MySQL带来额外的压力。InnoDB的统计信息值得深入研究。InnoDB引擎通过抽样的方式来计算统计信息，首先随机地读取少量的索引页面，然后以此为样本计算索引的统计信息。

在旧InnoDB版本中，样本页面数是8，新版本的InnoDB可以通过参数innodb_stats_sample_pages来设置样本页的数量。设置更大的值，理论上来说可以帮助生成更准确的索引信息，特别是对于某些超大的数据表来说，但具体设置多大合适依赖于具体的环境。

(三）减少索引和数据的碎片

数据库在长期运行中，经过大量的增、删、改操作后，其存储结构（包括索引和数据）可能变得不再紧凑、有序，这就是“碎片化”。可以把它想象成一个不断使用和整理的文件柜：

理想状态：文件（数据行）整齐地、按顺序排列在抽屉（数据页）里，抽屉也紧密地堆放在柜子中。
碎片化状态：文件被撕成几份塞在不同抽屉（行碎片），抽屉顺序混乱（行间碎片），或者抽屉里大部分是空的（剩余空间碎片）。当你需要找一系列文件时，就会花费更多时间翻找。

对于数据库，碎片化的直接后果就是需要读取更多的磁盘页，而磁盘I/O通常是数据库操作中最慢的环节，因此会显著降低查询效率。

1.索引碎片化

B-tree 索引碎片化指的是索引的叶子页（存储实际键值和指针的页）在物理磁盘上变得不再连续和紧凑。

产生原因：频繁的插入、删除和更新（尤其是非顺序的随机插入）。
具体影响：
1. 范围查询变慢：查询 WHERE id BETWEEN 100 AND 200，如果这些ID对应的叶子页是连续的，磁盘可以快速顺序读取。如果它们分散在磁盘各处，就需要多次随机寻址，速度大减。
2. 覆盖扫描变慢：如果查询只需要从索引中获取数据（覆盖索引），数据库本可以高效地连续读取索引页。碎片化会使这种连续读取变得困难。
举例说明：
想象一个按姓氏排序的电话簿（这就是一个B-tree索引）。如果每次有新用户加入，就把他的名字随手写在当时有空白的地方，那么最后要找“张”姓的所有人，你就不能连续翻页，而需要在整个电话簿里前后跳跃查找。

2.数据碎片化（更复杂，分三种类型）

数据表的碎片化主要有三种形式，它们通常会同时存在。

行碎片

定义：一行数据被分成多个片段，存储在不同的物理位置（不同数据页）。
影响：即使查询只访问一行（如通过主键查询），数据库也可能需要从磁盘的多个位置读取数据，然后拼凑成完整的一行，这增加了I/O开销。
举例说明：
一篇完整的文章（一行数据）被拆成了三段，分别存放在第1、第5、第10号文件夹（数据页）里。你想看这篇文章，就必须打开三个文件夹才能读全。

行间碎片

定义：逻辑上连续的数据行（例如按主键递增），在物理磁盘上并不连续存储。
影响：对全表扫描或范围扫描操作伤害最大。这类操作原本期望能像读流水账一样顺序、快速地读取磁盘，但行间碎片迫使磁盘磁头来回跳动，读取效率急剧下降。
举例说明：
一个停车场本该按车牌号顺序停车。但现在1号车停在A区，2号车停在D区，3号车停在B区。管理员要依次检查1-100号车，就不得不在整个停车场里来回奔跑，无法按区域高效检查。

剩余空间碎片

定义：数据页中存在大量未被利用的空白空间。这些空间可能是由于删除行或更新行（新行更大，导致原位置放不下而迁移）产生的。
影响：数据库读取一个数据页到内存时，页内有效数据很少，大部分是无效空间。这浪费了宝贵的I/O带宽和内存缓存空间，变相降低了缓存效率。
举例说明：
一个教室有100个座位（一个数据页），但只有30个学生（有效数据）。每次老师（数据库）检查教室情况时，都需要走进这个能容纳100人的大教室，但实际只关心那30个学生，70%的查看动作是浪费的。

3.如何解决碎片化问题？

解决方案的核心是 “重建表” ，使数据和索引恢复紧凑、有序的存储状态。

方法	命令 / 操作	说明与适用场景
标准整理	`OPTIMIZE TABLE table_name;`	最推荐。适用于大多数存储引擎（如InnoDB, MyISAM）。它会重建表并优化索引，同时释放未使用的空间。对于InnoDB，这相当于执行一个`ALTER TABLE`重建过程。
通用重建法	`ALTER TABLE table_name ENGINE=current_engine;`	“不做任何操作的ALTER”。通过指定当前正在使用的存储引擎来重建表。这是处理不支持`OPTIMIZE TABLE`的引擎，或`OPTIMIZE`命令不理想的通用方法。
导出/导入	使用 `mysqldump` 导出数据，然后重新导入。	最彻底的整理方式，但操作复杂且需要停机时间。通常在迁移数据或进行重大维护时使用。

重要操作示例：
假设你有一个使用InnoDB引擎的表 user_logs，发现其查询变慢，怀疑是碎片化导致。

-- 方法1：使用OPTIMIZE TABLE (InnoDB推荐)
OPTIMIZE TABLE user_logs;

-- 方法2：使用通用的ALTER重建法 (对不支持OPTIMIZE的引擎或作为替代方案)
ALTER TABLE user_logs ENGINE=INNODB;

执行建议：

选择低峰期：重建表会锁定表或产生大量I/O，影响线上服务。
确保有足够空间：重建过程需要额外的临时磁盘空间。
定期维护：对于写入频繁的表，可以定期（例如每周或每月）在维护窗口执行整理操作。

四、索引失效的常见情形

在MySQL中避免索引失效应考虑如下因素：

1. 未遵循最左前缀原则

最左前缀匹配原则。最左前缀原则指，如果一个索引建立在多列中，则where子句中的查询条件从索引的最左列开始匹配并且不跳过索引中的列。

-- 复合索引: (name, age, city)
--  失效：跳过第一列
SELECT * FROM users WHERE age = 25 AND city = '北京';
--  有效：从最左列开始
SELECT * FROM users WHERE name = '张三';
SELECT * FROM users WHERE name = '张三' AND age = 25;

2. 在索引列上使用函数或计算

不要在索引列中进行任何操作，如表达式运算、使用函数、自动或手动类型转换等操作，否则会导致索引失效从而引发全表扫描。

--  失效：对索引列使用函数
SELECT * FROM users WHERE YEAR(create_time) = 2023;
SELECT * FROM users WHERE UPPER(name) = 'ZHANGSAN';

--  失效：对索引列进行计算
SELECT * FROM products WHERE price * 1.1 > 100;

--  失效：索引列在表达式右边
SELECT * FROM orders WHERE amount + 100 > 2000;


--  有效：调整表达式
SELECT * FROM orders WHERE amount > 1900;

--  有效：避免函数
SELECT * FROM users 
WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01';

3. 类型转换导致失效（隐式编码转换）

不要在索引列中进行任何操作，自动或手动类型转换等操作会导致索引失效从而引发全表扫描。

字符串常量要加单引号，否则会导致索引失效。

-- 假设phone是varchar类型且有索引
--  失效：数字与字符串比较
SELECT * FROM users WHERE phone = 13800138000;

--  有效：保持类型一致
SELECT * FROM users WHERE phone = '13800138000';

4. LIKE以通配符开头

like不要以通配符开头。如果like以通配符开头（如like ‘%abc’）则会导致索引失效。

--  失效：前导通配符
SELECT * FROM users WHERE name LIKE '%张三%';
SELECT * FROM users WHERE name LIKE '%张三';

--  有效：后缀匹配
SELECT * FROM users WHERE name LIKE '张三%';

5. OR条件处理不当

少用or连接条件，可以用UNION代替。

--  失效：OR条件中有一个无索引
-- name有索引，email无索引
SELECT * FROM users WHERE name = '张三' OR email = 'a@b.com';


--  有效：使用UNION替代
SELECT * FROM users WHERE name = '张三'
UNION
SELECT * FROM users WHERE email = 'a@b.com';

-- 失效：OR连接的两个条件都涉及索引
-- MySQL可能选择全表扫描
SELECT * FROM users WHERE name = '张三' OR age = 25;

-- 优化：使用UNION
SELECT * FROM users WHERE name = '张三'
UNION ALL
SELECT * FROM users WHERE age = 25;

6. 范围查询后的列失效

索引范围条件右边的索引列会失效。

-- 索引: (a, b, c)
--  失效：范围查询后的列无法使用索引
SELECT * FROM table WHERE a = 1 AND b > 10 AND c = 20;

--  有效：范围列放最后
-- 设计索引时应考虑查询模式：(a, c, b)
SELECT * FROM table WHERE a = 1 AND c = 20 AND b > 10;

7. 使用不等于(!=, <>)

尽量不使用不等于（!=、＜＞）操作。

MySQL在使用不等于（!=、＜＞）的时候将无法使用索引，这样会引发全表扫描（除覆盖索引外）。

-- ❌ 失效：不等于通常走全表扫描
SELECT * FROM users WHERE status != 1;
SELECT * FROM users WHERE age <> 30;

-- ✅ 优化：考虑范围查询
SELECT * FROM users WHERE status < 1 OR status > 1;

8. IN和EXISTS的选择

如果子查询返回结果集很大，外层查询可能放弃使用索引

执行计划可能显示：type=ALL，使用全表扫描

--  可能失效：IN列表过长
SELECT * FROM users WHERE id IN (1,2,3,4,5,6,7,8,9,10,...上千个值);

-- 如果子查询返回结果集很大，外层查询可能放弃使用索引
-- 执行计划可能显示：type=ALL，使用全表扫描
SELECT * FROM users 
WHERE id IN (
    SELECT user_id FROM orders WHERE amount > 1000
);

--  通常失效：NOT IN效率低下
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM blacklist);

-- 使用JOIN或分批查询，使用LEFT JOIN
SELECT u.* FROM users u
LEFT JOIN blacklist b ON u.id = b.user_id
WHERE b.user_id IS NULL;

--  优化：使用EXISTS（有时更高效）
SELECT * FROM users u
WHERE EXISTS (
    SELECT 1 FROM orders o 
    WHERE o.user_id = u.id AND o.amount > 1000
);

9. NULL值处理

尽量不要进行null值判断。is null和is not null无法使用索引。

-- ❌ 可能失效：IS NULL可能不走索引
SELECT * FROM users WHERE phone IS NULL;

-- ✅ 设计建议：考虑默认值替代NULL

10. ORDER BY导致的失效

-- 索引: (status, create_time)
-- ❌ 失效：排序方向不一致
SELECT * FROM orders 
WHERE status = 1 
ORDER BY create_time ASC, id DESC;

-- ✅ 有效：保持排序方向一致
SELECT * FROM orders 
WHERE status = 1 
ORDER BY create_time ASC, id ASC;

11. 数据分布不均匀

-- 当某个值占比过高时（>20%），优化器可能选择全表扫描
-- 例如：status=1占90%数据
SELECT * FROM orders WHERE status = 1;

诊断工具：

-- 使用EXPLAIN分析查询
EXPLAIN SELECT * FROM users WHERE name = '张三';

-- 关键字段：
-- type: ALL表示全表扫描
-- key: NULL表示未使用索引
-- Extra: Using filesort, Using temporary表示性能问题

-- 查看索引使用情况
SHOW INDEX FROM table_name;

五、索引的性能测试

1.数据准备

（1）数据规模

首先确定测试的数据量级，这里选择常见的100万行的级别

-- 建议的测试数据量级
小规模：10万 - 50万行  （快速测试）
中等规模：100万 - 500万行 （常规测试）
大规模：1000万行以上   （压力测试）

（2）创建基准测试表

准备两张用于对照的基础表

-- 1. 无索引的表（基准对照）
CREATE TABLE users_no_index (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(100),
    age INT,
    city VARCHAR(50),
    created_at DATETIME,
    salary DECIMAL(10, 2),
    is_active BOOLEAN
);

-- 2. 带索引的表
CREATE TABLE users_with_index (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50),
    email VARCHAR(100),
    age INT,
    city VARCHAR(50),
    created_at DATETIME,
    salary DECIMAL(10, 2),
    is_active BOOLEAN,
    INDEX idx_username (username),
    INDEX idx_email (email),
    INDEX idx_age_city (age, city),
    INDEX idx_created (created_at)
);

（3）生成真实的数据分布


-- 删除原有存储过程（如果存在）
DROP PROCEDURE IF EXISTS generate_test_data_optimized;
DELIMITER $$
CREATE PROCEDURE generate_test_data_optimized(IN num_rows INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE batch_size INT DEFAULT 1000;
    DECLARE current_batch INT DEFAULT 0;
    DECLARE batch_count INT DEFAULT 0;
    DECLARE sql_stmt LONGTEXT DEFAULT '';
    DECLARE start_time BIGINT DEFAULT UNIX_TIMESTAMP();
    
    -- 关闭自动提交，使用事务
    SET autocommit = 0;
    SET unique_checks = 0;
    SET foreign_key_checks = 0;
    
    -- 计算批次
    SET batch_count = CEIL(num_rows / batch_size);
    SET i = 0;
    
    -- 批次循环
    WHILE current_batch < batch_count DO
        -- 重置SQL语句
        SET sql_stmt = CONCAT('INSERT INTO users_no_index VALUES ');
        
        -- 构建一批数据
        SET @batch_values = '';
        WHILE i < (current_batch + 1) * batch_size AND i < num_rows DO
            -- 构建单行数据
            SET @row_value = CONCAT(
                IF(@batch_values = '', '', ','),
                '(',
                'NULL, ',
                CHAR(39), 'user_', FLOOR(RAND() * 100000), CHAR(39), ', ',
                CHAR(39), 'email_', FLOOR(RAND() * 100000), '@test.com', CHAR(39), ', ',
                FLOOR(18 + (RAND() * 62)), ', ',
                CHAR(39), ELT(FLOOR(1 + RAND() * 10), '北京','上海','广州','深圳','杭州','成都','武汉','南京','西安','重庆'), CHAR(39), ', ',
                'DATE_SUB(NOW(), INTERVAL ', FLOOR(RAND() * 365*5), ' DAY), ',
                ROUND(3000 + (RAND() * 15000), 2), ', ',
                IF(RAND() > 0.3, 1, 0),
                ')'
            );
            
            -- 添加到批次值
            SET @batch_values = CONCAT(@batch_values, @row_value);
            SET i = i + 1;
        END WHILE;
        
        -- 合并SQL语句
        SET sql_stmt = CONCAT(sql_stmt, @batch_values);
        
        -- 执行插入
        SET @dynamic_sql = sql_stmt;
        PREPARE stmt FROM @dynamic_sql;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        
        -- 每10个批次提交一次
        IF current_batch % 10 = 0 THEN
            COMMIT;
        END IF;
        
        SET current_batch = current_batch + 1;
    END WHILE;
    
    -- 最终提交
    COMMIT;
    
    -- 恢复设置
    SET autocommit = 1;
    SET unique_checks = 1;
    SET foreign_key_checks = 1;
    
END$$
DELIMITER ;

-- 执行数据生成
CALL generate_test_data_optimized(1000000);

（4）复制数据到含索引的表中

-- 清空有索引表（如果已有数据）
TRUNCATE TABLE users_with_index;

--  禁用非唯一索引（对InnoDB有效）
ALTER TABLE users_with_index DISABLE KEYS;

-- 插入数据,从无索引表复制数据到有索引表
INSERT INTO users_with_index SELECT * FROM users_no_index;

-- 重新启用索引
ALTER TABLE users_with_index ENABLE KEYS;

2.环境标准化

执行测试前的准备，先预热数据库，关闭查询缓存，重启服务避免干扰。

# 1. 预热数据库
FLUSH TABLES;
FLUSH STATUS;
RESET QUERY CACHE;

# 2. 关闭查询缓存（避免干扰）
SET GLOBAL query_cache_size = 0;
SET GLOBAL query_cache_type = OFF;

# 3. 重启MySQL服务（可选）
sudo systemctl restart mysql

3.性能测试

（1）等值查询

-- 等值查询
WHERE username = 'specific_user'
-- 填入某个值
select * from users_no_index WHERE username ='user_69140';
select * from users_with_index WHERE username ='user_69140';
-- 结果：
-- users_no_index  耗时 1.041s
-- users_with_index 耗时 0.102s

对于等值查询，索引能带来10倍以上的性能提升。这是因为B+树索引可以快速定位到特定值。

（2）范围查询

-- 2. 范围查询  
WHERE age BETWEEN 20 AND 30
-- 执行查询
select * from users_no_index WHERE age BETWEEN 20 AND 30;
select * from users_with_index WHERE age BETWEEN 20 AND 30;
-- 结果：
-- users_no_index  耗时 1.015s
-- users_with_index 耗时 1.377s

问题：索引表反而慢了35%。可能原因：

索引是 idx_age_city (age, city)，对 BETWEEN 范围查询效果不佳
返回数据量大（年龄20-30岁可能占10-20%的数据），导致回表代价高
MySQL优化器可能选择全表扫描

（3）前缀匹配

-- 3. 前缀匹配
WHERE email LIKE 'prefix%'
-- 执行查询
select * from users_no_index WHERE email LIKE 'email_80789%';
select * from users_with_index WHERE email LIKE 'email_80789%';
-- 结果：
-- users_no_index  耗时 1.070s
-- users_with_index 耗时 1.410s

问题：索引表慢了32%。虽然email上有单列索引，但：

LIKE ‘prefix%’ 使用索引时仍需要扫描索引树的一部分
如果匹配的结果集很大，回表操作会成为瓶颈

（4）排序查询

-- 排序查询
ORDER BY created_at DESC LIMIT 100
-- 执行查询
select * from users_no_index ORDER BY created_at DESC LIMIT 100;
select * from users_with_index ORDER BY created_at DESC LIMIT 100;
-- 结果：
-- users_no_index  耗时 1.017s
-- users_with_index 耗时 0.295s

结论：索引带来3.5倍性能提升。这是因为：

索引 idx_created (created_at) 本身就是有序的
避免了对100万行数据的排序操作

（5）聚合查询

-- 聚合查询
GROUP BY city, COUNT(*) 
-- 执行查询
select city,count(*) as cnt from users_no_index GROUP BY city;
select city,count(*) as cnt from users_with_index  GROUP BY city;
-- 结果：
-- users_no_index  耗时 0.573s
-- users_with_index 耗时 3.974s

问题：索引表慢了7倍！严重问题：

没有针对 city 的单列索引，只有 idx_age_city (age, city)
MySQL可能选择了错误的索引，或者扫描索引树比扫描数据表更慢

4.结论

(1) 索引不是万能的

有效场景：等值查询、排序、连接

无效/负优化场景：返回大量数据、低选择性字段、范围查询返回大量结果

(2) 数据量是关键因素

你的测试显示，当查询返回大量行时，索引反而成为负担：

全表扫描：顺序I/O，效率高

索引+回表：随机I/O，效率低

(3) 索引设计需要针对性

你的索引设计可能存在问题

-- 当前索引
INDEX idx_age_city (age, city)  -- 对 GROUP BY city 帮助有限
INDEX idx_created (created_at)  -- 对排序有效
INDEX idx_username (username)   -- 对等值查询有效
INDEX idx_email (email)         -- 对前缀匹配帮助有限

天工造物局

北京人形旗下天工造物具身智能开源社区，聚焦具身天工与慧思开物两大平台

更多推荐

deck.gl TripsLayer 出租车轨迹动画示例实战：从示例运行到着色器原理

TripsLayer 是 deck.gl 中用于渲染**随时间流动的轨迹动画**（如出租车行程、物流路径、迁徙路线）的核心图层。本文以仓库中的最小独立示例 [examples/website/trips](https://link.gitcode.com/i/1442af8f442f4cff39ff546e050fceb5) 为骨架，带你完成从安装依赖、启动应用、理解曼哈顿出租车数据格式，到深入

天工造物局

Foundry Anvil 中 eth_simulateV1 的 Blob 交易与 Blob Gas 字段规范化：原理、实现与测试验证

本篇技术指南以 [anvil-simulate-blob-transactions.md](https://link.gitcode.com/i/3d2ebe227262de169335eb290af80da8) 这条 changelog 条目为起点，深入剖析 Foundry Anvil 对 `eth_simulateV1` RPC 返回结果中 blob（EIP-4844）交易与 blob gas

天工造物局

LMCache CLI 实战：用 `lmcache kvcache clear` 管理运行中服务的 KV Cache 状态

本文是一份针对 LMCache 官方 CLI 命令 `lmcache kvcache` 的完整技术指南。该命令用于在**运行中的 LMCache 服务（MP HTTP server）**上管理 KV cache 状态，当前提供 `clear` 子命令，用于清空 L1（CPU 内存）层中缓存的全部 KV 数据。读完本文，你将掌握该命令的完整参数体系、退出码语义、终端/JSON/静默三种输出模式，并能

天工造物局

所有评论(0)

查看更多评论

晓风残月淡

@qq_40610003

已为社区贡献10条内容

高性能MYSQL（二）索引基础、高性能策略、失效情形、索引性能测试

晓风残月淡

一、索引基础

（一）索引的类型

1. B-索引

（1）索引加快数据访问的理由

（2）B-tree适合按范围查询

（3）可以使用B-Tree索引的查询类型

（4）关于排序

（5）B-Tree索引的限制

2.哈希索引

（2）哈希索引的限制

（3）自适应哈希索引

（4）创建自定义哈希索引的案例

（5）哈希函数的选择

（6）处理哈希冲突

3.空间数据索引(R-Tree)

4.全文索引

（二）索引的优点

1.索引的优点

2.索引的局限

二、高性能的索引策略

（一）独立的列

（二）前缀索引和索引选择性

1.索引的选择性的定义

2.前缀索引的选择性

3.创建前缀索引

(三）多列索引

1.为每个列建立独立索引的问题

2.索引合并的策略

(四）选择合适的索引列顺序

1.如何选择索引的列顺序

2.性能与查询条件

3.特殊用户和组

（五）聚簇索引

1.聚簇索引中的记录存放

2.聚集数据的优缺点

(1)聚集数据的优点

(2)聚簇索引的缺点

(3)二级索引

3.InnoDB的数据分布

（六）覆盖索引

（七）使用索引扫描来做排序

1. 生成有序结果的两种方式

(1) 排序操作（Using filesort）

(2)索引顺序扫描（Using index scan）

2.索引排序的性能陷阱

3.理想情况：索引同时满足查找和排序

示例1：完美匹配的索引

示例2：无法同时满足的情况

4.索引排序的严格条件

条件1：顺序完全一致，方向相同

条件2：多表联接时的限制

5.ORDER BY与最左前缀原则

原则：ORDER BY必须满足索引的最左前缀

例外：前导列为常量时可以"跳跃"

6.实际示例解析

7.性能对比测试

8.最佳实践建议

（八）压缩（前缀压缩）索引

（九）冗余和重复索引

1.重复索引

2.冗余索引

3.找出冗余索引和重复索引

（十）未使用的索引

(十一）索引和锁

三、维护索引和表

（一）找到并修复损坏的表

1.表损坏的常见原因

2.表损坏的常见表现

3.如何检测表损坏

4.如何修复表损坏

5.特别注意：InnoDB 表损坏

6.建议的操作流程（遇到疑似损坏时）

(二）更新索引统计信息

(三）减少索引和数据的碎片

1.索引碎片化

2.数据碎片化（更复杂，分三种类型）

3.如何解决碎片化问题？

四、索引失效的常见情形

（2）`B-tree`适合按范围查询