索引和多列主键 [英] Indexes and multi column primary keys

问题描述

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

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

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

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

PRIMARY KEY id（发票，项目）

PRIMARY KEY id (invoice, item)

在我的申请中，我也经常单独选择项目，而不仅仅选择发票。我假设我会从这些列上的索引中受益。

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不会抱怨：

MySQL does not complain when I define the following:

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

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

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

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?

推荐答案

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

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做得非常不同，（可能不是），任何复合索引（在多个列） 可以被任何需要按索引中列的 子集 过滤或排序的查询使用因为列的列表是兼容的，即，如果列与完整索引中的列的顺序列表相同，则列是完整索引列集的有序子集，它从实际索引的开头开始顺序，没有间隙ex结束时...

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

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

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

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

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

这篇关于索引和多列主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！