mysql 类型转换 索引_经过字段类型转化后的查询不走索引

总结：索引字段上有发生int到varchar、varbinary的类型转换，不会使用到索引

而如在索引字段上发生varchar向int的类型转化，仍然会使用到索引

表结构如下：

mysql> show create table tag_item_list\G

*************************** 1. row ***************************

Table: tag_item_list

Create Table: CREATE TABLE `tag_item_list` (

`tag_item_list_id` bigint(20) unsigned NOT NULL auto_increment,

`tag_id` bigint(20) unsigned NOT NULL,

`item_type` bigint(20) unsigned NOT NULL,

`item_id` varchar(100) NOT NULL,

`list_id` bigint(20) unsigned NOT NULL,

`deleted` tinyint(1) NOT NULL,

`item_ext1` bigint(20) NOT NULL default '0',

`item_ext2` bigint(20) NOT NULL default '0',

`item_ext3` bigint(20) NOT NULL default '0',

`relation_ext1` bigint(20) NOT NULL default '0',

`relation_ext2` bigint(20) NOT NULL default '0',

`relation_ext3` bigint(20) NOT NULL default '0',

PRIMARY KEY  (`tag_item_list_id`),

KEY `idx_iid_itp` (`item_id`,`item_type`),

KEY `idx_tid_itp` (`tag_id`,`item_type`)

) ENGINE=InnoDB AUTO_INCREMENT=231210 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

1 row in set (0.00 sec)

索引字段上有发生int到varchare、varbinary的类型转换，不会使用到索引

mysql> explain  SELECT sql_no_cache * from tag_item_list where item_id = 122 and item_type = 1 ;

+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+

| id | select_type | table         | type | possible_keys | key  | key_len | ref  | rows   | Extra       |

+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+

|  1 | SIMPLE      | tag_item_list | ALL  | idx_iid_itp   | NULL | NULL    | NULL | 231852 | Using where |

+----+-------------+---------------+------+---------------+------+---------+------+--------+-------------+

1 row in set (0.00 sec)

mysql> explain  SELECT sql_no_cache * from tag_item_list where item_id = '122' and item_type = 1 ;

+----+-------------+---------------+------+---------------+-------------+---------+-------------+------+-------------+

| id | select_type | table         | type | possible_keys | key         | key_len | ref         | rows | Extra       |

+----+-------------+---------------+------+---------------+-------------+---------+-------------+------+-------------+

|  1 | SIMPLE      | tag_item_list | ref  | idx_iid_itp   | idx_iid_itp | 310     | const,const |    1 | Using where |

+----+-------------+---------------+------+---------------+-------------+---------+-------------+------+-------------+

1 row in set (0.00 sec)

而如在索引字段上发生varchar向int的类型转化，仍然会使用到索引

mysql> show create table tag_item_list\G

*************************** 1. row ***************************

Table: tag_item_list

Create Table: CREATE TABLE `tag_item_list` (

`tag_item_list_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,

`tag_id` bigint(20) unsigned NOT NULL,

`item_type` bigint(20) unsigned NOT NULL,

`item_id` int(10) NOT NULL,

`list_id` bigint(20) unsigned NOT NULL,

`deleted` tinyint(1) NOT NULL,

`item_ext1` bigint(20) NOT NULL DEFAULT '0',

`item_ext2` bigint(20) NOT NULL DEFAULT '0',

`item_ext3` bigint(20) NOT NULL DEFAULT '0',

`relation_ext1` bigint(20) NOT NULL DEFAULT '0',

`relation_ext2` bigint(20) NOT NULL DEFAULT '0',

`relation_ext3` bigint(20) NOT NULL DEFAULT '0',

PRIMARY KEY (`tag_item_list_id`),

KEY `idx_itemid_type` (`item_id`,`item_type`),

KEY `idx_tayid_type` (`tag_id`,`item_type`)

) ENGINE=InnoDB AUTO_INCREMENT=226322 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC

mysql> explain  SELECT sql_no_cache * from tag_item_list where item_id = '122' and item_type = 1 ;

+----+-------------+---------------+------+-----------------+-----------------+---------+-------------+------+-------+

| id | select_type | table         | type | possible_keys   | key             | key_len | ref         | rows | Extra |

+----+-------------+---------------+------+-----------------+-----------------+---------+-------------+------+-------+

|  1 | SIMPLE      | tag_item_list | ref  | idx_itemid_type | idx_itemid_type | 12      | const,const |    1 |       |

+----+-------------+---------------+------+-----------------+-----------------+---------+-------------+------+-------+

测试mysql版本 5.0.51b，5.1.45