mysql 大数据量插入遇到瓶颈 可行性方案探究

数据迁移、数据恢复往往都需要做大数据量的插入操作，但是，不同的插入方法对性能的影响也是非常大的

插入数据分析

一次插入多行的值

插入行所需的时间由以下因素决定（参考 MySQL 5.7 参考手册： 8.2.4.1 优化 INSERT 语句）
连接： 30%
向服务器发送查询： 20%
解析查询： 20%
插入行： 10% * 行的大小
插入索引： 10% * 索引数
结束： 10%

插入数据的过程（插入数据都会涉及哪些过程：）

服务器与客户端建立连接，耗时占比最多，大约 30%
发送插入数据到服务器，需要通过网络连接，耗时大约 20%
查询分析，包括对 SQL 语法、数据、权限等校验，耗时大约 20%
插入记录到数据表中，耗时大约 10%
更新索引，耗时大约 10%
关闭服务器与客户端的连接，耗时大约 10%

但是，需要注意，这里所说的是插入一条数据记录，对于大数据量的插入操作来说，插入记录和更新索引肯定是最为耗时的地方

插入数据有哪些常用方法

顺序 INSERT 插入数据：一次执行一条数据记录的插入
批量 INSERT 插入数据：一次执行多条数据记录的插入
LOAD DATA INFILE 插入数据：从文本文件中执行数据记录的插入

顺序 INSERT 插入数据

插入条数 耗时
100 1.195s
1000 12.091s
10000 124.163s

顺序 INSERT 的耗时是线性增长的，随着插入数据量的增大，操作过程是非常缓慢的。这种插入方式无疑是比较慢的（对于软件工程来说，线性和指数增长都是低效的），它所耗时的地方在于：
每一次 INSERT 都需要一次网络 IO
每一次 INSERT 都需要一次 SQL 语句解析
每一次 INSERT 都需要一次数据写入（先写入缓冲区，再刷写到磁盘）
每一次 INSERT 都需要一次索引的更新
每一次 INSERT 都可能需要多次日志记录过程（查询日志、 Binlog 日志等等）
每一次 INSERT 都需要一次事务的创建与提交

批量 INSERT 插入数据

INSERT INTO `worker` (`type`, `name`, `salary`, `version`)
VALUES ('B', 'H', 2000, 1), ('C', 'I', 3000, 0), ('C', 'L', 6000, 0);

但是，如果想要通过 INSERT 一次性插入大批量的数据，就需要去考虑 MySQL 中的 max_allowed_packet 参数。
这个参数会限制 MySQL 服务器接受的数据包大小，如果超过这个值时会导致大批量数据写入或更新失败。我们可以把它设置为一个比较大的值，例如：

--  设置服务器最大接受的数据包是 200M ，且生效范围是 GLOBAL
mysql> SET GLOBAL max_allowed_packet = 2 * 100 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)


--  检验变量的值是否符合预期（需要退出当前 session ，重新登录）
mysql> SHOW VARIABLES LIKE 'max_allowed_packet';
+--------------------+-----------+
| Variable_name | Value |
+--------------------+-----------+
| max_allowed_packet | 209715200 |
+--------------------+-----------+
1 row in set (0.00 sec)

批量插入耗时分析
插入条数 耗时
100 0.033s
1000 0.051s
10000 0.504s
其实，只要对照顺序插入而言，批量插入还是非常好理解的。这种方式效率高的原因主要是合并了日志、写入次数、网络往返 IO 以及事务，它将多次的执行过程变成了一次执行过程。主要的耗时是客户端到服务器的大数据量传输以及数据写入（表数据以及表索引）。正是由于批量执行的高效性，它也成为企业级开发中普遍采用的方式。

LOAD DATA INFILE 插入数据

MySQL 官方对 LOAD DATA INFILE 的描述是：用于高速的从一个文本文件中读取行，并把它写入数据表中。如果我们不想编写代码，且恰好手边有这样的一份文本文件，可以尝试考虑这种方式去完成大批量的数据插入。

通过对比三种大批量数据插入的方式，我们能够确定每一种方式的优劣性，当然也就能够在需要的时候做出合适的选择。对于大数据量插入需求来说，顺序插入不仅会影响客户端的性能，同样会给服务器带来很大的压力；批量插入和 LOAD DATA INFILE 都具有很高的性能，使用它们其中的任何一种都是可行的，只是需要考虑工作重心的问题。最后，还要好好把握每一种数据插入方式性能损耗的地方，也就是即使很慢，也要知道慢的原因。

常见 问题：
你做过大批量数据插入吗 ？是使用哪一种方式完成的呢 ？
对于批量 INSERT 过程，可以考虑使用多线程去完成吗 ？你觉得性能会有提升吗 ？
你能总结三种数据插入方式耗时的地方吗 ？
如果你遇到了大数据量插入的需求，你会选择使用什么方法呢 ？为什么 ？

如何动手实现一个关系型数据库系统？
单纯看书其实效果不大，对于数据库的每一个部分，书中讲述了诸多实现方案，如果可以配合Project来做，理解的程度会加深很多，对于书中提到的方法取舍自己心中也会有数，mit就出了一个教你造数据库Project，感觉很爽。

资料：

1. 本书。数据库系统实现 加西亚-莫利纳(Hector Garcia-Molina) / Jeffrey D.Ullman / Jennifer Widom

2. https://courses.cs.washington.edu/courses/cse444/19sp/ 的Lectures，这是UW关于该课程的ppt，强烈推荐，通俗易懂。

3. https://ocw.mit.edu/courses/electrical-engineering-and-computer-science/6-830-database-systems-fall-2010/assignments/ 这里的Lab就是教你一步步实现一个简单的关系型数据库系统，不仅要写完代码，而且要对整个系统的方方面面自己要梳理一遍，哪些模块用了书上的哪些方法，这些方法有什么优缺点，以及它的替代方法有哪些，以及优缺点。

4. https://courses.cs.washington.edu/courses/cse444/12sp/ 该链接的homework里也是一样的lab，不过扩展了并行数据库部分，有兴趣的同学可以尝试。

5. https://www.icourse163.org/course/HIT-1001578001 如果喜欢视频的同学可以参考，感觉就是专门对着本书讲的。

建议：这些资料混着看更有效果，例如做Lab1的内容，把上边说的书、ppt、视频对应的内容看看，基本就可以融会贯通了。

本Lab需要背景：

1. 上过数据库基础课程，知道基本的SQL语法，写过一些SQL语句。

2. 上过操作系统，知道进线程、内存、文件、磁盘块等基本概念。

3. 上过Java课，知道基本的Java语法，c++也可以，关键是要有面向对象的概念。

如果没有这些背景，那么做的过程中会比较难受，当然现学也可以，但是如果有这些背景就更好了，学习就是要踏踏实实地来，在良好的基础上学习会比较快，能吸收的也多。