-- 查看 Buffer Pool 命中率 SHOW STATUS LIKE'Innodb_buffer_pool_read_requests'; SHOW STATUS LIKE'Innodb_buffer_pool_reads'; -- 命中率 = (read_requests - reads) / read_requests * 100%，应 > 99%
-- 创建联合索引 CREATE INDEX idx_a_b_c ON users(a, b, c);
-- ✅ 走索引 (匹配 a) SELECT*FROM users WHERE a =1AND b =2AND c =3;
-- ✅ 走索引 (匹配 a, b，跳过 c 部分因为范围查询) SELECT*FROM users WHERE a =1AND b >2ORDERBY c;
-- ✅ 走索引 (匹配 a) SELECT*FROM users WHERE a =1ORDERBY b;
-- ❌ 不走索引 (没有从 a 开始) SELECT*FROM users WHERE b =2AND c =3;
-- ❌ 不走索引 (没有 a) SELECT*FROM users WHERE c =3;
-- ⚠️ 部分走索引 (a 走索引，c 不走 — 跳过了 b 导致断档) SELECT*FROM users WHERE a =1AND c =3;
2.3 索引字段顺序设计
高选择性原则：区分度大的列放在最前面
1 2 3 4 5 6 7 8 9 10
-- 计算区分度 SELECT COUNT(DISTINCT status) /COUNT(*) AS status_selectivity, -- 假设 0.001 (太低) COUNT(DISTINCT user_id) /COUNT(*) AS user_id_selectivity, -- 假设 0.8 (高) COUNT(DISTINCT created_at) /COUNT(*) AS time_selectivity -- 假设 0.95 (最高) FROM orders;
-- 建议顺序：WHERE 条件中频繁出现且区分度高的列放在前面 -- 如果查询模型是：WHERE user_id = ? AND status = ? AND created_at BETWEEN ? AND ? -- 最佳索引：idx_user_status_time (user_id, status, created_at)
等值查询优先于范围查询
1 2 3 4 5 6 7
-- ❌ 差：范围条件在中间，后面的列用不上 INDEX idx_a_b_c (a, created_at, b, c)
-- ✅ 好：等值条件在前，范围条件在最后 INDEX idx_a_b_c (a, b, created_at, c) -- WHERE a = 1 AND b = 2 AND created_at > '2024-01-01' -- → 可以用到 a, b, created_at 三列
2.4 覆盖索引
避免回表查询，将所需列全部包含在索引中。
1 2 3 4 5 6
-- ❌ 需要回表：索引只有 user_id，查 name 和 email 需要回表 SELECT name, email FROM users WHERE user_id =100;
-- ✅ 覆盖索引：索引包含所有需要的列 CREATE INDEX idx_user_cover ON users(user_id, name, email); -- Extra: Using index （不需要回表）
-- ❌ 4. LIKE 以 % 开头 SELECT*FROM users WHERE name LIKE'%张三'; -- ✅ 必要时用全文索引，或反向 LIKE 有时可改写为： SELECT*FROM users WHERE name LIKE'张三%'; -- 走索引
-- ❌ 5. OR 条件不全是索引列 SELECT*FROM users WHERE id =1OR name ='张三'; -- name 无索引 → 全表扫描 -- ✅ 改成 UNION ALL SELECT*FROM users WHERE id =1 UNIONALL SELECT*FROM users WHERE name ='张三';
-- ❌ 6. NOT IN / NOT EXISTS / != / <> 在大范围时可能不走索引 -- 优化器认为需要扫描大量数据时，会选择全表扫描 -- ✅ 能改写为 IN / EXISTS 则改写，或用范围查询替代
-- ❌ 7. IS NULL / IS NOT NULL 在稀疏索引上效率低 -- ✅ 设置默认值，避免 NULL
-- ❌ 8. 联合索引不满足最左前缀 INDEX idx_a_b_c (a, b, c) SELECT*FROM t WHERE b =2; -- 不走索引
-- ❌ 9. ORDER BY + WHERE 列不是同一索引 INDEX idx_a (a) SELECT*FROM t WHERE a =1ORDERBY b; -- filesort -- ✅ 创建 (a, b) 联合索引
-- ✅ 只取需要的列 SELECT id, name, email FROM users WHERE id =1;
SELECT * 的危害：
返回不需要的列 → 网络传输变大
无法使用覆盖索引 → 必须回表
表结构变更可能破坏调用方
大字段（TEXT/BLOB）白白传输
3.2 WHERE 条件优化
1 2 3 4 5 6 7 8 9 10 11 12 13
-- ❌ 复杂 OR 条件 SELECT*FROM orders WHERE (status ='paid'AND amount >100) OR (status ='pending'AND created_at >'2024-01-01');
-- ✅ 能合并则合并，不能则拆分为 UNION SELECT*FROM orders WHERE status ='paid'AND amount >100 UNIONALL SELECT*FROM orders WHERE status ='pending'AND created_at >'2024-01-01';
-- ❌ 负向条件 SELECT*FROM orders WHERE status !='cancelled'; -- ✅ 用 IN（如果状态值有限） SELECT*FROM orders WHERE status IN ('pending', 'paid', 'shipped');
3.3 COUNT 优化
1 2 3 4 5 6 7 8 9 10 11 12 13
-- ❌ COUNT(column) — 不统计 NULL SELECTCOUNT(name) FROM users;
-- ✅ COUNT(*) / COUNT(1) — 效率相同，统计所有行 SELECTCOUNT(*) FROM users;
-- 近似计数（大数据量，不需要精确值） -- MySQL: SHOW TABLE STATUS LIKE 'users'; -- 查看 Rows 列，近似值 -- EXPLAIN SELECT COUNT(*) FROM users; -- 查看 rows 列，估算值
-- 精确但高效（利用索引） SELECTCOUNT(*) FROM users WHERE status ='active'; -- 确保 status 有索引
3.4 IN vs EXISTS 选择
1 2 3 4 5 6 7 8 9 10 11
-- 外表小、子表大 → 用 EXISTS SELECT*FROM orders o WHEREEXISTS ( SELECT1FROM users u WHERE u.id = o.user_id AND u.vip =1 );
-- 子表结果集小 → 用 IN SELECT*FROM orders WHERE user_id IN (SELECT id FROM users WHERE vip =1);
-- MySQL 8.0+ 优化器已能自动转换，但显式写出仍有帮助
3.5 UNION vs UNION ALL
1 2 3 4 5 6 7 8 9 10
-- ❌ UNION — 默认去重，需要额外排序 SELECT name FROM users_2023 UNION SELECT name FROM users_2024;
-- ✅ UNION ALL — 不去重，性能更高 -- 如果确定无重复（如分区表不同年份），使用 UNION ALL SELECT name FROM users_2023 UNIONALL SELECT name FROM users_2024;
四、表结构设计优化
4.1 数据类型选择
场景
❌ 避免
✅ 推荐
理由
IP 地址
VARCHAR(15)
INT UNSIGNED (4字节) / INET_ATON()
更小更快
日期时间
VARCHAR
DATETIME(8字节) / TIMESTAMP(4字节)
原生类型支持函数
枚举状态
VARCHAR(20)
TINYINT / ENUM
1字节 vs 20+字节
UUID 主键
VARCHAR(36)
BINARY(16)
16字节 vs 36字节
布尔值
CHAR(1)
TINYINT(1)
1字节
货币金额
FLOAT/DOUBLE
DECIMAL(18,2) / BIGINT(分)
避免浮点精度问题
长文本
所有都用 TEXT
VARCHAR 优先 (< 65535)，必要时才用 TEXT
TEXT 不能有默认值，回表开销大
手机号
BIGINT
CHAR(11)
手机号不应做运算
4.2 字段设计规范
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
-- ✅ 好的表设计 CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键', order_no CHAR(20) NOT NULL COMMENT '订单号', user_id BIGINT UNSIGNED NOT NULL COMMENT '用户ID', amount DECIMAL(12,2) NOT NULLDEFAULT0.00 COMMENT '金额', status TINYINT UNSIGNED NOT NULLDEFAULT0 COMMENT '0待支付 1已支付 2已发货 3已完成 4已取消', source TINYINT UNSIGNED NOT NULLDEFAULT0 COMMENT '0PC 1H5 2小程序 3APP', created_at DATETIME NOT NULLDEFAULTCURRENT_TIMESTAMP COMMENT '创建时间', updated_at DATETIME NOT NULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP COMMENT '更新时间', PRIMARY KEY (id), UNIQUE KEY uk_order_no (order_no), KEY idx_user_status_time (user_id, status, created_at), KEY idx_created_at (created_at) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单表';
-- 主表（频繁访问） CREATE TABLE articles ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, title VARCHAR(200) NOT NULL, author_id BIGINT UNSIGNED NOT NULL, summary VARCHAR(500), status TINYINT NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id), KEY idx_author (author_id) ) ENGINE=InnoDB;
-- 扩展表（仅查看详情时 JOIN） CREATE TABLE article_contents ( article_id BIGINT UNSIGNED NOT NULLPRIMARY KEY, content MEDIUMTEXT NOT NULL, -- 大字段 images JSON, -- MySQL 5.7+ attachments TEXT ) ENGINE=InnoDB;
-- Buffer Pool 命中率（应 ≥ 99%） SELECT (1- (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME ='Innodb_buffer_pool_reads') / (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME ='Innodb_buffer_pool_read_requests') ) *100AS buffer_pool_hit_rate;
-- Buffer Pool 使用率 SELECT (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME ='Innodb_buffer_pool_pages_data') / (SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME ='Innodb_buffer_pool_pages_total') *100AS buffer_pool_usage;
-- ❌ 大表驱动小表 SELECT*FROM big_table b JOIN small_table s ON b.id = s.big_id;
-- ✅ 小表驱动大表（或使用 STRAIGHT_JOIN 强制） SELECT*FROM small_table s STRAIGHT_JOIN big_table b ON s.big_id = b.id;
-- 原则 2：被驱动表的 JOIN 列必须有索引 -- idx_orders_user_id ON orders(user_id) SELECT*FROM users u JOIN orders o ON u.id = o.user_id -- o.user_id 必须有索引 WHERE u.status =1;
-- 原则 3：避免笛卡尔积 -- ❌ 少了 ON 条件或条件错误 SELECT*FROM a JOIN b; -- 笛卡尔积！
-- 原则 4：JOIN 字段类型一致（避免隐式转换） -- ❌ users.id 是 BIGINT，orders.user_id 是 VARCHAR SELECT*FROM users u JOIN orders o ON u.id = o.user_id; -- ✅ 统一类型 ALTER TABLE orders MODIFY user_id BIGINT UNSIGNED NOT NULL;
7.3 多表 JOIN 优化
1 2 3 4 5 6 7 8 9 10 11 12
-- 最多 JOIN 3-5 张表，超出建议拆分
-- ❌ 过多 JOIN SELECT*FROM a JOIN b ON ... JOIN c ON ... JOIN d ON ... JOIN e ON ... JOIN f ON ...;
-- ✅ 拆分：先把核心数据查出，再查关联表 -- Step 1: 查主表 SELECT id, name FROM a WHERE ... LIMIT 100; -- Step 2: 应用程序拿到 id 列表，再 IN 查询 b, c SELECT*FROM b WHERE a_id IN (1,2,3,...); SELECT*FROM c WHERE b_id IN (1,2,3,...);
7.4 LEFT JOIN 注意事项
1 2 3 4 5 6 7 8 9 10 11 12
-- LEFT JOIN 时，右表的过滤条件放在 ON 中 vs WHERE 中效果不同
-- 写法 1：条件放在 WHERE（可能把 LEFT JOIN 变成 INNER JOIN） SELECT u.*, o.* FROM users u LEFTJOIN orders o ON u.id = o.user_id WHERE o.status =1; -- ❌ 会过滤掉没有订单的用户
-- 写法 2：条件放在 ON（保留左表所有行） SELECT u.*, o.* FROM users u LEFTJOIN orders o ON u.id = o.user_id AND o.status =1; -- ✅ 正确
八、子查询优化
8.1 子查询改写为 JOIN
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
-- ❌ 子查询（可能产生派生表） SELECT*FROM orders WHERE user_id IN ( SELECT id FROM users WHERE status =1 );
-- ✅ 改写为 JOIN SELECT o.*FROM orders o JOIN users u ON o.user_id = u.id WHERE u.status =1;
-- ✅ 或用 EXISTS SELECT*FROM orders o WHEREEXISTS ( SELECT1FROM users u WHERE u.id = o.user_id AND u.status =1 );
8.2 避免在 SELECT 中使用子查询
1 2 3 4 5 6 7 8 9 10 11
-- ❌ 每行执行一次子查询 SELECT u.id, u.name, (SELECTCOUNT(*) FROM orders o WHERE o.user_id = u.id) AS order_count FROM users u;
-- ✅ 改用 JOIN + GROUP BY SELECT u.id, u.name, COUNT(o.id) AS order_count FROM users u LEFTJOIN orders o ON u.id = o.user_id GROUPBY u.id, u.name;
8.3 NOT IN vs NOT EXISTS 的 NULL 陷阱
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
-- ⚠️ NOT IN + 子查询返回 NULL → 整个条件为 UNKNOWN，返回空！ SELECT*FROM orders WHERE user_id NOTIN ( SELECT id FROM users WHERE deleted_at ISNOT NULL -- 如果 id 中有 NULL，NOT IN 永远返回空！ );
-- ✅ 改用 NOT EXISTS（不受 NULL 影响） SELECT*FROM orders o WHERENOTEXISTS ( SELECT1FROM users u WHERE u.id = o.user_id AND u.deleted_at ISNOT NULL );
-- ✅ 或者子查询中显式排除 NULL SELECT*FROM orders WHERE user_id NOTIN ( SELECT id FROM users WHERE deleted_at ISNOT NULLAND id ISNOT NULL );
-- 场景 1：完全利用索引排序 INDEX idx_user_time (user_id, created_at) SELECT*FROM users WHERE user_id =1 ORDERBY created_at DESC; -- ✅ 使用索引，Extra: 无 filesort
-- 场景 2：WHERE 等值 + ORDER BY 不同方向 INDEX idx_a_b (a, b) SELECT*FROM t WHERE a =1ORDERBY b ASC, c DESC; -- ❌ 混合 ASC/DESC -- 索引只能单一方向 -- MySQL 8.0+ 支持降序索引：INDEX idx_a_b_c (a, b ASC, c DESC)
-- 场景 3：ORDER BY 多个字段但不在同一索引 SELECT*FROM users ORDERBY status, created_at; -- ✅ 建联合索引 CREATE INDEX idx_status_created ON users(status, created_at);
-- 查看当前锁等待 SELECT r.trx_id AS waiting_trx_id, r.trx_mysql_thread_id AS waiting_thread, b.trx_id AS blocking_trx_id, b.trx_mysql_thread_id AS blocking_thread FROM information_schema.innodb_lock_waits w JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id;
-- MySQL 8.0+ 推荐 SELECT*FROM performance_schema.data_lock_waits\G
-- MySQL 分区表（透明，业务代码无感知） -- 按年份范围分区 CREATE TABLE orders ( id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, user_id BIGINT UNSIGNED NOT NULL, amount DECIMAL(12,2) NOT NULL, created_at DATETIME NOT NULL, PRIMARY KEY (id, created_at), -- ⚠️ 分区键必须在主键中 KEY idx_user (user_id) ) ENGINE=InnoDB PARTITIONBYRANGE (YEAR(created_at)) ( PARTITION p2022 VALUES LESS THAN (2023), PARTITION p2023 VALUES LESS THAN (2024), PARTITION p2024 VALUES LESS THAN (2025), PARTITION p2025 VALUES LESS THAN (2026), PARTITION p_future VALUES LESS THAN MAXVALUE );
# Cache-Aside 模式（最常用） defget_user(user_id): # 1. 先查缓存 user = redis.get(f"user:{user_id}") if user: return user # 2. 缓存未命中，查 DB user = db.query("SELECT * FROM users WHERE id = ?", user_id) if user: # 3. 写入缓存，设置过期时间 redis.setex(f"user:{user_id}", 3600, user) return user
defupdate_user(user_id, data): # 1. 更新 DB db.execute("UPDATE users SET name = ? WHERE id = ?", data.name, user_id) # 2. 删除缓存（不是更新缓存！） redis.delete(f"user:{user_id}")
# 缓存穿透：缓存空值 defget_user_with_null_cache(user_id): user = redis.get(f"user:{user_id}") if user isnotNone: return user if user != "NULL"elseNone user = db.query(...) if user: redis.setex(f"user:{user_id}", 3600, user) else: redis.setex(f"user:{user_id}", 60, "NULL") # 空值也缓存，短过期 return user
# 缓存击穿：互斥锁 defget_hot_data(key): data = redis.get(key) if data isNone: # 加锁，只有一个线程查 DB if redis.setnx(f"lock:{key}", 1): try: data = db.query(...) redis.setex(key, 3600, data) finally: redis.delete(f"lock:{key}") else: time.sleep(0.1) data = redis.get(key) return data
-- ❌ SELECT*FROM users WHERE id =1OR id =2OR id =3OR id =4 ...; -- ✅ SELECT*FROM users WHERE id IN (1, 2, 3, 4, ...);
反模式 4：无 LIMIT 的查询
1 2 3 4
-- ❌ 可能返回几百万行 SELECT*FROM orders WHERE status =1; -- ✅ 加 LIMIT，配合分页 SELECT*FROM orders WHERE status =1 LIMIT 100;
反模式 5：过度依赖数据库函数处理
1 2 3 4
-- ❌ 大量数据在 DB 层做字符串拼接、格式化 SELECT CONCAT(u.first_name, ' ', u.last_name, ' - ', u.phone) FROM users u; -- ✅ 在应用层处理格式化，DB 只做数据检索 SELECT u.first_name, u.last_name, u.phone FROM users u;
反模式 6：NULL 值设计问题
1 2 3 4 5 6 7
-- ❌ 到处 NULL 加复杂判断 SELECT*FROM users WHERE deleted_at ISNOT NULLOR status ISNULL;
-- ✅ 合理设默认值 -- deleted_at DATETIME NOT NULL DEFAULT '9999-12-31' -- status TINYINT NOT NULL DEFAULT 0 SELECT*FROM users WHERE deleted_at ='9999-12-31'AND status =0;
反模式 7：用 MySQL 做全文搜索
1 2 3 4 5 6 7
-- ❌ LIKE '%keyword%'（全表扫描，极慢） SELECT*FROM articles WHERE content LIKE'%关键词%';
-- ✅ 方案 A：Elasticsearch / Meilisearch -- ✅ 方案 B：MySQL 内置全文索引（中文需配合 ngram parser） ALTER TABLE articles ADD FULLTEXT INDEX ft_content (content) WITH PARSER ngram; SELECT*FROM articles WHEREMATCH(content) AGAINST('关键词'INBOOLEAN MODE);
-- ❌ 原始 SQL（3.8 秒） SELECT u.*, a.title, p.prize_name, l.created_at AS join_time FROM activity_log l LEFTJOIN users u ON l.user_id = u.id LEFTJOIN activities a ON l.activity_id = a.id LEFTJOIN prizes p ON l.prize_id = p.id WHERE l.activity_id =100 ORDERBY l.created_at DESC LIMIT 20;
-- ❌ 先查再扣（并发下超卖） -- 线程 A：SELECT stock FROM products WHERE id = 1; -- stock = 10 -- 线程 B：SELECT stock FROM products WHERE id = 1; -- stock = 10 -- 线程 A：UPDATE products SET stock = 9 WHERE id = 1; -- 线程 B：UPDATE products SET stock = 9 WHERE id = 1; -- 超卖！
-- ✅ 原子操作 UPDATE products SET stock = stock -1 WHERE id =1AND stock >=1; -- 检查 affected_rows，为 0 说明库存不足
-- ✅ 或用乐观锁（版本号） UPDATE products SET stock = stock -1, version = version +1 WHERE id =1AND version =@old_version;
