mysql 学习记录（十七）--水平拆分表、垂直拆分表、中间表

来源：互联网发布：macbook pro windows 编辑：程序博客网时间：2024/06/05 10:31

一、理论：

1.垂直拆分表：把主码某些列放在一个表，把主码及另外的列放在另一个表中。优点是查询时可以减少I/O次数，缺点是查询时需要联合操作。
2.水平拆分：根据一列或多列的值把数据行放到多个表中。
3.水平拆分表的使用情况：
a.表很大，分割后可以降低在查询时需要读的数据和索引页数以及层数，可提高查询速度。
b.表中的数据有逻辑上的独立性。如：不同时期的数据或明显具有不同使用频率的数据。
c.需要把数据放在多个介质上。
4.反规范的优势：
a.增加冗余列：在多个表中具有相同列，可以在查询时避免连接操作
b.增加派生列：增加来自于其他表中来自于经过计算的数据，目的是可以减少连接操作以及集函数
c.重新组表：如果许多用户需要查看两个表连接出来的结果数据，则把这两个表重新组成一个表来减少连接
d.分割表
5.保证数据完整性的技术：
a.批处理维护
b.使用应用逻辑来实现（但此方法不易于维护）
c.使用触发器
6.中间表的优点：
a.中间表复制源表部分数据并与源表隔离，在中间表中的统计查询不会对在线应用产生负面应用
b.中间表上可以灵活地添加索引或增加临时用的新字段，可提高统计查询效率和辅助统计查询

二、实践：

mysql> CREATE TABLE  duck_cust(    -> cust_num MEDIUMINT AUTO_INCREMENT,    -> cust_title TINYINT ,     -> cust_last char(20) NOT NULL ,    -> cust_first char(15) NOT NULL,     -> cust_suffix enum('jr.','II','III','IV','v','m.d.','phd.'),    -> cust_add1 char(30) NOT NULL,     -> cust_add2 char(10) ,     -> cust_city char(18) NOT NULL,    -> cust_state char(2) NOT NULL,     -> cust_zip1 char(5) NOT NULL,    -> cust_zip2 char(4) ,     -> cust_duckname char(25) NOT NULL,    -> cust_duckbday DATE,    -> PRIMARY KEY (cust_num)    -> ) engine =MyISAM;Query OK, 0 rows affected (0.02 sec)mysql> insert into duck_cust values     -> (null,1,'IrishLoad','red','III','地址1','a302','sea','wa','1111','11111',etrek','2917:10:21');Query OK, 1 row affected, 2 warnings (0.00 sec)mysql> insert into duck_cust values     -> (null,2,'IrishLoad2','red2','III2','地址12','a3022','sea2','wa2','11112',2323422','Netrek2','2927:10:21');Query OK, 1 row affected, 4 warnings (0.00 sec)mysql> insert into duck_cust values     -> (null,3,'IrishLoad3','red3','III3','地址13','a3023','sea3','wa3','11113',232343','Netrek3','2937:10:21');Query OK, 1 row affected, 4 warnings (0.00 sec)mysql> insert into duck_cust values     -> (null,4,'IrishLoad4','red4','III4','地址14','a3024','sea4','wa4','11114',232344','Netrek4','2947:10:21');Query OK, 1 row affected, 4 warnings (0.01 sec)mysql> insert into duck_cust values     -> (null,5,'IrishLoad5','red5','III5','地址15','a3025','sea5','wa5','11115',232345','Netrek5','2957:10:21');Query OK, 1 row affected, 4 warnings (0.00 sec)mysql> insert into duck_cust values     -> (null,6,'IrishLoad6','red6','III6','地址16','a3026','sea6','wa6','11116',232346','Netrek6','2967:10:21');Query OK, 1 row affected, 4 warnings (0.00 sec)mysql> insert into duck_cust values     -> (null,7,'IrishLoad7','red7','III7','地址17','a3027','sea7','wa7','11117',232347','Netrek7','2977:1');Query OK, 1 row affected, 5 warnings (0.00 sec)mysql> select * from duck_cust procedure analyse() \G;*************************** 1. row ***************************             Field_name: sakila.duck_cust.cust_num              Min_value: 1              Max_value: 7             Min_length: 1             Max_length: 1       Empties_or_zeros: 0                  Nulls: 0Avg_value_or_avg_length: 4.0000                    Std: 2.0000      Optimal_fieldtype: ENUM('1','2','3','4','5','6','7') NOT NULL*************************** 2. row ***************************             Field_name: sakila.duck_cust.cust_title              Min_value: 1              Max_value: 7             Min_length: 1             Max_length: 1       Empties_or_zeros: 0                  Nulls: 0Avg_value_or_avg_length: 4.0000                    Std: 2.0000      Optimal_fieldtype: ENUM('1','2','3','4','5','6','7') NOT NULL*************************** 3. row ***************************             Field_name: sakila.duck_cust.cust_last              Min_value: IrishLoad              Max_value: IrishLoad7             Min_length: 9             Max_length: 10       Empties_or_zeros: 0                  Nulls: 0Avg_value_or_avg_length: 9.8571                    Std: NULL      Optimal_fieldtype: ENUM('IrishLoad','IrishLoad2','IrishLoad3','IrishLoad4','IrishLoad5','IrishLoad6','IrishLoad7') NOT NULL*************************** 4. row ***************************             Field_name: sakila.duck_cust.cust_first              Min_value: red              Max_value: red7             Min_length: 3             Max_length: 4       Empties_or_zeros: 0                  Nulls: 0Avg_value_or_avg_length: 3.8571                    Std: NULL      Optimal_fieldtype: ENUM('red','red2','red3','red4','red5','red6','red7') NOT NULL*************************** 5. row ***************************             Field_name: sakila.duck_cust.cust_suffix              Min_value: III              Max_value: III             Min_length: 3             Max_length: 3       Empties_or_zeros: 6                  Nulls: 0Avg_value_or_avg_length: 0.4286                    Std: NULL      Optimal_fieldtype: ENUM('','III') NOT NULL*************************** 6. row ***************************             Field_name: sakila.duck_cust.cust_add1              Min_value:               Max_value:              Min_length: 0             Max_length: 0       Empties_or_zeros: 7                  Nulls: 0Avg_value_or_avg_length: 0.0000                    Std: NULL      Optimal_fieldtype: CHAR(0) NOT NULL*************************** 7. row ***************************             Field_name: sakila.duck_cust.cust_add2              Min_value: a302              Max_value: a3027             Min_length: 4             Max_length: 5       Empties_or_zeros: 0                  Nulls: 0Avg_value_or_avg_length: 4.8571                    Std: NULL      Optimal_fieldtype: ENUM('a302','a3022','a3023','a3024','a3025','a3026','a3027') NOT NULL*************************** 8. row ***************************             Field_name: sakila.duck_cust.cust_city              Min_value: sea              Max_value: sea7             Min_length: 3             Max_length: 4       Empties_or_zeros: 0                  Nulls: 0Avg_value_or_avg_length: 3.8571                    Std: NULL      Optimal_fieldtype: ENUM('sea','sea2','sea3','sea4','sea5','sea6','sea7') NOT NULL*************************** 9. row ***************************             Field_name: sakila.duck_cust.cust_state              Min_value: wa              Max_value: wa             Min_length: 2             Max_length: 2       Empties_or_zeros: 0                  Nulls: 0Avg_value_or_avg_length: 2.0000                    Std: NULL      Optimal_fieldtype: ENUM('wa') NOT NULL*************************** 10. row ***************************             Field_name: sakila.duck_cust.cust_zip1              Min_value: 1111              Max_value: 11117             Min_length: 4             Max_length: 5       Empties_or_zeros: 0                  Nulls: 0Avg_value_or_avg_length: 4.8571                    Std: NULL      Optimal_fieldtype: ENUM('1111','11112','11113','11114','11115','11116','11117') NOT NULL*************************** 11. row ***************************             Field_name: sakila.duck_cust.cust_zip2              Min_value: 1111              Max_value: 2232             Min_length: 4             Max_length: 4       Empties_or_zeros: 0                  Nulls: 0Avg_value_or_avg_length: 4.0000                    Std: NULL      Optimal_fieldtype: ENUM('1111','2232') NOT NULL*************************** 12. row ***************************             Field_name: sakila.duck_cust.cust_duckname              Min_value: Netrek              Max_value: Netrek7             Min_length: 6             Max_length: 7       Empties_or_zeros: 0                  Nulls: 0Avg_value_or_avg_length: 6.8571                    Std: NULL      Optimal_fieldtype: ENUM('Netrek','Netrek2','Netrek3','Netrek4','Netrek5','Netrek6','Netrek7') NOT NULL*************************** 13. row ***************************             Field_name: sakila.duck_cust.cust_duckbday              Min_value: 0000-00-00              Max_value: 2967-10-21             Min_length: 10             Max_length: 10       Empties_or_zeros: 0                  Nulls: 0Avg_value_or_avg_length: 10.0000                    Std: NULL      Optimal_fieldtype: ENUM('0000-00-00','2917-10-21','2927-10-21','2937-10-21','2947-10-21','2957-10-21','2967-10-21') NOT NULL13 rows in set (0.01 sec)ERROR: No query specifiedmysql> alter table duck_cust modify cust_num mediumint(2);Query OK, 7 rows affected (0.02 sec)Records: 7  Duplicates: 0  Warnings: 0mysql> create table session(    -> cust_id varchar(10),    -> cust_amount decimal(16,2)    -> ,    -> cust_date DATE,    -> cust_ip varchar(20)    -> );Query OK, 0 rows affected (0.05 sec)mysql> insert into session values (2,3.22,adddate(now(),-2),'192.168.0.2');e(now(),-15),'192.168.0.15');insert into session values (16,3.36,adddate(now(),-16),'192.168.0.16');Query OK, 1 row affected, 1 warning (0.00 sec)mysql> insert into session values (3,3.23,adddate(now(),-3),'192.168.0.3');Query OK, 1 row affected, 1 warning (0.00 sec)mysql> insert into session values (4,3.24,adddate(now(),-4),'192.168.0.4');Query OK, 1 row affected, 1 warning (0.00 sec)mysql> insert into session values (5,3.25,adddate(now(),-5),'192.168.0.5');Query OK, 1 row affected, 1 warning (0.00 sec)mysql> insert into session values (6,3.26,adddate(now(),-6),'192.168.0.6');Query OK, 1 row affected, 1 warning (0.00 sec)mysql> insert into session values (7,3.27,adddate(now(),-7),'192.168.0.7');Query OK, 1 row affected, 1 warning (0.00 sec)mysql> insert into session values (8,3.28,adddate(now(),-8),'192.168.0.8');Query OK, 1 row affected, 1 warning (0.01 sec)mysql> insert into session values (9,3.29,adddate(now(),-9),'192.168.0.9');Query OK, 1 row affected, 1 warning (0.01 sec)mysql> insert into session values (10,3.30,adddate(now(),-10),'192.168.0.10');Query OK, 1 row affected, 1 warning (0.00 sec)mysql> insert into session values (11,3.31,adddate(now(),-11),'192.168.0.11');Query OK, 1 row affected, 1 warning (0.01 sec)mysql> insert into session values (12,3.32,adddate(now(),-12),'192.168.0.12');Query OK, 1 row affected, 1 warning (0.00 sec)mysql> insert into session values (13,3.33,adddate(now(),-13),'192.168.0.13');Query OK, 1 row affected, 1 warning (0.00 sec)mysql> insert into session values (14,3.34,adddate(now(),-14),'192.168.0.14');Query OK, 1 row affected, 1 warning (0.00 sec)mysql> insert into session values (15,3.35,adddate(now(),-15),'192.168.0.15');Query OK, 1 row affected, 1 warning (0.00 sec)mysql> insert into session values (16,3.36,adddate(now(),-16),'192.168.0.16');Query OK, 1 row affected, 1 warning (0.01 sec)mysql> create table tmp_session(    -> cust_id varchar(10),    -> cust_amount decimal(16,2) ,    -> cust_date DATE,    -> cust_ip varchar(20)    -> );Query OK, 0 rows affected (0.03 sec)mysql> select sum(cust_amount) from session     -> where cust_date > adddate(now(),-7);+------------------+| sum(cust_amount) |+------------------+|            19.41 |+------------------+1 row in set (0.02 sec)mysql> insert into tmp_session     ->     -> select sum(cust_amount) from session     -> where cust_date > adddate(now(),-7);ERROR 1136 (21S01): Column count doesn't match value count at row 1mysql> mysql> insert into tmp_session     -> select * from session     -> where cust_date > adddate(now(),-7);Query OK, 6 rows affected (0.01 sec)Records: 6  Duplicates: 0  Warnings: 0mysql> select sum(cust_amount) from tmp_session;+------------------+| sum(cust_amount) |+------------------+|            19.41 |+------------------+1 row in set (0.00 sec)

0 0