Mysql 覆盖 vs 复合 vs 列索引 [英] Mysql covering vs composite vs column index
问题描述
在以下查询中
SELECT col1,col2
FROM table1
WHERE col3='value1'
AND col4='value2'
如果我有 2 个单独的索引，一个在 col3 上，另一个在 col4 上，那么在此查询中将使用其中的哪一个?
If I have 2 separate indexes one on col3 and the other on col4, Which one of them will be used in this query ?
我在某处读到，对于查询中的每个表，只使用了一个索引.这是否意味着查询无法同时使用两个索引?
I read somewhere that for each table in the query only one index is used. Does that mean that there is no way for the query to use both indexes ?
其次，如果我同时使用 col3 和 col4 创建了一个复合索引，但在 WHEREcol3strong> 子句是否会影响性能?例子:
Secondly, If I created a composite index using both col3 and col4 together but used only col3 in the WHERE clause will that be worse for the performance? example:
SELECT col1,col2
FROM table1
WHERE col3='value1'
最后，在所有情况下都使用覆盖索引更好吗?MYISAM 和 innodb 存储引擎有区别吗?
Lastly, Is it better to just use Covering indexes in all cases ? and does it differ between MYISAM and innodb storage engines ?
推荐答案
覆盖索引与复合索引不同.
A covering index is not the same as a composite index.
如果我有 2 个单独的索引，一个在 col3 上，另一个在 col4 上，那么在此查询中将使用其中的哪一个?
If I have 2 separate indexes one on col3 and the other on col4, Which one of them will be used in this query ?
具有最高基数的索引.
MySQL 会统计哪些索引具有哪些属性.
将使用具有最大辨别力的索引(如 MySQL 的统计数据所示).
The index with the highest cardinality.
MySQL keeps statistics on which index has what properties.
The index that has the most discriminating power (as evident in MySQL's statistics) will be used.
我在某处读到，对于查询中的每个表，只使用了一个索引.这是否意味着查询无法同时使用两个索引?
I read somewhere that for each table in the query only one index is used. Does that mean that there is no way for the query to used both indexes ?
您可以使用子选择.
或者甚至更好地使用包含 col3 和 col4 的复合索引.
You can use a subselect.
Or even better use a compound index that includes both col3 and col4.
其次，如果我同时使用 col3 和 col4 创建了一个复合索引，但在 WHERE 子句中只使用了 col3，那么性能会不会更差?例子:
Secondly, If I created a composite index using both col3 and col4 together but used only col3 in the WHERE clause will that be worse for the performance? example:
复合指数
正确的术语是复合索引，而不是复合索引.
只会使用复合索引的最左侧部分.
所以如果索引定义为
Compound index
The correct term is compound index, not composite.
Only the left-most part of the compound index will be used.
So if the index is defined as
index myindex (col3, col4) <<-- will work with your example.
index myindex (col4, col3) <<-- will not work.
参见:http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html
请注意，如果您选择最左边的字段，则可以避免在 where 子句中不使用索引的那部分.
假设我们有一个复合索引
Note that if you select a left-most field, you can get away with not using that part of the index in your where clause.
Imagine we have a compound index
Myindex(col1,col2)
SELECT col1 FROM table1 WHERE col2 = 200 <<-- will use index, but not efficiently
SELECT * FROM table1 where col2 = 200 <<-- will NOT use index.
这样做的原因是第一个查询使用覆盖索引并对其进行扫描.
第二个查询需要访问表，因此扫描索引没有意义.
这只适用于 InnoDB.
The reason this works is that the first query uses the covering index and does a scan on that.
The second query needs to access the table and for that reason scanning though the index does not make sense.
This only works in InnoDB.
什么是覆盖索引
覆盖索引是指查询中选择的所有字段都被索引覆盖的情况，在这种情况下，InnoDB(不是MyISAM)永远不会读取表中的数据，而只会使用表中的数据索引，显着加快了选择.
请注意，在 InnoDB 中，主键包含在所有二级索引中，因此在某种程度上所有二级索引都是复合索引.
这意味着如果您在 InnoDB 上运行以下查询:
What's a covering index
A covering index refers to the case when all fields selected in a query are covered by an index, in that case InnoDB (not MyISAM) will never read the data in the table, but only use the data in the index, significantly speeding up the select.
Note that in InnoDB the primary key is included in all secondary indexes, so in a way all secondary indexes are compound indexes.
This means that if you run the following query on InnoDB:
SELECT indexed_field FROM table1 WHERE pk = something
MySQL 将始终使用覆盖索引并且不会访问实际表. 虽然它可以使用覆盖索引，但它更喜欢 PRIMARY KEY 因为它只需要击中一行.
MySQL will always use a covering index and will not access the actual table. Although it could use a covering index, it will prefer the PRIMARY KEY because it only needs to hit a single row.
这篇关于Mysql 覆盖 vs 复合 vs 列索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
