文章详情

短信预约-IT技能 免费直播动态提醒

请输入下面的图形验证码

提交验证

短信预约提醒成功

【MySql】mysql 常用查询优化策略详解

2023-08-31 20:20

关注

在程序上线运行一段时间后,一旦数据量上去了,或多或少会感觉到系统出现延迟、卡顿等现象,出现这种问题,就需要程序员或架构师进行系统调优工作了,其中,大量的实践经验表明,调优的手段尽管有很多,但涉及到SQL调优的内容仍然是非常重要的一环,本文将结合实例,总结一些工作中可能涉及到的SQL优化策略;

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

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

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

create procedure addMyData()begindeclare num int;set num =1;while num <= 100000 doinsert 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万条数据用于测试;

1、分页查询优化

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

实践经验告诉我们,越往后,分页查询效率越低,这就是分页查询的问题所在, 因为,当在进行分页查询时,如果执行 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,可以看到响应时间有一定的提升;

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

select * from student where id > 400000 limit 10;

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

2、关联查询优化

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

2.1 左连接或右连接

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

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

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

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

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

2.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;

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

create index idx_name on tenant(tenant_name);

create index idx_account on `user`(account);

再次使用explain分析结果如下

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

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

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

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

ALTER TABLE `user` DROP INDEX idx_account;
ALTER TABLE `tenant` DROP INDEX idx_name;

使用explain语句进行分析

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

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

3、子查询优化

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

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

select st.* from student st where st.classId in (select id from class where id > 100);

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

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

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

子查询性能低效的原因

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

一个真实的案例

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

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

4、排序(order by)优化

在mysql,排序主要有两种方式

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

4.1 使用age字段进行排序

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

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

4.2 使用多字段进行排序

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

如下根据stuno和age排序

 给stuno和age添加联合索引

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

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

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

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

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

2)排序时,排序的类型保持一致

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

5、分组(group by)优化

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

5.1 给group by的字段添加索引

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

 给stuno添加索引之后

  给stuno和age添加联合索引

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

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

6、count 优化

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

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

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

用法说明
count(主键)InnoDB 会遍历整张表,把每一行的主键id值都取出来,返回给服务层,服务层拿到主键后,直接按行进行累加(主键不可能为null);
count(*)InnoDB不会把全部字段取出来,而是专门做了优化,不取值,服务层直接按行进行累加;
count(字段)没有not null 约束 : InnoDB 引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,服务层判断是否为null,不为null,计数累加,有not null 约束:InnoDB 引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,直接按行进行累加;
count(数字)InnoDB 引擎遍历整张表,但不取值。服务层对于返回的每一行,放一个数字“1”进去,直接按行进行累加;

经验值总结

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

来源地址:https://blog.csdn.net/congge_study/article/details/127712927

阅读原文内容投诉

免责声明:

① 本站未注明“稿件来源”的信息均来自网络整理。其文字、图片和音视频稿件的所属权归原作者所有。本站收集整理出于非商业性的教育和科研之目的,并不意味着本站赞同其观点或证实其内容的真实性。仅作为临时的测试数据,供内部测试之用。本站并未授权任何人以任何方式主动获取本站任何信息。

② 本站未注明“稿件来源”的临时测试数据将在测试完成后最终做删除处理。有问题或投稿请发送至: 邮箱/279061341@qq.com QQ/279061341

软考中级精品资料免费领

  • 历年真题答案解析
  • 备考技巧名师总结
  • 高频考点精准押题
  • 2024年上半年信息系统项目管理师第二批次真题及答案解析(完整版)

    难度     813人已做
    查看
  • 【考后总结】2024年5月26日信息系统项目管理师第2批次考情分析

    难度     354人已做
    查看
  • 【考后总结】2024年5月25日信息系统项目管理师第1批次考情分析

    难度     318人已做
    查看
  • 2024年上半年软考高项第一、二批次真题考点汇总(完整版)

    难度     435人已做
    查看
  • 2024年上半年系统架构设计师考试综合知识真题

    难度     224人已做
    查看

相关文章

发现更多好内容

猜你喜欢

AI推送时光机
位置:首页-资讯-数据库
咦!没有更多了?去看看其它编程学习网 内容吧
首页课程
资料下载
问答资讯