Mon Jan 6 18:48:26 2020
autopilot@10.10.10.11 ((none)) > use event;
Database changed
Mon Jan 6 18:48:30 2020
autopilot@10.10.10.11 (event) > explain SELECT a.* FROM event as a INNER JOIN current as b on a.`event_id` = b.`event_id` AND a.`event_ts` = b.`event_ts` INNER JOIN event_receiver as c on a.`id` = c.`event_id` WHERE (c.`username` = 'zhaolilei' AND a.`priority` in ('0', '1', '2') AND b.`status` = 'PROBLEM') GROUP BY a.`id` ORDER BY c.`event_ts` desc LIMIT 100 OFFSET 0;
+----+-------------+-------+--------+-----------------------------------+--------------+---------+------------------+---------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+-----------------------------------+--------------+---------+------------------+---------+----------------------------------------------+
| 1 | SIMPLE | c | ref | idx_username,idx_event_id | idx_username | 98 | const | 1721438 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | a | eq_ref | PRIMARY,idx_event_id,idx_event_ts | PRIMARY | 8 | event.c.event_id | 1 | Using where |
| 1 | SIMPLE | b | eq_ref | PRIMARY,idx_event_ts | PRIMARY | 194 | event.a.event_id | 1 | Using where |
+----+-------------+-------+--------+-----------------------------------+--------------+---------+------------------+---------+----------------------------------------------+
3 rows in set (0.00 sec)
Mon Jan 6 18:48:32 2020
autopilot@10.10.10.11 (event) > show index from event;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| event | 0 | PRIMARY | 1 | id | A | 29439482 | NULL | NULL | | BTREE | | |
| event | 1 | idx_pdl | 1 | pdl | A | 17 | NULL | NULL | | BTREE | | |
| event | 1 | idx_event_id | 1 | event_id | A | 588789 | NULL | NULL | YES | BTREE | | |
| event | 1 | idx_event_ts | 1 | event_ts | A | 2943948 | NULL | NULL | YES | BTREE | | |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.01 sec)
Mon Jan 6 18:48:35 2020
autopilot@10.10.10.11 (event) >
Mon Jan 6 18:38:40 2020
autopilot@10.10.10.10 (event) > explain SELECT a.* FROM event as a INNER JOIN current as b on a.`event_id` = b.`event_id` AND a.`event_ts` = b.`event_ts` INNER JOIN event_receiver as c on a.`id` = c.`event_id` WHERE (c.`username` = 'zhaolilei' AND a.`priority` in ('0', '1', '2') AND b.`status` = 'PROBLEM') GROUP BY a.`id` ORDER BY c.`event_ts` desc LIMIT 100 OFFSET 0;
+----+-------------+-------+------+-----------------------------------+--------------+---------+------------------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+-----------------------------------+--------------+---------+------------------+--------+----------------------------------------------+
| 1 | SIMPLE | b | ALL | PRIMARY,idx_event_ts | NULL | NULL | NULL | 175557 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | a | ref | PRIMARY,idx_event_id,idx_event_ts | idx_event_ts | 99 | event.b.event_ts | 4 | Using where |
| 1 | SIMPLE | c | ref | idx_username,idx_event_id | idx_event_id | 8 | event.a.id | 2 | Using where |
+----+-------------+-------+------+-----------------------------------+--------------+---------+------------------+--------+----------------------------------------------+
3 rows in set (0.00 sec)
Mon Jan 6 18:39:38 2020
autopilot@10.10.10.10 (event) > show index from event;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| event | 0 | PRIMARY | 1 | id | A | 30352590 | NULL | NULL | | BTREE | | |
| event | 1 | idx_pdl | 1 | pdl | A | 19 | NULL | NULL | | BTREE | | |
| event | 1 | idx_event_id | 1 | event_id | A | 3035259 | NULL | NULL | YES | BTREE | | |
| event | 1 | idx_event_ts | 1 | event_ts | A | 7588147 | NULL | NULL | YES | BTREE | | |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.01 sec)
Mon Jan 6 18:39:41 2020
autopilot@10.10.10.10 (event) >
autopilot@10.10.10.10 (event) > show create table event\G;
*************************** 1. row ***************************
Table: event
Create Table: CREATE TABLE `event` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`event_id` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`status` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
`endpoint` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`hostname` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`is_customize` int(1) NOT NULL DEFAULT '1',
`pdl` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`idc` varchar(16) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`current_step` int(11) DEFAULT NULL,
`event_ts` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
`metric` varchar(64) COLLATE utf8_unicode_ci DEFAULT NULL,
`pushed_tags` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`left_val` double DEFAULT NULL,
`func` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
`op` varchar(4) COLLATE utf8_unicode_ci DEFAULT NULL,
`right_val` double DEFAULT NULL,
`max_step` int(11) DEFAULT NULL,
`priority` int(11) DEFAULT NULL,
`note` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`tpl_id` bigint(20) DEFAULT NULL,
`action_id` bigint(20) DEFAULT NULL,
`expression_id` bigint(20) DEFAULT NULL,
`strategy_id` bigint(20) DEFAULT NULL,
`strategy_group_id` bigint(20) DEFAULT NULL,
`last_values` varchar(1024) COLLATE utf8_unicode_ci DEFAULT NULL,
`db_ts` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `idx_pdl` (`pdl`),
KEY `idx_event_id` (`event_id`),
KEY `idx_event_ts` (`event_ts`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=125698572 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)
ERROR:
No query specified
的是否v
