MySQL如何快速创建800w条测试数据表

2024-10-02 04:31:37

一、数据插入思路

如果一条一条插入普通表的话，效率太低下，但内存表插入速度是很快的，可以先建立一张内存表，插入数据后，在导入到普通表中。

1、创建内存表

 View Code

2、创建普通表

普通表参数设置和内存表相同，否则从内存表往普通标导入数据会报错。

 View Code

3、创建存储函数

产生伪随机码user_id 要用到存储函数。

 View Code

4、创建存储过程

存储过程是保存起来的可以接受和返回用户提供的参数的Transact-SQL 语句的集合，可以创建一个过程供永久使用。

 View Code

5、调用存储过程

call 就是调用存储过程或者函数，这里调用存储过程1000000次

CALL add_vote_memory(1000000)

6、导入数据

将内存表中的数据导入普通表。

INSERT into vote_record SELECT * from vote_record_memory

7、内存不足

如果报错内存满了，报错信息如下：

 CALL add_vote_memory(1000000)
> 1114 - The table 'vote_record_memory' is full
 > 时间: 74.61s

则可以使用命令查看内存表和临时表允许写入的最大值：

show variables like '%%table_size%'

MySQL默认16M：

修改默认内存配置：

set session tmp_table_size=1024*1024*1024; 
set session max_heap_table_size=1024*1024*1024;

配置修改后，再执行上述调用存储过程和数据导入步骤。

8、查看结果

查看记录，是否有插入100W条数据。

select count(*) AS total from vote_record

9、插入800W条数据

测试插入800W条数据，call 调用存储过程800W次。

查看结果：

800W条测试数据插入OK，想插入多少条测试数据就调用n次存储过程，CALL add_vote_memory(n)。

二、MySQL深度分页

所谓的深度分页问题，涉及到mysql分页的原理。通常情况下，mysql的分页是这样写的：

select id, user_id, vote_id, group_id from vote_record limit 200, 10

SQL意思就是从vote_reccord 表里查200到210这10条数据即【201,210】，mysql会把前210条数据都查出来，抛弃前200条，返回10条。当分页所以深度不大的时候当然没问题，随着分页的深入，sql可能会变成这样：

select id, user_id, vote_id, group_id from vote_record limit 7999900, 10

这个时候，mysql会查出来7999920条数据，抛弃前7999900条，如此大的数据量，速度一定快不起来。

那如何解决呢？一般情况下，最简单的方式是增加一个条件，利用表的覆盖索引来加速分页查询：

select id, user_id, vote_id, group_id from vote_record where id > 7999900 limit 10

我们都知道，利用了索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。

因为利用索引查找有优化算法，且数据就在查询索引上面，不用再去找相关的数据地址了，这样节省了很多时间。上述vote_record 表的id字段是主键，自然就包含了默认的主键索引，这样，mysql会走主键索引，直接连接到7999900处，然后查出来10条数据。但是这个方式需要接口的调用方配合改造，把上次查询出来的最大id以参数的方式传给接口提供方，会有一定沟通成本。

1、测试深度分页

优化前，查询耗时2.362s，随着数据的增大耗时会更多，limit语句的查询时间与起始记录的位置成正比。

优化后，耗时0.012s，性能提升了196.8倍。

到此这篇关于MySQL如何快速创建800w条测试数据表的文章就介绍到这了,更多相关MySQL创建测试数据表内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！

在Mysql上创建数据表实例代码

复制代码代码如下: create table shujubiao( id int primary key auto_increment, 指定为i整形 name varchar(32) not null, 指定为不固定长度,最大为32为字符,不能为空 password varchar(64) not null, 指定为不固定长度,最大为64为字符,不能为空 email varchar(128) not null, 指定为不固定长度,最大为128为字符,不能为空 age tinyint unsi
mysql创建存储过程实现往数据表中新增字段的方法分析

本文实例讲述了mysql创建存储过程实现往数据表中新增字段的方法.分享给大家供大家参考,具体如下: 需求: 往某数据库的某个表中新增一个字段(若该字段已存在,则不做操作:若该字段不存在,则新增) 百度了n久,没有符合要求的例子,只有参考加自己琢磨,最终终于给弄出来了,以下是几个版本的更迭第一版: DELIMITER $$ CREATE PROCEDURE insert_column() BEGIN IF NOT EXISTS(SELECT 1 FROM information_schema.c
MySQL创建和删除数据表的命令及语法详解

创建数据表创建MySQL数据表需要以下信息: 表名表字段名定义每个表字段语法以下为创建MySQL数据表的SQL通用语法: CREATE TABLE table_name (column_name column_type); 以下例子中我们将在 TUTORIALS 数据库中创建数据表tutorials_tbl: tutorials_tbl( tutorial_id INT NOT NULL AUTO_INCREMENT, tutorial_title VARCHAR(100) NOT N
mysql中数据库与数据表编码格式的查看、创建及修改

一.查看数据库编码格式 mysql> show variables like 'character_set_database'; 二.查看数据表的编码格式 mysql> show create table <表名>; 三.创建数据库时指定数据库的字符集 mysql>create database <数据库名> character set utf8; 四.创建数据表时指定数据表的编码格式 create table tb_books ( name varchar(45
MySQL创建数据表时设定引擎MyISAM/InnoDB操作

我在配置mysql时将配置文件中的默认存储引擎设定为了InnoDB.今天查看了MyISAM与InnoDB的区别,在该文中的第七条"MyISAM支持GIS数据,InnoDB不支持.即MyISAM支持以下空间数据对象:Point,Line,Polygon,Surface等." 作为一个地理信息系统专业的学生(其实是测绘专业)来讲,能存储空间数据的数据库才是好数据库,原谅我是数据库小白的身份. 有三种方式可以设定数据库引擎: (1)修改配置文件将安装目录下~\MySQL\mysql-5.6
MySQL创建数据表并建立主外键关系详解

前言为mysql数据表建立主外键需要注意以下几点: 需要建立主外键关系的两个表的存储引擎必须是InnoDB. 外键列和参照列必须具有相似的数据类型,即可以隐式转换的数据类型. 外键列和参照列必须创建索引,如果外键列不存在索引,mysql将自动创建索引. 一.SQL语句创建数据表并设置主外键关系 create table demo.ChineseCharInfo ( ID int not null auto_increment, Hanzi varchar(10) not null, prima
MySQL如何快速创建800w条测试数据表

目录一.数据插入思路 1.创建内存表 2.创建普通表 3.创建存储函数 4.创建存储过程 6.导入数据 7.内存不足 8.查看结果 9.插入800W条数据二.MySQL深度分页 1.测试深度分页一.数据插入思路如果一条一条插入普通表的话,效率太低下,但内存表插入速度是很快的,可以先建立一张内存表,插入数据后,在导入到普通表中. 1.创建内存表 View Code 2.创建普通表普通表参数设置和内存表相同,否则从内存表往普通标导入数据会报错. View Code 3.创建存储函数产
mysql中迅速插入百万条测试数据的方法

对比一下,首先是用 mysql 的存储过程弄的: 复制代码代码如下: mysql>delimiter $ mysql>SET AUTOCOMMIT = 0$$ mysql> create procedure test() begin declare i decimal (10) default 0 ; dd:loop INSERT INTO `million` (`categ_id`, `categ_fid`, `SortPath`, `address`, `p_identifier`
Innodb中mysql快速删除2T的大表方法示例

前言本文主要给大家介绍了关于Innodb中mysql快速删除2T的大表的相关内容,分享出来供大家参考学习,下面话不多说了,来一起看看详细的介绍吧来,先来看小漫画陶冶一下情操 OK,这里就说了.假设,你有一个表erp,如果你直接进行下面的命令 drop table erp 这个时候所有的mysql的相关进程都会停止,直到drop结束,mysql才会恢复执行.出现这个情况的原因就是因为,在drop table的时候,innodb维护了一个全局锁,drop完毕锁就释放了. 这意味着,如果在白天,访
MySQL如何快速修改表的表结构

快速修改MySQL某张表的表结构--摘录自<MySQL管理之道> ALTER TABLE 表名 MODIFY 列名数据类型; 这个命令可以修改表结构此外,也可以如下方法修改表结构: 先创建一张表,如下: > create table t1 (id int, name varchar(5), rmb decimal(9,1)); 如果要修改name列为varchar(10)的,可以这样操作: alter table t1 modify name varchar(7); 也可以如下操作:
Mysql快速插入千万条数据的实战教程

一.创建数据库二.创建表 1.创建 dept表 CREATE TABLE `dept` ( `id` int(11) NOT NULL, `deptno` mediumint(9) DEFAULT NULL, `dname` varchar(20) DEFAULT NULL, `loc` varchar(13) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 2.创建emp表 CREATE TABLE
MySQL如何快速批量插入1000w条数据

听说有个面试题是: 如何快速向mysql中插入1000w条数据? 我私下试了一下, 发现插入10000条数据用了0.9s, 插入10w条数据用了4.7s, 插入100w条数据用了58s左右,1000w条数据,我的笔记本吭哧了5分钟,自己停了, 心中1000w只草泥马呼啸而过,我用的是下面的代码: -- 进入数据库 use test; -- 显示所有表 show tables; -- 创建majors表 create table majors(id int, major varchar(255))
MySQL快速插入一亿测试数据

目录 1.建表 1.1 建立测试表 t_user 1.2 创建临时表 2.生成数据 2.1 用 python生成 [一亿] 记录的数据文件(这个确实稍微花点时间) 2.2 将生成的文件导入到临时表tmp_table中 3.以临时表为基础数据,插入数据到t_user中 4.参考 1.建表 1.1 建立测试表 t_user CREATE TABLE `t_user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `c_user_id` varchar(36) NOT
MySQL存储过程的创建使用以及实现数据快速插入

目录一,存储过程介绍二,存储过程的优缺点三,存储过程的创建与调用 3.1,存储过程中的常用语法及参数 3.2,存储过程的使用四,存储过程中的变量及使用细则 4.1,变量定义 4.2,变量赋值 4.3,用户变量的使用 4.4,存储过程的一些常用查看命令五,常用的存储过程的控制语句 5.1,条件语句 5.1.1,if-then-else 5.1.2,case语句 5.2,循环语句 5.2.1,while ……end while语句 5.2.2,repeat……end repeat语句 5.
MySQL实现快速删除所有表而不删除数据库的方法

本文实例讲述了MySQL实现快速删除所有表而不删除数据库的方法.分享给大家供大家参考,具体如下: 如果直接使用phpmyadmin操作的话肯定非常简单,勾选数据表->点击删除->点击确定,操作完毕! 这里介绍一下快速删除数据表的SQL命令操作方法. 删除表的命令: drop table 表名; 如果有200张表,执行200次,想想就不想动手了. 下面提供一个使用information_schema库的方案: 复制代码代码如下: SELECT CONCAT('drop table ',tabl
MySQL入门教程（五）之表的创建、修改和删除

MySQL 为关系型数据库(Relational Database Management System), 这种所谓的"关系型"可以理解为"表格"的概念, 一个关系型数据库由一个或数个表格组成. 表是DB存储数据的基本单位,一个表包含若干个字段或记录.表的操作包括创建.修改和删除. 1.创建表创建表即在已存在的数据库中创立新表.在使用USE语句选择具体数据库后,可以使用它SQL语句创建表其中,属性名指表中字段的名称,数据类型即对应字段的数据类型,完整性约束条件指