【PHP】一次group by+order by 性能优化分析

最近通过一个日志表做排行的时候发现特别卡，最后问题得到了解决，梳理一些索引和MySQL执行过程的经验，但是最后还是有5个谜题没解开，希望大家帮忙解答下

主要包含如下知识点

• 用数据说话证明慢日志的扫描行数到底是如何统计出来的
• 从 group by 执行原理找出优化方案
• 排序的实现细节
• gdb 源码调试

• 10年架构师领你架构-成长之路-（附面试题（含答案））

  （腾讯T3-T4）打造互联网PHP架构师教程目录大全，只要你看完，薪资立马提升2倍（持续更新）

  点击与我交流企鹅群.

背景

需要分别统计本月、本周被访问的文章的 TOP10。日志表如下

CREATE TABLE `article_rank` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `aid` int(11) unsigned NOT NULL,
  `pv` int(11) unsigned NOT NULL DEFAULT '1',
  `day` int(11) NOT NULL COMMENT '日期 例如 20171016',
  PRIMARY KEY (`id`),
  KEY `idx_day_aid_pv` (`day`,`aid`,`pv`),
  KEY `idx_aid_day_pv` (`aid`,`day`,`pv`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

准备工作

为了能够清晰的验证自己的一些猜想，在虚拟机里安装了一个 debug 版的 mysql，然后开启了慢日志收集，用于统计扫描行数

安装

• 下载源码
• 编译安装
• 创建 mysql 用户
• 初始化数据库
• 初始化 mysql 配置文件
• 修改密码

如果你兴趣，具体可以参考我的博客，一步步安装  https://mengkang.net/1335.html

开启慢日志

编辑配置文件，在[mysqld]块下添加

slow_query_log=1
slow_query_log_file=xxx
long_query_time=0
log_queries_not_using_indexes=1

性能分析

发现问题

假如我需要查询2018-12-20 ~ 2018-12-24这5天浏览量最大的10篇文章的 sql 如下，首先使用explain看下分析结果

mysql> explain select aid,sum(pv) as num from article_rank where day>=20181220 and day<=20181224 group by aid order by num desc limit 10;
+----+-------------+--------------+------------+-------+-------------------------------+----------------+---------+------+--------+----------+-----------------------------------------------------------+
| id | select_type | table        | partitions | type  | possible_keys                 | key            | key_len | ref  | rows   | filtered | Extra                                                     |
+----+-------------+--------------+------------+-------+-------------------------------+----------------+---------+------+--------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | article_rank | NULL       | range | idx_day_aid_pv,idx_aid_day_pv | idx_day_aid_pv | 4       | NULL | 404607 |   100.00 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+--------------+------------+-------+-------------------------------+----------------+---------+------+--------+----------+-----------------------------------------------------------+

系统默认会走的索引是idx_day_aid_pv，根据Extra信息我们可以看到，使用idx_day_aid_pv索引的时候，会走覆盖索引，但是会使用临时表，会有排序。

我们查看下慢日志里的记录信息

# Time: 2019-03-17T03:02:27.984091Z
# User@Host: root[root] @ localhost []  Id:     6
# Query_time: 56.959484  Lock_time: 0.000195 Rows_sent: 10  Rows_examined: 1337315
SET timestamp=1552791747;
select aid,sum(pv) as num from article_rank where day>=20181220 and day<=20181224 group by aid order by num desc limit 10;

为什么扫描行数是 1337315

我们查询两个数据，一个是满足条件的行数，一个是group by统计之后的行数。

mysql> select count(*) from article_rank where day>=20181220 and day<=20181224;
+----------+
| count(*) |
+----------+
|   785102 |
+----------+

mysql> select count(distinct aid) from article_rank where day>=20181220 and day<=20181224;
+---------------------+
| count(distinct aid) |
+---------------------+
|              552203 |
+---------------------+

发现满足条件的总行数（785102）+group by 之后的总行数（552203）+limit 的值 = 慢日志里统计的 Rows_examined。

要解答这个问题，就必须搞清楚上面这个 sql 到底分别都是如何运行的。

执行流程分析

索引示例

为了便于理解，我按照索引的规则先模拟idx_day_aid_pv索引的一小部分数据

day	aid	pv