一文搞懂SQL优化——如何高效添加数据

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
云数据库 Redis 版,社区版 2GB
推荐场景:
搭建游戏排行榜
简介: **SQL优化关键点:**1. **批量插入**提高效率,一次性建议不超过500条。2. **手动事务**减少开销,多条插入语句用一个事务。3. **主键顺序插入**避免页分裂,提升性能。4. **使用`LOAD DATA INFILE`**大批量导入快速。5. **避免主键乱序**,减少不必要的磁盘操作。6. **选择合适主键类型**,避免UUID或长主键导致的性能问题。7. **避免主键修改**,保持索引稳定。这些技巧能优化数据库操作,提升系统性能。

一、SQL优化

1.高效添加数据的几种方式

普通插入(Insert语句)

先回顾一下向数据库中添加数据的基本操作:

当我们想要向数据库中的表tb中插入一条数据时,可以采用insert into语句:

insert into tb values(1,'value1');

当我们想要向数据库插入多条数据时,可以执行多条insert into语句:

insert into tb1 values(1,'value1');
insert into tb2 values(2,'value2');
insert into tb3 values(3,'value3');
.....

但是当想插入数据很多时,行数会非常密集,而且代码要多次请求数据库,每次请求都会消耗一定的性能,要怎样进行优化呢?

优化方案1:批量插入

  • 一般情况下都采用批量插入来使得添加数据更高效
  • 批量插入的思想就是把多行数据压缩成一行,只需要远程请求一次数据库,且代码更加简洁
  • 但是一次性批量插入的数据建议控制在500条之内,如果多于500条,则应该分多个批次处理
Insert into tb values(1,'value1'),(2,'value2'),(3,'value3');

优化方案2:手动控制事务

  • 通过手动控制事务添加数据有多种好处
  • 一般情况下,MySQL自动为每条插入语句创建一个事务,这样可能会导致大量的日志记录,从而降低系统性能。通过手动控制事务,可以将多条执行单元合并为一个事务,从而避免了多个事务的开销。
  • 手动控制事务可以帮助我们保证数据的完整性和一致性。

反例:

Insert into tb values(1,'value1'),(2,'value2'),(3,'value3');
Insert into tb values(4,'value1'),(5,'value2'),(6,'value3');
Insert into tb values(7,'value1'),(8,'value2'),(9,'value3');

正例:

start transaction;
Insert into tb values(1,'value1'),(2,'value2'),(3,'value3');
Insert into tb values(4,'value1'),(5,'value2'),(6,'value3');
Insert into tb values(7,'value1'),(8,'value2'),(9,'value3');
commit;

优化方案3:主键顺序插入

  • 不管数据量如何,推荐采用主键顺序插入来添加数据。
  • 主键顺序插入,性能要高于乱序插入。原理见后续的主键优化部分。
#主键乱序插入 : 6 2 9 7 2  
#主键顺序插入 : 1 2 4 6 8

优化方案4:load指令添加数据

如果一次性需要插入大批量数据(比如几百万的记录),使用insert语句可能需要花费几十分钟,此时可以使用MySQL数据库提供的load指令,这个过程只需要花费几十秒。

如何采用load指令大批量添加数据?

  • 如果是常规的连接数据库,只需要输入以下指令:
mysql -u root -p
  • 如果需要用load指令,需要额外添加-local-infile参数:
mysql –-local-infile -u root -p
  • 设置全局参数local_infile为1,开启从本地加载文件导入数据的开关:
set global local_infile = 1;
  • 我们也可以事先通过以下指令来查看local_infile全局参数是否开启:
  • 如果local_infile显示为0,则表示开关并未开启,则需要手动设置为1。
select @@local_infile;
  • 结果显示如下:

image-20240327211844684.png

举个栗子:

  • 假设要上传100万条数据,要上传的文件路径是'/root/load_user_100w_sort.sql',则往表tb1中添加数据的完整load指令是:
load data local infile '/root/load_user_100w_sort.sql' into table tb1 fields terminated by ',' lines terminated by '\n' ;
  • 其中,
  • load data local infile是固定格式;
  • into table tb1表示向表tb1添加数据;
  • fields terminated by ','表示每一个字段之间采用逗号分割;
  • lines terminated by '\n'表示每一行之间采用换行符分割。

2.主键优化的原理

为什么主键顺序插入的性能要大于乱序插入?

  • 首先了解在InnoDB存储引擎中数据的组织方式:在InnoDB存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表
  • 在索引组织表中,页是InnoDB磁盘管理的最小单元,其固定大小为16K。页可以为空,也可以填充一半,也可以填充满。每个页至少包含2行数据(如果只包含一行数据则是链表结构;如果一行数据过大超过阈值会导致行溢出),根据主键排列。

image-20240327215053009.png

页分裂与页合并现象

主键顺序添加数据时的过程是怎样的?

  • 主键顺序插入,就是先在第一个页中填写数据,如果第一个页满了就写第二个页中,依此类推
  • 从磁盘中申请一页,主键按照顺序进行插入

image-20240327215640423.png

  • 当第一页存满之后,会继续申请第二页,页与页之间通过双向指针进行连接;当第二页也存满,就会申请第三页;
    image-20240327220002356.png

主键乱序添加数据时的过程是怎样的?

  • 主键乱序插入,不是依此往后插入,因为叶子节点主键之间是有序的,所以就产生了页分裂现象
  • 假设1页和2页都已经存满,而此时主键50想要插入,不会直接写入新的页,因为索引结构的叶子节点是有顺序的。
  • 按照顺序,主键50应该存储在47之后。

image-20240327220908962.png

image-20240327220914166.png

  • 但1页显然已经存满,随后会进行“页分裂”的过程,即先开启第三页,然后将1页后一半的数据,移动到3页,然后在3页中插入50。

image-20240327221552343.png

  • 最后2#和3#两页位置互换,以符合主键排序规则。最终过程如下:

image-20240327221635696.png

与页分裂相对,还有页合并现象:

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

image-20240327222057379.png

当页中删除的记录达到 MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。

image-20240327222304251.png

最后2#中被标记的数据删除,同时2#和3#进行合并:

image-20240327223818964.png

3.总结

  • 插入多条数据时,尽量选择批量插入
    • 因为批量插入只需要远程请求一次数据库,且代码更加简洁
  • 插入多条数据时,尽量选择手动控制事务插入
    • 因为通过手动控制事务,可以将多条执行单元合并为一个事务,从免了多个事务的开销,同时保证数据的完整性和一致性。
  • 插入大量数据时,选择MySQL提供的load指令插入的效率要大于Insert语句
  • 插入数据时,尽量选择主键顺序插入,选择使用AUTO_INCREMENT自增主键。
    • 因为当主键乱序插入时,会产生“页分裂”,消耗性能
  • 尽量不要使用UUID做主键或者是其他自然主键,如身份证号。
    • 因为每次生成的UUID之间无序,插入时为主键乱序插入,会产生“页分裂”,消耗性能
  • 业务操作时,避免对主键的修改。
    • 因为修改主键后还需对索引结构进行修改,花费代价较大。
  • 满足业务需求的情况下,尽量降低主键的长度。
相关实践学习
基于CentOS快速搭建LAMP环境
本教程介绍如何搭建LAMP环境,其中LAMP分别代表Linux、Apache、MySQL和PHP。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
7天前
|
SQL 关系型数据库 MySQL
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
MYSQL根据查询结果删除sql 去除重复id 新增对比前一条与后一条数据 去重3种方法​ 窗口函数
|
8天前
|
SQL 存储 关系型数据库
【MySQL】SQL 优化
【MySQL】SQL 优化
28 0
|
20小时前
|
SQL Oracle 关系型数据库
实时计算 Flink版产品使用合集之如何SQL同步数据到Oracle数据库中
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
22小时前
|
SQL 消息中间件 关系型数据库
实时计算 Flink版产品使用合集之 sql采集mysql能拿到before的数据吗
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStreamAPI、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
|
1天前
|
SQL 关系型数据库 MySQL
简简单单 My SQL 学习笔记(2)——分组和简单数据的查询
简简单单 My SQL 学习笔记(2)——分组和简单数据的查询
|
1天前
|
SQL 关系型数据库 MySQL
简简单单 My SQL 学习笔记(1)——表中数据的整删改查
简简单单 My SQL 学习笔记(1)——表中数据的整删改查
|
5天前
|
网络安全 流计算 Python
实时计算 Flink版操作报错合集之Flink sql-client 针对kafka的protobuf格式数据建表,报错:java.lang.ClassNotFoundException 如何解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
17 1
|
5天前
|
SQL Java 关系型数据库
实时计算 Flink版操作报错合集之通过flink sql形式同步数据到hudi中,本地启动mian方法报错如何解决
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
24 8
|
6天前
|
SQL 流计算 API
实时计算 Flink版产品使用合集之ClickHouse-JDBC 写入数据时,发现写入的目标表名称与 PreparedStatement 中 SQL 的表名不一致如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
12 0
|
6天前
|
消息中间件 关系型数据库 网络安全
实时计算 Flink版操作报错合集之Flink sql-client 针对kafka的protobuf格式数据建表,报错:java.lang.ClassNotFoundException 如何解决
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
18 1