bitscn.com
在某些情况下，or条件可以避免全表扫描的。
1 .where 语句里面如果带有or条件, myisam表能用到索引，innodb不行。
1)myisam表：
 create table if not exists `a` (
  `id` int(1) not null auto_increment,
  `uid` int(11) not null,
  `anum` char(20) default null,
  primary key (`id`),
  key `uid` (`uid`)
) engine=myisam  default charset=latin1 auto_increment=6 ;
mysql> explain select * from a where id=1 or uid =2;
+----+-------------+-------+-------------+---------------+-------------+---------+------+------+---------------------------------------+
| id | select_type | table | type        | possible_keys | key         | key_len | ref  | rows | extra                                 |
+----+-------------+-------+-------------+---------------+-------------+---------+------+------+---------------------------------------+
|  1 | simple      | a     | index_merge | primary,uid   | primary,uid | 4,4     | null |    2 | using union(primary,uid); using where |
+----+-------------+-------+-------------+---------------+-------------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)
2)innodb表：
create table if not exists `a` (
  `id` int(1) not null auto_increment,
  `uid` int(11) not null,
  `anum` char(20) default null,
  primary key (`id`),
  key `uid` (`uid`)
) engine=innodb  default charset=latin1 auto_increment=6 ;
mysql>  explain select * from a where id=1 or uid =2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | simple      | a     | all  | primary,uid   | null | null    | null |    5 | using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
2 .必须所有的or条件都必须是独立索引：
+-------+----------------------------------------------------------------------------------------------------------------------
| table | create table
+-------+----------------------------------------------------------------------------------------------------------------------
| a     | create table `a` (
  `id` int(1) not null auto_increment,
  `uid` int(11) not null,
  `anum` char(20) default null,
  primary key (`id`)
) engine=myisam auto_increment=6 default charset=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)
explain查看：
mysql> explain select * from a where id=1 or uid =2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | simple      | a     | all  | primary       | null | null    | null |    5 | using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
全表扫描了。
摘自 hguisu的专栏
bitscn.com