Mysql的垂直分表-新建

说明

垂直分割：就是将一个表按照字段来分，每张表保证有相同的主键就好。一般来说，将常用字段和大字段分表来放。    

优势：比没有分表来说，提高了查询速度，降低了查询结果所用内存；

劣势：没有解决大量记录的问题，对于单表来说随着记录增多，性能还是下降很快；

案例

 

思路

硬分表，把常用字段建立一张，不常用的建立一张

案例

现在有个user表：如下，有很多字段，现在分成两个表，一个user1用了存放用户主要的属性字段，另一个表user2，用了存放各种时间相关的字段表user2中通过uid来关联user1表

mysql> desc user;
+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
| id             | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| group_id       | int(10) unsigned    | NO   |     | 0       |                |
| username       | varchar(32)         | NO   | MUL |         |                |
| nickname       | varchar(50)         | NO   | MUL |         |                |
| password       | varchar(32)         | NO   |     |         |                |
| salt           | varchar(30)         | NO   |     |         |                |
| email          | varchar(100)        | NO   | MUL |         |                |
| mobile         | varchar(11)         | NO   | MUL |         |                |
| avatar         | varchar(255)        | NO   |     |         |                |
| birthday       | date                | NO   |     | NULL    |                |
| prevtime       | int(10) unsigned    | NO   |     | 0       |                |
| logintime      | int(10) unsigned    | NO   |     | 0       |                |
| loginip        | varchar(50)         | NO   |     |         |                |
| loginfailure   | tinyint(1) unsigned | NO   |     | 0       |                |
| joinip         | varchar(50)         | NO   |     |         |                |
| jointime       | int(10) unsigned    | NO   |     | 0       |                |
| createtime     | int(10) unsigned    | NO   |     | 0       |                |
| updatetime     | int(10) unsigned    | NO   |     | 0       |                |

  
mysql> desc user1;
+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
| id             | int(10) unsigned    | NO   | PRI | NULL    | auto_increment |
| group_id       | int(10) unsigned    | NO   |     | 0       |                |
| username       | varchar(32)         | NO   | MUL |         |                |
| nickname       | varchar(50)         | NO   | MUL |         |                |
| password       | varchar(32)         | NO   |     |         |                |
| salt           | varchar(30)         | NO   |     |         |                |
| email          | varchar(100)        | NO   | MUL |         |                |
| mobile         | varchar(11)         | NO   | MUL |         |                |
| avatar         | varchar(255)        | NO   |     |         |                |
| birthday       | date                | NO   |     | NULL    |                |
 
mysql> desc user2;
+----------------+---------------------+------+-----+---------+----------------+
| Field          | Type                | Null | Key | Default | Extra          |
+----------------+---------------------+------+-----+---------+----------------+
| uid             | int(10) unsigned    | NO  |     |         |
| prevtime       | int(10) unsigned    | NO   |     | 0       |                |
| logintime      | int(10) unsigned    | NO   |     | 0       |                |
| loginip        | varchar(50)         | NO   |     |         |                |
| loginfailure   | tinyint(1) unsigned | NO   |     | 0       |                |
| joinip         | varchar(50)         | NO   |     |         |                |
| jointime       | int(10) unsigned    | NO   |     | 0       |                |
| createtime     | int(10) unsigned    | NO   |     | 0       |                |
| updatetime     | int(10) unsigned    | NO   |     | 0       |                |
————————————————