文档章节

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

夜辰
 夜辰
发布于 2017/07/25 17:39
字数 559
阅读 17
收藏 0

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

共有 人打赏支持
夜辰
粉丝 11
博文 26
码字总数 7946
作品 0
昌平
程序员
私信 提问
mysql explain中的type列含义和extra列的含义

很多朋友在用mysql进行调优的时候都肯定会用到explain来看select语句的执行情况,这里简单介绍结果中两个列的含义。 1 type列 官方的说法,说这列表示的是“访问类型”,更通俗一点就是:mysq...

Vincent-Duan
2015/03/31
0
0
mysql优化sql语句查询的方法(一)

第一方面:30种mysql优化sql语句查询的方法 1.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。   2.应尽量避免在 where 子句中使用!=或<>操作符...

落叶刀
2016/06/03
19
0
Mysql优化(基础*转载)

. 通过 show status和应用特点了解各种 SQL的执行频率 通过 SHOW STATUS 可以提供服务器状态信息,也可以使用 mysqladmin extende d-status 命令获得。 SHOW STATUS 可以根据需要显示 sessio...

四明狂客
2016/06/27
9
0
Mysql 查询优化

1、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 where and order by 涉及的列上建立索引。索引字段添加原则是 通过某个字段来 查询排序检索数据库时 应该加索引提高效率 2、...

石头记
2016/02/16
73
2
mysql 优化建议(转)

ysql处理海量数据时的一些优化查询速度方法 最近一段时间由于工作需要,开始关注针对Mysql数据库的select查询语句的相关优化方法。 由于在参与的实际项目中发现当mysql表的数据量达到百万级时...

小小人故事
2015/12/06
29
2

没有更多内容

加载失败,请刷新页面

加载更多

OSChina 周三乱弹 —— 有一天考拉麻麻拉肚子了

Osc乱弹歌单(2018)请戳(这里) 【今日歌曲】 @瘟神灬念 :分享周汇洋的单曲《Man Srae(曼斯拉之舞)》:美滋滋。。。。 手机党少年们想听歌,请使劲儿戳(这里) 我听了一下 赶紧关了, ...

小小编辑
52分钟前
23
4
oh-my-zsh 自定义

GitHub 地址 基于 oh-my-zsh 的自定义配置,增加了一些个人常用插件与皮肤。 采用的是 git submodule 来维护,包括 oh-my-zsh,之所以这么搞,主要是手头有多台 linux 需要维护, 每台机器、...

郁也风
今天
6
0
Docker安装踩坑:E_FAIL 0x80004005的解决

参考 菜鸟教程--Windows Docker 安装 http://www.runoob.com/docker/windows-docker-install.html 官方文档-Install Docker Toolbox on Windows https://docs.docker.com/toolbox/toolbox_in......

karma123
今天
6
0
js垃圾回收机制和引起内存泄漏的操作

JS的垃圾回收机制了解吗? Js具有自动垃圾回收机制。垃圾收集器会按照固定的时间间隔周期性的执行。 JS中最常见的垃圾回收方式是标记清除。 工作原理:是当变量进入环境时,将这个变量标记为“...

Jack088
昨天
18
0
大数据教程(10.1)倒排索引建立

前面博主介绍了sql中join功能的大数据实现,本节将继续为小伙伴们分享倒排索引的建立。 一、需求 在很多项目中,我们需要对我们的文档建立索引(如:论坛帖子);我们需要记录某个词在各个文...

em_aaron
昨天
27
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部