前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >SQL优化——如何高效添加数据

SQL优化——如何高效添加数据

原创
作者头像
changwoo
修改2024-03-29 07:41:54
1.5K0
修改2024-03-29 07:41:54
举报
文章被收录于专栏:MySQL系列

一、SQL优化

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

普通插入(Insert语句)

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

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

代码语言:sql
复制
insert into tb values(1,'value1');

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

代码语言:sql
复制
insert into tb1 values(1,'value1');
insert into tb2 values(2,'value2');
insert into tb3 values(3,'value3');
.....

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

优化方案1:批量插入

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

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

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

反例:

代码语言:sql
复制
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');

正例:

代码语言:sql
复制
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:主键顺序插入

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

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

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

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

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

举个栗子:

  • 假设要上传100万条数据,要上传的文件路径是'/root/load_user_100w_sort.sql',则往表tb1中添加数据的完整load指令是:
代码语言:sql
复制
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行数据(如果只包含一行数据则是链表结构;如果一行数据过大超过阈值会导致行溢出),根据主键排列。

页分裂与页合并现象

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

  • 主键顺序插入,就是先在第一个页中填写数据,如果第一个页满了就写第二个页中,依此类推
  • 从磁盘中申请一页,主键按照顺序进行插入
  • 当第一页存满之后,会继续申请第二页,页与页之间通过双向指针进行连接;当第二页也存满,就会申请第三页; 主键乱序添加数据时的过程是怎样的?
  • 主键乱序插入,不是依此往后插入,因为叶子节点主键之间是有序的,所以就产生了页分裂现象
  • 假设1页和2页都已经存满,而此时主键50想要插入,不会直接写入新的页,因为索引结构的叶子节点是有顺序的。
  • 按照顺序,主键50应该存储在47之后。
  • 但1页显然已经存满,随后会进行“页分裂”的过程,即先开启第三页,然后将1页后一半的数据,移动到3页,然后在3页中插入50。
  • 最后2#和3#两页位置互换,以符合主键排序规则。最终过程如下:

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

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

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

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

3.总结

  • 插入多条数据时,尽量选择批量插入
    • 因为批量插入只需要远程请求一次数据库,且代码更加简洁
  • 插入多条数据时,尽量选择手动控制事务插入
    • 因为通过手动控制事务,可以将多条执行单元合并为一个事务,从免了多个事务的开销,同时保证数据的完整性和一致性。
  • 插入大量数据时,选择MySQL提供的load指令插入的效率要大于Insert语句
  • 插入数据时,尽量选择主键顺序插入,选择使用AUTO_INCREMENT自增主键。
    • 因为当主键乱序插入时,会产生“页分裂”,消耗性能
  • 尽量不要使用UUID做主键或者是其他自然主键,如身份证号。
    • 因为每次生成的UUID之间无序,插入时为主键乱序插入,会产生“页分裂”,消耗性能
  • 业务操作时,避免对主键的修改。
    • 因为修改主键后还需对索引结构进行修改,花费代价较大。
  • 满足业务需求的情况下,尽量降低主键的长度。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。

如有侵权,请联系 cloudcommunity@tencent.com 删除。

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 一、SQL优化
    • 1.高效添加数据的几种方式
      • 2.主键优化的原理
        • 3.总结
        相关产品与服务
        云数据库 MySQL
        腾讯云数据库 MySQL(TencentDB for MySQL)为用户提供安全可靠,性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性,包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL,可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理,不仅经济实惠,而且稳定可靠,易于运维。
        领券
        问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档