MySQL插入一亿条数据

1. 创建数据表

use test;
CREATE TABLE `large_user` (
  `id` bigint(20) DEFAULT NULL,
  `name` varchar(64) DEFAULT NULL,
  `age` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. 创建用户表

use test;
CREATE TABLE `large_user_log` (
  `id` int(11) DEFAULT NULL,
  `msg` varchar(1000) DEFAULT NULL COMMENT '提交信息记录'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3. 创建存储过程

DELIMITER ;;
CREATE PROCEDURE `insert_large_user`(id_begin INT, id_end INT)
BEGIN

DECLARE i int;
SET i = id_begin;
SET AUTOCOMMIT = 0;

WHILE i >= id_begin && i <= id_end DO
    INSERT INTO large_user(id, name, age) VALUES (i, concat('user_', i % 100000), i % 100);

    SET i = i + 1;
    IF MOD(i, 100000) <=0 THEN
        INSERT INTO large_user_log (id, msg) VALUES(i, 'ready to commit');
        COMMIT;
    END IF;
END WHILE;

END;;
DELIMITER ;

4. 调用存储过程插入一亿条数据

show variables like 'sql_log_bin'
set sql_log_bin = 0;
call insert_large_user(1, 100000000);
set sql_log_bin = 1;

5. 查看表空间

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.tables where table_schema='test' and table_name='large_user';

6. 插入数据测试统计

	插入耗时	数据大小
1千万条数据	约30mins	约400MB
1亿条数据	约6hours	约4GB

7. 查询测试

# 无索引查询
mysql root@localhost:test> select * from large_user where id = 10086;
+-------+-----------+-----+
| id    | user_name | age |
+-------+-----------+-----+
| 10086 | admin     | 22  |
+-------+-----------+-----+
1 row in set
Time: 1428.979s

# 有索引查询
# 一亿条数据索引建立时间：447s ≈ 8min
mysql root@localhost:test> select * from large_user where id = 10086;
+-------+-----------+-----+
| id    | user_name | age |
+-------+-----------+-----+
| 10086 | admin     | 22  |
+-------+-----------+-----+
1 row in set
Time: 0.043s