[MySQL高级](四) 索引优化之索引失效

素颜马尾好姑娘i 2022-05-04 01:26 371阅读 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

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

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

**2. 索引失效优化案例**

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

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

☀ 正确的使用方式

> EXPLAIN SELECT \* FROM staffs WHERE NAME = 'July';

![70 1][]

>   
> EXPLAIN SELECT \* FROM staffs WHERE NAME = 'July' AND age = 25;

![70 2][]

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

![70 3][]  
☁ 索引失效

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

![70 4][]

>   
> EXPLAIN SELECT \* FROM staffs WHERE pos = '实习生';

![70 5][]

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

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

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

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

![70 6][]

如上图所示，如果是匹配了两个索引字段，那么key\_len应该至少大于74，而且ref应该是两个const。由此可见，该查询语句只使用了name字段的索引，后面的索引就失效了。

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

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

这里的任何操作包括计算、函数、（自动or手动）类型转换，会导致索引失效而转向全表扫描

> EXPLAIN SELECT \* FROM staffs WHERE NAME = 'July';

![70 7][]

>   
> EXPLAIN SELECT \* FROM staffs WHERE left(NAME,4) = 'July';

![70 8][]  
2.4 存储引擎不能使用索引中的范围条件右边的列

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

![70 9][]

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

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

2.5 尽量使用覆盖索引

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

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

![70 11][]

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

![70 12][]

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

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

> EXPLAIN SELECT \* FROM staffs WHERE NAME = 'July';

![70 14][]

>   
> EXPLAIN SELECT \* FROM staffs WHERE name != 'July';

![70 15][]

>   
> EXPLAIN SELECT \* FROM staffs WHERE name <> 'July';

![70 16][]  
**2.7 is null,is not null也无法使用索引**

> EXPLAIN SELECT \* FROM staffs WHERE name is null;

![70 17][]

> EXPLAIN SELECT \* FROM staffs WHERE name is not null;

![70 18][]  
**2.8 like以通配符开头（’%abc…’）索引失效会变成全表扫描**

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

![70 19][]

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

![70 20][]

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

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

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

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

> EXPLAIN SELECT id,name,age,add\_time FROM staffs WHERE name like '%July%'

![70 23][]  
**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;

![70 24][]  
   
我们来看一下这两个SQL的执行情况

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

![70 25][]

> EXPLAIN SELECT \* FROM staffs WHERE name = 200;

![70 26][]

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

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

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

![70 27][]  
**3. 总结**

**3.1 小练**

**![70 28][]**

**3.2 大练**

创建表并创建索引

> CREATE TABLE \`test03\` (  
>   \`id\` int(11) NOT NULL,  
>   \`c1\` varchar(255) DEFAULT NULL,  
>   \`c2\` varchar(255) DEFAULT NULL,  
>   \`c3\` varchar(255) DEFAULT NULL,  
>   \`c4\` varchar(255) DEFAULT NULL,  
>   \`c5\` varchar(255) DEFAULT NULL,  
>   PRIMARY KEY (\`id\`)  
> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
> 
> create index idx\_test03\_c1234 on test03(c1,c2,c3,c4);

【问题】我们创建了复合索引idx\_test03\_c1234，根据以下SQL分析索引的使用情况

*  mysql查询优化器自动优化 ![70 29][]
 *  范围查找 ![70 30][]
 *  order by ![70 31][]![70 32][]![70 33][]![70 34][]
 *  group by ![70 35][]

3.3 一般性建议

*  对于单键索引，尽量选择针对当前query过滤性更好的索引
 *  在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
 *  在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引
 *  尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

3.4 优化总结口诀

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

\---------------------

[70]: /images/20220504/8b4717ff79ed407b8a2dff3503e260bd.png
[70 1]: /images/20220504/9df1480e8457436eb5bb5ce28911a1ad.png
[70 2]: /images/20220504/d457f446373a4ab4b4bcd052a46bcfac.png
[70 3]: /images/20220504/7cbec5f2811f42b492b974f1cde7f4bb.png
[70 4]: /images/20220504/71c3ff958066477fac321e70e76b2fc0.png
[70 5]: /images/20220504/5fc468894b2e46058bcc4a9685ee9f19.png
[70 6]: /images/20220504/429e4fcc890a4b30956e9a4f51f879a9.png
[70 7]: /images/20220504/518d32d060a64ff492d0d696d97560d0.png
[70 8]: /images/20220504/9ca7358597e944a5833065735ae64cfa.png
[70 9]: /images/20220504/db406cd0c0874b6a8e17a94256c9db76.png
[70 10]: /images/20220504/d1f7f5bbfefd4f819fea0894011c2936.png
[70 11]: /images/20220504/0df48baf846544c7885ba999819df595.png
[70 12]: /images/20220504/7f73e77703d845198138fa0cffddd745.png
[70 13]: /images/20220504/8296564f18b14d0b9c87ce19fe9f2bbd.png
[70 14]: /images/20220504/4d9a6be6601245eea0eb7311459f9b89.png
[70 15]: /images/20220504/28a0e71cb7b84ccc9b271fec71bd1be0.png
[70 16]: /images/20220504/594d30270d2f48c9a2eed65b90dd17a6.png
[70 17]: /images/20220504/e7863024b60941cea111795448ad4df6.png
[70 18]: /images/20220504/467f9da043544ac7bedef40592cb800c.png
[70 19]: /images/20220504/288ba163546d4f07b2746895c91aa259.png
[70 20]: /images/20220504/40a4215319ef40ffbfdec4f6596c11b6.png
[70 21]: /images/20220504/369780a2f63c408d99df7a86d196b5da.png
[70 22]: /images/20220504/2db490e477084e1d92dc0bcba2e249bd.png
[70 23]: /images/20220504/e54a8d8103664abf8eba17e167a5890e.png
[70 24]: /images/20220504/b2fddaefce3e4b4d8eab9c7e667b74a6.png
[70 25]: /images/20220504/f7e8f1ac88064bd3a7000c70b158d07d.png
[70 26]: /images/20220504/3ebad70ef57745a6910c7fb363116769.png
[70 27]: /images/20220504/59797f1b545b43e5b57546859ad1e789.png
[70 28]: /images/20220504/b70230d8ed404102983f2b8b48095e4d.png
[70 29]: /images/20220504/33c572b609ae4930940d3a1acc05756b.png
[70 30]: /images/20220504/e2c02161f1134f6293c6563cf5cff6f5.png
[70 31]: https://img-blog.csdn.net/20181022134833529?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N6Y251b251bw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[70 32]: https://img-blog.csdn.net/20181022134851408?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N6Y251b251bw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[70 33]: https://img-blog.csdn.net/2018102213492384?watermark/2/text/aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2N6Y251b251bw==/font/5a6L5L2T/fontsize/400/fill/I0JBQkFCMA==/dissolve/70
[70 34]: /images/20220504/e51eb31ff66b43b1bef5d005a9d341e6.png
[70 35]: /images/20220504/cf05026827b049b2a4fcee9669796c27.png