开发项目,经常碰到订单量暴增的问题,简单点处理,需要给订单表做分表。分表后会碰到各种麻烦,以Laravel框架为例,记录一下分表ORM的改造。
把订单表按用户ID取模分为3张表:
- 数据库表
有 users表。3张orders和orders_goods表,(表sql在文章结尾)
- ORM
新建_Model.php文件。作为基类
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
class _Model extends Model
{
/**
* ******************************************************
* 分表 start
*/
protected $suffix = null;
// 设置表后缀
public function setSuffix($suffix)
{
$this->suffix = $suffix;
if ($suffix !== null) {
$this->table = $this->getTable() . '_' . $suffix;
}
}
// 提供一个静态方法设置表后缀
public static function suffix($suffix)
{
$instance = new static;
$instance->setSuffix($suffix);
return $instance->newQuery();
}
// 创建新的"orders_{$suffix}"的模型实例并返回
public function newInstance($attributes = [], $exists = false)
{
$model = parent::newInstance($attributes, $exists);
$model->setSuffix($this->suffix);
return $model;
}
/**
* 分表 end
* ******************************************************
*/
}
Order.php继承_Model
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
class Order extends _Model
{
protected $table = 'o_orders';
public $timestamps = false;
public static function getSuffix($user_id)
{
return ($user_id % 3);
}
/**
* 生成订单号,19位数,剩下一位数做分单的自增
* TODO:处理唯一问题
* @return string
*/
public static function createOrderNo()
{
$microtime = microtime(true);
$arr = explode('.', $microtime);
$order_no = date("ymdHis", $arr[0]) . str_pad($arr[1], 4, 0);
return $order_no . rand(0, 9);
}
}
OrderGoods.php继承_Model
<?php
namespace App\Models;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Support\Facades\DB;
class OrderGoods extends _Model
{
protected $table = 'o_orders_goods';
public $timestamps = false;
public static function getSuffix($user_id)
{
return ($user_id % 3);
}
}
- 订单入库
public function createOrder()
{
$goods_name_arr = ['华为P10', '小米Note 9', '联想thinkpad', '苹果5S'];
$user_id = User::inRandomOrder()->value('id');
// 订单
$orderModel = new Order();
$orderModel->setSuffix(Order::getSuffix($user_id));
$orderModel->user_id = $user_id;
$orderModel->order_no = Order::createOrderNo();
$orderModel->created_at = date('Y-m-d H:i:s');
$orderModel->save();
// 订单商品
$order_id = $orderModel->id;
$goods_len = rand(1, 2);
for ($i = 0; $i < $goods_len; $i++) {
$orderGoodsModel = new OrderGoods();
$orderGoodsModel->setSuffix(OrderGoods::getSuffix($user_id));
$orderGoodsModel->goods_name = $goods_name_arr[rand(0, count($goods_name_arr) - 1)];
$orderGoodsModel->order_id = $order_id;
$orderGoodsModel->save();
}
}
- 表SQL
用户表
DROP TABLE IF EXISTS `o_users`;
CREATE TABLE `o_users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(30) NOT NULL DEFAULT '' COMMENT '姓名',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='会员';
insert INTO `o_users` (`name`) VALUES
('张三'),
('李四'),
('王五'),
('小明'),
('小宏');
订单 和订单商品表
-- 存储过程创建10个表
#水平分表快速创建多张表
# 删除已存在的存储过程
DROP PROCEDURE IF EXISTS create_table_resource_user_proc;
#创键存储过程
DELIMITER ;;
CREATE PROCEDURE create_table_resource_user_proc()
BEGIN
DECLARE i INT DEFAULT(0);
DECLARE n INT DEFAULT(10); # 表数量
WHILE (i < n) DO
# 拼接sql
SET @sqlStr:=CONCAT("CREATE TABLE `jk_resource_user_",i,"` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`user_id` int(11) DEFAULT NULL COMMENT '用户ID',
`resource_id` int(11) DEFAULT NULL COMMENT '采集的资源ID',
`created_at` datetime DEFAULT NULL COMMENT '采集时间',
`is_export` tinyint(1) DEFAULT '0' COMMENT '是否已导出',
`is_in_addresslist` tinyint(1) DEFAULT '0' COMMENT '是否已列入通讯录',
`is_send_msg` tinyint(1) DEFAULT '0' COMMENT '是否已发送过短信',
`is_tel` tinyint(1) DEFAULT '0' COMMENT '是否已打电话',
`is_delete` tinyint(1) DEFAULT '0' COMMENT '是否已删除',
`collection_count` int(11) DEFAULT '0' COMMENT '采集批次',
`collection_from` tinyint(1) DEFAULT '0' COMMENT '1、全国客源 2 分类客源 3、范围客源 4、同城客源 5、全网客源 9、自定义',
`keyword` varchar(100) NOT NULL DEFAULT '' COMMENT '搜索时候的关键字',
`is_new` tinyint(1) NOT NULL DEFAULT '0' COMMENT '记录方便统计去重: 1、新数据 0、旧数据',
`is_del` int(1) NOT NULL DEFAULT '0' COMMENT '1、软删除',
`remark` varchar(200) NOT NULL DEFAULT '' COMMENT '备注',
PRIMARY KEY (`id`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
#用PREPARE将该字符串转化为MySQL脚本中的Prepared Statements(简写stmt)
PREPARE stmt from @sqlStr;
#执行stmt即可获得输出结果
EXECUTE stmt;
#通过DEALLOCATE释放该Prepared Statements
DEALLOCATE PREPARE stmt;
SET i = i + 1;
END WHILE;
END
;;
DELIMITER ;
CALL create_table_resource_user_proc();
-- 存储过程创建10个订单表处理分表案例
#水平分表快速创建多张表
# 删除已存在的存储过程
DROP PROCEDURE IF EXISTS create_table_order_proc;
#创键存储过程
DELIMITER ;;
CREATE PROCEDURE create_table_order_proc()
BEGIN
DECLARE i INT DEFAULT(0);
DECLARE n INT DEFAULT(3); # 表数量
WHILE (i < n) DO
# 拼接sql
SET @sqlStr:=CONCAT("CREATE TABLE `o_orders_",i,"` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_no` varchar(40) NOT NULL DEFAULT '' COMMENT '订单编号',
`user_id` int(11) NOT NULL DEFAULT '0' COMMENT '用户ID',
`created_at` datetime DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`),
KEY `idx_order_no` (`order_no`),
KEY `idx_user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
#用PREPARE将该字符串转化为MySQL脚本中的Prepared Statements(简写stmt)
PREPARE stmt from @sqlStr;
#执行stmt即可获得输出结果
EXECUTE stmt;
#通过DEALLOCATE释放该Prepared Statements
DEALLOCATE PREPARE stmt;
SET i = i + 1;
END WHILE;
END
;;
DELIMITER ;
CALL create_table_order_proc();
-- 存储过程创建10个订单表处理分表案例
#水平分表快速创建多张表
# 删除已存在的存储过程
DROP PROCEDURE IF EXISTS create_table_order_goods_proc;
#创键存储过程
DELIMITER ;;
CREATE PROCEDURE create_table_order_goods_proc()
BEGIN
DECLARE i INT DEFAULT(0);
DECLARE n INT DEFAULT(3); # 表数量
WHILE (i < n) DO
# 拼接sql
SET @sqlStr:=CONCAT("CREATE TABLE `o_orders_goods_",i,"` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`order_id` int(11) NOT NULL DEFAULT '0' COMMENT '订单ID',
`goods_name` varchar(200) NOT NULL DEFAULT '' COMMENT '商品名称',
PRIMARY KEY (`id`),
KEY `idx_order_id` (`order_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;");
#用PREPARE将该字符串转化为MySQL脚本中的Prepared Statements(简写stmt)
PREPARE stmt from @sqlStr;
#执行stmt即可获得输出结果
EXECUTE stmt;
#通过DEALLOCATE释放该Prepared Statements
DEALLOCATE PREPARE stmt;
SET i = i + 1;
END WHILE;
END
;;
DELIMITER ;
CALL create_table_order_goods_proc();