1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

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拆成两条

  • 分页

    1
2
3
4
5
6
7
8
9

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

  • 查询

     1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

    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。

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

第二次优化

对分页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

1
2
3
4
5
6
7
8

SELECT
    DISTINCT
	COL_B 
FROM
	Table
ORDER BY
	COL_C DESC
LIMIT 10

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

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