记一次MySQL统计查询SQL优化

要优化的查询大概是这样的，

SELECT
	COL_B,
	MAX( CASE COL_A WHEN 'a' THEN 1 ELSE 0 END ) AS 'a',
	MAX( CASE COL_A WHEN 'b' THEN 1 ELSE 0 END ) AS 'b',
	MAX( CASE COL_A WHEN 'c' THEN 1 ELSE 0 END ) AS 'c' 
FROM
	Table 
GROUP BY
	COL_B 
ORDER BY 
    COL_C DESC
LIMIT 10

COL_C上建立了一个普通索引。50w数据这个SQL的执行时间大概为3s。

第一次优化

这里用到了分页，所以第一步我先将分页的SQL拆出来。拆成两条SQL

1. 分页

SELECT
	COL_B 
FROM
	Table
GROUP BY
    COL_B
ORDER BY
	COL_C DESC
LIMIT 10

这条SQL查出COL_B列的值，然后在我真正需要查询数据的SQL中使用IN包含查询出来 COL_B列的值。这条SQL执行时间为1.4s左右。

2. 查询

SELECT
	COL_B,
	MAX( CASE COL_A WHEN 'a' THEN 1 ELSE 0 END ) AS 'a',
	MAX( CASE COL_A WHEN 'b' THEN 1 ELSE 0 END ) AS 'b',
	MAX( CASE COL_A WHEN 'c' THEN 1 ELSE 0 END ) AS 'c'
FROM
	Table 
WHERE
	COL_B IN (...) 
GROUP BY
	COL_B
ORDER BY 
    COL_C DESC

这条查询时间缩短到0.5s。在COL_B上加上索引之后，缩短到0.1s。

在第一次优化之后，2的速度我已经可以接受了，但是1我还是觉得太慢了。

第二次优化

对第1条SQL执行EXPLAIN，发现Extra列有一个Using filesort，我想去掉这个。决定使用索引覆盖，在原来的COL_C的索引改为COL_C,COL_B的多列索引。但是这样还是不行，因为GROUP BY 使用的是COL_B，所以无法用到COL_C,COL_B的多列索引，因为索引中COL_C在COL_B左边。

既然是GROUP BY这里引起的，那么我可以不用GROUP BY，改为用DISTINCT

SELECT
    DISTINCT
	COL_B 
FROM
	Table
ORDER BY
	COL_C DESC
LIMIT 10

这么一改时间由1.4s缩短至0.1s。

原来一条SQL查询需要3s，优化之后变成了两个0.1s的SQL。