mysql 表数据量太大优化方案--水平分表

Love The Way You Lie 2023-10-18 18:24 83阅读 0赞

由于数据库中表数据量太大，数据库设计不太合理，导致表中存放100多万条记录就查询非常慢，而且查询频率非常高，涉及的报表统计也比较多，插入修改删除频率也较高，对程序响应速度造成了很大影响。  
于是各种扒资料，方案也是五花八门的，顺便给自己做个记录. 然后顺便给自己矫正下之前的错误思想，1、创建视图的并不能提高查询速度，只是简化了sql语句复杂程度。  
网上大多方案为：

1、优化sql语句（这个是最简便的,也是在开发是一定要注意的）  
2、字段数据类型的正确适用（个人感觉较废，出现这问题的数据量已经非常大了，这个在设计库前期较适用）  
3、索引（加正确的索引）  
4、引擎（特点：MyISAM适合SELECT密集型的表，而InnoDB适合INSERT和UPDATE密集型的表，我的表既查询多，插入也不少，所以不适用）  
5、提升硬件（这个就不多说了，烧钱就能解决）  
6、读写分离 (暂时还未研究，改日再说)  
7、缓存（项目不太适用）  
8、表分区（但经过查阅资料发现都是在建表时所做的操作）  
9、拆表 （本文所记录的即是拆表）

拆表分为水平拆表和垂直拆表  
水平优缺点

水平拆分的优点是:  
不存在单库大数据和高并发的性能瓶颈  
应用端改造较少  
提高了系统的稳定性和负载能力  
缺点是：  
分片事务一致性难以解决  
跨节点Join性能差，逻辑复杂  
数据多次扩展难度跟维护量极大

垂直

垂直拆分的优点是：  
可以使得行数据变小，一个数据块(Block)就能存放更多的数据，在查询时就会减少I/O次数(每次查询时读取的Block 就少)  
可以达到最大化利用Cache的目的，具体在垂直拆分的时候可以将不常变的字段放一起，将经常改变的放一起  
数据维护简单  
缺点是：  
主键出现冗余，需要管理冗余列  
会引起表连接JOIN操作（增加CPU开销）可以通过在业务服务器上进行join来减少数据库压力  
依然存在单表数据量过大的问题（需要水平拆分）  
事务处理复杂  
（主要为了解决有的字段数据量较大又不需查询）

开始各种查询解决方案，发现写的各种高大上，奈何本人较为愚笨，发现看不懂，最后突然发现就是多建几个表将数据分别存进去

以下是我的建表语句

\`DROP TABLE IF EXISTS \`SO\_SALE\_ORDER\`;  
CREATE TABLE \`SO\_SALE\_ORDER\` (  
\`ID\` bigint(20) NOT NULL AUTO\_INCREMENT COMMENT '编号',  
\`ORDER\_TYPE\` char(1) DEFAULT '0' COMMENT '订单类型（0：美团外卖1：饿了吗2：微信预定3：普通订单）',  
\`ORDER\_CODE\` varchar(32) NOT NULL COMMENT '订单编号',  
\`PRICE\` decimal(20,4) NOT NULL COMMENT '应收',  
\`AMOUNT\` decimal(20,4) NOT NULL COMMENT '实收',  
\`NET\_RECEIPTS\` decimal(20,4) DEFAULT '0.0000' COMMENT '实收金额-对应找零',  
\`ODD\_CHANGE\` decimal(20,4) DEFAULT '0.0000' COMMENT '找零',  
\`COUPON\_PRICES\` decimal(10,2) DEFAULT '0.00' COMMENT '优惠券金额',  
\`DISCOUNT\_MONEY\` decimal(20,4) NOT NULL DEFAULT '0.0000' COMMENT '折扣',  
\`CUT\_MONEY\` decimal(20,4) NOT NULL DEFAULT '0.0000' COMMENT '抹零',  
\`BENEFIT\_MONEY\` decimal(20,4) NOT NULL DEFAULT '0.0000' COMMENT '优惠',  
\`PEOPLE\_NUM\` varchar(10) NOT NULL COMMENT '就餐人数',  
\`SALEDATE\` datetime NOT NULL COMMENT '销售日期',  
\`UPLOAD\_TIME\` datetime DEFAULT NULL COMMENT '上传订单时间',  
\`SHOP\_ID\` bigint(20) NOT NULL COMMENT '店面ID',  
\`CASHIER\_ID\` varchar(16) NOT NULL COMMENT '销售人员',  
\`CARD\_SHOP\_ID\` bigint(20) DEFAULT NULL COMMENT '开卡店面',  
\`CARD\_NUMBER\` varchar(255) DEFAULT NULL COMMENT '卡号',  
\`ECARD\_NUMBER\` varchar(255) DEFAULT NULL COMMENT '电子会员卡号',  
\`CARD\_FACECODE\` varchar(255) DEFAULT NULL COMMENT '会员脸部识别码',  
\`PRINCIPAL\` decimal(10,2) DEFAULT '0.00' COMMENT '本金',  
\`CONCESSION\` decimal(10,2) DEFAULT '0.00' COMMENT '优惠金',  
\`TABLE\_NAME\` varchar(100) DEFAULT NULL COMMENT '桌号',  
\`TABLE\_TYPE\` char(1) NOT NULL DEFAULT '0' COMMENT '桌台类型：0.本地或微信快餐 1.微信中餐',  
\`BESPOKE\_ID\` bigint(20) DEFAULT NULL COMMENT '微信中餐预约单号',  
\`CARD\_NUMERICAL\` decimal(11,0) DEFAULT NULL COMMENT '会员积分',  
\`CARD\_BALANCE\` decimal(11,2) DEFAULT NULL COMMENT '会员余额',  
\`COUPON\_ID\` bigint(20) DEFAULT '0' COMMENT '优惠券id',  
\`COUPON\_ENTITY\` text COMMENT '优惠券对象',  
\`MACHINE\_CODE\` varchar(32) NOT NULL COMMENT '机器代码',  
\`VERSION\_CODE\` int(4) DEFAULT '0' COMMENT '版本号',  
\`DELIVERY\_TIME\` datetime DEFAULT NULL COMMENT '送达时间',  
\`RECIPIENT\_NAME\` varchar(255) CHARACTER SET utf8mb4 DEFAULT NULL COMMENT '客户姓名',  
\`RECIPIENT\_PHONE\` varchar(255) DEFAULT NULL COMMENT '顾客联系方式',  
\`RECIPIENT\_ADDRESS\` varchar(255) DEFAULT NULL COMMENT '客户地址',  
\`BOX\_PRICE\` decimal(10,2) DEFAULT NULL COMMENT '餐盒费',  
\`FEE\_PRICE\` decimal(10,2) DEFAULT NULL COMMENT '运费',  
\`REMARKS\` varchar(255) DEFAULT NULL COMMENT '备注',  
\`ESTIMATE\_CLEAR\_FLAG\` char(1) DEFAULT '2' COMMENT '估清设置：0.手动 1.自动 2.不使用',  
\`UPDATE\_FLAG\` char(1) DEFAULT '1' COMMENT '赠菜标记（0：已修改 1：未修改）',  
\`DEL\_FLAG\` char(1) DEFAULT '0' COMMENT '退单标记（0：未退单1：已退单）',  
\`SERVICE\_TYPE\` char(1) CHARACTER SET utf8mb4 DEFAULT '0' COMMENT '餐饮类型：0.快餐 1.中餐 2.预约订单',  
PRIMARY KEY (\`ID\`,\`SHOP\_ID\`),  
KEY \`ORDER\_CODE\` (\`ORDER\_CODE\`) USING BTREE,  
KEY \`SHOP\_ID\` (\`SHOP\_ID\`) USING BTREE,  
KEY \`CARD\_NUMBER\` (\`CARD\_NUMBER\`) USING BTREE,  
KEY \`CARD\_SHOP\_ID\` (\`CARD\_SHOP\_ID\`) USING BTREE  
) ENGINE=InnoDB AUTO\_INCREMENT=1917263 DEFAULT CHARSET=utf8 COMMENT='销售订单';  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
34  
35  
36  
37  
38  
39  
40  
41  
42  
43  
44  
45  
46  
47  
48  
49  
50  
按上面的语句创建表1、2…  
按日期插入数据

insert into  
SO\_SALE\_ORDER1(  
\`ID\` ,  
\`ORDER\_TYPE\` ,  
\`ORDER\_CODE\`,  
\`PRICE\` ,  
\`AMOUNT\` ,  
\`NET\_RECEIPTS\` ,  
\`ODD\_CHANGE\` ,  
\`COUPON\_PRICES\` ,  
\`DISCOUNT\_MONEY\`,  
\`CUT\_MONEY\` ,  
\`BENEFIT\_MONEY\` ,  
\`PEOPLE\_NUM\` ,  
\`SALEDATE\` ,  
\`UPLOAD\_TIME\` ,  
\`SHOP\_ID\` ,  
\`CASHIER\_ID\` ,  
\`CARD\_SHOP\_ID\` ,  
\`CARD\_NUMBER\`,  
\`ECARD\_NUMBER\` ,  
\`CARD\_FACECODE\` ,  
\`PRINCIPAL\`,  
\`CONCESSION\` ,  
\`TABLE\_NAME\`,  
\`TABLE\_TYPE\` ,  
\`BESPOKE\_ID\` ,  
\`CARD\_NUMERICAL\` ,  
\`CARD\_BALANCE\`,  
\`COUPON\_ID\`,  
\`COUPON\_ENTITY\` ,  
\`MACHINE\_CODE\` ,  
\`VERSION\_CODE\` ,  
\`DELIVERY\_TIME\` ,  
\`RECIPIENT\_NAME\` ,  
\`RECIPIENT\_PHONE\`,  
\`RECIPIENT\_ADDRESS\`,  
\`BOX\_PRICE\`,  
\`FEE\_PRICE\` ,  
\`REMARKS\` ,  
\`ESTIMATE\_CLEAR\_FLAG\`,  
\`UPDATE\_FLAG\` ,  
\`DEL\_FLAG\`,  
\`SERVICE\_TYPE\`  
) select \`ID\` ,  
\`ORDER\_TYPE\` ,  
\`ORDER\_CODE\`,  
\`PRICE\` ,  
\`AMOUNT\` ,  
\`NET\_RECEIPTS\` ,  
\`ODD\_CHANGE\` ,  
\`COUPON\_PRICES\` ,  
\`DISCOUNT\_MONEY\`,  
\`CUT\_MONEY\` ,  
\`BENEFIT\_MONEY\` ,  
\`PEOPLE\_NUM\` ,  
\`SALEDATE\` ,  
\`UPLOAD\_TIME\` ,  
\`SHOP\_ID\` ,  
\`CASHIER\_ID\` ,  
\`CARD\_SHOP\_ID\` ,  
\`CARD\_NUMBER\`,  
\`ECARD\_NUMBER\` ,  
\`CARD\_FACECODE\` ,  
\`PRINCIPAL\`,  
\`CONCESSION\` ,  
\`TABLE\_NAME\`,  
\`TABLE\_TYPE\` ,  
\`BESPOKE\_ID\` ,  
\`CARD\_NUMERICAL\` ,  
\`CARD\_BALANCE\`,  
\`COUPON\_ID\`,  
\`COUPON\_ENTITY\` ,  
\`MACHINE\_CODE\` ,  
\`VERSION\_CODE\` ,  
\`DELIVERY\_TIME\` ,  
\`RECIPIENT\_NAME\` ,  
\`RECIPIENT\_PHONE\`,  
\`RECIPIENT\_ADDRESS\`,  
\`BOX\_PRICE\`,  
\`FEE\_PRICE\` ,  
\`REMARKS\` ,  
\`ESTIMATE\_CLEAR\_FLAG\`,  
\`UPDATE\_FLAG\` ,  
\`DEL\_FLAG\`,  
\`SERVICE\_TYPE\` from SO\_SALE\_ORDER where SALEDATE BETWEEN '2016-01-01' AND '2017-01-01' //插入2016-2017的数据  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
34  
35  
36  
37  
38  
39  
40  
41  
42  
43  
44  
45  
46  
47  
48  
49  
50  
51  
52  
53  
54  
55  
56  
57  
58  
59  
60  
61  
62  
63  
64  
65  
66  
67  
68  
69  
70  
71  
72  
73  
74  
75  
76  
77  
78  
79  
80  
81  
82  
83  
84  
85  
86  
这个方案要从代码中也需要较大需改，需增加定时创建表，按日期查询、修改删除，（注：可用其他字段分表如 ID …）

另一自己所想方案做个记录，将表分割成历史表，将数据按今年之前分割存储另一张表（冷数据，一般不会详细查询），另建一张结果表用以存储冷数据按年或月数据的统计，若涉及统计直接从结果表中取出，用以提高效率。

自己知识底蕴较为浅薄，如有不对，欢迎来怼，毕竟我是不会承认的（把对的留下，自己偷偷修改）  
\---------------------  
作者：拉风的蜗牛  
来源：CSDN  
原文：https://blog.csdn.net/qq\_41615095/article/details/86009872  
版权声明：本文为博主原创文章，转载请附上博文链接！