mysql分表的使用

实际业务中，我们遇到数据量大(百万级以上)的时候，表的大小太大了，影响到查询的时候，就需要做水平分表的操作了。今天就拿用户表作为例子。

首先总结一下：Mysql通过Merge引擎进行分表

使用场景

• 数据表记录很大，单表记录会超过500W，比如用户表、订单表、产品表等。

注意

分表的id不能是自增（auto increment）的；
分表必须使用MyISAM存储引擎；
每个分表的表结构必须相同；
MySQL必须具有存储分表数据文件和索引文件的目录的读写权限；
必须启用MySQL的符号链接支持功能。
总表的表结构必须与各个分表相同；
总表必须使用MRG_MyISAM存储引擎；
总表不会创建任何数据文件和索引文件；
MRG文件存储总表需要映射的子表的表名；
总表本身不存储任何数据和索引；
INSERT_METHOD需要设置为NO，或者不配置；
总表的id不能是自增（auto increment）的。

MERGE分表的优点

适用于存储日志数据。例如，可以将不同月份的数据存入不同的表，然后使用myisampack工具压缩数据，最后通过一张MERGE表来查询这些数据。

可以获得更快的速度。可以根据某种指标，将一张只读的大表分割成若干张小表，然后将这些小表分别放在不同的磁盘上存储。当需要读取数据时，MERGE表可以将这些小表的数据组织起来，就好像使用先前的大表一样，但是速度会快很多。

可以提高搜索效率。可以根据某种指标将一张只读的大数据表分割为若干个小表，然后根据不同的查询维度，可以得到若干种小表的组合，然后再为这些组合分别创建不同的MERGE表。例如，有一张只读的大数据表T，分割为T1、T2、T3、T4，共4张小表，有两种查询维度A和B，A可以得到小表组合T1、T2和T3，B可以得到小表组合T2、T3和T4，分别为A和B创建两个MERGE表，也就是M1和M2，这两个MERGE表分别关联的小表是存在交叠的。

可以更加有效的修复表。修复单个的小表要比修复大数据表更加容易。

多个子表映射至一个总表的速度极快。因为MERGE表本身不会存储和维护任何索引，索引都是由各个关联的子表存储和维护的，所以创建和重新映射MERGE表的速度非常快。

不受操作系统的文件大小限制。单个表会受到文件大小的限制，但是拆分成多个表，则可以无限扩容。

MERGE表还可以用来给单个表创建别名，并且几乎不会影响性能。

MERGE分表的问题

总表（MERGE表）必须使用MRG_MyISAM存储引擎，子表必须使用MyISAM存储引擎，不可避免会受到MyISAM存储引擎的限制。

MERGE表不能使用某些MyISAM特性。例如，虽然可以为子表创建全文索引，但是却不能使用全文索引，通过MERGE表查询数据。

MERGE表会使用更多的文件描述符。如果有10个客户端使用1张MERGE表，那么就需要消耗(10×10)+10个文件描述符（其中，10个客户端分别有10个数据文件描述符，并且会共享使用10个索引文件描述符）。

若使用ALTER TABLE语句修改总表的存储引擎，那么会立即丢失总表和子表的映射关系，并且会将所有子表的数据拷贝至修改后的新表。

总表和子表的主键都不能使用自动增长（auto increment）。

子表之间不能保证唯一键约束，只能保证单个子表内部的唯一性约束。

由于不能保证唯一键约束，导致REPLACE语句的行为会不可预期，INSERT ... ON DUPLICATE KEY UPDATE语句也有类似问题。因此，只能使用路由策略，对子表使用这些语句，而不能对总表使用。

子表不支持分区（Partition）。

当正在使用总表时，不能对任何子表执行ANALYZE TABLE、REPAIR TABLE、OPTIMIZE TABLE、ALTER TABLE、DROP TABLE、DELETE或TRUNCATE TABLE语句，否则会导致不可预期的结果。

总表和子表的表结构必须完全一致。

总表可以映射的所有子表的总行数上限为 264 行。

不支持INSERT DELAYED语句。

上面介绍了一下分表的优缺点，下面就实际操作一次。

第一步：创建子表（创建四张子表），注意：分表必须使用MyISAM存储引擎，而MyISAM表的数据文件（.MYD文件）和索引文件（.MYI文件）是可以分散在不同的磁盘或目录上存储的。

CREATE TABLE `lp_user_0` (
  `id` int(11) unsigned NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `sex` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
CREATE TABLE `lp_user_1` (
  `id` int(11) unsigned NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `sex` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
CREATE TABLE `lp_user_2` (
  `id` int(11) unsigned NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `sex` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;
CREATE TABLE `lp_user_3` (
  `id` int(11) unsigned NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `sex` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

第二步：创建主表

1、主表和字表的数据结构要一样。

2、insert_method=no设置成no，意思就是这个表只能select操作，不可以插入操作。或者默认不写也可以。

CREATE TABLE `lp_user_table` (
  `id` int(11) unsigned NOT NULL,
  `name` varchar(64) DEFAULT NULL,
  `sex` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8mb4 UNION=(`lp_user_0`,`lp_user_1`,`lp_user_2`,`lp_user_3`);

第三步：因为字表和主表的 id 编号都不是自增的，为了解决每个字表中id编号的问题，现在需要建立一张，可以提供唯一编号的数据表如下：

CREATE TABLE `lp_user_unique_id` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ids` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4;

现在有了主表、字表、还有自增id表、准备工作已经做完，接下来就是如何编程了。因为当前用的是laravel框架，这里就以laravel框架为例子了。写了一些简单的示意代码。这是一个控制器的整体代码，最后自己通过路由访问控制器，测试一下。在观察一下每个表数据的变化过程即可。这里需要注意的就是，获取表名称方法。因为是4张子表，所以模4。3张就模3，以此类推。

<?php

namespace App\Http\Controllers;


use Illuminate\Http\Request;
use Illuminate\Support\Facades\DB;

class TestController extends Controller
{
   public function index(){
        //获取自增ID
       $id = $this->getInsertId();
       //获取表名称
       $tableName = $this->getTableName($id);
        //插入数据
       $insertData = [
           'id' => $id,
           'name' => '张三',
           'sex' => 1
       ];
       $result = DB::table("$tableName")->insert($insertData);
       var_dump($result);
   }

   /**
    * 获取自增ID
    */
   private function getInsertId(){
       $id = DB::table('user_unique_id')->insertGetId(['ids'=>1]);
       return $id;
   }

   /**
    * 获取表名称
    */
   private function getTableName($id){
        return 'user_'.intval($id)%4;
   }

}

这里顺便再介绍一下php基于crc对mysql进行水平分表操作的几种方式

事实上水平分表可以按照很多的业务逻辑来分表，比如按照时间，地区，id等。不同的表作用不同，分表采用的方式也就不同

• 日志类表，统计类表

  按照时间来分表，因为这种表基本都是按照时间来插入的，而且用作分析时常需要生成图状结构来分析，所以常用作时间分表

• 区域类，比如美团，大众点评这种，按照区域来划分店铺

  按照地区进行分表，这样方便就近检索

• id数字类的，比如qq

按照数字取模来分表，这样分表最为简单，查找时先取模，然后再去指定表里面查找

function mod_hash($account,$tail=4,$mod=1)
{
 return fmod($account, $mod);    //取模计算数值
}

• 字符串这种，比如csdn的邮箱，用户名，手机号登录

按照crc32求整数，然后在取模分表

function mod_hash($account,$tail=4,$mod=1)
{
  $crc32=sprintf("%u",crc32($account));//使用%u解决32位下出现负数的问题
  return fmod($crc32, $mod);  //取模计算数值
}