MySQL水平拆分及垂直拆分优化样例

水平拆分或垂直拆分

将数据量特别大的表，将其做水平或垂直拆分。水平拆分即将查询频率很低的字段另外拆成一张表，垂直拆分则是按某个维度将记录分成不同的表，两种拆分方式可以尝试一种或结合使用。

Sample

垂直拆分

1.准备，为了生成较多的实验数据，多次执行：

INSERT INTO bus_zcfz_yingshou_detail
SELECT * FROM bus_zcfz_yingshou_detail
;
最终bus_zcfz_yingshou_detail 总表记录数60w条。

2.原语句压力测试：结果见 测试结果对比图

mysqlslap -c 50 -uroot -p0000 --query="SELECT count(*) from fxst.bus_zcfz_yingshou_detail where iyear=2020 and imonth>=4" --create-schema fxst

3.创建分表：

create table bus_zcfz_yingshou_detail_2019 as 
SELECT * 
from bus_zcfz_yingshou_detail 
where iyear<=2019
;
create table bus_zcfz_yingshou_detail_2020 as 
SELECT * 
from bus_zcfz_yingshou_detail 
where iyear>2019
;
bus_zcfz_yingshou_detail_2020 分表记录数37w条

4.分表后测试 结果见 测试结果对比图

mysqlslap -c 50 -uroot -p0000 --query="SELECT count(*) from fxst.bus_zcfz_yingshou_detail_2020 where iyear=2020 and imonth>=4" --create-schema fxst

5.测试结果对比：
①耗费时间

分表后的时间要比分表前减少近4秒。

②CPU使用情况：

左边的是未分表，右边的是分表后的，使用率虽然都是100%，但分表后耗时明显变短。

6.分表查询存储过程样例

show CREATE PROCEDURE `p_分表查询样例`;

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_分表查询样例`(IN `_date` date)
BEGIN
-- 根据传入的日期参数，计算出所在的年份分表，然后返回数据
-- 定义变量信息
DECLARE year_,month_ VARCHAR(20);
DECLARE table_name VARCHAR(100);

set year_=year(_date);
set month_=month(_date);
set @sql_=concat(
              "select count(*) from ",
              "bus_zcfz_yingshou_detail_",year_," "
              "where iyear=",year_," ",
              "and imonth=",month_," "
             );
PREPARE sql_ from @sql_; 
EXECUTE sql_;
-- 提交事务
COMMIT;
END

备注：
上面的按年份进行水平拆分的方式，如果查询是跨年的，就需要修改原有的查询语句，从相应的分表查询后再合并起来。

水平拆分

• 创建新表结构，注意一定要有主键 id，新表在维度字段上将名称去掉，在值字段上将各月数据去掉。

CREATE TABLE `bus_zcfz_yingshou_detail_cols` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `ddate` date DEFAULT NULL COMMENT '数据日期',
  `iyear` int(4) DEFAULT NULL COMMENT '年份',
  `imonth` int(2) DEFAULT NULL COMMENT '月份',
  `iday` int(2) DEFAULT NULL COMMENT '数据天数',
  `ctype` varchar(10) DEFAULT NULL COMMENT '数据类型',
  `cbk_code` varchar(20) DEFAULT NULL COMMENT '编码1',
  `cxw_code` varchar(20) DEFAULT NULL COMMENT '编码2',
  `cjyt_code` varchar(20) DEFAULT NULL COMMENT '编码3',
  `cfr_code` varchar(20) DEFAULT NULL COMMENT '公司编码',
  `ccus_code` varchar(20) DEFAULT NULL COMMENT '客户编码',
  `iysyue_qichu` double(18,2) DEFAULT NULL COMMENT '应收余额-期初',
  `iysyue_benyue` double(18,2) DEFAULT NULL COMMENT '应收余额-本月',
  `iysyue_tongqi` double(18,2) DEFAULT NULL COMMENT '应收余额-同期',
  PRIMARY KEY (`id`),
  KEY `cxw_code` (`cxw_code`)
) ENGINE=InnoDB AUTO_INCREMENT=607777 DEFAULT CHARSET=utf8
;

• 插入数据

TRUNCATE bus_zcfz_yingshou_detail_cols;
INSERT INTO bus_zcfz_yingshou_detail_cols
(ddate,iyear,imonth,iday,ctype,cbk_code,cxw_code,cjyt_code,
 cfr_code,ccus_code,iysyue_qichu,iysyue_benyue,iysyue_tongqi )
SELECT 
 ddate,iyear,imonth,iday,ctype,cbk_code,cxw_code,cjyt_code,
 cfr_code,ccus_code,iysyue_qichu,iysyue_benyue,iysyue_tongqi
from bus_zcfz_yingshou_detail
;

• 对比测试

-- 0.677s
SELECT 
 -- t0.cxw_code,t1.cname,t0.cjyt_code,t0.cfr_code,t0.iysyue_qichu
 count(*)
FROM bus_zcfz_yingshou_detail t0 
LEFT JOIN sys_department t1
on t0.cxw_code=t1.ccode
where t0.iyear=2020 and imonth=4 and iday=30
;
-- 0.541s
SELECT 
 -- t0.cxw_code,t1.cname,t0.cjyt_code,t0.cfr_code,t0.iysyue_qichu
  count(*)
FROM bus_zcfz_yingshou_detail_cols t0 
LEFT JOIN sys_department t1
on t0.cxw_code=t1.ccode
where t0.iyear=2020 and imonth=4 and iday=30
;

结果：拆分后的新表的查询时间0.541s，拆分前是0.677s，优化还是比较明显的。

备注

附录

参考文章：https://database.51cto.com/art/201809/583857.htm