創建還量表 · 個人筆記

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 $$ ~~~