学习MySQL索引设计原则提升数据库查询性能的最佳实践-mysql教程-PHP中文网

mysql索引优化需遵循选择性高、复合索引最左前缀、避免函数操作等原则，通过explain分析执行计划，确保查询使用合适索引，减少全表扫描和排序操作，从而提升性能。

学习MySQL索引设计原则提升数据库查询性能的最佳实践

说起MySQL的性能优化，索引绝对是个绕不开的话题。在我看来，它不是简单地给字段加个索引那么粗暴，而是一门需要深思熟虑的艺术，关乎你对数据访问模式的理解，以及对数据库内部机制的洞察。核心在于，我们通过精心设计的索引，能显著减少数据库在查询时需要扫描的数据量，从而极大地提升查询响应速度。但凡事过犹不及，不恰当的索引反而可能成为性能瓶颈，甚至带来意想不到的麻烦。

解决方案

要真正提升数据库查询性能，我们的工作流程得从理解业务需求开始，然后才是技术层面的实施。

首先，要明确你的查询模式。你的应用最频繁的查询语句长什么样？它们通常在哪些字段上进行过滤（

WHERE

登录后复制

子句）、排序（

ORDER BY

登录后复制

子句）或连接（

JOIN

登录后复制

子句）？这是索引设计的基石。比如，如果用户总是根据用户名和创建日期来查找订单，那么这两个字段就可能是索引的候选者。

其次，理解索引的成本。索引并非免费午餐，它会占用磁盘空间，并且在数据进行插入、更新、删除操作时，数据库需要额外的时间来维护这些索引结构。所以，索引设计是一个权衡的过程：查询性能的提升与写入性能的潜在下降。

再来，掌握核心的索引设计原则：

选择性（Cardinality）高的列优先考虑： 如果一个列的值重复率很低（比如身份证号、邮箱），那么它作为索引列的效果会非常好，因为索引能很快地定位到少数几行。反之，如果一个列只有少数几个值（比如性别），那么索引效果会大打折扣，因为即使使用了索引，数据库可能还是要扫描大部分数据。
WHERE、ORDER BY、GROUP BY 中出现的列： 这些是索引最能发挥作用的地方。索引能帮助数据库快速找到符合条件的行，或者避免全表扫描进行排序和分组。
复合索引的最左前缀原则： 如果你创建了一个包含多个列的复合索引，例如
```
(col1, col2, col3)
```
登录后复制
，那么这个索引可以用于查询
```
col1
```
登录后复制
、
```
col1
```
登录后复制
和
```
col2
```
登录后复制
、或者
```
col1
```
登录后复制
、
```
col2
```
登录后复制
和
```
col3
```
登录后复制
的组合。但它不能单独用于
```
col2
```
登录后复制
或
```
col3
```
登录后复制
的查询，这是一个非常常见的误区。
覆盖索引（Covering Index）： 当一个查询所需的所有列都包含在索引中时，数据库就不需要再去访问数据行本身，直接从索引中就能获取所有数据。这能显著减少I/O操作，性能提升非常明显。
避免在索引列上进行函数操作或隐式转换： 比如
```
WHERE DATE(create_time) = '2023-01-01'
```
登录后复制
会导致索引失效，因为数据库无法直接使用索引树来查找函数处理后的值。正确的做法是
```
WHERE create_time BETWEEN '2023-01-01 00:00:00' AND '2023-01-01 23:59:59'
```
登录后复制
。
定期审查和优化： 业务在发展，数据模式在变化，所以索引设计不是一劳永逸的。需要定期使用
```
EXPLAIN
```
登录后复制
分析慢查询日志，评估现有索引的有效性，并根据需要进行调整。

最终，通过这些原则的指导，我们能够构建出高效且维护成本可控的索引体系。

MySQL索引并非越多越好，如何避免过度索引带来的性能陷阱？

很多人一遇到慢查询，第一反应就是“加个索引试试”，结果往往是索引越加越多，性能却不见得好转，甚至可能变得更差。这背后其实是过度索引的陷阱。

过度索引的第一个问题是写入性能的下降。每次对表进行

INSERT

登录后复制

、

UPDATE

登录后复制

、

DELETE

登录后复制

操作时，数据库不仅要修改数据本身，还要同步更新所有相关的索引。索引越多，需要维护的结构就越多，这个维护成本就越高，直接导致写入操作变慢。想象一下，你往一个房间里搬家具，如果房间里堆满了杂物（索引），你每搬一件东西都要先挪开一大堆杂物，效率自然就低了。

其次，磁盘空间的浪费。每个索引都需要占用存储空间。虽然单个索引可能不大，但当表很大、索引很多时，累积起来的存储开销也是不容忽视的。

更隐蔽的问题在于，查询优化器的困惑。当一个表上有太多索引时，MySQL的查询优化器在选择执行计划时，需要花费更多的时间和资源来评估哪个索引最适合当前查询。有时，它甚至可能做出错误的决策，选择了一个效率不高的索引，或者干脆放弃使用索引，导致全表扫描。我见过一些情况，优化器甚至会尝试合并多个单列索引来满足一个多条件查询，这通常不如一个设计得当的复合索引效率高。

避免过度索引的关键在于“精”。我们需要：

聚焦核心查询： 识别出那些真正频繁且性能敏感的查询，优先为它们设计索引。
复合索引优于多个单列索引： 如果多个列经常一起出现在
```
WHERE
```
登录后复制
子句中，考虑创建一个复合索引，而不是为每个列单独创建索引。这不仅节省空间，也更符合优化器的预期。
利用
EXPLAIN
登录后复制
验证：在添加或调整索引后，务必使用
```
EXPLAIN
```
登录后复制
来检查查询是否真正使用了你期望的索引，以及其效率如何。如果一个索引从未被使用，或者使用频率极低，那么它很可能就是多余的。
定期清理： 对于不再使用或效率低下的索引，要果断删除。这可以通过分析慢查询日志和
```
information_schema.STATISTICS
```
登录后复制
表来辅助判断。

记住，索引是提升性能的工具，而不是解决所有性能问题的银弹。恰到好处的索引，才是数据库性能的保障。

复合索引的设计技巧：如何利用最左前缀原则优化多条件查询？

复合索引（也称组合索引或多列索引）是MySQL索引中一个非常强大但也容易让人困惑的概念，其核心就是“最左前缀原则”。理解并巧妙运用这个原则，能让你在处理多条件查询时事半功倍。

设计师AI工具箱

最懂设计师的效率提升平台，实现高效设计出图和智能改图，室内设计，毛坯渲染，旧房改造，软装设计

124

查看详情

一个复合索引，比如

INDEX idx_name_age_city (name, age, city)

登录后复制

，它实际上创建了一个有序的数据结构，这个结构首先按照

name

登录后复制

排序，在

name

登录后复制

相同的情况下再按照

age

登录后复制

排序，最后在

name

登录后复制

和

age

登录后复制

都相同的情况下按照

city

登录后复制

排序。

最左前缀原则意味着，这个索引可以支持从左边开始的任意前缀列组合的查询。具体来说：

WHERE name = 'xxx'
登录后复制
：索引
```
idx_name_age_city
```
登录后复制
可以完全用于这个查询，因为它使用了最左边的列
```
name
```
登录后复制
。
WHERE name = 'xxx' AND age = 25
登录后复制
：这个查询也能很好地利用索引，因为它使用了
```
name
```
登录后复制
和
```
age
```
登录后复制
这两个最左前缀列。
WHERE name = 'xxx' AND age = 25 AND city = 'yyy'
登录后复制
：当然，这也能完全利用索引。

然而，如果你尝试：

WHERE age = 25
登录后复制
：这个查询将无法使用
```
idx_name_age_city
```
登录后复制
索引，因为
```
age
```
登录后复制
不是最左前缀。索引的排序是从
```
name
```
登录后复制
开始的，跳过
```
name
```
登录后复制
直接查找
```
age
```
登录后复制
就像在一本按姓氏排序的电话簿里找一个不知道姓氏但知道年龄的人一样困难。
WHERE city = 'yyy'
登录后复制
：同理，也无法使用。
WHERE age = 25 AND city = 'yyy'
登录后复制
：同样无法使用。

那么，如何设计复合索引才能最大化利用最左前缀原则呢？

将最常用于
WHERE
登录后复制
子句中等值查询（
=
登录后复制
或
IN
登录后复制
）的列放在最左边。
将选择性（Cardinality）最高的列放在最左边。 这有助于数据库更快地缩小搜索范围。
考虑
ORDER BY
登录后复制
和
GROUP BY
登录后复制
子句。如果你的查询经常需要对某些列进行排序或分组，并且这些列与
```
WHERE
```
登录后复制
子句中的列有重叠，那么将它们放在复合索引的后续位置可以帮助避免额外的文件排序（
```
Using filesort
```
登录后复制
）。例如，
```
WHERE a = X ORDER BY b
```
登录后复制
，那么
```
(a, b)
```
登录后复制
这样的复合索引会非常有效。
避免冗余索引。 如果你已经有了
```
(a, b, c)
```
登录后复制
的复合索引，那么单独的
```
(a)
```
登录后复制
和
```
(a, b)
```
登录后复制
索引就是冗余的，可以考虑删除，因为复合索引已经包含了它们的功能。

举个例子，假设你有一个用户表

users

登录后复制

，经常需要查询“某个城市中某个年龄段的活跃用户”，查询语句可能是

SELECT * FROM users WHERE city = 'Beijing' AND age BETWEEN 20 AND 30 AND is_active = 1 ORDER BY last_login_time DESC;

登录后复制

一个可能的复合索引设计是

INDEX idx_city_age_active_login (city, age, is_active, last_login_time)

登录后复制

。

```
city
```
登录后复制
放在最左边，因为它是等值查询且可能区分度较高。
```
age
```
登录后复制
紧随其后，虽然是范围查询，但在
```
city
```
登录后复制
确定后能进一步缩小范围。
```
is_active
```
登录后复制
放在后面，因为它可能是低选择性的，但作为
```
WHERE
```
登录后复制
条件的一部分，包含在索引中可以避免回表。
```
last_login_time
```
登录后复制
放在最后，以支持
```
ORDER BY
```
登录后复制
操作，可能形成覆盖索引，避免
```
Using filesort
```
登录后复制
。

通过这样的设计，你可以确保查询在大多数情况下都能充分利用索引，避免全表扫描和额外的排序操作，从而获得最佳性能。

如何利用EXPLAIN分析SQL查询，诊断索引使用效率？

EXPLAIN

登录后复制

是MySQL提供的一个非常强大的工具，它能帮助我们深入了解SQL查询的执行计划，从而诊断索引使用效率，找出潜在的性能瓶颈。掌握

EXPLAIN

登录后复制

的输出是每个数据库优化者必备的技能。

使用方法很简单，你只需要在任何

SELECT

登录后复制

、

INSERT

登录后复制

、

UPDATE

登录后复制

、

DELETE

登录后复制

语句前加上

EXPLAIN

登录后复制

关键字即可：

EXPLAIN SELECT * FROM users WHERE city = 'Beijing' AND age > 25;

登录后复制

EXPLAIN

登录后复制

的输出通常包含多列信息，其中最重要的几列是：

id
登录后复制
：查询的唯一标识符。对于复杂的查询（如包含子查询、UNION等），会有多个
```
id
```
登录后复制
。
select_type
登录后复制
：查询的类型，如
```
SIMPLE
```
登录后复制
(简单查询)、
```
PRIMARY
```
登录后复制
(最外层查询)、
```
SUBQUERY
```
登录后复制
(子查询)、
```
UNION
```
登录后复制
等。
table
登录后复制
：当前操作的表名。
partitions
登录后复制
：匹配的分区信息，对于分区表有用。
type
登录后复制
： 这是最关键的指标之一，表示MySQL如何查找表中的行。
- system
  登录后复制
  /
  const
  登录后复制
  ：最佳，表示查询最多只有一行结果，通常是主键或唯一索引的等值查询。
- eq_ref
  登录后复制
  ：很好，用于联接查询，表示前一个表的每一行都精确匹配到当前表的一行（通常通过主键或唯一索引）。
- ref
  登录后复制
  ：良好，表示通过非唯一索引查找匹配值。
- range
  登录后复制
  ：良好，表示对索引的范围扫描（如
```
BETWEEN
```
  登录后复制
  ,
```
>
```
  登录后复制
  ,
```
<
```
  登录后复制
  ）。
- index
  登录后复制
  ：较差，表示对整个索引进行扫描，通常是覆盖索引但没有
```
WHERE
```
  登录后复制
  条件限制范围，或者
```
ORDER BY
```
  登录后复制
  仅依赖索引。比
```
ALL
```
  登录后复制
  好，因为它只扫描索引而不需要回表。
- ALL
  登录后复制
  ：最差，表示全表扫描。这意味着没有使用索引或者索引使用不当。看到
```
ALL
```
  登录后复制
  几乎总是意味着需要优化。
possible_keys
登录后复制
： MySQL认为可能用于查找的索引。
key
登录后复制
： MySQL实际选择使用的索引。 如果
```
key
```
登录后复制
为
```
NULL
```
登录后复制
，表示没有使用索引。
key_len
登录后复制
：实际使用的索引的长度（字节数）。可以用来判断复合索引使用了多少前缀。
ref
登录后复制
：显示哪些列或常量被用于
```
key
```
登录后复制
所表示的索引查找。
rows
登录后复制
： MySQL估计要扫描的行数。 这个值越小越好。
filtered
登录后复制
： MySQL估计通过表条件过滤后，剩余的行百分比。越高越好。
Extra
登录后复制
： 额外信息，提供关于查询执行计划的更多细节，非常重要。
- Using filesort
  登录后复制
  ：糟糕！表示MySQL需要额外的排序操作，通常发生在无法通过索引完成排序时。
- Using temporary
  登录后复制
  ：糟糕！表示MySQL需要创建临时表来处理查询（通常用于
```
GROUP BY
```
  登录后复制
  或
```
DISTINCT
```
  登录后复制
  操作，且无法通过索引优化）。
- Using index
  登录后复制
  ：极好！表示查询是覆盖索引，所有需要的数据都从索引中获取，无需回表。
- Using where
  登录后复制
  ：表示MySQL需要通过
```
WHERE
```
  登录后复制
  子句来过滤结果。如果
```
type
```
  登录后复制
  是
```
ALL
```
  登录后复制
  或
```
index
```
  登录后复制
  ，同时出现
```
Using where
```
  登录后复制
  ，可能意味着索引没有完全覆盖查询条件。
- Using index condition
  登录后复制
  ：在MySQL 5.6+中，表示使用了索引条件下推（Index Condition Pushdown, ICP），优化器在存储引擎层就对索引条目进行过滤，减少了回表次数。

诊断步骤：

关注
type
登录后复制
：优先将
```
ALL
```
登录后复制
和
```
index
```
登录后复制
优化为
```
range
```
登录后复制
、
```
ref
```
登录后复制
或更好。
查看
key
登录后复制
：确认是否使用了期望的索引。如果
```
key
```
登录后复制
为
```
NULL
```
登录后复制
，你需要重新审视索引设计。
检查
rows
登录后复制
：评估扫描行数是否合理。如果
```
rows
```
登录后复制
值很大，即使
```
type
```
登录后复制
不是
```
ALL
```
登录后复制
，也可能存在优化空间。
解析
Extra
登录后复制
：特别关注
```
Using filesort
```
登录后复制
和
```
Using temporary
```
登录后复制
，它们是性能瓶颈的明显信号。
```
Using index
```
登录后复制
是你努力追求的目标。