常见的SQL优化方案

Dear 丶 2023-10-12 21:14 38阅读 0赞

### 1. insert优化 ###

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

#### 1.1 批量插入 ####

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

我们之前插入数据都是一条一条插入的，会导致频繁操作数据库，从而影响性能。比如遍历某个集合，然后逐个使用insert语句插入数据库表中

INSERT INTO table (name, age) VALUES ('张三', 18);
    INSERT INTO table (name, age) VALUES ('李四', 19);
    INSERT INTO table (name, age) VALUES ('王五', 20);

解决方案：可以使用批量插入，即一条insert语句插入多条数据（逗号拼接的方式）

INSERT INTO table (name, age) VALUES ('张三', 18), ('李四', 19), ('王五', 20);

注意事件：

*  插入的数据不建议超过1000条；
 *  如果插入大量数据，可以将数据分成多个批次进行插入，每个批次插入一定数量的数据。

> 示例：使用SpringBoot+MybatisPlus进行大批量插入数据：

#### 1.2 手动提交事务 ####

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

> MySQL默认自动提交事务。

> 手动提交事务好处：将多个 INSERT 操作放在一个事务中执行，可以提供更好的性能和数据一致性。事务会把多个 INSERT 操作合并为一个原子操作，减少了磁盘 I/O 和日志刷新的开销。

# 执行insert语句之前开启事务
    start transaction;
    
    # 插入数据
    INSERT INTO table (name, age) VALUES ('张三', 18);
    INSERT INTO table (name, age) VALUES ('李四', 19);
    INSERT INTO table (name, age) VALUES ('王五', 20);
    
    # insert语句都执行完毕后,统一提交事务
    commit;

#### 1.3 大批量插入数据 ####

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

如果一次性需要插入大批量数据，使用insert语句插入性能就比较较低了，我们可以使用MySQL数据库提供的load指令进行插入。

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

使用load指令操作如下：

-- 客户端连接服务端时，加上参数 -–local-infile
    mysql –-local-infile -u root -p123456 
    
    -- 设置全局参数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' ;

load指令参数详解：

*  `load data local infile`：加载本地文件
 *  `/root/sql1.log`：表示磁盘文件
 *  `into table tb_user`：导入到指定的数据库表结构中
 *  `fields terminated by ','`：表示每个字段使用逗号分隔
 *  `lines terminated by '\n'` ：每一行数据使用\\n分隔

注意：使用load指令大批量插入数据的时候，也要按照主键的顺序进行插入，因为主键顺序插入的性能要高于乱序插入的。

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

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

#### 2.1 数据组织方式 ####

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

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

InnoDB存储引擎的索引分为：聚集索引（Clustered Index） 和 二级索引（Secondary Index）。

聚集索引他的叶子节点下面挂的是这一行的行数据，对于聚集索引来说，一张表默认主键索引是聚集索引，所以表中数据在存放的时候按照主键顺序存放。

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

InnoDB 存储引擎选择使用 B+ 树索引结构是基于以下几个原因：

1.  聚集索引的需求：InnoDB 存储引擎要求表必须有一个聚集索引，该聚集索引决定了数据在磁盘上的物理存储顺序。B+ 树索引结构非常适合用于构建聚集索引，因为 B+ 树的叶子节点是按照顺序存储的，可以加快范围查询和顺序访问的性能。
2.  结构简洁高效：B+ 树是一种平衡树数据结构，能够在有序的情况下保持较好的性能。它相对于其他平衡树（如 B 树）来说，更适合作为数据库索引的数据结构。B+ 树具有良好的平衡性和自平衡性，可以有效地支持索引的插入、删除和查找操作。
3.  支持范围查询：B+ 树的叶子节点是通过链表进行连接的，可以很方便地进行范围扫描。在数据库查询中，范围查询操作非常常见，使用 B+ 树索引可以高效地支持这些操作。
4.  磁盘访问优化：B+ 树索引的叶子节点按照顺序存储在磁盘上，利于利用 I/O 预读机制。这意味着在查询过程中，相邻的磁盘块很可能被顺序读取到内存，提高了磁盘访问效率。

总的来说，B+ 树索引结构适合 InnoDB 存储引擎的需求，它能够提供高效的聚集索引以及辅助索引的支持，同时也符合数据库系统对于索引结构的要求，能够提供高性能的数据存取和查询操作。

InnnoDB逻辑存储结构：

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

#### 2.2 页分裂 ####

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

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

> 主键顺序插入：

1.  从磁盘中申请页， 主键顺序插入。

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

1.  如果第一个页没有满，会继续往第一页插入。

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

1.  当第一个页写满之后，再写入第二个页，页与页之间会通过指针连接（双向指针）

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

1.  当第二页写满了，再往第三页写入，以此类推。

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

> 主键乱序插入：

1.  当前两个页都已经写满了，此时再插入id为50的记录，他就不在顺序插入到第三个页。

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

1.  会将1\#页后一半的数据，移动到3\#页，然后在3\#页，顺序插入id为50的记录。

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

1.  那么此时，这三个页之间的数据顺序是有问题的。 1\#的下一个页，应该是3\#， 3\#的下一个页是2\#。 所以需要重新设置链表指针。

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

上述的这种现象，称之为 “页分裂”，是比较耗费性能的操作。

#### 2.3 页合并 ####

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

例如表中数据的索引结构(叶子节点)如下所示：

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

**当我们对已有数据进行删除时，具体的执行效果如下:**

当删除一行记录时，实际上记录并没有被物理删除，只是这行记录被标识为删除状态并且它的空间变得允许被其他记录声明使用。

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

当我们继续删除2\#页，id为13、14的记录

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

当页中删除的记录达到一定阈值 MERGE\_THRESHOLD（默认为页的50%），InnoDB会开始寻找最靠近的页（前或后）看看是否可以将两个页合并以优化空间使用。

*  MERGE\_THRESHOLD：合并页的阈值，默认为页的50%，可以自己设置，在创建表或者创建索引时指定。

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

当我们在删除数据并将页合并之后，再次插入新的数据21，直接插入3\#页中。

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

这里面所发生合并页的现象，称为 “页合并”。

#### 2.4 主键的设计原则 ####

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

*  满足业务需求的情况下，尽量降低主键的长度。（可以减少数据库存储和索引的成本，并提高性能）
 *  插入数据时，尽量选择顺序插入，选择使用AUTO\_INCREMENT自增主键。
 *  尽量不要使用UUID做主键或者是其他自然主键，如 身份证号的自然主键。
    
     *  使用UUID会存在乱序插入，会造成页分裂。
     *  UUID的长度较长，在检索的时候也会耗费大量的磁盘IO。
 *  业务操作时，尽量避免对主键的修改。

### 3. order by优化 ###

### 4. group by优化 ###

### 5. limit优化 ###

### 6. count优化 ###

### 7. update优化 ###

[a3fb7ca4fe8f417084cd7bb2251a4497.png]: https://img-blog.csdnimg.cn/a3fb7ca4fe8f417084cd7bb2251a4497.png
[a941a731f1c94eda9e01cc4b742eda49.png]: https://img-blog.csdnimg.cn/a941a731f1c94eda9e01cc4b742eda49.png
[232ad31e553b423faf48f19a2f450375.png]: https://img-blog.csdnimg.cn/232ad31e553b423faf48f19a2f450375.png
[7a567ce3bb9f4bb3916276038ecb0faa.png]: https://img-blog.csdnimg.cn/7a567ce3bb9f4bb3916276038ecb0faa.png
[a5e7e5604c88467196d73a08c9b6c624.png]: https://img-blog.csdnimg.cn/a5e7e5604c88467196d73a08c9b6c624.png
[ae8e6ab179114cb0b5e0175312454e77.png]: https://img-blog.csdnimg.cn/ae8e6ab179114cb0b5e0175312454e77.png
[dad60f5db52c4b07bc53bba3a9b1fe67.png]: https://img-blog.csdnimg.cn/dad60f5db52c4b07bc53bba3a9b1fe67.png
[2f40f4069886448eaf88370c16b2f680.png]: https://img-blog.csdnimg.cn/2f40f4069886448eaf88370c16b2f680.png
[27af687d691442b39e67b146c3611669.png]: https://img-blog.csdnimg.cn/27af687d691442b39e67b146c3611669.png
[4c9d96b6581348608d1be07f42e4f69b.png]: https://img-blog.csdnimg.cn/4c9d96b6581348608d1be07f42e4f69b.png
[4dd275c6acdf4fcaa094b76ce8a1e60f.png]: https://img-blog.csdnimg.cn/4dd275c6acdf4fcaa094b76ce8a1e60f.png
[898f205644454d46b5d00642bd54e527.png]: https://img-blog.csdnimg.cn/898f205644454d46b5d00642bd54e527.png
[c70d25df4f904be7ba40d786ff370fd8.png]: https://img-blog.csdnimg.cn/c70d25df4f904be7ba40d786ff370fd8.png
[8d8a76705edd4afbb7b5b765251c9b1a.png]: https://img-blog.csdnimg.cn/8d8a76705edd4afbb7b5b765251c9b1a.png
[38a32e8df02642458a04908ef447477c.png]: https://img-blog.csdnimg.cn/38a32e8df02642458a04908ef447477c.png