mysql查询性能非常慢

1wnzp6jl 于 2021-06-19 发布在 Mysql

关注(0)|答案(3)|浏览(321)

我有一个大约需要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万条记录，其中86万条以上属于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秒。。。太好了！但这是一个黑客。
为什么当我使用 IN 声明？解决方法，提示，链接等。谢谢！

mysql query-performance mysql-5.7

来源：https://stackoverflow.com/questions/53403008/very-slow-mysql-query-performance

3条答案

按热度按时间

zwghvu4y1#

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

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

赞(0）回复(0）举报 2021-06-19

bq8i3lrv2#

使用like hack，您正在欺骗optimizer，因此它使用不同的计划（最有可能首先使用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\u id、company\u id、source和date列上添加覆盖索引。不确定最佳顺序，因为它取决于您拥有的数据（检查这些列的基数）。我从索引（日期、公司id、来源、客户id）开始。另外，我还要在partner上添加唯一索引（id、district\u id、owner\u id）。
还可以添加其他生成的存储列year和month（如果服务器有点旧，可以添加普通列并用触发器填充它们），以消除多个函数执行。

赞(0）回复(0）举报 2021-06-19

pcrecxhr3#

下面是优化器在查询中看到的内容。
正在检查是否对 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".

优化器将查看“统计信息”，粗略地决定从哪个表开始。所以，添加我建议的所有索引。
“covering”索引包含查询中任何位置所需的所有列。有时明智的做法是用更多的列来扩展“good”索引，使其“覆盖”。
但这里有个活扳手。 c.customer_id = p.id 意思是 customer_id IN (...) 有效存在。但是现在有两个“类似范围”的约束——一个是 IN ，另一个是“范围”。在一些较新的版本中，由于 IN 还能做“范围”扫描。因此，我建议订购：
测试 column = constant 测试 IN 一个“范围”测试( 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)

因为有一个 IN 或者“range”，看索引是否也能处理 GROUP BY .
关于 COUNT(x) --它检查 x 是 NOT NULL . 这通常是正确的说法 COUNT(*) ，它统计行数而不进行任何额外检查。
这是一个非启动程序，因为它隐藏了索引列( id )在函数中：

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

赞(0）回复(0）举报 2021-06-19

我来回答

mysql查询性能非常慢

3条答案

相关问题

热门标签

最新问答