varchar int 查詢 到底什麼情況下走索引？
- 2020 年 4 月 1 日
- 筆記
一個字元類型的、一個int類型的，查詢的時候到底會不會走索引，其實很多工作了幾年的開發人員有時也會暈，下面就用具體事例來測試一下。
1. 準備工作
先準備2張表，以備後續測試使用。
表1：創建表test1,總共3列，其中id 是主鍵（int）,c_no 為int型，且有索引，c_2為普通欄位
/*創建表test1 */ create table test1(id int primary key,c_no int ,c_2 varchar(1),key c_no(c_no)); /* 插入一些測試數據 */ insert into test1 values(1,1,'0'),(2,2,'1'),(3,4,'1'),(4,6,'0'),(5,7,''1),(6,11,'2'),(7,5,'3'),(8,100,'0'),(9,30,'1'),(10,50,'0');
表2： 創建表test1,總共3列，其中id 是主鍵（int）,c_no 為字元型，且有索引，c_2為普通欄位
/* 創建test2 */ create table test2(id int primary key auto_increment,c_no varchar(11) ,c2 varchar(2),key c_no(c_no)); /* 插入一些測試數據 */ insert into test2 values(1,'5','1'),(4,'100','0'),(3,'30','1'),(10,'500','0'),(11,'20','0'),(12,'20a','0'),(15,'020b','1');
兩張表的差異是c_no的欄位類型不同。
2. 等值查詢測試
2.1 測試test1
test1.c_no欄位為int類型，下面分別用整型和字元串進行比較，查看是否走索引。對應的執行計劃如下：
mysql> explain select * from test1 where c_no='100'; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | test1 | NULL | ref | c_no | c_no | 5 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from test1 where c_no=100; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | test1 | NULL | ref | c_no | c_no | 5 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
可見，兩種方式均走索引了，且走的是c_no的索引，類型為ref為const（常量的等值查詢），掃行數為1
也就是說當表中的欄位類型為整型時，無論查詢用字元串類型的數字還是int類型的數字均能走索引。其中用int類型的值查詢能走索引可以容易理解，那麼，字元型的為什麼能走？ 其實這裡的字元類型做了隱式轉化，上例中就相當於
mysql> explain select * from test1 where c_no=CAST('100' as UNSIGNED); +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | test1 | NULL | ref | c_no | c_no | 5 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
2.2 測試 test2 表
以同樣的方式測試一下test2的查詢情況
先測試正常情況下字元型與字元型比較，結果可想而知，可以走索引，如下：
mysql> explain select * from test2 where c_no='100'; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | test2 | NULL | ref | c_no | c_no | 47 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
而，如果是整型再查呢？結果如下（很遺憾，不能走索引了）
mysql> explain select * from test2 where c_no=100; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | test2 | NULL | ALL | c_no | NULL | NULL | NULL | 7 | 14.29 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 3 warnings (0.00 sec)
也就是說，表中欄位為字元類型的時候，查詢的值為整型時，無法走索引了。
那這句相當於如下情況：
mysql> explain select * from test2 where cast(c_no as unsigned)=100; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | test2 | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 100.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
也就是c_no做了隱式轉化。因為如果是100做了影視轉化，那麼結果應該是可以走索引，例如：
mysql> explain select * from test2 where c_no=cast(100 as char); +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | test2 | NULL | ref | c_no | c_no | 47 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
由此，我們也應證了如果欄位做了函數計算後，該列上即使有索引也無法使用（MySQL8.0之前的版本）。
2.3 進一步測試
其實針對test2表 還可以測試一點，進一步證明是c_no欄位做了隱式轉化，例如：
mysql> select * from test2 where c_no=20; +----+------+------+ | id | c_no | c2 | +----+------+------+ | 11 | 20 | 0 | | 12 | 20a | 0 | | 15 | 020b | 1 | +----+------+------+ 3 rows in set, 2 warnings (0.00 sec)
另外，看到了2個警告，內容如下：
mysql> show warnings; +---------+------+------------------------------------------+ | Level | Code | Message | +---------+------+------------------------------------------+ | Warning | 1292 | Truncated incorrect DOUBLE value: '20a' | | Warning | 1292 | Truncated incorrect DOUBLE value: '020b' | +---------+------+------------------------------------------+ 2 rows in set (0.00 sec)
更加證明了轉化為數字型（預轉為double）
3. 小結
通過上面的簡單測試，即可發現如下結論：
- 當表中的欄位類型為整型時，無論查詢用字元串類型的數字還是int類型的數字均能走索引；
- 表中欄位為字元類型的時候，查詢的值為整型時，無法走索引；
- 如果欄位做了函數計算後，該列上即使有索引也無法使用（MySQL8.0之前的版本）
因此開發同學在寫SQL的時候要注意SQL的寫法，缺少一個單引號可能導致很大的性能差異。
