MySQL怎样进行索引优化 MySQL索引优化的核心方法与案例-mysql教程-PHP中文网

首先通过慢查询日志和explain分析定位性能瓶颈，重点关注type、rows和extra字段，若出现type为all或extra含using filesort则需优化；2. 根据查询模式选择合适索引类型，优先使用b-tree索引，高选择性列前置，遵循最左前缀原则设计复合索引；3. 避免在索引列上使用函数或类型转换以防索引失效；4. 利用覆盖索引减少回表，提升查询效率；5. 定期使用analyze table更新统计信息，结合sys.schema_unused_indexes移除冗余索引，并通过optimize table或在线工具整理碎片；6. 持续监控慢查询日志、performance schema及系统状态变量，动态调整索引策略以适应数据增长和业务变化，确保索引长期高效有效。

MySQL怎样进行索引优化 MySQL索引优化的核心方法与案例

MySQL索引优化，说白了，就是为了让你的数据库查询跑得更快，少花冤枉钱在不必要的IO和CPU上。核心在于巧妙地引导MySQL，让它在茫茫数据中，能像导航一样精准定位到你需要的信息，而不是大海捞针。这通常涉及到选择合适的列来建立索引，理解不同索引类型的适用场景，以及更重要的，如何通过工具去分析和验证你的优化效果。

解决方案

MySQL的索引优化，本质上是一门艺术，也是一门科学。它要求你对数据访问模式有深入的理解，并能熟练运用MySQL提供的各种工具。以下是我个人总结的一些核心方法和策略：

理解查询模式： 在优化之前，你得知道哪些查询是瓶颈。慢查询日志（Slow Query Log）是你的第一手资料，它能告诉你哪些SQL语句执行时间过长，是优化的重点对象。我通常会结合
```
pt-query-digest
```
登录后复制
这类工具来分析日志，找出那些“罪魁祸首”。
选择合适的索引类型：
- B-Tree索引： 这是MySQL最常用的索引类型，适用于全值匹配、范围查询、最左前缀匹配等。几乎所有你能在
```
WHERE
```
  登录后复制
  、
```
JOIN
```
  登录后复制
  、`
```
ORDER BY
```
  登录后复制
  子句中看到的列，都可能用到B-Tree索引。
- Hash索引： 仅用于精确匹配，查询速度极快，但不支持范围查询和排序。InnoDB存储引擎只支持自适应哈希索引，不能手动创建。而Memory存储引擎支持手动创建哈希索引。如果你有大量等值查询，且数据更新不频繁，可以考虑Memory表上的Hash索引。但实际生产中，B-Tree索引覆盖了绝大多数场景。
索引列的选择：
- 高选择性（Cardinality）的列： 索引的列值越不重复，选择性就越高，索引效果越好。比如，用户ID、订单号通常是高选择性，而性别、状态等低选择性列单独做索引效果不佳。
- WHERE、JOIN、ORDER BY、GROUP BY子句中频繁出现的列： 这些是查询优化的核心。如果一个列经常出现在这些子句中，那么它就是索引的候选者。
- 避免在索引列上进行函数操作或类型转换： 这样做会导致索引失效，MySQL将不得不进行全表扫描。比如
```
WHERE DATE(create_time) = '2023-01-01'
```
  登录后复制
  ，这会让
```
create_time
```
  登录后复制
  上的索引失效。
复合索引（联合索引）的艺术：
- 最左前缀原则： 这是复合索引的灵魂。一个包含
```
(col1, col2, col3)
```
  登录后复制
  的复合索引，可以支持
```
col1
```
  登录后复制
  、
```
(col1, col2)
```
  登录后复制
  、
```
(col1, col2, col3)
```
  登录后复制
  的查询，但不能直接支持
```
col2
```
  登录后复制
  或
```
(col2, col3)
```
  登录后复制
  的查询。
- 列的顺序： 通常，将选择性高的列放在前面，或者将等值查询的列放在前面，范围查询的列放在后面。这个顺序对查询性能影响很大。
- 覆盖索引（Covering Index）： 如果一个查询所需的所有列都包含在索引中，那么MySQL可以直接从索引中获取数据，而无需回表查询，这能极大提升性能。
```
EXPLAIN
```
  登录后复制
  结果中
```
Extra
```
  登录后复制
  列显示
```
Using index
```
  登录后复制
  就是覆盖索引的标志。
索引维护：
- 定期分析表（
  ANALYZE TABLE
  登录后复制
  ）：更新索引统计信息，帮助优化器做出更准确的查询计划。
- 移除冗余和未使用的索引： 过多的索引会增加写操作的开销，并占用存储空间。MySQL 5.7+可以通过
```
sys.schema_unused_indexes
```
  登录后复制
  视图来查找未使用的索引。
- 碎片整理（
  OPTIMIZE TABLE
  登录后复制
  ）：对于某些存储引擎（如MyISAM），可以整理表和索引碎片。对于InnoDB，这通常意味着重建表和索引，会锁定表，需要谨慎操作。

如何通过EXPLAIN语句分析查询性能并指导索引优化？

EXPLAIN

登录后复制

语句是MySQL查询优化的“X光片”，它能清晰地展示MySQL如何执行你的SQL查询。我个人觉得，掌握

EXPLAIN

登录后复制

的输出是索引优化路上最关键的一步。

当你执行

EXPLAIN SELECT ... FROM ... WHERE ...;

登录后复制

时，会得到一张表格，其中有几个关键列需要你重点关注：

id
登录后复制
：查询的标识符，越大越先执行，相同id的从上到下执行。
select_type
登录后复制
：查询类型，如
```
SIMPLE
```
登录后复制
（简单查询）、
```
PRIMARY
```
登录后复制
（主查询）、
```
SUBQUERY
```
登录后复制
（子查询）、
```
UNION
```
登录后复制
（联合查询）等。
table
登录后复制
：正在访问的表名。
type
登录后复制
：这是最重要的指标之一，表示MySQL如何找到所需行。
- ALL
  登录后复制
  ：最差的情况，全表扫描。这意味着你的查询没有用到索引，或者索引失效了。看到这个，你得警惕了。
- index
  登录后复制
  ：全索引扫描。虽然比
```
ALL
```
  登录后复制
  好，但仍然是扫描了整个索引。通常发生在
```
ORDER BY
```
  登录后复制
  或
```
GROUP BY
```
  登录后复制
  子句只使用索引列时。
- range
  登录后复制
  ：范围扫描。通常用于
```
<
```
  登录后复制
  ,
```
>
```
  登录后复制
  ,
```
LIKE
```
  登录后复制
  （非前缀匹配）、
```
BETWEEN
```
  登录后复制
  等操作。这是一个不错的类型。
- ref
  登录后复制
  ：非唯一索引扫描，或者唯一索引的非前缀扫描。例如，基于一个普通索引列的等值查询。
- eq_ref
  登录后复制
  ：唯一性索引扫描，通常发生在联接操作中，
```
JOIN
```
  登录后复制
  字段是主键或唯一索引。这是非常高效的类型。
- const
  登录后复制
  ,
  system
  登录后复制
  ：当查询优化器能将查询转换为一个常量时，这是最快的类型。
possible_keys
登录后复制
： MySQL认为可能用到的索引。
key
登录后复制
： MySQL实际选择使用的索引。如果
```
key
```
登录后复制
为NULL，说明没有使用索引。
key_len
登录后复制
：使用的索引的长度。越短越好，说明匹配的越精确。
rows
登录后复制
： MySQL估计为了找到所需行而扫描的行数。这个值越小越好，直接反映了查询效率。
Extra
登录后复制
：额外信息，这里面藏着很多秘密。
- Using filesort
  登录后复制
  ： MySQL需要对结果进行外部排序，通常发生在
```
ORDER BY
```
  登录后复制
  或
```
GROUP BY
```
  登录后复制
  的列没有索引覆盖时。这是个性能杀手。
- Using temporary
  登录后复制
  ： MySQL需要创建临时表来处理查询，通常发生在复杂的
```
GROUP BY
```
  登录后复制
  或
```
DISTINCT
```
  登录后复制
  操作中。这也是个性能杀手。
- Using index
  登录后复制
  ：恭喜你，这是一个覆盖索引，查询所需的所有数据都可以在索引中找到，无需回表。
- Using where
  登录后复制
  ：表明MySQL将通过
```
WHERE
```
  登录后复制
  条件来过滤结果。
- Using index condition
  登录后复制
  ：索引条件下推（Index Condition Pushdown, ICP），MySQL 5.6+的优化，它会在存储引擎层进行过滤，而不是将所有数据返回到服务器层再过滤。

举个例子，如果你看到

type: ALL

登录后复制

和

Extra: Using filesort

登录后复制

，那几乎可以肯定，你的查询需要索引优化。比如：

EXPLAIN SELECT * FROM orders WHERE customer_id = 123 ORDER BY order_time DESC;

登录后复制

如果

customer_id

登录后复制

没有索引，或者

order_time

登录后复制

没有与

customer_id

登录后复制

构成合适的复合索引，你很可能会看到糟糕的

type

登录后复制

和

Using filesort

登录后复制

。我的做法是，先看

type

登录后复制

，再看

rows

登录后复制

，最后看

Extra

登录后复制

，这三者结合起来，基本就能定位问题了。

复合索引的最佳实践与常见误区有哪些？

复合索引，也就是联合索引，是MySQL索引优化中非常强大但也容易踩坑的一个点。我见过太多开发者，因为不理解其原理而白白浪费了索引的功效。

最佳实践：

遵循最左前缀原则： 这是复合索引的基石。一个复合索引
```
(col1, col2, col3)
```
登录后复制
，可以有效地支持以下查询：
- ```
WHERE col1 = ?
```
  登录后复制
- ```
WHERE col1 = ? AND col2 = ?
```
  登录后复制
- ```
WHERE col1 = ? AND col2 = ? AND col3 = ?
```
  登录后复制
- 甚至
```
WHERE col1 = ? AND col3 = ?
```
  登录后复制
  （
```
col2
```
  登录后复制
  会被跳过，但
```
col1
```
  登录后复制
  依然会用到索引）
- 但它不能直接支持
```
WHERE col2 = ?
```
  登录后复制
  或
```
WHERE col3 = ?
```
  登录后复制
  的查询，因为它们不从最左边的列开始。
- 个人经验： 在设计复合索引时，我通常会把最常用于等值查询的列放在前面，然后是范围查询的列，最后是用于排序或分组的列。
高选择性优先： 在复合索引中，将选择性（唯一性）最高的列放在最前面。这能让MySQL在索引扫描时，尽快地缩小查找范围。比如，如果你有一个用户表，经常根据
```
city
```
登录后复制
和
```
age
```
登录后复制
查询，但
```
city
```
登录后复制
的选择性远高于
```
age
```
登录后复制
，那么
```
(city, age)
```
登录后复制
会比
```
(age, city)
```
登录后复制
更有效。

纳米搜索
纳米搜索：360推出的新一代AI搜索引擎

30

查看详情
考虑查询模式的组合： 如果你的应用有多种查询模式，例如：
- 查询A：
```
WHERE col1 = ? AND col2 = ?
```
  登录后复制
- 查询B：
```
WHERE col1 = ? AND col3 = ?
```
  登录后复制
- 查询C：
```
WHERE col1 = ?
```
  登录后复制
  那么一个
```
(col1, col2, col3)
```
  登录后复制
  的复合索引可能就能同时优化这三种查询。但如果还有查询D：
```
WHERE col2 = ? AND col3 = ?
```
  登录后复制
  ，那这个复合索引就无能为力了，你可能需要考虑额外的索引。
利用覆盖索引： 如果你的查询只需要索引中的列，那么这个查询就是“覆盖索引”查询。例如，
```
SELECT col1, col2 FROM table WHERE col1 = ?
```
登录后复制
，如果存在
```
(col1, col2)
```
登录后复制
的复合索引，那么MySQL就无需回表查询，大大提升性能。在
```
EXPLAIN
```
登录后复制
结果中，
```
Extra
```
登录后复制
列显示
```
Using index
```
登录后复制
就是这个意思。

常见误区：

不理解最左前缀原则： 这是最常见的误区。很多人以为只要列在复合索引里，不管顺序如何，查询都能用到。结果就是创建了索引，但查询性能依然不佳。
过度索引： 为每个可能的查询都创建一个独立的索引，或者在一个表上创建了过多的复合索引。
- 后果： 增加磁盘空间占用；写操作（INSERT, UPDATE, DELETE）时，所有相关索引都需要更新，导致性能下降；优化器在选择索引时，决策成本增加，甚至可能选择错误的索引。
- 我的建议： 保持索引数量的精简，一个复合索引能解决多个查询问题时，就尽量用复合索引。
索引低选择性列： 比如，对一个只有“是/否”两个值的布尔列单独创建索引，效果通常很差，因为MySQL可能认为全表扫描更快。当然，如果这个低选择性列是复合索引的第一列，并且后续有高选择性列，那又是另一回事。
索引列上使用函数或表达式： 任何在索引列上进行的函数操作（如
```
DATE()
```
登录后复制
,
```
SUBSTRING()
```
登录后复制
,
```
UPPER()
```
登录后复制
等）或算术运算，都会导致索引失效。例如，
```
WHERE YEAR(order_date) = 2023
```
登录后复制
会让
```
order_date
```
登录后复制
上的索引失效。正确的做法是，将函数应用于常量，例如
```
WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31'
```
登录后复制
。
不定期检查索引使用情况： 索引不是一劳永逸的。随着业务发展和数据量变化，一些索引可能变得不再适用，或者新的查询模式需要新的索引。

索引优化后，如何持续监控和维护以保持其高效性？

索引优化不是一次性的任务，它是一个持续的过程。就像汽车需要定期保养一样，数据库索引也需要监控和维护，才能保证其长期的高效性。我个人在实际工作中，会把这部分工作融入到日常的运维流程中。

持续监控慢查询日志：
- 即使你优化了一批慢查询，新的业务逻辑或数据增长可能又会产生新的慢查询。
- 我通常会设置自动化脚本，定时分析慢查询日志，并对新的慢查询进行告警。这能帮助我及时发现问题，而不是等到用户抱怨时才行动。
- 工具如
```
pt-query-digest
```
  登录后复制
  、Percona Monitoring and Management (PMM) 都非常有用。
利用MySQL的性能监控工具：
- SHOW GLOBAL STATUS LIKE 'Handler_read%';
  登录后复制
  ：这组状态变量可以告诉你MySQL在处理请求时，从表中读取行的次数。如果
```
Handler_read_rnd_next
```
  登录后复制
  （随机读取下一行）的值很高，可能意味着存在大量的全表扫描。
- Performance Schema 和 sys schema： 这是MySQL 5.7+提供的强大工具，可以深入分析SQL语句、等待事件、I/O等性能指标。
```
sys.schema_table_io_waits
```
  登录后复制
  可以帮你找出哪些表是I/O瓶颈，
```
sys.schema_index_statistics
```
  登录后复制
  能告诉你索引的使用情况。我发现
```
sys.schema_unused_indexes
```
  登录后复制
  视图特别有用，它能直接列出那些创建了却从未被使用的索引，这是清理冗余索引的好起点。
定期分析和优化表：
- ANALYZE TABLE tbl_name;
  登录后复制
  ：这个命令会重新收集表的统计信息，包括索引的基数（cardinality）。MySQL优化器会根据这些统计信息来决定最佳的查询执行计划。如果数据发生了大量增删改，统计信息可能会过时，导致优化器做出错误的判断。我通常会安排在业务低峰期执行这个操作。
- OPTIMIZE TABLE tbl_name;
  登录后复制
  ：对于InnoDB表，
```
OPTIMIZE TABLE
```
  登录后复制
  实际上等同于
```
ALTER TABLE tbl_name ENGINE=InnoDB;
```
  登录后复制
  ，它会重建表和索引，消除碎片，并更新统计信息。这通常需要锁定表，所以操作前务必评估影响。对于MyISAM表，它能有效回收空间和整理碎片。我个人在InnoDB表上更倾向于使用
```
pt-online-schema-change
```
  登录后复制
  这类工具进行在线DDL操作，以避免长时间的表锁定。
定期审查索引的有效性：
- 随着时间的推移，业务需求可能会变化，一些旧的索引可能变得不再重要，而新的查询模式可能需要新的索引。
- 我建议每隔一段时间（比如几个月或半年），就重新审视一下核心表的索引设计，结合慢查询日志和
```
EXPLAIN
```
  登录后复制
  结果，看看是否有可以移除的冗余索引，或者需要新增的索引。
- 不要害怕删除不必要的索引。虽然创建索引是为了提升查询，但过多的索引反而会拖累写入性能，并增加存储成本。
关注数据增长和分布：
- 数据量的持续增长，可能会让原本有效的索引变得效率低下。例如，一个在小数据量下表现良好的索引，在大数据量下可能因为选择性降低（比如某个字段的值分布变得不均匀）而失效。
- 数据分布的变化也会影响索引效果。例如，某个时间段的数据特别多，导致范围查询效率下降。
- 理解这些变化，能帮助你预判并调整索引策略。