不做大哥好多年 不做大哥好多年
首页
  • MySQL
  • Redis
  • Elasticsearch
  • Kafka
  • Etcd
  • MongoDB
  • TiDB
  • RabbitMQ
  • 01.Python
  • 02.GO
  • 03.Java
  • 04.业务问题
  • 05.关键技术
  • 06.项目常识
  • 10.计算机基础
  • Docker
  • K8S
  • 容器原理
  • Istio
  • 01.GO基础
  • 02.面向对象
  • 03.并发编程
  • 04.常用库
  • 05.数据库操作
  • 06.Beego框架
  • 07.Beego商城
  • 08.GIN框架
  • 09.GIN论坛
  • 10.微服务
  • 01.Python基础
  • 02.Python模块
  • 03.Django
  • 04.Flask
  • 05.SYL
  • 06.Celery
  • 10.微服务
  • 01.Java基础
  • 02.面向对象
  • 03.Java进阶
  • 04.Web基础
  • 05.Spring框架
  • 100.微服务
  • 数据结构
  • 算法基础
  • 算法题分类
  • 前置知识
  • PyTorch
  • Langchain
  • Linux基础
  • Linux高级
  • Nginx
  • KeepAlive
  • ansible
  • zabbix
  • Shell
  • Linux内核

逍遥子

不做大哥好多年
首页
  • MySQL
  • Redis
  • Elasticsearch
  • Kafka
  • Etcd
  • MongoDB
  • TiDB
  • RabbitMQ
  • 01.Python
  • 02.GO
  • 03.Java
  • 04.业务问题
  • 05.关键技术
  • 06.项目常识
  • 10.计算机基础
  • Docker
  • K8S
  • 容器原理
  • Istio
  • 01.GO基础
  • 02.面向对象
  • 03.并发编程
  • 04.常用库
  • 05.数据库操作
  • 06.Beego框架
  • 07.Beego商城
  • 08.GIN框架
  • 09.GIN论坛
  • 10.微服务
  • 01.Python基础
  • 02.Python模块
  • 03.Django
  • 04.Flask
  • 05.SYL
  • 06.Celery
  • 10.微服务
  • 01.Java基础
  • 02.面向对象
  • 03.Java进阶
  • 04.Web基础
  • 05.Spring框架
  • 100.微服务
  • 数据结构
  • 算法基础
  • 算法题分类
  • 前置知识
  • PyTorch
  • Langchain
  • Linux基础
  • Linux高级
  • Nginx
  • KeepAlive
  • ansible
  • zabbix
  • Shell
  • Linux内核
  • MySQL

    • 01.安装MySQL
    • 02.MySQL事务
    • 03.MySQL锁
    • 04.MySQL索引 ✅
    • 05.MySQL慢查询
    • 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)索引字段类型设计不合理
    • 07.binlog redolog undolog ✅
    • 08.MVCC原理 ✅
    • 09.SQL执行过程 ✅
    • 10.MySQL主从同步
    • 11.MySQL主从配置
    • 12.MySQL和Redis一致性
    • 13.MySQL查询缓存
    • 90.其他
    • 95.MySQL管理
    • 96.MySQL基本查询
    • 97.创建表结构
    • 98.SQL语句面试50题
    • 99.FAQ
  • Redis

  • Elasticsearch

  • Kafka

  • Etcd

  • MongoDB

  • TiDB

  • RabbitMQ

  • 数据库
  • MySQL
xiaonaiqiang
2021-02-22
目录

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
1

# 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 ✅

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

最近更新
01
05.快递Agent智能体
06-04
02
200.AI Agent核心概念
06-04
03
105.Agent智能体梳理
06-04
更多文章>
Theme by Vdoing | Copyright © 2019-2025 逍遥子 技术博客 京ICP备2021005373号
  • 跟随系统
  • 浅色模式
  • 深色模式
  • 阅读模式