關(guān)于cast類型轉(zhuǎn)換后無法使用索引的優(yōu)化

因?yàn)楸碓O(shè)計(jì)時是各開發(fā)人員建立的表，同是建立日期字段add_time,有int,還有varchar(50)幾種數(shù)據(jù)類型，如下：
（字段有刪減）

mys

ql> show create table credit_tender; +--------------------------+------------------------ | Table | Create Table | +--------------------------+------------------------ | credit_tender | CREATE TABLE `credit_tender` (`assign_id` int(11) unsigned NOT NULL AUTO_INCREMENT,`user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用戶名稱',`credit_user_id` int(11) DEFAULT NULL COMMENT '出讓人id',`status` int(2) DEFAULT '0' COMMENT '狀態(tài)',`add_time` varchar(50) DEFAULT NULL COMMENT '添加時間',`addip` varchar(50) DEFAULT NULL COMMENT 'ip',`client` int(11) DEFAULT NULL COMMENT '客戶端',PRIMARY KEY (`assign_id`),KEY `user_id` (`user_id`),KEY `idx_add_time` (`add_time`) ) ENGINE=InnoDB AUTO_INCREMENT=20152 DEFAULT CHARSET=utf8 COMMENT='債權(quán)投資表' |

可以看到字段
add_time varchar(50) DEFAULT NULL COMMENT ‘添加時間’,
有索引：
KEY idx_add_time (add_time)

但現(xiàn)在的查詢，所有都是使用了以下查詢

SELECT * FROM borrow_tender t WHERE FROM_UNIXTIME(t.addtime,'%Y-%m-%d %H:%i:%s') >= '2018-03-19 00:00:00' AND FROM_UNIXTIME(t.addtime,'%Y-%m-%d %H:%i:%s') <= '2018-03-19 23:59:59'

查看執(zhí)行計(jì)劃如下：
可以看到，有索引，但并沒有走索引，還是全表搜索（type:ALL），

mysql> explain SELECT * FROM borrow_tender tWHERE FROM_UNIXTIME(t.addtime,'%Y-%m-%d %H:%i:%s') >= '2018-03-19 00:00:00' AND FROM_UNIXTIME(t.addtime,'%Y-%m-%d %H:%i:%s') <= '2018-03-19 23:59:59'; +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+ | 1 | SIMPLE | tender | ALL | NULL | NULL | NULL | NULL | 664731 | Using where | +----+-------------+--------+------+---------------+------+---------+------+--------+-------------+ 1 row in setmysql>

還看到這個表有以下查詢條件的，相對于上面的查詢，稍有思考，知道轉(zhuǎn)換成相同數(shù)據(jù)類型進(jìn)行對比查詢：

mysql> explain SELECT user_id,status,add_time from credit_tender t WHERE t.add_time >= cast(unix_timestamp('2018-03-19 00:00:00') as CHAR) AND t.add_time <= cast(unix_timestamp('2018-03-19 23:59:59') as CHAR); +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ | 1 | SIMPLE | hct | ALL | NULL | NULL | NULL | NULL | 14287 | Using where | +----+-------------+-------+------+---------------+------+---------+------+-------+-------------+ 1 row in set

但我們看到，還是沒走索引，為什么？
cast(unix_timestamp(‘2018-03-19 00:00:00’) as CHAR) 已轉(zhuǎn)換成字串了呀，為什么。
這里是因?yàn)閏har 是定長，而我們的字段類型為 varchar()
所以，這里只要使用以下函數(shù)即可
cast(unix_timestamp(‘2018-03-19 00:00:00’) as NCHAR)

mysql> explain SELECT user_id,status,add_time from credit_tender hct WHERE hct.add_time >= cast(unix_timestamp('2018-03-19 00:00:00') as NCHAR) AND hct.add_time <= cast(unix_timestamp('2018-03-19 23:59:59') as NCHAR); +----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+ | 1 | SIMPLE | hct | range | idx_add_time | idx_add_time | 153 | NULL | 45 | Using index condition | +----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+ 1 row in set

或者使用 CONCAT 函數(shù)，隱性轉(zhuǎn)換成字串的方法

mysql> explain SELECT user_id,status,add_time from credit_tender hct WHERE hct.add_time >= CONCAT(unix_timestamp('2018-03-19 00:00:00'),'') AND hct.add_time <= CONCAT(unix_timestamp('2018-03-19 23:59:59') ,'') ; +----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+ | 1 | SIMPLE | hct | range | idx_add_time | idx_add_time | 153 | NULL | 45 | Using index condition | +----+-------------+-------+-------+---------------+--------------+---------+------+------+-----------------------+ 1 row in setmysql>

總結(jié)

以上是生活随笔為你收集整理的关于cast类型转换后无法使用索引的优化的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。