索引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 A单一指数）创建一个索引每列（col1），（col2）...（col-n）

c）创建一个索引每列，包含pk（pk，col1），（pk，col2），（pk， col-n）

任何帮助你可以提供非常感谢。

由于

菲尔

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中单个表内的多个索引。

所以，如果你在col1创建单独的索引到colN，您可以运行此查询：

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

，这将导致对col2和col3合并索引加入（你会看到它为index_merge using union(col2, col3)在EXPLAIN输出）。为了确保唯一性，只需要声明第一列PRIMARY KEY就足够了，所以只要保持数据一致性（PK值确实是连接和分隔的col*值），您的数据唯一性将由PK进行管理。