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

﹏ヽ暗。殇╰゛Y 2022-06-16 01:12 167阅读 0赞

在某些情况下，or条件可以避免全表扫描的。本文使用mysql版本是5.7x

# **1 .where 语句里面如果带有or条件, myisam表能用到索引， innodb不行。** #

## **1)myisam表：** ##

CREATE TABLE IF NOT EXISTS \`**t\_myisam**\` (  
     \`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=utf8mb4  AUTO\_INCREMENT=1 ;

insert into  t\_myisam (uid,aNum) values(2,22);  
insert into  t\_myisam (uid,aNum) values(3,33);  
insert into  t\_myisam (uid,aNum) values(4,44);

mysql> explain select \* from t\_myisam  where id=1 or uid =2;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hndWlzdQ_size_16_color_FFFFFF_t_70][]

## 2)innodb表： ##

CREATE TABLE IF NOT EXISTS \`t\_innodb\` (  
  \`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=utf8mb4    AUTO\_INCREMENT=1 ;

insert into  t\_innodb (uid,aNum) values(2,22);  
insert into  t\_innodb (uid,aNum) values(3,33);  
insert into  t\_innodb (uid,aNum) values(4,44);  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hndWlzdQ_size_16_color_FFFFFF_t_70 1][]

## **2 .必须所有的or条件都必须是独立索引：** ##

CREATE TABLE IF NOT EXISTS \`t\_myisam2\` (  
     \`id\` int(1) NOT NULL AUTO\_INCREMENT,  
     \`uid\` int(11) NOT NULL,  
     \`aNum\` char(20) DEFAULT NULL,  
     PRIMARY KEY (\`id\`)  
 ) ENGINE=MyISAM  DEFAULT CHARSET=utf8mb4  AUTO\_INCREMENT=1 ;

insert into  t\_myisam2 (uid,aNum) values(2,22);  
insert into  t\_myisam2 (uid,aNum) values(3,33);  
insert into  t\_myisam2 (uid,aNum) values(4,44);

explain查看,全表扫描了。  
mysql> explain select \* from t\_myisam2 where id=1 or uid =2;  
![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hndWlzdQ_size_16_color_FFFFFF_t_70 2][]

## **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);

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hndWlzdQ_size_16_color_FFFFFF_t_70]: /images/20220616/f92ef29c6d54427cb4a30a02574f6719.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hndWlzdQ_size_16_color_FFFFFF_t_70 1]: /images/20220616/b8fa58f07989407e91f78774bc40c7ac.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2hndWlzdQ_size_16_color_FFFFFF_t_70 2]: /images/20220616/e6b1a39a4a794133a4856378a4cbaeb0.png