关于MySQL的索引，你知道多少呢

分手后的思念是犯贱 2023-09-27 12:38 149阅读 0赞

在工作中，如果我们想提高一条语句查询速度，通常都会想对字段建立索引。

但是索引并不是万能的。建立了索引，并不意味着任何查询语句都能走索引扫描。

稍不注意，可能你写的查询语句是会导致索引失效，从而走了全表扫描，虽然查询的结果没问题，但是查询的性能大大降低。

今天就来跟大家盘一盘，发生索引失效的几种场景。

首先介绍一下索引的语法，如何创建，查看，删除索引

<table style="width:500px;">  索引语法  
 <tbody> 
  <tr> 
   <td>创建索引</td> 
   <td>CREATE [ UNIQUE | FULLTEXT ] INDEX index_name ON table_name ( index_col_name,... );</td> 
  </tr> 
  <tr> 
   <td>查看索引</td> 
   <td>SHOW INDEX FROM table_name;</td> 
  </tr> 
  <tr> 
   <td>删除索引</td> 
   <td>DROP INDEX index_name ON table_name;</td> 
  </tr> 
 </tbody> 
</table>

在创建好索引之后，我们通过explain关键字来查看该sql语句的执行过程，即在该sql语句前加入explain，explain sql语句;

<table style="width:500px;">  explain执行计划详解  
 <tbody> 
  <tr> 
   <td>ld</td> 
   <td>select查询的序列号，表示查询中执行selet子句或者是操作表的顺序(id相同，执行顺序从上到下;id不同，值越大，越先执行)</td> 
  </tr> 
  <tr> 
   <td>select_type</td> 
   <td>表示 SELECT 的类型，常见的取值有 SIMPLE(简单表，即不使用表连接或者子查询)、PRIMARY(主查询，即外层的查询)UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等</td> 
  </tr> 
  <tr> 
   <td>type</td> 
   <td>表示连接类型，性能由好到差的连接类型为NULL、System、cnst、eg ref、ref、range、index、all.</td> 
  </tr> 
  <tr> 
   <td>possible_key</td> 
   <td>显示可能应用在这张表上的索引，一个或多个</td> 
  </tr> 
  <tr> 
   <td>Key</td> 
   <td>实际使用的索引，如果为NULL，则没有使用索引。</td> 
  </tr> 
  <tr> 
   <td>Key_len</td> 
   <td>表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好，</td> 
  </tr> 
  <tr> 
   <td>rows</td> 
   <td>MySQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能并不总是准确的。</td> 
  </tr> 
  <tr> 
   <td>filtered</td> 
   <td>表示返回结果的行数占需读取行数的百分比，filtered的值越大越好</td> 
  </tr> 
 </tbody> 
</table>

我个人经常关注的几个字段有 type，possible\_key，Key，rows

重点给大家讲下索引失效的几种场景

<table style="width:500px;">  索引失效的几种场景  
 <tbody> 
  <tr> 
   <td>最左前缀法则</td> 
   <td>如果索引了多列(联合索引》，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。<br> 如果跳跃某一列，索引将部分失效(后面的字段索引失效)。</td> 
  </tr> 
  <tr> 
   <td>范围查询</td> 
   <td>联合索引中，出现范围查询(&gt;,&lt;)，范围查询右侧的列索引失效</td> 
  </tr> 
  <tr> 
   <td>索引列运算</td> 
   <td>不要在索引列上进行运算操作，索引将失效</td> 
  </tr> 
  <tr> 
   <td>字符串不加引号</td> 
   <td>字符串类型字段使用时，不加引号，索引将失效</td> 
  </tr> 
  <tr> 
   <td>模糊查询</td> 
   <td>如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。</td> 
  </tr> 
  <tr> 
   <td>or连接的条件</td> 
   <td>用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到</td> 
  </tr> 
  <tr> 
   <td>查询所有</td> 
   <td>也就是使用select *&nbsp; ，它是会全表扫描，没有用到任何索引，查询效率是非常低的</td> 
  </tr> 
 </tbody> 
</table>

好了，先说这么多吧，后面有时间再给大家演示一下