mysql的where查询语句后有多个“or”的SQL语句执行分析！

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的这个选择也无可厚非。