mysql的where查询语句后有多个“or”的SQL语句执行分析
看到一篇文章里面提到where查询语句后有多个“or”的SQL语句执行分析，原来没有碰到这样的情况，做个实验测试下，详细过程如下：
一个数据表person有3个字段，都有索引。
mysql> show index from person;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| person | 0 | PRIMARY | 1 | id | A | 9 | NULL | NULL | | BTREE | |
| person | 1 | name | 1 | name | A | 9 | NULL | NULL | YES | BTREE | |
| person | 1 | descs | 1 | descs | A | 9 | NULL | NULL | YES | BTREE | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)
一个字段的情况，用到了索引，是正常的。
mysql> explain select * from person where id = 3;
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | person | const | PRIMARY | PRIMARY | 2 | const | 1 | |
+----+-------------+--------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
mysql> explain select * from person where name = 'chF';
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
| 1 | SIMPLE | person | ref | name | name | 181 | const | 1 | Using where |
+----+-------------+--------+------+---------------+------+---------+-------+------+-------------+
1 row in set (0.00 sec)
mysql> explain select * from person where descs = 'tA1C+_2BbU9YMATi';
+----+-------------+--------+------+---------------+-------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+-------+---------+-------+------+-------------+
| 1 | SIMPLE | person | ref | descs | descs | 63 | const | 1 | Using where |
+----+-------------+--------+------+---------------+-------+---------+-------+------+-------------+
1 row in set (0.00 sec)
2个字段的情况，用到了索引，正常。
mysql> explain select * from person where id = 3 or descs = 'tA1C+_2BbU9YMATi';
+----+-------------+--------+-------------+---------------+---------------+---------+------+------+-----------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------------+---------------+---------------+---------+------+------+-----------------------------------------+
| 1 | SIMPLE | person | index_merge | PRIMARY,descs | PRIMARY,descs | 2,63 | NULL | 2 | Using union(PRIMARY,descs); Using where |
+----+-------------+--------+-------------+---------------+---------------+---------+------+------+-----------------------------------------+
1 row in set (0.00 sec)
mysql> explain select * from person where name = 'syy' or descs = 'tA1C+_2BbU9YMATi';
+----+-------------+--------+-------------+---------------+------------+---------+------+------+--------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+-------------+---------------+------------+---------+------+------+--------------------------------------+
| 1 | SIMPLE | person | index_merge | name,descs | name,descs | 181,63 | NULL | 2 | Using union(name,descs); Using where |
+----+-------------+--------+-------------+---------------+------------+---------+------+------+--------------------------------------+
1 row in set (0.00 sec)
3个字段的情况，没有用到索引，异常。
mysql> explain select * from person where id = 3 or name = 'syy' or descs = 'tA1C+_2BbU9YMATi';
+----+-------------+--------+------+--------------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+--------------------+------+---------+------+------+-------------+
| 1 | SIMPLE | person | ALL | PRIMARY,name,descs | NULL | NULL | NULL | 9 | Using where |
+----+-------------+--------+------+--------------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
强制使用其中2个字段，没有用到索引，异常。
mysql> explain select * from person force index(primary, name) where id = 3 or name = 'syy' or descs = 'tA1C+_2BbU9YMATi';
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | person | ALL | PRIMARY,name | NULL | NULL | NULL | 9 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
综述所上：
只要是两个索引，都可以走index_merge，换成三个就不行了。
即使是强行指定用某两个索引也不行，索引虽然都能够找到，但优化器不使用任何一个。
原因如下：
即使强制使用了两个索引，那么会有剩下一个条件不会走索引，那么对于该条件的过滤还是要通过表查询，这样，对于 mysql来说就相当于要两个索引的index_mereg后再读表，而且仍然要做一次全表扫描，那还不如就作一次表扫描，Mysql最终还是选择一次表扫描，这样是可以理解的。
在Mysql官方文档上，在提示了mysql用某一个索引后，也就相当于告诉了mysql不要用其他的相关的一些索引。估计 Mysql也并没有去实现三个索引的index_merge，实际上想想就算是实现了，通过读三个索引然后做merge再去取表的记录，其消耗可能也并不会太小，对于Mysql的这个选择也无可厚非。
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
