MySQL增删改查性能优化：索引使用与避免全表扫描技巧

MySQL 增删改查性能优化的核心，并不是简单地“多建索引”，而是让查询尽量缩小需要扫描的数据范围，同时控制索引本身带来的写入维护成本。 对查询来说，应重点检查 WHERE、JOIN、ORDER BY 等条件能否有效利用索引；对 UPDATE、DELETE 来说，要确保定位目标记录时不会扫描整张表； 对 INSERT 来说，则要避免维护过多无价值索引。实际优化时，可以按照“定位慢 SQL → 查看执行计划 → 调整索引或 SQL → 再次验证”的顺序处理。

一、先理解：全表扫描为什么容易变慢

假设有一张用户表 users，包含几百万条记录。现在执行下面的查询：

SELECT id, username, email
FROM users
WHERE email = 'test@example.com';

如果 email 没有索引，MySQL 为了找到符合条件的数据，可能需要从表中的第一条记录开始逐行判断， 直到检查完符合执行条件的数据。这类访问方式通常就是我们关注的全表扫描。

如果给 email 建立合适的索引：

CREATE INDEX idx_users_email ON users(email);

MySQL 就有机会通过索引快速定位目标记录，而不必检查表中的大部分数据。表越大、查询越频繁，这种差异越明显。

不过，“没有全表扫描”并不等于 SQL 一定快，“出现全表扫描”也不一定永远错误。 如果表本身只有几十条数据，或者查询本来就需要读取表中绝大多数记录，优化器选择全表扫描可能是合理的。 真正需要关注的是：数据量较大、本应只返回少量记录，却因为条件无法利用索引而扫描大量数据。

二、不要凭感觉判断，先用 EXPLAIN 查看执行计划

判断查询有没有合理利用索引，最常用的方法之一是查看 EXPLAIN。 比如：

EXPLAIN
SELECT id, username, email
FROM users
WHERE email = 'test@example.com';

查看执行计划时，可以重点关注以下信息。不同 MySQL 版本的输出字段和细节可能存在差异，但这些基本判断方式长期适用。

三、SELECT 查询优化：索引应该建在真正用于筛选的数据上

查询优化通常是增删改查中最常见的部分。一个基本原则是：优先分析那些能够显著缩小数据范围的查询条件。

例如订单表：

CREATE TABLE orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    status TINYINT NOT NULL,
    created_at DATETIME NOT NULL,
    amount DECIMAL(10,2) NOT NULL
);

业务经常需要查询某个用户最近的订单：

SELECT id, status, amount, created_at
FROM orders
WHERE user_id = 10086
ORDER BY created_at DESC
LIMIT 20;

如果只给 user_id 建索引，MySQL 可以先找到这个用户的记录，但之后仍可能需要进一步处理排序。 如果这一查询非常高频，可以考虑让索引同时匹配过滤条件和排序字段：

CREATE INDEX idx_orders_user_created
ON orders(user_id, created_at);

这样设计的目的不是“联合索引一定比单列索引好”，而是这个查询的访问模式本身就是： 先按照 user_id 缩小范围，再在相同用户的数据中按照 created_at 获取最近记录。

联合索引要注意字段顺序

假设存在：

INDEX idx_orders_user_status_created
(user_id, status, created_at)

下面这种查询能够从索引最左侧的 user_id 开始匹配：

SELECT id, amount
FROM orders
WHERE user_id = 10086
  AND status = 1;

但如果查询只写：

SELECT id, amount
FROM orders
WHERE status = 1;

就不能简单理解为“联合索引里有 status，所以一定能高效使用该索引”。 联合索引的排列顺序必须围绕实际查询模式设计。

四、这些写法很容易让本来存在的索引失去优势

1. 对索引列进行函数或表达式计算

例如：

SELECT id, username
FROM users
WHERE YEAR(created_at) = 2026;

如果原本只有普通的 created_at 索引，对字段套用函数后，索引利用方式可能和直接比较原始字段不同。 如果需求是查询某一年的数据，更清晰的方式通常是使用时间范围：

SELECT id, username
FROM users
WHERE created_at >= '2026-01-01 00:00:00'
  AND created_at <  '2027-01-01 00:00:00';

这种写法直接针对原始时间列进行范围查找，也更准确地表达了查询意图。

2. LIKE 以通配符开头

SELECT id, username
FROM users
WHERE username LIKE '%mysql%';

这种条件要求匹配任意位置出现的字符串。普通 B-tree 索引通常难以像 LIKE 'mysql%' 这种前缀查询一样直接缩小范围。

如果业务真正需求是全文关键词检索，就不应该单纯依赖给字符串列加普通索引解决，而应根据数据规模、语言和搜索需求考虑适合的全文检索方案。

3. 数据类型不一致

如果字段和查询参数类型不一致，可能产生隐式类型转换，并影响索引利用方式。 应尽量保证程序传入的参数类型与字段定义保持一致。

例如数据库字段：

user_id BIGINT

程序查询时就应将其作为数值处理，而不是长期混用字符串和数字：

SELECT id, amount
FROM orders
WHERE user_id = 10086;

4. OR 条件组合不合理

例如：

SELECT id, username
FROM users
WHERE email = 'a@example.com'
   OR mobile = '13800000000';

这并不意味着 OR 一定导致全表扫描，但两个条件是否都有合适索引、优化器如何估算成本，都会影响最终执行计划。 遇到这类 SQL，应直接查看 EXPLAIN，而不是套用“看到 OR 就改写”的固定规则。

五、覆盖索引：减少不必要的回表读取

查询通过普通二级索引找到记录后，如果还需要获取索引中不存在的字段，通常还需要进一步读取对应的数据记录。 如果查询需要的字段已经能够从索引中取得，就可能减少额外的数据读取。

例如业务经常执行：

SELECT user_id, created_at
FROM orders
WHERE user_id = 10086
ORDER BY created_at DESC
LIMIT 20;

已经存在：

INDEX idx_orders_user_created(user_id, created_at)

查询条件、排序字段和需要返回的两列都与这个索引高度吻合，这就是覆盖索引思路可以发挥作用的典型场景之一。

但不要为了实现覆盖索引，把大量低价值字段全部塞入一个非常宽的联合索引。 索引越多、越宽，占用空间越大，同时 INSERT、UPDATE、DELETE 时需要维护的内容也越多。

六、UPDATE 优化：先快速找到记录，再执行修改

UPDATE 本质上通常包含两个过程：找到需要修改的记录，然后修改数据。因此 UPDATE 是否高效，同样受 WHERE 条件索引影响。

例如：

UPDATE orders
SET status = 2
WHERE id = 100001;

如果 id 是主键，MySQL 可以快速定位目标记录。

而下面的更新：

UPDATE orders
SET status = 2
WHERE user_id = 10086
  AND status = 1;

如果这是一种常见操作，就应该检查 user_id 和 status 对应的查询路径是否合理。 可以先使用同样的 WHERE 条件执行 SELECT 的 EXPLAIN：

EXPLAIN
SELECT id
FROM orders
WHERE user_id = 10086
  AND status = 1;

如果这里就已经需要扫描大量记录，那么真正执行 UPDATE 时也应该重点评估。

不要一次修改过多数据而毫无边界

即使 WHERE 条件正确，大批量更新仍可能持续较长时间并产生较大的事务压力。 对非常大的历史数据修改任务，可以按照主键区间或业务上明确的范围分批执行。

例如：

UPDATE orders
SET status = 3
WHERE id > 100000
  AND id <= 110000
  AND status = 2;

下一批再继续处理后面的主键区间。实际每批多少条不能机械固定，应结合单行大小、并发写入、事务时间和数据库负载测试确定。

七、DELETE 优化：最危险的往往不是慢，而是扫描范围失控

DELETE 和 UPDATE 类似，也需要先定位记录。比如：

DELETE FROM orders
WHERE user_id = 10086
  AND status = 0;

在真正删除之前，可以先使用相同条件执行：

SELECT id
FROM orders
WHERE user_id = 10086
  AND status = 0;

确认两个问题：

• WHERE 条件是否能正确定位到预期记录；
• 执行计划是否需要扫描明显超出预期的数据。

对于一次删除大量历史记录的任务，同样更适合按明确范围分批处理，而不是让一个超大 DELETE 事务持续很长时间。

DELETE FROM orders
WHERE id >= 100000
  AND id < 110000
  AND created_at < '2025-01-01 00:00:00';

八、INSERT 优化：索引越多，写入成本通常越高

索引可以提高很多查询的效率，但并不是没有代价。 插入一条数据时，MySQL 不仅要写入数据，还需要维护相关索引结构。

假设一张表同时存在：

INDEX idx_user_id(user_id)
INDEX idx_status(status)
INDEX idx_created_at(created_at)
INDEX idx_user_status(user_id, status)
INDEX idx_user_created(user_id, created_at)

每次 INSERT 都需要维护多个索引。因此优化 INSERT 的一个重要方向，就是清理重复、长期不使用、价值较低的索引。

例如已经存在联合索引：

INDEX idx_user_created(user_id, created_at)

是否还需要单独保留：

INDEX idx_user_id(user_id)

不能只根据“字段重复”直接删除，而应该结合真实查询、执行计划和索引使用情况判断。 但它确实是值得检查的重复索引候选。

批量插入通常比大量单条语句更合适

如果业务允许，可以使用一次 INSERT 插入多行：

INSERT INTO users (username, email, created_at)
VALUES
('user_a', 'a@example.com', NOW()),
('user_b', 'b@example.com', NOW()),
('user_c', 'c@example.com', NOW());

与应用程序连续发送大量独立 INSERT 相比，批量提交可以减少部分语句解析、网络往返等额外开销。 但单批数据也不应无限增大，应根据应用和数据库实际环境进行测试。

九、避免 SELECT *，但原因不只是“少返回几个字段”

如果业务只需要订单编号和金额：

SELECT id, amount
FROM orders
WHERE user_id = 10086;

通常比无条件写：

SELECT *
FROM orders
WHERE user_id = 10086;

更容易控制读取和传输的数据量。尤其当表中包含大文本、JSON、较长字符串等字段时， 没有必要把程序根本不用的数据一起取出来。

指定字段还有另一个价值：它能让查询与索引设计之间的关系更加明确，在适当情况下也更容易形成覆盖索引。

当然，SELECT * 并不会自动导致全表扫描。是否使用索引主要还是取决于 WHERE、JOIN、排序等条件及优化器决策。 不应该把“避免 SELECT *”和“避免全表扫描”简单画成等号。

十、LIMIT 也不能自动解决扫描问题

下面的 SQL 看起来只取 20 条：

SELECT id, username
FROM users
WHERE email LIKE '%example%'
LIMIT 20;

但 LIMIT 20 只是限制最终返回的记录数，并不意味着数据库一定只检查 20 条数据。 如果过滤条件无法快速定位数据，MySQL 仍可能检查大量记录才能找到这 20 条符合条件的结果。

所以看到分页查询很慢时，应检查的是“数据库需要从多少数据里找出这一页”，而不仅是“这一页返回多少行”。

十一、深分页需要注意 OFFSET 扫描成本

常见分页：

SELECT id, title, created_at
FROM articles
ORDER BY id DESC
LIMIT 20 OFFSET 200000;

返回结果只有 20 条，并不代表前面的偏移完全没有处理成本。 当页码越来越深时，这种分页方式可能越来越慢。

如果业务允许按照上一页最后一条记录继续向后读取，可以改为基于有序键的游标式分页：

SELECT id, title, created_at
FROM articles
WHERE id < 580001
ORDER BY id DESC
LIMIT 20;

这里的 580001 表示上一页最后一条记录的 id。这样做的关键是利用已经有序并且可以高效比较的索引列， 从确定的位置继续读取，而不是不断跳过大量历史结果。

十二、低区分度字段不能机械理解为“绝对不能建索引”

例如订单状态只有：

0 = 待支付
1 = 已支付
2 = 已取消

单独给 status 建索引未必有很高价值，因为每个状态可能对应大量数据。 如果：

SELECT *
FROM orders
WHERE status = 1;

最终需要读取表中很大比例的数据，即使存在索引，优化器也可能认为直接扫描更加合适。

但如果实际业务查询是：

SELECT id, amount
FROM orders
WHERE user_id = 10086
  AND status = 1;

那么 status 与 user_id 组合后就可能具备不同价值。 所以判断一个字段是否适合索引，不能只看它自身有多少种值，还要看它与实际查询条件怎样组合。

十三、不要建立大量重复或用途重叠的索引

假设存在下面几个索引：

INDEX idx_a(user_id)
INDEX idx_b(user_id, status)
INDEX idx_c(user_id, status, created_at)

这三个索引存在明显的字段前缀重叠，但是否都需要保留，要结合真实查询确定。 如果业务从来没有单独依赖某个短索引，而更长的联合索引已经能很好地覆盖对应访问模式，那么短索引可能成为清理候选。

但删除索引之前必须检查实际 SQL 和执行计划，因为更短的索引体积可能更小，在某些查询场景下仍可能具有价值。 索引优化的正确方法不是“看到重复字段就删”，而是确认这个索引是否承担独立的查询任务。

十四、索引字段本身也不宜无节制增加

有些开发者发现一个 SQL 慢，就不断向联合索引追加字段，例如：

INDEX idx_all(
    user_id,
    status,
    type,
    category_id,
    created_at,
    updated_at
)

这种索引可能可以帮助某一个特定查询，但同时会增加索引空间和写入维护成本，而且未必适合其他查询。

更合理的设计思路是从高频 SQL 反推：

1. WHERE 中哪些字段负责快速缩小数据范围；
2. 是否还需要匹配排序或范围查询；
3. 是否值得通过少量额外字段实现覆盖；
4. 新增索引是否与现有索引明显重复。

十五、一个完整的优化示例

假设订单表已经积累了大量数据，后台经常查询某个用户的已支付订单：

SELECT id, amount, created_at
FROM orders
WHERE user_id = 10086
  AND status = 1
ORDER BY created_at DESC
LIMIT 20;

步骤 1：先查看执行计划

EXPLAIN
SELECT id, amount, created_at
FROM orders
WHERE user_id = 10086
  AND status = 1
ORDER BY created_at DESC
LIMIT 20;

重点确认实际使用了什么索引、预计需要检查多少行，以及是否产生额外排序。

步骤 2：根据查询结构设计候选索引

这个查询的主要逻辑是：

• 限定一个 user_id；
• 进一步限定 status；
• 按照 created_at 排序；
• 只取最近 20 条。

可以评估类似下面的索引：

CREATE INDEX idx_orders_user_status_created
ON orders(user_id, status, created_at);

步骤 3：重新执行 EXPLAIN

EXPLAIN
SELECT id, amount, created_at
FROM orders
WHERE user_id = 10086
  AND status = 1
ORDER BY created_at DESC
LIMIT 20;

比较修改前后实际使用的 key、预估扫描行数以及 Extra 信息，确认索引是否真的改善了访问路径。

步骤 4：再看写入代价

如果订单表写入非常频繁，就不能只看这一条 SELECT 变快了多少。 还应该确认新索引是否与原有 (user_id, status)、(user_id, created_at) 等索引大量重叠， 避免为了优化一个查询长期保留过多索引。

十六、增删改查性能优化时最常见的几个误区

误区一：所有 WHERE 字段都应该建索引

不对。字段是否值得建立索引，要看数据分布、查询频率、组合条件和返回数据比例。 一个几乎每次查询都会返回大部分记录的条件，即使建立索引，也未必明显改善性能。

误区二：索引越多查询越快

索引可以增加查询路径，但也需要占用空间，并增加 INSERT、UPDATE、DELETE 的维护工作。 索引应该为真实高频访问模式服务，而不是把所有列都“保险式”加上索引。

误区三：查询用了索引就一定快

即使执行计划显示使用索引，如果索引最终仍匹配几十万甚至更多记录，SQL 依然可能很慢。 优化时还要观察扫描行数、实际返回数据量、排序、回表和磁盘读取等因素。

误区四：没有使用索引就一定有问题

小表扫描、读取大部分数据、优化器判断全表扫描成本更低等场景，都可能合理地不使用索引。 判断性能问题应以具体查询成本和实际运行情况为依据。

误区五：加上 LIMIT 就不用担心扫描量

LIMIT 限制的是结果数量，不一定限制查找这些结果需要检查的数据量。 WHERE 条件和排序无法利用合适索引时，LIMIT 很小的查询仍可能消耗较多资源。

十七、实际排查时可以按照这套顺序处理

遇到 MySQL 增删改查变慢，不建议一上来就修改数据库参数或大量添加索引。 可以先按照下面的顺序排查：

1. 找到真正耗时或调用频繁的 SQL；
2. 确认查询实际希望返回多少数据；
3. 用 EXPLAIN 检查访问路径；
4. 检查 WHERE、JOIN、ORDER BY 所涉及的字段；
5. 检查联合索引顺序是否符合查询方式；
6. 检查是否对索引列使用了不利于查找的函数或表达式；
7. 检查是否存在深分页、大范围模糊查询等问题；
8. 修改 SQL 或索引；
9. 重新查看执行计划并进行实际性能验证；
10. 同时检查新增索引对 INSERT、UPDATE、DELETE 的影响。

总结