06.MySQL优化

# 01.MySQL优化方向

查询语句执行顺序

# 1、数据类型优化

1）NOT NULL 设置
- 通常来说把可为NULL的列改为NOT NULL不会对性能提升有多少帮助
- 只是如果计划在列上创建索引，就应该将该列设置为NOT NULL
2）INT(11) 长度无用
- INT使用32位（4个字节）存储空间，那么它的表示范围已经确定
- 所以INT(1)和INT(20)对于存储和计算是相同的
3）UNSIGNED上限提高一倍
- UNSIGNED表示不允许负值，大致可以使正数的上限提高一倍
- 比如TINYINT存储范围是-128 ~ 127，而UNSIGNED TINYINT存储的范围却是0 - 255
4）DECIMAL 避免使用
- 通常来讲，没有太大的必要使用DECIMAL数据类型
- 即使是在需要存储财务数据时，仍然可以使用BIGINT
- 比如需要精确到万分之一，那么可以将数据乘以一百万然后使用BIGINT存储
- 这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题
5）TIMESTAMP 使用4个字节存储
- TIMESTAMP使用4个字节存储空间，因而TIMESTAMP只能表示1970 - 2038年
6）schema的列不要太多
- 原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据
- 然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的
- 如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高
7）大表ALTER TABLE非常耗时
- MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表
- 从旧表中查出所有的数据插入新表，然后再删除旧表
- 尤其当内存不足而表又很大，而且还有很大索引的情况下，耗时更久
8）建议把 BLOB 或是 TEXT 列分离到单独的扩展表中
- TEXT 或 BLOB 类型只能使用前缀索引
9）避免使用 ENUM 类型
- 修改 ENUM 值需要使用 ALTER 语句
- ENUM 类型的 ORDER BY 操作效率低，需要额外操作
- ENUM 数据类型存在一些限制比如建议不要使用数值作为 ENUM 的枚举值

# 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，减少扫描和丢弃的行数
使用分区：数据量大时，分区减少查询范围，提高性能
覆盖索引：查询列都在索引中时，避免回表，提高效率

# 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

上次更新: 2024/10/15 16:27:13

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

06.MySQL优化

# 01.MySQL优化方向

# 1、数据类型优化

# 2、创建高性能索引

# 1）前缀索引

# 2）多列索引

# 3）覆盖索引

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

# 3、特定类型查询优化

# 1）count()函数

# 2）优化关联查询

# 3）优化LIMIT分页

# 4、SQL 规范

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

# 1）索引下推 ICP

# 2）谓词下推

# 3）Limit下推

# `1）索引下推 ICP`

# `2）谓词下推`

# `3）Limit下推`