今天执行下面的语句,发现没有走索引:
SELECT
*
FROM
`a`
LEFT JOIN `b` ON `a`.`log_id` = `b`.`id`
LEFT JOIN `c` ON `a`.`another_log_id` = `c`.`id`
GROUP BY
`a`.`brand_id`,
`a`.`title`
ORDER BY
`a`.`id` DESC
LIMIT 20 OFFSET 0
其中groupby用到的所有字段都单独建立了索引,也尝试过建立组合索引,explain了一下发现都没有走索引,于是去404网站查了一下,发现了 这个链接,其中的loose index scan部分说到了groupby可以使用索引的情况,我这个查询第一条就不符合了。
为了方便部分人这边贴一部分使用索引的条件,不是全部,建议去原文看看!
-
The query is over a single table.
-
The GROUP BY names only columns that form a leftmost prefix of the index and no other columns. (If, instead of GROUP BY, the query has a DISTINCT clause, all distinct attributes refer to columns that form a leftmost prefix of the index.) For example, if a table t1 has an index on (c1,c2,c3), Loose Index Scan is applicable if the query has GROUP BY c1, c2. It is not applicable if the query has GROUP BY c2, c3 (the columns are not a leftmost prefix) or GROUP BY c1, c2, c4 (c4 is not in the index).
-
The only aggregate functions used in the select list (if any) are MIN() and MAX(), and all of them refer to the same column. The column must be in the index and must immediately follow the columns in the GROUP BY.
-
Any other parts of the index than those from the GROUP BY referenced in the query must be constants (that is, they must be referenced in equalities with constants), except for the argument of MIN() or MAX() functions.
For columns in the index, full column values must be indexed, not just a prefix. For example, with c1 VARCHAR(20), INDEX (c1(10)), the index uses only a prefix of c1 values and cannot be used for Loose Index Scan.