【MySQL 数据库】7、SQL 优化

「爱情、让人受尽委屈。」 2023-10-12 13:20 65阅读 0赞

#### 目录 ####

*  一、插入数据优化
 *   *  (1) insert 语句
     *   *  ① 批量插入数据
         *  ② 手动控制事务
         *  ③ 主键顺序插入，性能要高于乱序插入
     *  (2) load 大批量插入数据【☆❀
 *  二、主键优化
 *   *  (1) 数据组织形式
     *  (2) 页分裂
     *  (3) 页合并
     *  (4) 主键设计原则
 *  三、orber by 优化
 *  四、group by 优化
 *  五、limit 优化（分页）
 *  六、count 优化
 *  七、update 优化

## 一、插入数据优化 ##

### (1) insert 语句 ###

#### ① 批量插入数据 ####

insert into tb_test values(1, 'Tom'), (2, 'Cat'), (3, 'Jerry');

#### ② 手动控制事务 ####

start transaction;
    insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
    insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry');
    insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry');
    commit;

#### ③ 主键顺序插入，性能要高于乱序插入 ####

> 主键乱序插入 : 8 1 9 21 88 2 4 15 89 5 7 3  
> **主键顺序插入 : 1 2 3 4 5 7 8 9 15 21 88 89**【☆】

### (2) load 大批量插入数据【☆❀ ###

】

> ① 如果需要一次性插入大批量数据（百万级别），使用 insert 语句插入性能**很低**  
> ② 可使用 MySQL 数据库提供的 `load` 指令进行插入

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

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

可执行如下指令，将数据脚本文件中的数据加载到表结构中：

-- 客户端连接服务端时，加上参数 -–local-infile
    mysql –-local-infile -u root -p
    
    -- 设置全局参数local_infile为1，开启从本地加载文件导入数据的开关
    set global local_infile = 1;
    
    -- 执行load指令将准备好的数据，加载到表结构中
    load data local infile '/root/sql1.log' into table tb_user fields
    terminated by ',' lines terminated by '\n' ;

## 二、主键优化 ##

`主键顺序插入的性能是要高于乱序插入的, 为什么？`

### (1) 数据组织形式 ###

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

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

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

> 在 InnoDB 存储引擎中，数据行是记录在逻辑结构 **page 页**中的，而每一个页的大小是固定的，默认16K。那也就意味着， 一个页中所存储的行也是有限的，如果插入的数据行 row 在该页存储不小，将会存储到下一个页中，页与页之间会通过**指针**连接。

### (2) 页分裂 ###

*  页可以为空，也可以填充一半，也可以填充100%
 *  每个页包含了 `2-N` 行数据
 *  如果一行数据过大，会行溢出，**根据主键排列**

![在这里插入图片描述][567d3a666b2140d49787ddfbe2cf006a.png]  
![在这里插入图片描述][02df91beae824c099cd500406a437673.png]

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

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

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

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

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

### (3) 页合并 ###

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

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

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

### (4) 主键设计原则 ###

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

## 三、orber by 优化 ##

*  MySQL的排序，有**两**种方式：
 *  Using filesort : 全表扫描，读取满足条件的数据行，然后在**排序缓冲区 sort buffer** 中完成排序操作。所有**不是通过索引直接返回排序结果的排序都叫 FileSort 排序**。
 *  Using index : 通过**有序索引**顺序扫描**直接返回有序数据**，这种情况即为 using index，不需要额外排序，操作效率高。
 *  Using index 的性能高，而 Using filesort 性能低

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

*  根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
 *  尽量使用覆盖索引。
 *  多字段排序, 一个升序一个降序，此时需要注意联合索引在创建时的规则（ASC/DESC）。
 *  如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小 sort\_buffer\_size(默认256k)。

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

## 四、group by 优化 ##

*  在分组操作时，可以通过索引来提高效率。
 *  分组操作时，索引的使用也是满足最左前缀法则的

## 五、limit 优化（分页） ##

*  在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。
 *  在进行分页查询时，如果执行`limit 2000000,10` ，此时需要MySQL排序前2000010 条记录，仅仅返回 `2000000 - 2000010`的记录，其他记录丢弃，查询**排序**的代价非常大

优化思路: 一般分页查询时，通过创建 `覆盖索引` 能够比较好地提高性能，可以**通过覆盖索引加子查询（多表联查）形式进行优化**

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

## 六、count 优化 ##

*  `count()` 是一个聚合函数：对于返回的结果集，一行行地判断，如果 `count 函数`的参数不是 **NULL**，累计值就加 1，否则不加，最后返回累计值。

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

用法：

*  count（\*）
 *  count（主键）
 *  count（字段）
 *  count（数字）

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

## 七、update 优化 ##

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

InnoDB 的行锁是针对**索引**加的锁，不是针对记录加的锁。并且该索引不能失效，否则会从行锁升级为表锁

[4464ca9e84ec4d2796bef04b1f2fa1e1.png]: https://img-blog.csdnimg.cn/4464ca9e84ec4d2796bef04b1f2fa1e1.png
[a7c58f89c3d9406bb3132b4d1571eba7.png]: https://img-blog.csdnimg.cn/a7c58f89c3d9406bb3132b4d1571eba7.png
[18e2a6257b614c3eb39cf6bfffd73b24.png]: https://img-blog.csdnimg.cn/18e2a6257b614c3eb39cf6bfffd73b24.png
[567d3a666b2140d49787ddfbe2cf006a.png]: https://img-blog.csdnimg.cn/567d3a666b2140d49787ddfbe2cf006a.png
[02df91beae824c099cd500406a437673.png]: https://img-blog.csdnimg.cn/02df91beae824c099cd500406a437673.png
[74b45720279b4ce298867a5d870f02a9.png]: https://img-blog.csdnimg.cn/74b45720279b4ce298867a5d870f02a9.png
[b8a88cb5e85d4604abb638293399198f.png]: https://img-blog.csdnimg.cn/b8a88cb5e85d4604abb638293399198f.png
[168344b14d754e738839f0e330c74f62.png]: https://img-blog.csdnimg.cn/168344b14d754e738839f0e330c74f62.png
[5883e4ae104f43f2aac889967852be50.png]: https://img-blog.csdnimg.cn/5883e4ae104f43f2aac889967852be50.png
[a27c75ea403341c79210184939671508.png]: https://img-blog.csdnimg.cn/a27c75ea403341c79210184939671508.png
[3f8c0424c1ce4921926a26572599201d.png]: https://img-blog.csdnimg.cn/3f8c0424c1ce4921926a26572599201d.png
[76177efbb0834c0d89a028b6a93e7906.png]: https://img-blog.csdnimg.cn/76177efbb0834c0d89a028b6a93e7906.png
[649281f555aa40b78e7b07c7769c38e7.png]: https://img-blog.csdnimg.cn/649281f555aa40b78e7b07c7769c38e7.png
[7e48d940a1044bd89995cc357ea4f1d8.png]: https://img-blog.csdnimg.cn/7e48d940a1044bd89995cc357ea4f1d8.png
[481c25a0c66440b28fa7128f63e7c4b2.png]: https://img-blog.csdnimg.cn/481c25a0c66440b28fa7128f63e7c4b2.png
[7896d70e85214c54b474389ddce2f629.png]: https://img-blog.csdnimg.cn/7896d70e85214c54b474389ddce2f629.png