五个细节入手 帮助提升数据插入效率
插入数据，是MySQL数据库的基本任务。不过不要小看这个插入数据的动做。在数据库性能优化上，能够在这上面作很大的文章。若是利用MySQL做为一些即时信息化管理软件的后台数据库，如ERP系统。因为其数据插入的做业很是频繁，并且对性能的要求也比较高。此时数据库管理员就须要采起措施来提升数据插入的效率。针对这块内容，笔者认为数据库工程师在应用程序与数据库设计时，须要注意以下几个细节问题。
细节一：同时插入多行记录时，宜采用一条Insert语句
在数据插入时，每每须要同时往一个表中插入多条数据。如以ERP系统的采购订单为例。用户在前台客户端录入采购订单时，有可能须要向同一个供应商同时采购多个原材料。此时在数据库中，就须要往一个表格中同时插入多条记录。在插入数据时，能够经过两个方式实现。一是采用多条Insert语句，每一个Insert语句插入一条记录。另一种方式是只采用一条Insert语句，插入多条语句。采起这两种方式，有什么差别呢?
从语言编写的角度看，第一种方式比较清楚明了。可是从数据插入速度的角度来看，第二种方式性能比较好。特别是一次性插入一两百条记录时，二者所须要的时间会相差一倍以上。以下图所示，就是使用一条Insert语句插入多条记录的举例。
总之在数据插入时，须要注意使用带有多个值列表的Insert语句一次插入多行记录要比使用一个单行插入语句快不少。并且随着行数的增长，这个差别会愈来愈大。在数据库设计时，若是有设计到同时插入多条数据的纪录，如经过客户端导入基础数据、经过前置单据自动生成相关单据，这些做业都会遇到向某个表中一次性插入多条记录的状况。此时从提升数据库性能的角度考虑，笔者建议采用一个Insert语句同时插入多条记录的方式。虽说编写语言的时候会增长复杂程度，可是这点投资与数据库性能优化相比而言，是值得的。 细节二：批量插入记录时，创建采用Load Date Infile语句 有时候可能须要往Mysql数据库中批量导入数据。如在ERP系统初始化过程当中，须要将产品数据导入到系统中。实施顾问通常会要求用户根据他们的格式准备好相关的数据，而后利用导入做业，将这些数据一次性导入到数据库中。虽然这个导入做业是经过前台客户端来完成的，可是最终仍然是反映在数据库中。 针对这种批量数据的导入，数据库又提供了两种方式。一是上面所介绍的，采用一个Insert语句插入多行记录的方式;另一种就是采用Load Date Infile的方式。这两种方式有什么差别呢?Load Date Infile顾名思义，就是从一个文件中导入数据。为此若是采用这种方式的话，用户须要预先准备一个固定格式的文件。而Insert语句的话，没有这个要求，其能够直接利用其余做业中得到的数据。从这个角度讲，若是插入的纪录数比较少(如在一百条左右)或者经过系统交互来得到数据，此时比较适合使用Insert语句。从性能的角度考虑，一般状况下使用Load Date Infile语句的方式插入数据要比使用Insert语句速度快10到20倍。为此若是插入的数据量比较多，如期初导入数据时可能会有超过上万条的记录，此时使用Load Date Infile的方式可以取得比较好的性能。 细节三：插入延迟，提升插入操做对系统的不利影响 在某些状况下，用户对插入数据的即时性要求可能并非很高，此时就能够考虑经过插入延迟特性，来减小插入操做对系统资源的耗用。笔者再以ERP系统为例。在ERP系统中，有一个安全库存管理的做业。若是启用了这个做业的话，那么系统会在天天一个固定的时刻，分析帐上的库存数据与安全库存之间的关系。若是发现库存数量低于安全库存时，系统就会自动生成一张请购单，将差别的数据插入到后台的数据库表中。显然，对于这些记录，用户并不须要等待数据的插入完成。如在前台调用这个做业时，能够将这个做业放在后端执行。等到执行完毕后，再通知用户便可。 在遇到这种状况时，就可使用插入延迟的功能。当数据库工程师启用插入延迟功能时，服务器会马上返回，而不会在那边等着数据插入完成。若是表中没有被其余线程所调用的状况，那么记录会排队等待被插入。使用这个插入延迟特性，还可以带来另一个好处。当多个做业同时往同一个表中插入数据的时候，这些进程会被记录在同一个Block中。这就好像将多个单独的Insert语句合并成一条Insert语句来执行，其速度会快许多。 细节四：在插入大量数据以前，能够先将表锁定 在数据库库中，某些表只有特定的用户才使用。并且每次使用时可能会同时插入多条数据。如在ERP系统中导入期初数据时，就会碰到这种状况。从前台客户端导入产品数据时，一般状况下中间会有一个过渡表。用户能够经过这个过渡表来核对数据是否准确。若是没有问题的话，再将其导入到产品信息表中。采起这种方式，用户还能够查询到有哪些记录没有被导入到系统中。 因为期初数据的导入，通常是有系统管理员来完成的。此时这张过渡表只有一个用户使用。为了提升数据插入的效率，数据库工程师能够考虑，在插入以前先将表锁定。这能够提升数据插入的性能。这主要是由于索引缓冲区只是在全部的插入语句完成以后才对磁盘进行一次性刷新。也就是说，有多个插入语句就会有多少次索引缓冲区刷新。为此在数据插入以前，将数据表进行锁定，就能够大幅度的提升数据插入的效率。 细节五：插入数据以前先对数据进行排序 众所周知，在对记录进行排序时，须要耗费必定的系统资源。若是在插入数据时，就可以考虑到排序问题，那么在后续记录查询时，就能够避免从新排序所形成的二次开销。故笔者建议，在可行的状况下，在对数据插入以前先对记录根据必定的规则进行排序。 如在采购订单行表中，同时插入多条语句(从物料申购单转换为采购订单)。在采购订单行中插入多条记录以前，能够根据用户的使用习惯，如按照产品的编号进行排序。用户在后续查询时，就能够直接使用查询的结果(默认状况下是根据记录插入的前后顺序来显示结果的)。显示时就不须要再进行一次排序做业，从而能够提升数据后续查询的效率。 上面笔者提到的这个些内容，其实都是插入做业中的一些细节问题。可是这些细节内容，对于提升数据库的插入效率有很大的帮助。有时候单纯的经过提升硬件配置来改善数据库的性能，还不可以提到这么明显的效果。
细节一：同时插入多行记录时，宜采用一条Insert语句
在数据插入时，每每须要同时往一个表中插入多条数据。如以ERP系统的采购订单为例。用户在前台客户端录入采购订单时，有可能须要向同一个供应商同时采购多个原材料。此时在数据库中，就须要往一个表格中同时插入多条记录。在插入数据时，能够经过两个方式实现。一是采用多条Insert语句，每一个Insert语句插入一条记录。另一种方式是只采用一条Insert语句，插入多条语句。采起这两种方式，有什么差别呢?
从语言编写的角度看，第一种方式比较清楚明了。可是从数据插入速度的角度来看，第二种方式性能比较好。特别是一次性插入一两百条记录时，二者所须要的时间会相差一倍以上。以下图所示，就是使用一条Insert语句插入多条记录的举例。
总之在数据插入时，须要注意使用带有多个值列表的Insert语句一次插入多行记录要比使用一个单行插入语句快不少。并且随着行数的增长，这个差别会愈来愈大。在数据库设计时，若是有设计到同时插入多条数据的纪录，如经过客户端导入基础数据、经过前置单据自动生成相关单据，这些做业都会遇到向某个表中一次性插入多条记录的状况。此时从提升数据库性能的角度考虑，笔者建议采用一个Insert语句同时插入多条记录的方式。虽说编写语言的时候会增长复杂程度，可是这点投资与数据库性能优化相比而言，是值得的。 细节二：批量插入记录时，创建采用Load Date Infile语句 有时候可能须要往Mysql数据库中批量导入数据。如在ERP系统初始化过程当中，须要将产品数据导入到系统中。实施顾问通常会要求用户根据他们的格式准备好相关的数据，而后利用导入做业，将这些数据一次性导入到数据库中。虽然这个导入做业是经过前台客户端来完成的，可是最终仍然是反映在数据库中。 针对这种批量数据的导入，数据库又提供了两种方式。一是上面所介绍的，采用一个Insert语句插入多行记录的方式;另一种就是采用Load Date Infile的方式。这两种方式有什么差别呢?Load Date Infile顾名思义，就是从一个文件中导入数据。为此若是采用这种方式的话，用户须要预先准备一个固定格式的文件。而Insert语句的话，没有这个要求，其能够直接利用其余做业中得到的数据。从这个角度讲，若是插入的纪录数比较少(如在一百条左右)或者经过系统交互来得到数据，此时比较适合使用Insert语句。从性能的角度考虑，一般状况下使用Load Date Infile语句的方式插入数据要比使用Insert语句速度快10到20倍。为此若是插入的数据量比较多，如期初导入数据时可能会有超过上万条的记录，此时使用Load Date Infile的方式可以取得比较好的性能。 细节三：插入延迟，提升插入操做对系统的不利影响 在某些状况下，用户对插入数据的即时性要求可能并非很高，此时就能够考虑经过插入延迟特性，来减小插入操做对系统资源的耗用。笔者再以ERP系统为例。在ERP系统中，有一个安全库存管理的做业。若是启用了这个做业的话，那么系统会在天天一个固定的时刻，分析帐上的库存数据与安全库存之间的关系。若是发现库存数量低于安全库存时，系统就会自动生成一张请购单，将差别的数据插入到后台的数据库表中。显然，对于这些记录，用户并不须要等待数据的插入完成。如在前台调用这个做业时，能够将这个做业放在后端执行。等到执行完毕后，再通知用户便可。 在遇到这种状况时，就可使用插入延迟的功能。当数据库工程师启用插入延迟功能时，服务器会马上返回，而不会在那边等着数据插入完成。若是表中没有被其余线程所调用的状况，那么记录会排队等待被插入。使用这个插入延迟特性，还可以带来另一个好处。当多个做业同时往同一个表中插入数据的时候，这些进程会被记录在同一个Block中。这就好像将多个单独的Insert语句合并成一条Insert语句来执行，其速度会快许多。 细节四：在插入大量数据以前，能够先将表锁定 在数据库库中，某些表只有特定的用户才使用。并且每次使用时可能会同时插入多条数据。如在ERP系统中导入期初数据时，就会碰到这种状况。从前台客户端导入产品数据时，一般状况下中间会有一个过渡表。用户能够经过这个过渡表来核对数据是否准确。若是没有问题的话，再将其导入到产品信息表中。采起这种方式，用户还能够查询到有哪些记录没有被导入到系统中。 因为期初数据的导入，通常是有系统管理员来完成的。此时这张过渡表只有一个用户使用。为了提升数据插入的效率，数据库工程师能够考虑，在插入以前先将表锁定。这能够提升数据插入的性能。这主要是由于索引缓冲区只是在全部的插入语句完成以后才对磁盘进行一次性刷新。也就是说，有多个插入语句就会有多少次索引缓冲区刷新。为此在数据插入以前，将数据表进行锁定，就能够大幅度的提升数据插入的效率。 细节五：插入数据以前先对数据进行排序 众所周知，在对记录进行排序时，须要耗费必定的系统资源。若是在插入数据时，就可以考虑到排序问题，那么在后续记录查询时，就能够避免从新排序所形成的二次开销。故笔者建议，在可行的状况下，在对数据插入以前先对记录根据必定的规则进行排序。 如在采购订单行表中，同时插入多条语句(从物料申购单转换为采购订单)。在采购订单行中插入多条记录以前，能够根据用户的使用习惯，如按照产品的编号进行排序。用户在后续查询时，就能够直接使用查询的结果(默认状况下是根据记录插入的前后顺序来显示结果的)。显示时就不须要再进行一次排序做业，从而能够提升数据后续查询的效率。 上面笔者提到的这个些内容，其实都是插入做业中的一些细节问题。可是这些细节内容，对于提升数据库的插入效率有很大的帮助。有时候单纯的经过提升硬件配置来改善数据库的性能，还不可以提到这么明显的效果。