- 當(dāng)查詢語句中包含對(duì)索引字段的函數(shù)操作時(shí)，查詢將不會(huì)走索引，例如表t下有已建好索引的字段name，普通查詢語句執(zhí)行計(jì)劃如下：
mysql> explain select * from t where name='a';
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t | NULL | ref | idx_name | idx_name | 83 | const | 2 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
結(jié)果顯示使用了name的索引。而添加函數(shù)操作后：
mysql> explain select * from t where substr(name, 0, 1)='a';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
將不再使用name字段上的索引。
- 2.隱含的函數(shù)操作
查詢變量類型與字段類型不一致時(shí)，會(huì)進(jìn)行類型轉(zhuǎn)換，具體轉(zhuǎn)換規(guī)則可以通過以下方式驗(yàn)證：
mysql> select 3>'2';
+-------+
| 3>'2' |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)
返回1，表明將字符轉(zhuǎn)換為int進(jìn)行比較。
若查詢語句中存在這類轉(zhuǎn)換，那么索引也將失效，例如：
mysql> explain select * from t where name=1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t | NULL | ALL | idx_name | NULL | NULL | NULL | 3 | 33.33 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 4 warnings (0.00 sec)
接口顯示沒有使用name字段上的索引，因?yàn)閚ame轉(zhuǎn)換為了int進(jìn)行比較。
另外，對(duì)于不能轉(zhuǎn)換的字符，mysql將轉(zhuǎn)換為0進(jìn)行比較，例如：
mysql> select 0='abc';
+---------+
| 0='abc' |
+---------+
| 1 |
+---------+
1 row in set, 1 warning (0.00 sec)
