提高数据库插入效率

以你之姓@ 2022-08-18 01:55 160阅读 0赞

插入数据，是 [**MySQL**][MySQL] [**数据库**][MySQL] 的基本任务。不过不要小看这个插入数据的动作。在数据库性能优化上，可以在这上面做很大的 [**文章**][MySQL] 。如果利用MySQL作为一些即时信息化管理软件的后台数据库，如ERP系统。由于其数据插入的作业非常频繁，而且对性能的要求也比较高。此时数据库管理员就需要采取措施来提高数据插入的效率。针对这块内容，笔者认为数据库工程师在应用程序与数据库设计时，需要注意如下几个细节问题。

**　　细节一：同时插入多行记录时，宜采用一条Insert语句**

在数据插入时，往往需要同时往一个表中插入多条数据。如以ERP系统的采购订单为例。用户在前台客户端录入采购订单时，有可能需要向同一个供应商同时采购多个原材料。此时在数据库中，就需要往一个表格中同时插入多条记录。在插入数据时，可以通过两个方式实现。一是采用多条Insert语句，每个Insert语句插入一条记录。另外一种方式是只采用一条Insert语句，插入多条语句。采取这两种方式，有什么差异呢？

从语言编写的角度看，第一种方式比较清楚明了。但是从数据插入速度的角度来看，第二种方式性能比较好。特别是一次性插入一两百条记录时，两者所需要的时间会相差一倍以上。如下图所示，就是使用一条Insert语句插入多条记录的举例。

[![346836_201101241020581srQG.jpg][]][346836_201101241020581srQG.jpg 1]

总之在数据插入时，需要注意使用带有多个值列表的Insert语句一次插入多行记录要比使用一个单行插入语句快很多。而且随着行数的增加，这个差异会越来越大。在数据库设计时，如果有设计到同时插入多条数据的纪录，如通过客户端导入基础数据、通过前置单据自动生成相关单据，这些作业都会遇到向某个表中一次性插入多条记录的情况。此时从提高数据库性能的角度考虑，笔者建议采用一个Insert语句同时插入多条记录的方式。虽然说编写语言的时候会增加复杂程度，但是这点投资与数据库性能优化相比而言，是值得的。

**　　细节二：批量插入记录时，建立采用Load Date Infile语句**

有时候可能需要往Mysql数据库中批量导入数据。如在ERP系统初始化过程中，需要将产品数据导入到系统中。实施顾问一般会要求用户根据他们的格式准备好相关的数据，然后利用导入作业，将这些数据一次性导入到数据库中。虽然这个导入作业是通过前台客户端来完成的，但是最终仍然是反映在数据库中。

针对这种批量数据的导入，数据库又提供了两种方式。一是上面所介绍的，采用一个Insert语句插入多行记录的方式；另外一种就是采用Load Date Infile的方式。这两种方式有什么差异呢？Load Date Infile顾名思义，就是从一个文件中导入数据。为此如果采用这种方式的话，用户需要预先准备一个固定格式的文件。而Insert语句的话，没有这个要求，其可以直接利用[**其他**][MySQL]作业中获得的数据。从这个角度讲，如果插入的纪录数比较少（如在一百条左右）或者通过系统交互来获得数据，此时比较适合使用Insert语句。从性能的角度考虑，通常情况下使用Load Date Infile语句的方式插入数据要比使用Insert语句速度快10到20倍。为此如果插入的数据量比较多，如期初导入数据时可能会有超过上万条的记录，此时使用Load Date Infile的方式能够取得比较好的性能。

**细节三：插入延迟，提高插入操作对系统的不利影响**

在某些情况下，用户对插入数据的即时性要求可能并不是很高，此时就可以考虑通过插入延迟特性，来减少插入操作对系统资源的耗用。笔者再以ERP系统为例。在ERP系统中，有一个安全库存管理的作业。如果启用了这个作业的话，那么系统会在每天一个固定的时刻，分析账上的库存数据与安全库存之间的关系。如果发现库存数量低于安全库存时，系统就会自动生成一张请购单，将差异的数据插入到后台的数据库表中。显然，对于这些记录，用户并不需要等待数据的插入完成。如在前台调用这个作业时，可以将这个作业放在后端执行。等到执行完毕后，再通知用户即可。

在遇到这种情况时，就可以使用插入延迟的功能。当数据库工程师启用插入延迟功能时，服务器会立刻返回，而不会在那边等着数据插入完成。如果表中没有被其他线程所调用的情况，那么记录会排队等待被插入。使用这个插入延迟特性，还能够带来另外一个好处。当多个作业同时往同一个表中插入数据的时候，这些进程会被记录在同一个Block中。这就好像将多个单独的Insert语句合并成一条Insert语句来执行，其速度会快许多。

**　　细节四：在插入大量数据之前，可以先将表锁定**

在数据库库中，某些表只有特定的用户才使用。而且每次使用时可能会同时插入多条数据。如在ERP系统中导入期初数据时，就会碰到这种情况。从前台客户端导入产品数据时，通常情况下中间会有一个过渡表。用户可以通过这个过渡表来核对数据是否准确。如果没有问题的话，再将其导入到产品信息表中。采取这种方式，用户还可以查询到有哪些记录没有被导入到系统中。

由于期初数据的导入，一般是有系统管理员来完成的。此时这张过渡表只有一个用户使用。为了提高数据插入的效率，数据库工程师可以考虑，在插入之前先将表锁定。这可以提高数据插入的性能。这主要是因为索引缓冲区只是在所有的插入语句完成之后才对磁盘进行一次性刷新。也就是说，有多个插入语句就会有多少次索引缓冲区刷新。为此在数据插入之前，将数据表进行锁定，就可以大幅度的提高数据插入的效率。

**　　细节五：插入数据之前先对数据进行排序**

众所周知，在对记录进行排序时，需要耗费一定的系统资源。如果在插入数据时，就能够考虑到排序问题，那么在后续记录查询时，就可以避免重新排序所造成的二次开销。故笔者建议，在可行的情况下，在对数据插入之前先对记录根据一定的规则进行排序。

如在采购订单行表中，同时插入多条语句（从物料申购单转换为采购订单）。在采购订单行中插入多条记录之前，可以根据用户的使用习惯，如按照产品的编号进行排序。用户在后续查询时，就可以直接使用查询的结果（默认情况下是根据记录插入的先后顺序来显示结果的）。显示时就不需要再进行一次排序作业，从而可以提高数据后续查询的效率。

[MySQL]: http://blog.csdn.net/screensky/article/details/7696881
[346836_201101241020581srQG.jpg]: /images/20220731/2eaa01f2cce248718594d92eb41514fe.png
[346836_201101241020581srQG.jpg 1]: http://www.51testing.com/batch.download.php?aid=23883