MySQL性能优化（四）隐式类型转换
我们知道， where条件中，对一个索引字段进行运算会导致无法使用该字段的索引。
有些情况下，即使没有显式地对索引字段进行运算，但是数据库会进行隐式类型转换，这也会导致无法使用索引。
会发生隐式类型转换的常见情况包括：
1、字段类型为字符串，存储了数字，where条件中传入的参数为数字类型。
2、字段类型为字符串，存储了日期格式的信息，where条件中传入了日期类型
3、字段类型和传入的参数都是字符串类型，但是字符集不匹配。
4、两表关联时，关联字段的类型不一致，或字符集不一致。
一个例子
CREATE TABLE `tab` ( `id` int NOT NULL AUTO_INCREMENT, `phone` varchar(13) DEFAULT NULL, `phone2` bigint DEFAULT NULL, `create_time` varchar(20) DEFAULT NULL, `create_time2` datetime DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_phone` (`phone`), KEY `idx_phone2` (`phone2`), KEY `idx_createtime` (`create_time`), KEY `idx_createtime2` (`create_time2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
情况1： 索引字段类型为varchar，传入数字类型的参数
mysql> explain select * from tab where phone=13512345678; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tab | NULL | ALL | idx_phone | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 3 warnings (0.00 sec) mysql> show warnings; +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1739 | Cannot use ref access on index 'idx_phone' due to type or collation conversion on field 'phone' | | Warning | 1739 | Cannot use range access on index 'idx_phone' due to type or collation conversion on field 'phone' | | Note | 1003 | /* select#1 */ select `test`.`tab`.`id` AS `id`,`test`.`tab`.`phone` AS `phone`,`test`.`tab`.`phone2` AS `phone2`,`test`.`tab`.`create_time` AS `create_time`,`test`.`tab`.`create_time2` AS `create_time2` from `test`.`tab` where (`test`.`tab`.`phone` = 13512345678) | +---------+------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)
将传入的参数改成字符串类型，就可以使用到索引：
mysql> explain select * from tab where phone='13512345678'; +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ | 1 | SIMPLE | tab | NULL | ref | idx_phone | idx_phone | 55 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `test`.`tab`.`id` AS `id`,`test`.`tab`.`phone` AS `phone`,`test`.`tab`.`phone2` AS `phone2`,`test`.`tab`.`create_time` AS `create_time`,`test`.`tab`.`create_time2` AS `create_time2` from `test`.`tab` where (`test`.`tab`.`phone` = '13512345678') | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
情况2： 索引字段类型为varchar，传入日期类型的参数
mysql> explain select * from tab where create_time = date(now()); +----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | tab | NULL | ALL | idx_createtime | NULL | NULL | NULL | 1 | 100.00 | Using where | +----+-------------+-------+------------+------+----------------+------+---------+------+------+----------+-------------+ 1 row in set, 3 warnings (0.00 sec) mysql> show warnings; +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Warning | 1739 | Cannot use ref access on index 'idx_createtime' due to type or collation conversion on field 'create_time' | | Warning | 1739 | Cannot use range access on index 'idx_createtime' due to type or collation conversion on field 'create_time' | | Note | 1003 | /* select#1 */ select `test`.`tab`.`id` AS `id`,`test`.`tab`.`phone` AS `phone`,`test`.`tab`.`phone2` AS `phone2`,`test`.`tab`.`create_time` AS `create_time`,`test`.`tab`.`create_time2` AS `create_time2` from `test`.`tab` where (`test`.`tab`.`create_time` = <cache>(cast(now() as date))) | +---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 3 rows in set (0.00 sec)
如果字段类型为数字，但是传入字符串类型的参数，是否也会导致查询用不上索引呢？ 我们来测试一下。
mysql> explain select * from tab where phone2 = '13512345678'; +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | tab | NULL | ref | idx_phone2 | idx_phone2 | 9 | const | 1 | 100.00 | Using index condition | +----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `test`.`tab`.`id` AS `id`,`test`.`tab`.`phone` AS `phone`,`test`.`tab`.`phone2` AS `phone2`,`test`.`tab`.`create_time` AS `create_time`,`test`.`tab`.`create_time2` AS `create_time2` from `test`.`tab` where (`test`.`tab`.`phone2` = 13512345678) | +-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
phone为数字类型，SQL中传入字符串类型的参数，但是还是可以使用索引。因为这种情况下，类型转换发生在传入的参数上，所以不影响使用索引。
类似的，下面这种情况也不影响使用索引
mysql> explain select * from tab where create_time2 = '2023-01-01'; +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | tab | NULL | ref | idx_createtime2 | idx_createtime2 | 6 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> show warnings; +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `test`.`tab`.`id` AS `id`,`test`.`tab`.`phone` AS `phone`,`test`.`tab`.`phone2` AS `phone2`,`test`.`tab`.`create_time` AS `create_time`,`test`.`tab`.`create_time2` AS `create_time2` from `test`.`tab` where (`test`.`tab`.`create_time2` = TIMESTAMP'2023-01-01 00:00:00') | +-------+------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
总结
隐式类型转换可能会导致查询无法使用索引。做好以下几点，可以避免出现这种情况：
1、我们在设计表结构时，需要选择精确的字段类型。不要用字符串类型存储日期和数字。
2、同一个业务字段，在不同的表里面字段类型要保持一致，字符集也要保持一致。
3、查询的SQL，传入的参数要和字段的类型保持一致。
