之前有用戶很不解：SQL語(yǔ)句非常簡(jiǎn)單鲜屏，就是select * from test_1 where user_id=1 這種類型萎羔，而且user_id上已經(jīng)建立索引了，怎么還是查詢很慢？
test_1的表結(jié)構(gòu)：
CREATE TABLE `test_1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` varchar(30) NOT NULL,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
查看執(zhí)行計(jì)劃协饲，可以看出進(jìn)行了全表掃描，并沒(méi)有用上user_id的索引。
mysql> explain select * from test_1 where user_id=1;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | test_1 | ALL | idx_user_id | NULL | NULL | NULL | 3 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.01 sec)
仔細(xì)看下表結(jié)構(gòu)立莉，user_id的字段類型： user_id varchar(30) NOT NULL,
而用戶傳入的是int，這里會(huì)有一個(gè)隱式轉(zhuǎn)換的問(wèn)題七问。隱式轉(zhuǎn)換會(huì)導(dǎo)致全表掃描蜓耻。
把輸入改成字符串類型，執(zhí)行計(jì)劃如下械巡，這樣就會(huì)很快了刹淌。
mysql> explain select * from test_1 where user_id='1';
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------------+
| 1 | SIMPLE | test_1 | ref | idx_user_id | idx_user_id | 92 | const | 1 | Using where |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------------+
1 row in set (0.00 sec)
此外饶氏，還需要注意的是：
數(shù)字類型的0001等價(jià)于1
字符串的0001和1不等價(jià)
mysql> select * from test_1;
+----+---------+------+
| id | user_id | name |
+----+---------+------+
| 1 | 0001 | kate |
| 2 | 1101 | Jim |
| 3 | 1 | Jim |
+----+---------+------+
3 rows in set (0.01 sec)
mysql> select * from test_1 where user_id=1;
+----+---------+------+
| id | user_id | name |
+----+---------+------+
| 1 | 0001 | kate |
| 3 | 1 | Jim |
+----+---------+------+
2 rows in set (0.00 sec)
mysql> select * from test_1 where user_id='1';
+----+---------+------+
| id | user_id | name |
+----+---------+------+
| 3 | 1 | Jim |
+----+---------+------+
1 row in set (0.00 sec)
如果表定義的是int字段，傳入的是字符串芦鳍，則不會(huì)發(fā)生隱式轉(zhuǎn)換嚷往。
看下面的測(cè)試：
CREATE TABLE `test_2` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) NOT NULL,
`name` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8
mysql> explain select * from test_2 where user_id=1;
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
| 1 | SIMPLE | test_2 | ref | idx_user_id | idx_user_id | 4 | const | 2 | |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> explain select * from test_2 where user_id='1';
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
| 1 | SIMPLE | test_2 | ref | idx_user_id | idx_user_id | 4 | const | 2 | |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql隱式轉(zhuǎn)換規(guī)則：
a. 兩個(gè)參數(shù)至少有一個(gè)是 NULL 時(shí)，比較的結(jié)果也是 NULL柠衅，例外是使用 <=> 對(duì)兩個(gè) NULL 做比較時(shí)會(huì)返回 1皮仁，這兩種情況都不需要做類型轉(zhuǎn)換
b. 兩個(gè)參數(shù)都是字符串，會(huì)按照字符串來(lái)比較菲宴，不做類型轉(zhuǎn)換
c. 兩個(gè)參數(shù)都是整數(shù)贷祈，按照整數(shù)來(lái)比較，不做類型轉(zhuǎn)換
d. 十六進(jìn)制的值和非數(shù)字做比較時(shí)喝峦，會(huì)被當(dāng)做二進(jìn)制串
e. 有一個(gè)參數(shù)是 TIMESTAMP 或 DATETIME势誊，并且另外一個(gè)參數(shù)是常量，常量會(huì)被轉(zhuǎn)換為 timestamp
f. 有一個(gè)參數(shù)是 decimal 類型谣蠢，如果另外一個(gè)參數(shù)是 decimal 或者整數(shù)粟耻，會(huì)將整數(shù)轉(zhuǎn)換為 decimal 后進(jìn)行比較，如果另外一個(gè)參數(shù)是浮點(diǎn)數(shù)眉踱，則會(huì)把 decimal 轉(zhuǎn)換為浮點(diǎn)數(shù)進(jìn)行比較
g. 所有其他情況下挤忙，兩個(gè)參數(shù)都會(huì)被轉(zhuǎn)換為浮點(diǎn)數(shù)再進(jìn)行比較
開(kāi)發(fā)人員可能知道存在這么一個(gè)隱式類型轉(zhuǎn)換的坑，但卻又經(jīng)常不注意谈喳，所以干脆無(wú)需記住那么多規(guī)則册烈，該什么類型就與什么類型比較。
參考網(wǎng)站：
