MySql常用查询优化策略详解

本篇文章给大家带来了关于mysql的相关知识,其中主要介绍了关于常用查询优化的相关问题,下面一起来看一下,希望对大家有帮助。

MySql常用查询优化策略详解

推荐学习:mysql视频教程

在程序上线运行一段时间后,一旦数据量上去了,或多或少会感觉到系统出现延迟、卡顿等现象,出现这种问题,就需要程序员或架构师进行系统调优工作了,其中,大量的实践经验表明,调优的手段尽管有很多,但涉及到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万条数据用于测试;

MySql常用查询优化策略详解

MySql常用查询优化策略详解

1、分页查询优化

分页查询是开发中经常会遇到的,有一种情况是,当分页的数量非常大的时候,查询的时候往往非常耗时,比如查询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常用查询优化策略详解

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表走了主键索引,尽管结果集较大,还是走了索引;

MySql常用查询优化策略详解

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

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

MySql常用查询优化策略详解

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

左连接左边的表一般为驱动表,右边的表为被驱动表;尽可能让数据集小的表作为驱动表,减少mysql内部循环的次数;两表关联时,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;

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条件用于确定如何从右表搜索行,左边一定都有,所以右边是我们的关键点,一定需要建立索引 。

2.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常用查询优化策略详解

3、子查询优化

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

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

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

MySql常用查询优化策略详解

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

蓝心千询 蓝心千询

蓝心千询是vivo推出的一个多功能AI智能助手

蓝心千询 34 查看详情 蓝心千询

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常用查询优化策略详解

4、排序(order by)优化

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

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

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

4.1 使用age字段进行排序

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

MySql常用查询优化策略详解

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

MySql常用查询优化策略详解

4.2 使用多字段进行排序

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

如下根据stuno和age排序

MySql常用查询优化策略详解

 给stuno和age添加联合索引

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

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

MySql常用查询优化策略详解

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

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

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

MySql常用查询优化策略详解

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

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

MySql常用查询优化策略详解

5、分组(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会很慢;

5.1 给group by的字段添加索引

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

MySql常用查询优化策略详解

 给stuno添加索引之后

MySql常用查询优化策略详解

  给stuno和age添加联合索引

MySql常用查询优化策略详解

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

MySql常用查询优化策略详解

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

MySql常用查询优化策略详解

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(*)

推荐学习:mysql视频教程

以上就是MySql常用查询优化策略详解的详细内容,更多请关注创想鸟其它相关文章!

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。
如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 chuangxiangniao@163.com 举报,一经查实,本站将立刻删除。
发布者:程序猿,转转请注明出处:https://www.chuangxiangniao.com/p/278237.html

赞 (0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
小米11青春版怎么设置深色模式_小米11青春版设置深色模式方法
上一篇 2026年10月17日 03:36:11
如何让父容器内多个 DIV 横向排列且高度一致?
下一篇 2025年12月24日 13:45:49

相关推荐

  • mysql的触发器是什么级的

    mysql的触发器是什么级的mysql的触发器是什么级的mysql的触发器是什么级的mysql的触发器是什么级的

    mysql的触发器是行级的。按照SQL标准,触发器可以分为两种:1、行级触发器,对于修改的每一行数据都会激活一次,如果一个语句插入了100行数据,将会调用触发器100次;2、语句级触发器,针对每个语句激活一次,一个插入100行数据的语句只会调用一次触发器。而MySQL中只支持行级触发器,不支持预语句…

    2026年10月17日 • 用户投稿
    100
  • mysql有数组吗

    mysql有数组吗mysql有数组吗mysql有数组吗mysql有数组吗

    MySQL中无数组类型,通常将数组元素按某个字符分割以字符串形式存储。MYSQL中没有数组的原因是因为大多数人并不真正需要它。关系数据库使用关系工作,并且大多数情况下,最好是为每个“信息位”分配一行表。例如,可能认为“我想要一个东西列表”,而是创建一个新表,将一个表中的行与另一个表中的行相关联;这样…

    2026年10月17日 • 用户投稿
    400
  • mysql socket是什么

    mysql socket是什么mysql socket是什么mysql socket是什么mysql socket是什么

    mysql socket是指Unix套接字文件;在类unix平台,客户端连接MySQL服务端的方式有两种,分别是TCP/IP方式与socket套接字文件方式;Unix套接字文件连接的速度比TCP/IP快,但是只能连接到同一台计算机上的服务器使用。 本教程操作环境:linux5.9.8系统、mysql…

    2026年10月17日 • 用户投稿
    100
  • mysql连接数是什么意思

    mysql连接数是什么意思mysql连接数是什么意思mysql连接数是什么意思mysql连接数是什么意思

    mysql连接数是指数据库最多能够同时接受的客户连接数。MySQL连接数是一个必须要考虑到的组件,它可以在帮助数据库能够安全运行的同时,最大化数据库当前的性能;MySQL连接数是一个重要的配置,它可以影响数据库中客户端传输和处理的数据量。如果连接数太小,可能导致连接请求阻塞而发生超时;如果连接数太大…

    2026年10月17日 • 用户投稿
    200
  • ID集合庞大时,如何高效查询关联数据?

    ID集合庞大时,如何高效查询关联数据?ID集合庞大时,如何高效查询关联数据?ID集合庞大时,如何高效查询关联数据?ID集合庞大时,如何高效查询关联数据?

    应对海量ID关联查询的策略 当需要根据大量ID查找关联数据时,直接使用SQL IN语句效率低下。本文介绍几种优化策略,提升查询性能。 方案一:分批处理 将庞大的ID集合拆分成多个小批量,逐批查询。此方法有效避免单次查询ID过多导致内存溢出或响应迟缓。示例代码如下: int batchSize = 1…

    2026年10月17日 • 用户投稿
    100
  • 什么是mysql客户端

    什么是mysql客户端什么是mysql客户端什么是mysql客户端什么是mysql客户端

    mysql客户端指的是MySQL Client,是一个命令行客户端;在安装完MySQL后,会附赠一个MySQL Client程序,用户可以通过MySQL Client登录MySQL,然后输入SQL语句并执行即可。 本教程操作环境:Windows10系统、mysql8版本、Dell G3电脑。 什么是…

    2026年10月17日 • 用户投稿
    200
  • Web全栈开发者如何提升能力,拓展职业发展空间?

    Web全栈开发者如何提升能力,拓展职业发展空间?Web全栈开发者如何提升能力,拓展职业发展空间?Web全栈开发者如何提升能力,拓展职业发展空间?Web全栈开发者如何提升能力,拓展职业发展空间?

    Web全栈开发技能提升与职业发展路径 想成为更优秀的Web全栈开发者,并获得更广阔的职业发展空间?关键并非仅仅掌握某种编程语言,而是提升更深层次的能力。本文借鉴John Carmack和Edward Kmett两位编程大师的经验(虽然文中未具体列出他们的建议,但可推测其精髓),为Web全栈开发者提供…

    2026年10月17日 • 用户投稿
    200
  • mysql死锁是什么

    mysql死锁是什么mysql死锁是什么mysql死锁是什么mysql死锁是什么

    在mysql中,死锁指的是在两个或两个以上不同的进程或线程中,因争夺资源而造成的一种互相等待的现象;由于存在共同资源的竞争或进程(或线程)间的通讯而导致各个线程间相互挂起等待,如果没有外力作用,最终会引发整个系统崩溃。mysql出现死锁的必要条件:1、资源独占条件;2、请求和保持条件;3、不剥夺条件…

    2026年10月16日 • 用户投稿
    300
  • mysql有plsql吗

    mysql有plsql吗mysql有plsql吗mysql有plsql吗mysql有plsql吗

    mysql没有plsql。plsql一般指PL/SQL,是一种过程化SQL语言;plsql是Oracle数据库对SQL语句的扩展,是专门面向Oracle数据库存储程序单元的开发的,因此不能连接mysql数据库。 本教程操作环境:windows7系统、mysql8版本、Dell G3电脑。 mysql…

    2026年10月16日 • 用户投稿
    1600
  • 用Swoole实现实时在线聊天系统

    用Swoole实现实时在线聊天系统用Swoole实现实时在线聊天系统用Swoole实现实时在线聊天系统用Swoole实现实时在线聊天系统

    使用swoole实现实时在线聊天系统可以通过以下步骤实现:1. 建立websocket服务器处理连接和消息传输。2. 设计用户管理系统跟踪在线用户。3. 实现消息广播机制确保消息实时发送。4. 使用异步任务和协程优化性能和持久化存储。5. 通过设置连接超时和心跳检测以及使用redis发布订阅优化系统…

    2026年10月16日 • 用户投稿
    200
  • mysql的运行文件是什么

    mysql的运行文件是什么mysql的运行文件是什么mysql的运行文件是什么mysql的运行文件是什么

    mysql的运行文件是mysqld;mysqld是一个可执行文件,代表着Mysql服务器程序,执行这个文件可以直接启动一个服务器进程;而mysqld_safe是一个启动脚本,它会间接调用mysqld,并且还会顺带启动一个监控进程。 本教程操作环境:Windows10系统、mysql8版、Dell G…

    2026年10月16日 • 用户投稿
    200
  • 对比不同培训机构PHP+MySQL课程的更新频率

    php和mysql课程更新频率因机构不同而异:1. 年更新一次,适合系统学习和巩固基础;2. 季度或月更新,适合保持技术前沿,但可能导致内容碎片化。选择时需考虑课程更新频率和内容质量,以满足学习需求。 当谈到PHP和MySQL课程的更新频率时,选择一个能跟上技术潮流的培训机构至关重要。不同培训机构对…

    2026年10月16日
    200
  • ThinkPHP如何配置多数据库连接?

    ThinkPHP如何配置多数据库连接?ThinkPHP如何配置多数据库连接?ThinkPHP如何配置多数据库连接?ThinkPHP如何配置多数据库连接?

    在thinkphp中配置多数据库连接的原因是实现数据分离和负载均衡,具体步骤包括:1. 在config/database.php文件中定义多个数据库连接,如默认数据库、用户数据库和订单数据库;2. 在模型中指定使用哪个数据库连接,如user模型使用user_db,order模型使用order_db。…

    2026年10月16日 • 用户投稿
    100
  • mysql连接池是什么

    mysql连接池是什么mysql连接池是什么mysql连接池是什么mysql连接池是什么

    在mysql中,连接池就是在程序启动时就创建一定数量的数据库连接,将这些连接放入一个池子进行管理;由程序动态的进行连接的申请、使用和释放。使用数据库连接池的原因:1、资源复用,在减少系统资源消耗的基础上,增加了系统运行的稳定性;2、更快的响应速度;统一的连接管理,避免数据库连接泄漏。 本教程操作环境…

    2026年10月16日 • 用户投稿
    100
  • MyEclipse报错:无法转换为TIMESTAMP

    MyEclipse报错:无法转换为TIMESTAMPMyEclipse报错:无法转换为TIMESTAMPMyEclipse报错:无法转换为TIMESTAMPMyEclipse报错:无法转换为TIMESTAMP

    当myeclipse提示“无法转换为TIMESTAMP类型”时,通常是由于数据库中的时间字段包含零值(如 ‘0000-00-00 00:00:00’),而JDBC默认无法将其解析为合法的时间戳。 问题根源在于:数据库连接URL中缺少必要的处理参数,导致驱动程序在遇到非法时间格…

    2026年10月16日 • 用户投稿
    200
  • mysql如何删除unique约束

    mysql如何删除unique约束mysql如何删除unique约束mysql如何删除unique约束mysql如何删除unique约束

    mysql中可用“ALTER TABLE”语句和“DROP”关键字来unique约束,语法为“ALTER TABLE 表名 DROP INDEX 约束名;”;“ALTER TABLE”可增减约束,当和“DROP”一起使用,即可实现删除约束。 本教程操作环境:windows7系统、mysql8版本、D…

    2026年10月16日 • 用户投稿
    100
  • 如何优化ThinkPHP的数据库查询性能?

    如何优化ThinkPHP的数据库查询性能?如何优化ThinkPHP的数据库查询性能?如何优化ThinkPHP的数据库查询性能?如何优化ThinkPHP的数据库查询性能?

    优化thinkphp的数据库查询性能可以通过以下四个关键方面进行:1. 合理设置索引,如create index idx_name on users(name),但需避免过度索引;2. 优化查询语句,如避免使用通配符,提升查询速度;3. 利用缓存机制,如cache(‘users&#821…

    2026年10月16日 • 用户投稿
    200
  • MySQL视图定义中文乱码如何修改创建选项

    mysql创建视图时出现中文乱码问题,主要原因是字符集设置不正确。解决方法包括:1.检查数据库、表和连接的字符集,建议使用utf8mb4;2.在连接后执行set names ‘utf8mb4’或在配置文件中设置默认字符集为utf8mb4;3.创建视图时显式指定characte…

    2026年10月16日
    300
  • 数据库连接池(MySQL/Redis)的实现

    数据库连接池(MySQL/Redis)的实现数据库连接池(MySQL/Redis)的实现数据库连接池(MySQL/Redis)的实现数据库连接池(MySQL/Redis)的实现

    实现mysql和redis数据库连接池可以显著提高高并发应用的性能和资源利用率。1. mysql连接池使用dbcp或c3p0库,需设置合适的连接池参数。2. redis连接池使用jedis库,需注意连接泄漏并设置参数。3. 优化建议包括定期清理无效连接、使用多级连接池策略和设置重试机制。 在处理高并…

    2026年10月16日 • 用户投稿
    300
  • Spring @Transactional注解下,只读查询会加锁吗?

    Spring @Transactional注解下,只读查询会加锁吗?Spring @Transactional注解下,只读查询会加锁吗?Spring @Transactional注解下,只读查询会加锁吗?Spring @Transactional注解下,只读查询会加锁吗?

    Spring @Transactional 注解下的数据库查询加锁行为 使用Spring框架的@Transactional注解时,对于只读查询操作(不包含增删改),是否会对数据库表加锁,取决于数据库的事务隔离级别。 隔离级别决定加锁行为 在主流数据库系统(如MySQL)中,事务隔离级别直接影响查询操…

    2026年10月16日 • 用户投稿
    200

发表回复

登录后才能评论
关注微信