SQL优化——order by、group by、limit、count、update语句优化

最新推荐文章于 2025-11-21 18:38:03 发布
原创 最新推荐文章于 2025-11-21 18:38:03 发布 · 1.8k 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#sql #数据库

本文总结了SQL查询的优化方法，包括orderby语句优化，如创建合适索引以避免filesort，遵循最左前缀法则；groupby语句优化，通过索引提升分组效率；limit语句优化，利用覆盖索引和子查询减少全表扫描；count语句优化，理解不同计数方式的性能差异；update语句优化，确保基于索引字段更新数据，避免表锁升级。通过这些技巧，可显著提升SQL查询性能。

order by语句优化

Using filesort：通过表的索引或者全盘扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫做FileSort排序。

Using index：通过有序索引顺序扫描直接返回有序数据，这种情况即为Using index，不需要额外排序，操作效率高。

对于升序或者是降序排序，尽量给每一个相关的字段都设置上对应的索引，比如说一张表中，要给字段A和字段B进行排序，那么当我们需要A升序，并且当表中字段A出现相同时，再按照字段B进行升序，此时，我们在新建索引的时候，就需要这么来写创建索引的语句：create index tb_user_A_B_aa on the tb_user(A asc, B asc);这样，我们就新建了一个AB字段都是升序的索引，但我们需要A升序，B降序排序的时候，我们需要重新再新建一个B为降序的索引，不然SQL语句在排序的时候不会走索引，也就提升不了SQL性能，再新建一次索引：create index tb_user_A_B_ad on the tb_user(A asc, B dsc);即可

order by优化要点

  • 根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则
  • 尽量使用覆盖索引
  • 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）
  • 如果不可避免地出现filesort，大数据量排序时，可以适当增大排序缓冲徐的大小sort_buffer_size（默认是256K）。

group by语句优化

group by语句优化其实也就是针对需要分组的字段建立索引

  •  在分组操作时，可以通过索引来提高效率
  • 分组操作时，索引的使用也是满足最左前缀法则的

limit语句优化

limit在大数据量下进行分页查询，会消耗很长的查询时间，越往后性能越低。比如说需要前2000010的记录，仅仅返回2000000—2000010条记录，其他记录都丢弃，这样查询排序的代价非常大。

优化的思路：一般分页查询，通过创建覆盖索引可以比较好的提高性能，也可以通过覆盖索引加子查询（多表联查）的形式进行优化。

比如说如下图所示，子查询的语句中设置好对应的索引，然后返回主键。

 接下来再进行多表查询，将上图的查询到的结果，当做一张表来进行多表查询即可

 

count语句优化

count有几种用法：

  • count(主键)：InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层，服务层拿到主键后，直接按行进行累加
  • count(字段)：
  1. 当没有not null约束时：InnoDB引擎会遍历整张表把每一行的字段都提取出来，返回给服务层，服务层判断是否为null，不为则计数增加。
  2. 当有null约束：InnoDB引擎会遍历整张表，把每一行的字段值都取出来，返回给服务器，直接进行累加。
  • count(1)：InnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，然后直接按行进行累加。count()里边放什么参数，服务层就会放什么数字进去0，-1都是一样的。
  • count(*)：InnoDB引擎并不会把全部字段取出来，而是做了专门的优化，不取值，服务层直接按行进行累加。

所以最终按效率来排序的话：count(*) ≈ count(1)>count(主键id)>count(字段)，所以应尽量使用count(*)。

update语句优化

我们都知道InnoDB的三大特性：主键、事务、行级锁 

在对数据进行更新的时候，一定要根据索引字段来更新，因为当你where后面的字段如果说没有索引，那么行锁将会升级成表锁，从而降低并发性能。

InnoDB的行锁是针对索引加的锁，而不是针对记录加锁，并且索引不能失效，否则会从行级锁升级为表级锁。

总结

 

本篇文章仅作为学习笔记，供大家复习或者参考，学习源来自于B站黑马程序员下的MySQL数据库教程39. 进阶-SQL优化-小结_哔哩哔哩_bilibili

SQL优化（4）：group by优化
u013938578的博客
04-25 684
我们发现，如果仅仅根据age分组，就会出现 Using temporary；而如果是 根据profession,age两个字段同时分组，则不会出现 Using temporary。原因是因为对于分组操作，在联合索引中，也是符合最左前缀法则的。然后，我们在针对于 profession ， age， status 创建一个联合索引。分组操作，我们主要来看看索引对于分组操作的影响。紧接着，再执行前面相同的SQL查看执行计划。
MySQL性能优化，sql优化有哪些，数据库如何优化设计（一）
zhangfuping123456789的博客
03-18 4349
sql优化、mysql、java
一文带你快速掌握SQL优化（group by优化、limit优化、count优化、update优化）
cy的Java学习笔记
03-28 2743
本文介绍了SQL优化的一些方法，图文并茂，全面细致，适合数据库开发者和面试者学习和参考。
MySQL 分组查询（GROUP BY）与分页查询（LIMIT）详解
最新发布
2301_80724696的博客
11-21 533
本文详细介绍了MySQL中分组查询(GROUP BY)和分页查询(LIMIT)的核心用法。分组查询用于按指定字段归类并统计数据，需配合聚合函数使用，HAVING子句用于分组后过滤结果。分页查询通过LIMIT实现，起始索引从0开始，公式为(页码-1)*每页记录数。文章还讲解了查询语句的书写顺序(FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY→LIMIT)和执行顺序，并提供了常见错误示例和注意事项。这些技巧适用于数据统计和列表分页等常见场景，是MySQL开发的基础知识。
MySQL---优化（insert、order by 、group by 、limit、子查询）
weixin_43961909的博客
05-28 961
GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数。但是，有些情况下，子查询是可以被更高效的连接（①在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。②该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询。排序，所有不是通过索引直接返回排序结果的。
MySQL高级 - SQL优化 - group by 优化
Leon_Jinhai_Sun的博客
12-05 1432
优化group by 语句 由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。 如果查询包含 group by 但是用户想要避免排序结果的消耗， 则可以执行order by null 禁止排序。如下 ： drop index idx_emp_age_salar
SQL优化——group by优化
weixin_44860226的博客
07-22 407
using temperary用到了临时表这个性能是比较低的。不满足最左前缀法则的联合索引，在分组的时候的性能分析。根据专业进行分组，并统计个数。创建索引后再执行sql。
MySQL进阶之SQL优化（group by优化，limit优化，count优化，update优化）
杜小帅的博客
01-30 1564
当在进行分页查询时，如果执行 limit 2000000,10 ，此时需要MySQL排序前2000010 记 录，仅仅返回 2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大。count() 是一个聚合函数，对于返回的结果集，一行行地判断，如果 count 函数的参数不是 NULL，累计值就加 1，否则不加，最后返回累计值。当我们开启多个事务，在执行上述的SQL时，我们发现行锁升级为了表锁。在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。
SQL语句优化的七种方法
hulin的博客
03-23 2360
SQL语句优化的七种方法
PostgreSQL 查询慢？是不是忘了优化 GROUP BY、ORDER BY 和窗口函数？
https://blog.cmdragon.cn/
10-13 1982
PostgreSQL性能优化需关注GROUP BY、ORDER BY和窗口函数。GROUP BY利用功能依赖优化，当列为主键时可减少分组复杂度；GROUPING SETS、CUBE和ROLLUP能高效生成多维度聚合。HAVING过滤分组结果，而WHERE过滤原始行。ORDER BY依赖索引避免排序，Top-N查询可优化为堆排序。窗口函数在GROUP BY后执行，通过PARTITION BY和ORDER BY索引提升性能。合理设置work_mem参数可避免外部排序，临时调整或创建索引是常用优化手段。
SQL优化 - group by优化
vonlinee的博客
11-05 710
• 索引的任何其它部分（除了那些来自查询中引用的GROUP BY）必须为常数(也就是说，必须按常量数量来引用它们)，但MIN()或MAX() 函数的参数例外。• GROUP BY包括索引的第1个连续部分(如果对于GROUP BY，查询有一个DISTINCT子句，则所有DISTINCT的属性指向索引开头)。同时，临时表的使用是否可以用索引访问代替，还取决于查询中使用了索引的哪些部分、为这些部分指定的条件以及所选的聚合函数。• 如果使用聚集函数，只能使用MIN()和MAX()，并且它们均指向相同的列。
关于mysql的limit、order by和group by的用法
热门推荐
taipingliebeiluo的专栏
10-21 1万+
<br /><br />用mysql很长时间，limit是分页的一个好工具，<br />select * from table_a where num = 4 limit 1，10,<br />select * from table_a where num = 4 limit 10，10,<br />今天突然发现有些数据怎么也不会出来<br />也就是说第一页的数据会重复显示在第二页，有些在数据库的数据不会被查询出来<br />这样就造成了数据的缺失，如果用<br />select * from table
SQL数据库查询group by优化策略
09-07 887
因为如果group by 的过程因为内存临时表放不下数据，从而用到磁盘临时表的话，是比较耗时的。group by 就是分组统计的意思，一般情况都是配合聚合函数如（count(),sum(),avg(),max(),min())一起使用。当然，平时大家使用的时候，group by还是配合聚合函数使用的，除非一些特殊场景，比如你想去重，当然去重用distinct也是可以的。如果执行过程中，会发现内存临时表大小到达了上限（控制这个上限的参数就是tmp_table_size），会把内存临时表转成磁盘临时表。
数据库sql之order by优化
Yangyg_0818的博客
02-12 755
建立复合索引（age,birth），使用order by排序产生filesort情况 情况1：Select *from user where age>20 order by age;没有filesort,用到了index索引,原因是age是索引头 情况2：Select *from user where age>20 order by age, birth;没有filesort且用到...
sql优化之order by，group by，limit，join
bugNoneNull的博客
08-03 1246
一、前提准备 创建一个employees表，一个主键索引，一个联合索引，表结构如下： -- 创建表 CREATE TABLE `employees` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名', `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄', `position` varchar(20) NOT N
SQL高级之order by优化
youhebuke225的博客
06-13 2897
假设 KEY a_b_c(a,b,c)
SQL表连接、 order by 优化
ljl_name的专栏
03-07 632
select * from (select t2.* from (select rownum r, t1.* from (Select * From (Select * From crm_Vd健康档案 Where 1 = 1 And 有效状态b = '1') T order by 显示顺序, 系统序号) t1 where rownum &lt;= 100) t2 where t2.r &gt; 0...
【MySQL】基于MySQL的SQL优化（四）——对group by以及limit的优化
唐Ian的博客
10-19 4914
group by：通过下面一条 语句来了解有关group by的优化。SELECT a.first_name,a.last_name,COUNT(*) FROM film_actor fa INNER JOIN actor a USING(actor_id) GROUP BY fa.actor_id; 如图，这条SQL语句在执行时使用到了临时表（temporary）并用到了文件排序（filesor
MySQLSQL排序优化
三思的博客
03-21 2825
文章目录一、MySQL排序原理1.1 MySQL如何处理排序操作？1.2 两种排序扫描算法：1.3 排序优化方法论二、排序优化手段2.1 利用索引优化order by limit2.2 只对驱动表进行排序2.3 临时表排序2.4 利用覆盖索引优化排序 一、MySQL排序原理 1.1 MySQL如何处理排序操作？ 1）当SQL中排序字段可有效利用索引时，无论是单表查询还是多表关联查询，通过索引的有序性，我们可以快速的对order by字段进行排序检索。 2）当SQL中排序字段无法有效利用索引时：   a
sql语句优化提示词
08-23
在SQL语句的优化过程中，需要结合数据库的内部机制以及查询需求来调整查询方式，以提高执行效率和资源利用率。以下是一些常见的SQL优化技巧与最佳实践： ### 优化技巧与实践 #### 1. 合理使用索引 索引是提升查询性能的关键。在频繁查询的列上创建索引，例如主键或外键，可以显著减少数据扫描的范围。例如： ```sql CREATE INDEX idx_users_email ON users(email); ``` 但需要注意的是，索引并非越多越好，过多的索引会增加写入成本并占用额外的存储空间[^1]。 #### 2. 避免使用 `!=` 或 `<>` 操作符 在 `WHERE` 子句中使用不等于操作符会导致数据库引擎放弃已有的索引，从而进行全表扫描。例如： ```sql SELECT * FROM users WHERE status != 'active'; ``` 这种情况下，应考虑是否可以通过重构查询逻辑避免使用不等于操作符，以充分利用索引优势[^3]。 #### 3. 优化 `JOIN` 操作 确保在进行 `JOIN` 操作时，关联列上有适当的索引。避免不必要的多表连接，减少数据处理的复杂度。此外，尽量避免在连接中使用 `SELECT *`，而是只选择需要的字段，以减少数据传输量。 #### 4. 使用 `EXPLAIN` 分析查询计划 在执行查询之前，可以使用 `EXPLAIN` 命令查看查询的执行计划，了解是否使用了索引以及是否存在全表扫描。例如： ```sql EXPLAIN SELECT profession, COUNT(*) FROM tb_user GROUP BY profession; ``` 通过分析输出结果，可以识别性能瓶颈并进行针对性优化[^4]。 #### 5. 控制更新语句的检索条件 在执行 `UPDATE` 语句时，尽量选择有索引的列作为检索条件，尤其是主键索引。这样可以减少锁的粒度（如使用行锁而非表锁），提高并发性能。例如： ```sql -- 使用主键索引，添加行锁 UPDATE course SET name = 'javaEE' WHERE id = 1; -- 没有索引或索引失效，可能升级为表锁 UPDATE course SET name = 'SpringBoot' WHERE name = 'PHP'; ``` 通过这种方式，可以有效减少锁竞争，提高事务处理效率[^5]。 #### 6. 优化排序操作 在使用 `ORDER BY` 时，确保排序字段上有索引，以减少排序过程中的资源消耗。例如： ```sql SELECT * FROM users ORDER BY created_at DESC; ``` 如果 `created_at` 字段没有索引，则数据库可能需要进行额外的排序操作，从而影响性能。 #### 7. 避免全表扫描 全表扫描通常会导致性能下降，尤其是在数据量较大的情况下。可以通过创建合适的索引或调整查询条件来避免全表扫描。例如： ```sql -- 不推荐 SELECT * FROM users WHERE status = 'inactive'; -- 推荐：确保 status 字段上有索引 CREATE INDEX idx_users_status ON users(status); ``` #### 8. 使用分页查询 当需要处理大量数据时，可以使用分页查询来减少单次查询的数据量。例如： ```sql SELECT * FROM users ORDER BY id LIMIT 100 OFFSET 0; ``` 通过分页，可以有效减少内存占用和网络传输开销。 #### 9. 避免子查询嵌套 复杂的子查询嵌套可能导致性能下降。可以通过将子查询转换为 `JOIN` 操作来优化查询。例如： ```sql -- 不推荐 SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100); -- 推荐 SELECT u.* FROM users u JOIN orders o ON u.id = o.user_id WHERE o.amount > 100; ``` #### 10. 定期维护索引 随着数据的不断变化，索引可能会出现碎片化，影响查询性能。定期对索引进行重建或重组，可以保持索引的高效性。例如： ```sql -- MySQL 中可以使用 OPTIMIZE TABLE 命令 OPTIMIZE TABLE users; ``` ###
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值