金三银四已经开始啦,准备了SQL优化的13连问,内容非常干!相信大家看完一定会有帮助的。
1.日常工作中,你是怎么优化SQL的?
大家可以从这几个维度回答这个问题:
- 分析慢查询日志
- 使用explain查看执行计划
- 索引优化
- 深分页优化
- 避免全表扫描
- 避免返回不必要的数据(如select具体字段而不是select*)
- 使用合适的数据类型(如可以使用int类型的话,就不要设计为varchar)
- 优化sql结构(如join优化等等)
- 适当分批量进行 (如批量更新、删除)
- 定期清理无用的数据
- 适当分库分表
- 读写分离
2. 是否遇到过深分页问题,如何解决
我们可以通过减少回表次数来优化。一般有标签记录法和延迟关联法。
标签记录法
就是标记一下上次查询到哪一条了,下次再来查的时候,从该条开始往下扫描。就好像看书一样,上次看到哪里了,你就折叠一下或者夹个书签,下次来看的时候,直接就翻到啦。
假设上一次记录到100000,则SQL可以修改为:
select id,name,balance FROM account where id > 100000 limit 10;
复制代码
这样的话,后面无论翻多少页,性能都会不错的,因为命中了id索引。但是这种方式有局限性:需要一种类似连续自增的字段。
延迟关联法
延迟关联法,就是把条件转移到主键索引树,然后减少回表。 假设原生SQL是这样的的,其中id是主键,create_time是普通索引
select id,name,balance from account where create_time> '2020-09-19' limit 100000,10;
复制代码
使用延迟关联法优化,如下:
select acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN
(SELECT a.id FROM account a WHERE a.create_time > '2020-09-19' limit 100000, 10)
AS acct2 on acct1.id= acct2.id;
复制代码
优化思路就是,先通过idx_create_time二级索引树查询到满足条件的主键ID,再与原表通过主键ID内连接,这样后面直接走了主键索引了,同时也减少了回表。
3. 聊聊explain执行计划
当explain与SQL一起使用时,MySQL将显示来自优化器的有关语句执行计划的信息。即MySQL解释了它将如何处理该语句,包括有关如何连接表以及以何种顺序连接表等信息。
一条简单SQL,使用了explain的效果如下:
一般来说,我们需要重点关注type、rows、filtered、extra、key。
3.1 type
type表示连接类型,查看索引执行情况的一个重要指标。以下性能从好到坏依次:system > const > eq_ref > ref > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
- system:这种类型要求数据库表中只有一条数据,是const类型的一个特例,一般情况下是不会出现的。
- const:通过一次索引就能找到数据,一般用于主键或唯一索引作为条件,这类扫描效率极高,,速度非常快。
- eq_ref:常用于主键或唯一索引扫描,一般指使用主键的关联查询
- ref : 常用于非主键和唯一索引扫描。
- ref_or_null:这种连接类型类似于ref,区别在于MySQL会额外搜索包含NULL值的行
- index_merge:使用了索引合并优化方法,查询使用了两个以上的索引。
- unique_subquery:类似于eq_ref,条件用了in子查询
- index_subquery:区别于unique_subquery,用于非唯一索引,可以返回重复值。
- range:常用于范围查询,比如:between ... and 或 In 等操作
- index:全索引扫描
- ALL:全表扫描
3.2 rows
该列表示MySQL估算要找到我们所需的记录,需要读取的行数。对于InnoDB表,此数字是估计值,并非一定是个准确值。
3.3 filtered
该列是一个百分比的值,表里符合条件的记录数的百分比。简单点说,这个字段表示存储引擎返回的数据在经过过滤后,剩下满足条件的记录数量的比例。
3.4 extra
该字段包含有关MySQL如何解析查询的其他信息,它一般会出现这几个值:
- Using filesort:表示按文件排序,一般是在指定的排序和索引排序不一致的情况才会出现。一般见于order by语句
- Using index :表示是否用了覆盖索引。
- Using temporary: 表示是否使用了临时表,性能特别差,需要重点优化。一般多见于group by语句,或者union语句。
- Using where : 表示使用了where条件过滤.
- Using index condition:MySQL5.6之后新增的索引下推。在存储引擎层进行数据过滤,而不是在服务层过滤,利用索引现有的数据减少回表的数据。
3.5 key
该列表示实际用到的索引。一般配合possible_keys列一起看。
注意:有时候,explain配合show WARNINGS; (可以查看优化后,最终执行的sql),效果更佳哦。
4.说说大表的优化方案
- 数据库设计优化
合理的数据库设计可以极大地提高查询效率。我们在设计大表时,可以考虑拆分表、使用分区表、添加索引等方式来优化表结构。同时也要避免使用大量冗余字段、避免频繁使用join查询等操作。
- 索引优化
对于大表的查询操作,索引优化是非常重要的一环。可以考虑增加或者修改索引、使用覆盖索引、使用联合索引等方式来提高查询效率。同时也要注意定期清理冗余的索引以及对于经常使用的查询语句建立索引。
- 分区优化
将大表按照某个列分成多个分区表,每个分区表的数据量较小,可以提高查询和更新的性能。分区表还可以帮助在维护表结构的同时,减少锁表时间,提高并发处理能力。
- 数据清理归档
对于一些历史数据或者无用数据,可以进行定期归档,避免数据过多造成SQL查询效率降低。同时也要注意对于大表进行定期的数据备份以及紧急数据恢复的准备工作。
- 缓存优化
对于一些经常被查询的数据,可以使用缓存优化。使用Redis等缓存中间件来缓存常用的数据,以减少查询数据库的次数,提高查询效率。
- SQL语句优化
在编写SQL查询语句时,要尽可能地简单明了,避免复杂的查询语句,同时也要避免一些不必要的查询操作。对于复杂的查询语句,可以使用Explain执行计划来进行优化。同时也要注意避免使用OR等耗费性能的操作符。
- 分库分表
如果数据量千万级别,需要考虑分库分表哈。分库分表相关知识点,可以看我之前这篇文章哈,我们为什么要分库分表?
5.哪些因素可能导致MySQL慢查询?
慢查询一般有以下这些原因:
大家有兴趣可以看下。我之前写的这篇文章哈:盘点MySQL慢查询的12个原因
6.如何使用索引优化SQL查询?
- 添加合适索引(在where、group by、order by等后面的字段添加合适索引)
- 选择合适的索引类型 (B-tree索引适合范围查询、哈希索引适合等值查询)
- 注意不适合加索引的场景(数据量少的表,更新频繁的字段,区分度低的字段)
- 加索引的时候,需要考虑覆盖索引,减少回表,考虑联合索引的最左前缀原则
- explain查看SQL的执行计划,确认是否会命中索引。
- 注意索引并不是越多越好,通常建议在单个表中不要超过5个索引。因为索引会占用磁盘空间,索引更新代价高。
7.聊聊慢SQL的优化思路
- 查看慢查询日志记录,分析慢SQL
- explain分析SQL的执行计划
- profile 分析执行耗时
- Optimizer Trace分析详情
- 确定问题并采用相应的措施
7.1 查看慢查询日志记录,分析慢SQL
如何定位慢SQL呢、我们可以通过slow log来查看慢SQL。默认的情况下呢,MySQL数据库是不开启慢查询日志(slow query log)呢。所以我们需要手动把它打开。
查看下慢查询日志配置,我们可以使用show variables like 'slow_query_log%'命令,如下:
- slow query log表示慢查询开启的状态
- slow_query_log_file表示慢查询日志存放的位置
我们还可以使用show variables like 'long_query_time'命令,查看超过多少时间,才记录到慢查询日志,如下:
- long_query_time表示查询超过多少秒才记录到慢查询日志。
我们可以通过慢查日志,定位那些执行效率较低的SQL语句,重点关注分析。
7.2 explain查看分析SQL的执行计划
当定位出查询效率低的SQL后,可以使用explain查看SQL的执行计划。
当explain与SQL一起使用时,MySQL将显示来自优化器的有关语句执行计划的信息。即MySQL解释了它将如何处理该语句,包括有关如何连接表以及以何种顺序连接表等信息。
一条简单SQL,使用了explain的效果如下:
一般来说,我们需要重点关注type、rows、filtered、extra、key。
7.3 profile 分析执行耗时
explain只是看到SQL的预估执行计划,如果要了解SQL真正的执行线程状态及消耗的时间,需要使用profiling。开启profiling参数后,后续执行的SQL语句都会记录其资源开销,包括IO,上下文切换,CPU,内存等等,我们可以根据这些开销进一步分析当前慢SQL的瓶颈再进一步进行优化。
profiling默认是关闭,我们可以使用show variables like '%profil%'查看是否开启,如下:
可以使用set profiling=ON开启。开启后,可以运行几条SQL,然后使用show profiles查看一下。
show profiles会显示最近发给服务器的多条语句,条数由变量profiling_history_size定义,默认是15。如果我们需要看单独某条SQL的分析,可以show profile查看最近一条SQL的分析。也可以使用show profile for query id(其中id就是show profiles中的QUERY_ID)查看具体一条的SQL语句分析。
除了查看profile ,还可以查看cpu和io,如上图。
7.4 Optimizer Trace分析详情
profile只能查看到SQL的执行耗时,但是无法看到SQL真正执行的过程信息,即不知道MySQL优化器是如何选择执行计划。这时候,我们可以使用Optimizer Trace,它可以跟踪执行语句的解析优化执行的全过程。
我们可以使用set optimizer_trace="enabled=on"打开开关,接着执行要跟踪的SQL,最后执行select * from information_schema.optimizer_trace跟踪,如下:
大家可以查看分析其执行树,会包括三个阶段:
- join_preparation:准备阶段
- join_optimization:分析阶段
- join_execution:执行阶段
7.5 确定问题并采用相应的措施
最后确认问题,就采取对应的措施。
- 多数慢SQL都跟索引有关,比如不加索引,索引不生效、不合理等,这时候,我们可以优化索引。
- 我们还可以优化SQL语句,比如一些in元素过多问题(分批),深分页问题(基于上一次数据过滤等),进行时间分段查询
- SQl没办法很好优化,可以改用ES的方式,或者数仓。
- 如果单表数据量过大导致慢查询,则可以考虑分库分表
- 如果数据库在刷脏页导致慢查询,考虑是否可以优化一些参数,跟DBA讨论优化方案
- 如果存量数据量太大,考虑是否可以让部分数据归档
我之前写了一篇文章,有关于导致慢查询的12个原因,大家看一看一下哈:盘点MySQL慢查询的12个原因
8.一条sql执行过长的时间,你如何优化,从哪些方面入手?
这道面试题,其实跟慢SQl排查解决有点像,所以大家回答得时候,可以参考上一小节哈。我们可以从这几个方面入手哈:
- 确定瓶颈
- 索引优化
- 优化SQL语句
- 数据库参数优化
- 分析锁的情况
- 数据库硬件升级
确定瓶颈
首先,通过查看MySQL日志,慢查询日志,explain分析SQL的执行计划,profile 分析执行耗时,Optimizer Trace分析详情等操作,确定查询执行的瓶颈在哪里。只有确定了瓶颈,才能有针对性地进行优化。
索引优化
在确定了瓶颈之后,可以考虑通过增加索引来优化查询效率。可以根据查询语句的条件,增加相应的索引,从而加快查询速度。但是索引也会带来一些负面影响,如占用磁盘空间,降低写入效率等,所以需要根据具体情况权衡。
优化SQL语句
有些SQL语句本身可能存在一些问题,如join操作过于频繁,使用了不必要的子查询等,这些都会导致查询效率低下。可以通过优化SQL语句来减少不必要的操作,从而提高查询效率。
数据库参数优化
数据库参数也会影响查询效率,可以通过修改数据库参数来优化查询效率,如修改内存缓存大小、修改连接池大小等。不同的数据库参数优化方式不同,需要根据具体情况进行调整。
分析锁的情况
查询执行时间过长有可能是由于锁的问题导致的,需要分析查询语句中是否存在锁的问题,如果存在锁的问题,可以考虑增加锁的并发度,从而提高查询效率。
数据库硬件升级
如果以上方法都无法解决问题,可以考虑对数据库硬件进行升级,如增加 CPU 数量、加快磁盘读写速度等,从而提高数据库的整体性能。
9. 列举一下,常用的数据库设计优化技巧?
- 字段尽量避免使用NULL
- 合理选择数据类型
- 字段选择合适的长度
- 正确使用索引
- 尽量少定义text类型
- 合理的数据表结构设计
- 适当的冗余设计
- 优化SQL查询语句
- 一张表的字段不宜过多
10.列举日常开发中,列举十个书写高质量SQL的小技巧
- 查询SQL尽量不要使用select *,而是select具体字段。
- 小表驱动大表
- 优化你的like语句
- 尽量避免在索引列上使用mysql的内置函数
- 如果插入数据过多,考虑批量操作。
- 多用limit
- 小表驱动大表
- exist & in合理利用
- in元素不要过多
- 尽量用union all替换union
大家可以参考我之前这篇文章哈 :后端程序员必备:书写高质量SQL的30条建议
11.index merge了解过嘛?
index merge是什么?
在MySQL中,当执行一个查询语句需要使用多个索引时,MySQL可以使用索引合并(Index Merge)来优化查询性能。具体来说,索引合并是将多个单列索引或多个联合索引合并使用,以满足查询语句的需要。
当使用索引合并时,MySQL会选择最优的索引组合来执行查询,从而避免了全表扫描和排序操作,提高了查询效率。而对于使用多个单列索引的查询语句,MySQL也可以使用索引合并来优化查询性能。
大家可以看一个使用index merge的例子:
假设有一个名为orders的表,包含order_id、customer_id、product_id、order_date等字段,其中order_id、customer_id、product_id三个字段都建有索引。
如果要查询customer_id为1,order_date在2022年1月1日到2022年2月1日之间的订单记录,可以使用以下SQL语句:
SELECT *
FROM orders
WHERE customer_id = 1
AND order_date >= '2022-01-01'
AND order_date < '2022-02-01'
复制代码
在执行该查询语句时,MySQL可以使用customer_id索引和order_date索引来优化查询。如果使用单个索引,则需要扫描整个索引树来匹配查询条件;但如果使用索引合并,则可以先使用customer_id索引来过滤出符合条件的记录,然后再使用order_date索引来进一步过滤记录,从而大大减少了扫描的记录数,提高了查询效率。
大家可以使用EXPLAIN关键字可以查看查询计划,确认是否使用了索引合并。例如,执行以下语句:
EXPLAIN SELECT *
FROM orders
WHERE customer_id = 1
AND order_date >= '2022-01-01'
AND order_date < '2022-02-01'
复制代码
如果查询计划中出现了Using index merge的信息,则表示该查询使用了索引合并优化。
12. order by查询效率慢,如何优化.
大家是否还记得order by查询为什么会慢嘛?
order by排序,分为全字段排序和rowid排序。它是拿max_length_for_sort_data和结果行数据长度对比,如果结果行数据长度超过max_length_for_sort_data这个值,就会走rowid排序,相反,则走全字段排序。
rowid排序,一般需要回表去找满足条件的数据,所以效率会慢一点.如果是order by排序,可能会借助磁盘文件排序的话,效率就更慢一点.
如何优化order by的文件排序?
- 因为数据是无序的,所以就需要排序。如果数据本身是有序的,那就不会再用到文件排序啦。而索引数据本身是有序的,我们通过建立索引来优化order by语句。
- 我们还可以通过调整max_length_for_sort_data、sort_buffer_size等参数优化;
大家忘记order by的话,可以看我之前的这篇文章哈: 看一遍就理解:order by详解
13. group by 查询慢的话,如何优化呀.
group by一般用于分组统计,它表达的逻辑就是根据一定的规则,进行分组。日常开发中,我们使用得比较频繁。如果不注意,很容易产生慢SQL。
group by可能会慢在哪里?因为它既用到临时表,又默认用到排序。有时候还可能用到磁盘临时表。
- 如果执行过程中,会发现内存临时表大小到达了上限(控制这个上限的参数就是tmp_table_size),会把内存临时表转成磁盘临时表。
- 如果数据量很大,很可能这个查询需要的磁盘临时表,就会占用大量的磁盘空间。
如何优化group by呢?
- group by 后面的字段加索引
- order by null 不用排序
- 尽量只使用内存临时表
- 使用SQL_BIG_RESULT
日常开发中,我们经常会使用到group by。亲爱的小伙伴,你是否知道group by的工作原理呢?group by和having有什么区别呢?group by的优化思路是怎样的呢?使用group by有哪些需要注意的问题呢?本文将跟大家一起来学习,攻克group by~
- 使用group by的简单例子
- group by 工作原理
- group by + where 和 having的区别
- group by 优化思路
- group by 使用注意点
- 一个生产慢SQL如何优化
1. 使用group by的简单例子
group by一般用于分组统计,它表达的逻辑就是根据一定的规则,进行分组。我们先从一个简单的例子,一起来复习一下哈。
假设用一张员工表,表结构如下:
CREATE TABLE `staff` (
`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`id_card` varchar(20) NOT NULL COMMENT '身份证号码',
`name` varchar(64) NOT NULL COMMENT '姓名',
`age` int(4) NOT NULL COMMENT '年龄',
`city` varchar(64) NOT NULL COMMENT '城市',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='员工表';
表存量的数据如下:
我们现在有这么一个需求:统计每个城市的员工数量。对应的 SQL 语句就可以这么写:
select city ,count(*) as num from staff group by city;
执行结果如下:
这条SQL语句的逻辑很清楚啦,但是它的底层执行流程是怎样的呢?
2. group by 原理分析
2.1 explain 分析
我们先用explain查看一下执行计划
explain select city ,count(*) as num from staff group by city;
- Extra 这个字段的Using temporary表示在执行分组的时候使用了临时表
- Extra 这个字段的Using filesort表示使用了排序
group by 怎么就使用到临时表和排序了呢?我们来看下这个SQL的执行流程
2.2 group by 的简单执行流程
explain select city ,count(*) as num from staff group by city;
我们一起来看下这个SQL的执行流程哈
- 创建内存临时表,表里有两个字段city和num;
- 全表扫描staff的记录,依次取出city = 'X'的记录。
- 判断临时表中是否有为 city='X'的行,没有就插入一个记录 (X,1);
- 如果临时表中有city='X'的行的行,就将x 这一行的num值加 1;
- 遍历完成后,再根据字段city做排序,得到结果集返回给客户端。
这个流程的执行图如下:
临时表的排序是怎样的呢?
就是把需要排序的字段,放到sort buffer,排完就返回。在这里注意一点哈,排序分全字段排序和rowid排序
如果是全字段排序,需要查询返回的字段,都放入sort buffer,根据排序字段排完,直接返回
如果是rowid排序,只是需要排序的字段放入sort buffer,然后多一次回表操作,再返回。
怎么确定走的是全字段排序还是rowid 排序排序呢?由一个数据库参数控制的,max_length_for_sort_data
对排序有兴趣深入了解的小伙伴,可以看我这篇文章哈。
- 看一遍就理解:order by详解
3. where 和 having的区别
- group by + where 的执行流程
- group by + having 的执行流程
- 同时有where、group by 、having的执行顺序
3.1 group by + where 的执行流程
有些小伙伴觉得上一小节的SQL太简单啦,如果加了where条件之后,并且where条件列加了索引呢,执行流程是怎样?
好的,我们给它加个条件,并且加个idx_age的索引,如下:
select city ,count(*) as num from staff where age> 30 group by city;
//加索引
alter table staff add index idx_age (age);
再来expain分析一下:
explain select city ,count(*) as num from staff where age> 30 group by city;
从explain 执行计划结果,可以发现查询条件命中了idx_age的索引,并且使用了临时表和排序
Using index condition:表示索引下推优化,根据索引尽可能的过滤数据,然后再返回给服务器层根据where其他条件进行过滤。这里单个索引为什么会出现索引下推呢?explain出现并不代表一定是使用了索引下推,只是代表可以使用,但是不一定用了。大家如果有想法或者有疑问,可以加我微信讨论哈。
执行流程如下:
- 创建内存临时表,表里有两个字段city和num;
- 扫描索引树idx_age,找到大于年龄大于30的主键ID
- 通过主键ID,回表找到city = 'X'
- 判断临时表中是否有为 city='X'的行,没有就插入一个记录 (X,1);
- 如果临时表中有city='X'的行的行,就将x 这一行的num值加 1;
- 继续重复2,3步骤,找到所有满足条件的数据,
- 最后根据字段city做排序,得到结果集返回给客户端。
3.2 group by + having 的执行
如果你要查询每个城市的员工数量,获取到员工数量不低于3的城市,having可以很好解决你的问题,SQL酱紫写:
select city ,count(*) as num from staff group by city having num >= 3;
查询结果如下:
having称为分组过滤条件,它对返回的结果集操作。
3.3 同时有where、group by 、having的执行顺序
如果一个SQL同时含有where、group by、having子句,执行顺序是怎样的呢。
比如这个SQL:
select city ,count(*) as num from staff where age> 19 group by city having num >= 3;
- 执行where子句查找符合年龄大于19的员工数据
- group by子句对员工数据,根据城市分组。
- 对group by子句形成的城市组,运行聚集函数计算每一组的员工数量值;
- 最后用having子句选出员工数量大于等于3的城市组。
3.4 where + having 区别总结
- having子句用于分组后筛选,where子句用于行条件筛选
- having一般都是配合group by 和聚合函数一起出现如(count(),sum(),avg(),max(),min())
- where条件子句中不能使用聚集函数,而having子句就可以。
- having只能用在group by之后,where执行在group by之前
4. 使用 group by 注意的问题
使用group by 主要有这几点需要注意:
- group by一定要配合聚合函数一起使用嘛?
- group by的字段一定要出现在select中嘛
- group by导致的慢SQL问题
4.1 group by一定要配合聚合函数使用嘛?
group by 就是分组统计的意思,一般情况都是配合聚合函数如(count(),sum(),avg(),max(),min())一起使用。
- count() 数量
- sum() 总和
- avg() 平均
- max() 最大值
- min() 最小值
如果没有配合聚合函数使用可以吗?
我用的是Mysql 5.7 ,是可以的。不会报错,并且返回的是,分组的第一行数据。
比如这个SQL:
select city,id_card,age from staff group by city;
查询结果是
大家对比看下,返回的就是每个分组的第一条数据
当然,平时大家使用的时候,group by还是配合聚合函数使用的,除非一些特殊场景,比如你想去重,当然去重用distinct也是可以的。
4.2 group by 后面跟的字段一定要出现在select中嘛。
不一定,比如以下SQL:
select max(age) from staff group by city;
执行结果如下:
分组字段city不在select 后面,并不会报错。当然,这个可能跟不同的数据库,不同的版本有关吧。大家使用的时候,可以先验证一下就好。有一句话叫做,纸上得来终觉浅,绝知此事要躬行。
4.3 group by导致的慢SQL问题
到了最重要的一个注意问题啦,group by使用不当,很容易就会产生慢SQL 问题。因为它既用到临时表,又默认用到排序。有时候还可能用到磁盘临时表。
如果执行过程中,会发现内存临时表大小到达了上限(控制这个上限的参数就是tmp_table_size),会把内存临时表转成磁盘临时表。
如果数据量很大,很可能这个查询需要的磁盘临时表,就会占用大量的磁盘空间。
这些都是导致慢SQL的x因素,我们一起来探讨优化方案哈。
5. group by的一些优化方案
从哪些方向去优化呢?
- 方向1:既然它默认会排序,我们不给它排是不是就行啦。
- 方向2:既然临时表是影响group by性能的X因素,我们是不是可以不用临时表?
我们一起来想下,执行group by语句为什么需要临时表呢?group by的语义逻辑,就是统计不同的值出现的个数。如果这个这些值一开始就是有序的,我们是不是直接往下扫描统计就好了,就不用临时表来记录并统计结果啦?
- group by 后面的字段加索引
- order by null 不用排序
- 尽量只使用内存临时表
- 使用SQL_BIG_RESULT
5.1 group by 后面的字段加索引
如何保证group by后面的字段数值一开始就是有序的呢?当然就是加索引啦。
我们回到一下这个SQL
select city ,count(*) as num from staff where age= 19 group by city;
它的执行计划
如果我们给它加个联合索引idx_age_city(age,city)
alter table staff add index idx_age_city(age,city);
再去看执行计划,发现既不用排序,也不需要临时表啦。
加合适的索引是优化group by最简单有效的优化方式。
5.2 order by null 不用排序
并不是所有场景都适合加索引的,如果碰上不适合创建索引的场景,我们如何优化呢?
如果你的需求并不需要对结果集进行排序,可以使用order by null。
select city ,count(*) as num from staff group by city order by null
执行计划如下,已经没有filesort啦
5.3 尽量只使用内存临时表
如果group by需要统计的数据不多,我们可以尽量只使用内存临时表;因为如果group by 的过程因为内存临时表放不下数据,从而用到磁盘临时表的话,是比较耗时的。因此可以适当调大tmp_table_size参数,来避免用到磁盘临时表。
5.4 使用SQL_BIG_RESULT优化
如果数据量实在太大怎么办呢?总不能无限调大tmp_table_size吧?但也不能眼睁睁看着数据先放到内存临时表,随着数据插入发现到达上限,再转成磁盘临时表吧?这样就有点不智能啦。
因此,如果预估数据量比较大,我们使用SQL_BIG_RESULT 这个提示直接用磁盘临时表。MySQl优化器发现,磁盘临时表是B+树存储,存储效率不如数组来得高。因此会直接用数组来存
示例SQl如下:
select SQL_BIG_RESULT city ,count(*) as num from staff group by city;
执行计划的Extra字段可以看到,执行没有再使用临时表,而是只有排序
执行流程如下:
- 初始化 sort_buffer,放入city字段;
- 扫描表staff,依次取出city的值,存入 sort_buffer 中;
- 扫描完成后,对 sort_buffer的city字段做排序
- 排序完成后,就得到了一个有序数组。
- 根据有序数组,统计每个值出现的次数。
6. 一个生产慢SQL如何优化
最近遇到个生产慢SQL,跟group by相关的,给大家看下怎么优化哈。
表结构如下:
CREATE TABLE `staff` (
`id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
`id_card` varchar(20) NOT NULL COMMENT '身份证号码',
`name` varchar(64) NOT NULL COMMENT '姓名',
`status` varchar(64) NOT NULL COMMENT 'Y-已激活 I-初始化 D-已删除 R-审核中',
`age` int(4) NOT NULL COMMENT '年龄',
`city` varchar(64) NOT NULL COMMENT '城市',
`enterprise_no` varchar(64) NOT NULL COMMENT '企业号',
`legal_cert_no` varchar(64) NOT NULL COMMENT '法人号码',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8 COMMENT='员工表';
查询的SQL是这样的:
select * from t1 where status = #{status} group by #{legal_cert_no}
我们先不去探讨这个SQL的=是否合理。如果就是这么个SQL,你会怎么优化呢?有想法的小伙伴可以留言讨论哈,也可以加我微信加群探讨。如果你觉得文章那里写得不对,也可以提出来哈,一起进步,加油呀
最后
大家如果觉得面试题的答案有不对的地方,在评论区提出来哈,一起进步,加油~ 如果觉得文章对你有帮助,麻烦给个三连支持一下哈,感谢!