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

发布于：2024-10-24 编辑：匿名来源：网络

一、SQL优化 1、高效添加数据的几种方式普通插入（Insert语句）我们先回顾一下向数据库添加数据的基本操作：当我们要向数据库添加数据时数据库向表tb插入一条数据时，可以使用insert into语句：代码语言：sql copy insert into tb Values(1,'value1');当我们要向数据库中插入多条数据时，可以执行多个insert into语句：代码语言：sql copy insert into tb1 values(1,'value1');insert into tb2 values(2,'value2'); insert into tb3 values(3,'value3');....但是当你要插入大量数据时，行数会非常密集，代码需要多次请求数据库。每个请求都会消耗一定的性能。

如何优化呢？优化方案一：一般采用批量插入的方式，让添加数据更加高效。批量插入的思想是将多行数据压缩为一行。

只需要远程请求数据库一次，代码比较简洁，但可以一次性批量插入数据。建议控制在bar以内。

如果有多个条，您应该分多个批次处理代码。语言：sql 复制插入 tb 值(1,'value1'),(2,'value2'),(3,'value3');优化2：手动控制事务通过手动控制事务来添加数据有很多好处。

一般来说，MySQL会自动为每个insert语句创建一个事务，这可能会导致大量的日志记录，从而降低系统性能。通过手动控制事务，可以将多个执行单元组合成单个事务，从而避免多个事务的开销。

手动控制事务可以帮助我们保证数据的完整性和一致性。反例：代码语言：sql copy Insert into tb values(1,'value1'),(2,'value2'),(3,'value3');Insert into tb values(4,'value1'),(5 , 'value2'),(6,'value3');插入到tb中values(7,'value1'),(8,'value2'),(9,'value3');正例：代码语言：sql copy start transaction;Insert into tb values(1,'value1'),(2,'value2'),(3,'value3');Insert into tb values(4,'value1') ,(5,'value2'),(6,'value3');插入tb中values(7,'value1'),(8,'value2'),(9,'value3');commit;优化计划3 ：主键无论数据量多少都是顺序插入的。

建议使用主键顺序插入来添加数据。主键顺序插入的性能比乱序插入的性能要高。

原理请参见后续主键优化部分。代码语言：sql copy #主键乱序插入：6 2 9 7 2 #主键顺序插入：1 2 4 6 8 优化方案4：加载指令添加数据如果需要插入大量数据一次（如数百万条记录），使用insert语句可能需要几十分钟。

这时候就可以使用MySQL数据库提供的load命令了。这个过程只需要几十秒。

如何使用load命令大量添加数据？如果是常规连接数据库，则只需要输入以下指令：代码语言：sql copy mysql -u root -p 如果需要使用load命令，则需要额外添加-local-infile参数：代码语言：sql copy mysql --local-infile -u root -p 将全局参数local_infile设置为1，并打开从本地加载文件导入数据的开关：代码语言：sql copy set global local_infile = 1;我们还可以提前使用以下命令检查local_infile全局参数是否打开：如果local_infile显示为0，则说明开关没有打开，需要手动设置为1。代码语言：sql copy select @@local_infile;结果如下：例如：假设要上传10000条数据，上传的文件路径为“/root/load_user_w_sort.sql”，则将完整负载的数据添加到表tb1中。

代码语言： sql 将本地 infile '/root/load_user_w_sort.sql' 加载数据复制到表 tb1 中，字段以 ',' 结尾，行以 '\n' 结尾；其中load data local infile是固定格式； into table tb1 表示向表tb1添加数据；以“,”结尾的字段表示用逗号分隔各个字段；以“\n”结尾的行表示使用换行符来分隔每一行。 2、主键优化原理为什么主键顺序插入的性能比乱序插入的性能好？首先了解一下数据在InnoDB存储引擎中是如何组织的：在InnoDB存储引擎中，表数据是按照主键顺序组织和存储的。

采用这种存储方法的表称为索引组织表。在索引组织表中，页是InnoDB磁盘管理的最小单位，固定大小为16K。

页面可以是空的、半满的或全满的。每页至少包含2行数据（如果只包含一行数据，则为链表结构；如果一行数据太大超过阈值，会导致行溢出），按照主键。

页面拆分和页面合并时按主键顺序添加数据的过程是怎样的？主键是顺序插入的，即先填写第一页的数据。如果第一页已满，则写在第二页，以此类推。

从磁盘申请一个页面。主键按顺序插入。

当第一个页面保存完毕后，会继续申请第二个页面，页面之间通过双向指针连接；当第二页也满时，再申请第三页；通过主键乱序添加数据的过程是怎样的？主键是乱序插入的，而不是向后插入的。由于叶子节点的主键是有序的，因此会发生分页。

假设第1页和第2页已满，此时要插入主键50。新页不会直接写入，因为索引结构的叶节点是有序的。

按照顺序，主键50应该存储在47之后。但是第1页显然已经满了，那么就会进行“分页”的过程，即先打开第三页，然后再打开后半页将第1页的数据移至第3页，然后将50插入第3页。

最后将2#和3#两页的位置交换，以符合主键排序规则。最终的过程如下：与页拆分相反，还有页合并现象：当一行记录被删除时，该记录实际上并没有被物理删除，而是被标记为删除，其空间变得允许被其他记录使用声明。

当一个页面中删除的记录数量达到MERGE_THRESHOLD（默认为页面的50%）时，InnoDB将开始寻找最接近的页面（之前或之后），看看是否可以合并两个页面以优化空间使用。最后删除2#中标记的数据，同时合并2#和3#： 3.总结插入多条数据时，尽量选择批量插入，因为批量插入只需要对数据库进行一次远程请求，插入多条数据代码更加简洁。

插入时，尽量选择手动控制事务插入，因为通过手动控制事务，可以将多个执行单元合并为一个事务，从而避免多个事务的开销，同时保证数据的完整性和一致性。当插入大量数据时，MySQL提供的load指令比Insert语句效率更高。

插入数据时，尽量按照主键顺序插入，并使用AUTO_INCREMENT主键自增。因为当主键乱序插入时，会发生“分页”，消耗性能。

尽量不要使用UUID作为主键或者其他自然主键，比如ID号。由于每次生成的UUID都是乱序的，插入时主键乱序插入，会出现“分页”，消耗性能，避免业务操作时修改主键。

因为修改主键后，还需要修改索引结构，成本较高。在满足业务需求时，尽量减少主键的长度。

站长声明

标签：

上一篇：基于Python+uiautomation的Windows GUI自动化测试概述

下一篇：大数据ELK（十九）：使用FileBeat收集Kafka日志到Elasticsearch

受Model 3注册量下降影响，一季度特斯拉在加州的汽车注册量同比小幅下降

根据市场研究公司Cross-Sell的数据，今年第一季度，特斯拉的汽车注册量加州的增幅略低于去年同期。下降的主要原因是 Model 3 注册量下降。数据显示，加州的 Model 3 注册量同比下降 54% 至 7 辆，而 Model Y 紧凑型跨界车的数量为 7 辆。加州一直是特斯拉在美国市场的领头

06-18
小马智行获新融资2.67亿美元，估值超53亿美元

投资界消息（微信ID：pedialy），自动驾驶独角兽公司Pony.ai宣布获得安省教师基金资助加拿大退休基金会（OTPP）旗下教师创新平台（TIP）领投C轮融资，总额达2.67亿美元。本轮投资方还包括Fidelity China Special Situations PLC、五源资本（原晨兴资本）、凯明投资、耀明资本

06-17
工业互联网服务商“未明智能”获Pre-A+轮融资，4个月内完成两轮融资

无锡未明智能科技有限公司（以下简称“未明智能”）完成Pre-A+轮融资墨量资本投资的A+轮融资 -A+轮融资。本轮融资将继续用于产研迭代、业务拓展、人才引进和渠道建设。 7月30日，公司刚刚完成百卡创投领投的Pre-A轮融资。未名智能成立于2007年，是金属加工领域的工业互联网

06-18
腾讯再发阳光奖，价值超6万元

从不少腾讯员工处获悉，腾讯今年的阳光奖今天已经揭晓。每位腾讯员工将获得腾讯股票，提升期为一年。如果按照当前价格计算，腾讯股票市值约为7.4万港元，折合人民币约6.1万元。此外，入职不满一年的员工也可以获得此奖项，去年12月加入公司的员工也收到了通知。（界面）L

06-18
潮流手表品牌“HappieWatch”获数千万天使轮融资

手表及生活潮流品牌“HappieWatch”完成数千万天使轮融资，由德讯投资领投，君尚资本、春妮跟投资本充当专属财务顾问。本轮融资将用于产品研发、加大品牌建设、渠道拓展等。听，中小企业反馈平台。倾听用户的需求，倾听创业者的声音，解决中小企业的痛点。点击立即参与调

06-17
东方银星拟成立半导体产业基金投资8英寸MEMS射频产线

5月12日，东方银星（53）公告称，为全面加速公司战略转型，公司计划重点关注关于MEMS射频滤波器产业化的方向是成立投资管理公司，打造整合产业资源的投资平台。公司拟出资1万元投资设立上海兴泰股权投资管理有限公司（名称暂定，具体名称以注册地工商登记机关最终核准为准）

06-06
【谁说消费不行了·“变强”】AMIRO：今年品牌已进入10亿俱乐部

编者按“今年消费不行了”简直成为了今年的俗话，人人都可以说吧，大家都在议论。说“不”的人主要有两个原因：一是资本市场遇冷，二是疫情导致消费市场萎缩。 ——岁末年末，在疫情似乎无休无止的时候，清流资本不得不站出来唱反调：谁说消费不再可以了？！确实，对于大多

06-18
The North Face 和 Balenciaga 都很受欢迎，是全世界男生人必备的

最近一张性感孕肚照就获得了1万个点赞，并吸引了6.4万条网友评论。难道是因为这套Gucci内衣的售价是￥（美元）？ ▲ 图片来自：Instagram @chrissyteigen 照片中的辣妈就是曾登上“年度最高片酬模特”榜单的福布斯传奇嫂子 Chrissy Teigen。两年前，她经历了儿子出生半小时

06-21
神州数码赴台上市获批并获赛富、IDG投资

据投资界11月10日消息，神州数码控股有限公司（以下简称“神州数码”）宣布，去年晚间将发行台湾存托凭证（TDR）台湾TDR的配售架构及发行申请已获台湾中央银行、台湾证券交易所及台湾金融监督管理委员会批准。至此，神州数码已完成台湾TDR发行的全部审批工作，预计11月24日

06-17
我的宝城一票投给了东北小镇

最近，黑龙江边陲小城黑河热闹非凡。 9月21日，黑龙江省黑河市恢复中俄团体旅游免签业务。随后，进出黑河口岸的人数激增。一手拿着包子，一手拿着啤酒，眼睛盯着即将出炉的鸡蛋汉堡，黑河早市上有很多俄罗斯人。相关视频也在社交平台上走红。 “俄罗斯人成群结队去黑龙江

06-18
据悉，英特尔将投资数百亿美元在德国、法国和意大利建厂

北京时间12月23日晚间消息。据报道，知情人士今天表示，为了增加产能并重新获得技术优势，英特尔计划投资数百亿美元在德国、法国和意大利建设芯片工厂和研发中心。知情人士称，英特尔将在法国建立一个研究和设计中心，在意大利建立一个测试和组装工厂，在德国建立一个芯片制

06-08
上海全面推进制造业数字化，参与制定80多项改造标准，

上头条，上海市委常委、副主席吴清在9月9日的新闻发布会上表示，上海将全面推进城市数字化转型，其中制造业是关键对经济。数字化转型的重要主线之一。据悉，为加快全要素生态系统建设，目前已牵头或参与制定80余项制造业数字化转型标准。《上海市先进制造业发展“十四五”

06-17