mysql-復(fù)合分區(qū)(range+hash)示例

復(fù)合分區(qū)示例

本次復(fù)合分區(qū)使用Range+hash做分區(qū)

1.按照時間做range分區(qū)，本次模擬通過一個小時做間隔。

2.對省份編碼做hash分區(qū)，分了三個。

3.此次分區(qū)沒有對分區(qū)做磁盤上對劃分。

SQL代碼



SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for test_yd_table_range
-- ----------------------------
DROP TABLE IF EXISTS `test_yd_table`;
CREATE TABLE `test_yd_table` (
  `mail_no` char(13) COLLATE utf8_bin NOT NULL,
  `send_province_code` int(6) NOT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `receive_province_code` int(6) NOT NULL,
  PRIMARY KEY (`mail_no`,`send_province_code`,`create_time`,`receive_province_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin


/*!50500  PARTITION BY RANGE ( UNIX_TIMESTAMP(create_time))
subpartition by HASH(`send_province_code`)
SUBPARTITIONS 3 (
partition p2019120500 VALUES LESS THAN (1575475200),
partition p2019120501 VALUES LESS THAN (1575478800),
partition p2019120502 VALUES LESS THAN (1575482400),
partition p2019120503 VALUES LESS THAN (1575486000),
partition p2019120504 VALUES LESS THAN (1575489600),
partition p2019120505 VALUES LESS THAN (1575493200),
partition p2019120506 VALUES LESS THAN (1575496800),
partition p2019120507 VALUES LESS THAN (1575500400),
partition p2019120508 VALUES LESS THAN (1575504000),
partition p2019120509 VALUES LESS THAN (1575507600),
partition p2019120510 VALUES LESS THAN (1575511200),
partition p2019120511 VALUES LESS THAN (1575514800),
partition p2019120512 VALUES LESS THAN (1575518400),
partition p2019120513 VALUES LESS THAN (1575522000),
partition p2019120514 VALUES LESS THAN (1575525600),
partition p2019120515 VALUES LESS THAN (1575529200),
partition p2019120516 VALUES LESS THAN (1575532800),
partition p2019120517 VALUES LESS THAN (1575536400),
partition p2019120518 VALUES LESS THAN (1575540000),
partition p2019120519 VALUES LESS THAN (1575543600),
partition p2019120520 VALUES LESS THAN (1575547200),
partition p2019120521 VALUES LESS THAN (1575550800),
partition p2019120522 VALUES LESS THAN (1575554400),
partition p2019120523 VALUES LESS THAN (1575558000)
)*/;

創(chuàng)表之后，打開數(shù)據(jù)庫底層文件


文件大小             文件名
98304   test_yd_table#P#p2019120500#SP#p2019120500sp0.ibd
98304   test_yd_table#P#p2019120500#SP#p2019120500sp1.ibd
98304   test_yd_table#P#p2019120500#SP#p2019120500sp2.ibd
98304   test_yd_table#P#p2019120501#SP#p2019120501sp0.ibd
98304   test_yd_table#P#p2019120501#SP#p2019120501sp1.ibd
98304   test_yd_table#P#p2019120501#SP#p2019120501sp2.ibd
98304   test_yd_table#P#p2019120502#SP#p2019120502sp0.ibd
98304   test_yd_table#P#p2019120502#SP#p2019120502sp1.ibd
98304   test_yd_table#P#p2019120502#SP#p2019120502sp2.ibd
98304   test_yd_table#P#p2019120503#SP#p2019120503sp0.ibd
98304   test_yd_table#P#p2019120503#SP#p2019120503sp1.ibd
98304   test_yd_table#P#p2019120503#SP#p2019120503sp2.ibd
98304   test_yd_table#P#p2019120504#SP#p2019120504sp0.ibd
98304   test_yd_table#P#p2019120504#SP#p2019120504sp1.ibd
98304   test_yd_table#P#p2019120504#SP#p2019120504sp2.ibd
98304   test_yd_table#P#p2019120505#SP#p2019120505sp0.ibd
98304   test_yd_table#P#p2019120505#SP#p2019120505sp1.ibd
98304   test_yd_table#P#p2019120505#SP#p2019120505sp2.ibd
98304   test_yd_table#P#p2019120506#SP#p2019120506sp0.ibd
98304   test_yd_table#P#p2019120506#SP#p2019120506sp1.ibd
98304   test_yd_table#P#p2019120506#SP#p2019120506sp2.ibd
98304   test_yd_table#P#p2019120507#SP#p2019120507sp0.ibd
98304   test_yd_table#P#p2019120507#SP#p2019120507sp1.ibd
98304   test_yd_table#P#p2019120507#SP#p2019120507sp2.ibd
98304   test_yd_table#P#p2019120508#SP#p2019120508sp0.ibd
98304   test_yd_table#P#p2019120508#SP#p2019120508sp1.ibd
98304   test_yd_table#P#p2019120508#SP#p2019120508sp2.ibd
98304   test_yd_table#P#p2019120509#SP#p2019120509sp0.ibd
98304   test_yd_table#P#p2019120509#SP#p2019120509sp1.ibd
98304   test_yd_table#P#p2019120509#SP#p2019120509sp2.ibd
98304   test_yd_table#P#p2019120510#SP#p2019120510sp0.ibd
98304   test_yd_table#P#p2019120510#SP#p2019120510sp1.ibd
98304   test_yd_table#P#p2019120510#SP#p2019120510sp2.ibd
98304   test_yd_table#P#p2019120511#SP#p2019120511sp0.ibd
98304   test_yd_table#P#p2019120511#SP#p2019120511sp1.ibd
98304   test_yd_table#P#p2019120511#SP#p2019120511sp2.ibd
98304   test_yd_table#P#p2019120512#SP#p2019120512sp0.ibd
98304   test_yd_table#P#p2019120512#SP#p2019120512sp1.ibd
98304   test_yd_table#P#p2019120512#SP#p2019120512sp2.ibd
98304   test_yd_table#P#p2019120513#SP#p2019120513sp0.ibd
98304   test_yd_table#P#p2019120513#SP#p2019120513sp1.ibd
98304   test_yd_table#P#p2019120513#SP#p2019120513sp2.ibd
98304   test_yd_table#P#p2019120514#SP#p2019120514sp0.ibd
98304   test_yd_table#P#p2019120514#SP#p2019120514sp1.ibd
98304   test_yd_table#P#p2019120514#SP#p2019120514sp2.ibd
98304   test_yd_table#P#p2019120515#SP#p2019120515sp0.ibd
98304   test_yd_table#P#p2019120515#SP#p2019120515sp1.ibd
98304   test_yd_table#P#p2019120515#SP#p2019120515sp2.ibd
98304   test_yd_table#P#p2019120516#SP#p2019120516sp0.ibd
98304   test_yd_table#P#p2019120516#SP#p2019120516sp1.ibd
98304   test_yd_table#P#p2019120516#SP#p2019120516sp2.ibd
98304   test_yd_table#P#p2019120517#SP#p2019120517sp0.ibd
98304   test_yd_table#P#p2019120517#SP#p2019120517sp1.ibd
98304   test_yd_table#P#p2019120517#SP#p2019120517sp2.ibd
98304   test_yd_table#P#p2019120518#SP#p2019120518sp0.ibd
98304   test_yd_table#P#p2019120518#SP#p2019120518sp1.ibd
98304   test_yd_table#P#p2019120518#SP#p2019120518sp2.ibd
98304   test_yd_table#P#p2019120519#SP#p2019120519sp0.ibd
98304   test_yd_table#P#p2019120519#SP#p2019120519sp1.ibd
98304   test_yd_table#P#p2019120519#SP#p2019120519sp2.ibd
98304   test_yd_table#P#p2019120520#SP#p2019120520sp0.ibd
98304   test_yd_table#P#p2019120520#SP#p2019120520sp1.ibd
98304   test_yd_table#P#p2019120520#SP#p2019120520sp2.ibd
98304   test_yd_table#P#p2019120521#SP#p2019120521sp0.ibd
98304   test_yd_table#P#p2019120521#SP#p2019120521sp1.ibd
98304   test_yd_table#P#p2019120521#SP#p2019120521sp2.ibd
98304   test_yd_table#P#p2019120522#SP#p2019120522sp0.ibd
98304   test_yd_table#P#p2019120522#SP#p2019120522sp1.ibd
98304   test_yd_table#P#p2019120522#SP#p2019120522sp2.ibd
98304   test_yd_table#P#p2019120523#SP#p2019120523sp0.ibd
98304   test_yd_table#P#p2019120523#SP#p2019120523sp1.ibd
98304   test_yd_table#P#p2019120523#SP#p2019120523sp2.ibd
8732    test_yd_table.frm

創(chuàng)建完畢后，模擬數(shù)據(jù)插入，啟動Springboot。

    @RequestMapping("testYDListSave")
    public String testYDListSave() {
        long start = System.currentTimeMillis();
        for (int i = 0; i < 10000; i++) {
            testYdListService.save();
            System.out.println(i);
        }
        long end = System.currentTimeMillis();
        return end - start + "ms\r\n";
    }

調(diào)用腳本

#!/usr/bin/env bash

for (( VAR = 0; VAR < 20 ; ++VAR )); do
    curl http://localhost:8080/testYDListSave >> result.txt &
done

調(diào)用結(jié)果

136332ms
136564ms
136623ms
136627ms
136653ms
136742ms
136788ms
136801ms
136801ms
136814ms
136889ms
136893ms
136941ms
136958ms
136966ms
137028ms
137048ms
137106ms
137156ms
137212ms

這里因?yàn)槭遣l(fā)插入的，所以插入20w數(shù)據(jù)在137秒 ， 兩分多鐘。

然后是查詢數(shù)據(jù),我需要查詢一個小時內(nèi)的從北京發(fā)貨的數(shù)據(jù)。

EXPLAIN SELECT * FROM test_yd_table WHERE create_time >= '2019-12-05 12:00:00' and create_time < '2019-12-05 13:00:00' AND send_province_code = '110000'

1   SIMPLE  test_yd_table   p2019120513_p2019120513sp2  index       PRIMARY 51      67994   1.11    Using where; Using index
截屏2019-12-0512.13.53.png

只使用了一個分區(qū)，節(jié)省了查詢時間。
不過這種方式太過理想。
所以在執(zhí)行Dao方法時 ，多存了一份 110000的數(shù)據(jù)

    public void save() {
        testYDListDao.save(CreateMailNo.getMailNo(), CreateProvince.getProvince(), CreateProvince.getProvince());
        testYDListDao.save110000(CreateMailNo.getMailNo(), CreateProvince.getProvince(), CreateProvince.getProvince());
    }

再執(zhí)行一次腳本(request_api.sh)。


261260ms
261582ms
261609ms
261619ms
261996ms
262123ms
262247ms
262254ms
262332ms
262376ms
262416ms
262493ms
262553ms
262588ms
262594ms
262618ms
262620ms
262660ms
262661ms
262697ms

同樣的 ，此次也是并發(fā)插入，時間差不多在4分多鐘。

查看數(shù)據(jù)庫底層文件，發(fā)現(xiàn)test_yd_table#P#p2019120513#SP#p2019120513sp2.ibd
比其他兩個同時間分區(qū)的數(shù)據(jù)量多了一倍多。

26214400    test_yd_table#P#p2019120513#SP#p2019120513sp0.ibd
26214400    test_yd_table#P#p2019120513#SP#p2019120513sp1.ibd
58720256    test_yd_table#P#p2019120513#SP#p2019120513sp2.ibd

比較幾次常規(guī)查詢

1.不使用where

不使用where屬于全表查詢了，所以explain查找了全部的分區(qū)。


不實(shí)用where-explain.png

查看數(shù)據(jù)，發(fā)現(xiàn)code列的都是mod%3==0的數(shù)值，time列不是按順序來展示，
所以是先返回了第一個分區(qū)(test_yd_table#P#p2019120513#SP#p2019120513sp0.ibd)的數(shù)據(jù)。

不使用where-select.png

2.使用where create_time

使用where+ create_time來查詢，在explain里是查找了小時段的分區(qū)。


使用where+時間-explain.png

查看數(shù)據(jù)，發(fā)現(xiàn)code列的都是mod%3==0的數(shù)值，time列不是按順序來展示，
所以是先返回了第一個分區(qū)(test_yd_table#P#p2019120513#SP#p2019120513sp0.ibd)的數(shù)據(jù)。

使用where+時間-select.png

3.使用where code

使用where+ code來查詢，在explain里是查找了每個小時段sp2分區(qū)的數(shù)據(jù)，
110000%3 = 2
目前 只插入了 一個小時的數(shù)據(jù)。

使用where+code-explain.png

查看數(shù)據(jù),code都是110000 ，時間是順序展示。


使用where+code-select.png
4.使用where create_time code

使用where+ create_time code來查詢，在explain里是查找了1個小時段sp2分區(qū)的數(shù)據(jù)，
只查了分區(qū)test_yd_table#P#p2019120513#SP#p2019120513sp2.ibd

使用where+時間+code-explain.png

查看數(shù)據(jù),code都是110000 ，時間是順序展示。


使用where+時間+code-select.png

這種分區(qū)方式要視實(shí)際情況而定，
目前能滿足按時間段查省份數(shù)據(jù)，在業(yè)務(wù)上，大批量的流水?dāng)?shù)據(jù)需要加時間。

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

友情鏈接更多精彩內(nèi)容