MySQL：UNIX_TIMESTAMP函数不走索引问题

最新推荐文章于 2024-08-18 15:02:14 发布

Two手揣兜

最新推荐文章于 2024-08-18 15:02:14 发布

阅读量7.7k

点赞数

分类专栏： SQL优化

本文链接：https://blog.csdn.net/u013820054/article/details/51659839

版权

SQL优化专栏收录该内容

22 篇文章 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()))) 他就不走索引？