您的当前位置：首页正文

MySQL数据库插入速度调整

2023-11-08 来源：画鸵萌宠网

1.优化数据库主机IO；2.调整MySQL对应参数。开始处理：1.主机IO优化a.将数据库设置为独立表空间，并且将每个表空间放在不同的硬盘上；b.关闭主机其他影响IO的进程。2. 调整my.cnf参数，提升MySQL写速度

innodb_autoextend_increment =128M

innodb_log_buffer_size =32M

innodb_log_file_size=128M

以上3个参数是逐步变更到对应大小的

innodb_flush_log_at_trx_commit = 0

MySQL数据库插入速度调整

标签：mysql数据库插入速度调整

小编还为您整理了以下内容，可能对您也有帮助：

mysql怎么提高insert into的速度啊?

sql语句中，添加记录的语法为：insert into 表名（col1，col2....coln）values(value1,value2.....valuen)；

其中，如果你插入的每一列都是顺序插入，无一缺漏的话，（col1，col2...coln）可以省略。

也就是上式也可以简化为：insert into 表名 values(value1,value2.....valuen)；

看了你写的sql代码，问题出在insert into 的整体语句出现在了不该出现的地方，只需做一点小改动即可解决，如下图：

解析：insert into语句需要在user表已经存在的情况下才可以使用。而你原来的语句中，将上图2中的语句插入到了create table user的语句中，致使create table user 语句未能成功执行，所以才会报错。

而将“INSERT INTO user(uid,tel) values('甲','3354986');”整条语句直接拿出来放在“ENGINE=InnoDB DEFAULT CHARSET=gbk;”后面之后，整个sql就可以顺利执行了。

扩展资料：

当mysql大批量插入数据的时候就会变的非常慢, mysql提高insert into 插入速度的方法有三种:

1、第一种插入提速方法:

如果数据库中的数据已经很多(几百万条), 那么可以加大mysql配置中的 bulk_insert_buffer_size，这个参数默认为8M

举例：bulk_insert_buffer_size=100M；

2、第二种mysql插入提速方法:

改写所有 insert into 语句为 insert delayed into

这个insert delayed不同之处在于：立即返回结果，后台进行处理插入。

3、第三个方法: 一次插入多条数据:

insert中插入多条数据，举例:

insert into table values('11','11'),('22','22'),('33','33')...;

mysql怎么提高insert into的速度啊?

sql语句中，添加记录的语法为：insert into 表名（col1，col2....coln）values(value1,value2.....valuen)；

其中，如果你插入的每一列都是顺序插入，无一缺漏的话，（col1，col2...coln）可以省略。

也就是上式也可以简化为：insert into 表名 values(value1,value2.....valuen)；

看了你写的sql代码，问题出在insert into 的整体语句出现在了不该出现的地方，只需做一点小改动即可解决，如下图：

而将“INSERT INTO user(uid,tel) values('甲','3354986');”整条语句直接拿出来放在“ENGINE=InnoDB DEFAULT CHARSET=gbk;”后面之后，整个sql就可以顺利执行了。

扩展资料：

当mysql大批量插入数据的时候就会变的非常慢, mysql提高insert into 插入速度的方法有三种:

1、第一种插入提速方法:

如果数据库中的数据已经很多(几百万条), 那么可以加大mysql配置中的 bulk_insert_buffer_size，这个参数默认为8M

举例：bulk_insert_buffer_size=100M；

2、第二种mysql插入提速方法:

改写所有 insert into 语句为 insert delayed into

这个insert delayed不同之处在于：立即返回结果，后台进行处理插入。

3、第三个方法: 一次插入多条数据:

insert中插入多条数据，举例:

insert into table values('11','11'),('22','22'),('33','33')...;

有没有提高mysql数据库insert速率的方法

加快MySQL插入速度可循下列手段去做：
1）数据表使用尽量少的索引；
2）合理设计表结构、尽量插入冗余量较小的信息，避免插入多余、重复和无用的信息；
3）尽量减少应用程序与数据库之间的网络往返量（如使用存储过程等）；
4）数据表使用MyISAM存储引擎替代默认的InnoDB存储引擎。在不需要支持事务的情况下，MyISAM引擎表的插入速度要远高于InnoDB引擎表，因为前者不需要增加额外的事务、回滚和崩溃修复等系统开销，自然插入速度要比后者迅速的多；
5）减少并发量、提升硬件。

有没有提高mysql数据库insert速率的方法

如何提高mysql 的插入速度,高手请帮忙

mysql：insert插入数据过慢如何解决，设置innodb_flush_log_at_trx_commit为0就能解决问题：

最近在做性能测试，造数据，发现insert好慢，只有几十条每秒，很奇怪，最后再网上找到了原因。

网文如下：

MY SQL insert 速度过慢

最近在用MySQL做存储，测试中发现插入数据太慢了，插入速度只有20-30 条/秒，后来查资料后，将MySQL的1个参数：innodb_flush_log_at_trx_commit，1改为了0（修改方法，可以直接修改my.ini(windows)/my.cnf(linux)中的对应参数，似乎用命令直接设置没有效果），插入速度就提升到了3000+每S，MySQL的文档中，对这个参数的描述如下：

If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1 (the default),

the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed

on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, e to process scheling issues.

这样，如果transaction commit的频率非常高，1秒钟内会进行很多次的话，可以考虑将其设置为0，但这样就要承担数据库Crash后，1秒内未存储到数据库数据丢失可能的风险

设置后重启mysql，再重新执行存储过程，基本上插入速度能达到6000条/s。

mysql：insert插入数据过慢如何解决，设置innodb_flush_log_at_trx_commit为0就能解决

标签：secondash执行alsform很多原因直接mit

mysql数据库插入数据很慢,咋回事

如果原表很大，插入数据会非常慢，建议插入到临时表，然后用一个语句（INSERT
INTO
XXX
SELECT
*
FTOM
TMPXXX）把数据插入，这样速度会快一点，如果想更快，需要减少不必要的索引，如果大批量的插入，可以插入前删除索引，插入后重新建立。

mysql数据库插入数据很慢,咋回事

优化MySQL插入方法的五个妙招

以下是涉及到插入表格的查询的种改进方法

)使用LOAD DATA INFILE从文本下载数据这将比使用插入语句快倍

)使用带有多个VALUES列表的INSERT语句一次插入几行这将比使用一个单行插入语句快几倍调整bulk_insert_buffer_size变量也能提高（向包含行的表格中）插入的速度

)可以对myisam表并行插入Concurrent_insert系统变量可以被设置用于修改concurrent insert处理该变量默认设置为如果concurrent_insert被设置为并行插入就被禁用如果该变量被设置为在表的末端可以并行插入即便该表的某些行已经被删除

)使用插入延迟

如果你的客户不能或无需等待插入完成的时候这招很有用当你使用MySQL存储并定期运行需要很长时间才能完成的SELECT和UPDATE语句的时候你会发现这种情况很常见当客户使用插入延迟服务器立刻返回如果表没有被其他线程调用则行会列队等待 *** 入使用插入延迟的另一个好处就是从多个客户插入的情况会被绑定并记录在同一个block中这将比处理多个独立的插入要快得多

)插入之前将表锁定(只针对非事务处理型的表)

这将提高数据库性能因为索引缓冲区只是在所有的插入语句完成后才对磁盘进行一次刷新通常情况下有多少个插入语句就会有多少次索引缓冲区刷新如果你可以用一个插入语句实现所有行的插入则无需使用显式锁定语句

lishixin/Article/program/MySQL/201311/29642

优化MySQL插入方法的五个妙招

以下是涉及到插入表格的查询的种改进方法

)使用LOAD DATA INFILE从文本下载数据这将比使用插入语句快倍

)使用插入延迟

)插入之前将表锁定(只针对非事务处理型的表)

lishixin/Article/program/MySQL/201311/29642

mysql 如何加快存储过程插入数据速度

1 索引可以先去了这样会快点插入完毕之后再重建索引
2 不要1个insert一条数据可以一条长长的insert 里面包含1000条数据做一次插入操作，这样快很多

mysql 如何加快存储过程插入数据速度

1 索引可以先去了这样会快点插入完毕之后再重建索引
2 不要1个insert一条数据可以一条长长的insert 里面包含1000条数据做一次插入操作，这样快很多

mysql插入1000条数据到数据表中如何能加快速度

常用的插入语句如：

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)

VALUES ('0', 'userid_0', 'content_0', 0);

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)

VALUES ('1', 'userid_1', 'content_1', 1);

修改成：

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)

VALUES ('0', 'userid_0', 'content_0', 0), ('1', 'userid_1', 'content_1', 1);

修改后的插入操作能够提高程序的插入效率。这里第二种SQL执行效率高的主要原因是合并后日志量（MySQL的binlog和innodb的事务让日志）减少了，降低日志刷盘的数据量和频率，从而提高效率。通过合并SQL语句，同时也能减少SQL语句解析的次数，减少网络传输的IO。

SQL语句是有长度，在进行数据合并在同一SQL中务必不能超过SQL长度，通过max_allowed_packet配置可以修改，默认是1M，测试时修改为8M。

如何提升MySQL批量插入的效率

需要将大量数据(大概5W条)插入MySQL数
据库，用普通的SQL
Statement执行,时间大概是几分钟。于是想到用PreparedStatement，但是改了之后发现效率并没有很大的提升。不成，想到了
load data local
infile...命令，以前一直认为这条命令仅限MySQL终端上使用而不是标准的SQL语句，今天看了几篇文章之后有了很大的收获。

1. 使用PreparedStatement batch operation

以前使用PreparedStatement性能没有很大提升的原因在于：

没有使用批处理方法
在语句执行之前应关闭事务自动提交，语句执行完之后再提交

public
void batchLoad(Connection connection)

{

try
{

connection.setAutoCommit(false);

BufferedReader reader =
new BufferedReader(new
FileReader("tfacts_result"));

String sqlString =
"insert into test(node1, node2, weight) values(?, ?, ?)";

PreparedStatement pstmt = connection.prepareStatement(sqlString);

String line =
null;

while(true)

{

line = reader.readLine();

if(line == null)

{

break;

}

String[] columns = line.split("\t");

for(int
i = 1; i <= columns.length; i++)

{

pstmt.setString(i, columns[i-1]);

}

pstmt.addBatch();

}

pstmt.executeBatch();

connection.commit();

pstmt.close();

reader.close();

}
catch (FileNotFoundException e) {

e.printStackTrace();

}catch
(SQLException e){

e.printStackTrace();

}catch
(IOException e){

e.printStackTrace();

}

2.使用load data local infile into tabel XXX（注意在文件中用\t将每列数据隔开）

public
void loadData(Connection connection)

{

long
starTime = System.currentTimeMillis();

String sqlString =
"load data local infile ? into table test";

PreparedStatement pstmt;

try
{

pstmt = connection.prepareStatement(sqlString);

pstmt.setString(1,
"tfacts_result");

pstmt.executeUpdate();

pstmt.close();

}
catch (SQLException e) {

e.printStackTrace();

}

long
endTime = System.currentTimeMillis();

System.out.println("program runs "
+ (endTime - starTime) + "ms");

}

测试了5W条数据，PreparedStatement耗时10s，而load data infile耗时3s。

如何提升MySQL批量插入的效率

读取excel表，每次读取5000条并往数据库插入，往mysql插数据时,每一次花的时间都比上一次多，

（1）提高数据库插入性能中心思想：尽量将数据一次性写入到Data File和减少数据库的checkpoint 操作。这次修改了下面四个配置项：

1）将 innodb_flush_log_at_trx_commit 配置设定为0；按过往经验设定为0，插入速度会有很大提高。

0: Write the log buffer to the log file and flush the log file every second, but do nothing at transaction commit.

1：the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file

2：the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it

2）将 innodb_autoextend_increment 配置由于默认8M 调整到 128M

此配置项作用主要是当tablespace 空间已经满了后，需要MySQL系统需要自动扩展多少空间，每次tablespace 扩展都会让各个SQL 处于等待状态。增加自动扩展Size可以减少tablespace自动扩展次数。

3）将 innodb_log_buffer_size 配置由于默认1M 调整到 16M

此配置项作用设定innodb 数据库引擎写日志缓存区；将此缓存段增大可以减少数据库写数据文件次数。

4）将 innodb_log_file_size 配置由于默认 8M 调整到 128M

此配置项作用设定innodb 数据库引擎UNDO日志的大小；从而减少数据库checkpoint操作。

经过以上调整，系统插入速度由于原来10分钟几万条提升至1秒1W左右；注：以上参数调整，需要根据不同机器来进行实际调整。特别是 innodb_flush_log_at_trx_commit、innodb_log_buffer_size和 innodb_log_file_size 需要谨慎调整；因为涉及MySQL本身的容灾处理。

（2）提升数据库读取速度，重数据库层面上读取速度提升主要由于几点：简化SQL、加索引和分区；经过检查程序SQL已经是最简单，查询条件上已经增加索引。我们只能用武器：表分区。

数据库 MySQL分区前准备：在MySQL中，表空间就是存储数据和索引的数据文件。

将S11数据库由于同享tablespace 修改为支持多个tablespace;

将wb_user_info_sina 和 wb_user_info_tx 两个表修改为各自表空间；（Sina：1700W数据，2.6G 大数据文件，Tencent 1400W，2.3G大数据文件）；

分区操作：

将现有的主键和索引先删除

重现建立id,uid 的联合主键

再以 uid 为键值进行分区。这时候到/var/data/mysql 查看数据文件，可以看到两个大表各自表空间已经分割成若干个较少分区空间。（这时候若以uid 为检索条件进行查询，并不提升速度；因为键值只是安排数据存储的分区并不会建立分区索引。我非常郁闷这点比Oracle 差得不是一点半点。）

再以 uid 字段上进行建立索引。再次到/var/data/mysql 文件夹查看数据文件，非常郁闷地发现各个分区Size竟然大了。MySQL还是老样子将索引与数据存储在同一个tablespace里面。若能index 与数据分离能够更加好管理。

经过以上调整，暂时没能体现出系统读取速度提升；基本都是在 2~3秒完成5K数据更新。

MySQL数据库插入速度调整补充资料：

MySQL 从最开始的时候 1000条/分钟的插入速度调高至 10000条/秒。相信大家都已经等急了相关介绍，下面我做调优时候的整个过程。提高数据库插入性能中心思想：

1、尽量使数据库一次性写入Data File

2、减少数据库的checkpoint 操作

3、程序上尽量缓冲数据，进行批量式插入与提交

4、减少系统的IO冲突

根据以上四点内容，作为一个业余DBA对MySQL服务进行了下面调整：

修改负责收录记录MySQL服务器配置，提升MySQL整体写速度；具体为下面三个数据库变量值：innodb_autoextend_increment、innodb_log_buffer_size、innodb_log_file_size；此三个变量默认值分别为 5M、8M、8M，根据服务器内存大小与具体使用情况，将此三只分别修改为：128M、16M、128M。同时，也将原来2个 Log File 变更为 8 个Log File。此次修改主要满足第一和第二点，如：增加innodb_autoextend_increment就是为了避免由于频繁自动扩展Data File而导致 MySQL 的checkpoint 操作；

将大表转变为表空并且进行分区，然后将不同分区下挂在多个不同硬盘阵列中。

完成了以上修改操作后；我看到下面幸福结果：

获取测试结果：

Query OK, 2500000 rows affected (4 min 4.85 sec)

Records: 2500000 Duplicates: 0 Warnings: 0

Query OK, 2500000 rows affected (4 min 58.89 sec)

Records: 2500000 Duplicates: 0 Warnings: 0

Query OK, 2500000 rows affected (5 min 25.91 sec)

Records: 2500000 Duplicates: 0 Warnings: 0

Query OK, 2500000 rows affected (5 min 22.32 sec)

Records: 2500000 Duplicates: 0 Warnings: 0

最后表的数据量：

+------------+

| count(*) |

+------------+

| 10000000|

+------------+

从上面结果来看，数据量增加会对插入性能有一定影响。不过，整体速度还是非常面议。一天不到时间，就可以完成4亿数据正常处理。预计数据库瓶颈已经被巧妙解决，结果变成程序“猿”苦地向我埋怨，大哥不用这么狠啊。