非常慢的MySQL查询性能

Question

我有一个关于18秒的查询：

查询：

SELECT YEAR(c.date), MONTH(c.date), p.district_id, COUNT(p.owner_id)
FROM commission c
  INNER JOIN partner p ON c.customer_id = p.id
WHERE (c.date BETWEEN '2018-01-01' AND '2018-12-31')
  AND (c.company_id = 90)
  AND (c.source = 'ACTUAL')
  AND (p.id IN (3062, 3063, 3064, 3065, 3066, 3067, 3068, 3069, 3070, 3071,
    3072, 3073, 3074, 3075, 3076, 3077, 3078, 3079, 3081, 3082, 3083, 3084,
    3085, 3086, 3087, 3088, 3089, 3090, 3091, 3092, 3093, 3094, 3095, 3096,
    3097, 3098, 3099, 3448, 3449, 3450, 3451, 3452, 3453, 3454, 3455, 3456,
    3457, 3458, 3459, 3460, 3461, 3471, 3490, 3491, 6307, 6368, 6421))
  GROUP BY YEAR(c.date), MONTH(c.date), p.district_id

commission表拥有约2800万的记录，其中860 000+属于当前2018年。此时，partner表有8600+记录。

结果

| `YEAR(c.date)` | `MONTH(c.date)` | district_id | `COUNT(c.id)` | 
|----------------|-----------------|-------------|---------------| 
| 2018           | 1               | 1           | 19154         | 
| 2018           | 1               | 5           | 9184          | 
| 2018           | 1               | 6           | 2706          | 
| 2018           | 1               | 12          | 36296         | 
| 2018           | 1               | 15          | 13085         | 
| 2018           | 2               | 1           | 21231         | 
| 2018           | 2               | 5           | 10242         | 
| ...            | ...             | ...         | ...           | 

55 rows retrieved starting from 1 in 18 s 374 ms 
(execution: 18 s 368 ms, fetching: 6 ms)

解释：

| id | select_type | table | partitions | type  | possible_keys                                                                                        | key                  | key_len | ref             | rows | filtered | extra                                        | 
|----|-------------|-------|------------|-------|------------------------------------------------------------------------------------------------------|----------------------|---------|-----------------|------|----------|----------------------------------------------| 
| 1  | SIMPLE      | p     | null       | range | PRIMARY                                                                                              | PRIMARY              | 4       |                 | 57   | 100      | Using where; Using temporary; Using filesort | 
| 1  | SIMPLE      | c     | null       | ref   | UNIQ_6F7146F0979B1AD62FC0CB0F5F8A7F73,IDX_6F7146F09395C3F3,IDX_6F7146F0979B1AD6,IDX_6F7146F0AA9E377A | IDX_6F7146F09395C3F3 | 5       | p.id            | 6716 | 8.33     | Using where                                  | 

DDL:

create table if not exists commission (
    id int auto_increment
        primary key,
    date date not null,
    source enum('ACTUAL', 'EXPECTED') not null,
    customer_id int null,
    transaction_id varchar(255) not null,
    company_id int null,
    constraint UNIQ_6F7146F0979B1AD62FC0CB0F5F8A7F73 unique (company_id, transaction_id, source),
    constraint FK_6F7146F09395C3F3 foreign key (customer_id) references partner (id),
    constraint FK_6F7146F0979B1AD6 foreign key (company_id) references companies (id)
) collate=utf8_unicode_ci;
create index IDX_6F7146F09395C3F3 on commission (customer_id);
create index IDX_6F7146F0979B1AD6 on commission (company_id);
create index IDX_6F7146F0AA9E377A on commission (date);

我注意到，通过删除合作伙伴IN条件，MySQL只需要3s。我试着替换它，做这样疯狂的事情：

AND (',3062,3063,3064,3065,3066,3067,3068,3069,3070,3071,3072,3073,3074,3075,3076,3077,3078,3079,3081,3082,3083,3084,3085,3086,3087,3088,3089,3090,3091,3092,3093,3094,3095,3096,3097,3098,3099,3448,3449,3450,3451,3452,3453,3454,3455,3456,3457,3458,3459,3460,3461,3471,3490,3491,6307,6368,6421,'
     LIKE CONCAT('%,', p.id, ',%')) 

结果是大约5秒..。太棒了！但这是个黑客。

为什么使用IN语句时执行时间很长？解决办法，提示，链接等。谢谢！

Answer 1

MySQL一次可以使用一个索引。对于这个查询，您需要一个包含搜索各个方面的复合索引。WHERE子句的常量方面应在范围方面之前使用，如：

ALTER TABLE commission
DROP INDEX IDX_6F7146F0979B1AD6,
ADD INDEX IDX_6F7146F0979B1AD6 (company_id, source, date)

Answer 2

以下是优化器在查询中看到的内容。

检查是否为GROUP BY使用索引

• 函数(YEAR())在GROUP BY中，所以没有。
• 提到了多个表(c和p)，所以没有。

对于一个JOIN，优化器将(几乎总是)从一个开始，然后延伸到另一个。那么，让我们看看这两个选项：

如果从p开始

假设您有PRIMARY KEY(id)，就没有什么可考虑的了。它将简单地使用该索引。

对于从p中选择的每一行，它将查看c，并且这个INDEX的任何变化都是最优的。

c: INDEX(company_id, source, customer_id,  -- in any order (all are tested "=")
         date)       -- last, since it is tested as a range

如果从c开始

c: INDEX(company_id, source,  -- in any order (all are tested "=")
         date)       -- last, since it is tested as a range
-- slightly better:
c: INDEX(company_id, source,  -- in any order (all are tested "=")
         date,       -- last, since it is tested as a range
         customer_id)  -- really last -- added only to make it "covering".

优化器将查看“统计数据”，粗略地决定从哪个表开始。因此，添加我建议的所有索引。

“覆盖”索引包含查询中任何地方所需的所有列。有时，明智的做法是扩展一个“好”索引，增加更多列，使其“覆盖”。

但这里有个猴子扳手。c.customer_id = p.id意味着customer_id IN (...)的有效存在。但现在有两个“范围-类似”的约束-一个是一个IN，另一个是‘范围’。在一些较新的版本中，优化器将高兴地跳来跳去，因为IN仍然能够进行“范围”扫描。所以，我推荐这个订单：

1. column = constant测试
2. 用IN进行测试
3. 一个“范围”测试(BETWEEN、>=、带尾通配符的LIKE等)
4. 也许添加更多的列以使其“覆盖”--但是如果索引中有超过5列，则不要执行此步骤。

因此，对于c，下面的内容对于WHERE来说是最优的，并且恰好是“覆盖”。

INDEX(company_id, source,  -- first, but in any order (all "=")
      customer_id,  -- "IN"
      date)       -- last, since it is tested as a range

p: (same as above)

因为有一个IN或“范围”，所以查看索引是否也可以处理GROUP BY是没有用的。

COUNT(x)上的一张便条--它检查x是NOT NULL。通常来说，COUNT(*)也是正确的，它不需要任何额外的检查就可以计算行数。

这是不可能的，因为它将索引列(id)隐藏在函数中：

AND (',3062,3063,3064,3065,3066,...6368,6421,'
     LIKE CONCAT('%,', p.id, ',%'))

Answer 3

通过类似的攻击，您正在欺骗优化器，因此它使用了不同的计划(最可能首先使用IDX_6F7146F0AA9E377A索引)。你应该能在解释中看到这个。

我认为在您的例子中真正的问题是解释的第二行:服务器对6716行执行多个函数(月、年)，然后尝试对所有这些行进行分组。在此期间，所有这6716行都应该存储(存储在基于服务器配置的内存或磁盘中)。

SELECT COUNT(*) FROM commission WHERE (date BETWEEN '2018-01-01' AND '2018-12-31') AND company_id = 90 AND source = 'ACTUAL';

=>，我们说的是多少行？

如果上面的查询中的数字比6716低得多，我会尝试在列customer_id、company_id、source和date上添加覆盖索引。不确定最佳顺序，因为它取决于您拥有的数据(检查这些列的基数)。我从索引开始(日期、company_id、源代码、customer_id)。此外，我还会在partner上添加唯一索引(id、district_id、owner_id)。

还可以添加额外生成的存储列_year和_month (如果服务器有点老了，可以添加普通列并使用触发器填充它们)，以消除多个函数的执行。