【项目实现典型案例】12.数据库数据类型不一致导致查询慢

雨点打透心脏的1/2处 2023-10-11 21:11 54阅读 0赞

#### 目录 ####

*  一、背景介绍
 *  二、思路&方案
 *  三、索引失效复现
 *   *  1、对应的索引
     *  2、表中字段的数据类型
     *  3、使用数值型进行查询
     *  4、使用字符型进行查询
 *  四、索引实现的六种情况
 *   *  1、类型转换，函数
     *  2、ISNULL
     *  3、通配符开头
     *  4、范围查询
     *  5、组合索引，不符合最左匹配原则
     *  6、WHERE子句中的OR
 *  五、总结
 *   *  1、理解索引失效的原因
     *  2、优化查询条件和索引顺序
     *  3、创建合适的索引
     *  4 、更新索引统计信息
     *  5、避免过度索引
 *  六、升华

## 一、背景介绍 ##

在开发的过程中在编写查询语句的时候经常会用到is\_delete=0的语句条件，但是我们在设计表的时候会把is\_delete字段的类型设置为varchar。这就导致我们在执行有is\_delete=0的sql语句的时候索引会失效。因为sql有一个优化器他会把数值类型的0转换成字符类型的0。数据类型转换会导致索引的失效。![在这里插入图片描述][3c2ecd50d3d24ecb9bf5d37a6b9b9c62.png]  
![在这里插入图片描述][d8dabfcff0004217958297db8e81d30a.png]

## 二、思路&方案 ##

1.  mysql三大数据类型，分未数值类型、时间日期类型和字符类型
2.  验证数据类型转换的sql语句查看索引是否生效
3.  验证数据类型没有转换的sql语句查看索引是否生效

## 三、索引失效复现 ##

> where条件里，字符类型的列如果传递的是数据类型的话可以查出来数据，但是索引会失效

### 1、对应的索引 ###

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

### 2、表中字段的数据类型 ###

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

### 3、使用数值型进行查询 ###

![在这里插入图片描述][0d0353945a884071b4d6ff830c409ddb.png]  
  type中的ALL代表全表查询，用结果上来看我们添加的索引是没有生效的。因为我们在执行sql语句的时候is\_delete赋的值是数值类型，但是数据库中是字符类型。所以进行了类型的转换。导致索引是没有生效的。

### 4、使用字符型进行查询 ###

![在这里插入图片描述][fd169e8f65b94be188e7e0af4964ae5e.png]  
type中从ALL转换成了ref，索引失效了

## 四、索引实现的六种情况 ##

### 1、类型转换，函数 ###

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

### 2、ISNULL ###

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

### 3、通配符开头 ###

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

### 4、范围查询 ###

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

### 5、组合索引，不符合最左匹配原则 ###

假设我们对字段 a，b 建立了一个组合索引（a，b）。  
如果我们的查询是以下几种，则会正常走联合索引：

where a = 1;  
where a = 2 and b = 2;  
如果是如下几种则不会走组合索引：

where b = 2;  
where b = 2 and a = 3;  
通过对比我们可以看出，索引的生效是遵循最左匹配原则的。在使用的时候，必须从最左侧的索引开始。

### 6、WHERE子句中的OR ###

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

## 五、总结 ##

### 1、理解索引失效的原因 ###

索引失效可能由于多种原因导致，如查询条件不符合索引列的顺序、使用了不匹配的数据类型、使用了函数或表达式等。理解索引失效的原因是优化索引的第一步，可以通过数据库的查询执行计划或者索引统计信息来定位问题。

### 2、优化查询条件和索引顺序 ###

根据索引失效的原因，可以优化查询条件和索引的顺序。例如，将查询条件中最具选择性的条件放在前面，以便数据库优化器能够更好地使用索引。另外，避免在索引列上使用函数或表达式，以免导致索引失效。

### 3、创建合适的索引 ###

根据查询的特点和频率，创建合适的索引可以提高查询性能。通过分析查询语句和数据访问模式，可以确定需要创建的索引类型和列。同时，避免创建过多的索引，以免增加维护成本和降低更新性能。

### 4 、更新索引统计信息 ###

索引统计信息对于数据库优化器的查询计划选择非常重要。定期更新索引统计信息可以帮助数据库优化器更准确地评估索引的选择性和成本，从而选择更合适的查询计划。

### 5、避免过度索引 ###

过度索引是指创建了过多的重复或不必要的索引。过度索引不仅增加了存储和维护成本，还可能导致索引失效和查询性能下降。因此，需要审查和删除不必要的索引，保持索引的简洁和高效。

## 六、升华 ##

&emps;只有我们明确了什么情况下索引会失效，我们再添加索引和使用索引的时候才会生效。才不会导致我们添加了索引占用了资源还没有生效的情况。

[3c2ecd50d3d24ecb9bf5d37a6b9b9c62.png]: https://img-blog.csdnimg.cn/3c2ecd50d3d24ecb9bf5d37a6b9b9c62.png
[d8dabfcff0004217958297db8e81d30a.png]: https://img-blog.csdnimg.cn/d8dabfcff0004217958297db8e81d30a.png
[d8d498bea5ed4e4389bda8daa41f251d.png]: https://img-blog.csdnimg.cn/d8d498bea5ed4e4389bda8daa41f251d.png
[18ad0a5ed2364717b0c1dc5b062a7e23.png]: https://img-blog.csdnimg.cn/18ad0a5ed2364717b0c1dc5b062a7e23.png
[0d0353945a884071b4d6ff830c409ddb.png]: https://img-blog.csdnimg.cn/0d0353945a884071b4d6ff830c409ddb.png
[fd169e8f65b94be188e7e0af4964ae5e.png]: https://img-blog.csdnimg.cn/fd169e8f65b94be188e7e0af4964ae5e.png
[770b29287ccf4a98af811dc0826d5f8f.png]: https://img-blog.csdnimg.cn/770b29287ccf4a98af811dc0826d5f8f.png
[6eea0e65d9bd4408a143f74601ecdf9a.png]: https://img-blog.csdnimg.cn/6eea0e65d9bd4408a143f74601ecdf9a.png
[1949a2188a054155b581ce2ed3be6580.png]: https://img-blog.csdnimg.cn/1949a2188a054155b581ce2ed3be6580.png
[6860009bc5e34e7784ad4bc5f29e8c3b.png]: https://img-blog.csdnimg.cn/6860009bc5e34e7784ad4bc5f29e8c3b.png
[083dd6a96ec24c3b8d98f1865214faa1.png]: https://img-blog.csdnimg.cn/083dd6a96ec24c3b8d98f1865214faa1.png