MySQL--索引失效的各种情况举例分析（一）

太过爱你忘了你带给我的痛 2021-08-13 15:10 368阅读 0赞

### 索引失效 ###

**建表及准备数据：**

CREATE TABLE `staffs`(
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名',
    `age` INT NOT NULL DEFAULT 0 COMMENT '年龄',
    `pos` VARCHAR(20) NOT NULL DEFAULT '' COMMENT '职位',
    `add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间'
    )CHARSET utf8 COMMENT '员工记录表';
    
    
    INSERT INTO staffs(`name`,age,pos,add_time) VALUES('z3',22,'manager',NOW());
    INSERT INTO staffs(`name`,age,pos,add_time) VALUES('July',23,'dev',NOW());
    INSERT INTO staffs(`name`,age,pos,add_time) VALUES('2000',23,'dev',NOW());
    
    SELECT * from staffs;
    
    ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(`name`,age,pos);

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

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

*  全值匹配
 *  最佳左前缀法则
 *  不再索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
 *  存储引擎不能使用索引中范围条件右边的列
 *  尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少SELECT \*
 *  mysql在使用不等于（!= 或 <>）的时候无法使用索引会导致全表扫描
 *  is null, is not null 也无法使用索引
 *  like 以通配符开头（’%abc…’）mysql索引失效会变成全表扫描的操作
 *  字符串不加单引号索引失效
 *  少用or，用它来连接时会索引失效

全值匹配

EXPLAIN SELECT * FROM staffs WHERE name = 'July';

![在这里插入图片描述][20191124190251177.png]\`\`\`sql  
EXPLAIN SELECT \* FROM staffs WHERE name = ‘July’ AND age = 25;

![在这里插入图片描述](https://img-blog.csdnimg.cn/20191124190434151.png)
    
    ```sql
    EXPLAIN SELECT * FROM staffs WHERE name = 'July' AND age = 25 AND pos = 'dev';

![在这里插入图片描述][20191124190524642.png]  
**以上三种情况都可以用到索引**

EXPLAIN SELECT * FROM staffs WHERE age = 23 AND pos = 'dev';

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

EXPLAIN SELECT * FROM staffs WHERE pos = 'dev';

![在这里插入图片描述][20191124190814492.png]  
**以上两种情况索引失效了。。。违背了左前缀法则**

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 1]

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

EXPLAIN SELECT * FROM staffs WHERE LEFT(name,4) = 'July';
    #left是一个函数，就是字符串截取的作用

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

结论：**原来的name加上left(name,4)之后，索引就失效了**

存储引擎不能使用索引中范围条件右边的列

EXPLAIN SELECT * FROM staffs WHERE name = 'July' AND age > 25 and pos = 'manager';

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

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 2]

尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少SELECT \*

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

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

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70]: /images/20210813/fc34347e2cf54e94ac08e7a1e8176f7f.png
[20191124184639354.png]: /images/20210813/2c426883e6b64729a29cdc9ab9d5c635.png
[20191124190251177.png]: /images/20210813/c64d2b266389421ba9f122bb53f89b26.png
[20191124190524642.png]: /images/20210813/397a8ccc40394e12b14819bf91a27aa1.png
[20191124190721725.png]: /images/20210813/b9863b29e8af4c3183cbb275f2b7dc3f.png
[20191124190814492.png]: /images/20210813/b4d99cc631de4ee69b5bcaf384deece1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 1]: /images/20210813/0a541212201e4d84ae17bcc0961428c0.png
[20191124193005236.png]: /images/20210813/24e4533588db4b4f8b10662ab1a1120c.png
[20191124193615404.png]: /images/20210813/c574a5516a0840cf8bae1f96c313ef71.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 2]: /images/20210813/bf41d2c5135c432b84201b85798669b1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 3]: /images/20210813/1bfc68c0e5304858a889fe3dca550797.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NvbGRfX19wbGF5_size_16_color_FFFFFF_t_70 4]: /images/20210813/257925b4b51747cbab79e6613fa8bfa4.png