我在 MySQL 中有下表:

CREATE TABLE `events` (
  `pv_name` varchar(60) COLLATE utf8mb4_bin NOT NULL,
  `time_stamp` bigint(20) unsigned NOT NULL,
  `event_type` varchar(40) COLLATE utf8mb4_bin NOT NULL,
  `has_data` tinyint(1) NOT NULL,
  `data` json DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=COMPRESSED;

ALTER TABLE `events`
 ADD PRIMARY KEY (`pv_name`,`time_stamp`),
 ADD UNIQUE KEY `has_data` (`pv_name`,`has_data`,`time_stamp`);

我试图找到不同的 pv_names 集，这些 pv_names 在两个给定时间之间有一些没有数据的行。以下两个查询似乎都返回此信息:

mysql> EXPLAIN SELECT pv_name FROM events
         WHERE has_data = 0
           AND events.time_stamp > 0 AND events.time_stamp < 9999999999999999999
         GROUP BY events.pv_name;
+----+-------------+--------+------------+-------+------------------+----------+---------+------+---------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys    | key      | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+--------+------------+-------+------------------+----------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | events | NULL       | index | PRIMARY,has_data | has_data | 251     | NULL | 1855281 |     1.11 | Using where; Using index |
+----+-------------+--------+------------+-------+------------------+----------+---------+------+---------+----------+--------------------------+

mysql> EXPLAIN SELECT pv_name, MAX(events.time_stamp) FROM events
         WHERE has_data = 0
           AND events.time_stamp > 0 AND events.time_stamp < 9999999999999999999
         GROUP BY events.pv_name;
+----+-------------+--------+------------+-------+------------------+----------+---------+------+--------+----------+---------------------------------------+
| id | select_type | table  | partitions | type  | possible_keys    | key      | key_len | ref  | rows   | filtered | Extra                                 |
+----+-------------+--------+------------+-------+------------------+----------+---------+------+--------+----------+---------------------------------------+
|  1 | SIMPLE      | events | NULL       | range | PRIMARY,has_data | has_data | 251     | NULL | 203123 |   100.00 | Using where; Using index for group-by |
+----+-------------+--------+------------+-------+------------------+----------+---------+------+--------+----------+---------------------------------------+

我不明白的是，为什么第二个查询(它对返回的内容有额外限制(我不需要)似乎比第一个查询运行的时间更少)。有没有一种方法可以改进第一个查询以匹配第二个查询的效率，而无需对 time_stamp 列进行聚合？

编辑:

根据 Rick James 的建议，我更改了 has_data 索引:

ALTER TABLE `events`
 ADD PRIMARY KEY (`pv_name`,`time_stamp`), ADD KEY `has_data` (`has_data`,`pv_name`,`time_stamp`);

这将查询报告更改为:

mysql> EXPLAIN SELECT pv_name FROM events WHERE has_data = 0 AND events.time_stamp > 0 AND events.time_stamp < 9999999999999999999 GROUP BY events.pv_name;
+----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key      | key_len | ref   | rows   | filtered | Extra                    |
+----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
|  1 | SIMPLE      | events | NULL       | ref  | PRIMARY,has_data | has_data | 1       | const | 267096 |    11.11 | Using where; Using index |
+----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT pv_name, MAX(events.time_stamp) FROM events WHERE has_data = 0 AND events.time_stamp > 0 AND events.time_stamp < 9999999999999999999 GROUP BY events.pv_name;
+----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key      | key_len | ref   | rows   | filtered | Extra                    |
+----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
|  1 | SIMPLE      | events | NULL       | ref  | PRIMARY,has_data | has_data | 1       | const | 267096 |    11.11 | Using where; Using index |
+----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
1 row in set, 1 warning (0.01 sec)

这似乎运行得更快。

编辑:

Rick James 要求的测试结果:

mysql> FLUSH STATUS;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT pv_name FROM events WHERE has_data = 0 AND events.time_stamp > 0 AND events.time_stamp < 9999999999999999999 GROUP BY events.pv_name;
.
.
.
114480 rows in set (0.34 sec)

mysql> SHOW SESSION STATUS LIKE 'Handler%';
+----------------------------+--------+
| Variable_name              | Value  |
+----------------------------+--------+
| Handler_commit             | 1      |
| Handler_delete             | 0      |
| Handler_discover           | 0      |
| Handler_external_lock      | 2      |
| Handler_mrr_init           | 0      |
| Handler_prepare            | 0      |
| Handler_read_first         | 0      |
| Handler_read_key           | 1      |
| Handler_read_last          | 0      |
| Handler_read_next          | 125527 |
| Handler_read_prev          | 0      |
| Handler_read_rnd           | 0      |
| Handler_read_rnd_next      | 0      |
| Handler_rollback           | 0      |
| Handler_savepoint          | 0      |
| Handler_savepoint_rollback | 0      |
| Handler_update             | 0      |
| Handler_write              | 0      |
+----------------------------+--------+
18 rows in set (0.01 sec)

mysql> SELECT COUNT(*) FROM events;
+----------+
| COUNT(*) |
+----------+
|  3683887 |
+----------+
1 row in set (11.66 sec)

编辑:

运行时间:

mysql> SHOW INDEXES FROM events;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| events |          0 | PRIMARY  |            1 | pv_name     | A         |      216061 |     NULL | NULL   |      | BTREE      |         |               |
| events |          0 | PRIMARY  |            2 | time_stamp  | A         |     4450791 |     NULL | NULL   |      | BTREE      |         |               |
| events |          1 | has_data |            1 | has_data    | A         |         258 |     NULL | NULL   |      | BTREE      |         |               |
| events |          1 | has_data |            2 | pv_name     | A         |      496542 |     NULL | NULL   |      | BTREE      |         |               |
| events |          1 | has_data |            3 | time_stamp  | A         |     4390035 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

mysql> EXPLAIN SELECT events.pv_name FROM events WHERE has_data = 0 AND events.time_stamp > 0 AND events.time_stamp < 9999999999999999999 GROUP BY events.pv_name;
+----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key      | key_len | ref   | rows   | filtered | Extra                    |
+----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
|  1 | SIMPLE      | events | NULL       | ref  | PRIMARY,has_data | has_data | 1       | const | 267096 |    11.11 | Using where; Using index |
+----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT events.pv_name, MAX(events.time_stamp) FROM events WHERE has_data = 0 AND events.time_stamp > 0 AND events.time_stamp < 9999999999999999999 GROUP BY events.pv_name;
+----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
| id | select_type | table  | partitions | type | possible_keys    | key      | key_len | ref   | rows   | filtered | Extra                    |
+----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
|  1 | SIMPLE      | events | NULL       | ref  | PRIMARY,has_data | has_data | 1       | const | 267096 |    11.11 | Using where; Using index |
+----+-------------+--------+------------+------+------------------+----------+---------+-------+--------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)


SELECT events.pv_name FROM events WHERE has_data = 0 AND events.time_stamp > 0 AND events.time_stamp < 9999999999999999999 GROUP BY events.pv_name;
114480 rows in set (0.37 sec)

SELECT events.pv_name, MAX(events.time_stamp) FROM events WHERE has_data = 0 AND events.time_stamp > 0 AND events.time_stamp < 9999999999999999999 GROUP BY events.pv_name;
114480 rows in set (0.30 sec)


mysql> SHOW INDEXES FROM events;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| events |          0 | PRIMARY  |            1 | pv_name     | A         |      422951 |     NULL | NULL   |      | BTREE      |         |               |
| events |          0 | PRIMARY  |            2 | time_stamp  | A         |     4321990 |     NULL | NULL   |      | BTREE      |         |               |
| events |          0 | has_data |            1 | pv_name     | A         |      240067 |     NULL | NULL   |      | BTREE      |         |               |
| events |          0 | has_data |            2 | has_data    | A         |      436525 |     NULL | NULL   |      | BTREE      |         |               |
| events |          0 | has_data |            3 | time_stamp  | A         |     4205163 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

mysql> EXPLAIN SELECT events.pv_name FROM events WHERE has_data = 0 AND events.time_stamp > 0 AND events.time_stamp < 9999999999999999999 GROUP BY events.pv_name;
+----+-------------+--------+------------+-------+------------------+----------+---------+------+---------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys    | key      | key_len | ref  | rows    | filtered | Extra                    |
+----+-------------+--------+------------+-------+------------------+----------+---------+------+---------+----------+--------------------------+
|  1 | SIMPLE      | events | NULL       | index | PRIMARY,has_data | has_data | 251     | NULL | 4462633 |     1.11 | Using where; Using index |
+----+-------------+--------+------------+-------+------------------+----------+---------+------+---------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> EXPLAIN SELECT events.pv_name, MAX(events.time_stamp) FROM events WHERE has_data = 0 AND events.time_stamp > 0 AND events.time_stamp < 9999999999999999999 GROUP BY events.pv_name;
+----+-------------+--------+------------+-------+------------------+----------+---------+------+--------+----------+---------------------------------------+
| id | select_type | table  | partitions | type  | possible_keys    | key      | key_len | ref  | rows   | filtered | Extra                                 |
+----+-------------+--------+------------+-------+------------------+----------+---------+------+--------+----------+---------------------------------------+
|  1 | SIMPLE      | events | NULL       | range | PRIMARY,has_data | has_data | 251     | NULL | 240076 |   100.00 | Using where; Using index for group-by |
+----+-------------+--------+------------+-------+------------------+----------+---------+------+--------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

SELECT events.pv_name FROM events WHERE has_data = 0 AND events.time_stamp > 0 AND events.time_stamp < 9999999999999999999 GROUP BY events.pv_name;
114480 rows in set (6.79 sec)

SELECT events.pv_name, MAX(events.time_stamp) FROM events WHERE has_data = 0 AND events.time_stamp > 0 AND events.time_stamp < 9999999999999999999 GROUP BY events.pv_name;
114480 rows in set (2.65 sec)

最佳答案

根据 [文档](http://dev.mysql.com/doc/refman/5.7/en/group-by-optimization.html 用于松散索引扫描):

除查询中引用的 GROUP BY 之外，索引的任何其他部分都必须是常量(也就是说，它们必须与常量等价引用)，MIN() 或 MAX( ) 功能。

在您的第一个查询中，time_stamp 被引用但不是常量。在您的第二个查询中，time_stamp 也在 MAX() 的参数中。因此，松散索引扫描适用于这种情况。

关于通过添加聚合改进了 MySQL 分组查询性能，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/40854279/