100万数据入mysql过程记录

个人瞎琢磨的,如有不对的地方还望个位看官给我指点一下,拜谢 

表:建表语句如下

CREATE TABLE `push_record_customer` (
  `id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT 'ID',
  `push_task_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '推送任务id',
  `customer_id` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '学员id',
  `is_read` int DEFAULT '0' COMMENT '是否已读（0否1是）',
  `read_time` datetime DEFAULT NULL COMMENT '阅读时间',
  `push_status` int DEFAULT '0' COMMENT 'push推送状态（0-;1推送成功；2推送失败）',
  `push_fail_reason` varchar(36) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT 'push推送失败原因',
  `push_arrive_time` datetime DEFAULT NULL COMMENT 'push送达时间',
  `register_status` int DEFAULT NULL COMMENT '注册状态（1已注册2未注册）',
  `is_push_point` int DEFAULT NULL COMMENT '是否push点击（0否1是）',
  `create_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '创建人',
  `create_time` datetime DEFAULT NULL COMMENT '创建日期',
  `update_by` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL COMMENT '更新人',
  `update_time` datetime DEFAULT NULL COMMENT '更新日期',
  `tenant_id` int DEFAULT NULL COMMENT '商户编号',
  `deleted` int DEFAULT '0' COMMENT '逻辑删除',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC COMMENT='推送记录(学员)表'

 首先来讲,100万级的数据肯定是要使用多线程来插入的,(之前用过for循环+saveBatch()的方式插入100万条数据,直接插了一下午,好几个小时,肯定是不能采用这种方式的)

明确了要是用多线程,现在要知道的是mybatis的saveBatch方法 每次插入多少数据,相对来说效率是比较快的 我这里做了一些插入操作来对比

插入对比

插入数据量	mybatis的saveBatch()插入	在xml中手写sql插入
1000	17159ms	1800ms
5000	88816ms	2582ms
10000	172845ms	4090ms

查阅资料发现,批量操作的时候需要给jdbc链接叫一个参数rewriteBatchedStatements=true

MySQL的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入。

MySQL JDBC驱动在默认情况下会无视executeBatch()语句，把我们期望批量执行的一组sql语句拆散，一条一条地发给MySQL数据库，批量插入实际上是单条插入，直接造成较低的性能。

只有把rewriteBatchedStatements参数置为true, 驱动才会帮你批量执行SQL

另外这个选项对INSERT/UPDATE/DELETE都有效
 

 添加参数后重新测试一下:

插入数据量	添加参数前(saveBatch())	添加参数后(saveBatch())
1000	17159ms	1850ms
5000	88816ms	2261ms
10000	172845ms	2851ms

可以看出添加 rewriteBatchedStatements=true参数后saveBatch()方法与我们自己写的sql插入速率上差不多了.有了明显的提升

现在就是要找到每次插入多少条数据,相对来数,插入的时间最短,

插入数据量	平均耗时(5次)ms	平均每条耗时(ms)
1000	(1807+1578+1622+1856+1576)/5=1688	1.688
2000	(1995+1779+2260+1751+2113)/5=1980	0.994
3000	(1832+2115+2046+1944+1960)/5=1980	0.66
4000	(2206+2275+2036+2127+2624)/5=2254	0.563
5000	(2225+2367+2296+2268+2215)/5=2274	0.455
6000	(2459+2548+2347+2259+2347)/5=2410	0.402
7000	(2347+2806+2480+2467+3166)/5=2653	0.379
8000	(2511+3071+2799+2628+2713)/5=2744	0.343
9000	(2828+3150+2875+3393+2904)/5=3030	0.337
10000	(3046+3064+3299+2972+3101)/5=3096	0.309

这里数量可以加到10万每次,但是在多线程情况下一次插入操作 耗时比较长,可能会导致某些链接等不到cpu时间片而抛出超时关闭异常,所以我觉得还需要保证单次插入时间不能太长.还有就是cpu切换也是消耗资源的,数据库支持的最大连接数等都有影响.所以,线程最好不要开得太大.

我的电脑是i5的四核心的电脑 (每次重新测试我都会清空数据库数据,数据库数据越多后续插入越慢)

测试插入数据数量	每次插入数量	线程数	线程池核心数	时间(s)
1000000	5000	20	40	188
1000000	10000	20	40	189
1000000	20000	20	40	206
1000000	30000	20	40	202
1000000	100000	10	40	211
1000000	5000	50	50	190
1000000	10000	50	50	192
1000000	5000	100	100	193
1000000	10000	100	100	154

100个线程,每次插入10000调数据的时候 出现了链接超时的异常,可以看到我设置的数据库连接池超时时间为100秒(已经很长了),应该是线程太多了,而且每一个线程插入10000调数据的时间太长,导致一部分线程一直拿不到cpu时间片,最终导致了链接超时. 所以线程不是越多越好,每次插入的数据也不是越多越好.

测试过程中还发现一个蛋疼的问题,我本地数据库效率极其低,一个select count(*) 100万居然要一分多钟 ,于是我买了个腾讯云服务器,在服务器上按照默认的配置安装了mysql来进行测试.才算正常了 select count(*)100万到0.3s左右 

结论:插入100万条数据

1.批量操作数据要在jdbc链接添加参数rewriteBatchedStatements=true

2 .每次插入的数据最好不要超过10000/次 

3.开启的线程最好不要超过50(4核心,更多核心的没试过)

4.数据库链接超时的时间修改长一点.(默认是30s)