Mysql索引失效

曾经终败给现在 2023-10-11 18:26 50阅读 0赞

在数据库日常使用中，因索引失效而导致的没有充分利用索引，是导致数据库查询效率低下的一个重要原因之一。

MySQL中提高性能的一个最有效的方式是对数据表**设计合理的索引** 。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。

*  使用索引可以**快速地定位**表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。
 *  如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。

大多数情况下都(默认)采用B+树 来构建索引。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash索引。  
其实，用不用索引，最终都是**优化器**说了算。优化器是基于基于**cost开销**，它不是基干规则，也不是基于语义。怎么样开销小就怎么来。 另外，**SOL语句是否使用索引，跟数据库版本，数据量，数据选择度都有关系。**

#### 接下来介绍索引失效的十一种情况： ####

####    一、全值匹配 ####

student表中有字段【id】和【age】，并创建以下两个索引

CREATE INDEX idx_id ON student(id);
    CREATE INDEX idx_id_age ON student(id,age);

此时执行以下查询语句

# idx_id 索引生效
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE id = 1;
    # idx_id 索引失效， idx_id_age 索引生效
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE id = 1 AND age = 17;

#### 二、**最佳左前缀法则**  ####

在MySQL建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

为student表中字段【age】、【classId】、【name】创建以下索引：

CREATE INDEX idx_age_cid_name ON student(age, class_id, NAME);

执行以下SQL语句：

# idx_age_cid_name 索引未生效，因为索引最左侧age没有匹配
    EXPLAIN SELECT SQL_NO_CACHE * FROM student 
    WHERE class_id = 3 AND NAME = 'Jerry';
    # idx_age_cid_name 索引生效
    EXPLAIN SELECT SQL_NO_CACHE * FROM student 
    WHERE age = 17 AND class_id = 3 AND NAME = 'Jerry';

> #### 结论:MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中第1个字段时，多列(或联合)索引不会被使用。 ####

#### 三、主键插入原则 ####

![2ad1b1da7b5e4f78b8cf4a8e678bfc53.png][]

如果此时再插入一条主键值为 9 的记录，那它插入的位置就如下图： ![165023b010614ab592911fa223059997.png][]

可这个数据页已经满了，再插进来咋办呢？我们需要把当前  **页面分裂**  成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着：  **性能损耗**  ！所以如果我们想尽量 避免这样无谓的性能损耗，最好让插入的记录的  **主键值依次递增**  ，这样就不会发生这样的性能损耗了。

所以我们建议：让主键具有  **AUTO\_INCREMENT**  ，让存储引擎自己为表生成主键，而不是我们手动插入， 这样的主键占用空间小，顺序写入，减少页分裂。

#### 四、**计算、使用函数****导致索引失效** ####

**① 计算**

在student的字段【stuno】上加入以下索引：

CREATE INDEX idx_stu ON student(stuno);

执行SQL语句：

#索引生效
    EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student 
    WHERE stuno = 900000;
    #从b+树中取出值计算后再比较，索引失效
    EXPLAIN SELECT SQL_NO_CACHE id, stuno, NAME FROM student 
    WHERE stuno+1 = 900001;

**② 函数**

为student的字段【name】加入以下索引：

CREATE INDEX idx_name ON student(NAME);

以下语句idx\_name索引失效

# 运用了函数，idx_name 索引失效
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.NAME,3) = 'Jer';

#### 五、**类型转换****(****自动或手动****)****导致索引失效** ####

# 未使用到索引 
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
    # 使用到索引 
    EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

*  name=123发生类型转换，索引失效。

#### 六、**范围条件右边的列索引失效** ####

在student表上的创建联合索引

CREATE INDEX idx_age_cid_name ON student(age, class_id, NAME);

执行以下SQL语句发现只用到了 idx\_age\_cid\_name 索引的【age】和【class\_id】字段

EXPLAIN SELECT SQL_NO_CACHE * FROM student s
    WHERE s.age=30 AND s.class_id>20 AND s.name = 'abc' ;

因为sql语句中class\_id是范围查询，索引中范围以后的字段会失效

*  范围右边(这里指的是索引中的排列)的列不能使用。例如：(<), (<=), (>=), (>)，between等。
 *  更改索引,将范围查询条件放置语句最后：

CREATE INDEX idx_age_name_cid ON student(age, NAME, class_id);

#### 七、**不等于****(!=** **或者****<>)****索引失效**  ####

CREATE INDEX idx_name ON student(NAME);
    
    #对于以下SQL语句 idx_name 索引失效
    EXPLAIN SELECT SQL_NO_CACHE * FROM student 
    WHERE NAME <> 'Jerry';
    #或者
    EXPLAIN SELECT SQL_NO_CACHE * FROM student 
    WHERE NAME != 'Jerry';

#### **八、空值判断** ####

is null可以使用索引，is not null无法触发索引

CREATE INDEX idx_age ON student(age);
    
    # idx_age 索引生效
    EXPLAIN SELECT SQL_NO_CACHE * FROM student 
    WHERE age IS NULL;
    
    #idx_name 索引失效
    EXPLAIN SELECT SQL_NO_CACHE * FROM student 
    WHERE age IS NOT NULL;

**结论**:

*  最好在设计数据表的时候就将字段设置为 **NOT NULL** 约束，可以将INT类型的字段的默认值设置为 0以及将字符类型的默认值设置为空字符串(”)来替代NULL。
 *  同理，在查询中使用 **not like** 也无法使用索引，导致全表扫描。

#### 九、**like****以通配符****%****开头索引失效**  ####

# idx_name 索引失效，因为Like为左模糊匹配
    EXPLAIN SELECT id, stuno, NAME FROM student 
    WHERE NAME LIKE '%abc';

> 结论：页面搜索**严禁**左模糊或者全模糊，如果需要请走**搜索引擎**来解决。

#### 十、**OR** **前后存在非索引的列，索引失效**  ####

假设此时student表中只有以下索引

CREATE INDEX idx_age ON student(age);
    CREATE INDEX idx_name ON student(name);

# 未使用到索引 
    EXPLAIN SELECT SQL_NO_CACHE * FROM student 
    WHERE age = 10 OR class_id = 100;
    # 使用到索引 
    EXPLAIN SELECT SQL_NO_CACHE * FROM student 
    WHERE age = 10 OR name = 'Abel';

#### 十一、**数据库和表的字符集统一使用****utf8mb4** ####

1.  统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好，统一字符集可以避免由于字符集转换产生的乱码。
2.  不 同的 **字符集**进行比较前需要进行 **转换**会造成索引失效。

#### **总结** ####

*  对于单列索引，尽量选择针对当前query过滤性更好的索引
 *  在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
 *  在选择组合索引的时候，尽量选择能够包含当前query中的where子句中更多字段的索引。
 *  在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。

总之，书写 SQL 语句时，尽量避免造成索引失效的情况。

[2ad1b1da7b5e4f78b8cf4a8e678bfc53.png]: https://img-blog.csdnimg.cn/2ad1b1da7b5e4f78b8cf4a8e678bfc53.png
[165023b010614ab592911fa223059997.png]: https://img-blog.csdnimg.cn/165023b010614ab592911fa223059997.png