首页 文章

索引MySQL表以获得最佳查询的替代方法?

提问于
浏览
2

我很困惑如何最好地索引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 回答

  • 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) 上的唯一键强制数据完整性 .

  • 0

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

    因此,如果您在 col1 上创建单独的索引到 colN ,则可以运行此查询:

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

    这将导致 col2col3 上的索引合并加入(您将在 EXPLAIN 输出中将其视为 index_merge using union(col2, col3) ) .

    为了确保唯一性,将第一列声明为 PRIMARY KEY 就足够了,因此只要您保持数据一致性(PK值确实是连接和分离的 col* 值),您的数据唯一性将由PK监管 .

相关问题