mysql分表认知

第一种分表方法：通过merge生成合并表的形式，该方法用于已经存在的大表需要分表的情况

举例情况：good表中有300万条数据，需要分成2个分表g1和g2

 

步骤1：备份good表，以免操作失败，用于恢复数据

 

步骤2：将good表中的数据分摊到分表中去:

              1.使用程序处理，

     2.在mysql中使用 create table g1 select * from good where ....， 需要注意字符，字段类型等问题

                     insert into g1 select * from good where .....,  需要先手动建表

 

步骤3：删除good表，通过merge重新创建合并表good，语句如下：

       CREATE TABLE good (

.....

       ) TYPE=MERGE UNION=(分表1,分表2) INSERT_METHOD=LAST;

       ps：INSERT_METHOD参数用于决定当向合并表中插入数据时候，数据真正插入的是哪个分表，last表示向最后一个分表插入数据，first则是向第一分表插入, 0表示不允许插入 

 

           创建合并表会碰到不少创建不成功的情况，需要看情况处理，碰到的一些情况：

     1.分表和主表的字段总数和类型需要一致,包括列名、顺序,UNION中的表必须同属一个DATABASE

     2.分表类型必须是MyISAM的

     3.合并表中的“索引字段”必须在分表中都存在

     4.定义在合并表中的索引没有任何作用，索引是由分表控制的，例如两个分表中存在着同样的一个索引，那么在MERGE表中会有两个一样的索引，也就说每个分表的索引在merge表中都会存在，所以在merge表中不能根据该索引进行唯一性检索

     5.可以通过修改.mrg文件（在mysql对应的数据库目录中）来修改MERGE表，每个基本表的名字占一行。注意：修改后要通过FLUSH TABLES刷新表缓存。

 

创建成功后，直接访问合并表即可

 

上面的方法的PHP实现代码如下：

$con = new PDO("mysql:host=localhost;dbname=test", 'root', 'ajia123');

$sql = "select count(*) from good";

$rel = $con->query($sql)->fetch();

$totalRow = isset($rel[0]) ? $rel[0] : 1;       //合并表的总记录数

 

$tablePerRow = 1000000;

$item        = ceil( $totalRow/$tablePerRow );  //每个分表的记录数

 

for( $i=1; $i<=$item; $i++){

 

        $tableName[] = "g{$i}";

 

//先创建分表，再插入记录数

        $sql = "CREATE TABLE `g{$i}` (

                          `id` int(11) NOT NULL DEFAULT '0',

                          `name` char(20) NOT NULL DEFAULT '',

                          `create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

                          `the_date` int(8) DEFAULT '0'

                        ) ENGINE=MyISAM DEFAULT CHARSET=utf8;";

 

        $con->query($sql);

 

        $start = (($i-1)*$tablePerRow+1);

        $end   = ($i*$tablePerRow);

        $sql2  = "insert into g{$i} select * from good where id between {$start} and {$end}";

 

        $con->query($sql2);

}

 

//生成合并表

$allSql = "CREATE TABLE gg (

  `id` int(11) NOT NULL,

  `name` char(20) CHARACTER SET utf8 NOT NULL DEFAULT '',

  `create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

  `the_date` int(8) DEFAULT '0'

) TYPE=MERGE UNION=(".implode(",", $tableName).") INSERT_METHOD=LAST;";

 

$con->query($allSql);

 

 

第二种分表方式：在设计表的阶段就考虑需要分表的情况

举例情况：表good需要分表为10个分表（分表名称为：g1,g2,g3 ....），数据存储根据散列算法得出存取的分表表名

 

步骤一：创建分表和合并表的方式和代码与第一种方式的代码类似，只是不需插入数据到分表，而且表中需要有唯一值字段(我们假设该字段名为：unique_filed)

 

步骤二：根据散列算法获取对应数据记录应该存储到的分表名，从而将数据存储到对应的分表中去，代码如下：

 

        //$table:分表的前缀， $flag:记录的唯一字段值，$max:分表的总数

function get_hash_table($table, $flag ,$max=10) {

$hashTem = sprintf("%u", crc32($flag));

$hash    = intval(fmod($hashTem, $max));

return $table.$hash;

}

 

$theTableName = get_hash_table( "g", "user_19890" );

        其中$flag表示记录的唯一字段值，该值在我的理解是通过程序来生成一个唯一标示一条记录的值，而并不是表的主键，为什么需要程序自动生成呢，因为我们需要通过哈希算法get_hash_table来获得该记录存放的分表名称，同样我们在获取记录时候也可以通过get_hash_table方法得到该数据存储的分表名称，直接查询分表而不用通过合并表来查询，这样快速多了

 

 

步骤三：查询数据的分两种情况：在知道数据唯一字段值的情况下，首先通过get_hash_table方法获取分表名，直接查询该分表，另一种则直接通过查询合并表来获取数据

 

代码如下：

 

$con = new PDO("mysql:host=localhost;dbname=test", 'root', 'ajia123');

$item = 3;           //分表的总数

$childTable = "cg_"; //分表的表名前缀

 

for( $i=1; $i<=$item; $i++){

        $sql = "CREATE TABLE if not exists `{$childTable}{$i}` (

                          `unique_filed` char(30) NOT NULL DEFAULT '',

                          `create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

                          `the_date` int(8) DEFAULT '0'

                        ) ENGINE=MyISAM DEFAULT CHARSET=utf8;";

 

        $con->query($sql);

 

        $tableName[] = "{$childTable}{$i}";

}

 

        $allSql = "CREATE TABLE if not exists all_cg (

  `unique_filed` char(30) CHARACTER SET utf8 NOT NULL DEFAULT '',

  `create_time` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',

  `the_date` int(8) DEFAULT '0'

) TYPE=MERGE UNION=(".implode(",", $tableName).") INSERT_METHOD=LAST;";

 

$con->query($allSql);

 

 

 

//下面是数据存取

 

//生成唯一标示值

function get_unique_flag(){

        return uniqid().rand(1,10000);  //在高并发下可能出现重复，根据情况做调整

}

 

/*

 * $table 分表的前缀

 * $flag  记录的唯一字段值

 * $max   分表总数

 */

function get_hash_table($table, $flag ,$max = 3) {

        $hashTem = sprintf("%u", crc32($flag));

        $hash    = intval(fmod($hashTem, $max));

        return $table.$hash;

}

 

//插入数据，先获得分表名

$theFlag        = get_unique_flag();

$childTableName = get_hash_table( $childTable, $theFlag );

$sql = "insert into {$childTableName} (unique_filed) value ('{$theFlag}') ";

 

 

$con->query($sql);