数据库的水平切分和垂直切分

参考文章：https://blog.csdn.net/xiaoshihoukeshuaiL/article/details/77917121

当一个数据库的某个表的数据较多的时候

1. 垂直切分：

1. 定义：将一个数据库表的字段拆开（需依旧满足相应范式）

2. 一般怎么拆：

• 把常用的字段放一个表，不常用的放另外一个表
• 把字段较大（例如text）的放到另外一个表
• 可根据具体业务来拆，查询时使用多表查询，再配合redis存储

3. 例子

拆表前

表的字段太多，数据量太大，拆分为两个表

+--------+---------+--------+--------+-------+---------+---------+--------+-----+-------------+--------+-----------+------+--------+ 
| userid | groupid | areaid | amount | point | modelid | message | islock | vip | overduedate | siteid | connectid | from | mobile | 
+--------+---------+--------+--------+-------+---------+---------+--------+-----+-------------+--------+-----------+------+--------+ 
|      1 |       5 |      0 |   0.00 |    50 |      10 |       0 |      0 |   1 |           0 |      1 |           |      |        | 
+--------+---------+--------+--------+-------+---------+---------+--------+-----+-------------+--------+-----------+------+--------+ 

拆表后

+--------+---------+--------+--------+-------+---------+---------+
| userid | groupid | areaid | amount | point | modelid | message |
+--------+---------+--------+--------+-------+---------+---------+ 
|      1 |       5 |      0 |   0.00 |    50 |      10 |       0 |   
+--------+---------+--------+--------+-------+---------+---------+
 
和 
 
+--------+--------+-----+-------------+--------+-----------+------+--------+ 
| userid | islock | vip | overduedate | siteid | connectid | from | mobile | 
+--------+--------+-----+-------------+--------+-----------+------+--------+ 
|      1 |      0 |   1 |           0 |      1 |           |      |        | 
+--------+--------+-----+-------------+--------+-----------+------+--------+ 

水平拆表

1. 定义：就是根据表的数据来拆分，例如有十条数据，可以拆成10表

2. 一般如何拆

• 尽量不要有几条数据就拆除多少个表，通常可以采用"取模"的形式来将数据进行表的存储,例如有9条数据，使用id%4来取模，就会分为4个表，user_0,user_1,user_2,user_3就够了。

注意点，使用取模方法时，查询语句就select * from user_“取的模”，但是我们可能是使用自增id，无法进行取模，这时就可以先插入一个临时表，找出数据库插入的自增id，得到id后就可以取模插入了

• 第二种形式:就是user_0先存放10w条表，存满后创建新表user_1,但是对旧表的访问需求量可能会越来越少，可以自己根据需求来决定分表方法。

3. 例子

水平拆分之前

+--------+---------+--------+--------+-------+---------+---------+ 
| userid | groupid | areaid | amount | point | modelid | message | 
+--------+---------+--------+--------+-------+---------+---------+ 
|      1 |       5 |      0 |   0.00 |    50 |      10 |       0 |   
+--------+---------+--------+--------+-------+---------+---------+ 
|      1 |       5 |      0 |   0.00 |    50 |      10 |       0 |   
+--------+---------+--------+--------+-------+---------+---------+ 
|      2 |       5 |      0 |   0.00 |    50 |      10 |       0 |   
+--------+---------+--------+--------+-------+---------+---------+ 
表3user_3 
+--------+---------+--------+--------+-------+---------+---------+ 
| userid | groupid | areaid | amount | point | modelid | message | 
+--------+---------+--------+--------+-------+---------+---------+ 
|      3 |       5 |      0 |   0.00 |    50 |      10 |       0 |   
+--------+---------+--------+--------+-------+---------+---------+ 

+--------+---------+--------+--------+-------+---------+---------+ 
| userid | groupid | areaid | amount | point | modelid | message | 
+--------+---------+--------+--------+-------+---------+---------+ 
|      1 |       5 |      0 |   0.00 |    50 |      10 |       0 |   
+--------+---------+--------+--------+-------+---------+---------+ 
表1 user_1 
+--------+---------+--------+--------+-------+---------+---------+ 
| userid | groupid | areaid | amount | point | modelid | message | 
+--------+---------+--------+--------+-------+---------+---------+ 
|      1 |       5 |      0 |   0.00 |    50 |      10 |       0 |   
+--------+---------+--------+--------+-------+---------+---------+ 
表2 user_2 
+--------+---------+--------+--------+-------+---------+---------+ 
| userid | groupid | areaid | amount | point | modelid | message | 
+--------+---------+--------+--------+-------+---------+---------+ 
|      2 |       5 |      0 |   0.00 |    50 |      10 |       0 |   
+--------+---------+--------+--------+-------+---------+---------+ 
表3user_3 
+--------+---------+--------+--------+-------+---------+---------+ 
| userid | groupid | areaid | amount | point | modelid | message | 
+--------+---------+--------+--------+-------+---------+---------+ 
|      3 |       5 |      0 |   0.00 |    50 |      10 |       0 |   
+--------+---------+--------+--------+-------+---------+---------+