您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

实用性极强的MySQL查询优化策略

时间:2023-12-25 14:43:25  来源:  作者:dbaplus社群

前言

在程序上线运行一段时间后,一旦数据量上去了,或多或少会感觉到系统出现延迟、卡顿等现象,出现这种问题,就需要程序员架构师进行系统调优工作了。

其中,大量的实践经验表明,调优的手段尽管有很多,但涉及到SQL调优的内容仍然是非常重要的一环,本文将结合实例,总结一些工作中可能涉及到的SQL优化策略。

一、查询优化

可以说,对于大多数系统来说,读多写少一定是常态,这就表示涉及到查询的SQL是非常高频的操作。

前置准备:给一张测试表添加10万条数据。

使用下面的存储过程给单表造一批数据,将表换成自己的就好了。

create procedure addMyData()

begin

declare num int;

set num =1;

while num <= 100000 do

insert into XXX_table values(

replace(uuid(),'-',''),concat('测试',num),concat('cs',num),'123456'

);

set num =num +1;

end while;

end ;

然后调用该存储过程:

call addMyData();

本篇准备了3张表,分别为学生(student)表,班级(class)表,账户(account)表,各自有50万,1万和10万条数据用于测试。

实用性极强的MySQL查询优化策略

实用性极强的MySQL查询优化策略

二、分页查询优化

分页查询是开发中经常会遇到的,有一种情况是,当分页的数量非常大的时候,查询的时候往往非常耗时,比如查询student表,使用下面的sql查询,耗时达到0.2秒。

实用性极强的MySQL查询优化策略

实践经验告诉我们,越往后,分页查询效率越低,这就是分页查询的问题所在, 因为,当在进行分页查询时,如果执行 limit 400000,10 ,此时需要 MySQL 排序前4000 10 记录,仅仅返回400000 - 4 00010 的记录,其他记录丢弃,查询排序的代价非常大。

优化思路:

一般分页查询时,通过创建 覆盖索引 能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化。

1.在索引上完成排序分页操作,最后根据主键关联回原表查询所需要的其他列内容

SELECT * FROM student t1,(SELECT id FROM student ORDER BY id LIMIT 400000,10) t2 WHERE t1.id =t2.id;

执行上面的sql,可以看到响应时间有一定的提升。

实用性极强的MySQL查询优化策略

2.对于主键自增的表,可以把Limit 查询转换成某个位置的查询

select * from student where id > 400000 limit 10;

执行上面的sql,可以看到响应时间有一定的提升。

三、关联查询优化

在实际的业务开发过程中,关联查询可以说随处可见,关联查询的优化核心思路是,最好为关联查询的字段添加索引,这是关键。具体到不同的场景,还需要具体分析,这个跟mysql的引擎在执行优化策略的方案选择时有一定关系。

1.左连接或右连接

下面是一个使用left join 的查询,可以预想到这条sql查询的结果集非常大。

select t.* from student t left join class cs on t.classId = cs.id;

为了检查下sql的执行效率,使用explAIn做一下分析,可以看到,第一张表即left join左边的表student走了全表扫描,而class表走了主键索引,尽管结果集较大,还是走了索引。

实用性极强的MySQL查询优化策略

针对这种场景的查询,思路如下:

  • 让查询的字段尽量包含在主键索引或者覆盖索引中;
  • 查询的时候尽量使用分页查询。

实用性极强的MySQL查询优化策略

关于左连接(右连接)的explain结果补充说明:

  • 左连接左边的表一般为驱动表,右边的表为被驱动表;
  • 尽可能让数据集小的表作为驱动表,减少mysql内部循环的次数;
  • 两表关联时,explain结果展示中,第一栏一般为驱动表。

2.关联查询关联的字段建立索引

看下面的这条sql,其关联字段非表的主键,而是普通的字段。

explain select u.* from tenant t left join `user` u on u.account = t.tenant_name where t.removed is null and u.removed is null;

实用性极强的MySQL查询优化策略

通过explain分析可以发现,左边的表走了全表扫描,可以考虑给左边的表的tenant_name和user表的account 各自创建索引。

create index idx_name on tenant(tenant_name);

create index idx_account on `user`(account);

再次使用explain分析结果如下:

实用性极强的MySQL查询优化策略

可以看到第二行type变为ref,rows的数量优化比较明显。这是由左连接特性决定的,LEFT JOIN条件用于确定如何从右表搜索行,左边一定都有,所以右边是我们的关键点,一定需要建立索引。

3.内连接关联的字段建立索引

我们知道,左连接和右连接查询的数据分别是完全包含左表数据,完全包含右表数据,而内连接(inner join 或join) 则是取交集(共有的部分),在这种情况下,驱动表的选择是由mysql优化器自动选择的。

在上面的基础上,首先移除两张表的索引:

ALTER TABLE `user` DROP INDEX idx_account;

ALTER TABLE `tenant` DROP INDEX idx_name;

使用explain语句进行分析:

实用性极强的MySQL查询优化策略

然后给user表的account字段添加索引,再次执行explain我们发现,user表竟然被当作是被驱动表了。

实用性极强的MySQL查询优化策略

此时,如果我们给tenant表的tenant_name加索引,并移除user表的account索引,得出的结果竟然都没有走索引,再次说明,使用内连接的情况下,查询优化器将会根据自己的判断进行选择。

实用性极强的MySQL查询优化策略

四、子查询优化

子查询在日常编写业务的SQL时也是使用非常频繁的做法,不是说子查询不能用,而是当数据量超出一定的范围之后,子查询的性能下降是很明显的,关于这一点,本人在日常工作中深有体会。

比如下面这条sql,由于student表数据量较大,执行起来耗时非常长,可以看到耗费了将近3秒。

select st.* from student st where st.classId in (

select id from class where id > 100

);

通过执行explain进行分析得知,内层查询 id > 100的子查询尽管用上了主键索引,但是由于结果集太大,带入到外层查询,即作为in的条件时,查询优化器还是走了全表扫描。

实用性极强的MySQL查询优化策略

针对上面的情况,可以考虑下面的优化方式:

select st.id from student st join class cl on st.classId = cl.id where cl.id > 100;

子查询性能低效的原因

  • 子查询时,MySQL需要为内层查询语句的查询结果建立一个临时表 ,然后外层查询语句从临时表中查询记录,查询完毕后,再撤销这些临时表 。这样会消耗过多的CPU和IO资源,产生大量的慢查询;
  • 子查询结果集存储的临时表,不论是内存临时表还是磁盘临时表都不能走索引,所以查询性能会受到一定的影响;
  • 对于返回结果集比较大的子查询,其对查询性能的影响也就越大。

使用mysql查询时,可以使用连接(JOIN)查询来替代子查询。连接查询不需要建立临时表 ,其速度比子查询要快 ,如果查询中使用索引的话,性能就会更好,尽量不要使用NOT IN 或者 NOT EXISTS,用LEFT JOIN xxx ON xx WHERE xx IS NULL替代。

一个真实的案例

在下面的这段sql中,优化前使用的是子查询,在一次生产问题的性能分析中,发现某个tenant_id下的数据达到了35万多,这样直接导致某个列表页面的接口查询耗时达到了5秒左右。

实用性极强的MySQL查询优化策略

找到了问题的根源后,尝试使用上面的优化思路进行解决即可,优化后的sql大概如下:

实用性极强的MySQL查询优化策略

五、排序(order by)优化

在MySQL,排序主要有两种方式:

  • Using filesort : 通过表索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序;
  • Using index : 通过有序的索引顺序扫描直接返回有序数据,这种情况即为 using index,不需要额外排序,操作效率高。

对于以上两种排序方式,Using index的性能高,而Using filesort的性能低,我们在优化排序操作时,尽量要优化为 Using index。

1.使用age字段进行排序

由于age字段未加索引,查询结果按照age排序的时候发现使用了filesort,排序性能较低。

实用性极强的MySQL查询优化策略

给age字段添加索引,再次使用order by时就走了索引。

实用性极强的MySQL查询优化策略

2.使用多字段进行排序

通常在实际业务中,参与排序的字段往往不只一个,这时候,就可以对参与排序的多个字段创建联合索引。

如下根据stuno和age排序:

实用性极强的MySQL查询优化策略

给stuno和age添加联合索引:

create index idx_stuno_age on `student`(stuno,age);

再次分析时结果如下,此时排序走了索引:

实用性极强的MySQL查询优化策略

1)关于多字段排序时的注意事项

  • 排序时,需要满足最左前缀法则,否则也会出现 filesort

在上面我们创建的联合索引顺序是stuno和age,即stuno在前面,而age在后,如果查询的时候调换排序顺序会怎样呢?通过分析结果发现,走了filesort。

实用性极强的MySQL查询优化策略

  • 排序时,排序的类型保持一致

在保持字段排序顺序不变时,默认情况下,如果都按照升序或者降序时,order by可以使用index,如果一个是升序,另一个是降序会如何呢?分析发现,这种情况下也会走filesort。

六、分组(group by)优化

group by 的优化策略和order by 的优化策略非常像,主要列举如下几个要点:

  • group by 即使没有过滤条件用到索引,也可以直接使用索引;
  • group by 先排序再分组,遵照索引建的最佳左前缀法则;
  • 当无法使用索引列时,增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置;
  • where效率高于having,能写在where限定的条件就不要写在having中了;
  • 减少使用order by,能不排序就不排序,或将排序放到程序去做。Order by、groupby、distinct这些语句较为耗费CPU,数据库的CPU资源是极其宝贵的;
  • 如果sql包含了order by、group by、distinct这些查询的语句,where条件过滤出来的结果集请保持在1000行以内,否则SQL会很慢;

1.给group by的字段添加索引

如果字段未加索引,分析结果如下,这种结果性能显然很低效。

实用性极强的MySQL查询优化策略

给stuno添加索引之后:

实用性极强的MySQL查询优化策略

给stuno和age添加联合索引:

实用性极强的MySQL查询优化策略

如果不遵循最佳左前缀,group by 性能将会比较低效。

实用性极强的MySQL查询优化策略

遵循最佳左前缀的情况如下:

实用性极强的MySQL查询优化策略

七、count 优化

count() 是一个聚合函数,对于返回的结果集,一行行判断,如果 count 函数的参数不是NULL,累计值就加 1,否则不加,最后返回累计值。

用法:count(*)、count(主键)、count(字段)、count(数字)

如下列举了count的几种写法的详细说明:

实用性极强的MySQL查询优化策略

经验值总结:

按照效率排序来看,count(字段) < count(主键 id) < count(1) ≈ count(*),所以尽量使用 count(*)

作者丨逆风飞翔的小叔

来源丨blog.csdn.NET/congge_study/article/details/127712927



Tags:MySQL   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,不构成投资建议。投资者据此操作,风险自担。如有任何标注错误或版权侵犯请与我们联系,我们将及时更正、删除。
▌相关推荐
MySQL 核心模块揭秘
server 层会创建一个 SAVEPOINT 对象,用于存放 savepoint 信息。binlog 会把 binlog offset 写入 server 层为它分配的一块 8 字节的内存里。 InnoDB 会维护自己的 savepoint...【详细内容】
2024-04-03  Search: MySQL  点击:(5)  评论:(0)  加入收藏
MySQL 核心模块揭秘,你看明白了吗?
为了提升分配 undo 段的效率,事务提交过程中,InnoDB 会缓存一些 undo 段。只要同时满足两个条件,insert undo 段或 update undo 段就能被缓存。1. 关于缓存 undo 段为了提升分...【详细内容】
2024-03-27  Search: MySQL  点击:(10)  评论:(0)  加入收藏
MySQL:BUG导致DDL语句无谓的索引重建
对于5.7.23之前的版本在评估类似DDL操作的时候需要谨慎,可能评估为瞬间操作,但是实际上线的时候跑了很久,这个就容易导致超过维护窗口,甚至更大的故障。一、问题模拟使用5.7.22...【详细内容】
2024-03-26  Search: MySQL  点击:(8)  评论:(0)  加入收藏
从 MySQL 到 ByteHouse,抖音精准推荐存储架构重构解读
ByteHouse是一款OLAP引擎,具备查询效率高的特点,在硬件需求上相对较低,且具有良好的水平扩展性,如果数据量进一步增长,可以通过增加服务器数量来提升处理能力。本文将从兴趣圈层...【详细内容】
2024-03-22  Search: MySQL  点击:(23)  评论:(0)  加入收藏
MySQL自增主键一定是连续的吗?
测试环境:MySQL版本:8.0数据库表:T (主键id,唯一索引c,普通字段d)如果你的业务设计依赖于自增主键的连续性,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不...【详细内容】
2024-03-10  Search: MySQL  点击:(5)  评论:(0)  加入收藏
准线上事故之MySQL优化器索引选错
1 背景最近组里来了许多新的小伙伴,大家在一起聊聊技术,有小兄弟提到了MySQL的优化器的内部策略,想起了之前在公司出现的一个线上问题,今天借着这个机会,在这里分享下过程和结论...【详细内容】
2024-03-07  Search: MySQL  点击:(26)  评论:(0)  加入收藏
MySQL数据恢复,你会吗?
今天分享一下binlog2sql,它是一款比较常用的数据恢复工具,可以通过它从MySQL binlog解析出你要的SQL,并根据不同选项,可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。主要...【详细内容】
2024-02-22  Search: MySQL  点击:(41)  评论:(0)  加入收藏
如何在MySQL中实现数据的版本管理和回滚操作?
实现数据的版本管理和回滚操作在MySQL中可以通过以下几种方式实现,包括使用事务、备份恢复、日志和版本控制工具等。下面将详细介绍这些方法。1.使用事务:MySQL支持事务操作,可...【详细内容】
2024-02-20  Search: MySQL  点击:(50)  评论:(0)  加入收藏
为什么高性能场景选用Postgres SQL 而不是 MySQL
一、 数据库简介 TLDR;1.1 MySQL MySQL声称自己是最流行的开源数据库,它属于最流行的RDBMS (Relational Database Management System,关系数据库管理系统)应用软件之一。LAMP...【详细内容】
2024-02-19  Search: MySQL  点击:(37)  评论:(0)  加入收藏
MySQL数据库如何生成分组排序的序号
经常进行数据分析的小伙伴经常会需要生成序号或进行数据分组排序并生成序号。在MySQL8.0中可以使用窗口函数来实现,可以参考历史文章有了这些函数,统计分析事半功倍进行了解。...【详细内容】
2024-01-30  Search: MySQL  点击:(53)  评论:(0)  加入收藏
▌简易百科推荐
MySQL 核心模块揭秘
server 层会创建一个 SAVEPOINT 对象,用于存放 savepoint 信息。binlog 会把 binlog offset 写入 server 层为它分配的一块 8 字节的内存里。 InnoDB 会维护自己的 savepoint...【详细内容】
2024-04-03  爱可生开源社区    Tags:MySQL   点击:(5)  评论:(0)  加入收藏
MySQL 核心模块揭秘,你看明白了吗?
为了提升分配 undo 段的效率,事务提交过程中,InnoDB 会缓存一些 undo 段。只要同时满足两个条件,insert undo 段或 update undo 段就能被缓存。1. 关于缓存 undo 段为了提升分...【详细内容】
2024-03-27  爱可生开源社区  微信公众号  Tags:MySQL   点击:(10)  评论:(0)  加入收藏
MySQL:BUG导致DDL语句无谓的索引重建
对于5.7.23之前的版本在评估类似DDL操作的时候需要谨慎,可能评估为瞬间操作,但是实际上线的时候跑了很久,这个就容易导致超过维护窗口,甚至更大的故障。一、问题模拟使用5.7.22...【详细内容】
2024-03-26  MySQL学习  微信公众号  Tags:MySQL   点击:(8)  评论:(0)  加入收藏
从 MySQL 到 ByteHouse,抖音精准推荐存储架构重构解读
ByteHouse是一款OLAP引擎,具备查询效率高的特点,在硬件需求上相对较低,且具有良好的水平扩展性,如果数据量进一步增长,可以通过增加服务器数量来提升处理能力。本文将从兴趣圈层...【详细内容】
2024-03-22  字节跳动技术团队    Tags:ByteHouse   点击:(23)  评论:(0)  加入收藏
MySQL自增主键一定是连续的吗?
测试环境:MySQL版本:8.0数据库表:T (主键id,唯一索引c,普通字段d)如果你的业务设计依赖于自增主键的连续性,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不...【详细内容】
2024-03-10    dbaplus社群  Tags:MySQL   点击:(5)  评论:(0)  加入收藏
准线上事故之MySQL优化器索引选错
1 背景最近组里来了许多新的小伙伴,大家在一起聊聊技术,有小兄弟提到了MySQL的优化器的内部策略,想起了之前在公司出现的一个线上问题,今天借着这个机会,在这里分享下过程和结论...【详细内容】
2024-03-07  转转技术  微信公众号  Tags:MySQL   点击:(26)  评论:(0)  加入收藏
MySQL数据恢复,你会吗?
今天分享一下binlog2sql,它是一款比较常用的数据恢复工具,可以通过它从MySQL binlog解析出你要的SQL,并根据不同选项,可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。主要...【详细内容】
2024-02-22  数据库干货铺  微信公众号  Tags:MySQL   点击:(41)  评论:(0)  加入收藏
如何在MySQL中实现数据的版本管理和回滚操作?
实现数据的版本管理和回滚操作在MySQL中可以通过以下几种方式实现,包括使用事务、备份恢复、日志和版本控制工具等。下面将详细介绍这些方法。1.使用事务:MySQL支持事务操作,可...【详细内容】
2024-02-20  编程技术汇    Tags:MySQL   点击:(50)  评论:(0)  加入收藏
MySQL数据库如何生成分组排序的序号
经常进行数据分析的小伙伴经常会需要生成序号或进行数据分组排序并生成序号。在MySQL8.0中可以使用窗口函数来实现,可以参考历史文章有了这些函数,统计分析事半功倍进行了解。...【详细内容】
2024-01-30  数据库干货铺  微信公众号  Tags:MySQL   点击:(53)  评论:(0)  加入收藏
mysql索引失效的场景
MySQL中索引失效是指数据库查询时无法有效利用索引,这可能导致查询性能显著下降。以下是一些常见的MySQL索引失效的场景:1.使用非前导列进行查询: 假设有一个复合索引 (A, B)。...【详细内容】
2024-01-15  小王爱编程  今日头条  Tags:mysql索引   点击:(82)  评论:(0)  加入收藏
站内最新
站内热门
站内头条