MySQL垂直分表

业务表增长速度较快，单表数据较大，对表的读写有影响。
思路：化整为零，把单表拆解为多表，按指定规则选择表

以Laravel项目为例，分表读取代码如下：
public function changeTable($companyId) {
    // 分表算法，根据companyId选择分表，请勿修改算法，以免混淆分表数据
    $posfix = intval($companyId) % 10;
    $tableName = $posfix ? "demo_$posfix" : ‘demo‘;
    return $this->setTable($tableName);
}

调用方法：
public function countCompanyData($companyId, $condition) {
    return $this->changeTable($companyId)->multiwhere($condition)->count();
}

算法实现后，就差表数据迁移了

步骤一，先执行 SHOW CREATE TABLE `demo`; 得到原始表的创建语句，然后，加个下标，就是分表的创建语句了

步骤二，创建所需要的所有分表，需要创建 1~9 编号的分表，这里以编号1为例
CREATE TABLE `demo_1` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT ‘主键ID‘,
  `company_id` int(11) unsigned NOT NULL COMMENT ‘商家ID‘,
  `content` varchar(255) NOT NULL COMMENT ‘短信内容‘,
  `phone` varchar(11) NOT NULL COMMENT ‘手机号‘
  PRIMARY KEY (`id`),
  KEY `company_id` (`company_id`),
  KEY `Phone` (`phone`,`company_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘演示表‘;

步骤三，创建好分表后，执行迁移语句
INSERT INTO `demo_1` SELECT * FROM `demo` WHERE MOD(`company_id`, 10) = 1;
INSERT INTO `demo_2` SELECT * FROM `demo` WHERE MOD(`company_id`, 10) = 2;
INSERT INTO `demo_3` SELECT * FROM `demo` WHERE MOD(`company_id`, 10) = 3;
INSERT INTO `demo_4` SELECT * FROM `demo` WHERE MOD(`company_id`, 10) = 4;
INSERT INTO `demo_5` SELECT * FROM `demo` WHERE MOD(`company_id`, 10) = 5;
INSERT INTO `demo_6` SELECT * FROM `demo` WHERE MOD(`company_id`, 10) = 6;
INSERT INTO `demo_7` SELECT * FROM `demo` WHERE MOD(`company_id`, 10) = 7;
INSERT INTO `demo_8` SELECT * FROM `demo` WHERE MOD(`company_id`, 10) = 8;
INSERT INTO `demo_9` SELECT * FROM `demo` WHERE MOD(`company_id`, 10) = 9;

步骤四，备份原始表（demo表）的数据

步骤五，删除已迁移的数据，留下属于 编号0 表的数据
DELETE FROM `demo` WHERE MOD(`company_id`, 10) != 0;

P.S：修改后所有的SQL操作，都应该含有 company_id 信息，这样才能找到具体的分表。

如果业务中有和company_id无关的汇总类的查询，已统计所有记录为例，那么可以

public function countTable($condition) {
    $posfixList = range(0, 9);
    $sum = 0;
    foreach ($posfixList as $posfix) {
        $tableName = $posfix ? "demo_$posfix" : ‘demo‘ ;
        $count = $this->setTable($tableName)->multiwhere($condition)->sum(‘id‘);
    }
    $sum += $count;
    return $sum;
}