MySQL：UNIX_TIMESTAMP函数不走索引问题 清疚 2022-08-23 09:56 365阅读 0赞 关于时间戳的类型转换 取当时的时间戳函数 mysql> SELECT UNIX_TIMESTAMP(TIMESTAMP(DATE(SYSDATE()))),UNIX_TIMESTAMP(NOW()); +--------------------------------------------+-----------------------+ | UNIX_TIMESTAMP(TIMESTAMP(DATE(SYSDATE()))) | UNIX_TIMESTAMP(NOW()) | +--------------------------------------------+-----------------------+ | 1464537600 | 1464575169 | +--------------------------------------------+-----------------------+ 1 row in set (0.00 sec) 使用UNIX_TIMESTAMP函数，只走了member_id的索引 mysql> explain SELECT COUNT(1) AS COUNT -> FROM gt_member_login_log -> WHERE member_id = 528656 -> AND login_time BETWEEN UNIX_TIMESTAMP(TIMESTAMP(DATE(SYSDATE()))) AND UNIX_TIMESTAMP(NOW()); +----+-------------+---------------------+------+----------------------------------+-----------+---------+-------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------------+------+----------------------------------+-----------+---------+-------+---------+-------------+ | 1 | SIMPLE | gt_member_login_log | ref | member_id,i_login_time_member_id | member_id | 9 | const | 1352424 | Using where | +----+-------------+---------------------+------+----------------------------------+-----------+---------+-------+---------+-------------+ 1 row in set (0.00 sec) 直接带入转化后的值，会用到时间列的索引 mysql> explain SELECT COUNT(1) AS COUNT -> FROM gt_member_login_log -> WHERE member_id = 528656 -> AND login_time BETWEEN 1464537600 AND 1464575169; +----+-------------+---------------------+-------+----------------------------------+------------------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------------+-------+----------------------------------+------------------------+---------+------+------+--------------------------+ | 1 | SIMPLE | gt_member_login_log | range | member_id,i_login_time_member_id | i_login_time_member_id | 14 | NULL | 448 | Using where; Using index | +----+-------------+---------------------+-------+----------------------------------+------------------------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) force之后rows 基本是全表 mysql> explain SELECT COUNT(1) AS COUNT -> FROM gt_member_login_log FORCE INDEX (i_login_time_member_id) -> WHERE member_id = 528656 -> AND login_time BETWEEN UNIX_TIMESTAMP(TIMESTAMP(DATE(SYSDATE()))) AND UNIX_TIMESTAMP(NOW()); +----+-------------+---------------------+-------+------------------------+------------------------+---------+------+---------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------------+-------+------------------------+------------------------+---------+------+---------+--------------------------+ | 1 | SIMPLE | gt_member_login_log | index | i_login_time_member_id | i_login_time_member_id | 14 | NULL | 4279788 | Using where; Using index | +----+-------------+---------------------+-------+------------------------+------------------------+---------+------+---------+--------------------------+ 1 row in set (0.00 sec) 疑问：为什么使用UNIX\_TIMESTAMP函数，login\_time不能走索引呢？ 过了几天，根据另外一个sql的条件，出发灵感，UNIX\_TIMESTAMP(函数)是走不了索引的。 mysql> explain SELECT COUNT(1) AS COUNT -> FROM gt_member_login_log -> WHERE member_id = 528656 -> AND login_time BETWEEN UNIX_TIMESTAMP('2016-06-24') AND UNIX_TIMESTAMP('2016-06-25'); +----+-------------+---------------------+-------+----------------------------------+------------------------+---------+------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------------+-------+----------------------------------+------------------------+---------+------+------+--------------------------+ | 1 | SIMPLE | gt_member_login_log | range | member_id,i_login_time_member_id | i_login_time_member_id | 14 | NULL | 742 | Using where; Using index | +----+-------------+---------------------+-------+----------------------------------+------------------------+---------+------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain SELECT COUNT(1) AS COUNT -> FROM gt_member_login_log -> WHERE member_id = 528656 -> AND login_time BETWEEN UNIX_TIMESTAMP(SYSDATE()) AND UNIX_TIMESTAMP(NOW()); +----+-------------+---------------------+------+----------------------------------+-----------+---------+-------+---------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+---------------------+------+----------------------------------+-----------+---------+-------+---------+-------------+ | 1 | SIMPLE | gt_member_login_log | ref | member_id,i_login_time_member_id | member_id | 9 | const | 1428912 | Using where | +----+-------------+---------------------+------+----------------------------------+-----------+---------+-------+---------+-------------+ 1 row in set (0.00 sec) 请忽略SYSDATE()与‘2016-06-24’等值问题。 依然不解，为什么UNIX\_TIMESTAMP(TIMESTAMP(DATE(SYSDATE()))) 他就不走索引？
还没有评论，来说两句吧...