MySQL该如何进行大数据量快速插入方法和语句优化？

Tech Neo技巧沙龙 | 11月25号，九州云/ZStack与您一路商量云时代收集界线治理实践

MySQL该若何进行大年夜数据量快速插入办法和语句优化？

INSERT 语句的速度

插入一个记录须要的时光由下列身分构成，个中的数字表示大年夜约比例：

二、IGNORE 的应用

连接：（3）

INSERT DELAYED 应当仅用于指定值清单的 INSERT 语句。办事器忽视用于 INSERT DELAYED…SELECT 语句的 DELAYED。办事器忽视用于 INSERT DELAYED…ON DUPLICATE UPDATE 语句的 DELAYED。

发送萌芽给办事器：（2）

分析萌芽：（2）

插入记录：（1x 记录大年夜小）

插入索引：（1x 索引）

封闭：（1）

这不推敲打开表的初始?销，每个并发运行的萌芽打开。

表的大年夜小以 logN （B 树）的速度减慢索引的插入。

LOCK TABLES a WRITE; 
INSERT INTO a VALUES (1,23),(2,34),(4,33); 
INSERT INTO a VALUES (8,26),(6,29); 
UNLOCK TABLES;

对于 SELECT 语句，DELAYED 行弗成见，直到这些行确切被插入了为止。

加快插入的一些办法

如不雅同时大年夜同一个客户端插入很多行，应用含多个 VALUE 的 INSERT 语句同时插入几行。这比应用单行 INSERT 语句快（在某些情况下快几倍）。如不雅你正向一个非空表添加数据，可声调节 bulk_insert_buffer_size 变量，使数据插入更快。

如不雅你大年夜不合的客户端插入很多行，能经由过程 INSERT DELAYED 语句加快速度。

用 MyISAM，如不雅在表中没有删除的行，能在 SELECT 语灸┞俘在运行的同时插入行。

昔时腋荷琐文本文件装载一个表时，应用 LOAD DATA INFILE。这平日比应用很多 INSERT 语句快 20 倍。拜见 13.2.5 节，“LOAD DATA INFILE 语法”。

当表有很多索引时，有可能要多做些工作使得 LOAD DATA INFILE 更快些。应用下列过程：

有选择地用 CREATE TABLE 创建表

履行 FLUSH TABLES 语句或敕令 mysqladmin flush-tables。应用 myisamchk –keys-used=0 -rq /path/to/db/tbl_name。这将大年夜表中撤消所有索引的应用。

用 LOAD DATA INFILE 把数据插入到表中，因为不更新任何索引，是以很快。如不雅只想在今后攫取表，应用 myisampack 紧缩它。拜见 15.1.3.3 节，“紧缩表特点”。

三、ON DUPLICATE KEY UPDATE 的应用

用 myisamchk -r -q /path/to/db/tbl_name 从新创建索引。这将在写入磁盘前在内存中创建索引树，并且它更快，因为避免了大年夜量磁盘搜刮。结不雅索引树也被完美地均衡。履行 FLUSH TABLES 语句或 mysqladmin flush-tables 敕令。

请留意如不雅插入一个空 MyISAM 表，LOAD DATA INFILE 也可以履行前面的优化；重要不合处是可以让 myisamchk 为创建索引分派更多的临时内存，比履行 LOAD DATA INFILE 语句时为办事看从新创建索引分派得要多。

也可以应用 ALTER TABLE tbl_name DISABLE KEYS 代替 myisamchk –keys-used=0 -rq/path/to/db/tbl_name，应用 ALTER TABLE tbl_name ENABLE KEYS 代替 myisamchk -r -q/path/to/db/tbl_name。应用这种方法，还可以跳过 FLUSH TABLES。

锁定表可以加快用多个语句履行的 INSERT 操作：

如许机能会进步，因为索引缓存区仅在所有 INSERT 语句完成后刷新稻磁逄上一次。一般有若干 INSERT 语句即竽暌剐若干索引缓存区刷新。如不雅能用一个语句插入所有的行，就不须要锁定。

对于事务表，应应用 BEGIN 和 COMMIT 代替 LOCK TABLES 来加快插入。

锁定也将降低多连接测试的┞符体时光，尽管因为它们等待锁定最大年夜等待时光将上升。例如：

MySQL大年夜数据量快速插入办法和语句优化是本文我们重要要介绍的内容。

Connection 1 does 1000 inserts 
Connections 2, 3, and 4 do 1 insert 
Connection 5 does 1000 inserts

如不雅不应用锁定，2、3 和 4 将在 1 和 5 前完成。如不雅应用锁定，2、3 和 4 将可能不在 1 或 5 前完成，然则整体时光应当快大年夜约 40%。

INSERT、UPDATE 和 DELETE 操作在 MySQL 中是很快的，经由过程为在一行中多于大年夜约 5 次持续赓续地插入或更新的操作加锁，可以获得更好的┞符体机能。如不雅在一行中进行多次插入，可以履行 LOCK TABLES，随后急速履行 UNLOCK TABLES(大年夜约每 1000 行) 以许可其它的线查拜访表。这也会获得好的机能。

INSERT 装载数据比 LOAD DATA INFILE 要慢得多，即使是应用上述的策略。

为了对 LOAD DATA INFILE 和 INSERT 在 MyISAM 表获得更快的速度，经由过程增长 key_buffer_size 体系变量来扩大年夜键高速缓冲区。

1/3 1 2 3 下一页尾页

　　推荐阅读

　　大数据分析42万字的歌词，为了搞清楚民谣歌手们在唱些什么

Tech Neo技巧沙龙 | 11月25号，九州云/ZStack与您一路商量云时代收集界线治理实践听了这么多年平易近谣，我有一种感到，就是很多歌都似曾了解，然则细心一想，又哪一首都想不起来，为了搞>>>详细阅读

本文标题：MySQL该如何进行大数据量快速插入方法和语句优化？

地址：http://www.17bianji.com/lsqh/39158.html

1/2 1