索引和多列主键

Went searching and didn't find the answer to this specific noob question. My apologies if I missed it.

没有找到这个特定的菜鸟问题的答案。如果我错过了，我道歉。

In a MySQL database I have a table with the following primary key

在MySQL数据库中，我有一个包含以下主键的表

PRIMARY KEY id (invoice, item)

PRIMARY KEY id（发票，项目）

In my application I will also frequently be selecting on "item" by itself and less frequently on only "invoice". I'm assuming I would benefit from indexes on these columns.

在我的应用程序中，我还经常选择“项目”本身，而不是经常选择“发票”。我假设我会从这些列的索引中受益。

MySQL does not complain when I define the following:

当我定义以下内容时，MySQL不会抱怨：

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

INDEX（发票），INDEX（项目），PRIMARY KEY id（发票，项目）

But I don't see any evidence (using DESCRIBE -- the only way I know how to look) that separate indexes have been established for these two columns.

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

So the question is, are the columns that make up a primary key automatically indexed individually? Also, is there a better way than DESCRIBE to explore the structure of my table?

所以问题是，构成主键的列是否会自动单独索引？另外，有没有比DESCRIBE更好的方法来探索我的桌子的结构？

5 个解决方案

#1

I'm not intimately familiar with the internals of indices on mySql, but on the two database vendor products that I am familiar with (MsSQL, Oracle) indices are balanced-Tree structures, whose nodes are organized as a sequenced tuple of the columns the index is defined on (In the Sequence Defined)

我对mySql上的索引内部并不是很熟悉，但是我熟悉的两个数据库供应商产品（MsSQL，Oracle）索引是平衡树结构，其节点被组织为列的有序元组。 index定义于（在序列中定义）

So, unless mySql does it very differently, (probably not), any composite index (on more than one column) can be useable by any query that needs to filter or sort by a subset of the columns in the index, as long as the list of columns is compatible, i.e., if the columns, when sequenced the same as the sequenced list of columns in the complete index, is an ordered subset of the complete set of index columns, which starts at the beginning of the actual index sequence, with no gaps except at the end...

因此，除非mySql以非常不同的方式（可能不是）执行，否则任何复合索引（在多个列上）都可以由任何需要按索引中的列子集进行筛选或排序的查询使用，只要列列表是兼容的，即，如果列与完整索引中的列的顺序列表相同，则列是完整索引列集的有序子集，从实际索引序列的开头开始，没有差距，除了最后......

In other words, this means that if you have an index on (a,b,c,d) a query that filters on (a), (a,b), or (a,b,c) can also use the index, but a query that needs to filter on (b), or (c) or (b,c) will not be able to use the index...

换句话说，这意味着如果在（a，b，c，d）上有索引，则对（a），（a，b）或（a，b，c）进行过滤的查询也可以使用索引，但是需要在（b），或（c）或（b，c）上过滤的查询将无法使用索引...

So in your case, if you often need to filter or sort on column item alone, you need to add another index on that column by itself...

因此，在您的情况下，如果您经常需要单独对列项进行过滤或排序，则需要在该列上添加另一个索引...

#2

I personally use phpMyAdmin to view and edit the structure of MySQL databases. It is a web application but it runs well enough on a local web server (I run an instance of apache on my machine for this and phpPgAdmin).

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

As for the composite key of (invoice, item), it acts like an index for (invoice, item) and for invoice. If you want to index by just item you have to add that index yourself. Your PK will be sorted by invoice and then by item where invoice is the same in multiple records. While the order in a composite PK does not matter for uniqueness enforcement, it does matter for access.

对于（发票，项目）的复合关键字，它的作用类似于（发票，项目）和发票的索引。如果您只想按项目索引，则必须自己添加该索引。您的PK将按发票排序，然后按发票在多个记录中相同的项目排序。虽然复合PK中的顺序与唯一性实施无关，但它对访问很重要。

On your table I would use:

在你的桌子上我会使用：

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

#3

To return table index information, you can use:

要返回表索引信息，您可以使用：

SHOW INDEX FROM <table>;

See: http://dev.mysql.com/doc/refman/5.0/en/show-index.html

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

To view table information:

要查看表信息：

SHOW CREATE TABLE <table>;

See: http://dev.mysql.com/doc/refman/5.0/en/show-create-table.html

请参阅：http：//dev.mysql.com/doc/refman/5.0/en/show-create-table.html

Primary keys are indexes, so there's no need to create additional indexes. You can find out more information about them under the CREATE TABLE syntax (there's too much to insert here):

主键是索引，因此无需创建其他索引。您可以在CREATE TABLE语法下找到有关它们的更多信息（这里插入的内容太多了）：

http://dev.mysql.com/doc/refman/5.0/en/create-table.html

#4

I'm not that familiar with MySQL, but generally an multiple-column index is equally useful on the first column in the index as an index on that column alone. The multiple-column index becomes less useful for querying against a single column the further the column appears into the index.

我对MySQL并不熟悉，但通常多列索引在索引的第一列上同样有用，仅作为该列的索引。对于单个列，多列索引变得不那么有用，列进一步显示在索引中。

This makes some sense if you think of the multi-column index as a hierarchy. The first column in the index is the root of the hierarchy, so searching it is just a matter of scanning that first level. However, in order to scan the second column, the database has to look up the tree for each unique value found in the first column. This can be costly enough that most optimizers won't bother to look deeply into a multi-column index, instead opting to full-table-scan.

如果您将多列索引视为层次结构，则这有一定意义。索引中的第一列是层次结构的根，因此搜索它只是扫描第一级的问题。但是，为了扫描第二列，数据库必须为第一列中找到的每个唯一值查找树。这可能足够昂贵，大多数优化器都不会费心深入研究多列索引，而是选择全表扫描。

For example, if you have a table as follows:

例如，如果您有一个表格如下：

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

Assuming you have an index on all three columns, in order, the tree will look something like this:

假设您在所有三列上都有一个索引，按顺序，树将看起来像这样：

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

Looking for Col1='A' is easy: you only have to look at 2 ordered values. However, to resolve col3='X', you have to look at all of the values in the 4 bigger buckets, each of which is ordered individually.

寻找Col1 ='A'很简单：您只需要查看2个有序值。但是，要解决col3 ='X'，您必须查看4个较大存储桶中的所有值，每个存储桶都是单独订购的。

#5

There is a difference between composite index and composite primary key. If you have defined a composite index like below

复合索引和复合主键之间存在差异。如果您已定义了如下所示的复合索引

INDEX idx(invoice,item)

the index wont work if you query based on item and you need to add a separate index

如果基于项目进行查询并且需要添加单独的索引，则索引将不起作用

INDEX itemidx(item)

But, if you have defined a composite primary key like below

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

PRIMARY KEY(invoice, item)

the index would work if you query based on item and no separate index is required.

如果基于项目查询并且不需要单独的索引，则索引将起作用。

Working example:

工作范例：

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 |
+----+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+

#1