- 當查詢語句中包含對索引字段的函數(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)