南山住房和建设局网站官网,百度pc权重,不花钱的做网站,做网站联系大家好#xff0c;我是蓝胖子,前段时间mysql经常碰到慢查询报警#xff0c;我们线上的慢sql阈值是1s#xff0c;出现报警的表数据有 7000多万#xff0c;经常出现报警的是一个group by的count查询#xff0c;于是便开始着手优化这块#xff0c;遂有此篇#xff0c;记录下… 大家好我是蓝胖子,前段时间mysql经常碰到慢查询报警我们线上的慢sql阈值是1s出现报警的表数据有 7000多万经常出现报警的是一个group by的count查询于是便开始着手优化这块遂有此篇记录下自己优化过程中的心得。 优化慢sql前肯定是要懂sql的查询逻辑所以我先介绍下group by 语句的执行逻辑。
group by 执行逻辑
环境准备
拿下面这张表举例这是一张记录文件夹id和用户id关联关系的表。其中dir_id代表文件夹iduid代表用户id还有个唯一索引是uniq_dir_id。
create table t_dir_user
(
id bigint unsigned auto_increment
primary key,
dir_id bigint default 0 not null,
uid bigint default 0 not null,
constraint uniq_dir_id
unique (dir_id, uid)
)表一共有7000多万的数据。下面开始介绍使用group by 语句时sql执行的原理。
没有用到索引的情况 先说下结论group by后面的列如果不能使用上索引那么则会产生临时表且很可能产生文件排序的情况。 group by 语句有分 使用到索引和没有使用到索引的情况先看看没有使用到索引的情况。假如我想查询在一些文件夹范围内用户关注的文件夹数量。那我可以写出下面这样的sql。
explain select count(1), uid
from t_dir_user
where dir_id in (1803620,4368250,2890924,2033475,3038030)
group by uid;使用explain分析时会发现这个查询是使用到索引的且Extra 那一栏会出现下面的信息。
Using index condition; Using temporary; Using filesort上述信息代表了查询是使用到了索引来做where条件查询并且使用到了临时表和文件排序。 注意 ❗️ 临时表和文件排序这两个操作都是性能不佳的操作写sql时应尽量避免。 现在来对这种情况做更加具体的分析在上述例子中mysql相当于建立了一张临时表具体是内存的临时表还是磁盘的临时表要看临时表数据量大小内存放不下会放到磁盘上。
临时表一列存放需要分组的值上述案例中就是 uid一列存放统计出来的count值mysql会一遍扫描uniq_dir_id索引一边向这个临时表中写入数据或更新count值当索引扫描完成后再将填满数据的临时表做下排序然后返回给客户端。注意这个排序的行为如果需要排序的数据量很大则会产生文件排序否则则是内存排序。
使用到索引的情况
再来看看group by 后跟的列能使用到索引的情况。 先说下结论使用到索引的时候mysql会使用内置的聚合函数来进行操作而不是创建临时表。并且节省了排序这一步这种方式会更高效。 还是拿上面t_dir_user 这张表举例这次我们要查一定文件夹范围内一个文件夹与多少个用户关联。我们可以这样写sql
explain select count(1), dir_id
from t_dir_user
where dir_id in (1803620,4368250,2890924,2033475,3038030)
group by dir_id;此时explain分析后你会发现虽然使用的是相同的索引但是Extra这一栏的信息已经变了Extra信息如下
Using index condition; Using aggregate; Using indexUsing aggregate 这条sql会使用mysql内置的聚合函数进行分组聚合的操作。
我们来具体分析下因为group by此次是按dir_id文件夹id进行分组的而dir_id刚好可以用上dir_id和uid建立的联合索引uniq_dir_id并且索引是有序的这样mysql在扫描索引的时候就是一个文件夹id的索引数据扫描完成后再次去扫描下一个文件夹id的索引数据扫描的同时会对该文件夹id的count值进行累加。 这样一个文件夹的索引数据扫描完成后刚好就能知道这个文件夹id关联的uid的count值并将这个值发送给客户端。
所以整个过程其实是一边扫描索引对特定文件夹id的count值进行累加一边将累加后的结果返回给客户端的过程。 注意mysql返回给客户端的结果并不是全部查询出来后才返回给客户端而是可以边查边返回的。 整个过程是没有用上临时表的。这样的查询会更加高效。
使用索引的情况下如何优化千万级count group by查询
在了解完group by语句的执行逻辑后我对线上的sql进行了分析发现线上的sql的group by列是属于已经使用了索引的情况。那为啥还会慢呢 因为即使是使用了索引group by的过程还是会有扫描索引和进行累加的过程由于扫描的数据量太大了最终导致了sql整体耗时还是很慢超过了1s的阈值。
既然如此那就换一种优化思路这也是对大数据量的聚合统计的一种常用手段。 业务大部分时候都是读多写少的可以建立一张新表专门用于记录对应的文件夹管理的用户数每次关联关系发生变化时同时再更新下这张统计表的数量即可。而业务在查询数量时则直接查统计表中的数据。 这种优化非常适合大数据量的统计。
除此以外甚至还可以使用elasticsearch 这类型数据库存数据在这个案例里相当于就把t_dir_user整张表的数据同步到elasticsearch中并且做mysql到elasticsearch集群数据的实时同步机制这样以后在查询对应文件夹的关联人数时可以直接在elasticsearch进行查询。elasticsearch会对每个字段建立倒排索引。由于倒排索引中会存储该索引的记录条数在这个案例中就是dir_id对应的记录条数所以在用elasticsearch进行dir_id的分组count查询时是相当快的。
我们线上已经有elasticsearch同步部分mysql表的机制了基于此我选择了方案2直接在之前同步表中新增了t_dir_user这张表并且修改了业务查询文件夹下关联人数的逻辑改由直接查询elasticsearch。
其实你可以发现由于elasticsearch的倒排索引内直接记录了数量信息这个和由mysql建立新的统计表记录数量原理其实是一致的就是将高频的读count查询改由低频的更新操作。
