【MySQL高级】（五）索引失效、索引优化

た入场券 2022-04-23 15:04 332阅读 0赞

# 1. 前言 #

索引的建立是为了让我们更加高效快速的查询出结果，但是，要想充分利用起索引，我们首先要解决的最大问题就是要避免索引失效，下面我们来一起通过实例来探讨造成索引失效的情况，并通过优化SQL查询语句来避免索引失效。

➤ 准备工作：、

*  创建数据表SQL

CREATE TABLE `staffs` (
      `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
      `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
      `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
      `pos` varchar(20) NOT NULL COMMENT '职位',
      `add_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

*  插入基础数据

INSERT INTO `test`.`staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES ('1', '王洪玉', '25', '总经理', '2018-05-22 09:45:44');
    INSERT INTO `test`.`staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES ('2', 'July', '25', '实习生', '2018-05-22 09:45:58');
    INSERT INTO `test`.`staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES ('3', '李四', '20', '实习生', '2018-05-22 09:46:04');
    INSERT INTO `test`.`staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES ('4', '王玉', '21', '老板娘', '2018-05-22 09:46:17');
    INSERT INTO `test`.`staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES ('5', '王五', '22', '服务员', '2018-05-22 09:46:26');
    INSERT INTO `test`.`staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES ('6', '赵六', '80', '传菜生', '2018-05-22 09:46:45');

*  创建索引

ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos (NAME, age, pos);

*   查询索引

SHOW INDEX FROM staffs

![20190317172659554.png][]

【知识补充】：  
MYSQL EXPLAIN解析一 EXTRA中的USING INDEX，USING WHERE，USING INDEX CONDITION  
using index 和using where只要使用了索引我们基本都能经常看到，而using index condition则是在mysql5.6后新加的新特性

1.      using index ：使用覆盖索引的时候就会出现
2.      using where：在查找使用索引的情况下，需要回表去查询所需的数据
3.      using index condition：查找使用了索引，但是需要回表查询数据
4.      using index & using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

# 2. 索引失效的情况（超详细） #

## 2.1 全值匹配我最爱 ##

全值匹配指的是我要**查询的语句的字段和顺序恰好和建立的索引的字段和顺序一致**，否则索引失效。

**✈ 总结：**当索引是按照name,age,pos顺序建立的时候，如果查询条件不是以name开头，就会导致索引失效。总结一句话就是：`带头大哥不能死！`

## 2.2 最佳左前缀法则 ##

如果索引了多列，要遵守最左前缀法则，指的是查询从索引的最左前列开始并且`不跳过索引中的列`。

**✈ 总结：**当索引是按照name,age,pos顺序建立的时候，如果查询条件是以name开头，但是没有按顺序，就会导致后面的索引失效。总结一句话就是：`中间兄弟不能断！`

## 2.3 不在索引列上做任何操作 ##

这里的任何操作包括计算、函数、（自动or手动）类型转换，会导致索引失效而转向全表扫描。总结一句话就是：`索引列上不操作！`

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

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = '实习生';

![20190317173229568.png][]

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age > 25 AND pos = '实习生';

![2019031717324928.png][]

**✈ 总结：**当我们使用age > 25这种范围查找的时候，type变为了range，并且key\_len等于78，说明已经使用了name，和age的索引，但是，age的索引变为了范围排序，而并不是精确查找，导致后面的pos索引失效。所以总结就是：`范围之后全失效！`

## 2.5 尽量使用覆盖索引 ##

尽量使用索引的查询即索引列和查询列一致，减少select \*

EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME = 'July' AND age > 25 AND pos = '实习生';

![20190317173413101.png][]

## 2.6 mysql在使用不等于（！=或者<>）的时候无法使用索引导致全表扫描 ##

EXPLAIN SELECT * FROM staffs WHERE NAME <> 'July';

**如果定要需要使用不等于,请用覆盖索引**

EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME != 'July';

## 2.7 is null,is not null也可能导致索引失效 ##

**1、在字段为not null的情况下，使用is null 或 is not null 会导致索引失效：**

EXPLAIN select * from staffs where name is null
    EXPLAIN select * from staffs where name is not null

![20190317173549907.png][]

** 解决方式：覆盖索引**

EXPLAIN select  name,age,pos from staffs where name is not null

**2、在字段为null的情况下，使用 is not null 会导致索引失效，而is null 不会失效：**

**解决方式：覆盖索引**

EXPLAIN select  name,age,pos from staffs where name is not null

## 2.8 like以通配符开头（’%abc…’）索引失效会变成全表扫描 ##

EXPLAIN SELECT * FROM staffs WHERE name like '%July%'

![20190317173712581.png][]

EXPLAIN SELECT * FROM staffs WHERE name like '%July'

![20190317173738561.png][]

EXPLAIN SELECT * FROM staffs WHERE name like 'July%'

![20190317173800551.png][]

那么，我们的需求就是要模糊查询带 July 的条件，你在后面加%匹配符明显不符合我的需求，那么有没有一种方法解决like’%字符串%’时索引不被使用的方法呢？

EXPLAIN SELECT id,name,age FROM staffs WHERE name like '%July%'

![20190317173825176.png][]这种方式就是通过覆盖索引方式解决索引丢失问题，但是问题又来了，如果我要查询的字段多呢，如下SQL，就不能使用覆盖索引了，所以说还是存在局限性。

EXPLAIN SELECT id,name,age,add_time FROM staffs WHERE name like '%July%'

![20190317173904382.png][]

## 2.9 字符串不加单引号索引失效 ##

我们在数据库中添加一条数据，SQL如下：

INSERT INTO `test`.`staffs` (`id`, `name`, `age`, `pos`, `add_time`) VALUES ('7', '200', '0', '假人', '2018-05-22 15:02:55');

需要注意的是，我们的name字段类型为varchar类型，我们插入了一个名字为“200”的假人。  
我们通过下面两个SQL进行查询，都能够得到正确的数据，这是因为mysql自动给200做了类型转换。

SELECT * FROM staffs WHERE name = '200';
    SELECT * FROM staffs WHERE name = 200;

![20190317174006570.png][]

我们来看一下这两个SQL的执行情况

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

![20190317174035399.png][]

EXPLAIN SELECT * FROM staffs WHERE name = 200;

![20190317174053744.png][]

从上图所示可以看出，第二条的SQL语句索引失效，全表扫描。

## 2.10 少用or，用它来连接时会索引失效 ##

EXPLAIN SELECT * FROM staffs WHERE name = 'July' or age = 25

**可改为联合查询**

![20190317174149773.png][]

# 3. 总结 #

## 3.1 小练 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ppblhZYW4_size_16_color_FFFFFF_t_70][]

##  优化总结口诀 ##

*  全值匹配我最爱，最左前缀要遵守；
 *  带头大哥不能死，中间兄弟不能断；
 *  索引列上少计算，范围之后全失效；
 *  LIKE百分写最右，覆盖索引不写星；
 *  不能空值还有OR，索引失效要少用；
 *  VAR引号不可丢，SQL高级也不难；

原文：https://blog.csdn.net/why15732625998/article/details/80409164

[20190317172659554.png]: /images/20220307/9304d74c27a54719be3af4b0e3d1c592.png
[20190317173229568.png]: /images/20220307/7e571d5539924057bfd1e387bcb1733f.png
[2019031717324928.png]: /images/20220307/c3763e4173e04af7944dae8ceb6a836f.png
[20190317173413101.png]: /images/20220307/a1506fd8a8234f22a6912f00f8b66b50.png
[20190317173549907.png]: /images/20220307/91a87c509cb04c4baf18b4379de91bda.png
[20190317173712581.png]: /images/20220307/909bdc524c7d4666abde7e94d9c6604c.png
[20190317173738561.png]: /images/20220307/3a309e210ee64f9482e8a63ca4793954.png
[20190317173800551.png]: /images/20220307/54fb37d7c3f443218e2ce78c4007cb4e.png
[20190317173825176.png]: /images/20220307/2abb071242eb42f4b713ff858685ce40.png
[20190317173904382.png]: /images/20220307/51ab7b01f9c84a608bf6227f319ffe56.png
[20190317174006570.png]: /images/20220307/5fc49a73f4b24b5a943a0d124979d1ed.png
[20190317174035399.png]: /images/20220307/3e14820694794590a6c3f379c84b12c5.png
[20190317174053744.png]: /images/20220307/8a186f4a76c84b3589995131cbcd59ef.png
[20190317174149773.png]: /images/20220307/f177db1426ef4eaf87302289325d0ddf.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L0ppblhZYW4_size_16_color_FFFFFF_t_70]: /images/20220307/8943296b8ea5495eb6ec0feec99e8c2d.png