索引优化实战八

一 点睛

1 字符串不加单引号索引失效。

2 少用 or，用它来连接时会使得索引失效。

二 实战——字符串不加单引号索引失效

mysql> select * from staffs;
+----+------+-----+---------+---------------------+
| id | name | age | pos     | add_time            |
+----+------+-----+---------+---------------------+
|  1 | z3   |  22 | manager | 2021-09-19 09:15:14 |
|  2 | July |  23 | dev     | 2021-09-19 09:15:14 |
|  3 | 2000 |  23 | dev     | 2021-09-19 09:15:14 |
+----+------+-----+---------+---------------------+
3 rows in set (0.00 sec)

# 使用单引号可以查出
mysql> select * from staffs where name='2000';
+----+------+-----+-----+---------------------+
| id | name | age | pos | add_time            |
+----+------+-----+-----+---------------------+
|  3 | 2000 |  23 | dev | 2021-09-19 09:15:14 |
+----+------+-----+-----+---------------------+
1 row in set (0.00 sec)

# 不使用单引号可以查出
mysql> select * from staffs where name=2000;
+----+------+-----+-----+---------------------+
| id | name | age | pos | add_time            |
+----+------+-----+-----+---------------------+
|  3 | 2000 |  23 | dev | 2021-09-19 09:15:14 |
+----+------+-----+-----+---------------------+
1 row in set, 1 warning (0.00 sec)

# 使用到索引
mysql> explain select * from staffs where name='2000';
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

# 发生了隐式的类型转换，索引失效
mysql> explain select * from staffs where name=2000;
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys           | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

三 实战——使用 or，使得索引失效

# 使用 or ，索引失效
mysql> explain select * from staffs where name='2000' or name='zhangsan';
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys           | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | index_staffs_nameAgePos | NULL | NULL    | NULL |    3 |    66.67 | Using where |
+----+-------------+--------+------------+------+-------------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

# 使用 union all 或者 union 来替代，索引生效
mysql> explain select * from staffs where name='2000' union select * from staffs where name='zhangsan';
+----+--------------+------------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------+
| id | select_type  | table      | partitions | type | possible_keys           | key                     | key_len | ref   | rows | filtered | Extra           |
+----+--------------+------------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------+
|  1 | PRIMARY      | staffs     | NULL       | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | const |    1 |   100.00 | NULL            |
|  2 | UNION        | staffs     | NULL       | ref  | index_staffs_nameAgePos | index_staffs_nameAgePos | 74      | const |    1 |   100.00 | NULL            |
| NULL | UNION RESULT | <union1,2> | NULL       | ALL  | NULL                    | NULL                    | NULL    | NULL  | NULL |     NULL | Using temporary |
+----+--------------+------------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------+
3 rows in set, 1 warning (0.00 sec)

四 总结