where子句中使用 or 来连接条件的各种情况

在写sql的时候避免在where子句中使用 or 来连接条件，因为：

1. 使用or可能会使索引失效，从而全表扫描；

2. 两个列都是唯一索引，执行计划会走两个索引。

3. 两个列一个有唯一索引，另一个没有索引，不会走索引，因为整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定；

4. 两个列都有索引但是不是唯一索引，看查询条件占数据的情况，如果查询条件都是唯一或者说很少的情况，会走两个索引，有一个不是（例如查询性别=‘男’，而‘男’占大多数，就不会走索引），这种情况就是全表扫描，原因和3相同；

5. 联合索引的情况，分两种

   1. ）select 联合索引的两个列 from 表 where name = 'ew' or age = 12;    这种的执行计划是index，显示索引树扫描二级索引。
   2. ）select * from 表where name = 'ew' or age = 12;   这种执行计划就是all，因为or会导致索引失效。

6. 虽然mysql是有优化器的，出于效率与成本考虑，遇到or条件，索引还是可能失效的；

优化：

使用union all

SELECT * FROM user WHERE id=1 
UNION ALL
SELECT * FROM user WHERE name=5000

实验1（一个是唯一索引，另一个是普通索引）：

 创建表且插入数据

create table orsql(
	id int PRIMARY KEY,
	name VARCHAR(10),
	age INT,
	INDEX inx_orsql_name(name)
)
drop table orsql;
delete from orsql;
insert into orsql values(1,'1',1);
insert into orsql values(2,'2',1);
insert into orsql values(3,'3',1);
insert into orsql values(4,'4',1);insert into orsql values(10,'10',1);
insert into orsql values(5,'5',1);
insert into orsql values(6,'6',1);
insert into orsql values(7,'7',1);
insert into orsql values(8,'8',1);
insert into orsql values(9,'9',1);
insert into orsql values(11,'1',1);
insert into orsql values(12,'1',1);
insert into orsql values(13,'1',1);
insert into orsql values(14,'1',1);insert into orsql values(20,'1',1);
insert into orsql values(15,'1',1);
insert into orsql values(16,'1',1);
insert into orsql values(17,'1',1);
insert into orsql values(18,'1',1);
insert into orsql values(19,'1',1);

explain 
select * from orsql where id =2 or name = '2';

这种情况就是两个列一个是唯一索引另一个是普通索引（虽然name不是唯一索引，但是查询条件在数据里是唯一的所以类似唯一索引），这种情况type是index_merge，走的是两个索引，并且将结果union

如果name=’1‘，会分两种情况：

情况1，只查询name，那么在二级索引上就可以，但是这种是index，类似于全表扫描

explain 
select name from orsql where id =2 or name = '1';

 情况2，select * ，那么这种就在主键索引上，进行全表扫描。

explain 
select * from orsql where id =2 or name = '1';

补充：name = 'a'  or   name='b'的情况

情况1：

explain 
select * from orsql where id = 2 or id =4;

explain 
select * from orsql3 where name = '3' or name = '4';

        情况1是本列是唯一索引的情况，可以看出用二级索引的范围查询（type=range），extra是using index condition索引下推。

情况2：

explain 
select * from orsql where name = '3' or name = '1';

        情况2就是普通索引=’重复率多的数据‘，执行计划走的是全表扫描

情况3：

explain 
select NAME from orsql where name = '3' or name = '1';

        情况3就是2的优化，查询索引列。

实验2（两个都是普通索引）：

create table orsql5(
	id int PRIMARY KEY,
	name VARCHAR(10),
	age INT,
	INDEX inx_orsql_name(name),
	INDEX inx_orsql_age(age)
)

insert into orsql5 values(1,'1',1);
insert into orsql5 values(2,'2',1);
insert into orsql5 values(3,'3',1);
insert into orsql5 values(4,'4',1);insert into orsql5 values(10,'10',1);
insert into orsql5 values(5,'5',1);
insert into orsql5 values(6,'6',1);
insert into orsql5 values(7,'7',1);
insert into orsql5 values(8,'8',1);
insert into orsql5 values(9,'9',1);
insert into orsql5 values(11,'1',2);
insert into orsql5 values(12,'2',2);
insert into orsql5 values(13,'3',2);
insert into orsql5 values(14,'4',2);insert into orsql5 values(20,'10',1);
insert into orsql5 values(15,'5',2);
insert into orsql5 values(16,'6',2);
insert into orsql5 values(17,'7',3);
insert into orsql5 values(18,'8',4);
insert into orsql5 values(19,'9',5);

这种情况取决于查询条件，查询的条件占大多数，就算另一个查询的是唯一的，也会all 

explain 
select * from orsql5 where age =1 or name = '6';

查询的条件占少数，就相当于两个唯一索引，会走索引。 

explain 
select * from orsql5 where age =3 or name = '6';

 实验3（只有一个联合索引）：

create table orsql2(
	id int PRIMARY KEY,
	name VARCHAR(10),
	age INT,
	INDEX inx_orsql_name(id,name)
)

insert into orsql2 values(1,'1',1);
insert into orsql2 values(2,'2',1);
insert into orsql2 values(3,'3',1);
insert into orsql2 values(4,'4',1);insert into orsql2 values(10,'10',1);
insert into orsql2 values(5,'5',1);
insert into orsql2 values(6,'6',1);
insert into orsql2 values(7,'7',1);
insert into orsql2 values(8,'8',1);
insert into orsql2 values(9,'9',1);


explain 
select id,name from orsql2 where id =2 or name = '6';

就是index全表扫描联合索引。

explain 
select * from orsql2 where id =2 or name = '6';

查询全部还是走全表扫描。

 实验4（一个联合索引并在第二列建一个普通索引）：

create table orsql3(
	id int PRIMARY KEY,
	name VARCHAR(10),
	age INT,
	INDEX inx_orsql_name_age(name,age)
)

insert into orsql3 values(1,'1',1);
insert into orsql3 values(2,'2',1);
insert into orsql3 values(3,'3',1);
insert into orsql3 values(4,'4',1);insert into orsql3 values(10,'10',1);
insert into orsql3 values(5,'5',1);
insert into orsql3 values(6,'6',1);
insert into orsql3 values(7,'7',1);
insert into orsql3 values(8,'8',1);
insert into orsql3 values(9,'9',1);
insert into orsql3 values(11,'1',1);
insert into orsql3 values(12,'2',1);
insert into orsql3 values(13,'3',1);
insert into orsql3 values(14,'4',1);insert into orsql3 values(20,'10',1);
insert into orsql3 values(15,'5',1);
insert into orsql3 values(16,'6',1);
insert into orsql3 values(17,'7',1);
insert into orsql3 values(18,'8',1);
insert into orsql3 values(19,'9',1);

create index inx_orsql_age on orsql3(age);

explain 
select name,age from orsql3 where age =1 or name = '6';

这种情况相当于有两个索引的情况，实验结果也和两种普通索引情况相同（分两种情况，上图是第一种情况，第二种情况不再赘述）。
 

总结：

1、只要是查询的列可以在二级索引找到，执行计划就是index，扫描索引树。找全部列那就是全表扫描。

2、只要两个列是唯一索引或者查询的值占比很少就走两个索引，type是index_meger

3、两个列其中一个查找不是唯一值或者占比很多，分两种情况

        1）查询的列在二级索引可以全部找到，type是index

        2）查询的列在二级索引上找不到，type是all