索引和多列主键

Question

在MySQL数据库中，我有一张带有以下主键的表

PRIMARY KEY id (invoice, item)

在我的应用程序中，我还会经常仅选择item或仅选择invoice。我假设在这些列上建立索引会对我有益。 当我定义以下内容时，MySQL没有报错：

INDEX (invoice),
INDEX (item),
PRIMARY KEY id (invoice, item)

但是我没有看到任何证据（使用DESCRIBE -- 我知道的唯一查看方式）表明为这两列建立了单独的索引。

组成主键的列是否自动单独建立索引？有比DESCRIBE更好的方法来探索我的表结构吗？

- David Jenings

---

谢谢大家。像往常一样，这个慷慨的社区提供了很棒的信息。答案似乎集中在排序和选择上。对于选择来说，索引是否同样重要？我将使用返回1到~10行的select语句。在我的情况下，顺序并不重要。 - David Jenings

当查询返回少量行时，索引在选择时更为重要，在查询返回许多行时，索引在排序时更为重要...对于10行的排序来说是微不足道的，但找到它们却很困难。如果查询返回整个表（或其大部分），那么查找行并不是问题，但对这个大结果集进行排序则是一个问题... - Charles Bretana

Answer 1

我对mySql索引的内部机制并不非常熟悉，但在我熟悉的两个数据库供应商产品（MsSQL、Oracle）中，索引是平衡树（B-Tree）结构，其节点被组织为索引所定义的列的有序元组（按照定义的顺序）。请参阅注释。

所以，除非mySql的做法非常不同（可能不是这样），任何复合索引（多列索引）都可以被需要通过索引进行过滤或排序的查询使用，只要列的列表是兼容的。也就是说，如果这些列按照完整索引的顺序进行排序后，是完整索引列集合的有序子集，且从实际索引序列的开头开始，除了末尾可能有间隔之外，没有其他间隔...

换句话说，这意味着如果你在(a,b,c,d)上有一个索引，过滤条件为(a)、(a,b)或(a,b,c)的查询也可以使用该索引，但需要过滤条件为(b)、(c)、(b,c)或(a,c)的查询将无法使用该索引...

所以在你的情况下，如果你经常需要单独对列item进行过滤或排序，你需要在该列上添加另一个单独的索引...

注意：通常，使用平衡树这个术语时，暗示着树中的每个节点最多可以有两个子节点或分支。这被称为平衡二叉树。SQL Server中的索引只是平衡的非二叉B树，其中节点可以有多于两个的子节点或分支。索引优化器根据性能优化计算每个节点分配多少个分支。每个节点存在的子节点越少，树的高度（或深度）就越高。最佳分支数（最大化定位特定记录的性能）取决于总数据集域中索引属性的离散值数量。

- Charles Bretana

---

这正是MySQL的做法。这里有一个参考链接：http://dev.mysql.com/doc/refman/5.7/en/multiple-column-indexes.html - Nikhil Sahu

@Nikhil，谢谢您提供的参考！ - Charles Bretana

这并没有回答用户的问题，即在多个主键中使用单个列的索引是否必要。 - Ron Ross

是的，它确实有作用。请阅读最后一句话。之前的文本为理解答案奠定了基础。否则，我本可以只说“不”。 - Charles Bretana

@CharlesBretana 那内连接呢？哪个列（a或b或c或d）适合作为内连接条件？ - gaurav kumar

Answer 2

我个人使用phpMyAdmin来查看和编辑MySQL数据库的结构。它是一个Web应用程序，但在本地Web服务器上运行得足够好（我在我的机器上运行了一个Apache实例和phpPgAdmin）。

至于(invoice, item)的复合键，它像(invoice,item)和invoice的索引。如果您想按item建立索引，则必须自己添加该索引。您的主键将按invoice排序，然后按item排序，其中invoice在多条记录中相同。虽然复合主键的顺序对于唯一性强制执行没有影响，但对于访问却有影响。

在您的表上，我会使用：

PRIMARY KEY id (invoice, item), INDEX (item)

- George Steel

---

感谢您提供的信息和清晰易懂的答案，您为我节省了很多时间 :) - Accountant م

这应该被选为答案，因为它解释了多个主键的使用方式类似于索引，并使用左前缀，因此创建一个带有项目列的索引是有用而不是多余的，而为发票列创建索引将由于左前缀访问而成为多余的。 - Ron Ross

Answer 3

我不太熟悉MySQL，但通常情况下，多列索引在索引的第一列上与仅在该列上创建的索引一样有用。随着列越靠后出现在索引中，多列索引对单个列进行查询的效果会变得越来越不如预期。

如果将多列索引视为层次结构，则索引中的第一列是层次结构的根，因此搜索它只是扫描该第一级。然而，为了扫描第二列，数据库必须查找每个在第一列中找到的唯一值的树。这可能会非常昂贵，以至于大多数优化器不会深入查看多列索引，而是选择全表扫描。

例如，如果您有一个如下所示的表：

Col1 |Col2 |Col3
----------------
   A |   1 |   Z
   A |   2 |   Y
   A |   2 |   X
   B |   1 |   Z
   B |   2 |   X

假设您在这三个列上都有一个索引，按顺序排列，树的结构将如下所示：

A
+-1
  +-Z
+-2
  +-X
  +-Y
B
+-1
  +-Z
+-2
  +-X

查找Col1='A'很容易：您只需要查看2个有序值。然而，要解决col3='X'，您必须查看4个更大的桶中的所有值，每个桶都是单独排序的。

- Allan

Answer 4

为了返回表格索引信息，您可以使用以下方法：

SHOW INDEX FROM <table>;

参见：http://dev.mysql.com/doc/refman/5.0/en/show-index.html

查看表信息：

SHOW CREATE TABLE <table>;

请看：http://dev.mysql.com/doc/refman/5.0/en/show-create-table.html 主键是索引，因此不需要创建额外的索引。您可以在CREATE TABLE语法下找到更多信息（这里无法插入太多）。请参考：http://dev.mysql.com/doc/refman/5.0/en/create-table.html

- Mike

Answer 5

复合索引和复合主键是有区别的。 如果您定义了如下的复合索引：

INDEX idx(invoice,item)  

如果您基于item进行查询，则索引将无法工作，您需要添加一个单独的索引。

INDEX itemidx(item)  

但是，如果您定义了如下的复合主键

PRIMARY KEY(invoice, item)  

如果您的查询基于 item，则索引将起作用，无需单独的索引。

工作示例：

mysql>create table test ( col1 int(20), col2 int(20) ) primary key(col1,col2);
mysql>explain select * from test where col2 = 1;
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra                    |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
|  1 | SIMPLE      | test  | index | NULL          | PRIMARY | 8       | NULL |   10 | Using where; Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

- cppcoder

---

这个答案似乎与所选答案和https://dba.stackexchange.com/questions/14256/how-is-index-on-composite-primary-key-in-mysql相矛盾。 - Rich

这是错误的，不正确的。 - Charles Bretana

Answer 6

如果您的查询在where子句中同时使用了这两个列，则不需要在复合主键中创建单独的索引。

EXPLAIN SELECT * FROM `table` WHERE invoice = 1 and item = 1

如果您只想查询第一列，也可以。

EXPLAIN SELECT * FROM `table` WHERE invoice = 1

但是，如果您想要使用复合PK中的后续列col2、col3进行查询，则需要在这些列上创建单独的索引。以下解释查询显示第二列没有被MySQL检测到可能的键。

EXPLAIN SELECT * FROM `table` WHERE item = 1

- Syed Waqas Bukhary

Answer 7

Mysql会自动为复合键创建索引。根据您的查询，您可能需要为复合键中的每个列创建单独的索引。

如果您正在使用mysql workbench，您可以手动右键单击模式并单击编辑以查看有关表的所有内容。

- valentine