mysql> explain select pad from sbtest1 where k='501462' or pad='00592560354-80393027097-78244247549-39135306455-88936868384';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| 1 | SIMPLE | sbtest1 | NULL | ALL | k_1 | NULL | NULL | NULL | 986400 | 19.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
pad列没索引所以整条的sql就不会使用到索引
假设使用union all来改写一样需要全表扫描所以意义也不大，如下
mysql> explain select pad from sbtest1 where k='501462' union all select pad from sbtest1 where pad='00592560354-80393027097-78244247549-39135306455-88936868384';
+----+-------------+---------+------------+------+---------------+------+---------+-------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+-------+--------+----------+-------------+
| 1 | PRIMARY | sbtest1 | NULL | ref | k_1 | k_1 | 4 | const | 113 | 100.00 | NULL |
| 2 | UNION | sbtest1 | NULL | ALL | NULL | NULL | NULL | NULL | 986400 | 10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+-------+--------+----------+-------------+
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来进行改写