mysql or 导致的全表扫描

or 导致的全表扫描

explain select id from tradelog where tradeid = '12' or operator = 10 limit 10;
+------+---------------+------+---------+------+--------+----------+-------------+
| type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+------+---------------+------+---------+------+--------+----------+-------------+
| ALL  | tradeid       | NULL | NULL    | NULL | 389107 |    10.00 | Using where |
+------+---------------+------+---------+------+--------+----------+-------------+

查询条件中有 or 时，mysql 会分别查询每一个条件，然后再把结果合并在一起。

先对 tradeid = ‘12’ 进行判断，发现可以使用索引，再对 operator = 10 进行判断，发现不能使用索引，只好全表扫描，即然都全表扫描了，顺带也就可以判断当前记录的 tradeid 是否等于 12，就不需要在单独再搜索 tradeid 索引树了。

所以当 or 条件中出现没有索引的字段会导致使用全表扫描。如果出现的字段都有索引，mysql会尽量使用每个列的索引

explain select id from tradelog where tradeid = '12' or id =10  limit 10;
+----+-------------+----------+-------------+-----------------+-----------------+
| id | select_type | table    | type        | possible_keys   | key             |
+----+-------------+----------+-------------+-----------------+-----------------+
|  1 | SIMPLE      | tradelog | index_merge | PRIMARY,tradeid | tradeid,PRIMARY |
+----+-------------+----------+-------------+-----------------+-----------------+

+---------+------+------+----------+-------------------------------------------+
| key_len | ref  | rows | filtered | Extra                                     |
+---------+------+------+----------+-------------------------------------------+
| 131,4   | NULL |    2 |   100.00 | Using union(tradeid,PRIMARY); Using where |
+---------+------+------+----------+-------------------------------------------+

虽然条件中也有 or ，但 tradeid 和 id 都有索引，mysql 分别单独对这两个索引扫描，然后把找到的结果再合并到一起。执行计划显示 type:index_merge ， key:tradeid,PRIMARY，Extra:Using union

同样的条件，把 or 换成 and 时是可以走索引的，因为找的是要满足所有条件的记录，那么就可以在最小的索引树先找到一条满足条件的记录，再检查这条记录是否还满足其它条件

explain select id from tradelog where tradeid = '12' and  operator = 10 limit 10;
+------+---------------+---------+---------+-------+------+----------+-------------+
| type | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+------+---------------+---------+---------+-------+------+----------+-------------+
| ref  | tradeid       | tradeid | 131     | const |    1 |    10.00 | Using where |
+------+---------------+---------+---------+-------+------+----------+-------------+

key:tradeid 显示使用了 tradeid 索引。通过索引树先找到一条 tradeid = ‘12’的记录，再回表查询 operator 是否等于 10。