【MySql】索引优化～某些情况下or会导致索引失效

本机环境

$ mysql --version
mysql  Ver 14.14 Distrib 5.7.18, for macos10.12 (x86_64) using  EditLine wrapper

teacher表中有一个组合(多列)索引，在name和sex上

mysql> show index from teacher;
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| teacher |          0 | PRIMARY      |            1 | id          | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
| teacher |          1 | idx_name_sex |            1 | name        | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
| teacher |          1 | idx_name_sex |            2 | sex         | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> select * from teacher;
+----+---------+-------------+------------------+-----+-----+
| id | name    | telephone   | addr             | age | sex |
+----+---------+-------------+------------------+-----+-----+
|  1 | 教师1   | 13679280019 | 中国武汉1区      |  18 |   1 |
|  2 | 教师2   | 13679280119 | 中国武汉2区      |  18 |   0 |
|  3 | 教师3   | 13679280219 | 中国武汉3区      |  18 |   0 |
|  4 | 教师4   | 13679280319 | 中国武汉4区      |  18 |   1 |
|  5 | 教师5   | 13679280419 | 中国武汉5区      |  18 |   0 |
|  6 | 教师6   | 13679280519 | 中国武汉6区      |  18 |   1 |
|  7 | 教师7   | 13679280619 | 中国武汉7区      |  18 |   1 |
|  8 | 教师8   | 13679280719 | 中国武汉8区      |  18 |   0 |
|  9 | 教师9   | 13679280819 | 中国武汉9区      |  18 |   1 |
+----+---------+-------------+------------------+-----+-----+
9 rows in set (0.00 sec)

例一

mysql> explain select name from teacher t where t.name = '教师3' or t.name = '教师7';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t     | NULL       | range | idx_name_sex  | idx_name_sex | 202     | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

例二

mysql> explain select name from teacher t where t.name = '教师1' or t.sex = 1;
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t     | NULL       | index | idx_name_sex  | idx_name_sex | 206     | NULL |    9 |    20.99 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

例三

mysql> explain select t.name from teacher t where t.name = '教师1' or t.addr = '中国武汉4区';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | idx_name_sex  | NULL | NULL    | NULL |    9 |    20.99 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

例四

mysql> explain select t.* from teacher t where t.name = '教师3' or t.name = '教师7';
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key          | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t     | NULL       | range | idx_name_sex  | idx_name_sex | 202     | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+--------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

例五

mysql> explain select t.* from teacher t where t.name = '教师1' or t.sex = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | idx_name_sex  | NULL | NULL    | NULL |    9 |    20.99 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

例六

mysql> explain select t.* from teacher t where t.name = '教师1' or t.addr = '中国武汉4区';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t     | NULL       | ALL  | idx_name_sex  | NULL | NULL    | NULL |    9 |    20.99 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

---

将多列索引拆分成多个单列索引

mysql> show index from teacher;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| teacher |          0 | PRIMARY  |            1 | id          | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
| teacher |          1 | idx_name |            1 | name        | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
| teacher |          1 | idx_age  |            1 | age         | A         |           1 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

例五

mysql> explain select name from teacher t where t.name = '教师3' or t.name = '教师7';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t     | NULL       | range | idx_name      | idx_name | 202     | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

例六

mysql> explain select name from teacher t where t.name = '教师1' or t.sex = 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 |    9 |    20.99 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

例七

mysql> explain select t.* from teacher t where t.name = '教师3' or t.name = '教师7';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t     | NULL       | range | idx_name      | idx_name | 202     | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

例八

mysql> explain select t.* from teacher t where t.name = '教师1' or t.sex = 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 |    9 |    20.99 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

---

将多个单列索引删除只保留一个单列索引

mysql> show index from teacher;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| teacher |          0 | PRIMARY  |            1 | id          | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
| teacher |          1 | idx_name |            1 | name        | A         |           9 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

例九

mysql> explain select name from teacher t where t.name = '教师3' or t.name = '教师7';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | t     | NULL       | range | idx_name      | idx_name | 202     | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

例十

mysql> explain select name from teacher t where t.name = '教师1' or t.sex = 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 |    9 |    20.99 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

例十一

mysql> explain select t.* from teacher t where t.name = '教师3' or t.name = '教师7';
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | t     | NULL       | range | idx_name      | idx_name | 202     | NULL |    2 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

例十二

mysql> explain select t.* from teacher t where t.name = '教师1' or t.sex = 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 |    9 |    20.99 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)