我很困惑如何最好地索引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)为每个col创建一个索引,包括pk(pk,col1),(pk,col2),(pk,col-n)
非常感谢您提供的任何帮助 .
谢谢
菲尔
2 回答
如果
WHERE
子句包含第一列的条件,则只能使用(col1, col2, col3, col4)
上的索引 . 这意味着,如果查询在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)
上的唯一键强制数据完整性 .MySQL
能够在PK上的单个表中合并多个索引,只要您搜索确切的键值(不是范围) .因此,如果您在
col1
上创建单独的索引到colN
,则可以运行此查询:这将导致
col2
和col3
上的索引合并加入(您将在EXPLAIN
输出中将其视为index_merge using union(col2, col3)
) .为了确保唯一性,将第一列声明为
PRIMARY KEY
就足够了,因此只要您保持数据一致性(PK值确实是连接和分离的col*
值),您的数据唯一性将由PK监管 .