主页 / dba / 问题 / 205182
Accepted
Eaten by a Grue
Asked: 2018-04-28 07:31:57 +0800 CST

复合索引的顺序

  • 772

假设我有这样的查询：

SELECT *
FROM table_a
    JOIN table_b USING (id)
WHERE table_b.column = 1

我有一个索引id和一个索引，column但我经常添加一个复合索引，两者都可以提高这样的查询效率。我的问题是关于索引中列的顺序。通过反复试验，我发现有时 DBMS 更喜欢连接索引，有时它更喜欢WHERE索引。

在上面的查询中，我可以遵守一个硬性、快速的规则来知道哪个键顺序最有效？

通常我只是添加两个索引，EXPLAIN在查询上运行并检查哪个是首选，然后删除另一个。但是这个过程感觉可以通过更好地理解确定索引顺序所涉及的逻辑来改进。

mysql optimization

3 个回答

  1. 对于这个查询

    SELECT *
FROM table_a
    JOIN table_b USING (id)
WHERE table_b.column = 1

    最佳方式是执行它是

    1. 该WHERE子句提供了一些过滤，所以让我们利用它。也就是说，有一个以table_b 开头 column的索引。（稍后我们将讨论是否将其复合。）因此，优化器将使用该索引来查找table_b.
    2. 对于这些行中的每一行，JOIN到table_a. （注意JOIN, notLEFT JOIN被使用；LEFT JOIN是另一回事。）
    3. 要进入table_a，需要一个以 开头的索引id。（注： USING(id)意思是table_a.id = table_b.id。）

    到目前为止，我们有

    b:  INDEX(column)
a:  INDEX(id)   -- though it probably exists as PRIMARY KEY(id)

    覆盖？

    我们不知道这两个表中还有哪些其他列。如果列很少，那么构建“覆盖”索引可能很诱人。这是一个索引，其中包含SELECT. 好处是通过仅查看索引的 BTree 而不必触及数据 BTree 来提高性能。

    因为table_b，这会很诱人INDEX(column, id)。如果只有这两列，那会很好（并且“覆盖”）。但可能有更多的列。所以，这可能INDEX(column)就是所有值得做的事情。

    对于table_a，我假设它id是PRIMARY KEY（根据定义，它是唯一的和索引）。所以那里不需要更多的东西。

    底线：使用上面列出的两个单列索引。

    这个例子并没有举例说明“复合”索引。 有关更多信息，请参阅

    基数和范围
    基数和复合
    单列索引
    索引食谱

    但我经常添加一个复合索引，两者都可以提高这样的查询效率......

    更好的例子

    正如我所说，你的例子并没有说明这个问题。那么，我将尝试回答“何时应该使用复合索引”？有很多情况（见链接）；我给你一个简单的案例。

    WHERE x = 1
  AND y > 2

    相关特征是：

    • x并且y在同一张桌子上。（不能跨两个表建立索引。）
    • AND用来。（OR无法优化。）
    • 其中一项测试是使用=. （如果两者都是范围，复合将无济于事。）
    • y是一个“范围”（例如： y>2、、、y LIKE 'm%'）y BETWEEN ... AND ...。

    一般规则是：

    1. 将所有=列放在首位（x在我的示例中）
    2. 将一个范围列放在最后 ( y)

    也就是说，您必须订购它INDEX(x,y)。

    对于WHERE x = 1 AND y = 2（两者=），您是否拥有或都没有关系。INDEX(x,y)INDEX(y,x)

    另一个花絮：使用ENGINE=InnoDB，PRIMARY KEY列被隐式附加到每个辅助键上。因此，您INDEX(column)的与 相同INDEX(column, id)。但是这个事实在这个讨论中并没有发挥作用。

    我意识到我在这里（和其他地方）不同意其他答案，但我坚持自己的立场。

    • 5
  2. Best Answer

    一个好的经验法则是使复合索引中的前导列尽可能具有选择性。一个很好的想象方法是用电话簿类比：想象你需要在电话簿中找到某人，并且有两个索引......第一个是姓氏，名字。第二个是名字，姓氏。您会使用哪个索引来查找名叫 John Xylophone 的人？当然，您会使用 LastName, Firstname 索引，因为 Xylophone 条目很少，而且与查看所有 John 条目以查找姓氏为 Xylophone 的条目相比，所花费的时间要少得多。

    因此，如果id是高选择性且选择性column低，您希望索引为(id, column)，但是如果column选择性高且选择性id低，您可能会受益于将索引定义为(column, id)。

    (column, id)如果您将两个表连接id在一起where column = x，您可能会看到一个正在使用的索引，此时需要连接x的行数大大减少。

    • 4

  3. 在上面的查询中，我可以遵守一个硬性、快速的规则来知道哪个键顺序最有效？

    在您给出的示例中，如果您可以自由更改连接顺序，最好的选择是根本没有复合索引：

    create table table_a(id integer, dummy_a integer);
create index index_a on table_a(id);
create table table_b(id integer, col integer, dummy_b integer);
create index index_b on table_b(col);

    explain select * from table_b join table_a using(id) where table_b.col=1;

    编号 | 选择类型 | 表| 隔断 | 类型 | 可能的键 | 关键 | key_len | 参考 | 行 | 过滤 | 额外的      
-: | :------------ | :-------- | :--------- | :--- | :------------ | :-------- | :-------- | :------------------------------------------------- | ---: | --------: | :----------
 1 | 简单 | 表_b | 空       | 参考 | 索引_b | 索引_b | 5 | 常量 | 1 | 100.00 | 使用哪里
 1 | 简单 | 表_a | 空       | 参考 | index_a | index_a | 5 | fiddle_YRFDITQONPXNRMDBQSYV.table_b.id | 1 | 100.00 | 无效的

    db<>在这里摆弄

    • 2

相关问题