代码之家 › 专栏 › 技术社区 › yceruto

MySQL查询性能非常慢

mysql-5.7 query-performance mysql

yceruto · 技术社区 · 6 年前

我有个疑问 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 桌子周围有 280万 记录,其中 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只需要3秒,我尝试替换它做一些疯狂的事情:

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秒…伟大的!但这是一个黑客。

为什么 当我使用 在 声明?解决方法、提示、链接等。谢谢!

3 回复 | 直到 6 年前

danblack 6 年前

MySQL可以一次使用一个索引。对于这个查询,您需要一个涵盖搜索方面的复合索引。WHERE子句的常量方面应在范围方面之前使用,例如:

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

Rick James 6 年前

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

正在检查是否将索引用于 GROUP BY :

功能( YEAR() 在 小组通过 所以没有。
多个表( c 和 p )提到过,所以没有。

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

如果启动具有 磷 :

假设你有 PRIMARY KEY(id) 没什么可想的。它只会使用那个索引。

对于从中选择的每一行 磷 然后它会调查 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".

优化器将查看“statistics”,粗略地决定从哪个表开始。所以,添加我建议的所有索引。

“覆盖”索引包含 全部的 需要的列 在任何地方 在查询中。它是有时明智的做法是用更多的列扩展“好”索引,使其“覆盖”。

但这里有个活动扳手。 c.customer_id = p.id 意味着 customer_id IN (...) 有效存在。但现在有两个“类似范围”的约束——一个是 IN ,另一个是“范围”。在一些较新的版本中,优化器会很高兴地跳转,因为 在 和仍然能够进行“范围”扫描。因此,我建议您订购:

测试 column = constant
测试与 在
一 “距离”试验 BETWEEN , >= , LIKE 带尾随通配符等)
也许可以添加更多的列使其“覆盖”——但是如果索引中的列超过(比如说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)

因为有一个 在 或者“范围”,看索引是否也可以处理 分组依据 .

关于…的笔记 COUNT(x) --它检查那个 x 是 NOT NULL . 它是通常说得对 COUNT(*) ,它计算行数,而不进行任何额外的检查。

因为它隐藏了索引列,所以它不是起始列( id )在函数中:

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

fifonik 6 年前

使用类似的黑客程序,您正在欺骗优化器,因此它使用不同的计划(最可能的做法是首先使用idx f7146f0aa9e377a索引)。你应该能在解释中看到这一点。

我认为在您的案例中,真正的问题是第二行解释:服务器对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_i d、company_i d、source和date列上添加覆盖索引。不确定最佳顺序,因为它取决于您拥有的数据(检查这些列的基数)。我从索引(日期、公司ID、来源、客户ID)开始。另外,我会在合作伙伴上添加唯一索引(i d,District_i d,Owner_i d)。

还可以添加其他生成的存储列(年份和月份)(如果服务器有点旧,可以添加普通列并用触发器填充它们)以消除多个函数执行。