编辑:顺便说一句,在mysql版本5.5.62-38.14-log上,我遇到了问题,尽管这些示例是在本地计算机上的5.7.27-0ubuntu0.18.04.1上运行的。我已将查询中的UNIX_TIMESTAMP()
更改为TIMESTAMP()
,但没有更改。
请问有人能帮忙看光吗?我有一个相对简单的表格:
mysql> CREATE TABLE `game_instance` (
-> `game_instance_id` bigint(20) NOT NULL AUTO_INCREMENT,
-> `game_id` int(11) NOT NULL,
-> `currency_code` varchar(15) DEFAULT NULL,
-> `start_datetime` timestamp,
-> `status` varchar(20) NOT NULL DEFAULT '' COMMENT 'COMING, NMB = No More Bets, RESOLVED, TB= Taking Bets',
-> `created_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
-> `end_datetime` datetime DEFAULT NULL,
-> `external_ref` varchar(50) DEFAULT NULL,
-> `game_room_id` int(11) DEFAULT NULL,
-> PRIMARY KEY (`game_instance_id`,`start_datetime`),
-> KEY `GI_IDX4` (`external_ref`),
-> KEY `GI_IDX5` (`game_id`,`status`),
-> KEY `game_instance_status` (`status`),
-> KEY `game_instance_end_datetime` (`end_datetime`),
-> KEY `game_instance_start_datetime` (`start_datetime`)
-> ) ENGINE=InnoDB AUTO_INCREMENT=118386942 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.14 sec)
mysql> explain select * from game_instance where start_datetime >= unix_timestamp(CONCAT(DATE_SUB(CURDATE(), INTERVAL 30 DAY), ' ', '00:00:00'));
+----+-------------+---------------+------------+------+------------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------------+------+------------------------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | game_instance | NULL | ALL | game_instance_start_datetime | NULL | NULL | NULL | 1 | 100.00 | Using where |
+----+-------------+---------------+------------+------+------------------------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)
我有一个关于start_datetime
的索引,但我仍然得到一个完整的表扫描,根据explain
。
然而:
mysql> create table ex1(
-> id bigint(20),
-> start_datetime timestamp,
-> primary key (id,start_datetime),
-> key (start_datetime)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> explain select * from ex1 where start_datetime>=unix_timestamp(CONCAT(DATE_SUB(CURDATE(), INTERVAL 30 DAY), ' ', '00:00:00'));
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | ex1 | NULL | index | start_datetime | start_datetime | 4 | NULL | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
1 row in set, 3 warnings (0.00 sec)
警告是:
mysql> show warnings;
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1292 | Incorrect datetime value: '1563663600' for column 'start_datetime' at row 1 |
| Warning | 1292 | Incorrect datetime value: '1563663600' for column 'start_datetime' at row 1 |
| Note | 1003 | /* select#1 */ select `ex`.`ex1`.`id` AS `id`,`ex`.`ex1`.`start_datetime` AS `start_datetime` from `ex`.`ex1` where (`ex`.`ex1`.`start_datetime` >= <cache>(unix_timestamp(concat((curdate() - interval 30 day),' ','00:00:00')))) |
+---------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)
这似乎表明start_datetime
在后台被静默转换,这可以解释为什么不使用索引,但为什么在两个查询中都没有发生呢?(作为推论,如何将我的日期字符串转换为MySQL时间戳是什么?
编辑2:
正如评论中所建议的那样,我已经在表上运行了优化(我还没有运行分析,因为它似乎已经这样做了(:
mysql> optimize table game_instance;
+-----------------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+-----------------------+----------+----------+-------------------------------------------------------------------+
| gameiom.game_instance | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| gameiom.game_instance | optimize | status | OK |
+-----------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (21 min 31.80 sec)
但是,它没有区别:
mysql> explain select * from game_instance
where start_datetime >= timestamp(CONCAT(DATE_SUB(CURDATE(), INTERVAL 30 DAY), ' ', '00:00:00')) and
start_datetime <= timestamp(CONCAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), ' ', '23:59:59'));
+----+-------------+---------------+------+------------------------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------------+------+------------------------------+------+---------+------+----------+-------------+
| 1 | SIMPLE | game_instance | ALL | game_instance_start_datetime | NULL | NULL | NULL | 19065747 | Using where |
+----+-------------+---------------+------+------------------------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)
这是一个真正的问题,因为表格是 19m 行(而不是我之前所说的 11m(。
有时,查询计划程序会根据有关索引中值的数量和分布的统计信息来决定是扫描整个表还是使用索引。有时,它会猜测全表扫描将比表查找占用更少的 CPU 和 IO 资源。
当表的行数较少时,查询计划程序的选择通常与直觉不匹配。在花费大量时间尝试理解EXPLAIN
输出之前,请确保您至少有几千行。
此外,查询规划器在每个MySQL版本中都会更好地完成工作。
OPTIMIZE TABLE game_instance
清理表格,尤其是在插入了许多行的情况下。
然后执行ANALYZE TABLE game_instance
以重新计算查询计划程序使用的统计信息。
顺便一提
where start_datetime>=unix_timestamp(CONCAT(DATE_SUB(CURDATE(), INTERVAL 30 DAY), ' ', '00:00:00'));
与
where start_datetime >= DATE_SUB(CURDATE(), INTERVAL 30 DAY)
MySQL知道如何直接在TIMESTAMP过滤器中使用日期计算的结果,并且UNIX_TIMESTAMP((产生整数,而不是TIMESTAMP。
关于您的时间戳警告无效,我可以建议您再问一个问题吗?请在问题中包含您的时区设置。
O. Jones的回答是正确的,但让我补充一些关于我所做的事情的注释。我看到的是这个,我无法理解:
mysql> explain extended
select * from game_instance
where
start_datetime >= timestamp(CONCAT(DATE_SUB(CURDATE(), INTERVAL 30 DAY), ' ', '00:00:00')) and
start_datetime <= timestamp(CONCAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), ' ', '23:59:59'));
+----+-------------+---------------+------+------------------------------+------+---------+------+----------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+------+------------------------------+------+---------+------+----------+----------+-------------+
| 1 | SIMPLE | game_instance | ALL | game_instance_start_datetime | NULL | NULL | NULL | 18741262 | 50.00 | Using where |
+----+-------------+---------------+------+------------------------------+------+---------+------+----------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
所以,我发现你可以强制MySQL使用索引,这给了我:
mysql> explain extended select * from game_instance force index (game_instance_start_datetime) where start_datetime >= timestamp(CONCAT(DATE_SUB(CURDATE(), INTERVAL 30 DAY), ' ', '00:00:00')) and start_datetime <= timestamp(CONCAT(DATE_SUB(CURDATE(), INTERVAL 1 DAY), ' ', '23:59:59'));
+----+-------------+---------------+-------+------------------------------+------------------------------+---------+------+---------+----------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------------+-------+------------------------------+------------------------------+---------+------+---------+----------+-------------+
| 1 | SIMPLE | game_instance | range | game_instance_start_datetime | game_instance_start_datetime | 4 | NULL | 9391936 | 100.00 | Using where |
+----+-------------+---------------+-------+------------------------------+------------------------------+---------+------+---------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
IOW,使用索引选择表中大约一半的行,现在filtered
列是有意义的:它是由于不符合条件而被丢弃的行的百分比,这就是MySQL不使用索引的原因:效率较低,因为您将在读取索引和在表中查找地址之间交替。