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

最新推荐文章于 2024-02-16 06:30:00 发布
最新推荐文章于 2024-02-16 06:30:00 发布
阅读量3.4k 收藏 1
点赞数
分类专栏： mysql mysql学习
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/bwshqh/article/details/49429651
版权

一、理论：

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: 0
Avg_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: 0
Avg_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: 0
Avg_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: 0
Avg_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: 0
Avg_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: 0
Avg_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: 0
Avg_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: 0
Avg_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: 0
Avg_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: 0
Avg_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: 0
Avg_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: 0
Avg_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: 0
Avg_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 NULL
13 rows in set (0.01 sec)

ERROR: 
No query specified

mysql> alter table duck_cust modify cust_num mediumint(2);
Query OK, 7 rows affected (0.02 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> 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 1
mysql> 
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: 0

mysql> select sum(cust_amount) from tmp_session;
+------------------+
| sum(cust_amount) |
+------------------+
|            19.41 |
+------------------+
1 row in set (0.00 sec)


bwshqh
关注
  • 0
    点赞
  • 踩
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
Mysql大表处理方案一：分区详解教程
weixin_44846446的博客
05-22 800
在我们日常处理海量数据的过程中，如何有效管理和优化数据库一直是一个既重要又具有挑战性的问题，较为通用的做法就是分区、分表、分库了。那我们应该如何选择这几种方式呢？下面本人记录以下几种方式的优劣，希望能够帮助到大家。首先分区:数据库中分区是将表或索引的数据划分成更小、更可管理的部分的一种技术。这些部分被称为分区，每个分区可以独立地进行维护和管理，从而提高了整体数据库操作的性能。.frm.myi.myd*.frm：这个是表定义，是描述表结构的文件。*.myd：这个是数据信息文件，是表的数据文件。
MySql 分库分表：垂直分表、垂直分库、水平分表、水平分库
书香水墨
12-15 2704
读写分离解决的是数据库读写操作的压力，但是没有分散数据库的存储压力，利用分库分表可以解决数据库的储存瓶颈，并提升数据库的查询效率。 12.1、垂直拆分： （1）垂直分表：将一个表按照字段分成多个表，每个表存储其中一部分字段。一般会将常用的字段放到一个表中，将不常用的字段放到另一个表中。 优点： （1）避免IO竞争减少锁表的概率。因为大的字段效率更低，第一，大字段占用的空间更大，单页内存储的行数变少，会使得IO操作增多；第二数据量大，需要的读取时间长。 （2）可以更好地提升热门数据的查询效率。 （2）
MySQL性能调优篇(9)-数据库的水平与垂直分割
俞兆鹏的博客
02-16 708
在大规模互联网应用中，数据库的性能和可扩展性是至关重要的。为了满足高并发和大规模数据需求，水平和垂直分割成为了大型数据库架构的关键概念。在本篇博客中，我将介绍MySQL数据库的水平和垂直分割的概念、原则、示例以及其优缺点。
Mysql数据库垂直拆分与水平拆分
qq_36350266的博客
02-25 2702
当我们使用读写分离、缓存后，数据库的压力还是很大的时候，这就需要使用到数据库拆分了。 数据库拆分简单来说，就是指通过某种特定的条件，按照某个维度，将我们存放在同一个数据库中的数据分散存放到多个数据库（主机）上面以达到分散单库（主机）负载的效果。 切分模式： 垂直（纵向）拆分、水平拆分。 垂直拆分 专库专用 一个数据库由很多表的构成，每个表对应着不同的业务，垂直切分是指按照业务将表进行分类，分布到...
MySQL分库分表
weixin_37672801的博客
11-24 2701
如果一张表中的记录数过多，那么会对数据库的读写性能产生较大的影响，虽然此时仍然能够正确地读写，但读写的速度已经到了业务无法忍受的地步，此时就需要使用水平分表来解决这个问题。使用41bit作为毫秒数，10bit作为机器的ID（5个bit是数据中心，5个bit的机器ID），12bit作为毫秒内的流水号，最后还有一个符号位，永远是0。一致性Hash在增加或者删除节点的时候，受到影响的数据是比较有限的，只会影响到Hash环相邻的节点，不会发生大规模的数据迁移。使用一致性Hash算法能够很大程度的避免这个问题。
MySQL 表的垂直拆分和水平拆分
09-08
MySQL 数据库在面对大规模数据存储时，为了保持良好的性能和可管理性，常常需要对表进行拆分，即垂直拆分和水平拆分。这两种拆分策略分别针对数据的不同维度进行优化。 **垂直拆分** 垂直拆分是基于列的拆分方式，...
mysql学习笔记之表的基本操作
12-15
这篇学习笔记主要关注了如何进行表的基本操作，包括创建数据库、切换数据库、创建表以及查看表的相关信息。以下是对这些操作的详细解释： 1. **创建数据库**： 使用`CREATE DATABASE`语句可以创建一个新的数据库。...
Mysql基础知识学习-创建数据库和表.pdf
最新发布
05-28
mysql创建数据库和表
MySQL水平拆分（读书笔记整理）
02-26
一般来说，简单的水平切分主要是将某个访问极其平凡的表再按照某个字段的某种规则来分散到多个表之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中的某些行...
linux mycat mysql 水平拆分——分表
weixin_44285972的博客
07-01 462
linux mycat mysql 水平拆分——分表
数据库（九）_MySQL分区、分表、分库
小小小跟班的博客
05-27 462
MySQL分区 一般情况下我们创建的表对应一组存储文件，使用MyISAM存储引擎时是一个.MYI和.MYD文件，使用Innodb存储引擎时是一个.ibd和.frm（表结构）文件。 当数据量较大时（一般千万条记录级别以上），MySQL的性能就会开始下降，这时我们就需要将数据分散到多组存储文件，保证其单个文件的执行效率 能干嘛 逻辑数据分割 提高单一的写和读应用速度 提高分区范围读查询的速度 分割数据能够有多个不同的物理文件路径 高效的保存历史数据 怎么玩 首先查看当前数据库是否支持分区 MySQL5.6
yii2 模型中set_Laravel模型间关系设置分表方法详解
weixin_39599454的博客
12-12 108
在实际开发中经常用到分库分表，比如用户表分成 100 张，那么这个时候查询数据需要设置分表，比如 Laravel 的 Model 类中提供了 setTable 方法：/** * Set the table associated with the model. * * @param string $table * @return $this */ public functi...
MySQL数据库表如何水平拆分和垂直拆分
热门推荐
施勇
03-02 1万+
目前很多互联网系统都存在单表数据量过大的问题，这就降低了查询速度，影响了客户体验。为了提高查询速度，我们可以优化sql语句，优化表结构和索引，不过对那些百万级千万级的数据库表，即便是优化过后，查询速度还是满足不了要求。这时候我们就可以通过分表降低单次查询数据量，从而提高查询速度，一般分表的方式有两种：水平拆分和垂直拆分，两者各有利弊，适用于不同的情况。水平拆分 水平拆分是指数据表行的拆分，表的行数
【mysql的设计与优化专题(4)】表的垂直拆分和水平拆分
weixin_30474613的博客
05-23 454
垂直拆分 垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表 通常我们按以下原则进行垂直拆分: 把不常用的字段单独放在一张表; 把text，blob等大字段拆分出来放在附表中; 经常组合查询的列放在一张表中; 垂直拆分更多时候就应该在数据表设计之初就执行的步骤，然后查询的时候用jion关键起来即可; 水平拆分 水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可...
mysql一条记录split拆多条_sql如何将一条内容包含空格的记录拆分成多条记录
weixin_32150027的博客
01-27 788
展开全部“张三62616964757a686964616fe4b893e5b19e31333365656632,李四,王五,钱六,赵七,Tom,Amy,Joe,Leo”，现在要将此字符串按照“,”进行分离得到一组数据，现在可以得出这个函数传入变量有两个，一个是目标字符串，我们定义为@string，数据类型为nvarchar(500)，选择nvarchar()是为了兼顾所有Unicode字符集，比如...
mysql表水平拆分和分区分表
jaryle的专栏
06-22 2057
业务需求就是，有6个数据库，分为3个区，每组是主从复制，配置路由算法：根据用户名hashcode值取模的算法来判断存储在那个区。 和spring整合后，spring的配置文件中配置6个数据源，spring中注册路由算法，来实现。
MySQL5.5表的水平拆分
weixin_34234829的博客
07-14 118
一：业务背景二：第一种业务水平拆分方案三：第二种业务水平拆分方案1.1由于业务量的急剧增加,某张表的数据越来越大,现在假设的情况为某家公司的P2P网站,由于前期宣传比较好,在近三个月的时间里业务量急剧增加,使得流水记录表(trade)的数据量变的越来越大,已经超过300万行记录,预计年内流水表的记录可能会超过1000万行,这个时候就需要考虑做表的水平拆分了1.2 假设随着业务...
MySQL拆表
杨二的博客
04-02 1368
这里是areas表 id name 1 北京 2 上海 3 杭州 4 杭州 这里是province表 id name 1 北京 2 上海 3 杭州 此时如何将areas表中的name字段改成province表中的id呢。。。 此时使用update语法。进行拆表 update areas as a (条件) set a.name=p.id；...
深入浅出MySQL(七) 如何通俗地来理解表的横向拆分的实现原理
Danny_idea的博客
08-03 1860
今天的这个案例主要是正对于讲解这个Mysql的横向拆分技术： 首先我们来模拟一个场景，用户表里面有着一千万的数据量的内容，这个时候我们该如何进行优化呢？ 这个时候不妨试试用横向拆分的技术来进行优化。 首先我们来看看数据库里面的内容： 一共有一千万条数据信息，这个时候我们可以继续深入： 假设有一个请求要开展用户搜索： SELECT * from user where userna...
《MySQL表约束和表关系学习ppt 重点回顾与三范式视图表约束详解》
本文将总结生成一个关于MySQL表约束和表关系学习的描述，严格要求2000字。 在MySQL学习中，了解和掌握表约束和表关系是非常重要的。表约束用于限制表中数据的合法性和完整性，保证数据的准确性和一致性。而表关系则...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值