MySQL SQL 优化
查询语句优化
1. 避免使用select *
反例:
select * from user where id=1;
在实际业务场景中,可能我们真正需要使用的只有其中一两列。此外,多查出来的数据,通过网络 IO 传输的过程中,也会增加数据传输的时间。最重要的一点是:select *
不走覆盖索引,会出现大量回表操作
正例:
select name,age from user where id=1;
2. 用连接查询代替子查询
MySQL 执行子查询时,需要创建临时表,查询完毕后再删除临时表,产生额外的性能消耗,这时可以改成连接查询
子查询的例子如下:
select * from order
where user_id in (select id from user where status=1)
连接查询的例子如下:
select o.* from order o
inner join user u on o.user_id = u.id
where u.status=1
3. 小表驱动大表
MySQL 的 join 实现原理是:以驱动表的数据为基础,循环匹配被驱动表的记录
- left join 前面的表是驱动表,后面的表是被驱动表
- right join 后面的表是驱动表,前面的表是被驱动表
- inner join / join 会自动选择表数据比较少的作为驱动表
假设 a 表 10000 条数据,b 表 20 数据,sql 如下:
select * from a join b on a.id = b.id
这里有两个过程,b 表数据最少,查询引擎优化选择 b 为驱动表:
- 循环 b 表的 20 条数据
- 根据条件(a.id = b.id)在 a 表的 10000 数据中匹配,找到符合条件的数据后组装返回
小表驱动大表的优点有两个:
- 减少创建链接的次数
- 对被驱动表的 join 字段建立索引,匹配时可以走索引
对被驱动表的 join 字段,也就是 b.id 建立索引,匹配时可以走索引,此时查找次数为:20 * log10000
如果反过来选择 a 为驱动表,同样的过程,此时查找次数为:10000 * log20
显然 20 * log10000 要远远小于 10000 * log20
对于 in 关键字同样适用小表驱动大表
sql 如下:
select name from a where id in (select id in b);
MySQL 会先执行子查询,再执行主查询。这里相当于先获取 b 表的所有 id,然后循环所有 id,以每一个 id 为条件去执行主查询,因此子查询选择小表
对于 exists 关键字同样适用小表驱动大表
sql 如下:
select name from b where exists (Select * from a where a.id=b.id);
MySQL 会先执行主查询,再执行子查询。这里相当于先获取 b 表的所有数据,然后循环所有数据,取 b.id 为条件执行子查询,若有匹配则返回,因此主查询选择小表
4. 高效分页
查询数据时,为了避免一次性返回过多的数据影响接口性能,一般会对查询接口做分页处理
MySQL 分页一般用 Limit 关键字:
select id,name,age from user limit 10,20;
随着页码增大,就会出现性能问题,因为 Limit 分页的原理是查出第一条到最后一条数据,再丢弃前面的数据。此时可以先记录上次分页最大的 id,然后根据 id 为条件查询,该方案要求 id 是有序的
select id,name,age from user where id > 1000000 limit 20;
还可以使用 Between 关键字实现分页,注意 id 要是连续的,不然会出现每页大小不一致的问题
select id,name,age
from user where id between 1000000 and 1000020;
5. 优化 Group By 语句
group by 关键字的主要功能是去重和分组,通常跟 having 一起使用,表示分组后再根据一定的条件过滤数据
使用 Having 例子如下:
select user_id,user_name from order
group by user_id
having user_id <= 200;
这种写法会先把所有的订单根据用户 id 分组后,再过滤用户 id 大于等于 200 的用户。分组是一个相对耗时的操作,我们可以先缩小数据的范围,再进行分组
select user_id,user_name from order
where user_id <= 200
group by user_id;
使用 where 条件分组前,把多余的数据过滤掉,这样分组效率就会更高一些
索引优化
1. 分析查询语句
找出查询中经常用于过滤、连接的字段,为这些字段创建索引,创建时要注意索引失效常见场景:https://www.cnblogs.com/Yee-Q/p/18103308
2. 避免冗余索引
控制索引数量,因为对表的数据做增删改操作时,需要同步更改索引,消耗额外的性能。建议单表的索引数量尽量控制在 5 个以内,单个索引的字段数不超过 5 个
3. 使用 explain 检查索引性能
参考文章:https://www.cnblogs.com/Yee-Q/p/18066673
数据库设计优化
1. 遵循数据库设计范式
范式是具有最小冗余的表结构,三范式的概念如下所述
第一范式:如果每列都是不可再分的最小数据单元,则满足第一范式
第一范式的目标是确保每列的原子性。例如 Address 列存储地址信息,值为“中国北京市”,违背了第一范式列不可再分的原则,要满足第一范式,就需要将 Address 列拆分为 Country 列和 Ciy 列,分别存储“中国”和“北京市”
第二范式:第二范式在第一范式的基础上,规定表中的非主键列不存在对主键的部分依赖,即第二范式要求每个表都只描述一件事情
例如 Orders 表有“订单编号”,“产品编号”,“订单日期”,“产品价格”列,既包含了订单信息,也包含了产品信息,需要拆分成订单表和产品表
第三范式:满足第一范式和第二范式,并且表中的列不存在对非主键列的业务依赖。例如 Orders 表有“订单编号”,“顾客编号”,“订单日期”,“顾客姓名”列,除了主键“订单编号”,“顾客姓名”依赖于“顾客编号”,因此需要将该“顾客姓名”移去
2. 选择合理的字段类型
char 表示固定字符串类型,存储空间是固定的。varchar 表示变长字符串类型,存储空间会根据实际数据的长度调整。如果是长度固定的字段,比如用户手机号,可以定义成 char 类型,长度是 11 字节,如果是用户名称,使用 char 类型定义长度太长或太短都有可能出现问题,建议使用 varchar 类型
选择字段类型时,应该遵循这样的原则:
- 能用数字类型,就不用字符串,因为字符的处理往往比数字要慢
- 尽可能使用小的类型,比如:用 bit 存布尔值,用 tinyint 存枚举值等
- 长度固定的字符串字段,用 char 类型
- 长度可变的字符串字段,用 varchar 类型
- 金额字段用 decimal,避免精度丢失问题
3. 大表拆分
参考资料:https://www.cnblogs.com/Yee-Q/p/18406528
数据库配置优化
1. 硬件资源配置
innodb_buffer_pool_size
:MySQL 占用内存,如果是专用的 DB 服务器,通常设置物理内存的 60% - 80%innodb_thread_concurrency
:控制 InnoDB 存储引擎允许同时进入内核的线程数量,默认设置为 0,表示不限制并发数,一般建议设置为 CPU 核心数的两倍innodb_write_io_threads
:控制 InnoDB 存储引擎负责写入 I/O 操作的线程数量,取值范围是 1 - 64,对于高性能硬盘可以设置为 16 或更高innodb_read_io_threads
:控制 InnoDB 存储引擎负责读取 I/O 操作的线程数量,取值范围是 1 - 64,对于高性能硬盘可以设置为 16 或更高max_connection
:MySQL 的最大连接数,默认值为 151,一般来说,每增加一个连接,大约需要消耗 256KB 到 1MB 的内存,需要根据服务器的内存大小来估算可支持的最大连接数thread_pool_size
:配置线程池大小,MySQL 会根据 CPU 核心数自动配置为对应的倍数,通常可设置为核心数的 2 - 4 倍
2. 查询缓存配置
query_cache_type
:用于控制查询缓存的行为,0 为关闭,1 为开启query_cache_size
:设置查询缓存的大小,默认为 0,上不封顶,视服务器内存大小按需设置
3. 日志配置
innodb_flush_log_at_trx_commit
:用于控制事务提交时 REDO 日志刷盘的行为,取值含义如下:- 0:事务提交时,日志不会立即写入磁盘,而是每秒将日志缓冲写入日志文件并刷新到磁盘,可能导致在系统崩溃时丢失最多 1 秒的事务数据
- 1:事务提交时,必须将日志缓冲写入日志文件并刷新到磁盘,这是最安全的设置,但可能会因频繁的磁盘 I/O 操作影响性能
- 2:事务提交时,将日志缓冲写入日志文件,但每秒才刷新到磁盘,在数据库实例崩溃时不会丢失事务数据,但在操作系统崩溃时可能丢失最多 1 秒的事务数据
slow_query_log
:开启慢查询日志,帮助发现执行缓慢的查询语句,1 为开启,long_query_time
可设置慢查询的时间阈值,如设为 2 秒,即执行时间超过 2 秒的查询会被记录到慢查询日志log_bin
:开启二进制日志,0 为关闭,1 为开启,用于记录数据库变更和支持主从复制
其他优化
1. 批量操作
例如一批数据需要插入表,逐条插入需要建立多次请求数据库,此时可以选择批量插入
insert into order(id,code,user_id)
values(123,'001',100),(124,'002',100),(125,'003',101);
但不建议一次批量操作太多的数据,数据太多会导致数据库响应缓慢。建议每批数据尽量控制在 500 以内,如果数据多于500,则分多批次处理
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战