【2023】MySQL详细 ——SQL优化篇(二)

╰+哭是因爲堅強的太久メ 2023-10-13 09:09 21阅读 0赞

### \- 表格关系设 ###

> crm系统有多种权限，用户可以分为不同的角色，但是每个用户可以属于多个角色，一个权限授予多种角色，一个角色拥有多种权限1. 先寻找实体表、
> 
> 1.  先寻找实体表
> 2.  再寻找他们的关系——1:1，1:M，M:N

![在这里插入图片描述][bb9b1496c5ff4fdfa31b0f883428929b.png_pic_center]

## 一、SQL性能检测：EXPLAIN ##

#### 1. 在查询语句前面加入 ’EXPLAIN ‘ 关键字 ####

![在这里插入图片描述][d250e8ae7f2449c89c6cfc8dca9bf6bf.png_pic_center]

*  id：表示查询顺序

数值越大，越先查询，子查询时显示多个；

from后面的sql查询最先执行，select后面from前面的sql查询第二执行，

![在这里插入图片描述][c1a6f4558bed49d080253ffe54e9852d.png_pic_center]

*  select\_type：表示select的类型，常见的取值  
    ![在这里插入图片描述][2c3943780301463eb3f4ba8fd9e6bf7f.png_pic_center]
 *  table：表名
 *  ⭐**type**：显示的访问类型，  
    ![在这里插入图片描述][f8d43fa68cfe4f40a3a6d1690290b5bd.png_pic_center]

possi  
![在这里插入图片描述][5ba74ab27b3142cfa9710e53623aa84a.png_pic_center]

⭐**key：索引**

key\_len：索引长度

⭐**rows：扫描的行数**

#### 2. 使用have\_profiling分析sql ####

>  *  SELECT @@have\_profiling; ：通过该指令查看是否支持
>  *  SELECT @@profiling; ：通过该指令查看是否开启，默认0，表示为开启，
>  *  SET profiling=1; ：开启have\_profiling开关，
>  *  SHOW PROFILES; 开启之后可以通过该指令查看前面执行的sql的执行时间

![在这里插入图片描述][f165fd23ccfe483fb4306ecc8da6df13.png_pic_center]

1.  慢查询日志

## 二、常见sql优化 ##

1.  **优化insert语句**

![在这里插入图片描述][d1dd026de7b342a18eae2ad1445e2e7e.png_pic_center]

start transaction：开启事务

commit：关闭事务  
![在这里插入图片描述][0dbd6c2112cd4eedbdad5b7156160f59.png_pic_center]

1.  **order by优化-排序**
    
    mysql排序分为两种：
    
    a. filesort排序：非索引排序，文件排序；当有需要排序的字段没有索引时，即会使用该方式  
    ![在这里插入图片描述][3f9c8e4635194e55b3d42a3e1990cd4b.png_pic_center]
    
    b. using index排序：通过有序索引顺序扫描返回有效数据，这种情况不需要额外排序，操作效率高；当查询排序的字段都是有索引时，即会使用该排序方式；所以尽量使用覆盖索引，对需要排序的字段都建立索引
    
    ![在这里插入图片描述][fd37395fcae04d2f85b3ec682bf8ce25.png_pic_center]
    
    进行多字段排序时，尽量使用同一种排序方式，否则第二种排序会失效  
    ![在这里插入图片描述][1c81037c048946a19a7dfd52af993ea2.png_pic_center]
    
    1.  **group by优化-分组**
    
    ![在这里插入图片描述][6a0ca7f6db23494da7b187765b4b5eed.png_pic_center]
    
    如果查询group by的时候，取消掉排序则可以执行 order by null禁止排序
    
    SELECT area\_name, COUNT(\*) FROM stb\_area GROUP BY `code` //优化前
    
    SELECT area\_name, COUNT(\*) FROM stb\_area GROUP BY `code` ORDER BY NULL; //优化后
    
    1.  嵌套查询-子查询
    
    使用子查询时，尽量可以改为join查询
    
    1.  LIMIT：分页查询
    
    在进行分页查询时，越晚后面查询的越慢，所以当查询的数据量较大时，可以采用子查询，先查询出id，在通过id进行条件查询，（十万条以上）、
    
    优化前
    
    `SELECT * FROM stb_area LIMIT 940,10`
    
    优化后
    
    `SELECT * FROM stb_area s1 JOIN (SELECT id FROM stb_area ORDER BY id LIMIT 940,10) s2 ON s1.id=s2.id`
    
    当查询10万条数据时，可以会不走索引，可以拆分为一百次查询，每次查1000条，查询方案
    
    在业务层使用多线程执行，然后再使用countDownLatch进行整合
    
    countdownlatch：同步工具类
    
    #### Trace工具 ####
    
    ![在这里插入图片描述][30ff3fdc9bc64fc3aa8fa7aa456f2b98.png_pic_center]

[bb9b1496c5ff4fdfa31b0f883428929b.png_pic_center]: https://img-blog.csdnimg.cn/bb9b1496c5ff4fdfa31b0f883428929b.png#pic_center
[d250e8ae7f2449c89c6cfc8dca9bf6bf.png_pic_center]: https://img-blog.csdnimg.cn/d250e8ae7f2449c89c6cfc8dca9bf6bf.png#pic_center
[c1a6f4558bed49d080253ffe54e9852d.png_pic_center]: https://img-blog.csdnimg.cn/c1a6f4558bed49d080253ffe54e9852d.png#pic_center
[2c3943780301463eb3f4ba8fd9e6bf7f.png_pic_center]: https://img-blog.csdnimg.cn/2c3943780301463eb3f4ba8fd9e6bf7f.png#pic_center
[f8d43fa68cfe4f40a3a6d1690290b5bd.png_pic_center]: https://img-blog.csdnimg.cn/f8d43fa68cfe4f40a3a6d1690290b5bd.png#pic_center
[5ba74ab27b3142cfa9710e53623aa84a.png_pic_center]: https://img-blog.csdnimg.cn/5ba74ab27b3142cfa9710e53623aa84a.png#pic_center
[f165fd23ccfe483fb4306ecc8da6df13.png_pic_center]: https://img-blog.csdnimg.cn/f165fd23ccfe483fb4306ecc8da6df13.png#pic_center
[d1dd026de7b342a18eae2ad1445e2e7e.png_pic_center]: https://img-blog.csdnimg.cn/d1dd026de7b342a18eae2ad1445e2e7e.png#pic_center
[0dbd6c2112cd4eedbdad5b7156160f59.png_pic_center]: https://img-blog.csdnimg.cn/0dbd6c2112cd4eedbdad5b7156160f59.png#pic_center
[3f9c8e4635194e55b3d42a3e1990cd4b.png_pic_center]: https://img-blog.csdnimg.cn/3f9c8e4635194e55b3d42a3e1990cd4b.png#pic_center
[fd37395fcae04d2f85b3ec682bf8ce25.png_pic_center]: https://img-blog.csdnimg.cn/fd37395fcae04d2f85b3ec682bf8ce25.png#pic_center
[1c81037c048946a19a7dfd52af993ea2.png_pic_center]: https://img-blog.csdnimg.cn/1c81037c048946a19a7dfd52af993ea2.png#pic_center
[6a0ca7f6db23494da7b187765b4b5eed.png_pic_center]: https://img-blog.csdnimg.cn/6a0ca7f6db23494da7b187765b4b5eed.png#pic_center
[30ff3fdc9bc64fc3aa8fa7aa456f2b98.png_pic_center]: https://img-blog.csdnimg.cn/30ff3fdc9bc64fc3aa8fa7aa456f2b98.png#pic_center