mysql笔记-字段上的函数操作会使索引失效

• 1. 当查询语句中包含对索引字段的函数操作时，查询将不会走索引，例如表t下有已建好索引的字段name，普通查询语句执行计划如下：

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的索引。而添加函数操作后：

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.隐含的函数操作
  查询变量类型与字段类型不一致时，会进行类型转换，具体转换规则可以通过以下方式验证：

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)