MySQL 索引实战：从 0 到 1 的系统优化手册

原创 于 2025-09-14 22:52:58 发布 · 764 阅读 · 9 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库

面向 MySQL 8.x 与 InnoDB，引导你用“可复制”的 SQL 示例完成索引设计、验证与排错。

目录

1.索引基础与原则

  • 索引本质：有序数据结构（InnoDB 使用 B+Tree），加速定位，减少 I/O。
  • 聚簇索引：InnoDB 的主键索引即数据本身；二级索引的叶子节点存主键值 → 发生“回表”。
  • 基本原则：
    • 高频过滤列、高选择性列优先建索引。
    • 联合索引遵循最左前缀与等值优先、范围靠后。
    • 读好建索引，写多控制索引数量（写放大）。

2.准备一套可复现的测试环境与数据

下述脚本基于 MySQL 8.x，可直接演示 EXPLAIN/EXPLAIN ANALYZE 及递归 CTE 造数。

-- 环境准备
SET sql_safe_updates = 0;
CREATE DATABASE IF NOT EXISTS idx_lab DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci;
USE idx_lab;

-- 用户表
DROP TABLE IF EXISTS users;
CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(120) NOT NULL,
  phone VARCHAR(20) NOT NULL,
  created_at DATETIME NOT NULL,
  KEY k_name(name),
  UNIQUE KEY uk_email(email)         -- 唯一索引示例
) ENGINE=InnoDB;

-- 订单表
DROP TABLE IF EXISTS orders;
CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  status TINYINT NOT NULL,           -- 0/1/2
  amount DECIMAL(10,2) NOT NULL,
  created_at DATETIME NOT NULL,
  KEY k_user_status_created (user_id, status, created_at)  -- 典型复合索引
) ENGINE=InnoDB;

-- 登录表
DROP TABLE IF EXISTS user_logins;
CREATE TABLE user_logins (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  login_time DATETIME NOT NULL,
  KEY k_user_time (user_id, login_time)                    -- 覆盖索引候选
) ENGINE=InnoDB;

-- 使用递归 CTE 快速造 10 万数据
WITH RECURSIVE seq(n) AS (
  SELECT 1 UNION ALL SELECT n+1 FROM seq WHERE n < 100000
)
INSERT INTO users(name, email, phone, created_at)
SELECT CONCAT('user_', n),
       CONCAT('user_', n, '@demo.com'),
       CONCAT('1380013', LPAD(n % 10000, 4, '0')),
       TIMESTAMP(DATE('2025-01-01') + INTERVAL (n % 200) DAY, SEC_TO_TIME(n % 86400))
FROM seq;

-- 造 20 万订单（每个用户 2 条左右）
WITH RECURSIVE seq2(n) AS (
  SELECT 1 UNION ALL SELECT n+1 FROM seq2 WHERE n < 200000
)
INSERT INTO orders(user_id, status, amount, created_at)
SELECT (n % 100000) + 1,
       (n % 3),
       (n % 10000) / 10.0,
       TIMESTAMP(DATE('2025-03-01') + INTERVAL (n % 60) DAY, SEC_TO_TIME(n % 86400))
FROM seq2;

-- 造登录记录
WITH RECURSIVE seq3(n) AS (
  SELECT 1 UNION ALL SELECT n+1 FROM seq3 WHERE n < 200000
)
INSERT INTO user_logins(user_id, login_time)
SELECT (n % 100000) + 1,
       TIMESTAMP(DATE('2025-04-01') + INTERVAL (n % 30) DAY, SEC_TO_TIME(n % 86400))
FROM seq3;

ANALYZE TABLE users, orders, user_logins;

单列/唯一/前缀/函数/联合索引实战

1) 单列与唯一

-- 单列：name 已有 k_name
SELECT * FROM users WHERE name='user_8888';

-- 唯一：email 已有 uk_email
SELECT id FROM users WHERE email='user_8888@demo.com';

EXPLAIN ANALYZE SELECT id FROM users WHERE email='user_8888@demo.com';

2) 前缀索引（长文本/URL 场景）

-- 长 email/URL 可以用前缀索引降低大小
ALTER TABLE users ADD KEY k_email_prefix (email(20));
-- 注意：只有前缀参与比较时能完全利用此索引（覆盖索引能力受限）

3) 函数索引（MySQL 8+ 支持）

避免对列做函数导致“不可 SARG 化”（索引失效）。

-- 例：对低大小写敏感查询
ALTER TABLE users ADD KEY k_lower_name ((LOWER(name)));

-- 写法对比
-- 坏：WHERE LOWER(name)='tom'  （若无函数索引通常无法用 k_name）
-- 好：WHERE name='Tom' 或使用函数索引：WHERE LOWER(name)='tom'

4) 联合索引（最左前缀 & 范围靠后）

-- 已有：orders(user_id, status, created_at)
-- 典型查询 1：等值+排序 分页
EXPLAIN ANALYZE
SELECT id, amount, created_at
FROM orders
WHERE user_id=12345 AND status=1
ORDER BY created_at DESC
LIMIT 20;

-- 典型查询 2：仅用到后缀列可能无法覆盖（MySQL 8 某些场景存在 Skip Scan，但不应依赖）
EXPLAIN SELECT * FROM orders WHERE status=1;  -- 一般不会选用 k_user_status_created

经验：将过滤最强的列放最左，范围查询（> < BETWEEN）尽量靠后；排序列紧随其后以避免 filesort。

覆盖索引与回表

  • 覆盖索引：所需列全在索引里，执行计划 Extra 显示 Using index，无需回表。
  • 回表：二级索引命中后需再用主键回到聚簇索引取剩余列。
-- 覆盖索引示例（user_logins 上有 k_user_time(user_id, login_time)）
EXPLAIN ANALYZE
SELECT user_id, MAX(login_time) AS last_login
FROM user_logins
GROUP BY user_id;

若只查 user_id, login_time，可最大化覆盖；若还查其它列可能触发回表。

排序、分页与“文件排序”优化

1) 避免 filesort：索引顺序 = where顺序 + order by 顺序（方向尽量相同）

-- 已有复合索引 (user_id, status, created_at)
-- ORDER BY 与索引后缀对齐，通常可避免 Using filesort
EXPLAIN
SELECT id, amount
FROM orders
WHERE user_id=12345 AND status=1
ORDER BY created_at DESC
LIMIT 20;

2) 大偏移分页：用“Seek Method”替代 OFFSET

-- 慢：LIMIT 20 OFFSET 100000
SELECT id, amount FROM orders
WHERE user_id=12345 AND status=1
ORDER BY created_at DESC
LIMIT 20 OFFSET 100000;

-- 快：基于“上次游标”
-- 假设上页最后一条 (created_at, id) = ('2025-05-01 12:00:00', 998877)
SELECT id, amount, created_at
FROM orders
WHERE user_id=12345 AND status=1
  AND (created_at, id) < ('2025-05-01 12:00:00', 998877)
ORDER BY created_at DESC, id DESC
LIMIT 20;

-- 关键：为 (user_id, status, created_at, id) 建联合索引或在现有索引后补 id
ALTER TABLE orders DROP KEY k_user_status_created,
ADD KEY k_user_status_created_id (user_id, status, created_at, id);

常见“索引失效”场景与改写

1.前导 % 的 LIKE

-- 失效：无法用 B-Tree
SELECT * FROM users WHERE name LIKE '%Tom';

-- 改写 A：仅后缀通配（可走索引）
SELECT * FROM users WHERE name LIKE 'Tom%';

-- 改写 B：反转列 + 生成列 + 索引，支持“后缀匹配”
ALTER TABLE users
  ADD COLUMN name_rev VARCHAR(50) GENERATED ALWAYS AS (REVERSE(name)) STORED,
  ADD KEY k_name_rev (name_rev);
-- 查询以 '...Tom' 结尾
SELECT * FROM users WHERE name_rev LIKE REVERSE('Tom%');

2.对列做函数/表达式

-- 失效：YEAR(created_at)=2025
SELECT * FROM orders WHERE YEAR(created_at)=2025;

-- 改写（范围谓词可走索引）
SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

3.隐式类型转换

-- 失效风险：phone 为 VARCHAR
SELECT * FROM users WHERE phone = 13800138000;

-- 正确：显式匹配
SELECT * FROM users WHERE phone = '13800138000';

4.范围列放前导致后缀列失效

-- 索引 (a, b)
-- 条件 a BETWEEN ... AND ... AND b=... 时，b 往往难以利用
-- 解决：调整列序或改写查询。

5.OR 条件跨不同列

-- 可能全表：WHERE a=... OR b=...
-- 改写为 UNION（让优化器分别用各自索引），再去重/合并
SELECT ... FROM t WHERE a=...
UNION ALL
SELECT ... FROM t WHERE b=...;

选择性、统计信息与执行计划

1) 选择性与基数

-- 查看索引信息
SHOW INDEX FROM orders;

-- 更新统计信息
ANALYZE TABLE orders;

-- 直方图辅助选择（MySQL 8）
ANALYZE TABLE orders
  UPDATE HISTOGRAM ON status WITH 32 BUCKETS;

EXPLAIN / EXPLAIN ANALYZE / JSON

EXPLAIN SELECT * FROM orders WHERE user_id=1 AND status=1;

EXPLAIN ANALYZE
SELECT id FROM users WHERE email='user_999@demo.com';

EXPLAIN FORMAT=JSON
SELECT id FROM orders WHERE user_id=1 ORDER BY created_at DESC LIMIT 10;

关注：type（const/ref/range/index/ALL）、key、rows、filtered、Extra（Using index / Using filesort / Using temporary）。

维护与排查：冗余索引/未使用索引/慢日志

-- sys 库：找潜在冗余索引（不同版本视图名可能略有差异）
SELECT * FROM sys.schema_redundant_indexes LIMIT 50;

-- 找“可能未使用”的索引（仅供参考，短期业务可能没覆盖到）
SELECT * FROM sys.schema_unused_indexes LIMIT 50;

-- 观察索引使用等待
SELECT OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME, COUNT_READ, COUNT_WRITE
FROM performance_schema.table_io_waits_summary_by_index_usage
ORDER BY (COUNT_READ+COUNT_WRITE) DESC
LIMIT 20;

-- 打开慢日志（需有权限）
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5;   -- 超过 0.5s 记慢日志

FAQ：中文全文检索、JSON、OR 条件等

Q1：中文模糊搜索怎么做？

  • InnoDB 支持 FULLTEXT + WITH PARSER ngram：
CREATE TABLE articles(
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  title VARCHAR(200),
  content TEXT,
  FULLTEXT KEY ft_content (content) WITH PARSER ngram
) ENGINE=InnoDB;

SELECT id, MATCH(content) AGAINST('索引 优化' IN NATURAL LANGUAGE MODE) score
FROM articles
WHERE MATCH(content) AGAINST('索引 优化' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC;

Q2：JSON 字段可建索引吗？

  • 通过生成列+ 索引：
ALTER TABLE orders
 ADD COLUMN ext_json JSON,
 ADD COLUMN ext_region VARCHAR(10) GENERATED ALWAYS AS (JSON_UNQUOTE(JSON_EXTRACT(ext_json, '$.region'))) STORED,
 ADD KEY k_ext_region(ext_region);

SELECT * FROM orders WHERE ext_region='SG';

Q3：OR 条件索引不生效怎么办？

  • 拆为 UNION ALL 并各自命中索引；或改写为 IN (…)；或使用分区/物化视图（逻辑层）等

结语

  • 索引是“设计问题”，不是“语法问题”：围绕核心业务查询路径，建立能够“过滤 + 排序 + 覆盖”的最小索引集。
  • 工具方面：EXPLAIN ANALYZE + 慢日志 + sys/performance_schema 已足够覆盖 90% 的排查工作。
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

枫叶v.

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值