MySQL常用分表分库方式

最新推荐文章于 2024-08-22 09:44:18 发布

chuanguan1820

最新推荐文章于 2024-08-22 09:44:18 发布

阅读量140

点赞数

文章标签： php 数据结构与算法数据库

原文链接：https://my.oschina.net/chinaliuhan/blog/3074465

版权

[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;

转载于:https://my.oschina.net/chinaliuhan/blog/3074465

chuanguan1820

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL常用分表分库方式

[TOC]MySQL常用分表分库方式一般都喜欢分表要么借助第三方工具,比如MySQL各种各样的中间件.这样会比较省事.但是不同的中间件也有不同的要求和坑,中间件对数据一直要求高,容易出问题.这里推荐之前用过的中间件360的Atla和MyCat,挺不错的.垂直分表垂直分表就是对表进...
复制链接

扫一扫