mysql - 以下场景需要哪些索引

Question

我有一个表格，其中包含一次性优惠券代码的库存。每行都是一个单独的代码。为简单起见，表格如下所示:

| id (AUTO_INCREMENTING) | owner (VARCHAR) | brand (VARCHAR) | currency (CHAR) | value (INTEGER) | activated_at (TIMESTAMP) | expires_at (DATE) |
|------------------------|-----------------|-----------------|-----------------|-----------------|--------------------------|-------------------|
| 1                      | first-owner     | brand-one       | gbp             | 10              | NULL                     | 2018-06-06        |
| 2                      | second-owner    | brand-one       | gbp             | 15              | NULL                     | 2018-06-06        |
| 3                      | second-owner    | brand-one       | gbp             | 15              | NULL                     | 2018-06-06        |

我知道我可以规范化表并将所有字段提取到它们自己的表中，并且让 stock 表只包含 ids，但是到目前为止尚未证明可以提高性能，并且为了简单性和用户可读性，我倾向于保持原样。

查询此表时，我希望能够有选择地传递所有者、品牌或两者。

对于以下查询，我需要在此表上使用哪些索引？为什么？

查询表格而不提供所有者或品牌:

SELECT
    `owner`,
    `brand`,
    `currency`,
    `value`,
    count(*) AS 'quantity_of_codes',
    (count(*) * `value`) AS 'total_face_value'
FROM `inventory`
WHERE `activated_at` IS NULL
AND `expires_at` >= '2018-06-05'
GROUP BY `owner`, `brand`, `currency`, `value`

如果我运行 explain 我可以看到使用了索引:

| select_type | table     | type | possible_keys | key | key_len | ref   | rows   | Extra                                                     |
|-------------|-----------|------|---------------|-----|---------|-------|--------|-----------------------------------------------------------|
| SIMPLE      | inventory | ref  |               |     | 766     | const | 416144 | Using where; Using index; Using temporary; Using filesort |

当我提供一个 owner 时，会使用索引，但它会创建一个临时表并使用 filesort，我认为这不太好。

SELECT
    `brand`,
    `owner`,
    `currency`,
    `value`,
    count(*) AS 'quantity_of_codes',
    (count(*) * `value`) AS 'total_face_value'
FROM `inventory`
WHERE `owner` = 'first-owner'
AND `activated_at` IS NULL
AND `expires_at` >= '2017-06-06'
GROUP BY `brand`, `owner`, `currency`, `value`

运行 explain 显示:

| select_type | table     | type  | possible_keys                                            | key                                                      | key_len | ref | rows | Extra                                                     |
|-------------|-----------|-------|----------------------------------------------------------|----------------------------------------------------------|---------|-----|------|-----------------------------------------------------------|
| SIMPLE      | inventory | index | brand_owner_currency_value_activated_at_expires_at_index | brand_owner_currency_value_activated_at_expires_at_index | 2310    |     | 87   | Using where; Using index; Using temporary; Using filesort |

当我还提供一个品牌时，我得到:

SELECT
    `brand`,
    `owner`,
    `currency`,
    `value`,
    count(*) AS 'quantity_of_codes',
    (count(*) * `value`) AS 'total_face_value'
FROM `inventory`
WHERE `owner` = 'first-owner'
AND `brand` = 'brand-one'
AND `activated_at` IS NULL
AND `expires_at` >= '2018-06-05'
GROUP BY `brand`, `owner`, `currency`, `value`

最后，当我运行 explain 时，我得到:

| select_type | table     | type | possible_keys                                            | key                                                      | key_len | ref | rows | Extra                                                     |
|-------------|-----------|------|----------------------------------------------------------|----------------------------------------------------------|---------|-----|------|-----------------------------------------------------------|
| SIMPLE      | inventory | ref  | brand_owner_currency_value_activated_at_expires_at_index | brand_owner_currency_value_activated_at_expires_at_index | 1532    |     | 1    | Using where; Using index; Using temporary; Using filesort |

如前所述，我有一个涵盖品牌、所有者、货币、值(value)、activated_at 和 expires_at 字段，尽管当我不在查询的 where 部分包含 brand 时不会使用它。

Answer 1

http://use-the-index-luke.com/是一个很好的引用，可以帮助你熟练地设计索引。

通过为此类查询选择复合索引，您走在了正确的轨道上。您的查询包含这些 WHERE 子句项。

 WHERE `owner` = 'an-owner'
   AND `activated_at` IS NULL
   AND `expires_at` >= '2017-06-06'

您在第一项上寻找相等性，在第二个上寻找 IS NULL，在第三个上寻找范围扫描。因此，(owner, activated_at, expires_at) 上的索引可以让查询计划程序跳转到正确的行，然后扫描索引以查找连续的行。 (activated_at, owner, expires_at) 上的索引也将起作用。

您在 (brand, owner, currency, value, _activated_at, _expires_at) 上的索引无法针对您显示的查询进行范围扫描。所以查询规划器会忽略它。

专业提示:不要仅仅因为它们看起来很有用就为表创建索引。设计它们以加快您需要加快的查询速度。

专业提示#2:使用临时；使用 filesort 并不是性能不佳的明确标志。 临时 意味着一些结果集需要放入服务器中一个临时的类似表的数据结构中。 filesort 表示结果集在传递前需要排序。当您使用 GROUP BY 时，查询规划器通常需要一个临时 结构来完成您的查询。读这个。 https://www.percona.com/blog/2009/03/05/what-does-using-filesort-mean-in-mysql/

你的 EXPLAIN 输出对我来说看起来非常好。一排？太好了，