Mysql之SQL语句里3个会导致性能问题的场景

条件字段做函数操作：
    破坏了索引值的有序性，所以优化器决定放弃走树搜索功能，但不是放弃走索引，只是不能使用索引的快速定位功能，可以使用使用全索引扫描，当然也可能会直接遍历主键索引。
 

mysql> desc select * from t  where substr(age,1,1)='1';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   10 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> desc select * from t  where age like '1%';
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t     | NULL       | range | idx_age       | idx_age | 15      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

 上面例子中，age字段是varchar(4)类型且列上有idx_age索引，从两种查询方式和执行计划可以明显看出对条件字段做函数操作和对传参做操作的区别。

隐式类型转换：

mysql> desc select * from t where age=20;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | idx_age       | NULL | NULL    | NULL |   10 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

mysql> show warnings;
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                                                       |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1739 | Cannot use ref access on index 'idx_age' due to type or collation conversion on field 'age'                                                                                   |
| Warning | 1739 | Cannot use range access on index 'idx_age' due to type or collation conversion on field 'age'                                                                                 |
| Note    | 1003 | /* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`city` AS `city`,`test`.`t`.`name` AS `name`,`test`.`t`.`age` AS `age` from `test`.`t` where (`test`.`t`.`age` = 20) |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> desc select * from t where age='20';
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t     | NULL       | ref  | idx_age       | idx_age | 15      | const |    5 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                                         |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select `test`.`t`.`id` AS `id`,`test`.`t`.`city` AS `city`,`test`.`t`.`name` AS `name`,`test`.`t`.`age` AS `age` from `test`.`t` where (`test`.`t`.`age` = '20') |
+-------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

 上面例子中，age字段是varchar(4)类型且列上有idx_age索引，从两种查询方式和执行计划，以及执行完Mysql的warning中我们都能看出，在 MySQL 中，字符串和数字做比较的话，是将字符串转换成数字。所以select * from t where age=20时，数据库相当于是先对age字段做了运算(隐式转换)，然后再和20相比，这也和上面条件字段做函数操作不走索引的快速定位功能相呼应。

隐式字符编码转换：    
    字符集utf8mb4是utf8的超集，在程序设计语言里面，做自动类型转换的时候，为了避免数据在转换的过程中由于截断导致数据错误，也都是"按数据长度增长的方向"进行转换的。
    所以说当这两个字段比较时，会先把utf8字符串转换成uft8mb4字符集，再做比较，就有相当于在uft8的列上做了函数操作(隐式转换)

3种情况其实本质是一样的，就是在原本计划走索引快速扫描的列上进行了函数操作(显式or隐式)，导致其不能“按计划行事”