MySQL插入一亿条数据

最新推荐文章于 2024-07-13 10:44:00 发布

释然于心

最新推荐文章于 2024-07-13 10:44:00 发布

阅读量2.1k

点赞数 1

本文链接：https://blog.csdn.net/qq_25138909/article/details/103781012

版权

1、创建数据表

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

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、插入数据测试统计
测试图片
测试结果在这里插入图片描述
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