Mysql索引失效的几种情况

迈不过友情╰ 2022-10-15 13:52 183阅读 0赞

## 写在前面 ##

`explain`命令中`type`中的属性，效率从上往下递减

*  `system`：系统表，少量数据，往往不需要进行磁盘 IO
 *  `const`：常量连接
 *  `eq_ref`：主键索引 (primary key) 或者非空唯一索引 (unique not null) 等值扫描
 *  `ref`：非主键非唯一索引等值扫描
 *  `range`：范围扫描
 *  `index`：索引树扫描
 *  `ALL`：全表扫描 (full table scan)

**表结构：**

<table> 
 <thead> 
  <tr> 
   <th>Field</th> 
   <th>Type</th> 
   <th>Null</th> 
   <th>Key</th> 
   <th>Default</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>id</td> 
   <td>int(11)</td> 
   <td>NO</td> 
   <td>PRI</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>title</td> 
   <td>varchar(100)</td> 
   <td>NO</td> 
   <td></td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>author</td> 
   <td>varchar(30)</td> 
   <td>NO</td> 
   <td>MUL</td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>create_time</td> 
   <td>datetime</td> 
   <td>NO</td> 
   <td></td> 
   <td>NULL</td> 
  </tr> 
  <tr> 
   <td>views</td> 
   <td>int(30)</td> 
   <td>NO</td> 
   <td></td> 
   <td>NULL</td> 
  </tr> 
 </tbody> 
</table>

**建索引：**

create index idx_blog_acv on blog(author,create_time,views);

show index from blog;

<table> 
 <thead> 
  <tr> 
   <th>Table</th> 
   <th>Non_unique</th> 
   <th>Key_name</th> 
   <th>Seq_in_index</th> 
   <th>Column_name</th> 
   <th>Index_type</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>blog</td> 
   <td>0</td> 
   <td>PRIMARY</td> 
   <td>1</td> 
   <td>id</td> 
   <td>BTREE</td> 
  </tr> 
  <tr> 
   <td>blog</td> 
   <td>1</td> 
   <td>idx_blog_acv</td> 
   <td>1</td> 
   <td>author</td> 
   <td>BTREE</td> 
  </tr> 
  <tr> 
   <td>blog</td> 
   <td>1</td> 
   <td>idx_blog_acv</td> 
   <td>2</td> 
   <td>create_time</td> 
   <td>BTREE</td> 
  </tr> 
  <tr> 
   <td>blog</td> 
   <td>1</td> 
   <td>idx_blog_acv</td> 
   <td>3</td> 
   <td>views</td> 
   <td>BTREE</td> 
  </tr> 
 </tbody> 
</table>

### 一、最左前缀匹配法则 ###

用到了所建立的全部索引

explain select * from blog where author ="张三" and create_time="2020-05-08" and views = 10000000;

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>blog</td> 
   <td>ref</td> 
   <td>idx_blog_acv</td> 
   <td>idx_blog_acv</td> 
   <td>101</td> 
   <td>const,const,const</td> 
   <td>1</td> 
   <td>Using index condition</td> 
  </tr> 
 </tbody> 
</table>

由于条件中缺少第二个索引字段，所以`author`后的所有字段索引失效，变成全表扫描，数据量一大，效率降低

explain select * from blog where author ="张三" and views = 10000000;

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>blog</td> 
   <td>ref</td> 
   <td>idx_blog_acv</td> 
   <td>idx_blog_acv</td> 
   <td>92</td> 
   <td>const</td> 
   <td>1</td> 
   <td>Using index condition</td> 
  </tr> 
 </tbody> 
</table>

### 二、在索引列上计算（函数，类型转换）会导致索引失效 ###

**未计算：**

explain select * from blog where author = "张三";

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>blog</td> 
   <td>ref</td> 
   <td>idx_blog_acv</td> 
   <td>idx_blog_acv</td> 
   <td>92</td> 
   <td>const</td> 
   <td>1</td> 
   <td>Using index condition</td> 
  </tr> 
 </tbody> 
</table>

**已计算：**

explain select * from blog where left(author,6) = "张三";

**导致全表扫描：**

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>blog</td> 
   <td>ALL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>3</td> 
   <td>Using where</td> 
  </tr> 
 </tbody> 
</table>

### 三、索引中范围条件右边的列的索引会自动失效（但是范围条件之前的和范围条件还是用到索引的） ###

**未使用范围条件：**

explain select * from blog where author="张三" and create_time = "2020-5-8" and views = 10000000;

**使用范围条件：**

explain select * from blog where author="张三" and create_time > "2020-5-7" and views = 10000000;

**`create_time`后的索引列失效，变成全表查询**

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>blog</td> 
   <td>range</td> 
   <td>idx_blog_acv</td> 
   <td>idx_blog_acv</td> 
   <td>97</td> 
   <td>NULL</td> 
   <td>1</td> 
   <td>Using index condition</td> 
  </tr> 
 </tbody> 
</table>

### 四、索引列使用 ！= ，<>，is null，is not null会导致索引失效 ###

**!=：**

explain select * from blog where author != "张三";

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>blog</td> 
   <td>ALL</td> 
   <td>idx_blog_acv</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>3</td> 
   <td>Using where</td> 
  </tr> 
 </tbody> 
</table>

**is null：**

explain select * from blog where author is null;

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>Impossible WHERE</td> 
  </tr> 
 </tbody> 
</table>

**is not null：**

explain select * from blog where author is not null;

<table> 
 <thead> 
  <tr> 
   <th>id</th> 
   <th>select_type</th> 
   <th>table</th> 
   <th>type</th> 
   <th>possible_keys</th> 
   <th>key</th> 
   <th>key_len</th> 
   <th>ref</th> 
   <th>rows</th> 
   <th>Extra</th> 
  </tr> 
 </thead> 
 <tbody> 
  <tr> 
   <td>1</td> 
   <td>SIMPLE</td> 
   <td>blog</td> 
   <td>ALL</td> 
   <td>idx_blog_acv</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>NULL</td> 
   <td>3</td> 
   <td>Using where</td> 
  </tr> 
 </tbody> 
</table>

### 五、LIKE以%开头会导致索引失效，使用覆盖索引解决，用or连接也会导致索引失效 ###

### 六、字符串不加单引号会导致索引失效（因为mysql底层会自动将int类型转换为varchar） ###