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个字符进行索引的索引,从而提高索引效率,同时节约索引空间
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);
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' 就是固定值
2
3
# 3、特定类型查询优化
# 1)count()函数
查询速度:
COUNT(1)
>COUNT(*)
>COUNT(列)
COUNT(*)
:统计表中所有行的总数,包括NULL值COUNT(列)
:统计指定列中非NULL值的行数
,列中存在NULL值不会进行计数COUNT(1)
:统计所有行的总数,包括NULL值,这种方式与COUNT(*)
相同- 都是统计所有行的总数,但是它使用的是常量1,而不是通配符,在某些情况下可能会比
COUNT(*)
更快
- 都是统计所有行的总数,但是它使用的是常量1,而不是通配符,在某些情况下可能会比
# 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);
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);
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;
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
,仅返回符合条件的数据
- 无 ICP:存储引擎按
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;
2
# 3)Limit下推
- 对分页查询使用
WHERE
+ 索引列替代OFFSET
- 尽早丢弃不需要的数据,避免全量排序或全表扫描
-- 低效写法
SELECT * FROM orders
WHERE user_id=100
ORDER BY create_time DESC
LIMIT 1000, 10;
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;
2
3
4
5
6
# 6、无法命中索引
# 1)索引使用不当类
- 1.
%
开头的LIKE
查询(无法走索引)
-- 无法走索引
SELECT * FROM user WHERE name LIKE '%abc';
-- 能走索引
SELECT * FROM user WHERE name LIKE 'abc%';
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;
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';
2
3
4
5
6
7
- 4.隐式类型转换(尤其是字符串 vs 整数)
-- phone 是 varchar 类型
-- ❌ 失效:整数会隐式转换为字符串
SELECT * FROM user WHERE phone = 13800000000;
-- ✅ 正确:加引号
SELECT * FROM user WHERE phone = '13800000000';
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;
2
3
4
5
6
7
8
9
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);
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;
2
3
4
5
6
7
# 3)索引字段类型设计不合理
8.字段是 NULL 且未显式加默认值
- 索引列中 NULL 的存在会影响统计信息及使用决策,特别在 join 或 where 条件中参与比较时更明显
9.使用低选择性(区分度低)字段建索引
- 比如性别、布尔字段,只能过滤少量数据,MySQL 可能判断使用索引成本更高而放弃