MySQL索引优化原则和失效情况

小鱼儿 2024-03-17 16:52 63阅读 0赞

#### 目录 ####

*  1. 全值匹配
 *  2. 最佳左前缀法则
 *  3. 不要在索引列上做任何计算
 *  4. 范围之后全失效
 *  5. 尽量使用覆盖索引
 *  6. 使用不等于（!=或<>）会使索引失效
 *  7. is null 或 is not null也无法使用索引
 *  8. like通配符以%开头会使索引失效
 *  9. 字符串不加单引号导致索引失效
 *  10. 少用or，用or连接会使索引失效

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

## 1. 全值匹配 ##

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

*  **创建表 插入数据**

CREATE TABLE users(
      id INT PRIMARY KEY AUTO_INCREMENT,
      user_name VARCHAR(20) NOT NULL COMMENT '姓名',
      user_age INT NOT NULL DEFAULT 0 COMMENT '年龄',
      user_level VARCHAR(20) NOT NULL COMMENT '用户等级',
      reg_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '注册时间'
    );
    
    INSERT INTO users(user_name,user_age,user_level,reg_time)
    VALUES('tom',17,'A',NOW()),('jack',18,'B',NOW()),('lucy',18,'C',NOW());

**按索引字段顺序匹配使用。**

xxxxxxxxxx
    EXPLAIN SELECT * FROM users WHERE user_name = 'tom';
    
    EXPLAIN SELECT * FROM users WHERE user_name = 'tom' AND user_age = 17
    
    EXPLAIN SELECT * FROM users WHERE user_name = 'tom' AND user_age = 17 
    AND user_level = 'A';

按顺序使用联合索引时, type类型都是 ref ,使用到了索引 效率比较高

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-L96I1PWu-1688780333046)(.\\img\\07.jpg)\]

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

如果创建的是联合索引,就要遵循 **最佳左前缀法则: 使用索引时，where后面的条件需要从索引的最左前列开始并且不跳过索引中的列使用。**

*  场景1: 按照索引字段顺序使用，三个字段都使用了索引,没有问题。
    
        xxxxxxxxxx
    
        EXPLAIN SELECT * FROM users WHERE user_name = 'tom'
    
        AND user_age = 17 AND user_level = 'A';
    
    \[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-j386Lpod-1688780333047)(.\\img\\04444.jpg)\]
 *  场景2: 直接跳过user\_name使用索引字段，索引无效，未使用到索引。
    
        xxxxxxxxxx
    
        EXPLAIN SELECT * FROM users WHERE user_age = 17 AND user_level = 'A';
    
    \[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-111H7Awo-1688780333047)(.\\img\\08.jpg)\]
 *  场景3: 不按照创建联合索引的顺序,使用索引
    
        xxxxxxxxxx
    
        EXPLAIN SELECT * FROM users WHERE
    
        user_age = 17 AND user_name = 'tom' AND user_level = 'A';
    
    \[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0IHrXbKh-1688780333048)(.\\img\\09.jpg)\]
    
    where后面查询条件顺序是 user\_age、user\_level、user\_name与我们建的索引顺序user\_name、user\_age、user\_level不一致，为什么还是使用了索引，这是因为MySql底层优化器给咱们做了优化。
    
    但是，最好还是要按照顺序 使用索引。

**最佳左前缀底层原理**

MySQL创建联合索引的规则是: 首先会对联合索引**最左边**的字段进行**排序** ( 例子中是 `user_name` ), **在第一个字段的基础之上** 再对第二个字段进行排序 ( 例子中是 `user_age` )

所以: 最佳左前缀原则其实是个B+树的结构有关系, 最左字段肯定是有序的, 第二个字段则是无序的(**联合索引的排序方式是: 先按照第一个字段进行排序,如果第一个字段相等再根据第二个字段排序**). 所以如果直接使用第二个字段 `user_age` 通常是使用不到索引的.

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-JtWzkEJk-1688780333048)(.\\img\\40.jpg)\]

## 3. 不要在索引列上做任何计算 ##

**不要在索引列上做任何操作，比如计算、使用函数、自动或手动进行类型转换,会导致索引失效，从而使查询转向全表扫描。**

*  插入数据
    
        xxxxxxxxxx
    
        INSERT INTO users(user_name,user_age,user_level,reg_time) VALUES('11223344',22,'D',NOW());
 *  场景1: 使用系统函数 left()函数
    
        xxxxxxxxxx
    
        EXPLAIN SELECT * FROM users WHERE LEFT(user_name, 6) = '112233';
    
    where条件使用计算后的索引字段 user\_name，没有使用索引，索引失效。
    
    \[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-rKkyEog1-1688780333048)(.\\img\\10.jpg)\]
 *  场景2: 字符串不加单引号 (隐式类型转换)
    
        xxxxxxxxxx
    
        EXPLAIN SELECT * FROM users WHERE user_name = 11223344;
    
    \[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-yUIxLJ38-1688780333049)(.\\img\\724444.jpg)\]

> 注: `Extra = Using where` 表示Mysql将对storage engine提取的结果进行过滤，过滤条件字段无索引；
> 
> ( 需要回表去查询所需的数据 )

## 4. 范围之后全失效 ##

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

*  场景1: 条件单独使用user\_name时, `type=ref`, `key_len=82`
    
        xxxxxxxxxx
    
        -- 条件只有一个 user_name
    
        EXPLAIN SELECT * FROM users WHERE user_name = 'tom';
    
    \[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lX7o3jjZ-1688780333049)(.\\img\\11.jpg)\]
 *  场景2: 条件增加一个 user\_age ( 使用常量等值) ,`type= ref` , `key_len = 86`
    
        xxxxxxxxxx
    
        EXPLAIN SELECT * FROM users WHERE user_name = 'tom' AND user_age = 17;
    
    \[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-c4GzImnZ-1688780333049)(.\\img\\12.jpg)\]
 *  场景3: 使用全值匹配, `type = ref` , `key_len = 168` , 索引都利用上了.
    
        xxxxxxxxxx
    
        EXPLAIN SELECT * FROM users WHERE user_name = 'tom'
    
        AND user_age = 17 AND user_level = 'A';
    
    \[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-XbMFpZzd-1688780333050)(.\\img\\13.jpg)\]
 *  场景4: 使用范围条件时, avg > 17 , `type = range` , `key_len = 86` , 与场景3 比较,可以发现 `user_level` 索引没有用上.
    
        xxxxxxxxxx
    
        EXPLAIN SELECT * FROM users WHERE user_name = 'tom'
    
        AND user_age > 17 AND user_level = 'A';
    
    \[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-mQRWgWE7-1688780333050)(.\\img\\14.jpg)\]

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

尽量使用覆盖索引（**查询列和索引列尽量一致，通俗说就是对A、B列创建了索引，然后查询中也使用A、B列**），减少select \*的使用。

*  场景1: 全值匹配查询, 使用 `select *`
    
        xxxxxxxxxx
    
        EXPLAIN SELECT * FROM users WHERE user_name = 'tom' AND user_age = 17
    
        AND user_level = 'A';
    
    \[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-aGRzWYP8-1688780333050)(.\\img\\15.jpg)\]
 *  场景1: 全值匹配查询, 使用 `select 字段名1 ,字段名2`
    
        xxxxxxxxxx
    
        EXPLAIN SELECT user_name , user_age , user_level FROM users WHERE user_name = 'tom'
    
        AND user_age = 17 AND user_level = 'A';
    
    使用覆盖索引（查询列与条件列对应），可看到Extra从Null变成了Using index，提高检索效率。
    
    \[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dLEoWZXb-1688780333051)(.\\img\\16.jpg)\]

> 注: `Using index` 表示 使用到了索引 , 并且所取的数据完全在索引中就能拿到,
> 
> (使用覆盖索引的时候就会出现)

## 6. 使用不等于（!=或<>）会使索引失效 ##

**使用 != 会使type=ALL，key=Null，导致全表扫描，并且索引失效。**

*  使用 `!=`
    
        xxxxxxxxxx
    
        EXPLAIN SELECT * FROM users WHERE user_name != 'tom';
    
    \[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-xfdbKRdk-1688780333051)(.\\img\\17.jpg)\]

## 7. is null 或 is not null也无法使用索引 ##

**在使用is null的时候，索引完全失效，使用is not null的时候，type=ALL全表扫描，key=Null索引失效。**

*  场景1: 使用 `is null`
    
        xxxxxxxxxx
    
        EXPLAIN SELECT * FROM users WHERE user_name IS NULL;
    
    \[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-SFCRT8xA-1688780333052)(.\\img\\18.jpg)\]
 *  场景2: 使用 `not null`
    
        xxxxxxxxxx
    
        EXPLAIN SELECT * FROM users WHERE user_name IS NOT NULL;
    
    \[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-ectYGPOL-1688780333052)(.\\img\\19.jpg)\]

## 8. like通配符以%开头会使索引失效 ##

**like查询为范围查询，%出现在左边，则索引失效。%出现在右边索引未失效。口诀：like百分加右边。**

*  场景1
    
        xxxxxxxxxx
    
        EXPLAIN SELECT * FROM users WHERE user_name LIKE '%tom%';
    
    \[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-7IcPJBim-1688780333052)(.\\img\\20.jpg)\]
 *  场景2
    
        xxxxxxxxxx
    
        EXPLAIN SELECT * FROM users WHERE user_name LIKE '%tom';
    
    \[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-5Dl4PQGs-1688780333053)(.\\img\\21.jpg)\]
 *  场景3
    
        xxxxxxxxxx
    
        EXPLAIN SELECT * FROM users WHERE user_name LIKE 'tom%';
    
    \[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-l8GJuXWz-1688780333053)(.\\img\\22.jpg)\]

> 注: `Using index condition` 表示 查找使用了索引，但是需要;';查询数据

**解决%出现在左边索引失效的方法：使用覆盖索引。**

**Case1:**

xxxxxxxxxx
    EXPLAIN SELECT user_name FROM users WHERE user_name LIKE '%jack%';

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-0gR1yhok-1688780333054)(.\\img\\23.jpg)\]

*  对比场景1可以知道, 通过使用覆盖索引 type = index,并且使用了 Using index,从全表扫描变成了全索引扫描.

> 注: `Useing where; Using index;` 查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

**Case2:**

xxxxxxxxxx
    EXPLAIN SELECT id FROM users WHERE user_name LIKE '%jack%';

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wnQHiZ83-1688780333054)(.\\img\\24.jpg)\]

*  这里出现 `type=index`，**因为主键自动创建唯一索引。**

**Case3:**

xxxxxxxxxx
    EXPLAIN SELECT user_name,user_age FROM users WHERE user_name LIKE '%jack%';
    EXPLAIN SELECT user_name,user_age,user_level FROM users WHERE user_name LIKE '%jack%';
    EXPLAIN SELECT id,user_name,user_age,user_level FROM users WHERE user_name LIKE '%jack%';

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Mu0tyc6F-1688780333055)(.\\img\\21114.jpg)\]

*  上面三组, explain执行的结果都相同，表明都使用了索引.

**Case4:**

xxxxxxxxxx
    EXPLAIN SELECT id,user_name,user_age,user_level,reg_time FROM users WHERE user_name 
    LIKE '%jack%';

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-wye4Cwff-1688780333055)(.\\img\\25.jpg)\]

*  分析：由于只在（user\_name,user\_age,user\_level）上创建索引， 当包含reg\_time时，导致结果集偏大（reg\_time未建索引）【**锅大，锅盖小，不能匹配**】，所以type=ALL。
 *  **like 失效的原理**

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-Lp6qrEsY-1688780333056)(.\\img\\411110.jpg)\]

1.  **%号在右:** 由于B+树的索引顺序，是按照首字母的大小进行排序，%号在右的匹配又是匹配首字母。所以可以在B+树上进行有序的查找，查找首字母符合要求的数据。所以有些时候可以用到索引.
2.  **%号在左:** 是匹配字符串尾部的数据，我们上面说了排序规则，尾部的字母是没有顺序的，所以不能按照索引顺序查询，就用不到索引.
3.  **两个%%号:** 这个是查询任意位置的字母满足条件即可，只有首字母是进行索引排序的，其他位置的字母都是相对无序的，所以查找任意位置的字母是用不上索引的.

## 9. 字符串不加单引号导致索引失效 ##

**varchar类型的字段，在查询的时候不加单引号导致索引失效，转向全表扫描。**

*  场景1
    
        xxxxxxxxxx
    
        SELECT * FROM users WHERE user_name = '123';
    
        SELECT * FROM users WHERE user_name = 123;
    
    上述两条sql语句都能查询出相同的数据。
    
    \[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-72WuMtD3-1688780333056)(.\\img\\26.jpg)\]
 *  场景2:
    
    \[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-RJJJEOBa-1688780333056)(.\\img\\27.jpg)\]
    
    \[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-DfJZmhRT-1688780333057)(.\\img\\28.jpg)\]
    
    通过explain执行结果可以看出，字符串（name）不加单引号在查询的时候，导致索引失效（type=ref变成了type=ALL，并且key=Null），并全表扫描。

## 10. 少用or，用or连接会使索引失效 ##

**在使用or连接的时候 `type=ALL`，key=Null，索引失效，并全表扫描。**

\[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-q0nH03HK-1688780333057)(.\\img\\29.jpg)\]

[54cbda7a4cac4b5883293da99fe39d03.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/15/b5c75a0912784208a46ce6bb7ed13df8.png