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,则分多批次处理

posted @   低吟不作语  阅读(11)  评论(0编辑  收藏  举报
相关博文:
阅读排行:
· 没有源码,如何修改代码逻辑?
· PowerShell开发游戏 · 打蜜蜂
· 在鹅厂做java开发是什么体验
· 百万级群聊的设计实践
· WPF到Web的无缝过渡:英雄联盟客户端的OpenSilver迁移实战
点击右上角即可分享
微信分享提示