【MySQL优化】分表

    当一张的数据达到几百万时，你查询一次所花的时间会变多，如果有联合查询的话，有可能会死在那儿了。分表的目的就在于减小数据库的负担，缩短查询时间。分表是将一个大表按照一定的规则分解成独立的N个子表。读写数据库的时候根据事先定义好的规则得到对应的子表名，然后去操作它。

1、mysql集群

它并不是分表，但起到了和分表相同的作用。集群可分担数据库的操作次数，将任务分担到多台数据库上。集群可以读写分离，减少读写压力。从而提升数据库性能。

2、预先估计会出现大数据量并且访问频繁的表，将其分为若干个表(水平拆分)

大表可以按照业务的规则来分解为多个子表。也可自己定义分表规则。

例如：我事先建5个这样的表，message_0,message_1,message_2,message_3,message_4。然后根据用户的ID来判断这个用户的聊天信息放到哪张表里面。下面用求余数的方法来获得表名：

$tableName = 'message_'.$id%5; 

3、利用merge存储引擎来实现分表

merge分表，分为主表和子表，主表类似于一个壳子，逻辑上封装了子表，实际上数据都是存储在子表中的。

我们可以通过主表插入和查询数据，如果清楚分表规律，也可以直接操作子表。

缺点：
1、只能在相同结构的MyISAM表上使用 
2、无法享受到MyISAM的全部功能
3、通过主表查询的时候，相当于将所有子表合在一起查询。速度慢于第二种分表方式。

子表user1

//普通建表
CREATE TABLE IF NOT EXISTS `user1`(
`id`int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`sex`int(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

子表user2

create table user2 like user1; //建立同样结构的表user2

主表alluser

CREATE TABLE IF NOT EXISTS `alluser`(
`id`int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`sex`int(1) NOT NULL DEFAULT '0',
) ENGINE=MERGE UNION=(user1,user2) INSERT_METHOD=LAST CHARSET=utf8 AUTO_INCREMENT=1;

创建主表的时候有个INSERT_METHOD，指明插入方式，取值可以是：0 不允许插入；FIRST 插入到UNION中的第一个表； LAST 插入到UNION中的最后一个表。

 

旧表迁移方法：

假如我有一张用户表user，有50W条数据，现在要拆成二张表user1和user2，每张表25W条数据。

INSERT INTO user1(user1.id,user1.name,user1.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id <=250000
INSERT INTO user2(user2.id,user2.name,user2.sex)SELECT (user.id,user.name,user.sex)FROM user where user.id >250000

把以前的user表备份一下，然后删除掉，上面的操作中我建立了一个alluser表，只把这个alluser表的表名改成user就行了。

4、垂直拆分

垂直分割指的是：表的记录并不多，但是字段却很长，表占用空间很大，检索表的时候需要执行大量的IO，严重降低了性能。这时需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系。

例如学生答题表tt：有如下字段：
Id name 分数题目回答
其中题目和回答是比较大的字段，id name 分数比较小。
如果我们只想查询id为8的学生的分数：select分数from tt where id =8;虽然知识查询分数，但是题目和回答这两个大字段也是要被扫描的，很消耗性能。但是我们只关心分数，并不想查询题目和回答。这就可以使用垂直分割。
我们可以把题目单独放到一张表中，通过id与tt表建立一对一的关系，同样将回答单独放到一张表中。这样我们插叙tt中的分数的时候就不会扫描题目和回答了。

 

 

 

 

 

转载于:https://www.cnblogs.com/php99/p/9766194.html