数据库 mysql索引失效

桃扇骨 2022-12-28 00:57 106阅读 0赞

### 文章目录 ###

*   *  数据库 mysql索引失效
     *   *  一、概述
         *  二、索引失效场景
         *   *  1. 全值匹配
             *  2. 最佳左前缀
             *  3. 索引计算
             *  4.索引范围：索引列上不能有范围查询
             *  5.索引覆盖：尽量使用覆盖索引
             *  6.不等： 使用不等于（!= 或者 <>）的时候
             *  7. null：字段的is not null 与is null
             *  8. like：like的前后模糊匹配
             *  9. or：减少使用or

## 数据库 mysql索引失效 ##

### 一、概述 ###

常见索引失效的9种情况：
    1. 全值匹配：查询条件的列与索引列的字段，顺序完全相同。
    2. 最佳左前缀：查询条件的列与索引列的字段相同，顺序不同，从不同顺序列开始后边都不走索引。
    3. 索引计算：不要在索引上做任何计算
    4. 索引范围：索引列上不能有范围查询，比如大于，小于，大于等于，小于等于。
    5. 索引覆盖：尽量使用覆盖索引
    6. 不等： 使用不等于（!= 或者 <>）的时候
    7. null：字段的is not null 与is null
    8. like：like的前后模糊匹配
    9. or：减少使用or
    
    总结：
    全值匹配，左前缀。
    索引计算范围要覆盖。
    不等于（!= 或者 <>）扫全表，null走索引，not不走。
    like模后不模前，见or就走union all

### 二、索引失效场景 ###

**测试环境**

*  mysql版本：5.7.27-log，查询语句：select VERSION();
 *  建表语句及数据：[mysql批量插入数据][mysql]

#### 1. 全值匹配 ####

-- 全值匹配
    -- 查看sql执行计划
    EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30;
    EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4;
    EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd';
    
    -- 创建联合索引
    CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);

创建索引前  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70]  
创建索引后  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70 1]

#### 2. 最佳左前缀 ####

-- 创建索引
    CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME);
    -- 缺少联合索引的第一个字段
    explain select sql_no_cache * from emp where deptId = 4 and name = 'abcd';
    -- 联合索引的第一二个字段，缺少最后一个字段
    explain select sql_no_cache * from emp where age = 30 and deptId = 4;
    -- 联合索引的第一三各字段，缺少第二个字段
    explain select sql_no_cache * from emp where age = 30 and name = 'abcd' ;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70 2]查询字段与索引字段顺序的不同会导致， 索引无法充分使用， 甚至索引失效！  
原因： 使用复合索引， 需要遵循最佳左前缀法则， 即如果索引了多列， 要遵守最左前缀法则。 指的是查询从索引的最左前列开始并且不跳过索引中的列。  
结论： 过滤条件要使用索引必须按照索引建立时的顺序， 依次满足， 一旦跳过某个字段， 索引后面的字段都无法被使用

#### 3. 索引计算 ####

不要在索引上做任何计算！  
不在索引列上做任何操作（计算、 函数、 (自动 or 手动)类型转换）， 会导致索引失效而转向全表扫描。

1.  在查询列上使用函数

-- 索引不带计算
    EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age=30;
    -- 索引字段计算
    EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(age,3)=30;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70 3]

1.  在查询列上做了转换

-- 创建单值索引，字符串类型 name
    create index idx_name on emp(name);
    -- 字符串加单引号情况
    explain select sql_no_cache * from emp where name='30000';
    -- 字符串不加单引号， 则会在 name 列上做一次转换！
    explain select sql_no_cache * from emp where name=30000;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70 4]

#### 4.索引范围：索引列上不能有范围查询 ####

explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=5 AND emp.name = 'abcd';
    explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid<=5 AND emp.name = 'abcd';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70 5]建议： 将可能做范围查询的字段的索引顺序放在最后

#### 5.索引覆盖：尽量使用覆盖索引 ####

explain SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptId=4 and name='XamgXt';
    explain SELECT SQL_NO_CACHE age,deptId,name FROM emp WHERE emp.age=30 and deptId=4 and name='XamgXt';

![在这里插入图片描述][20201213120842536.png]

#### 6.不等： 使用不等于（!= 或者 <>）的时候 ####

mysql 在使用不等于(!= 或者<>)时， 有时会无法使用索引会导致全表扫描。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70 6]

#### 7. null：字段的is not null 与is null ####

当字段允许为 Null 的条件下：  
is not null 用不到索引， is null 可以用到索引。  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70 7]![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70 8]

#### 8. like：like的前后模糊匹配 ####

前缀不能出现模糊匹配！  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70 9]

#### 9. or：减少使用or ####

![在这里插入图片描述][20201213121308198.png]使用 union all 或者 union 来替代：  
![在这里插入图片描述][2020121312133856.png]

[mysql]: https://blog.csdn.net/m0_37583655/article/details/111088956
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70]: /images/20221120/4bab683789aa4f7397791e19b585ab95.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70 1]: /images/20221120/b7e12444f888404c8f1fb47c9150c116.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70 2]: /images/20221120/f997ee368c054364890b85c0cbb4b6ed.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70 3]: /images/20221120/111bcc9071e740e4bca4c1072d83ae70.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70 4]: /images/20221120/f40f067b9b294d71a4f7c5f99a276b23.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70 5]: /images/20221120/c32965b9bdff41fb9403fd715b8d1251.png
[20201213120842536.png]: /images/20221120/6d7e0731ad6d4b67911cee082a5189f6.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70 6]: /images/20221120/b8959a176cb0443685d178ae7cfb5bbb.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20201213121050469.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70 8]: /images/20221120/07a12cbb2b994b8791d18a1e192f5307.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L20wXzM3NTgzNjU1_size_16_color_FFFFFF_t_70 9]: /images/20221120/b1eb4ac9fad44079887dc8654dd3213d.png
[20201213121308198.png]: /images/20221120/50108c63b4d24c36b588c68e126dcf89.png
[2020121312133856.png]: /images/20221120/58825530353c49c19341cdf2634bcd8e.png