3.接下来我们看看多表关联查询

mysql> explain select a.pad,b.pad from sbtest1 a,sbtest2 b where a.id=b.id and (a.c='123' or b.c='1234');               
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+--------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref      | rows  | filtered | Extra      |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+--------+----------+-------------+
|  1 | SIMPLE      | a    | NULL      | ALL    | PRIMARY,c_1  | NULL    | NULL    | NULL      | 986400 |  100.00 | NULL        |
|  1 | SIMPLE      | b    | NULL      | eq_ref | PRIMARY,c_2  | PRIMARY | 4      | test.a.id |      1 |  100.00 | Using where |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+--------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

mysql> explain select a.pad,b.pad from sbtest1 a,sbtest2 b where a.id=b.id and (a.c='123' or a.c='1234');
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key    | key_len | ref      | rows | filtered | Extra                |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-----------------------+
|  1 | SIMPLE      | a    | NULL      | range  | PRIMARY,c_1  | c_1    | 120    | NULL      |    2 |  100.00 | Using index condition |
|  1 | SIMPLE      | b    | NULL      | eq_ref | PRIMARY      | PRIMARY | 4      | test.a.id |    1 |  100.00 | NULL                  |
+----+-------------+-------+------------+--------+---------------+---------+---------+-----------+------+----------+-----------------------+
2 rows in set, 1 warning (0.00 sec)

mysql>

可以看出在多表查询的情况下or条件如果不在同一个表内执行计划表a的查询不走索引。

我们试试看用union all来进行改写