Mysql什么情况下不走索引

谁践踏了优雅 2023-09-30 20:01 43阅读 0赞

## 背景分析 ##

今天领导在查询报表时，发现特别慢，于是引发一系列关于sql优化的工作，最终发现是分析同学在进行多表关联时进行不等值关联造成全表扫描，且使用字段无索引造成1W条数据表和20W数据表关联时执行缓慢。但是在MySQL中，并不是你建立了索引，并且你在SQL中使用到了该列，MySQL就肯定会使用到那些索引的，有一些情况很可能在你不知不觉中，你就“成功的避开了”MySQL的所有索引。

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

> 1.用`!=`或者`<>`导致索引失效  
> 2.类型不一致导致的索引失效  
> 3.函数导致的索引失效 如： `SELECT * FROM user WHERE DATE(create_time) = ‘2020-09-03’`; 如果使用函数在索引列，这是不走索引的。  
> 4.运算符导致的索引失效 SELECT \* FROM user WHERE age - 1 = 20; 如果你对列进行了`（+，-，*，/，!）`, 那么都将不会走索引。  
> 5.`OR`引起的索引失效 SELECT \* FROM user WHERE name = ‘张三’ OR height = ‘175’; OR导致索引是在特定情况下的，并不是所有的OR都是使索引失效，如果OR连接的是同一个字段或者连接的两个字段都有索引，那么索引不会失效，反之索引失效。  
> 6.模糊搜索导致的索引失效 SELECT \* FROM user WHERE name LIKE ‘%冰’; 当%放在匹配字段前是不走索引的，放在后面才会走索引。  
> 7.NOT IN、NOT EXISTS导致索引失效

## 针对不走索引的情况详细分析 ##

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

SELECT `username` FROM `t_user` WHERE age=20;-- 会使用索引
    SELECT `username` FROM `t_user` WHERE age+10=30;-- 不会使用索引！！因为所有索引列参与了计算
    SELECT `username` FROM `t_user` WHERE age=30-10;-- 会使用索引

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

-- 不会使用索引,因为使用了函数运算,原理与上面相同
    SELECT username FROM t_user WHERE concat(username,'1') = 'admin1'; 
    -- 会使用索引
    SELECT username FROM t_user WHERE username = concat('admin','1');

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

SELECT * FROM USER WHERE username LIKE 'mysql测试%'   --走索引
    SELECT * FROM USER WHERE username LIKE '%mysql测试'   --不走索引
    SELECT * FROM USER WHERE username LIKE '%mysql测试%'  --不走索引

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

-- stock_code字符串类型带索引
    SELECT * FROM `stock_data` WHERE stock_code = '600538'  --走索引
    SELECT * FROM `stock_data` WHERE stock_code = 600538  --不走索引

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

-- stock_code带索引，open不带索引
    SELECT * FROM `stock_data` WHERE `stock_code` = '600538' OR `open` = 6.62  -- 不走索引
    -- stock_code带索引，up_down_pre带索引
    SELECT * FROM `stock_data` WHERE `stock_code` = '600538' OR `up_down_pre` = 5.1  -- 走索引

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

SELECT * FROM t_user WHERE username <> 'mysql测试'

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

SELECT * FROM t_user WHERE username IS NULL -- 不走索引
    SELECT * FROM t_user WHERE username IS NOT NULL -- 不走索引

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

-- stock_code数据类型为varchar
    SELECT * FROM `stock_data` WHERE `stock_code` IN ('600538')  -- 走索引
    SELECT * FROM `stock_data` WHERE `stock_code` IN ('600538','688663','688280')  -- 走索引
    SELECT * FROM `stock_data` WHERE `stock_code` IN (大量数据)  -- 不走索引
    SELECT * FROM `stock_data` WHERE `stock_code` IN (600538)  -- 不走索引

## 对索引失效情况的一些建议，仅供大家有则改之无则加勉 ##

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

> 建议：  
> 1 换成有索引的列作为查询条件
> 
> 2 或者将查询频繁的列建立索引

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

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

> 建议：  
> 1 如果业务允许，可以使用limit控制。
> 
> 2 结合业务判断，有没有更好的方式。如果没有更好的改写方案
> 
> 3 尽量不要在mysql存放这个数据了。放到redis里面。

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

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

> 建议：  
> 备份表数据，删除重建相关表

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

> 建议： 减少在mysql中使用加减乘除等计算运算。

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

索引建立的字段为varchar();
    select * from stu where name = ‘111'；走索引
    select * from stu where name = 111；不走索引

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

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

> 建议： 尽量不要用以上方式进行查询，或者选择有索引列为筛选条件。 单独的>,<,in 有可能走，也有可能不走，和结果集有关，  
> 尽量结合业务添加limit or或in 尽量改成union

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

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE ‘31%' 走索引
    EXPLAIN SELECT * FROM teltab WHERE telnum LIKE ‘%110' 不走索引

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

--------------------

## MySQL5.7使用in查询不走索引 ##

### 问题现象 ###

*  前提有一个订单表，4000w+的数据量。
 *  因为某些原因，需要执行一下这条查询SQL，简化后如下，in内有4w个数据：
    
        SELECT id from order_header
        where sourcePlatOrderCode in ('2653569147221821250',
        '2652952250350393936', .....);
 *  执行后发现特别慢，且无法查询出结果。

### 排查过程 ###

#### 执行explain ####

*  对SQL执行进行分析后发现虽然使用了in查询，但是没有使用索引查询，仍然使用全表扫描。
 *  减少in数据项后发现可以正常使用索引查询，分析结果为范围扫描。
 *  初步确定是无法命中索引的问题。

#### 使用强制索引 ####

*  使用强制索引`force index()`后再次执行分析，发现结果任然是扫描全表。

#### 对比explain结果 ####

*  相同SQL在其它项目系统，数据量差不多的情况下可以正常命中索引。
 *  这时候发现全表扫描的数据库版本为5.7，而范围扫描的数据库版本为5.6。
 *  考虑到是不是数据库版本的影响，就对数据库的版本进行操作。

#### 升级数据库小版本 ####

*  由于是买的云数据库，服务商只能提供向上升级的功能，并不能向下降级。
 *  尝试升级两个小版本后，仍然是全表扫描。
 *  跨大版本升级风险太大，就将数据库小版本升级到最新再次尝试，发现仍然是全表扫描。

#### 咨询云服务商 ####

*  这个时候终于提了工单，服务商回复是，业务低峰期执行表空间整理或者使用强制索引（这个方法直接略过）。
 *  由于是大促期间业务高峰，这个整理表空间想法只能暂时被搁置，待后面验证。

#### range\_optimizer\_max\_mem\_size ####