欢迎投稿

今日深度:

mysql varchar int 123 走索引吗?,

mysql varchar int 123 走索引吗?,


结论:

当MySQL中字段为int类型时,搜索条件where num='111' 与where num=111都可以使用该字段的索引。
当MySQL中字段为varchar类型时,搜索条件where num='111' 可以使用索引,where num=111 不可以使用索引

验证过程:

    建表语句:

1 2 3 4 5 6 7 8 9 CREATE TABLE `gyl` (   `id` int(11) NOT NULL AUTO_INCREMENT,   `str` varchar(255) NOT NULL,   `num` int(11) NOT NULL DEFAULT '0',   `obj` varchar(255) DEFAULT NULL,   PRIMARY KEY (`id`),   KEY `str_x` (`str`),   KEY `num_x` (`num`) ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

  向表中使用自复制语句插入数据

            insert into gyl (`str`,`num`)values(123123,'12313');

            insert into gyl (`str`,`num`) select `str`,`num` from gyl;

更改数据 update gyl set num=id,str=id

结果:

 

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 mysql> explain select * from gyl where str=123123 limit 1; +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ | id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra       | +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ |  1 | SIMPLE      | gyl   | ALL  | str_x         | NULL | NULL    | NULL | 262756 | Using where | +----+-------------+-------+------+---------------+------+---------+------+--------+-------------+ 1 row in set mysql> explain select * from gyl where str='123123' limit 1; +----+-------------+-------+------+---------------+-------+---------+-------+--------+-------------+ | id | select_type | table | type | possible_keys | key   | key_len | ref   | rows   | Extra       | +----+-------------+-------+------+---------------+-------+---------+-------+--------+-------------+ |  1 | SIMPLE      | gyl   | ref  | str_x         | str_x | 257     | const | 131378 | Using where | +----+-------------+-------+------+---------------+-------+---------+-------+--------+-------------+ 1 row in set   mysql> explain select * from gyl where num='12313' limit 1;; +----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+ | id | select_type | table | type | possible_keys | key   | key_len | ref   | rows   | Extra | +----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+ |  1 | SIMPLE      | gyl   | ref  | num_x         | num_x | 4       | const | 131378 |       | +----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+ 1 row in set   1065 - Query was empty mysql> explain select * from gyl where num=12313 limit 1; +----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+ | id | select_type | table | type | possible_keys | key   | key_len | ref   | rows   | Extra | +----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+ |  1 | SIMPLE      | gyl   | ref  | num_x         | num_x | 4       | const | 131378 |       | +----+-------------+-------+------+---------------+-------+---------+-------+--------+-------+ 1 row in set

字段类型不同造成的隐式转换,导致索引失效

www.htsjk.Com true http://www.htsjk.com/Mysql/42545.html NewsArticle mysql varchar int 123 走索引吗?, 结论: 当MySQL中字段为int类型时,搜索条件where num='111' 与where num=111都可以使用该字段的索引。 当MySQL中字段为varchar类型时,搜索条件where num='111' 可以使...
相关文章
    暂无相关文章
评论暂时关闭