覆盖索引的正确字段顺序 - MySQL [英] Proper field orders for covering index - MySQL
问题描述
是否有标准订单为MySQL中的表创建覆盖索引？这意味着如果我的查询具有where子句，order by和select语句中的字段，我将以什么顺序将索引的字段正确创建覆盖索引？
Is there a standard order to create a covering index for a table in MySQL? Meaning if I have query that has a where clause, order by and the fields in the select statement, in what order would I have the fields to the index to properly create a covering index?
推荐答案
覆盖索引采用逗号分隔列表中的列列表。从左侧开始遍历/审查此列表。如果未使用最左侧的列，则不使用索引。含义，列表如下：
A covering index takes a list of columns in a comma separated list. This list is traversed/reviewed starting at the left side. If the left most column is not used, the index is not used. Meaning, having a column list like:
col_a, col_b, col_c
如果查询不包含对 col_a 的引用，则不会使用它。如果订单更改为：
If the query does not contain a reference to col_a, it won't be used. If the order is changed to:
col_c, col_b, col_a
...然后 col_c 需要在查询中引用。继续使用第二个覆盖索引列示例， col_b 或 col_a 不必在查询中但是评估从左到右逐列移动。
...then col_c needs to be referenced in the query. Continuing to use the second covering index column example, col_b or col_a don't have to be in in the query but the evaluation moves column by column, from left to right.
索引使用的列引用可以在以下子句中：
Column references for index use can be in the following clauses:
- SELECT
- WHERE
- GROUP BY
- HAVING
- ORDER BY
- SELECT
- WHERE
- GROUP BY
- HAVING
- ORDER BY
参考：
- Multiple-Column Indexes, MySQL documentation
这篇关于覆盖索引的正确字段顺序 - MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
