此查询需要1.2秒:
select * from alert limit 10000;
此查询需要33.99秒:
select * from alert_version limit 10000;
alert\u version是一个视图,基本上是带有子查询的“alert”表:
select `alert`.`Alert_UID` AS `Alert_UID`,`alert`.`Rule_Name` AS `Rule_Name`,
`alert`.`Headline` AS `Headline`,`alert`.`Severity` AS `Severity`,
`alert`.`Device_UID` AS `Device_UID`,`alert`.`Configuration_Set_ID` AS `Configuration_Set_ID`,
`alert`.`Instance_UID` AS `Instance_UID`,`alert`.`Create_DateTime` AS `Create_DateTime`,
`alert`.`Delete_DateTime` AS `Delete_DateTime`,
( SELECT `version_build`.`Version`
from `version_build`
where ((`version_build`.`Instance_UID` = `alert`.`Instance_UID`)
and (`version_build`.`Create_DateTime` >= `alert`.`Create_DateTime`)
)
order by `version_build`.`Create_DateTime`
limit 1
) AS `version`
from `alert`
当我对这个查询运行explain时,我得到:
+----+--------------------+---------------+------+-----------------------------------------------------------------------------------+--------------------------+---------+----------------------------+--------+------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+---------------+------+-----------------------------------------------------------------------------------+--------------------------+---------+----------------------------+--------+------------------------------------------+
| 1 | PRIMARY | alert | ALL | NULL | NULL | NULL | NULL | 301274 | NULL |
| 2 | DEPENDENT SUBQUERY | version_build | ref | uid_version_build_create,version_build_Instance_UID,version_build_Create_DateTime | uid_version_build_create | 110 | insight.alert.Instance_UID | 6 | Using where; Using index; Using filesort |
+----+--------------------+---------------+------+-----------------------------------------------------------------------------------+--------------------------+---------+----------------------------+--------+------------------------------------------+
那么,你认为我应该怎么做才能得到可以接受的表现呢?
更新:
每个请求,添加表信息:
CREATE TABLE `alert` (
`Alert_UID` varchar(36) NOT NULL,
`Rule_Name` varchar(80) DEFAULT NULL,
`Headline` varchar(255) DEFAULT NULL,
`Severity` varchar(12) DEFAULT NULL,
`Device_UID` varchar(36) NOT NULL,
`Configuration_Set_ID` varchar(12) DEFAULT NULL,
`Instance_UID` varchar(36) NOT NULL,
`Create_DateTime` timestamp NOT NULL,
`Delete_DateTime` timestamp NULL DEFAULT NULL,
PRIMARY KEY (`Alert_UID`),
KEY `alert_Create_DateTime` (`Alert_UID`,`Create_DateTime`),
KEY `alert_Headline` (`Headline`),
KEY `alert_Rule_Headline` (`Rule_Name`,`Headline`),
KEY `alert_Instance_UID` (`Instance_UID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
CREATE TABLE `version_build` (
`Instance_UID` varchar(36) NOT NULL,
`Version` varchar(12) NOT NULL,
`Build` varchar(30) DEFAULT NULL,
`Create_DateTime` timestamp NOT NULL,
UNIQUE KEY `uid_version_build_create`
(`Instance_UID`,`Version`,`Build`,`Create_DateTime`),
KEY `version_build_Instance_UID` (`Instance_UID`),
KEY `version_build_Create_DateTime` (`Create_DateTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
2条答案
按热度按时间bvuwiixz1#
你打算用10公里的距离做什么?对于一个应用程序来说,这通常太多了。
没有一个
ORDER BY
,您希望获得哪一条10公里的赛道?这是不可预测的。带着一个ORDER BY
,查询速度可能会更慢。子查询是性能杀手。以速度生活。但是,您似乎在以一种低效的方式执行“groupwise max”。此索引可能有助于:
有关groupwise max的更多信息:http://mysql.rjweb.org/doc.php/groupwise_max
ar7v8xwq2#
请仔细测试!我只是做了些快速测试
从我的观点来看,子查询只是这个查询中的一个过度杀伤力(因此性能非常糟糕):
groupby不是顺序的,所以必须在select字段中使用min的技巧,否则顺序是不可预测的。我没有测试性能,但我猜它们只是子查询的一小部分。
如果连接条件不满足,则使用空值。