MySQL 覆盖索引 vs 组合索引 vs 列索引

Question

MySQL 覆盖索引 vs 组合索引 vs 列索引

mysqlsqlindexingcovering-indexcomposite-index

40

在下面的查询中：

SELECT  col1,col2
FROM    table1
WHERE   col3='value1'
  AND   col4='value2'

如果我有两个分别在col3和col4上的索引，那么哪一个会在这个查询中使用？

我曾在某处阅读到，对于查询中的每个表，只使用一个索引。这是否意味着查询无法同时使用两个索引？

其次，如果我创建了一个复合索引，将col3和col4一起使用，但仅在WHERE子句中使用col3，那么性能会更差吗？示例：

SELECT  col1,col2
FROM    table1
WHERE   col3='value1'

最后，是否在所有情况下都最好只使用覆盖索引？并且这是否在MYISAM和innodb存储引擎之间有所不同？

- Songo

4个回答

5

我因为完整性而点赞Johan的回答，但我认为他对于二级索引所做的陈述是不正确和/或令人困惑的；

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 will always use a covering index and will not access the actual table.

虽然我同意主键在二级索引中是包含的，但我不同意MySQL“总是会使用一个覆盖索引”在这里指定的SELECT查询。

要了解原因，请注意，在这种情况下始终需要进行完整的索引“扫描”。这与“查找”操作不同，而是对二级索引内容的100％扫描。这是由于二级索引不是按主键排序的；它是按“indexed_field”排序的（否则作为索引就没有太多用处！）。

考虑到后一事实，有时从“实际表”而不是从二级索引中提取indexed_field更有效率。

- Sam T

如果有显式的相等条件：pk = something，那么它被排序的方式又有什么关系呢？ - poige

@poige 在二级索引中，对应的“pk”在哪里可以找到？数据库引擎并不知道。它必须从二级索引的开头开始，并“扫描”其中的每个条目，从开头到结尾。想象一下食谱书的索引部分：它是按字母顺序排序的，而不是按页码排序的。如果您想查找某个菜谱的标题，并且已经知道了页码，您可以直接翻到该页并查看标题，而不是从头到尾扫描索引以查找所有出现的页码。 - Sam T

这是我最喜欢的关于这个主题的书，顺便提一下：https://www.amazon.com/Server-Execution-Plans-Grant-Fritchey/dp/1906434026。它直接涉及到MS SQL Server，但概念与MySQL和其他关系型数据库引擎几乎相同。 - Sam T

"在辅助索引中对应的'pk'在哪里找到？" — "请注意，在InnoDB中，主键包含在所有辅助索引中，因此从某种意义上说，所有辅助索引都是复合索引。" - poige

@poige 我的意思是，如果我正在寻找值为“123”的主键，那么在二级索引中我应该从哪里顺序查找这个“123”？数据库引擎并不知道，因为二级索引不是按照主键排序的。它必须从头到尾扫描每个条目。 - Sam T

1

这是我经常听到的问题，由于以下原因，这个问题存在很多混淆：

mySQL在多年间的差异。索引和多重索引支持随着时间的推移而发生了变化（趋向于被支持）
InnoDB / myISAM的差异有一些关键的区别（如下），但我不认为多重索引是其中之一

MyISAM比较老但经过验证。MyISAM表中的数据分为三个不同的文件：表格式、数据和索引。
InnoDB相对于MyISAM来说比较新，并且是事务安全的。InnoDB提供行锁定而不是表锁定，这增加了多用户并发性和性能。InnoDB还具有外键约束。
由于其行锁定功能，InnoDB非常适合高负载环境。

为了确保事情正确，请使用explain_plan来分析查询执行。

- Michael Durrant

你现在是否认为InnoDB已经被证明是可靠的？ - Pacerier

@Pacerier - 在回答发布之前，我可以说InnoDB已经“被证明”了。8.0版摆脱了MyISAM的影响。这也表明Oracle今天对InnoDB非常有信心。 - Rick James

-1

复合索引与组合索引不同。

组合索引覆盖筛选、连接和选择条件中的所有列。这些列将相应地存储在索引B树的所有页面上。
复合索引覆盖B树中所有筛选和连接关键列，但仅将选择列保留在叶子页面上，因为它们不会被搜索，而只是提取！这样可以节省空间，从而创建更少的索引页面，因此I/O速度更快。

- Christo Belokov

2

不。 "覆盖"索引包含在SELECT中使用的所有列。 "复合"或"组合"索引在索引中具有多个列。 - Rick James

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Johan · Accepted Answer

覆盖索引并不等同于复合索引。

如果我在col3和col4上分别创建了两个独立的索引，那么在这个查询中会使用哪一个索引？

会使用基数最高的索引。
MySQL会统计每个索引的属性。
使用具有最大区分度的索引（如MySQL的统计数据所示）。

我在某处读到，对于查询中的每个表只使用一个索引。这意味着查询无法同时使用两个索引吗？

您可以使用子查询。
或者更好的方法是使用包含col3和col4的复合索引。

其次，如果我创建了一个复合索引，将col3和col4组合在一起，但只在WHERE子句中使用了col3，那么性能会更差吗？例如：

复合索引
正确术语是 compound 索引，而不是 composite 索引。
只有复合索引的左边部分会被使用。
因此，如果索引定义为：

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子句中不使用该部分索引也可以实现查询。
假设我们有一个复合索引。

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。 什么是覆盖索引
当查询中选择的所有字段都被索引“覆盖”时，称为覆盖索引。在这种情况下，InnoDB（而不是MyISAM）永远不会读取表中的数据，而只使用索引中的数据，从而显著加速选择操作。
请注意，在InnoDB中，主键包含在所有二级索引中，因此所有二级索引都是复合索引。
这意味着如果在InnoDB上运行以下查询：

SELECT indexed_field FROM table1 WHERE pk = something

~~MySQL将始终使用覆盖索引，而不会访问实际表。~~虽然它可能使用覆盖索引，但它更喜欢使用PRIMARY KEY，因为它只需要命中一行。