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

　　如果你打算好好学习一下MySQL，性能优化肯定是绕不过去一个问题。当你撸起袖子准备开始的时候，突然发现一个问题摆在眼前，本地数据库中没那么大的数据量啊，几条数据优化个毛线啊。生产库里数据多，但谁敢直接在生产环境动手啊，想被提前优化吗？
　　要知道，程序员从不轻言放弃，没有数据我们就自己创造数据嘛，new对象这种事情可是我们的拿手好戏，对象都能new出来，更别说几百万条数据了。使用官方数据
　　官方显然知道我们需要一些测试数据做个练习什么的，所以准备了一份测试数据给我们。可以到https：github。comdatacharmertestdb上去下载，这个数据库包含约30万条员工记录和280万个薪水条目，文件大小为167M。
　　下载完成之后，直接使用MySQL客户端运行sql文件即可。
　　或者直接使用命令，然后输入密码导入。mysqlurootpemployees。sql
　　这是最简单的一种方法，只要你能把sql文件下载下来就可以了。但是数据量不够大，员工表才30万条数据，还不够百万级别，而且字段都是定义好的，不能灵活定制。背景说明
　　创建百万级数据的方式，要到达的目的有两点：定制比较灵活，不能只是一两个字段了事，那没什么实际意义。速度快，不能说弄个几百万数据好几个小时甚至更长，那不能接收。
　　本次目标是创建两个表，一个用户表，另外一个订单表，当然没有真实环境中的表字段那么多，但是对于学习测试来说差不多够了。
　　两个表的表结构如下：用户表CREATETABLEuser（idvarchar（36）NOTNULL，usernamevarchar（12）DEFAULTNULL，agetinyint（3）DEFAULTNULL，phonevarchar（11）DEFAULTNULL，provincevarchar（10）DEFAULTNULL，cityvarchar（10）DEFAULTNULL，createtimedatetimeDEFAULTNULL，updatetimedatetimeDEFAULTNULL，PRIMARYKEY（id））ENGINEInnoDBDEFAULTCHARSETutf8mb4；订单表复制代码CREATETABLEorder（idvarchar（36）NOTNULL，useridvarchar（36）DEFAULTNULL，productcountint（11）DEFAULTNULL，pricedecimal（10，0）DEFAULTNULL，createtimedatetimeDEFAULTNULL，updatetimedatetimeDEFAULTNULL，PRIMARYKEY（id））ENGINEInnoDBDEFAULTCHARSETutf8mb4
　　用户表（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支持一次性插入多条记录，就是下面这样的形式。insertintotablename（id，column1）values（1，value1），（2，value2），（3，value3）；
　　这样一来，比你一条一条语句执行要快很多，比如1000条记录执行一次insert，一共执行5000次即可，如果是一条一条插入呢，那就要执行500万次。
　　由于后面两种方式用到了Python生成文件，所以这种方式也用了Python实现，实例代码如下。完整代码可在文末给出的github上获取。definsertdata（self）：cursorself。conn。cursor（）forxinrange（5000）：insertusersqlinsertintouser（id，username，phone，age，province，city，createtime，updatetime）VALUES（s，s，s，s，s，s，s，s）insertordersqlinsertintoorder（id，productcount，userid，price，createtime，updatetime）values（s，s，s，s，s，s）uservalues，ordervalues〔〕，〔〕foriinrange（1000）：timestampself。randomTimestamp（）timelocaltime。localtime（timestamp）createTimetime。strftime（YmdH：M：S，timelocal）useridstr（uuid。uuid4（））uservalues。append（（userid，名字str（x）str（i），self。createPhone（），random。randint（1，120），str（random。randint（1，26）），str（random。randint（1，1000）），createTime，createTime））randomordercountrandom。randint（0，3）ifrandomordercount0：forcinrange（randomordercount）：timestampself。randomTimestamp（）timelocaltime。localtime（timestamp）ordercreatetimetime。strftime（YmdH：M：S，timelocal）ordervalues。append（（str（uuid。uuid4（）），random。randint（1，5），userid，random。randint（10，2000），ordercreatetime，ordercreatetime））cursor。executemany（insertusersql，uservalues）cursor。executemany（insertordersql，ordervalues）self。conn。commit（）cursor。close（）
　　经过一段时间时间的等待后，运行完成了，整个运行过程耗时1823秒，30分钟。
　　最后成功生成用户记录500万条，订单记录749万多条。
　　速度还算能接受吧，马马虎虎吧。
　　再想速度快一点，可以开多线程，我用5个线程跑了一下，一个线程插入100万条，最终最长的线程耗时1294秒，21分钟，也没快多少，线程个数对时间多少有些影响，但是我没有试。
　　生成SQL脚本
　　这种方式和上面的方式类似，只不过上面通过程序方式直接将拼接出来的SQL语句执行了，而这种方式是将拼接好的SQL语句写入文件中。当然还是以一条语句插入多行记录的形式。insertintotablename（id，column1）values（1，value1），（2，value2），（3，value3）；
　　写500万用户数据，加上随机的订单数据，sql文件的过程耗时为696秒，11分钟左右。
　　当然这么大数据量拼接出来的脚本文件也很大，用户表脚本680多M，订单表脚本1个G。
　　最后将写好的这两个文件分别在MySQL中执行。
　　执行用户表脚本，耗时3分钟左右。mysqlurootpmastslavesqlinsertuser500w。sql
　　执行订单表脚本，耗时7分钟左右，订单量750多万个。mysqlurootpmastslavesqlinsertorder500w。sql
　　一共耗时，20分钟左右，加上中间的手工操作，感觉不如第一种方法中的多线程方式省事。loaddatainfile方式
　　最后这种方式是使用loaddatainfile方式，这是MySQL提供的一种从文件快速导入的方式。比如按照特定符号分隔，导入对应的字段中。
　　本文例子中我是按照逗号分隔的，字段之间以逗号分隔，生成500万条用户行和随机订单行。
　　依然是用Python脚本生成文件，生成文件的过程耗时779秒，12分钟左右。
　　两个文件大小分别是560多M和900M。
　　最后执行loaddatainfile将文件导入到对应的表中，在执行这个命令后可能会出现下面这个错误提示。
　　ERROR1290（HY000）：TheMySQLserverisrunningwiththesecurefileprivoptionsoitcannotexecutethisstatement
　　这是因为MySQL自身的安全配置所致，需要更改my。cnf，在其中加入下面的配置，然后重启服务。securefilepriv
　　等于号后边为空表示允许所有目录下的文件load，如果要限定某个特定目录，在等于号后边填上对应的文件目录即可。
　　然后执行下面的语句，将用户记录导入到user表。loaddatainfileUsersfengzheng知识管理技术写作mysql创建测试数据sqlloadusertxt500w。txtreplaceintotableuserFIELDSTERMINATEDBY，；
　　500万条耗时3分32秒。
　　将订单记录导入到order表。loaddatainfileUsersfengzheng知识管理技术写作mysql创建测试数据sqlloadordertxt500w。txtreplaceintotableorderFIELDSTERMINATEDBY，；
　　749万条记录，耗时8分31秒。
　　整个过程加起来24分钟左右。最后
　　好了，现在可以愉快的做各种测试和优化了。
　　有同学看完可能要说了，20多分钟好像也不算快啊。因为数据量确实比较大，再有数据复杂度和导入时间也有很大关系，如果你只是导入一列自增id，别说500万，1000万都用不了一分钟就完成了。
　　其实还有一点优化空间的，比如说把数据库引擎改成MYISAM会更快一些，尤其是对于批量插入的情景，但是插入完成后还要再改回来，也需要耗费一些时间，而且来回切换也比较麻烦。
　　上面的几种方法都配合了Python脚本，当然你可以换成自己熟悉的语言，比如Java，或者直接写bash脚本也可以。
　　作者：古时的风筝
　　链接：https：juejin。impost6858042078221074445