创建还量表 · 个人笔记

mysql3范式 1NF：表的设计首先要满足第一范式（1NF），第一范式的要求就是表的列具有原子性，也就是列不能再分割，因为关系型数据库本身就符合列的原子型，所以MYSQL天然满足第一范式。 2NF：第二范式就是表中不能有完全重复的两条记录，一般情况我们通过设置主键自增长来满足第二范式。 3NF：第三范式要求列之间不存在依赖关系，A列不能显示或者隐式的被其他列推导出来，如果A列能够被推导出来就不要单独存放这一列。比如产品单价乘以销量能得到总金额，那么总金额就不要用一列单独存放。如果不符合第三范式就会数据冗余。创建海量表步骤： 1，创建表部门表 ~~~ create table dept( deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, dname VARCHAR(20) NOT NULL DEFAULT '', loc varchar(13) not null default '' ) engine = myisam default charset = utf8; ~~~ 雇员表 ~~~ create TABLE emp ( empno MEDIUMINT UNSIGNED not null DEFAULT 0, ename VARCHAR(20) not null DEFAULT '', job VARCHAR(9) not null DEFAULT '', mgr MEDIUMINT UNSIGNED not null DEFAULT 0, hiredate DATE NOT NULL, sal DECIMAL(7,2) NOT NULL, comm DECIMAL(7,2) NOT NULL, dpetno MEDIUMINT UNSIGNED not null DEFAULT 0 )ENGINE=MyISAM DEFAULT CHARSET=utf8; ~~~ 工资级别表 ~~~ create TABLE salgrade ( grade MEDIUMINT UNSIGNED not null DEFAULT 0, losal DECIMAL(17,2) NOT NULL, hisal DECIMAL(17,2) NOT NULL )ENGINE=MyISAM DEFAULT CHARSET=utf8; ~~~ 测试数据 ~~~ insert into salgrade values(1, 700, 1200); insert into salgrade values(2, 1201, 1400); insert into salgrade values(3, 1401, 2000); insert into salgrade values(4, 2001, 3000); insert into salgrade values(5, 3001, 9999); ~~~ 创建rand_string函数，返回指定个数的随口字符串 ~~~ delimiter $$ create function rand_string(n int) returns varchar(255) begin declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ'; declare return_str varchar(255) default ''; declare i int default 0; while i < n do set return_str = concat(return_str, substring(chars_str, floor(1+rand()*52), 1)); set i = i + 1; end while; return return_str; end $$ ~~~ 创建rand_num函数，返回随机部门编号 ~~~ create function rand_num(n int) returns int(20) begin declare return_tel varchar(255) default '187'; declare i int default 0; while i < n do set return_tel =return_tel+floor(rand(1)*10); set i = i + 1; end while; return return_tel; end$$ ~~~ 2，创建存储过程 ~~~ create procedure insert_emp(in start int(10), in max_num int(10)) begin declare i int default 0; set autocommit = 0; repeat set i = i + 1; insert into sbh_artnews values((start+i),rand_tel(8),rand_string(3),rand_string(20),1,rand_string(20),rand_string(20),now(),1,1,rand_num(4),rand_num(5),rand_tel(8),rand_tel(8),rand_string(10) ); until i = max_num end repeat; commit; end $$ ~~~ 3，执行插入存储过程，插入800w条数据 ~~~ call insert_emp(100001, 8000000)$$ ~~~ 测试不加索引的SQL语句查询快慢 ~~~ mysql> select * from emp where empno = 1273232 ; +---------+--------+----------+-----+------------+---------+--------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +---------+--------+----------+-----+------------+---------+--------+--------+ | 1273232 | yivDHx | salesman | 1 | 2016-04-10 | 2000.00 | 400.00 | 184 | +---------+--------+----------+-----+------------+---------+--------+--------+ 1 row in set (2.68 sec) ~~~ 执行时间耗时2.68秒，对于程序来说非常的慢了，根本无法满足业务需求。添加索引 alter table emp add primary key(empno) 再次测试 ~~~ mysql> select * from emp where empno = 1582342; +---------+--------+----------+-----+------------+---------+--------+--------+ | empno | ename | job | mgr | hiredate | sal | comm | deptno | +---------+--------+----------+-----+------------+---------+--------+--------+ | 1582342 | HOZFeD | salesman | 1 | 2016-04-10 | 2000.00 | 400.00 | 285 | +---------+--------+----------+-----+------------+---------+--------+--------+ 1 row in set (0.00 sec) ~~~ 添加索引之后，执行时间从2.68秒变成0.00秒。索引的优化是非常的重要，效果也是很显著的。 4，创建手机号码 ~~~ create function rand_tel(n int) returns varchar(255) begin declare chars_str varchar(100) default '123456789123456789123456789123456789123456798123456798132456789123456798'; declare return_tel varchar(255) default '187'; declare i int default 0; while i < n do set return_tel = concat(return_tel, substring(chars_str, floor(1+rand()*52), 1)); set i = i + 1; end while; return return_tel; end $$ ~~~