关于mysql 隐式转换的一个小问题

废话先不多说，直接上实验情况。测试表结构如下：

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL DEFAULT '0',
  `stat_date` varchar(10) COLLATE utf8_bin DEFAULT NULL,
  `earner_id` varchar(20) COLLATE utf8_bin DEFAULT NULL,
  `website_id` bigint(20) DEFAULT NULL,
  `advertiser_id` bigint(20) DEFAULT NULL,
  `campaign_id` bigint(20) DEFAULT NULL,
  `link_id` bigint(20) DEFAULT NULL,
  `impr_amount` bigint(20) DEFAULT '0',
  `impr_ip_amount` bigint(20) DEFAULT '0',
  `click_amount` bigint(20) DEFAULT '0',
  `click_ip_amount` bigint(20) DEFAULT '0',
  `create_time` datetime DEFAULT NULL,
  KEY `ind_earid` (`earner_id`)
) ENGINE=InnoDB

说明：测试字段earner_id 字段类型 varchar类型，表数据量 100w 

select count(1) from test;
+----------+
| count(1) |
+----------+
|  1000000 |
+----------+

1），earner_id 做等值查询条件的正常情况

(user:root  time: 23:51)[db: yiqifa_bs]explain select * from test where earner_id='1001';
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key       | key_len | ref   | rows | Extra       |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+
|  1 | SIMPLE      | test    | ref    | ind_earid     | ind_earid | 63      | const |2211| Using where |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+-------------+
查询条件为字符串'1001' 为条件进行查询，走索引ind_earid扫描数据行数大概2211，这个没有问题。


2），人为制造隐式转换场景：（查询条件为数字类型1001）
 
(user:root  time: 23:50)[db: yiqifa_bs]explain select * from test where earner_id=1001;         
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | test  | ALL  | ind_earid     | NULL | NULL    | NULL | 1000580 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
ok ，发现发生了隐式转换故走的是全表扫描，也没问题。这里扫描行数为100多万，是因为这是一个估值并不是一直确切的值

因为mysql在sql解析阶段会默认随机抽取8个page进行数据的估算和统计。

注：隐式转换实质是mysql将字段earner_id 的字符串值隐式转换为数值类型的值然后进行等值比较。

再者：这里earner_id列时字符类型而传入的值是数值类型，这种情况会发生隐式转换而无法走索引，反过来如果列时数值类型而传入的值是字符串数字则不会发生隐式转化 效果如下：

 对数值类型id添加索引正常情况： create index ind_id on test(id);
 传入数值类型的字符串作为查询条件发现，没有发生隐式转化，正常走索引：
 desc select * from test where id = '34243';
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key    | key_len | ref   | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+
|  1 | SIMPLE      | test  | ref  | ind_id        |ind_id | 8       | const |    1 |       |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------+

3），突发奇想，如果select的列只有索引列呢？

正常情况：

(user:root  time: 23:51)[db: yiqifa_bs]explain select earner_id from test where earner_id='1001';
+----+-------------+-------+------+---------------+-----------+---------+-------+------+--------------------------+
| id | select_type | table | type | possible_keys | key       | key_len | ref   | rows | Extra                    |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+--------------------------+
|  1 | SIMPLE      | test  | ref  | ind_earid     | ind_earid | 63      | const | 2211 | Using where; Using index |
+----+-------------+-------+------+---------------+-----------+---------+-------+------+--------------------------+
隐式转换情况：

(user:root  time: 23:50)[db: yiqifa_bs]explain select earner_id from test where earner_id=1001;
+----+-------------+-------+-------+---------------+-----------+---------+------+---------+--------------------------+
| id | select_type | table | type  | possible_keys | key       | key_len | ref  | rows    | Extra                    |
+----+-------------+-------+-------+---------------+-----------+---------+------+---------+--------------------------+
|  1 | SIMPLE      | test  | index | ind_earid     | ind_earid | 63      | NULL | 1000580| Using where;Using index |
+----+-------------+-------+-------+---------------+-----------+---------+------+---------+--------------------------+

看到rows 显示的是100w左右，type 显示的是index，ref 下又是NULL ,仔细看Extra列你会发现居然显示的是 Using index，这里就是要说的核心问题：

这里的显示容易误导大家以为查询走了合适的索引，其实是全索引扫描的，数据量如果很大的话代价也非常大。

新浪微博：freedom3959

qq ：554370286