mysql or条件可以使用索引而避免全表

在某些情况下,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)

全表扫描了。

3. 用UNION替换OR (适用于索引列)

       通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 

       注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 

       在下面的例子中, LOC_ID 和REGION上都建有索引.
       高效: 

select loc_id , loc_desc , region from location where loc_id = 10   
union   
select loc_id , loc_desc , region  from location where region = "melbourne"

     低效: 

select loc_id , loc desc , region from location where loc_id = 10 or region = "melbourne"

如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

4. 用in来替换or  

     这是一条简单易记的规则,但是实际的执行效果还须检验,在oracle8i下,两者的执行路径似乎是相同的. 
低效: 
select…. from location where loc_id = 10 or loc_id = 20 or loc_id = 30 
高效 
select… from location where loc_in  in (10,20,30);

转自:http://blog.csdn.net/hguisu/article/details/7106159

原创文章,作者:s19930811,如若转载,请注明出处:http://www.178linux.com/3119

(0)
s19930811s19930811
上一篇 2015-04-13
下一篇 2015-04-13

相关推荐

  • LVM逻辑卷&RAID管理&磁盘配置详解

    LVM逻辑卷管理 应用场景:想象一下,你通过传统的硬盘分区方式为某些用户提供了一块磁盘,随着用户数据的不断增加,分区的容量告急。这时要增加容量,只能选择用另一块容量更大的硬盘或分区来替换,在这个过程中,你需要将数据先拷贝至新设备,拷贝完成之后,再用新设备替换容量告罄的设备。假设你很有耐心并且整个数据的拷贝过程是顺顺利利的,你也需要考虑靠背后文件的权限变化、替…

    Linux干货 2016-09-02
  • 亿级用户下的新浪微博平台架构

    序言     新浪微博在2014年3月公布的月活跃用户(MAU)已经达到1.43亿,2014年新年第一分钟发送的微博达808298条,如此巨大的用户规模和业务量,需要高可用(HA)、高并发访问、低延时的强大后台系统支撑。 微博平台第一代架构为LAMP架构,数据库使用的是MyIsam,后台用的是php,缓存为Memcache。 随着应用规模…

    2015-03-16
  • N25 – week 3 blog

    本周的blog开始使用了代码语言格式,我原本以为自然的才是最好的,orginal的才是最美的,但是我错了。。。我发现同学们都在各种markdown,各种排版。我说过我早已过了care这些的年纪,但是我不能脱离群体单独存在,所以我底下了傲娇的头。 本周开始blog标题改为英文,逼格满满,麦满分~ 下面开始第$wk_num周的作业 [root@dhcp-10-1…

    Linux干货 2016-12-19
  • 第十四周作业

    系统的INPUT和OUTPUT默认策略为DROP; 1、限制本地主机的web服务器在周一不允许访问;新请求的速率不能超过100个每秒;web服务器包含了admin字符串的页面不允许访问;web服务器仅允许响应报文离开本机; ]# iptables -P INPUT DROP #INPUT默认策略为DROP ]# iptables -P OUTPUT DROP…

    Linux干货 2017-04-12
  • 推荐-Linux命令帮助的获取

    帮助命令 1. 使用帮助命令和帮助选项来获取帮助 2. 使用man来查看命令使用手册(manual) 3. 使用info来查看命令的信息页 4. 程序自身的帮助文档 5. 程序官方文档 6. 发行版的官方文档 7. 使用Google搜索关键字 1. 使用帮助命令和帮助选项来获取帮助 Linux系统中命令分为【内部命令】和【外部命令】。 【内部命令】:内部命令…

    Linux干货 2016-03-25
  • crm命令行命令详解

    crm -status 显示当前集群状态 -configure 配置集群 a.show xml 显示当前集群配置信息(xml格式) b.property 显示集群可用的全局属性(键入之后按两下Tab) -verifiy 校验 -commit 提交 -node a.online 设置当前节点上线 b.show 显示当前节点状态 c.standby 将当前节点设…

    Linux干货 2017-11-04