【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)

分析例三、例六可知，or 左右若存在不在索引中的列，则会导致索引失效，or条件有括号时失效不影响其它索引，否则影响其他索引
分析例一、例四可知，or 左右的字段为同一个字段且满足索引的最左原则，则为索引范围扫描
分析例二、例五可知，or 左右的字段为组合索引的不同字段且满足最左原则，当存在索引覆盖时为索引扫描，否则为全表扫描

---

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

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_sex  |            1 | sex         | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

例七

mysql> explain select name from teacher where name = '教师1' or sex = 1;
+----+-------------+---------+------------+------+------------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys    | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+------------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | teacher | NULL       | ALL  | idx_name,idx_sex | NULL | NULL    | NULL |    9 |    55.56 | Using where |
+----+-------------+---------+------------+------+------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 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,idx_sex | NULL | NULL    | NULL |    9 |    55.56 | Using where |
+----+-------------+-------+------------+------+------------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

分析例七、例八，or 左右为不同的单列索引字段时索引失效，全表扫描