为MySQL表建立索引以实现最佳查询的替代方法？

Question

我对如何最好地在MySQL中为表建立索引感到困惑，并且需要关于使用的最佳索引构造类型的帮助。目前，我在这个表上使用了唯一键索引，但不知道这是否是最好的方法，在某些情况下，由于MySQL的限制，我不能使用这种类型的索引。

该表由一个主键和n列组成，在此场景中，为了使其保持简单的n=4，该表如下: pk，col1，col2，col3，col4

col1-n中的值是VARCHAR，通常长度在1到4个字符之间。主键是col值的串联。因此，典型的行可能如下所示：

A:B:C:D, A, B, C, D
A:B:C:E, A, B, C, E
A:B:F:F, A, B, F, F

其中第一个元素是主键，后续元素是col1、col2等。

表需要针对查询而不是插入进行优化。我希望执行的查询将有一个WHERE子句，其中我们知道列1-4中的一些值。因此，例如，我可能想要查找第二列为'B‘或'C’的所有行。一旦我有了主键，我就用它来连接另一个表。

我在col1-4上创建了一个唯一的键(因为它们是唯一的)。问题是，一旦n变得很大(> 16 )，我就不能再创建唯一键索引(对于唯一键约束，MySQL限制为16列)。这不是问题，因为主键确保了唯一性。然而，我不确定两件事：

a)为了优化查询速度，唯一键是一个很好的索引吗？

b)当我不能使用唯一键时，我应该使用什么索引？

我有以下选项，但我不确定哪一个(如果有)是最好的：

a)在(col1，col2，col3，col4)上创建单个索引

b)创建每列索引(col1)，(col2)…(col-n)

c)每列创建一个索引，其中包含主键( pk，col1)，(pk，col2)，(pk，col2)

我们非常感谢您能提供的任何帮助。

谢谢

菲尔

Answer 1

只有当(col1, col2, col3, col4)子句包含第一列的条件时，才能使用WHERE索引。这意味着，如果查询不包含col1上的条件，则根本不能使用索引(请参见Multiple-Column Indexes)。如果你有这样的查询，应该定义额外的索引。这些可能是(col2, col3, col4)、(col3, col4)和(col4)。

另一方面，(col1)、(col2)、(col3)和(col4)上的单独索引也是一个很好的选择。在这种情况下，不需要在索引中包括主键。与上面提到的解决方案相比，我更喜欢这个解决方案。

我发现你对主键的选择很奇怪。如果(col1, col2, col3, col4)是唯一的，则将其用作主键。如果您不想在四列上使用主键(大多数人不需要)，那么下一个选择通常是代理键(即MySQL中的auto_increment列)。在这种情况下，(col1, col2, col3, col4)上的唯一键将强制数据完整性。

Answer 2

只要您正在搜索确切的键值(而不是范围)，MySQL就能够在PK上合并单个表中的多个索引。

因此，如果您在colN的col1上创建单独的索引，则可以运行以下查询：

SELECT  *
FROM    mytable
WHERE   col2 = 'B'
        OR
        col3 = 'C'

这将导致col2和col3合并连接上的索引(您将在EXPLAIN输出中看到它为index_merge using union(col2, col3) )。

为了保证唯一性，只要你的第一列声明为PRIMARY KEY就足够了，所以只要你保持数据的一致性(pk值实际上是连接和分隔的col*值)，你的数据唯一性就会受到PK的监督。