MySQL：UNIX_TIMESTAMP函数不走索引问题

清疚 2022-08-23 09:56 409阅读 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(函数)是走不了索引的。

请忽略SYSDATE()与‘2016-06-24’等值问题。

依然不解，为什么UNIX\_TIMESTAMP(TIMESTAMP(DATE(SYSDATE())))   他就不走索引？