MySQL 学习记录（二）：索引、EXPLAIN、事务与锁

2401_83011856

360人浏览 · 2026-08-21 17:31:33

2401_83011856 · 2026-08-21 17:31:33 发布

MySQL 学习记录（二）：索引、EXPLAIN、事务与锁

上一篇整理了表结构、增删改查、多表连接和常用函数。这一篇继续整理索引、执行计划、事务、隔离级别和锁。

这些内容单看定义比较散，放到 SQL 执行过程中就容易区分：

索引：减少查询需要检查的数据量
EXPLAIN：查看 MySQL 准备怎样执行 SQL
事务：保证一组操作的一致性
锁：处理多个事务同时读写数据的问题

一、索引是什么

没有索引时，MySQL 可能需要逐行检查数据。

例如：

SELECT *
FROM sys_user
WHERE username = 'zhangsan';

如果 username 没有索引，数据量较大时可能进行全表扫描。

创建索引：

CREATE INDEX idx_user_username
ON sys_user(username);

再查询相同用户名时，MySQL 可以通过索引缩小查找范围。

索引可以理解成单独维护的一套数据结构。创建索引后，数据库不仅要保存表数据，还要保存索引数据。

所以索引不是免费的：

索引占用磁盘空间；
插入数据时需要维护索引；
修改索引字段时需要更新索引；
删除数据时也要调整索引；
索引太多会增加写操作成本。

创建索引前需要先看实际查询，而不是给每个字段都加索引。

二、InnoDB 为什么使用 B+Tree

InnoDB 中常见索引使用 B+Tree 结构。

B+Tree 的几个特点：

数据按照索引键值保持有序；
非叶子节点主要保存索引信息；
叶子节点保存数据或主键值；
叶子节点之间通过指针连接；
树的高度通常比较低。

数据库数据主要保存在磁盘中。一次磁盘页读取的成本比内存计算高，因此索引结构需要尽量减少磁盘访问次数。

B+Tree 一个节点可以保存较多键值，树的分支较多，高度较低。查询单条数据时，不需要从根节点向下经过很多层。

叶子节点保持有序，也适合范围查询：

SELECT *
FROM sys_user
WHERE id BETWEEN 1000 AND 2000;

找到范围起点后，可以沿着叶子节点继续读取后续数据。

三、聚簇索引和二级索引

InnoDB 表的数据和主键索引存放在一起，这个索引一般称为聚簇索引。

假设表结构如下：

CREATE TABLE sys_user (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    phone VARCHAR(20),
    status TINYINT NOT NULL
) ENGINE = InnoDB;

主键索引的叶子节点中保存完整行数据。

通过主键查询：

SELECT *
FROM sys_user
WHERE id = 100;

可以在主键索引中直接找到这一行。

给用户名创建普通索引：

CREATE INDEX idx_user_username
ON sys_user(username);

这个普通索引属于二级索引。其叶子节点通常保存：

username + 主键 id

执行：

SELECT *
FROM sys_user
WHERE username = 'zhangsan';

可能经过两个步骤：

在 idx_user_username 中找到对应主键；
根据主键到聚簇索引中读取完整行。

第二步通常称为回表。

四、回表和覆盖索引

查询：

SELECT id, username
FROM sys_user
WHERE username = 'zhangsan';

普通索引 idx_user_username 中已经包含 username 和主键 id，不需要再读取完整行。这种情况属于覆盖索引。

如果查询：

SELECT id, username, phone
FROM sys_user
WHERE username = 'zhangsan';

而索引中没有 phone，MySQL 通常需要根据主键回表查询。

可以建立联合索引：

CREATE INDEX idx_user_username_phone
ON sys_user(username, phone);

对于下面的查询：

SELECT id, username, phone
FROM sys_user
WHERE username = 'zhangsan';

需要的字段都能从索引中取得，可能不再回表。

覆盖索引可以减少读取次数，但不能把接口需要的所有字段全部塞进索引。联合索引字段越多，占用空间越大，维护成本也越高。

五、常见索引类型

1. 主键索引

PRIMARY KEY (id)

主键值唯一并且不能为 NULL。一张表只能有一个主键。

2. 唯一索引

CREATE UNIQUE INDEX uk_user_phone
ON sys_user(phone);

唯一索引除了提高查询效率，还能保证数据不重复。

如果手机号必须唯一，只在 Java 中查询是否存在不够。并发请求可能同时通过检查，数据库唯一约束才是最后一道限制。

3. 普通索引

CREATE INDEX idx_user_status
ON sys_user(status);

普通索引允许重复值。

4. 联合索引

CREATE INDEX idx_user_status_time
ON sys_user(status, create_time);

一个索引包含多个字段。

5. 全文索引

全文索引用于较长文本的关键词搜索。普通 B+Tree 索引不适合：

LIKE '%关键字%'

中文全文检索还涉及分词。数据量和搜索要求较高时，也可能使用 Elasticsearch 等专门的搜索工具。

六、联合索引和最左前缀

创建联合索引：

CREATE INDEX idx_user_status_time
ON sys_user(status, create_time);

这个索引首先按照 status 排序，状态相同时再按照 create_time 排序。

下面的查询能够使用联合索引的前缀：

SELECT *
FROM sys_user
WHERE status = 1;

SELECT *
FROM sys_user
WHERE status = 1
  AND create_time >= '2026-01-01 00:00:00';

只查询第二个字段时：

SELECT *
FROM sys_user
WHERE create_time >= '2026-01-01 00:00:00';

通常不能充分利用这个联合索引，因为缺少最左侧的 status。

最左前缀指的是联合索引字段的排列，不是 SQL 中条件书写顺序。

下面两条 SQL 对优化器来说通常没有本质区别：

WHERE status = 1
  AND create_time >= '2026-01-01'

WHERE create_time >= '2026-01-01'
  AND status = 1

重要的是索引定义顺序：

(status, create_time)

不是 WHERE 后面的书写顺序。

七、范围条件对联合索引的影响

创建索引：

CREATE INDEX idx_user_status_time_id
ON sys_user(status, create_time, id);

查询：

SELECT *
FROM sys_user
WHERE status = 1
  AND create_time >= '2026-01-01'
  AND id = 100;

status 是等值条件，create_time 是范围条件。

在传统的最左前缀说明中，范围条件右侧的字段通常不能继续用于缩小索引扫描范围。MySQL 的版本、索引条件下推和优化器策略会影响实际结果，所以不能只靠规则判断。

最稳妥的方法是执行：

EXPLAIN
SELECT *
FROM sys_user
WHERE status = 1
  AND create_time >= '2026-01-01'
  AND id = 100;

再结合：

key
key_len
rows
Extra

判断实际使用了哪些索引部分。

八、哪些情况可能导致索引没有按预期使用

1. 在索引字段上使用函数

索引：

CREATE INDEX idx_user_create_time
ON sys_user(create_time);

查询：

SELECT *
FROM sys_user
WHERE DATE(create_time) = '2026-08-20';

对字段使用 DATE() 后，普通索引可能不能直接用于定位。

可以改成范围：

SELECT *
FROM sys_user
WHERE create_time >= '2026-08-20 00:00:00'
  AND create_time <  '2026-08-21 00:00:00';

2. 前导模糊查询

WHERE username LIKE '%zhang%'

开头是 % 时，MySQL 无法从 B+Tree 的有序前缀开始定位。

下面的查询更容易使用索引：

WHERE username LIKE 'zhang%'

3. 隐式类型转换

假设 phone 是 VARCHAR：

WHERE phone = 13800000001

查询值没有加引号，MySQL 可能进行类型转换。

应该写成：

WHERE phone = '13800000001'

字段类型和查询参数类型应保持一致。

4. 联合索引缺少最左字段

索引：

(status, create_time)

查询只有：

WHERE create_time >= '2026-01-01'

不能充分使用这个联合索引。

5. 查询返回的数据比例太高

即使存在索引，MySQL 也可能选择全表扫描。

例如 status 只有两个值，并且大部分记录的状态都是 1：

SELECT *
FROM sys_user
WHERE status = 1;

如果查询要返回整张表的大部分数据，走二级索引后再大量回表，不一定比全表扫描快。

优化器会根据统计信息估算成本，而不是看到索引就一定使用。

6. OR 两侧索引条件不同

WHERE username = 'zhangsan'
   OR email = 'zhangsan@example.com'

如果一个字段有索引，另一个没有，执行计划可能与预期不同。

不能简单记成“OR 一定导致索引失效”。MySQL 也可能使用索引合并，具体仍然要看 EXPLAIN。

九、查看索引

查看表中已有索引：

SHOW INDEX FROM sys_user;

创建普通索引：

CREATE INDEX idx_user_username
ON sys_user(username);

创建唯一索引：

CREATE UNIQUE INDEX uk_user_phone
ON sys_user(phone);

删除索引：

DROP INDEX idx_user_username
ON sys_user;

查看建表语句：

SHOW CREATE TABLE sys_user;

建索引前应先检查已有索引，避免创建重复或包含关系明显的索引。

例如已经有：

(status, create_time)

又单独创建：

(status)

后一个索引可能是冗余的，因为联合索引已经能支持以 status 为最左前缀的查询。

但是否删除仍要结合索引大小、查询频率和执行计划判断。

十、EXPLAIN 的基本使用

在查询语句前添加 EXPLAIN：

EXPLAIN
SELECT *
FROM sys_user
WHERE username = 'zhangsan';

常见输出列包括：

id
select_type
table
partitions
type
possible_keys
key
key_len
ref
rows
filtered
Extra

不需要一开始把所有列都记住。排查普通单表查询时，可以先看：

type
possible_keys
key
rows
filtered
Extra

十一、type

type 表示 MySQL 访问数据的大致方式。

常见值从较好到较差可以记为：

system
const
eq_ref
ref
range
index
ALL

这只是大致顺序，不代表看到某个类型就能直接判断 SQL 好坏。

const

通过主键或唯一索引等值查询，最多匹配一行：

SELECT *
FROM sys_user
WHERE id = 1;

eq_ref

多表连接时，被连接表通过主键或唯一索引匹配一行。

ref

使用普通索引进行等值查询，可能匹配多行：

SELECT *
FROM sys_user
WHERE status = 1;

range

使用索引进行范围扫描：

SELECT *
FROM sys_user
WHERE id BETWEEN 100 AND 200;

index

扫描整个索引。

它不等于高效，只是扫描的是索引结构，而不是直接扫描整张数据表。

ALL

全表扫描。

小表出现 ALL 不一定需要处理。数据量较大、查询频率较高、过滤比例较小时，才需要重点检查。

十二、possible_keys 和 key

possible_keys 表示优化器认为可能使用的索引。

key 表示最终实际选择的索引。

可能出现：

possible_keys: idx_user_status
key: NULL

这不一定表示优化器出错。可能是因为：

表中数据很少；
条件返回的数据太多；
使用索引后需要大量回表；
优化器估算全表扫描成本更低；
统计信息不准确。

不能只看有没有索引，还要看最终是否使用，以及为什么没有使用。

十三、key_len

key_len 表示执行计划预计使用的索引键长度。

联合索引中，可以通过 key_len 辅助判断使用到了多少个字段。

但长度计算与字段类型、字符集、是否允许 NULL 等因素有关，不适合只背固定数字。

实际排查时可以做对比：

EXPLAIN
SELECT *
FROM sys_user
WHERE status = 1;

再执行：

EXPLAIN
SELECT *
FROM sys_user
WHERE status = 1
  AND create_time >= '2026-01-01';

观察两个执行计划中的 key_len 是否变化。

十四、rows 和 filtered

rows 是优化器估算需要检查的行数，不是 SQL 真正执行后精确扫描的行数。

通常情况下，rows 越小，说明预计需要检查的数据越少。

filtered 表示经过表条件过滤后，预计保留的数据比例。

例如：

rows = 10000
filtered = 10

可以粗略理解为读取约 10000 行后，预计有 10% 能通过当前表的过滤条件。

这些值来自统计信息和成本估算，不能当成真实运行结果。

MySQL 8.0 可以使用：

EXPLAIN ANALYZE
SELECT *
FROM sys_user
WHERE status = 1;

它会真正执行查询，并给出实际执行时间、实际行数和循环次数。因为查询会执行，分析更新或大查询时需要谨慎。

十五、Extra

Extra 会显示一些附加执行信息。

Using index

表示查询需要的列可以直接从索引获取，通常是覆盖索引。

Using where

读取数据后还需要按照 WHERE 条件过滤。

它不代表 SQL 一定很慢，普通查询中经常出现。

Using filesort

MySQL 需要额外排序，不能直接利用当前索引顺序得到结果。

ORDER BY create_time DESC

不一定真的使用磁盘文件排序，也可能在内存中完成。名字保留了历史叫法。

Using temporary

查询过程中使用了临时表，常见于某些 GROUP BY、DISTINCT 或排序场景。

Using index condition

表示使用了索引条件下推。部分条件在存储引擎读取索引时提前过滤，可以减少回表。

看到 Using filesort 或 Using temporary 时，需要结合数据量和查询频率判断，不是出现后就一定要修改 SQL。

十六、用 EXPLAIN 对比一条查询

创建表：

CREATE TABLE sys_user (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    status TINYINT NOT NULL,
    create_time DATETIME NOT NULL
);

查询：

EXPLAIN
SELECT id, username, create_time
FROM sys_user
WHERE status = 1
ORDER BY create_time DESC
LIMIT 20;

如果没有索引，可能出现：

type: ALL
key: NULL
Extra: Using where; Using filesort

根据查询条件和排序建立联合索引：

CREATE INDEX idx_user_status_time
ON sys_user(status, create_time);

再次执行相同的 EXPLAIN。

可能看到：

key: idx_user_status_time
type: ref

排序也可能利用索引顺序。

这里不能预先保证执行计划一定变成某个固定结果。表的数据量、字段分布、查询列和 MySQL 版本都会影响优化器选择。

正确流程是：

记录原执行计划
创建或调整索引
重新执行 EXPLAIN
对比 key、rows 和 Extra
再测试实际耗时

十七、索引字段顺序怎么确定

假设常见查询是：

SELECT
    id,
    order_no,
    total_amount
FROM rental_order
WHERE user_id = ?
  AND status = ?
ORDER BY create_time DESC
LIMIT 20;

可以考虑：

CREATE INDEX idx_order_user_status_time
ON rental_order(
    user_id,
    status,
    create_time
);

考虑顺序时主要看：

哪些条件总是出现；
哪些是等值查询；
哪些是范围查询；
是否需要排序；
字段区分度；
查询返回哪些列；
其他 SQL 能否复用这个索引。

没有一条固定规则能代替实际执行计划。

“区分度高的字段一定放前面”也不是绝对的。业务查询通常先按照固定前缀过滤时，索引顺序仍然要服务于完整查询模式。

十八、事务的基本使用

事务用于把多条 SQL 作为一个整体执行。

START TRANSACTION;

UPDATE account
SET balance = balance - 100
WHERE id = 1;

UPDATE account
SET balance = balance + 100
WHERE id = 2;

COMMIT;

如果第二条 SQL 失败，可以回滚：

ROLLBACK;

自动提交状态：

SELECT @@autocommit;

关闭当前会话自动提交：

SET autocommit = 0;

手动事务中，如果既没有 COMMIT，也没有 ROLLBACK，事务可能持续占用锁和连接。

十九、事务的 ACID

原子性 Atomicity

事务中的操作要么全部成功，要么全部失败。

转账时不能只扣款，不加款。

一致性 Consistency

事务执行前后，数据需要满足约束和业务规则。

例如总金额不能无故变化，外键和唯一约束不能被破坏。

一致性是最终目标，原子性、隔离性和持久性共同帮助实现一致性。

隔离性 Isolation

多个事务同时执行时，彼此之间的操作需要受到隔离。

持久性 Durability

事务提交后，结果需要被持久保存。即使数据库进程异常退出，已提交数据也应该能够恢复。

二十、并发事务中的问题

1. 脏读

一个事务读取到另一个事务尚未提交的数据。

事务 A：

UPDATE account
SET balance = 0
WHERE id = 1;

事务 A 还没有提交。

事务 B 此时读取到余额为 0。随后事务 A 回滚，事务 B 之前读取的值就是脏数据。

2. 不可重复读

同一事务内，两次读取同一行，结果不同。

事务 A 第一次查询余额为 1000。

事务 B 修改余额为 500 并提交。

事务 A 再次查询得到 500。

3. 幻读

同一事务中，按照同一条件查询，返回的记录数量发生变化。

事务 A：

SELECT *
FROM sys_user
WHERE status = 1;

事务 B 插入一条 status = 1 的数据并提交。

事务 A 再执行相同条件查询，可能看到新增记录。

二十一、事务隔离级别

查看当前隔离级别：

SELECT @@transaction_isolation;

MySQL 常见隔离级别有四种。

READ UNCOMMITTED

读未提交。

可以读取其他事务尚未提交的数据，可能出现脏读。

READ COMMITTED

读已提交。

只能读取其他事务已经提交的数据，可以避免脏读。但同一事务内重复读取，结果可能变化。

REPEATABLE READ

可重复读。

InnoDB 默认隔离级别通常是可重复读。同一事务中普通快照读能够保持一致视图。

SERIALIZABLE

串行化。

隔离程度最高，并发能力最低。事务之间更接近串行执行。

设置当前会话隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL
READ COMMITTED;

设置全局级别会影响之后新建的连接，需要谨慎。

隔离级别越高，并发性能不一定越好。实际系统需要在一致性和并发能力之间选择。

二十二、快照读和当前读

普通查询通常属于快照读：

SELECT *
FROM sys_user
WHERE id = 1;

快照读通过 MVCC 读取符合当前一致性视图的数据，不一定读取最新版本。

带锁查询属于当前读：

SELECT *
FROM sys_user
WHERE id = 1
FOR UPDATE;

SELECT *
FROM sys_user
WHERE id = 1
FOR SHARE;

UPDATE、DELETE 和 INSERT 也属于当前读，需要基于当前数据版本进行操作。

FOR UPDATE 会对查询到的数据加锁，必须放在事务中使用才有实际意义。

二十三、MVCC

MVCC 是多版本并发控制。

InnoDB 会为记录维护事务相关信息，并通过 Undo Log 保存旧版本数据。普通查询可以按照事务的一致性视图读取适合的版本，不需要所有读操作都阻塞写操作。

MVCC 主要服务于快照读。

下面的查询通常通过 MVCC 获取一致性数据：

SELECT *
FROM sys_user
WHERE id = 1;

下面的查询要求读取当前版本并加锁：

SELECT *
FROM sys_user
WHERE id = 1
FOR UPDATE;

MVCC 的详细可见性判断涉及事务 ID、Read View 和 Undo Log，这部分可以单独继续整理。

二十四、行锁

InnoDB 支持行级锁。

事务 A：

START TRANSACTION;

UPDATE sys_user
SET status = 0
WHERE id = 1;

在事务 A 提交或回滚前，事务 B 修改同一行时可能需要等待：

UPDATE sys_user
SET status = 1
WHERE id = 1;

如果事务 B 修改的是另一行：

UPDATE sys_user
SET status = 1
WHERE id = 2;

通常可以继续执行。

行锁实际加在索引记录上。查询条件没有合适索引时，扫描和加锁范围可能比预期更大。

因此索引不仅影响查询速度，也会影响并发更新中的锁范围。

二十五、间隙锁和临键锁

在 InnoDB 可重复读隔离级别下，范围查询进行当前读时，可能涉及间隙锁或临键锁。

例如：

SELECT *
FROM sys_user
WHERE id BETWEEN 10 AND 20
FOR UPDATE;

除了锁住已有记录，还可能锁住相关索引区间，防止其他事务在范围内插入数据。

临键锁可以理解为：

记录锁 + 间隙锁

这部分行为与隔离级别、查询条件、索引类型以及是否唯一查询有关。

遇到“不同主键的插入为什么也被阻塞”时，需要检查：

当前隔离级别；
SQL 是否是范围查询；
查询使用了哪个索引；
是否存在间隙锁；
事务是否长时间未提交。

二十六、悲观锁

悲观锁假设并发冲突可能发生，所以操作前先锁定数据。

START TRANSACTION;

SELECT balance
FROM account
WHERE id = 1
FOR UPDATE;

UPDATE account
SET balance = balance - 100
WHERE id = 1;

COMMIT;

在事务提交前，其他事务对同一行的冲突更新需要等待。

Java 中的典型流程：

@Transactional
public void deduct(Long accountId,
                   BigDecimal amount) {

    Account account =
            accountMapper.selectByIdForUpdate(
                    accountId
            );

    if (account.getBalance()
            .compareTo(amount) < 0) {
        throw new BusinessException(
                "余额不足"
        );
    }

    accountMapper.deduct(
            accountId,
            amount
    );
}

悲观锁适合冲突较多、操作时间较短且必须严格串行的场景。

事务中不能执行耗时较长的外部请求，否则会长时间占用数据库锁。

二十七、乐观锁

乐观锁不提前锁定数据，而是在更新时检查数据是否仍然是读取时的版本。

表中增加版本号：

ALTER TABLE account
ADD COLUMN version INT NOT NULL DEFAULT 0;

读取：

SELECT id, balance, version
FROM account
WHERE id = 1;

假设读取到：

balance = 1000
version = 3

更新时带上原版本号：

UPDATE account
SET balance = 900,
    version = version + 1
WHERE id = 1
  AND version = 3;

如果受影响行数是 1，说明更新成功。

如果是 0，说明数据已经被其他事务修改，需要重新读取、重试或提示冲突。

乐观锁适合冲突较少的场景。它没有消除冲突，只是通过版本条件检测冲突。

二十八、避免超卖的条件更新

扣减库存时，可以直接把业务条件放入 SQL：

UPDATE product
SET stock = stock - 1
WHERE id = 100
  AND stock > 0;

然后检查受影响行数：

1：扣减成功
0：库存不足或商品不存在

相比先查询库存再更新：

SELECT stock
判断 stock > 0
UPDATE stock

条件更新减少了查询和更新之间的并发空隙。

如果业务流程还涉及订单、支付等多张表，仍然需要继续处理事务、幂等和失败补偿。

二十九、死锁

事务 A：

先锁定记录 1
再等待记录 2

事务 B：

先锁定记录 2
再等待记录 1

两个事务相互等待，形成死锁。

InnoDB 检测到死锁后，会选择回滚其中一个事务，让另一个事务继续执行。

查看最近一次死锁信息：

SHOW ENGINE INNODB STATUS;

减少死锁的方法：

多个事务按照相同顺序访问数据；
缩短事务执行时间；
不在事务中执行不必要的远程调用；
为查询条件建立合适索引；
一次事务不要修改过多数据；
对死锁失败进行有限次数重试。

死锁不能保证完全消失，重点是减少发生概率，并让应用能够处理回滚。

三十、慢查询日志

查看是否开启慢查询：

SHOW VARIABLES LIKE 'slow_query_log';

查看慢查询时间阈值：

SHOW VARIABLES LIKE 'long_query_time';

查看慢查询日志文件：

SHOW VARIABLES LIKE 'slow_query_log_file';

慢查询日志记录执行时间超过阈值的 SQL。

排查流程可以按下面进行：

找到慢 SQL
    ↓
确认参数和执行频率
    ↓
执行 EXPLAIN 或 EXPLAIN ANALYZE
    ↓
检查索引、扫描行数和额外排序
    ↓
修改 SQL 或索引
    ↓
重新测试

不能只看到一条 SQL 执行 500 毫秒就立即增加索引。还需要确认：

SQL 一天执行一次还是每秒执行几百次；
返回了多少数据；
是否存在网络和对象转换耗时；
当前测试数据量是否接近实际环境；
新索引会不会增加大量写入成本。

三十一、ORDER BY 和索引

查询：

SELECT
    id,
    username,
    create_time
FROM sys_user
WHERE status = 1
ORDER BY create_time DESC
LIMIT 20;

建立联合索引：

CREATE INDEX idx_user_status_time
ON sys_user(status, create_time);

status 使用等值条件，后面的 create_time 可以用于有序读取。

但下面的情况可能无法完全利用索引排序：

ORDER BY username, create_time

索引字段顺序和排序要求不匹配。

排序方向、范围条件、多表查询和查询列都会影响执行计划。是否出现 Using filesort 应以实际 EXPLAIN 为准。

三十二、GROUP BY 和临时表

查询每种状态的用户数量：

SELECT
    status,
    COUNT(*) AS user_count
FROM sys_user
GROUP BY status;

如果 status 有合适索引，MySQL 可能利用索引顺序完成分组。

更复杂的分组：

SELECT
    DATE(create_time) AS create_date,
    COUNT(*) AS user_count
FROM sys_user
GROUP BY DATE(create_time);

对字段使用函数后，可能需要临时表或额外排序。

可以考虑增加生成列：

ALTER TABLE sys_user
ADD COLUMN create_date DATE
GENERATED ALWAYS AS (
    DATE(create_time)
) STORED;

再根据实际查询为生成列建立索引。

是否值得这样做，要看统计查询的频率和数据量。低频后台报表不一定需要为了避免临时表增加表结构复杂度。

三十三、深分页

普通分页：

SELECT *
FROM sys_user
ORDER BY id
LIMIT 100000, 20;

MySQL 需要找到并跳过前面的 100000 条，再返回 20 条。偏移量越大，执行成本通常越高。

如果按主键连续翻页，可以记录上一页最后一个 ID：

SELECT *
FROM sys_user
WHERE id > 100000
ORDER BY id
LIMIT 20;

这种方式也叫游标式分页或基于最后值的分页。

它适合：

下一页加载；
时间线；
数据导出；
不要求直接跳到任意页。

如果必须跳到第 5000 页，仍然需要结合业务重新考虑分页方式。

还可以先通过覆盖索引找到主键，再回表：

SELECT u.*
FROM sys_user AS u
INNER JOIN (
    SELECT id
    FROM sys_user
    ORDER BY id
    LIMIT 100000, 20
) AS temp
ON temp.id = u.id;

是否更快需要实际测试，不能只根据写法判断。

三十四、避免 N+1 查询

先查询用户列表：

SELECT *
FROM sys_user
LIMIT 20;

然后循环每个用户查询订单：

SELECT *
FROM rental_order
WHERE user_id = ?;

20 个用户会额外执行 20 条 SQL，一共 21 条。这类情况通常称为 N+1 查询。

可以批量查询：

SELECT *
FROM rental_order
WHERE user_id IN (
    1, 2, 3, 4, 5
);

再在 Java 中按照 user_id 分组。

也可以根据返回结构使用连接查询：

SELECT
    u.id,
    u.username,
    o.id AS order_id,
    o.order_no
FROM sys_user AS u
LEFT JOIN rental_order AS o
    ON o.user_id = u.id
WHERE u.id IN (
    1, 2, 3, 4, 5
);

使用连接时要注意一对多关系会产生重复的用户行，分页也不能直接套在展开后的结果上。

三十五、大批量更新和删除

一次更新大量数据：

UPDATE sys_user
SET status = 0
WHERE create_time < '2020-01-01';

可能带来：

大事务；
大量 Undo Log；
长时间持有锁；
主从复制延迟；
回滚时间很长。

可以根据主键分批处理：

UPDATE sys_user
SET status = 0
WHERE id > 0
  AND id <= 10000
  AND create_time < '2020-01-01';

下一批：

UPDATE sys_user
SET status = 0
WHERE id > 10000
  AND id <= 20000
  AND create_time < '2020-01-01';

分批大小需要根据实际环境调整。执行前要备份或确认恢复方案，并观察锁等待、磁盘和复制状态。

三十六、索引不是越多越好

假设一张表有这些索引：

(username)
(username, status)
(username, status, create_time)

前两个索引可能被后面的联合索引覆盖部分能力，但不能只看字段前缀就直接删除。

还要检查：

每个索引支持哪些查询；
更长的联合索引是否明显增大；
查询是否需要覆盖索引；
写操作是否频繁；
优化器实际选择哪个索引。

可以查看索引信息：

SHOW INDEX FROM sys_user;

索引设计应该服务于具体查询，不是服务于字段本身。

比较适合建立索引的字段通常具有以下特点：

经常出现在 WHERE 中；
经常用于表连接；
经常参与排序或分组；
区分度较高；
查询频率较高。

不适合单独建立索引的情况包括：

表中数据很少；
字段很少用于查询；
字段频繁修改；
字段值重复比例很高；
查询通常返回大部分数据。

这些仍然只是判断方向，最终需要执行计划和实际测试。

三十七、一次完整的 SQL 排查顺序

遇到接口查询慢时，可以先把问题缩小到 SQL。

第一步：拿到真实 SQL

需要包括真实参数，不只看 Mapper 或 Wrapper 代码。

例如：

SELECT
    id,
    username,
    phone,
    create_time
FROM sys_user
WHERE status = 1
  AND username LIKE '张%'
ORDER BY create_time DESC
LIMIT 20;

第二步：确认返回数据量

先执行：

SELECT COUNT(*)
FROM sys_user
WHERE status = 1
  AND username LIKE '张%';

第三步：查看执行计划

EXPLAIN
SELECT
    id,
    username,
    phone,
    create_time
FROM sys_user
WHERE status = 1
  AND username LIKE '张%'
ORDER BY create_time DESC
LIMIT 20;

主要查看：

type
key
rows
filtered
Extra

第四步：检查已有索引

SHOW INDEX FROM sys_user;

第五步：根据查询设计索引

可能考虑：

CREATE INDEX idx_user_status_name_time
ON sys_user(
    status,
    username,
    create_time
);

但 username LIKE '张%' 属于范围形式，后面的排序字段是否能继续利用，需要看实际执行计划。

第六步：重新测试

增加索引后再次执行 EXPLAIN，同时测试真实耗时。

第七步：检查副作用

确认：

插入和更新是否变慢；
索引占用空间是否可接受；
是否与已有索引重复；
其他主要 SQL 是否受到影响。

三十八、这一部分需要记住的内容

索引部分：

主键索引
二级索引
回表
覆盖索引
联合索引
最左前缀
索引失效或未被选择的情况

执行计划部分：

type
possible_keys
key
key_len
rows
filtered
Extra

事务部分：

ACID
隔离级别
脏读
不可重复读
幻读
MVCC

锁部分：

行锁
间隙锁
临键锁
悲观锁
乐观锁
死锁

目前比较重要的不是记住所有名词，而是形成固定的排查方式：

先确认 SQL 和参数
再看 EXPLAIN
检查索引是否合适
修改后重新验证
事务问题检查隔离级别和锁
并发修改检查受影响行数

MySQL 会根据统计信息和成本选择执行计划，所以很多规则都不能写成“只要这样就一定走索引”。

索引是否生效、排序是否使用索引、查询是否发生回表，最后都要以实际执行计划为准。

文章标签： MySQL、索引、EXPLAIN、事务、锁、SQL优化、学习笔记

2048 AI社区

有“AI”的1024 = 2048，欢迎大家加入2048 AI社区

更多推荐

出海企业如何依托拓氪科技AIGEO布局AI采购流量入口？

2048 AI社区

2026年AI写作辅助平台推荐：9款实用AI工具终极合辑

2048 AI社区

你的论文被“误判”成AI写的？别急着改，先看懂aigc检测的“潜规则”

2048 AI社区

所有评论(0)

查看更多评论

2401_83011856

@2401_83011856

已为社区贡献1条内容