一般来都有这样一个说法,MYSQL 表的数据超过500万行就不行了,而在这个说法之后就是MYSQL 的group by 的性能奇差无比。
如果要用一句话来说,你把MYSQL 当其他数据库用了(PG, SQL SERVER ,ORACLE),所招致的结果。
![5226f9d2ff477000ae2d907563bcc3d5.png](https://i-blog.csdnimg.cn/blog_migrate/1639fe52c6c2508444c984054ccddb45.jpeg)
select last_name,count(*) from employees where hire_date between '1990-01-01' and '2000-01-01' group by last_name limit 10;
上面是一个查询语句
下面是有索引,没索引,不同索引的查询时间
三种情况,最后将索引落在分组字段的情况下,查询的时间是最短的。
![018be596b30af64807718b5520b6aea2.png](https://i-blog.csdnimg.cn/blog_migrate/04a83b5f3f423d41e66f8b71c165d1b5.jpeg)
![741e4be93b8b1a534920507a3dc4b314.png](https://i-blog.csdnimg.cn/blog_migrate/54252f169f427e59d4105478e3224f3d.jpeg)
![803d7062abb8c0c3c237006b658f2eb2.png](https://i-blog.csdnimg.cn/blog_migrate/c5c4e713a69a68b7619144066d046dd6.jpeg)
因为group by实际上执行相同的排序操作,所以group by基本上只是排序后的分组操作,这样,我们就可以一组一组地扫描数据,并动态地执行组。所以在有where 后的条件的索引和GROUP BY 的字段的索引,这样的情况大概率的可能性选择的是分组的索引来进行相关的查询。
当然我们也可以通过,一些参数来强制系统查询的预期结果,例如 SQL_SMALL_RESULT , SQL_BIG_RESULT , SQL_BUFFER_RESULT
![7605e5f1d190b2f6969e33f389f0edec.png](https://i-blog.csdnimg.cn/blog_migrate/81c72acdf5f91fce83eb7806a0763ac0.jpeg)
我们可以看到三种强制的预期,
1 我们的group by 或 distinct 操作的数据结果集是比较大的,则使用big_result,MYSQL会在磁盘创建临时表,并且很可能走全表扫描的方式
2 如果我们的预设的结果集比较小,则结果集会在内存中进行存储,大家可以看到连香港的 file sort 都不在存在
3 如果希望更快的解锁查询的表,可以选择buffer_result, 将尽快的将表解锁并且将结果存储在本地机,而不是 直接 send data
下面我们来看一个稍微复杂的查询
select d.dept_no,count(s.salary) as count_salary from employees as e
-> left join salaries as s on e.emp_no = s.emp_no
-> left join dept_emp as d on d.emp_no = e.emp_no
-> where e.gender = 'M'
-> group by d.dept_no;
查询的主要目的查询是男性的每个部门的总的工资消耗
![38d46b5dfb2b33e98618a61d60a72ba4.png](https://i-blog.csdnimg.cn/blog_migrate/bc00ac17ef21fb92be774fbd944770bc.jpeg)
可以看到基本上查询在不到6秒的时间,如何优化这样的查询在MYSQL中。
首先查询的时间过长是一个问题,有的时候我们的想法一般是怎么让这个语句更快的出结果,而加各种的索引,而实际中语句的优化的另一种想法是怎么能让锁表的时间更短,看上去这两者不矛盾,但实际当然其实可能是两种截然不同的思路。
例如上面的语句我这样操作,首先获得所有的部门分组信息的dept_no
![3d7acbf970d3a37bf1331e895a86294b.png](https://i-blog.csdnimg.cn/blog_migrate/1d597ac0b5a13c77cb95ddd4356134c4.jpeg)
将其保存在程序的缓存中,然后
![d03e91e04256022c62de92c83500ca78.png](https://i-blog.csdnimg.cn/blog_migrate/e43e9990d4a14486798584f4a9cf1d79.jpeg)
通过下面的语句将每个部门的工资总和获取后,在进行累加的计算(这使用程序来做不是一件困难的事情),最后获得总体的和上面语句一样的结果,而经过实际的操作,整体的查询九个部门的工资最长的不过0.34秒,最短的仅仅0.02秒。整体的查询9次累加的耗时都不超过1 秒。
select d.dept_no,count(s.salary) as count_salary from employees as e left join salaries as s on e.emp_no = s.emp_no left join dept_emp as d on d.emp_no = e.emp_no where e.gender = 'M' and d.dept_no = 'd009';
![0bffaf6e1849a2f2fd851203792587a5.png](https://i-blog.csdnimg.cn/blog_migrate/20e01c9a94f790504661c9e00f07d04e.jpeg)
通过这样的查询方法,总比死在怎么整体优化一条SQL 要好的多,语句优化,一定要灵活,不要一根筋。当然遇到类似的情况也要分析,如果遇到GROUP BY 就用这样的方法,其实还是一根筋。
![17d2038d30fe43e8f4734296f8c5aa68.png](https://i-blog.csdnimg.cn/blog_migrate/68d708ca3e823ceddcbccea8a33d4fac.jpeg)
注意:本文最早由东方瑞通讲师刘老师发表于东方瑞通官网讲师原创专区,转载请注明出处。更多讲师精彩文章,点此链接查看~
MYSQL group by 怎么能快一点,之别一根筋www.easthome.com![dea6b12a3016852fbe8322e6a37c4f63.png](https://i-blog.csdnimg.cn/blog_migrate/cc5833d7df72af1a899a3852d0c1d11d.jpeg)