⑩③【MySQL】详解SQL优化

逃离我推掉我的手 2024-02-18 11:58 11阅读 0赞

![在这里插入图片描述][8d8e0d8a542946af858489a536162608.gif_pic_center]

> 个人简介：Java领域新星创作者；阿里云技术博主、星级博主、专家博主；正在Java学习的路上摸爬滚打，记录学习的过程~  
> 个人主页：[.29.的博客][.29.]  
> 学习社区：[进去逛一逛~][Link 1]

> [![在这里插入图片描述][ff95d349de5a4059b4211448c7f4551d.png_pic_center]][ff95d349de5a4059b4211448c7f4551d.png_pic_center 1]

#### SQL优化 ####

*  ⑩③【MySQL】了解并掌握SQL优化
 *   *  1. 插入数据 优化
     *  2. 主键优化
     *  3. order by 排序优化
     *  4. group by 分组优化
     *  5. limit 分页优化
     *  6. count 优化
     *  7. update 更新优化

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

## ⑩③【MySQL】了解并掌握SQL优化 ##

### 1. 插入数据 优化 ###

`insert优化`：

*  ⚪使用**批量插入**
 *   *  ![在这里插入图片描述][4c6fede1913f420985fdc5fb0e3eab2a.png]
     *  
 *  ⚪**手动提交事务**（每次SQL语句执行后事务自动提交，手动提交避免了多次提交，提升效率）
 *   *  ![在这里插入图片描述][03ab265eb54148a9b67519f2862c90ff.png]
     *  
 *  ⚪使用**主键顺序插入**（顺序比乱序速度更快，性能更高）
 *   *  ![在这里插入图片描述][dfe6d8be1626400e93d12ede661cab46.png]
     *

`大批量插入数据`：

如果一次性需要插入大批量数据，使用**insert语句插入性能较低**，此时可以使用MySQL数据库提供的**load指令进行插入**。操作如下：

# （命令行）客户端连接数据库时，加上参数： --local-infile
    mysql --local-infile -u root -p

-- 查看从本地加载文件导入数据的开关是否开启
    select @@local_infile;
    
    -- 设置全局参数local_infile为1，表示开启从本地加载文件导入数据的开关。
    set global local_infile=1;
    
    -- 执行load指令将准备好的数据，加载到表结构中
    -- 加载文件: /root/sql.log 中的数据插入表
    -- 字段间使用 逗号',' 分隔
    -- 行间使用 换行'\n' 分隔
    load data local infile '/root/sql.log' into table `表名` fields terminated by ',' lines terminated by '\n';

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

### 2. 主键优化 ###

`数据组织方式`：

*  在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为**索引组织表(index organized table 简称`IOT`)。**

`页分裂`：

*  页可以为空，也可以填充一半，也可以填充100%。每个页包含了至少2行数据（如果一行数据多大，会行溢出)，根据主键排列。
 *  > ![在这里插入图片描述][6f571bf0a7bd4d36bd08e31e4e15e5cd.png]
    > 
    > 分裂后插入↓
    > 
    > ![在这里插入图片描述][04f506d72c8742aa9e49665f94df05ab.png]
    > 
    > 重新设置指针↓
    > 
    > ![在这里插入图片描述][eb88b4cb132d409d9b1cf2cdd5195eb5.png]

`页合并`：

*  当删除一行记录时，实际上记录并没有被物理删除，只是记录被标记(flaged)为删除并且它的空间变得允许被其他记录声明使用。
 *  当页中删除的记录达到**MERGE\_THRESHOLD(默认为页的50%)**，InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个**页合并以优化空间使用**。
 *  > ![在这里插入图片描述][fc33f75dc7b745bdbacea621e6210344.png]
    > 
    > 合并↓
    > 
    > ![在这里插入图片描述][95fc6de22a424982b5f0d755ae9e28b1.png]

`主键设计原则`：

*  主键设计原则：
 *   *  ①在满足业务需求的情况下，**尽量降低主键的长度**。
     *  ②插入数据时，**尽量选择顺序插入，选择使用`AUTO_INCREMENT`自增主键。**
     *  ③**尽量不要使用UUID做为主键**或者作为其他自然主键，如身份证号。
     *  ④在业务操作时，**尽量避免对主键的修改**。

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

### 3. order by 排序优化 ###

`order by 优化`：

①. `Using filesort` : 通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。

②. `Using index` : 通过有序索引顺序扫描直接返回有序数据，这种情况即为using index,.不需要额外排序，操作效率高。

排序效率：Using index > Using filesort

*  **order by优化策略:**
 *   *  **①根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。**
     *   *      -- 没有建立索引时，排序性能为：`Using filesort`
                explain select id,age,phone from tb_user order by age,phone;
                
                -- 为排序字段建立合适索引
                create index idx_age_phone_aa on tb_user(age,phone);
                -- 等价于：
                create index idx_age_phone_aa on tb_user(age asc,phone asc);
                -- 建立索引后，排序性能提升为：`Using index`
     *  **②尽量使用覆盖索引，非覆盖索引需要回表查询，会从Using index 变为 Using filesort。**
     *  **③多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC\\DESC)。**
     *   *      -- 一个升序一个降序
                select id,age,phone from tb_user order by age asc,phone desc;
                
                -- 注意联合索引在创建时的规则
                create index idx_age_phone_ad on tb_user(age asc,phone desc);
         *  
     *  ④**如果不可避免地出现filesort，大数据量排序时，可以适当增大排序缓冲区`sort_buffer_size`的大小（默认256k）。**
     *   *      -- 查看参数sort_buffer_size大小
                show variables like 'sort_buffer_size';
                
                -- 设置参数sort_buffer_size大小
                set sort_buffer_size=自定义的大小;
         *

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

### 4. group by 分组优化 ###

*  根据分组字段建立合适的索引来提高效率。
 *  分组操作时，多字段通过联合索引排序也是遵循最左前缀法则的。
 *   *      -- 如何建立合适索引：建议使用联合索引，可参考上文的order by优化
     *

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

### 5. limit 分页优化 ###

一个常见又非常头疼的问题就是大数据量的分页，如：`limit2000000,10`,此时需要MySQL排序前2000010记录，仅仅返回2000000到2000010的记录，其他记录丢弃，查询排序的代价非常大。

*  `优化策略`：
 *   *  一般分页查询时，通过建立`覆盖索引`能够较好提升性能，可通过`覆盖索引+子查询`形式进行优化。
     *      -- 优化前：
            select * from tb_sku limit 2000000,10;
            
            -- 优化后
            -- 子查询的id字段存在主键索引，order by性能得到优化
            -- 根据子查询的到的主键字段id进行查询，效率高。
            select s.* from 
            tb_sku s,(select id from tb_sku order by id limit 2000000,10) i
            where s.id = i.id;
     *

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

### 6. count 优化 ###

`count()`：

*  count()是一个聚合函数，对于返回的结果集，一行行地判断，**如果count()函数的参数不是NULL,累计值就加1，否则不加**，最后返回累计值。（只记录不为NULL的记录）
 *  \*\*用法：\*\*count(\*)、count(主键)、count(字段)、count(1)
 *   *  ①`count(*)`
     *   *  InnoDB引擎并不会把全部字段取出来，而是**专门做了优化**，**不取值**，服务层**直接按行进行累加**。
         *  
     *  ②`count(主键)`
     *   *  InnoDB引擎会遍历整张表，把每一行的主键id值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加（主键不可能为NULL)。
         *  
     *  ③`count(字段)`
     *   *  \*\*没有not null约束：\*\*InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为null,不为null,计数累加。
         *  \*\*有not null约束：\*\*InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
         *  
     *  ④`count(1)`
     *   *  lnnoDB引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
         *  
 *  
 *  ![在这里插入图片描述][fd11fbca08a048199534fba3adcb3c7c.png]

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

### 7. update 更新优化 ###

`需要优化的问题`：

*  InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，更新没有索引的记录或索引失效，使用的锁会从行锁变为表锁。
 *  使用表锁会使并发性能下降，所以应当经可能去更新 使用了索引的字段。

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

[![在这里插入图片描述][20a7f1b58dfb4660b75d7f021c157d57.png_pic_center]][20a7f1b58dfb4660b75d7f021c157d57.png_pic_center 1]

[8d8e0d8a542946af858489a536162608.gif_pic_center]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/83681b08116848458b8200fdcfb71293.png
[.29.]: https://blog.csdn.net/ebb29bbe?spm=1018.2226.3001.5343
[Link 1]: https://bbs.csdn.net/forums/185241f773fb401b8ca7994dc3d02333?joinKey=cyru4ex9o7wq-mz6v1koj5x-1-add3ae8dc3827dbbd4360c79af92bbbe
[ff95d349de5a4059b4211448c7f4551d.png_pic_center]: https://img-blog.csdnimg.cn/ff95d349de5a4059b4211448c7f4551d.png#pic_center
[ff95d349de5a4059b4211448c7f4551d.png_pic_center 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/ae1f62831d9d475eafc0084b6ace3cd6.png
[4c6fede1913f420985fdc5fb0e3eab2a.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/2a6e780bf82e4da3b326f09f41f6ccc1.png
[03ab265eb54148a9b67519f2862c90ff.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/710c480364ea41259250b72d185e899b.png
[dfe6d8be1626400e93d12ede661cab46.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/f3956e9e18994c8787d4cc6bda3837f7.png
[6f571bf0a7bd4d36bd08e31e4e15e5cd.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/f9126c089bd64d66bc3279b7a54ce7a4.png
[04f506d72c8742aa9e49665f94df05ab.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/89392b1590b74d3abd425be7f7e51524.png
[eb88b4cb132d409d9b1cf2cdd5195eb5.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/36c92f9ad84f4261b2d19c9eb5c79919.png
[fc33f75dc7b745bdbacea621e6210344.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/3da8b0c46b78487daa9a67ac15d2c27f.png
[95fc6de22a424982b5f0d755ae9e28b1.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/a895de035446492c94a1b74091d55082.png
[fd11fbca08a048199534fba3adcb3c7c.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/da6e3cc5953f4b3b8e4a987c4fa5ae6f.png
[20a7f1b58dfb4660b75d7f021c157d57.png_pic_center]: https://img-blog.csdnimg.cn/20a7f1b58dfb4660b75d7f021c157d57.png#pic_center
[20a7f1b58dfb4660b75d7f021c157d57.png_pic_center 1]: https://image.dandelioncloud.cn/pgy_files/images/2024/02/18/6204d34fd95a45e38849b3978ac341e2.png