4.测试MySQL的数据量

By youfang at: 2022-08-13

工作中遇到的表数据量会很大

SQL

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
CREATE TABLE `r_sku_storage_goods` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键ID',
  `sku_id` bigint NOT NULL COMMENT 'SKU ID',
  `storage_id` bigint NOT NULL DEFAULT '0' COMMENT '库点ID',
  `section_id` bigint NOT NULL DEFAULT '0' COMMENT '库区ID',
  `bin_id` bigint NOT NULL DEFAULT '0' COMMENT '库位ID',
  `goods_identity_code` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL COMMENT '物品识别码',
  `processing_plan_receipt_id` bigint NOT NULL COMMENT '加工单ID',
  `batch_number` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '批号',
  `expiry_date` date NOT NULL COMMENT '有效期',
  `production_date` date DEFAULT NULL COMMENT '生产日期',
  `status` int NOT NULL DEFAULT '1' COMMENT '物品状态：1-正常使用 10-删除',
  `purchase_price` decimal(10,2) DEFAULT NULL COMMENT '采购价格',
  `trace_storage_id` bigint NOT NULL DEFAULT '0' COMMENT '追溯库点ID(用来记录出库前所在的库点)',
  `consume_time` datetime DEFAULT NULL COMMENT '消耗时间',
  `settlement_time` datetime DEFAULT NULL COMMENT '结算时间',
  `create_user` bigint NOT NULL COMMENT '创建人ID',
  `create_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间,默认当前时间',
  `update_user` bigint DEFAULT NULL COMMENT '最后修改人ID',
  `update_time` datetime DEFAULT NULL COMMENT '最后修改时间',
  `remark` varchar(1024) CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `idx_sku_storage_goods_goodsIdentityCode` (`goods_identity_code`) USING HASH,
  KEY `idx_sku_storage_goods_skuid` (`sku_id`) USING BTREE,
  KEY `idx_sku_storage_goods_storageid` (`storage_id`) USING BTREE,
  KEY `idx_sku_storage_goods_expirydate` (`expiry_date`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='仓库物品表';

准备数据

随机数

MySQL中的随机数

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- 随机UUID
SELECT UUID_SHORT(); -- 纯数字
SELECT UUID();       -- UUID

-- 产生500~550之间的随机数
SELECT FLOOR( 500 + RAND() * (550 - 500));
-- 产生0到10000间的随机数
SELECT RAND() * 10000;          -- 带小数
SELECT FLOOR(RAND() * 10000);   -- 整数

-- 减少一天
SELECT DATE_SUB(now(), INTERVAL 2 DAY);
-- 日期格式化
SELECT DATE_FORMAT(DATE_SUB(now(), INTERVAL 2 DAY),'%Y%m%d');
组合使用，创建存储过程
1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_storage_goods`(IN total BIGINT)
BEGIN
DECLARE start_key BIGINT DEFAULT 0;
DECLARE max_id BIGINT DEFAULT 0;
SELECT COUNT(1) into max_id from product;
WHILE start_key<total
DO
SET max_id=max_id+1;
INSERT INTO `r_sku_storage_goods`(`sku_id`, `storage_id`, `section_id`, `bin_id`, `goods_identity_code`, `processing_plan_receipt_id`, `batch_number`, `expiry_date`, `production_date`, `status`, `purchase_price`, `trace_storage_id`, `consume_time`, `settlement_time`, `create_user`, `create_time`, `update_user`, `update_time`, `remark`) VALUES (FLOOR( 500 + RAND() * (550 - 500)), FLOOR( 800 + RAND() * (1000 - 800)), 0, 0, UUID_SHORT(), 110, DATE_FORMAT(DATE_SUB(now(), INTERVAL FLOOR(RAND() * 1095) DAY),'%Y%m%d'), '2030-08-01', '2020-07-01', 1, 3000.00, 0, NULL, NULL, 12, now(), 12, now(), NULL);
SET start_key=start_key+1;
COMMIT;
END WHILE;
END
测试

用CALL来调用函数：

1
2
3
4
5
6
7
8
-- 插入9条数据
call insert_storage_goods(9) ;
-- 插入50w数据
call insert_storage_goods(500000);
> OK
> 时间: 1963.974s


测试

插入数据的效率

插入50W数据要35分钟左右。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
call insert_storage_goods(500000)
> OK
> 时间: 2128.539s
call insert_storage_goods(500000)
> OK
> 时间: 2110.505s
call insert_storage_goods(500000)
> OK
> 时间: 2091.534s
call insert_storage_goods(500000)
> OK
> 时间: 2157.772s
call insert_storage_goods(500000)
> OK
> 时间: 2119.331s
call insert_storage_goods(500000)
> OK
> 时间: 2272.175s

ibd文件（硬盘文件）的大小

  • 700W 文件: 1.81 GB
  • 800W 文件: 2.07 GB
  • 1000W 文件: 2.57 GB

SQL执行时间

1
2
3
4
5
6
7
8
9
10
11
12
-- 100W数据量
SELECT COUNT(*) from r_sku_storage_goods
> OK
> 时间: 1.76s

SELECT COUNT(id) FROM r_sku_storage_goods
> OK
> 时间: 1.732s

SELECT COUNT(1) FROM r_sku_storage_goods
> OK
> 时间: 1.697s
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
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
-- SQL 


SELECT COUNT(*) from r_sku_storage_goods;
-- 100W数据： 时间:   1.667s
-- 200W数据： 时间:   5.096s
-- 300W数据： 时间:   6.297s
-- 400W数据： 时间:   6.708s
-- 500W数据： 时间:   9.926s
-- 600W数据： 时间:   11.574s
-- 700W数据： 时间:   11.777s
-- 800W数据： 时间:   15.222s
-- 900W数据： 时间:   16.786s
-- 1000W数据： 时间:  16.825s

SELECT * FROM r_sku_storage_goods WHERE goods_identity_code = '98918337795277389';
-- 100W数据： 时间:  0.004s
-- 200W数据： 时间:  0.004s
-- 300W数据： 时间:  0.004s
-- 400W数据： 时间:  0.004s
-- 500W数据： 时间:  0.004s
-- 600W数据： 时间:  0.004s
-- 700W数据： 时间:  0.004s
-- 800W数据： 时间:  0.004s
-- 900W数据： 时间:  0.004s
-- 1000W数据：时间:  0.004s

SELECT rssg.storage_id,count(*) as current_stock FROM r_sku_storage_goods rssg	WHERE rssg.storage_id > 0 GROUP BY rssg.storage_id;
-- 100W数据： 时间:  2.414s
-- 200W数据： 时间:  4.435s
-- 300W数据： 时间:  6.193s
-- 400W数据： 时间:  8.054s
-- 500W数据： 时间:  10.456s
-- 600W数据： 时间:  13.188s
-- 700W数据： 时间:  14.094s
-- 800W数据： 时间:  16.154s
-- 900W数据： 时间:  20.044s
-- 1000W数据：时间:  25.301s

SELECT *
        from (SELECT count(*) as unConsumptionCount, IFNULL(sum(purchase_price), 0.00) as unConsumptionAmount
              FROM r_sku_storage_goods
              where create_time > '2020-07-01'
                and create_time < '2020-12-01'
                and consume_time is null
                and settlement_time is null) as t1,
             (SELECT count(*) as unSettlementCount, IFNULL(sum(purchase_price), 0.00) as unSettlementAmount
              FROM r_sku_storage_goods
              where create_time > '2020-07-01'
                and create_time < '2020-12-01'
                and consume_time is not null
                and settlement_time is null) as t2,
             (SELECT count(*) as settlementCount, IFNULL(sum(purchase_price), 0.00) as settlementAmount
              FROM r_sku_storage_goods
              where create_time > '2020-07-01'
                and create_time < '2020-12-01'
                and consume_time is not null
                and settlement_time is not null) as t3;
-- 100W数据： 时间:  5.018s
-- 200W数据： 时间:  9.737s
-- 300W数据： 时间:  13.948s
-- 400W数据： 时间:  18.658s
-- 500W数据： 时间:  22.651s
-- 600W数据： 时间:  28.59s
-- 700W数据： 时间:  32.345s
-- 800W数据： 时间:  35.574s
-- 900W数据： 时间:  40.268s
-- 1000W数据：时间:  46.663s


SELECT sku_id, storage_id, section_id, bin_id, count(id) as total_number from r_sku_storage_goods
             WHERE storage_id > 0
             GROUP BY sku_id, storage_id, section_id, bin_id
-- 100W数据： 时间: 1.741s
-- 200W数据： 时间: 3.277s
-- 300W数据： 时间: 4.81s
-- 400W数据： 时间: 6.411s
-- 500W数据： 时间: 7.621s
-- 600W数据： 时间: 9.95s
-- 700W数据： 时间: 11.435s
-- 800W数据： 时间: 12.145s
-- 900W数据： 时间: 13.606s
-- 1000W数据：时间: 16.014s


select t.*, (@sum := @sum + t.number) as cume_number
from (SELECT rssg.sku_id,
						rssg.expiry_date,
						rssg.storage_id,
						rssg.section_id,
						rssg.bin_id,
						count(goods_identity_code) as number
		 FROM r_sku_storage_goods rssg
		 WHERE rssg.sku_id = 549
		 GROUP BY rssg.expiry_date, rssg.sku_id, rssg.storage_id, rssg.section_id, rssg.bin_id
		 ORDER BY rssg.expiry_date) t
				cross join
				(select @sum := 0) params
order by t.expiry_date desc;
-- 100W数据： 1.977s
-- 200W数据： 3.817s
-- 300W数据： 5.439s
-- 400W数据： 7.265s
-- 500W数据： 9.713s
-- 600W数据： 11.027s
-- 700W数据： 12.475s
-- 800W数据： 14.621s
-- 900W数据： 15.811s
-- 1000W数据：18.6s