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

总结：索引字段上有发生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

转载于:https://www.cnblogs.com/muliu/p/6495630.html