[TOC]
MySQL常用分表分库方式
一般都喜欢分表要么借助第三方工具,比如MySQL各种各样的中间件.这样会比较省事.
但是不同的中间件也有不同的要求和坑,中间件对数据一直要求高,容易出问题.
这里推荐之前用过的中间件360的Atla和MyCat,挺不错的.
垂直分表
- 垂直分表就是对表进行竖着切一刀
垂直分表一般都没有什么难度, 比如将20个字段的表,砍掉10个挪到其他表.
如: 假设一个简单的用户表 user
CREATE TABLE `user_test` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`invite_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '邀请人id',
`user_email` char(60) NOT NULL COMMENT '登录邮箱',
`reg_type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '用户注册标识',
`user_mobile` char(15) NOT NULL DEFAULT '0' COMMENT '登录手机',
`auth_level` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '用户认证等级',
`nickname` char(15) NOT NULL DEFAULT '' COMMENT '昵称',
`password` char(100) NOT NULL DEFAULT '' COMMENT '密码',
`trade_password` char(100) NOT NULL DEFAULT '' COMMENT '交易密码',
`activite_status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '状态(0-未激活/1-激活)',
`delete_status` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '状态(0-正常状态/1-准备删除)',
`vip_level` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT 'VIP等级',
`notify_mobile` char(15) NOT NULL DEFAULT '0' COMMENT '订阅手机',
`notify_email` char(60) NOT NULL DEFAULT '' COMMENT '订阅邮箱',
`name` char(32) NOT NULL DEFAULT '' COMMENT '姓名',
`id_number` char(18) NOT NULL DEFAULT '0' COMMENT '身份证号',
`id_type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '证件类型',
`auth_status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '实名认证状态(0-未实名/1-实名成功/2-失败)',
`auth_coutry` char(30) NOT NULL DEFAULT '0' COMMENT '实名认证国籍',
`auth_msg` varchar(250) NOT NULL DEFAULT '' COMMENT '实名认证审核评语',
`create_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
`create_ip` char(15) NOT NULL DEFAULT '0.0.0.0' COMMENT '创建ip',
`update_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
`update_ip` char(15) NOT NULL DEFAULT '0.0.0.0' COMMENT '更新ip',
PRIMARY KEY (`id`),
KEY `intive_id` (`intive_id`),
KEY `user_email` (`user_email`) USING BTREE,
KEY `user_mobile` (`user_mobile`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户';
可以看到上面这个表实际上是比较大的(有的公司可能不嫌大)
实际上所有的用户信息都放在一起的话比这个还要大的多,毕竟这只是一个用户表的案例.
当这个表再大的时候就需要分表了(实际上一般在设计之初就会把用户的信息定为两个表)
一个必备信息表: user
; 一个非必备信息表: user_info
; 或者说,一个是热数据经常要用,一个没那么热不常用.
user
表
CREATE TABLE `user` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`invite_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '邀请人id',
`user_email` char(60) NOT NULL COMMENT '登录邮箱',
`reg_type` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '用户注册标识',
`user_mobile` char(15) NOT NULL DEFAULT '0' COMMENT '登录手机',
`auth_level` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '用户认证等级',
`nickname` char(15) NOT NULL DEFAULT '' COMMENT '昵称',
`password` char(32) NOT NULL DEFAULT '' COMMENT '密码',
`pay_password` char(32) NOT NULL DEFAULT '' COMMENT '支付密码',
`lock_status` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '状态(0-未锁定账户/1-账户锁定)',
`activite_status` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '状态(0-未激活/1-激活)',
`create_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
`create_ip` char(15) NOT NULL DEFAULT '0.0.0.0' COMMENT '创建ip',
`update_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
`update_ip` char(15) NOT NULL DEFAULT '0.0.0.0' COMMENT '更新ip',
PRIMARY KEY (`id`),
KEY `intive_id` (`intive_id`),
KEY `user_email` (`user_email`) USING BTREE,
KEY `user_mobile` (`user_mobile`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户';
user_info
表
CREATE TABLE `user_info` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
`user_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '用户ID',
`notify_mobile` char(15) NOT NULL DEFAULT '0' COMMENT '订阅手机',
`notify_email` char(60) NOT NULL DEFAULT '' COMMENT '订阅邮箱',
`name` char(15) NOT NULL DEFAULT '' COMMENT '姓名',
`id_number` char(18) NOT NULL DEFAULT '0' COMMENT '身份证号',
`id_type` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '证件类型',
`auth_status` tinyint(4) unsigned NOT NULL DEFAULT '0' COMMENT '实名认证状态(0-未实名/1-实名成功/2-失败)',
`auth_coutry` char(30) NOT NULL DEFAULT '0' COMMENT '实名认证国籍',
`auth_msg` varchar(250) NOT NULL DEFAULT '' COMMENT '实名认证审核评语',
`create_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '创建时间',
`create_ip` char(15) NOT NULL DEFAULT '0.0.0.0' COMMENT '创建ip',
`update_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
`update_ip` char(15) NOT NULL DEFAULT '0.0.0.0' COMMENT '更新ip',
PRIMARY KEY (`id`),
KEY `user_id` (`user_id`),
KEY `user_email` (`user_email`) USING BTREE,
KEY `user_mobile` (`user_mobile`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户';
基本上垂直分表没有什么难度,按照字段热度切割就行了.
如果真的都很热的话直接做缓存吧,修改的时候只修改数据库,直接把缓存删了.
水平分表
水平分表也没有什么难度, 稍微有点难的地方, 为分表后的查询工作.
主要分表方式
- 按日期分表
- 取模算法
- 哈希算法
- 按日期分表
简单表结构,假设这样的表有10个,从money_0到money_9,每个表中存1000万个用户的信息.每个用户不止一条数据
CREATE TABLE `money_0` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`user_id` int(11) unsigned NOT NULL DEFAULT '0' COMMENT 'uid',
`country` char(10) NOT NULL DEFAULT '' COMMENT '国家代号',
`money` decimal(22,2) NOT NULL DEFAULT '0.0000000000' COMMENT '锁定资产',
PRIMARY KEY (`id`),
UNIQUE KEY `uid_country` (`user_id`,`coutry`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
按日期分表
这个很简单,基本没有什么难度. 比如每个月一张表,把新增表的表名设置为该月的时间戳或时间格式即可.
<?php
$tableNameFormt = 'money_';
echo $tableNameFormt . date('Y-m', strtotime('today')). PHP_EOL;
取模算法- 按范围分
注意: 大整数溢出问题.
- 简单的取模方式
直接用用户id最后一位和表的个数取余,是几就放到哪个表中.
这种方式被除数如果不是5和10的话做不到平均分配
该方式一旦定下多少个表, 后期就没法改了.因为5个表1-9和5取余和10个表1-9和100取余的结果是一样的.
而如果100个表的话, 我们取后3位的话应该可行.但是我没试过也不推荐
所以需要提前把所有表都准备好,今后要扩容也很要命.
- 复杂的取模方式
这种方式比如每10万用户一个表,当用户增长到1个亿,就需要1000个表.注意要维护表的不断新增.
该方式不限于多少个表, 对今后的扩容来说不是事.
<?php
$userId = 1963341; //用户id
$rowCount = 100000; //每个表存多少数据
$tableNameFormt = 'money_'; //表名格式
$tableIndex = ($userId - ($userId % $rowCount)) / $rowCount; //计算该用户的信息保存在哪个表中
$tableName = $tableNameFormt . $tableIndex; //拼装完整的表名
echo $tableName . PHP_EOL;
哈希算法- 按范围分
- 案例1
<?php
$userId = 1963341;
$crcInt = crc32($userId); //注意:crc32()在64位和32位的结果不一样,crc32返回的结果在32位机上会产生溢出,所以结果可能为负数
$tableNameFormt = 'money_';
if ( $crcInt < 0 ) {
//容错处理
$hashId = "0".substr(abs($crcInt), 0, 1);
} else {
//正常获取到的hash
$hashId = substr($crcInt, 0, 2);
}
//拼装表名
$tableName = $hashId . $hashId;
- 案例2
<?php
/**
* getTableName 获取用户数据保存在哪个表中.
*
* @param [int] $id 用户ID
* @param [int] $tableCount 分成多少个表
*
* @return bool
*/
function getTableName($id, $tableCount)
{
$md5 = md5($id);
$str1 = substr($md5, 0, 2);
$str2 = substr($md5, -2, 2);
$newStr = intval(intval($str1.$str2, 16));
$hashID = $newStr % $tableCount + 1;
return $hashID;
}
- 案例3
直接取md5()也有用uniquid()的最后一个字符, 该字符是0-9a-z攻击36个字符.可以平均分为36个表.
<?php
$userId = 1963341; //用户id
$rowCount = 100000; //每个表存多少数据
$tableNameFormt = 'money_'; //表名格式
$tableNameIndex = substr( md5( $userId ), -2 );
$tabName = $tableNameFormt. $tableNameIndex;