06.MySQL优化

# 01.MySQL优化方向

查询语句执行顺序

# 1、SQL规范

# 1）基础规范

1.必须使用InnoDB存储引擎
- 解读：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高
2.必须使用UTF8字符集
- 解读：万国码，无需转码，无乱码风险，节省空间
3.数据表、数据字段必须加入中文注释
- 解读：N年后谁知道这个r1,r2,r3字段是干嘛的
4.禁止使用存储过程、视图、触发器、Event
- 高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”
- 并发量大的情况下，这些功能很可能将数据库拖死
- 业务逻辑放到服务层具备更好的扩展性，能够轻易实现“增机器就加性能”
- 数据库擅长存储与索引，CPU计算还是上移吧
5.禁止存储大文件或者大照片
- 解读：为何要让数据库做它不擅长的事情？大文件和照片存储在文件系统，数据库里存URI多好

# 2）命名规范

6.只允许使用内网域名，而不是ip连接数据库
7.线上环境、开发环境、测试环境数据库内网域名遵循命名规范
8.库名、表名、字段名：小写，下划线风格，不超过32个字符，必须见名知意，禁止拼音英文混用
9.表名t_xxx，非唯一索引名idx_xxx，唯一索引名uniq_xxx

# 3）表设计规范

10.单实例表数目必须小于500
11.单表列数目必须小于30
12.表必须有主键，例如自增主键
13.禁止使用外键，如果有外键完整性约束，需要应用程序控制
- 外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql 的性能，甚至会造成死锁

# 4）字段设计规范

14.必须把字段定义为NOT NULL并且提供默认值
15.禁止使用TEXT、BLOB类型
- 会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急剧降低，影响数据库性能
16.禁止使用小数存储货币
- 解读：使用整数吧，小数容易导致钱对不上 (可以整数小数分成两个字段)
17.必须使用varchar(20)存储手机号
- a）涉及到区号或者国家代号，可能出现+-()
- b）手机号会去做数学运算么？
- c）varchar可以支持模糊查询，例如：like“138%”
18.禁止使用ENUM，可使用TINYINT代替
- a）增加新的ENUM值要做DDL操作
- b）ENUM的内部实际存储就是整数，你以为自己定义的是字符串？

# 5）索引设计规范

19.单表索引建议控制在5个以内
20.单索引字段数不允许超过5个
- 解读：字段超过5个时，实际已经起不到有效过滤数据的作用了
21.禁止在更新十分频繁、区分度不高的属性上建立索引
- a）更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能
- b）“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类似
22.建立组合索引，必须把区分度高的字段放在前面
- 解读：能够更加有效的过滤数据

# 6）SQL使用规范

23.禁止使用SELECT *，只获取必要的字段，需要显示说明列属性
- a）读取不需要的列会增加CPU、IO、NET消耗
- b）不能有效的利用覆盖索引
24.禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性
- 解读：容易在增加或者删除字段后出现程序BUG
25.禁止使用属性隐式转换
- 解读：SELECT uid FROM t_user WHERE phone=13800000000 会导致全表扫描
- 而不能命中phone索引，猜猜为什么？（这个线上问题不止出现过一次）
26.禁止在WHERE条件的属性上使用函数或者表达式
- 解读：SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-01-15' 会导致全表扫描
- 正确的写法是：SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-01-15 00:00:00')
27.禁止负向查询，以及%开头的模糊查询
- a）负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描
- b）%开头的模糊查询，会导致全表扫描
28.禁止大表使用JOIN查询，禁止大表使用子查询
- 解读：会产生临时表，消耗较多内存与CPU，极大影响数据库性能
29.禁止使用OR条件，必须改为IN查询
- 解读：旧版本Mysql的OR查询是不能命中索引的，即使能命中索引，为何要让数据库耗费更多的CPU帮助实施查询优化呢？
30.应用程序必须捕获SQL异常，并有相应处理
31.同表的增删字段、索引合并一条DDL语句执行，提高执行效率，减少与数据库的交互

# 2、创建高性能索引

# 1）前缀索引

如果列很长，通常可以索引开始的部分字符，这样可以有效节约索引空间，从而提高索引效率

CREATE INDEX idx_name ON user (name(20));
-- name VARCHAR(255)类型的列，如果对整个列进行索引，索引的空间会很大
-- 这样就可以创建一个只对“name”列的前20个字符进行索引的索引，从而提高索引效率，同时节约索引空间

1
2
3

# 2）多列索引

在多数情况下，在多个列上建立独立的索引并不能提高查询性能
理由非常简单，MySQL不知道选择哪个索引的查询效率更好
当出现多个索引多个AND条件，一个包含所有相关列的索引要优于多个独立索引

-- 联合索引最优
SELECT * FROM payment where staff_id = 2 and customer_id = 584

-- 下面查询语句这样创建索引最优
select user_id from trade where user_group_id = 1 and trade_amount > 0
CREATE INDEX idx_trade_user_group_amount ON trade(user_group_id, trade_amount, user_id);

1
2
3
4
5
6

# 3）覆盖索引

如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引
覆盖索引是非常有用的工具，可以极大的提高性能，因为查询只需要扫描索引会带来许多好处
- 索引条目远小于数据行大小，如果只读取索引，极大减少数据访问量
- 索引是有按照列值顺序存储的，对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多

# 4）使用索引扫描来排序

MySQL 生成有序结果集的两种方式
- 1. 结果集排序
- 2. 按索引顺序扫描
EXPLAIN 结果中 type=index 表示使用索引扫描排序
索引扫描快，但若索引不覆盖查询列，则需回表查询，导致随机 I/O，速度可能慢于全表扫描
只有 索引列顺序 与 ORDER BY 顺序 完全一致且方向相同时，索引排序才生效
多表查询时，只有 ORDER BY 字段全部来自第一张表，才能利用索引排序
ORDER BY 受最左前缀限制，除非最左列为常数，否则需执行排序操作

-- 最左列为常数，索引：(date,staff_id,customer_id)
select  staff_id,customer_id from demo where date = '2015-06-01' order by staff_id,customer_id
-- 这里最左列索引 date = '2015-06-01' 就是固定值

1
2
3

# 3、特定类型查询优化

# 1）count()函数

查询速度： COUNT(1) > COUNT(*) > COUNT(列)

COUNT(*)：统计表中所有行的总数，包括NULL值
COUNT(列)：统计指定列中非NULL值的行数，列中存在NULL值不会进行计数
COUNT(1)：统计所有行的总数，包括NULL值，这种方式与COUNT(*)相同
- 都是统计所有行的总数，但是它使用的是常量1，而不是通配符，在某些情况下可能会比COUNT(*)更快

# 2）优化关联查询

嵌套循环关联（默认策略）
- 先取 A 表一行，再在 B 表查找匹配行，循环执行，直到 A 表遍历完
无索引时（低效）
- A 表取一行，B 表全表扫描匹配 zz，导致 A 表每行都触发 B 表全表扫描

SELECT A.xx, B.yy 
  FROM A INNER JOIN B ON A.zz = B.zz
  WHERE A.xx IN (5,6);

1
2
3

索引优化（避免全表扫描）
- A(xx, zz) 复合索引：加速 xx IN (5,6) 查询，并直接定位 zz 参与 JOIN
- B(zz) 索引：加速 JOIN，避免 B 表全表扫描
- 优化效果
  - 减少 I/O 和 CPU 计算，提升查询效率

CREATE INDEX idx_a_xx_zz ON A(xx, zz);
CREATE INDEX idx_b_zz ON B(zz);

1
2

# 3）优化LIMIT分页

索引优化：创建索引加速查询，排序字段是索引一部分时效率更高
记录上次查询位置：使用 id > last_id 方式避免 OFFSET 开销
减少偏移量：尽可能缩小 LIMIT 的 OFFSET，减少扫描和丢弃的行数
使用分区：数据量大时，分区减少查询范围，提高性能
覆盖索引：查询列都在索引中时，避免回表，提高效率

-- eg: 分页查询 status = 1 的用户，按照 created_at 升序排列，跳过前 6000 条，返回 10 条记录

-- 错误实现：存在大分页
SELECT * FROM user WHERE status = 1 
	ORDER BY created_at LIMIT 6000, 10;

-- 推荐: 使用子查询 + JOIN，减少回表次数
SELECT u.*
  FROM (
    SELECT * FROM user WHERE status = 1 
    ORDER BY created_at LIMIT 6000, 10;
  ) AS tmp
JOIN user u ON u.id = tmp.id;

1
2
3
4
5
6
7
8
9
10
11
12
13

# 4、SQL 规范

1. 计算不要放到 SQL
- 复杂计算移至业务层，避免数据库负担过重，影响性能

2. 避免子查询

子查询性能差：临时表无索引，占用 CPU 和 IO，导致慢查询
优化方式：使用 JOIN 替代子查询

-- 子查询（低效）
SELECT c.customer_name, 
  (SELECT COUNT(*) FROM orders o WHERE o.customer_id = c.customer_id) AS order_count 
FROM customers c;

-- JOIN 查询（优化）
SELECT c.customer_name, COUNT(*) AS order_count 
	FROM customers c 
	JOIN orders o ON c.customer_id = o.customer_id 
GROUP BY c.customer_id;

1
2
3
4
5
6
7
8
9
10

3. 避免过多表关联
- MySQL 关联缓存受 join_buffer_size 限制，表关联越多，占用内存越大，影响稳定性
- 建议不超过 5 个表，MySQL 最大支持 61 个表关联
4. IN 代替 OR
- 同一列多个 OR 查询时，用 IN 代替，更容易利用索引
- IN 内部值不超过 500 个，避免 SQL 过大影响性能

5. 避免 WHERE 函数转换

对列使用函数或计算会导致索引失效

-- ❌ 不推荐
WHERE DATE(create_time) = '2019-01-01'

-- ✅ 推荐
WHERE create_time >= '2019-01-01' AND create_time < '2019-01-02'

1
2
3
4
5

6. 使用 UNION ALL 代替 UNION
- UNION 需要去重，影响性能，若无重复数据，使用 UNION ALL
7. 拆分大 SQL
- 单个 SQL 只能用 1 个 CPU，大 SQL 逻辑复杂时拆分多个小 SQL，可并行执行提高效率

# 5、索引、谓词、Limit下推

技术	核心目标	适用场景	优化效果
索引下推	减少回表次数	复合索引的非最左列过滤	存储引擎层提前过滤数据
谓词下推	减少中间结果集大小	JOIN、子查询前的过滤	提前丢弃不满足条件的数据
Limit下推	避免全量数据处理	分页、Top-N 查询	尽早终止扫描或排序

# `1）索引下推 ICP`

数据库将 WHERE 子句中的部分过滤条件下推到存储引擎层执行，直接在索引遍历过程中过滤数据，减少回表次数
利用复合索引中的非最左前缀列提前过滤数据，避免将所有满足最左前缀的数据返回到 Server 层再过滤
示例：假设索引为 (a, b)，查询 WHERE a > 1 AND b = 2
- 无 ICP：存储引擎按 a > 1 扫描索引，返回所有 a > 1 的行到 Server 层，再过滤 b = 2
- 有 ICP：存储引擎在扫描索引时，直接过滤 a > 1 AND b = 2，仅返回符合条件的数据

SELECT * FROM t WHERE a > 1 AND b = 2;  -- 索引 (a, b)，ICP 直接过滤 b=2

# `2）谓词下推`

在 JOIN、子查询或聚合操作前提前过滤数据，减少中间结果集大小
若过滤条件在查询计划中执行较晚，会导致处理大量无用数据
- 无谓词下推：先执行 A JOIN B，再过滤 A.time 和 B.value
- 有谓词下推：先分别过滤 A.time > '2023-01-01' 和 B.value > 100，再进行 JOIN

SELECT * FROM A  JOIN B ON A.id = B.id 
WHERE A.time > '2023-01-01' AND B.value > 100;

1
2

# `3）Limit下推`

对分页查询使用 WHERE + 索引列替代 OFFSET
尽早丢弃不需要的数据，避免全量排序或全表扫描

-- 低效写法
SELECT * FROM orders 
WHERE user_id=100 
ORDER BY create_time DESC 
LIMIT 1000, 10;

1
2
3
4
5

-- 利用Limit下推和索引设计
-- 假设有联合索引(user_id, create_time)
SELECT * FROM orders 
WHERE user_id=100 AND create_time < 'last_page_time' 
ORDER BY create_time DESC 
LIMIT 10;

1
2
3
4
5
6

# 6、无法命中索引

# 1）索引使用不当类

1.% 开头的 LIKE 查询（无法走索引）

-- 无法走索引
SELECT * FROM user WHERE name LIKE '%abc';

-- 能走索引
SELECT * FROM user WHERE name LIKE 'abc%';

1
2
3
4
5

2.索引字段顺序不满足最左前缀匹配原则（组合索引失效）

-- 索引：(a, b, c)

-- ✅ 走索引：满足最左前缀
SELECT * FROM t WHERE a = 1;
SELECT * FROM t WHERE a = 1 AND b = 2;

-- ❌ 不走索引：跳过前缀字段
SELECT * FROM t WHERE b = 2 AND c = 3;

-- ❌ 只走 a 的索引，c 无法命中
SELECT * FROM t WHERE a = 1 AND c = 3;

1
2
3
4
5
6
7
8
9
10
11

3.在索引字段上使用函数或表达式（索引失效）

-- 索引字段为 create_time

-- ❌ 索引失效
SELECT * FROM t WHERE DATE(create_time) = '2024-05-19';

-- ✅ 改写为：
SELECT * FROM t WHERE create_time >= '2024-05-19 00:00:00' AND create_time < '2024-05-20 00:00:00';

1
2
3
4
5
6
7

4.隐式类型转换（尤其是字符串 vs 整数）

-- phone 是 varchar 类型

-- ❌ 失效：整数会隐式转换为字符串
SELECT * FROM user WHERE phone = 13800000000;

-- ✅ 正确：加引号
SELECT * FROM user WHERE phone = '13800000000';

1
2
3
4
5
6
7

# 2）SQL结构问题类

5.OR 连接多个字段，即使都建立索引也容易失效

-- a 和 b 都有索引

-- ❌ 可能全表扫（旧版本 MySQL）
SELECT * FROM t WHERE a = 1 OR b = 2;

-- ✅ 推荐改写为 UNION 或 IN
SELECT * FROM t WHERE a = 1
UNION
SELECT * FROM t WHERE b = 2;

1
2
3
4
5
6
7
8
9

1. NOT / != / NOT IN 等负向条件

-- ❌ 不命中索引
SELECT * FROM t WHERE status != 1;
SELECT * FROM t WHERE name NOT LIKE 'abc%';

-- ✅ 推荐改写为正向表达式
SELECT * FROM t WHERE status IN (2,3,4);

1
2
3
4
5
6

7.范围查询后续字段无法使用索引（范围阻断索引）

-- 索引 (a, b)

-- ✅ a 精确匹配，b 可以使用索引
SELECT * FROM t WHERE a = 1 AND b = 2;

-- ❌ a 范围，b 无法使用索引
SELECT * FROM t WHERE a > 5 AND b = 2;

1
2
3
4
5
6
7

# 3）索引字段类型设计不合理

8.字段是 NULL 且未显式加默认值
- 索引列中 NULL 的存在会影响统计信息及使用决策，特别在 join 或 where 条件中参与比较时更明显
9.使用低选择性（区分度低）字段建索引
- 比如性别、布尔字段，只能过滤少量数据，MySQL 可能判断使用索引成本更高而放弃

上次更新: 2025/4/29 17:38:19

← 05.MySQL慢查询 07.binlog redolog undolog ✅→

06.MySQL优化

# 01.MySQL优化方向

# 1、SQL规范

# 1）基础规范

# 2）命名规范

# 3）表设计规范

# 4）字段设计规范

# 5）索引设计规范

# 6）SQL使用规范

# 2、创建高性能索引

# 1）前缀索引

# 2）多列索引

# 3）覆盖索引

# 4）使用索引扫描来排序

# 3、特定类型查询优化

# 1）count()函数

# 2）优化关联查询

# 3）优化LIMIT分页

# 4、SQL 规范

# 5、索引、谓词、Limit下推

# 1）索引下推 ICP

# 2）谓词下推

# 3）Limit下推

# 6、无法命中索引

# 1）索引使用不当类

# 2）SQL结构问题类

# 3）索引字段类型设计不合理

# `1）索引下推 ICP`

# `2）谓词下推`

# `3）Limit下推`