- 當查詢語句中包含對索引字段的函數(shù)操作時账嚎，查詢將不會走索引，例如表t下有已建好索引的字段name，普通查詢語句執(zhí)行計劃如下：
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)
結果顯示使用了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ù)操作
查詢變量類型與字段類型不一致時，會進行類型轉換召锈，具體轉換規(guī)則可以通過以下方式驗證：
mysql> select 3>'2';
+-------+
| 3>'2' |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)
返回1旁振，表明將字符轉換為int進行比較。
若查詢語句中存在這類轉換烟勋，那么索引也將失效规求，例如：
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字段上的索引，因為name轉換為了int進行比較卵惦。
另外，對于不能轉換的字符瓦戚，mysql將轉換為0進行比較沮尿，例如：
mysql> select 0='abc';
+---------+
| 0='abc' |
+---------+
| 1 |
+---------+
1 row in set, 1 warning (0.00 sec)
