与mariadb相比，mysql查询在mysql中花费的时间太长
与mariadb相比，mysql查询在mysql中花费的时间太长
提问于 2018-07-17 15:13:29
我从MariaDB迁移到Mysql。我下面的查询在Mysql中花费了三分钟多的时间，但在MariaDB中不到一分钟。Mysql版本为5.7。请建议我如何处理这个问题，非常感谢！
SELECT
COUNT(*)
FROM
(SELECT i.*, f.*, c.*, ft.*, -- summarized for readability
(SELECT f1.id
FROM sys_exten_flow_task f1
WHERE f1.instance_id = f.INSTANCE_ID AND state = '1'
ORDER BY f1.update_date DESC
LIMIT 0 , 1) AS pretaskid,
CASE
WHEN i.FLOW_DEFINE IS NULL THEN 'free'
ELSE i.FLOW_DEFINE
END AS fd,
CASE
WHEN f.TASK_DEFINE_ID IS NULL THEN 'free'
ELSE f.TASK_DEFINE_ID
END AS tdi
FROM
sys_exten_flow_task f, sys_exten_flow_instance i,
sys_exten_flow_define_task dt, sys_user c, sys_dict ft
WHERE
i.ID = f.INSTANCE_ID
AND i.CREATE_BY = c.ID
AND i.FLOW_TYPE = ft.ID
AND dt.id = f.task_define_id) a;
+----------+
| count(*) |
+----------+
| 3841309 |
+----------+
1 row in set (3 min 21.97 sec)使用解释计划
+----+--------------------+------------+------------+--------+--------------------------------------------------+------------------------+---------+----------------------------+---------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+--------------------+------------+------------+--------+--------------------------------------------------+------------------------+---------+----------------------------+---------+----------+---------------------------------------+
| 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 3640604 | 100.00 | NULL |
| 2 | DERIVED | i | NULL | ALL | PRIMARY,CREATE_BY | NULL | NULL | NULL | 788059 | 100.00 | Using where |
| 2 | DERIVED | ft | NULL | eq_ref | PRIMARY,ID | PRIMARY | 258 | scdxoa.i.FLOW_TYPE | 1 | 100.00 | Using where |
| 2 | DERIVED | c | NULL | eq_ref | PRIMARY,ID | PRIMARY | 258 | scdxoa.i.CREATE_BY | 1 | 100.00 | NULL |
| 2 | DERIVED | f | NULL | ref | INDEX_TASK_INSTANCE_ID,INDEX_TASK_TASK_DEFINE_ID | INDEX_TASK_INSTANCE_ID | 402 | scdxoa.i.ID | 4 | 100.00 | Using index condition; Using where |
| 2 | DERIVED | dt | NULL | eq_ref | PRIMARY | PRIMARY | 258 | scdxoa.f.TASK_DEFINE_ID | 1 | 100.00 | Using where |
| 3 | DEPENDENT SUBQUERY | f1 | NULL | ref | INDEX_TASK_STATUS,INDEX_TASK_INSTANCE_ID,INDEX | INDEX | 408 | const,scdxoa.f.INSTANCE_ID | 3 | 100.00 | Using index condition; Using filesort |
+----+--------------------+------------+------------+--------+--------------------------------------------------+------------------------+---------+----------------------------+---------+----------+---------------------------------------+
7 rows in set, 2 warnings (0.01 sec)在MariaDB中执行相同的查询时，我得到
+----------+
| count(*) |
+----------+
| 3912445 |
+----------+
1 row in set (56.54 sec)使用解释计划
+------+--------------------+-------+--------+--------------------------------------------------+------------------------+---------+-------------------------+-------+----------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+------+--------------------+-------+--------+--------------------------------------------------+------------------------+---------+-------------------------+-------+----------+------------------------------------+
| 1 | PRIMARY | c | index | PRIMARY,ID | group_login_name | 205 | NULL | 23047 | 100.00 | Using index |
| 1 | PRIMARY | i | ref | PRIMARY,CREATE_BY | CREATE_BY | 259 | scdxoa.c.ID | 15 | 100.00 | |
| 1 | PRIMARY | ft | eq_ref | PRIMARY,ID | PRIMARY | 258 | scdxoa.i.FLOW_TYPE | 1 | 100.00 | Using where; Using index |
| 1 | PRIMARY | f | ref | INDEX_TASK_INSTANCE_ID,INDEX_TASK_TASK_DEFINE_ID | INDEX_TASK_INSTANCE_ID | 402 | scdxoa.i.ID | 2 | 100.00 | Using index condition; Using where |
| 1 | PRIMARY | dt | eq_ref | PRIMARY | PRIMARY | 258 | scdxoa.f.TASK_DEFINE_ID | 1 | 100.00 | Using where; Using index |
| 3 | DEPENDENT SUBQUERY | f1 | ref | INDEX_TASK_STATUS,INDEX_TASK_INSTANCE_ID,INDEX | INDEX_TASK_INSTANCE_ID | 402 | scdxoa.f.INSTANCE_ID | 2 | 100.00 | Using where; Using filesort |
+------+--------------------+-------+--------+--------------------------------------------------+------------------------+---------+-------------------------+-------+----------+------------------------------------+
6 rows in set, 2 warnings (0.00 sec)我还尝试让Mysql执行mariadb优化后的结果sql。
select count(0) AS "count(*)"
from "scdxoa"."sys_exten_flow_task" "f"
join "scdxoa"."sys_exten_flow_instance" "i"
join "scdxoa"."sys_exten_flow_define_task" "dt"
join "scdxoa"."sys_user" "c"
join "scdxoa"."sys_dict" "ft"
where (("scdxoa"."i"."CREATE_BY" = "scdxoa"."c"."ID")
and ("scdxoa"."i"."ID" = "scdxoa"."f"."INSTANCE_ID")
and ("scdxoa"."i"."FLOW_TYPE" = "scdxoa"."ft"."ID")
and ("scdxoa"."dt"."ID" = "scdxoa"."f"."TASK_DEFINE_ID"));
+----------+
| count(*) |
+----------+
| 3841309 |
+----------+
1 row in set (21.83 sec)回答 1
Stack Overflow用户
发布于 2018-07-18 03:32:35
此构造会浪费一些时间：
SELECT COUNT(*)
FROM ( SELECT lots-of-columns
FROM, etc )而不是简单地
SELECT COUNT(*)
FROM, etc这是因为派生表是构建并放入临时表中的，在本例中可能放在磁盘上，然后进行计数。
因为我在MariaDB的EXPLAIN中没有看到"2“，所以我猜他们是为你做了这个优化。
在5.6和10.0版本中，他们在优化器中做了一些明显的变化。你遇到了一个MariaDB大放异彩的案例。
将第一个CASE..END简化为IFNULL(i.FLOW_DEFINE, 'free')；等等。
请使用JOIN..ON而不是comma+WHERE --这不会提高速度，但可能有助于提高可读性。
即使在子查询中，也有其他简化；例如，CASEs没有提供任何计数。无论您是否关心，如果您提供一个演示问题的最小测试用例，您可能会从我们那里获得更多帮助。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/51375303
复制相关文章
相似问题