Mysql-sql语句优化指北

-- 开启慢查询日志（会话级演示，生产建议写进配置文件）
SET GLOBAL slow_query_log = ON;
SET GLOBAL long_query_time = 1;             -- 超过 1 秒记录
SET GLOBAL log_output = 'FILE';             -- 也可设 TABLE，落到 mysql.slow_log
SET GLOBAL log_queries_not_using_indexes = ON;  -- 记录未走索引的 SQL（线上慎开，日志量爆炸）
SHOW VARIABLES LIKE 'slow_query_log%';
SHOW VARIABLES LIKE 'long_query_time';

# 按总耗时排序，取最慢的 10 条
mysqldumpslow -s t -t 10 /var/lib/mysql/slow.log
# 按出现次数排序
mysqldumpslow -s c -t 10 /var/lib/mysql/slow.log

SHOW FULL PROCESSLIST;
SELECT * FROM information_schema.processlist WHERE command <> 'Sleep' ORDER BY time DESC;

EXPLAIN SELECT ...\G
EXPLAIN FORMAT=TREE SELECT ...\G      -- 看 join 顺序、是否 hash join
EXPLAIN ANALYZE SELECT ...\G          -- 8.0.18+，返回真实耗时与真实行数

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge
       > unique_subquery > index_subquery > range > index > ALL

CREATE TABLE t_user (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  phone       VARCHAR(20)     NOT NULL,
  nickname    VARCHAR(64)     NOT NULL DEFAULT '',
  city        VARCHAR(32)     NOT NULL DEFAULT '',
  status      TINYINT         NOT NULL DEFAULT 1,
  create_time DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_phone (phone),
  KEY idx_city_status (city, status)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
​
CREATE TABLE t_order (
  id          BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  user_id     BIGINT UNSIGNED NOT NULL,
  order_no    VARCHAR(32)     NOT NULL,
  amount      DECIMAL(12,2)   NOT NULL DEFAULT 0.00,
  status      TINYINT         NOT NULL DEFAULT 0,
  create_time DATETIME        NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (id),
  UNIQUE KEY uk_order_no (order_no),
  KEY idx_user_status_time (user_id, status, create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

-- ❌ 1000 次网络往返 + 1000 次 SQL 解析
INSERT INTO t_user (phone, nickname) VALUES ('13800000001', 'a');
INSERT INTO t_user (phone, nickname) VALUES ('13800000002', 'b');
-- ... 重复 1000 次
​
-- ✅ 1 次往返
INSERT INTO t_user (phone, nickname) VALUES
  ('13800000001', 'a'),
  ('13800000002', 'b'),
  ('13800000003', 'c');

-- 方式一：关闭自动提交
SET autocommit = 0;
-- ... 批量 INSERT ...
COMMIT;
​
-- 方式二：显式事务（推荐，语义清晰）
START TRANSACTION;
-- ... 批量 INSERT ...
COMMIT;

-- 8.0：有序 UUID（交换时间低位，使 UUID 变得单调递增），存 BINARY(16) 而不是 CHAR(36)
ALTER TABLE t_user ADD COLUMN uid BINARY(16) NULL, ADD UNIQUE KEY uk_uid (uid);
INSERT INTO t_user (uid) VALUES (UUID_TO_BIN(UUID(), 1));
SELECT BIN_TO_UUID(uid, 1) FROM t_user;

SET unique_checks = 0;
SET foreign_key_checks = 0;
-- ... 导入 ...
SET unique_checks = 1;
SET foreign_key_checks = 1;

LOAD DATA LOCAL INFILE '/tmp/user.csv'
INTO TABLE t_user
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(phone, nickname, city, @create_time)
SET create_time = STR_TO_DATE(@create_time, '%Y-%m-%d %H:%i:%s');

-- 8.0 内置视图：从未使用过的索引
SELECT * FROM sys.schema_unused_indexes;
-- 冗余索引（是另一个索引的前缀）
SELECT * FROM sys.schema_redundant_indexes;

-- ❌ phone 上没有索引：为了更新 1 行，扫描并锁住整张表
UPDATE t_user SET status = 0 WHERE phone = '13800000000';
​
-- ✅ 先补索引（示例表里 uk_phone 已经存在，这里演示缺失的情况）
ALTER TABLE t_user ADD INDEX idx_phone (phone);

SELECT * FROM performance_schema.data_locks WHERE OBJECT_NAME = 't_user';
SELECT * FROM performance_schema.data_lock_waits;   -- 谁在等谁的锁
SELECT * FROM sys.innodb_lock_waits;                -- 更好读的封装视图

-- ❌ 一次更新 1000 万行：长时间持锁、undo 暴涨、binlog 巨大、主从延迟飙升、还可能撑爆 redo
UPDATE t_order SET status = 2 WHERE status = 1 AND create_time < '2024-01-01';
​
-- ✅ 按唯一列分批，应用层循环直到 affected_rows = 0
UPDATE t_order SET status = 2
WHERE status = 1 AND create_time < '2024-01-01'
ORDER BY id
LIMIT 2000;

-- 应用层循环
SELECT id FROM t_order WHERE status = 1 AND create_time < '2024-01-01' ORDER BY id LIMIT 2000;
UPDATE t_order SET status = 2 WHERE id IN (/* 上一步拿到的主键列表 */);

SHOW ENGINE INNODB STATUS\G   -- 查看 LATEST DETECTED DEADLOCK 段落

-- 找出运行超过 60 秒的事务
SELECT trx_id,
       trx_state,
       trx_mysql_thread_id,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_sec,
       trx_query
FROM information_schema.innodb_trx
WHERE TIMESTAMPDIFF(SECOND, trx_started, NOW()) > 60
ORDER BY duration_sec DESC;

ALTER TABLE t_order ADD INDEX idx_user_status_amount (user_id, status, amount);
​
EXPLAIN SELECT user_id, status, amount
FROM t_order
WHERE user_id = 100 AND status = 1\G
-- Extra: Using index   ← 覆盖索引，无回表

-- 索引 (user_id, status, create_time)，user_id BIGINT NOT NULL(8) / status TINYINT NOT NULL(1) / create_time DATETIME NOT NULL(5)
EXPLAIN SELECT * FROM t_order WHERE user_id = 100\G             -- key_len: 8  → 只用 user_id
EXPLAIN SELECT * FROM t_order WHERE user_id = 100 AND status = 1\G  -- key_len: 9  → 用 user_id + status

-- 索引 (zipcode, lastname, firstname)
EXPLAIN SELECT * FROM people
WHERE zipcode = '95054'
  AND lastname LIKE '%etrunia%'
  AND address LIKE '%Main Street%'\G
-- Extra: Using index condition  ← ICP 生效，lastname 的判断在引擎层完成

-- 可能被物化成临时表（Extra 出现 Using temporary 或 table=derived2）
SELECT * FROM t_order
WHERE user_id IN (SELECT id FROM t_user WHERE city = '杭州');
​
-- 改写为 JOIN（t_user.id 是主键，不会放大行数，语义等价；否则需要 DISTINCT 去重）
SELECT o.*
FROM t_order o
JOIN t_user u ON o.user_id = u.id
WHERE u.city = '杭州';

-- 无 WHERE：InnoDB 会挑"最小的那个二级索引"来扫描（覆盖索引，不回表）
SELECT COUNT(*) FROM t_order;
​
-- 有 WHERE：按条件选索引，但仍然要扫描全部匹配行
SELECT COUNT(*) FROM t_order WHERE status = 1;
​
-- 只要判断存在，加 LIMIT 1 让优化器提前停止
SELECT 1 FROM t_order WHERE user_id = 100 LIMIT 1;

EXPLAIN FORMAT=TREE
SELECT * FROM t1 JOIN t2 ON t1.c1 = t2.c1\G
-- -> Inner hash join (t2.c1 = t1.c1)
--     -> Table scan on t2
--     -> Hash
--         -> Table scan on t1

-- ❌ t_order.user_id 是 BIGINT，t_user_ext.uid 是 VARCHAR(32)
--    字符串列被转成数字比较 → t_user_ext.uid 的索引失效
SELECT * FROM t_order o JOIN t_user_ext e ON o.user_id = e.uid;
​
-- ✅ 统一为 BIGINT
ALTER TABLE t_user_ext MODIFY uid BIGINT UNSIGNED NOT NULL;

-- 排查库表字符集是否统一
SELECT TABLE_SCHEMA, TABLE_NAME, TABLE_COLLATION
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_COLLATION NOT LIKE 'utf8mb4%';

-- ❌ 条件写在 WHERE：右表为 NULL 的行被过滤掉，LEFT JOIN 语义退化成 INNER JOIN
SELECT u.id, u.nickname, o.order_no
FROM t_user u
LEFT JOIN t_order o ON u.id = o.user_id
WHERE o.status = 1;
​
-- ✅ 条件写在 ON：保留"没有匹配订单"的用户
SELECT u.id, u.nickname, o.order_no
FROM t_user u
LEFT JOIN t_order o ON u.id = o.user_id AND o.status = 1;

CREATE TABLE t_user_ext (
  uid   BIGINT UNSIGNED NOT NULL,
  level TINYINT NOT NULL DEFAULT 0,
  PRIMARY KEY (uid)
) ENGINE=InnoDB;
​
-- 慢 SQL
SELECT o.id, o.order_no, o.amount, o.create_time, u.nickname, e.level
FROM t_order o
LEFT JOIN t_user u     ON o.user_id = u.id
LEFT JOIN t_user_ext e ON u.id = e.uid
WHERE o.status = 1
ORDER BY o.create_time DESC
LIMIT 20;

-- EXPLAIN 关键输出（慢）
+----+-------+------+---------------+------+-------------------------------+
| id | table | type | possible_keys | key  | Extra                         |
+----+-------+------+---------------+------+-------------------------------+
|  1 | o     | ALL  | NULL          | NULL | Using where; Using filesort   |
|  1 | u     | ALL  | NULL          | NULL | Using join buffer (hash join) |
|  1 | e     | ALL  | NULL          | NULL | Using join buffer (hash join) |
+----+-------+------+---------------+------+-------------------------------+

-- 补一个"以 status 为最左列、create_time 紧随其后"的索引
ALTER TABLE t_order ADD INDEX idx_status_time (status, create_time);

-- 优化后执行计划（预期）
+----+-------+---------+------------------+-------------------+--------------------------------+
| id | table | type    | possible_keys    | key               | Extra                          |
+----+-------+---------+------------------+-------------------+--------------------------------+
|  1 | o     | ref     | idx_status_time  | idx_status_time   | Using where                    |
|  1 | u     | eq_ref  | PRIMARY          | PRIMARY           | NULL                           |
|  1 | e     | eq_ref  | PRIMARY          | PRIMARY           | NULL                           |
+----+-------+---------+------------------+-------------------+--------------------------------+

-- 1) 完全匹配索引顺序
SELECT * FROM t1 ORDER BY key_part1, key_part2;
​
-- 2) 前导列是等值条件，其余列有序（最常见的可用形态）
SELECT * FROM t1 WHERE key_part1 = 'x' ORDER BY key_part2;
​
-- 3) 范围条件与排序列是同一列
SELECT * FROM t1 WHERE key_part1 > 'x' ORDER BY key_part1;
SELECT * FROM t1 WHERE key_part1 < 'x' ORDER BY key_part1 DESC;
​
-- 4) ORDER BY 未命名索引前导列，但它在 WHERE 中是常量，索引同样可用
SELECT * FROM t1
WHERE key_part1 = 'x' AND key_part2 > 'y'
ORDER BY key_part2;   -- 不必从 key_part1 开始写
​
-- 5) InnoDB 二级索引隐含主键，因此查询主键 + 索引列也能用索引排序
SELECT pk, key_part1, key_part2 FROM t1 ORDER BY key_part1, key_part2;
​
-- 6) 全 DESC 的反向扫描（8.0 显示 Backward index scan）
SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC;

-- 8.0：DESC 不再被忽略，会真正按降序存储
ALTER TABLE t ADD INDEX idx_a_desc_b_asc (a DESC, b ASC);
​
EXPLAIN SELECT * FROM t ORDER BY a DESC, b ASC\G   -- Extra 无 Using filesort
EXPLAIN SELECT * FROM t ORDER BY a ASC, b DESC\G   -- 反向扫描同一索引也可以

"filesort_summary": {
  "rows": 100,
  "examined_rows": 100,
  "number_of_tmp_files": 0,
  "peak_memory_used": 25192,
  "sort_mode": "<sort_key, packed_additional_fields>"
}

-- 现有索引：idx_user_status_time (user_id, status, create_time)
-- ❌ user_id 是等值，但 status 不是，create_time 无法保证有序 → Using filesort
SELECT id, order_no FROM t_order
WHERE user_id = 100
ORDER BY create_time DESC
LIMIT 20;
​
-- ✅ 按"等值列在前、排序列紧随其后"补索引
ALTER TABLE t_order ADD INDEX idx_user_time (user_id, create_time);

-- 索引 (user_id, create_time, amount)
-- ❌ create_time 是范围条件，后面的 amount 无法用于有序定位
SELECT * FROM t_order
WHERE user_id = 100 AND create_time > '2024-01-01'
ORDER BY amount DESC LIMIT 20;
​
-- ✅ 把 amount 提前，让排序列紧跟在等值列 user_id 之后
ALTER TABLE t_order ADD INDEX idx_user_amount (user_id, amount, create_time);

-- ❌ 对百万行排序后才取 10 行
SELECT * FROM t_order WHERE status = 1 ORDER BY create_time DESC LIMIT 10;
​
-- ✅ 若有 idx_status_time (status, create_time)：等值 + 有序，取够即停
ALTER TABLE t_order ADD INDEX idx_status_time (status, create_time);

-- ❌ ORDER BY 与 GROUP BY 表达式不同，必然 filesort
SELECT city, COUNT(*) FROM t_user GROUP BY city ORDER BY COUNT(*) DESC;
​
-- ✅ 把排序键显式对齐到索引列（示例：统一按 city 排序，做业务侧再加工）
SELECT city, COUNT(*) AS cnt FROM t_user GROUP BY city ORDER BY city;

-- ❌ 对全表每一行生成随机数并排序，百万行表上是灾难
SELECT * FROM t_user ORDER BY RAND() LIMIT 10;
​
-- ✅ 方案一：主键连续时，随机一个起点后顺序取
SELECT * FROM t_user
WHERE id >= (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM t_user)))
ORDER BY id
LIMIT 10;
​
-- ✅ 方案二：主键稀疏时，随机取 N 个起点分别取一条，应用层合并
SELECT * FROM t_user WHERE id >= ? ORDER BY id LIMIT 1;

-- 默认优先选择能满足 ORDER BY 的有序索引（有时反而更慢）
SET optimizer_switch = 'prefer_ordering_index=off';

SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10;

-- 第一页
SELECT id, order_no, amount, create_time
FROM t_order
ORDER BY id
LIMIT 10;
​
-- 之后每页，把上一页最后一条的游标值传回来
SELECT id, order_no, amount, create_time
FROM t_order
WHERE id > 1000000            -- 上一页最后一条的 id
ORDER BY id
LIMIT 10;

-- 按创建时间倒序翻页：排序键必须是 (create_time, id) 组合，否则同秒数据会乱序
SELECT id, order_no, amount, create_time
FROM t_order
WHERE (create_time = ? AND id < ?)   -- ? = 上一页最后一条的 create_time / id
   OR create_time < ?
ORDER BY create_time DESC, id DESC
LIMIT 10;

-- ❌ 深分页 + SELECT *：扫描 100 万行且每行都回表
SELECT * FROM t_order ORDER BY id LIMIT 1000000, 10;
​
-- ✅ 延迟关联：内层只在主键上分页（覆盖索引、不回表），外层只回表 10 次
SELECT o.*
FROM t_order o
INNER JOIN (
  SELECT id FROM t_order ORDER BY id LIMIT 1000000, 10
) AS d ON o.id = d.id
ORDER BY o.id;

-- 内层只需 idx_create_time 就能覆盖 (create_time, id)，不回表
SELECT o.*
FROM t_order o
INNER JOIN (
  SELECT id FROM t_order
  ORDER BY create_time DESC, id DESC
  LIMIT 1000000, 10
) AS d ON o.id = d.id
ORDER BY o.create_time DESC, o.id DESC;

ALTER TABLE t_order ADD INDEX idx_status_time_id (status, create_time, id);
​
-- 仍然扫描 100 万行，但全程不回表
SELECT id, status, create_time
FROM t_order
WHERE status = 1
ORDER BY create_time DESC, id DESC
LIMIT 1000000, 10;

-- ❌ create_time 有重复值时，翻页可能重复或漏掉数据
SELECT * FROM t_order ORDER BY create_time DESC LIMIT 0, 10;
SELECT * FROM t_order ORDER BY create_time DESC LIMIT 10, 10;
​
-- ✅ 追加唯一列，构成全序
SELECT * FROM t_order ORDER BY create_time DESC, id DESC LIMIT 10, 10;

-- ❌ 8.0.17 起被标记废弃：为了算总数，必须把全部匹配行都读完，无法提前中止
SELECT SQL_CALC_FOUND_ROWS * FROM t_order WHERE status = 1 LIMIT 20;
SELECT FOUND_ROWS();

-- 取 N+1 条：返回 21 条说明还有下一页，只展示前 20 条
SELECT id, order_no FROM t_order WHERE status = 1 ORDER BY id LIMIT 21;

-- LIMIT 0：快速校验 SQL 语法与结果集元数据（不返回数据）
SELECT id, order_no FROM t_order WHERE status = 1 ORDER BY id DESC LIMIT 0;
-- mysql 客户端还可配合 --column-type-info 查看列类型
​
-- LIMIT 1：只需要"存在性判断"时，让优化器尽早停止
SELECT 1 FROM t_order WHERE user_id = 100 LIMIT 1;

慢 SQL
  │
  ├─ 是写语句？ → 拆批 + 短事务 + 顺序主键 + 控索引数量
  │
  └─ 是读语句？
        │
        ├─ WHERE 走索引了吗？（EXPLAIN key / type）
        │     ├─ 没走 → 按 3.2 清单改写，或补索引
        │     └─ 走了 → 下一步
        │
        ├─ 扫描行数是否远大于返回行数？
        │     ├─ 是 → 索引选择性/最左前缀/深分页问题
        │     └─ 否 → 下一步
        │
        ├─ 有 Using filesort 吗？ → 按第 5 章调整索引列顺序
        │
        ├─ 有多表联查吗？ → 按第 4 章检查被驱动表索引与关联列类型
        │
        └─ 有 LIMIT 深分页吗？ → 按第 6 章改游标分页 / 延迟关联