【项目实战典型案例】02.SQL语句优化

Myth丶恋晨 2023-10-11 21:11 132阅读 0赞

#### 目录 ####

*  一、背景介绍
 *   *  1、查询11s导致接口请求超时
     *  2、由于设计数据稍微大了点，接口调用就超过了1s
 *  二、思路&方案
 *  三、过程
 *   *  1、Mysql语句的执行顺序
     *  2、explain关键字解析
     *  3、distinct和group by效率对比
     *  4、验证数据类型不一致是否会影响索引
     *   *  1）、数据类型不一致
         *  2）、数据类型一致
 *  四、优化的好处
 *   *  1、提高查询性能
     *  2、减少系统负载
     *  3、提高用户体验
     *  4、节省成本
     *  5、改善系统的可扩展性
 *  五、总结

## 一、背景介绍 ##

### 1、查询11s导致接口请求超时 ###

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

### 2、由于设计数据稍微大了点，接口调用就超过了1s ###

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

## 二、思路&方案 ##

*  sql语句各部分的执行顺序
 *  明确distinct和group by的差异
 *  查看sql语句是否已经添加了索引，索引是否失效

## 三、过程 ##

### 1、Mysql语句的执行顺序 ###

1. from
    2. on
    3. join
    4. where
    5. group by
    6. having + 聚合函数
    7. select
    8. distinct
    9. order by
    10. limit

![在这里插入图片描述][4dcb3320f2de4f2383fdd1ab9f43e581.png]  
上面sql语句执行的顺序为2–>3–>4–>1–>5–>6

### 2、explain关键字解析 ###

explain是解释、说明的意思。在mysql里，一条查询语句需要经过Mysql查询优化器的各种成本和规则，生成一个执行计划。儿explain关键字就是查询这个执行计划的，通过explain关键字，可以分析我们的查询语句的效率。

> 语法：explain select \* from table

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

### 3、distinct和group by效率对比 ###

1.  有索引的情况下，group by和distinct都能用索引，效率相同
2.  没有索引的时候，distinct效率高于group by，distinct是根据信息不同进行直接进行去重，group by的原理是结果先进行分组排序，然后返回每组中的第一条数据
3.  如果是单纯的去重操作的话，无论是否有索引，distinct的效率都更加高，但是吐过查询的列和去重的列不对应的话，distinct就无法使用了。相较于group by不够灵活
4.  group by的语义更加的明确，并且group by可以根据分组的情况加上聚合函数，做一些其他的处理，功能更加丰富，但是有时间效率将低于distinct
5.  distinct用法

select distinct 列1 ， 列2 from table

1.  group by用法

select 列1，列2 from table group by 列1 ，列2

### 4、验证数据类型不一致是否会影响索引 ###

#### 1）、数据类型不一致 ####

sql语句

SELECT
    	aui.ding_phone,
    	aui.ding_name,
    	aui.chaoxing_name,
    	aui.chaoxing_phone 
    FROM
    	(SELECT info_id FROM arpro_user_course_info WHERE course_id = 223667994 AND class_id = 55801765 AND is_delete = 0 GROUP BY info_id ) auci
    	LEFT JOIN arpro_user_info aui ON auci.info_id = aui.id

表字段数据类型  
![在这里插入图片描述][32116caf347f47de934abc97089441be.jpeg_pic_center]  
![在这里插入图片描述][16d7b69642ce425da3c2c8c881457b4d.jpeg_pic_center]  
创建索引  
![在这里插入图片描述][92e65b919c40434fb476a3bf522705c1.jpeg_pic_center]  
执行结果：主键索引没有生效  
![在这里插入图片描述][e2b113fab0734827982b4f60ef3d3712.jpeg_pic_center]

#### 2）、数据类型一致 ####

将数据类型调整为bigint

将group调整为distinct

SELECT
    	aui.ding_phone,
    	aui.ding_name,
    	aui.chaoxing_name,
    	aui.chaoxing_phone 
    FROM
    	( SELECT DISTINCT info_id FROM arpro_user_course_info WHERE course_id = 223667994 AND class_id = 55801765 AND is_delete = 0 ) auci
    	LEFT JOIN arpro_user_info aui ON auci.info_id = aui.id

执行结果  
![在这里插入图片描述][ee194cb303574af3a7a8ff86df15b61e.png]

## 四、优化的好处 ##

### 1、提高查询性能 ###

优化SQL语句可以减少查询的执行时间和资源消耗，提高系统的响应速度和吞吐量。通过使用合适的索引、优化查询计划和减少不必要的数据读取，可以有效地提高查询的性能。

### 2、减少系统负载 ###

优化SQL语句可以减少系统的负载，提高系统的稳定性和可靠性。通过减少不必要的数据库访问、优化查询语句的效率和减少数据传输量，可以降低系统的资源消耗，减少数据库的负载。

### 3、提高用户体验 ###

优化SQL语句可以提高用户的体验和满意度。快速响应的查询结果可以让用户更好地享受系统的功能，提高用户的工作效率和满意度。同时，优化SQL语句还可以降低系统的错误率和异常情况，提高系统的可用性和稳定性。

### 4、节省成本 ###

优化SQL语句可以节省系统的资源和成本。通过减少不必要的数据库操作和优化查询语句的效率，可以降低数据库的存储和计算成本。同时，优化SQL语句还可以减少系统的维护和运维成本，提高系统的可维护性和可扩展性。

### 5、改善系统的可扩展性 ###

优化SQL语句可以改善系统的可扩展性和性能扩展能力。通过优化查询语句的效率和减少数据传输量，可以降低系统的资源消耗，提高系统的性能和扩展能力。这有助于系统在面对大量用户和数据时仍然能够保持高性能和稳定性。

## 五、总结 ##

明确sql语句的执行顺序有助于我们对于sql语句进行优化。提高我们对数据库和sql语句的理解。来编写效率更高的语句。优化SQL语句可以提高查询性能、减少系统负载、提高用户体验、节省成本和改善系统的可扩展性。这些好处有助于提高系统的性能、稳定性和可用性，提升用户的满意度和系统的价值。

[41f407acee084048889519666c355efa.png]: https://img-blog.csdnimg.cn/41f407acee084048889519666c355efa.png
[641eb57068b545d7b7ea31c03d410b18.png]: https://img-blog.csdnimg.cn/641eb57068b545d7b7ea31c03d410b18.png
[4dcb3320f2de4f2383fdd1ab9f43e581.png]: https://img-blog.csdnimg.cn/4dcb3320f2de4f2383fdd1ab9f43e581.png
[24652080447546698a4082ff95c39f1b.png]: https://img-blog.csdnimg.cn/24652080447546698a4082ff95c39f1b.png
[32116caf347f47de934abc97089441be.jpeg_pic_center]: https://img-blog.csdnimg.cn/32116caf347f47de934abc97089441be.jpeg#pic_center
[16d7b69642ce425da3c2c8c881457b4d.jpeg_pic_center]: https://img-blog.csdnimg.cn/16d7b69642ce425da3c2c8c881457b4d.jpeg#pic_center
[92e65b919c40434fb476a3bf522705c1.jpeg_pic_center]: https://img-blog.csdnimg.cn/92e65b919c40434fb476a3bf522705c1.jpeg#pic_center
[e2b113fab0734827982b4f60ef3d3712.jpeg_pic_center]: https://img-blog.csdnimg.cn/e2b113fab0734827982b4f60ef3d3712.jpeg#pic_center
[ee194cb303574af3a7a8ff86df15b61e.png]: https://img-blog.csdnimg.cn/ee194cb303574af3a7a8ff86df15b61e.png