Three scenarios in the SQL statement that cause performance problems
Condition fields do function operations:
Destroy the order of index values, so the optimizer decided to give up the tree search function, but not to give up the index, but can not use the index's fast positioning function, you can use the full index scan, of course, may also directly traverse the primary key index.
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)
In the above example, the age field is of type varchar(4) and the column has idx_age index. From the two query methods and execution plans, it is obvious that the difference between doing function operations on conditional fields and doing operations on parameters can be seen.
Implicit type conversion:
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) Wuxi Good ×× Hospital http://www.zzchnk.com/
In the above example, the age field is of type varchar(4) and the column has an idx_age index. From the two query methods and execution plans, as well as the warning after executing Mysql, we can see that in MySQL, strings and numbers are compared, and strings are converted to numbers. Therefore, when select * from t where age=20, the database is equivalent to first doing an operation on the age field (implicit conversion), and then comparing it with 20, which is also in line with the fast positioning function of the above conditional field without taking the index.
Implicit character encoding conversion:
Character set utf8mb4 is a superset of utf8. In programming languages, automatic type conversion is also performed "in the direction of data length growth" in order to avoid data errors caused by truncation during conversion.
Therefore, when comparing these two fields, the utf8 string will be converted to the uft8mb4 character set first, and then the comparison will be made, which is equivalent to doing a function operation on the uft8 column (implicit conversion).
In fact, the three cases are essentially the same, that is, a function operation (explicit or implicit) is performed on the column originally planned to go through the index quick scan, resulting in its failure to "act as planned"
