数据库or、in、<>、>=、<=、butween区别

操作前先关闭数据库缓存

#创建测试的test表

DROP TABLE IF EXISTS test;
CREATE TABLE test(
    `id` INT(10) NOT NULL,
    `name` VARCHAR(20) DEFAULT '' NOT NULL,
    `status` TINYINT(1) DEFAULT 1 NOT NULL,
     PRIMARY KEY( ID )
)ENGINE=INNODB DEFAULT CHARSET utf8;

插入数据

create procedure a()
    BEGIN
    DECLARE i INT DEFAULT 0;
    SET autocommit = 0;
    WHILE i<10000000 DO
        INSERT INTO test ( `id`,`name`,`status` ) VALUES( i, CONCAT( 'abcd', i ),i%3 );
        SET i = i+1;
        IF i%2000 = 0 THEN // 每满2000行执行一次
            COMMIT;
        END IF;
    END WHILE;
END;
call a();
耗时: 0.042s

先测试带主键模式下查询主键执行时间

读取10条数据
# in
SELECT * FROM test WHERE id IN (1,2,3,4,5,6,7,8,9,10);
用时：0.001

# or
SELECT * FROM test WHERE id =1 OR id=2 OR id=3 OR id=4 OR id=5 OR id=6 OR id=7 OR id=8 OR id=9 OR id=10;
用时：0.001

# between
SELECT * FROM test WHERE id between 1 and 10;
用时：0.001

# <>
SELECT * FROM test WHERE id>=1 and id<=10;
用时：0.001

# <=
SELECT * FROM test WHERE id <= 10;
用时：0.001

读取1000条数据
# in
SELECT * FROM test WHERE id IN (1,2,3,...1000);
用时：0.003

# or
SELECT * FROM test WHERE id =1 OR id=2 OR id=3 OR... id=1000;
用时：0.019

# between
SELECT * FROM test WHERE id between 1 and 100;
用时：0.002

# <>
SELECT * FROM test WHERE id>=1 and id<=1000;
用时：0.002

# <=
SELECT * FROM test WHERE id <= 1000;
用时：0.002

读取10000条数据
# in
SELECT * FROM test WHERE id IN (1,2,3,...10000);
用时：0.027

# or 
SELECT * FROM test WHERE id =1 OR id=2 OR id=3 OR... id=10000;

执行用时：4.834

# between
SELECT * FROM test WHERE id between 1 and 10000;
用时：0.008

# <>
SELECT * FROM test WHERE id>=1 and id<=10000;
用时：0.008

# <=
SELECT * FROM test WHERE id <= 10000;
用时：0.008

读取50000条数据（sql语句太长，10万数据 in和or会崩溃）
# in
SELECT * FROM test WHERE id IN (1,2,3,...50000);
用时：5.193s 

# or 
SELECT * FROM test WHERE id =1 OR id=2 OR id=3 OR... id=50000; 
用时：（sql语句太长，电脑崩溃）

 # between
 SELECT * FROM test WHERE id between 1 and 50000;
 用时：0.035s

 # <> 
SELECT * FROM test WHERE id>=1 and id<=50000;
 用时：0.032s 

# <= 
SELECT * FROM test WHERE id <= 50000;
 用时：0.031s

在主键id上 in的效率要高于or；其它几个比较符相近。

 

测试非主键 status

读取10000000条数据（一千万条数据）
# in
SELECT * FROM test WHERE status IN (0,1);
用时：6.736s 

# or 
SELECT * FROM test WHERE status =0 OR status=1; 
用时：6.655s

 # between
 SELECT * FROM test WHERE status between 0 and 1;
 用时：6.476s

 # <> 
SELECT * FROM test WHERE status>=0 and status<=1;
 用时：6.680s 

# <= 
SELECT * FROM test WHERE status <= 1;
 用时：6.440s

# <>
SELECT * FROM test WHERE status <> 2;
用时：6.263s

当id的主键索引去除后

读取10000000条数据（一千万条数据）
# in
SELECT * FROM test WHERE status IN (0,1);
用时：7.888s 

# or 
SELECT * FROM test WHERE status =0 OR status=1; 
用时：7.770s

 # between
 SELECT * FROM test WHERE status between 0 and 1;
 用时：7.989s

 # <= => 
SELECT * FROM test WHERE status>=0 and status<=1;
 用时：7.835s 

# <= 
SELECT * FROM test WHERE status <= 1;
 用时：7.476s

# <>
SELECT * FROM test WHERE status <> 2;
用时：8.346s

非主键字段有无主键索引差距不是很大。

 

强制走主键索引

select * from test force index(PRIMARY) where status in (0,1);
用时：10.930