mysql笔记-字段上的函数操作会使索引失效
2021-03-29 本文已影响0人
matthewfly
- 当查询语句中包含对索引字段的函数操作时，查询将不会走索引，例如表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)
