你知道mysql哪些查询情况不走索引吗？

秒速五厘米 2024-03-16 10:56 48阅读 0赞

众所周知,索引类似于字典的目录,可以提高查询的效率,下面这篇文章主要给大家介绍了关于mysql哪些查询情况不走索引的相关资料,文中通过实例代码介绍的非常详细,需要的朋友可以参考下~

### 前言 ###

在MySQL中，并不是你建立了索引，并且你在SQL中使用到了该列，MySQL就肯定会使用到那些索引的，有一些情况很可能在你不知不觉中，你就“成功的避开了”MySQL的所有索引。

### mysql哪些查询情况不走索引 ###

**1、索引列参与计算，不走索引**

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p><code>SELECT</code> <code>`username` </code><code>FROM</code> <code>`t_user` </code><code>WHERE</code> <code>age=20;</code><code>-- 会使用索引</code></p> <p><code>SELECT</code> <code>`username` </code><code>FROM</code> <code>`t_user` </code><code>WHERE</code> <code>age+10=30;</code><code>-- 不会使用索引！！因为所有索引列参与了计算</code></p> <p><code>SELECT</code> <code>`username` </code><code>FROM</code> <code>`t_user` </code><code>WHERE</code> <code>age=30-10;</code><code>-- 会使用索引</code></p> </td> 
  </tr> 
 </tbody> 
</table>

**2、索引列使用函数，可能不走索引**

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> </td> 
   <td> <p><code>-- 不会使用索引,因为使用了函数运算,原理与上面相同</code></p> <p><code>SELECT</code> <code>username </code><code>FROM</code> <code>t_user </code><code>WHERE</code> <code>concat(username,</code><code>'1'</code><code>) = </code><code>'admin1'</code><code>; </code></p> <p><code>-- 会使用索引</code></p> <p><code>SELECT</code> <code>username </code><code>FROM</code> <code>t_user </code><code>WHERE</code> <code>username = concat(</code><code>'admin'</code><code>,</code><code>'1'</code><code>); </code></p> </td> 
  </tr> 
 </tbody> 
</table>

**3、索引列使用 like 语句，可能不走索引**

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p><code>SELECT</code> <code>* </code><code>FROM</code> <code>USER</code> <code>WHERE</code> <code>username </code><code>LIKE</code> <code>'mysql测试%'</code>&nbsp;&nbsp; <code>--走索引</code></p> <p><code>SELECT</code> <code>* </code><code>FROM</code> <code>USER</code> <code>WHERE</code> <code>username </code><code>LIKE</code> <code>'%mysql测试'</code>&nbsp;&nbsp; <code>--不走索引</code></p> <p><code>SELECT</code> <code>* </code><code>FROM</code> <code>USER</code> <code>WHERE</code> <code>username </code><code>LIKE</code> <code>'%mysql测试%'</code>&nbsp; <code>--不走索引</code></p> </td> 
  </tr> 
 </tbody> 
</table>

**4、数据类型隐式转换，字符串列与数字直接比较，不走索引**

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> </td> 
   <td> <p><code>-- stock_code字符串类型带索引</code></p> <p><code>SELECT</code> <code>* </code><code>FROM</code> <code>`stock_data` </code><code>WHERE</code> <code>stock_code = </code><code>'600538'</code>&nbsp; <code>--走索引</code></p> <p><code>SELECT</code> <code>* </code><code>FROM</code> <code>`stock_data` </code><code>WHERE</code> <code>stock_code = 600538&nbsp; </code><code>--不走索引</code></p> </td> 
  </tr> 
 </tbody> 
</table>

**5、尽量避免 OR 操作，只要有一个字段没有索引，该语句就不走索引！**

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> </td> 
   <td> <p><code>-- stock_code带索引，open不带索引</code></p> <p><code>SELECT</code> <code>* </code><code>FROM</code> <code>`stock_data` </code><code>WHERE</code> <code>`stock_code` = </code><code>'600538'</code> <code>OR</code> <code>`</code><code>open</code><code>` = 6.62&nbsp; </code><code>-- 不走索引</code></p> <p><code>-- stock_code带索引，up_down_pre带索引</code></p> <p><code>SELECT</code> <code>* </code><code>FROM</code> <code>`stock_data` </code><code>WHERE</code> <code>`stock_code` = </code><code>'600538'</code> <code>OR</code> <code>`up_down_pre` = 5.1&nbsp; </code><code>-- 走索引</code></p> </td> 
  </tr> 
 </tbody> 
</table>

**6、where id !=2 或者 where id <> 2，不走索引！**

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> </td> 
   <td> <p><code>SELECT</code> <code>* </code><code>FROM</code> <code>t_user </code><code>WHERE</code> <code>username &lt;&gt; </code><code>'mysql测试'</code></p> </td> 
  </tr> 
 </tbody> 
</table>

**7、is null，is not null也无法使用索引，不走索引！**

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p><code>SELECT</code> <code>* </code><code>FROM</code> <code>t_user </code><code>WHERE</code> <code>username </code><code>IS</code> <code>NULL</code> <code>-- 不走索引</code></p> <p><code>SELECT</code> <code>* </code><code>FROM</code> <code>t_user </code><code>WHERE</code> <code>username </code><code>IS</code> <code>NOT</code> <code>NULL</code> <code>-- 不走索引</code></p> </td> 
  </tr> 
 </tbody> 
</table>

**8、索引列使用 in 语句，可能不走索引**

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> <p>3</p> <p>4</p> <p>5</p> </td> 
   <td> <p><code>-- stock_code数据类型为varchar</code></p> <p><code>SELECT</code> <code>* </code><code>FROM</code> <code>`stock_data` </code><code>WHERE</code> <code>`stock_code` </code><code>IN</code> <code>(</code><code>'600538'</code><code>)&nbsp; </code><code>-- 走索引</code></p> <p><code>SELECT</code> <code>* </code><code>FROM</code> <code>`stock_data` </code><code>WHERE</code> <code>`stock_code` </code><code>IN</code> <code>(</code><code>'600538'</code><code>,</code><code>'688663'</code><code>,</code><code>'688280'</code><code>)&nbsp; </code><code>-- 走索引</code></p> <p><code>SELECT</code> <code>* </code><code>FROM</code> <code>`stock_data` </code><code>WHERE</code> <code>`stock_code` </code><code>IN</code> <code>(大量数据)&nbsp; </code><code>-- 不走索引</code></p> <p><code>SELECT</code> <code>* </code><code>FROM</code> <code>`stock_data` </code><code>WHERE</code> <code>`stock_code` </code><code>IN</code> <code>(600538)&nbsp; </code><code>-- 不走索引</code></p> </td> 
  </tr> 
 </tbody> 
</table>

### 不走索引的情况： ###

**1.没有查询条件，或者查询条件没有建立索引在业务数据库中，特别是数据量比较大的表。**

建议：

1 换成有索引的列作为查询条件

2 或者将查询频繁的列建立索引

**2.查询结果集是原表中的大部分数据，应该是25％以上**

查询的结果集，超过了总数行数25%，优化器觉得就没有必要走索引了。

建议：

1 如果业务允许，可以使用limit控制。

2 结合业务判断，有没有更好的方式。如果没有更好的改写方案

3 尽量不要在mysql存放这个数据了。放到redis里面。

**3.索引本身失效，统计数据不真实**

索引有自我维护的能力，对于表内容变化比较频繁的情况下，有可能会出现索引失效。

更改方案：

备份表数据，删除重建相关表。

**4.查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，\*，/，! 等)**

更改方法：

减少在mysql中使用加减乘除等计算运算。

**5.隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.**

索引建立的字段为varchar();

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p><code>select</code> <code>* </code><code>from</code> <code>stu </code><code>where</code> <code>name</code> <code>= ‘111'；走索引</code></p> <p><code>select</code> <code>* </code><code>from</code> <code>stu </code><code>where</code> <code>name</code> <code>= 111；不走索引</code></p> </td> 
  </tr> 
 </tbody> 
</table>

更改方法：

与研发协商，语句查询符合规范。

**6.<> ，not in 不走索引（辅助索引）**

更改方法：

尽量不要用以上方式进行查询，或者选择有索引列为筛选条件。

单独的>,<,in 有可能走，也有可能不走，和结果集有关，尽量结合业务添加limit

or或in 尽量改成union

**7.like “%” 百分号在最前面不走**

<table> 
 <tbody> 
  <tr> 
   <td> <p>1</p> <p>2</p> </td> 
   <td> <p><code>EXPLAIN </code><code>SELECT</code> <code>* </code><code>FROM</code> <code>teltab </code><code>WHERE</code> <code>telnum </code><code>LIKE</code> <code>‘31%</code><code>' 走索引</code></p> <p><code>EXPLAIN SELECT * FROM teltab WHERE telnum LIKE ‘%110'</code> <code>不走索引</code></p> </td> 
  </tr> 
 </tbody> 
</table>

更改方法：

%linux%类的搜索需求，可以使用elasticsearch+mongodb 专门做搜索服务的数据库产品

### 总结 ###

到此这篇关于mysql哪些查询情况不走索引的文章就介绍到这了。

原文链接：[你知道mysql哪些查询情况不走索引吗\_Mysql\_脚本之家][mysql_Mysql]

[mysql_Mysql]: https://www.jb51.net/article/245266.htm