快速创建MySQL五百万级数据，愉快的学习各种优化技巧

　　如果你打算好好学习一下 MySQL，性能优化肯定是绕不过去一个问题。当你撸起袖子准备开始的时候，突然发现一个问题摆在眼前，本地数据库中没那么大的数据量啊，几条数据优化个毛线啊。生产库里数据多，但谁敢直接在生产环境动手啊，想被提前优化吗？
　　要知道，程序员从不轻言放弃，没有数据我们就自己创造数据嘛，new 对象这种事情可是我们的拿手好戏，对象都能 new 出来，更别说几百万条数据了。使用官方数据
　　官方显然知道我们需要一些测试数据做个练习什么的，所以准备了一份测试数据给我们。可以到 https://github.com/datacharmer/test_db 上去下载，这个数据库包含约30万条员工记录和280万个薪水条目，文件大小为 167 M。
　　下载完成之后，直接使用 MySQL 客户端运行 sql 文件即可。
　　或者直接使用命令，然后输入密码导入。mysql -u root -p < employees.sql
　　这是最简单的一种方法，只要你能把 sql 文件下载下来就可以了。但是数据量不够大，员工表才 30 万条数据，还不够百万级别，而且字段都是定义好的，不能灵活定制。背景说明
　　创建百万级数据的方式，要到达的目的有两点：定制比较灵活，不能只是一两个字段了事，那没什么实际意义。速度快，不能说弄个几百万数据好几个小时甚至更长，那不能接收。
　　本次目标是创建两个表，一个用户表，另外一个订单表，当然没有真实环境中的表字段那么多，但是对于学习测试来说差不多够了。
　　两个表的表结构如下：# 用户表 CREATE TABLE `user` (   `id` varchar(36) NOT NULL,   `user_name` varchar(12) DEFAULT NULL,   `age` tinyint(3) DEFAULT NULL,   `phone` varchar(11) DEFAULT NULL,   `province` varchar(10) DEFAULT NULL,   `city` varchar(10) DEFAULT NULL,   `create_time` datetime DEFAULT NULL,   `update_time` datetime DEFAULT NULL,   PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; 订单表 复制代码CREATE TABLE order ( id varchar(36) NOT NULL, user_id varchar(36) DEFAULT NULL, product_count int(11) DEFAULT NULL, price decimal(10,0) DEFAULT NULL, create_time datetime DEFAULT NULL, update_time datetime DEFAULT NULL, PRIMARY KEY (id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
　　用户表（user）创建 500 万条数据，id 使用 uuid，年龄从 1 到 120 随机，电话号码随机 11 位，省份编码和城市编码随机，创建时间和更新时间在某一时间范围内随机。
　　订单表（order）根据用户表生成，每个用户随机生成 0 到 3 个订单，订单编号采用 uuid，商品数量随机 1 到 5 个，价格随机，创建时间和更新时间在某一时间段内随机。由于每个用户产生 0 到 3 个订单，所以，产生的订单量应该大于 500 万，我在本地跑的时候基本上在 700多万左右。
　　创建总时间和表的字段个数以及字段的生成算法有直接关系，字段越多、算法越复杂，需要的时间就越多，比如使用 uuid 就比使用自增 id 花费更长时间，随机时间就比直接使用当前时间花费更长时间。
　　如果只插入 500 万自增 id 这一个字段，十几秒就能完成，但是无论是模拟线上环境还是自学性能优化技巧都没什么意义。
　　下面就来介绍三种方式来快速创建 500 万用户数据以及大于 500 万的订单数据。写程序批量插入
　　作为一个开发人员，当你打算创建百万条数据的时候，大多数时候首先相当的应该就是写程序，毕竟 CURD 我们最拿手了。
　　用程序的方式插入也分两种情况，第一种就是逐条插入，这也是平时开发中最常用到的方法，直觉上我们可能会认为这样比较快。事实上并不是这样，虽然比起手动一条一条插入是快的多，但是，很有可能你在等待了一段时间后失去耐心，然后结束程序，不管你用哪种数据库连接池都一样，在百万数量级面前仍然慢的离谱。
　　第二种情况就是使用 MySQL 的批量插入方法，我们都知道 MySQL 支持一次性插入多条记录，就是下面这样的形式。insert into `table_name` (id,column1) values (1,＂value1＂),(2,＂value2＂),(3,＂value3＂);
　　这样一来，比你一条一条语句执行要快很多，比如 1000 条记录执行一次 insert，一共执行 5000 次即可，如果是一条一条插入呢，那就要执行 500 万次。
　　由于后面两种方式用到了 Python 生成文件，所以这种方式也用了 Python 实现，实例代码如下。完整代码可在文末给出的 github 上获取。def insert_data(self):   cursor = self.conn.cursor()   for x in range(5000):     insert_user_sql = ＂＂＂             insert into `user` ( `id`,`user_name`,`phone`,`age`, `province`, `city`, `create_time`,`update_time` )                     VALUES(%s,%s,%s,%s,%s,%s,%s,%s)                 ＂＂＂     insert_order_sql = ＂＂＂ insert into `order` ( `id`, `product_count`, `user_id`, `price`, `create_time`, `update_time`)                                 values(%s,%s,%s,%s,%s,%s)                                ＂＂＂     user_values, order_values = [], []     for i in range(1000):       timestamp = self.randomTimestamp()       time_local = time.localtime(timestamp)       createTime = time.strftime(＂%Y-%m-%d %H:%M:%S＂, time_local)       user_id = str(uuid.uuid4())       user_values.append(         (user_id, ＂名字＂ + str(x) + str(i), self.createPhone(), random.randint(1, 120),          str(random.randint(1, 26)),          str(random.randint(1, 1000)), createTime, createTime))        random_order_count = random.randint(0, 3)       if random_order_count > 0:         for c in range(random_order_count):           timestamp = self.randomTimestamp()           time_local = time.localtime(timestamp)           order_create_time = time.strftime(＂%Y-%m-%d %H:%M:%S＂, time_local)           order_values.append((str(uuid.uuid4()), random.randint(1, 5), user_id,                                random.randint(10, 2000), order_create_time, order_create_time))           cursor.executemany(insert_user_sql, user_values)           cursor.executemany(insert_order_sql, order_values)           self.conn.commit()            cursor.close()
　　经过一段时间时间的等待后，运行完成了，整个运行过程耗时 1823 秒，30分钟。
　　最后成功生成用户记录 500 万条，订单记录 749 万多条。
　　速度还算能接受吧，马马虎虎吧。
　　再想速度快一点，可以开多线程，我用 5 个线程跑了一下，一个线程插入 100万条，最终最长的线程耗时 1294秒，21分钟，也没快多少，线程个数对时间多少有些影响，但是我没有试。
　　生成 SQL 脚本
　　这种方式和上面的方式类似，只不过上面通过程序方式直接将拼接出来的 SQL 语句执行了，而这种方式是将拼接好的 SQL 语句写入文件中。当然还是以一条语句插入多行记录的形式。insert into `table_name` (id,column1) values (1,＂value1＂),(2,＂value2＂),(3,＂value3＂);
　　写 500 万用户数据，加上随机的订单数据， sql 文件的过程耗时为 696 秒，11分钟左右。
　　当然这么大数据量拼接出来的脚本文件也很大，用户表脚本 680 多M，订单表脚本 1个G。
　　最后将写好的这两个文件分别在 MySQL 中执行。
　　执行用户表脚本，耗时 3 分钟左右。mysql -uroot -p mast_slave < sql/insert_user_500w.sql
　　执行订单表脚本，耗时 7 分钟左右，订单量 750 多万个。mysql -uroot -p mast_slave < sql/insert_order_500w+.sql
　　一共耗时，20分钟左右，加上中间的手工操作，感觉不如第一种方法中的多线程方式省事。load data infile 方式
　　最后这种方式是使用 load data infile 方式，这是 MySQL 提供的一种从文件快速导入的方式。比如按照特定符号分隔，导入对应的字段中。
　　本文例子中我是按照逗号分隔的，字段之间以逗号分隔，生成 500 万条用户行 和随机订单行。
　　依然是用 Python 脚本生成文件，生成文件的过程耗时 779 秒，12分钟左右。
　　两个文件大小分别是 560 多M 和 900 M。
　　最后执行 load data infile 将文件导入到对应的表中，在执行这个命令后可能会出现下面这个错误提示。
　　ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
　　这是因为 MySQL 自身的安全配置所致，需要更改 my.cnf，在其中加入下面的配置，然后重启服务。secure_file_priv=
　　等于号后边为空表示允许所有目录下的文件 load，如果要限定某个特定目录，在等于号后边填上对应的文件目录即可。
　　然后执行下面的语句，将用户记录导入到 user 表。load data infile ＂/Users/fengzheng/知识管理/技术写作/mysql/创建测试数据/sql/load_user_txt_500w.txt＂ replace into table user FIELDS TERMINATED BY ＂,＂;
　　500万条耗时 3分32秒。
　　将订单记录导入到 order 表。load data infile ＂/Users/fengzheng/知识管理/技术写作/mysql/创建测试数据/sql/load_order_txt_500w+.txt＂ replace into table `order` FIELDS TERMINATED BY ＂,＂;
　　749 万条记录，耗时 8分31秒。
　　整个过程加起来 24 分钟左右。最后
　　好了，现在可以愉快的做各种测试和优化了。
　　有同学看完可能要说了，20多分钟好像也不算快啊。因为数据量确实比较大，再有数据复杂度和导入时间也有很大关系，如果你只是导入一列自增id，别说 500 万，1000万都用不了一分钟就完成了。
　　其实还有一点优化空间的，比如说把数据库引擎改成 MYISAM 会更快一些，尤其是对于批量插入的情景，但是插入完成后还要再改回来，也需要耗费一些时间，而且来回切换也比较麻烦。
　　上面的几种方法都配合了 Python 脚本，当然你可以换成自己熟悉的语言，比如 Java，或者直接写 bash 脚本也可以。
　　作者：古时的风筝
　　链接：https://juejin.im/post/6858042078221074445