在某些情况下,or条件可以避免全表扫描的。虽然在我在MariaDB10.0.10版本和MySQL5.7.14上都测试不成功,但还是很有必要记录下来。
mysql> show variables like 'version'; +---------------+---------------------+ | Variable_name | Value | +---------------+---------------------+ | version | 10.0.10-MariaDB-log | +---------------+---------------------+ 1 row in set (0.00 sec)
mysql> show variables like 'version'; +---------------+------------+ | Variable_name | Value | +---------------+------------+ | version | 5.7.14-log | +---------------+------------+ 1 row in set, 1 warning (0.00 sec)
一.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)
二.必须所有的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)
全表扫描了。
三.用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, 那就需要返回记录最少的索引列写在最前面。
四.用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);
PS:查看mysql版本的方法
1.在命令行输入“mysql --version",按”Enter“键即可,如下图所示:
2.使用命令行查看mysql版本-mysql变量查看
在命令行输入“mysql",按”Enter“进入mysql命令行模式,输入”show variables like 'version';“即可,如下所示:
相关推荐
应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num=10 or num=20 可以这样查询: select id from t where num=10 union all ...
7、7、尽量避免在索引列上使用mysql的内置函数,8、应尽量避免在 where 子句中对字段进行表达式操作,这将导致系统放弃使用索引而进行全表扫,9,Inner join 、left join、right join,优先使用Inner join,如果是...
mysql-optimize 1.对查询进行优化,应尽量避免全表扫描,首先应考虑...4.应尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描, Sql 代码 : select id from t where num=10 or num
2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num is null可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:select...
2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num is null 可以在 num 上设置默认值 0,确保表中 num 列没有 null 值,然后这样查询...
应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如: select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询: ...
2.应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描,如:select id from t where num is null 可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:...
8.尽量避免在where子句中对字段进行表达式操作,会导致系统放弃使用索引而进行全表扫描 9.Inner join、left join、right join,优先使用Inner join,如果是left join,左边表结果尽量少 10.尽量避免
3、索引的创建要适度,多了会影响增删改的效率,少了会影响查询的效率,索引最好创建在取值分散的列上,避免对同一张表创建过多的索引 4、索引的使用对用户来说是透明的,由系统来决定什么时候使用索引。...
2、空间换时间:建立索引,走索引,避免全表扫描 select id ,name from t where name is null -- 空值不能利用索引,所以还是全表扫描 select id ,name from t where id in(1,2,3) -- 全表扫描 select id,name from...
1、字段避免null值出现,null值很难查询优化且占用额外的索引空间,推荐默认数字0代替null。 2、尽量使用INT而非BIGINT,如果非负则加上UNSIGNED(这样数值容量会扩大一倍),当然能使用TINYINT、SMALLINT、MEDIUM...
优化mysql数据库的方法:建立Index索引,少用select语句,开启查询缓存,选择适合的存储引擎,避免在where子句中使用or来连接以及避免大数据量返回等。 对于一个以数据为中心的应用,数据库的好坏直接影响到程序的...
本篇文章我们将了解ORDER BY语句的优化,在此之前,你需要对索引有基本的了解,不了解的老少爷们可以先看一下我之前写过的索引相关文章。现在让我们开始吧。 MySQL中的两种排序方式 1.通过有序索引顺序扫描直接返回...
避免在where子句中使用 or 来连接条件;避免在where子句中使用!=或操作符;将大的DELETE,UPDATE、INSERT 查询变成多个小查询;如何选择索引列的顺序;避免产生大事务操作;优化like语句等这样那样的问题。 作为数据...
varchar2 1~4000字节 可变长度字符串,与CHAR类型相比,使用VARCHAR2可以节省磁盘空间,但查询效率没有char类型高 数值类型 Number(m,n) m(1~38) n(-84~127) 可以存储正数、负数、零、定点数和精度为38位的浮点数...
定期使用ANALYZE TABLE使表统计信息保持最新 ,为优化器提供构造有效执行计划所需的信息。 阅读EXPLAIN计划并调整索引。 调整MySQL用于缓存的内存区域的大小和属性。 避免锁导致的查询性能问题。 1. WHERE子句优化 ...
13.4 使用联结和联结条件 88 13.5 小结 88 第14章 组合查询 89 14.1 组合查询 89 14.2 创建组合查询 89 14.2.1 使用UNION 90 14.2.2 UNION规则 91 14.2.3 包含或取消重复的行 92 14.2.4 对组合查询结果排序 ...
前言: 我们都知道事务的几种性质,数据库为了维护这些性质,尤其是一致性和隔离性,一般使用加锁这种方式。同时数据库又是个高并发的应用,同一时间会有大量的并发访问...这种方式可以有效的避免循环死锁,但在数据库中
要避免复制记录,表必须有PRIMARY KEY或一个UNIQUE索引。当新记录复制唯一键值的旧记录时,REPLACE关键字可以将旧记录替换为新记录。 如果备份时遇到服务器性能问题,可以有帮助的一个策略是在从服务器而不是主...