【TiDB 版本】:
3.0.11
前置条件
1 表分区数量
PARTITION BY RANGE(TO_DAYS(seller_date)) (
PARTITION p200001 VALUES LESS THAN ( TO_DAYS('2000-01-01 00:00:00') ),
PARTITION p201701 VALUES LESS THAN ( TO_DAYS('2017-02-01 00:00:00') ),
PARTITION p201702 VALUES LESS THAN ( TO_DAYS('2017-03-01 00:00:00') ),
PARTITION p201703 VALUES LESS THAN ( TO_DAYS('2017-04-01 00:00:00') ),
PARTITION p201704 VALUES LESS THAN ( TO_DAYS('2017-05-01 00:00:00') ),
PARTITION p201705 VALUES LESS THAN ( TO_DAYS('2017-06-01 00:00:00') ),
PARTITION p201706 VALUES LESS THAN ( TO_DAYS('2017-07-01 00:00:00') ),
PARTITION p201707 VALUES LESS THAN ( TO_DAYS('2017-08-01 00:00:00') ),
PARTITION p201708 VALUES LESS THAN ( TO_DAYS('2017-09-01 00:00:00') ),
PARTITION p201709 VALUES LESS THAN ( TO_DAYS('2017-10-01 00:00:00') ),
PARTITION p201710 VALUES LESS THAN ( TO_DAYS('2017-11-01 00:00:00') ),
PARTITION p201711 VALUES LESS THAN ( TO_DAYS('2017-12-01 00:00:00') ),
PARTITION p201712 VALUES LESS THAN ( TO_DAYS('2018-01-01 00:00:00') ),
PARTITION p201801 VALUES LESS THAN ( TO_DAYS('2018-02-01 00:00:00') ),
PARTITION p201802 VALUES LESS THAN ( TO_DAYS('2018-03-01 00:00:00') ),
PARTITION p201803 VALUES LESS THAN ( TO_DAYS('2018-04-01 00:00:00') ),
PARTITION p201804 VALUES LESS THAN ( TO_DAYS('2018-05-01 00:00:00') ),
PARTITION p201805 VALUES LESS THAN ( TO_DAYS('2018-06-01 00:00:00') ),
PARTITION p201806 VALUES LESS THAN ( TO_DAYS('2018-07-01 00:00:00') ),
PARTITION p201807 VALUES LESS THAN ( TO_DAYS('2018-08-01 00:00:00') ),
PARTITION p201808 VALUES LESS THAN ( TO_DAYS('2018-09-01 00:00:00') ),
PARTITION p201809 VALUES LESS THAN ( TO_DAYS('2018-10-01 00:00:00') ),
PARTITION p201810 VALUES LESS THAN ( TO_DAYS('2018-11-01 00:00:00') ),
PARTITION p201811 VALUES LESS THAN ( TO_DAYS('2018-12-01 00:00:00') ),
PARTITION p201812 VALUES LESS THAN ( TO_DAYS('2019-01-01 00:00:00') ),
PARTITION p201901 VALUES LESS THAN ( TO_DAYS('2019-02-01 00:00:00') ),
PARTITION p201902 VALUES LESS THAN ( TO_DAYS('2019-03-01 00:00:00') ),
PARTITION p201903 VALUES LESS THAN ( TO_DAYS('2019-04-01 00:00:00') ),
PARTITION p201904 VALUES LESS THAN ( TO_DAYS('2019-05-01 00:00:00') ),
PARTITION p201905 VALUES LESS THAN ( TO_DAYS('2019-06-01 00:00:00') ),
PARTITION p201906 VALUES LESS THAN ( TO_DAYS('2019-07-01 00:00:00') ),
PARTITION p201907 VALUES LESS THAN ( TO_DAYS('2019-08-01 00:00:00') ),
PARTITION p201908 VALUES LESS THAN ( TO_DAYS('2019-09-01 00:00:00') ),
PARTITION p201909 VALUES LESS THAN ( TO_DAYS('2019-10-01 00:00:00') ),
PARTITION p201910 VALUES LESS THAN ( TO_DAYS('2019-11-01 00:00:00') ),
PARTITION p201911 VALUES LESS THAN ( TO_DAYS('2019-12-01 00:00:00') ),
PARTITION p201912 VALUES LESS THAN ( TO_DAYS('2020-01-01 00:00:00') ),
PARTITION p202001 VALUES LESS THAN ( TO_DAYS('2020-02-01 00:00:00') ),
PARTITION p202002 VALUES LESS THAN ( TO_DAYS('2020-03-01 00:00:00') ),
PARTITION p202003 VALUES LESS THAN ( TO_DAYS('2020-04-01 00:00:00') ),
PARTITION p202004 VALUES LESS THAN ( TO_DAYS('2020-05-01 00:00:00') ),
PARTITION p202005 VALUES LESS THAN ( TO_DAYS('2020-06-01 00:00:00') ),
PARTITION p202006 VALUES LESS THAN ( TO_DAYS('2020-07-01 00:00:00') ),
PARTITION p202007 VALUES LESS THAN ( TO_DAYS('2020-08-01 00:00:00') ),
PARTITION p202008 VALUES LESS THAN ( TO_DAYS('2020-09-01 00:00:00') ),
PARTITION p202009 VALUES LESS THAN ( TO_DAYS('2020-10-01 00:00:00') ),
PARTITION p202010 VALUES LESS THAN ( TO_DAYS('2020-11-01 00:00:00') ),
PARTITION p202011 VALUES LESS THAN ( TO_DAYS('2020-12-01 00:00:00') ),
PARTITION p202012 VALUES LESS THAN ( TO_DAYS('2021-01-01 00:00:00') ),
PARTITION p202101 VALUES LESS THAN ( TO_DAYS('2021-02-01 00:00:00') ),
PARTITION p202102 VALUES LESS THAN ( TO_DAYS('2021-03-01 00:00:00') ),
PARTITION p202103 VALUES LESS THAN ( TO_DAYS('2021-04-01 00:00:00') ),
PARTITION p202104 VALUES LESS THAN ( TO_DAYS('2021-05-01 00:00:00') ),
PARTITION p202105 VALUES LESS THAN ( TO_DAYS('2021-06-01 00:00:00') ),
PARTITION p202106 VALUES LESS THAN ( TO_DAYS('2021-07-01 00:00:00') ),
PARTITION p202107 VALUES LESS THAN ( TO_DAYS('2021-08-01 00:00:00') ),
PARTITION p202108 VALUES LESS THAN ( TO_DAYS('2021-09-01 00:00:00') ),
PARTITION p202109 VALUES LESS THAN ( TO_DAYS('2021-10-01 00:00:00') ),
PARTITION p202110 VALUES LESS THAN ( TO_DAYS('2021-11-01 00:00:00') ),
PARTITION p202111 VALUES LESS THAN ( TO_DAYS('2021-12-01 00:00:00') ),
PARTITION p202112 VALUES LESS THAN ( TO_DAYS('2022-01-01 00:00:00') ),
PARTITION p202201 VALUES LESS THAN ( TO_DAYS('2022-02-01 00:00:00') ),
PARTITION p202202 VALUES LESS THAN ( TO_DAYS('2022-03-01 00:00:00') ),
PARTITION p202203 VALUES LESS THAN ( TO_DAYS('2022-04-01 00:00:00') ),
PARTITION p202204 VALUES LESS THAN ( TO_DAYS('2022-05-01 00:00:00') ),
PARTITION p202205 VALUES LESS THAN ( TO_DAYS('2022-06-01 00:00:00') ),
PARTITION p202206 VALUES LESS THAN ( TO_DAYS('2022-07-01 00:00:00') ),
PARTITION p202207 VALUES LESS THAN ( TO_DAYS('2022-08-01 00:00:00') ),
PARTITION p202208 VALUES LESS THAN ( TO_DAYS('2022-09-01 00:00:00') ),
PARTITION p202209 VALUES LESS THAN ( TO_DAYS('2022-10-01 00:00:00') ),
PARTITION p202210 VALUES LESS THAN ( TO_DAYS('2022-11-01 00:00:00') ),
PARTITION p202211 VALUES LESS THAN ( TO_DAYS('2022-12-01 00:00:00') ),
PARTITION p202212 VALUES LESS THAN ( TO_DAYS('2023-01-01 00:00:00') ),
PARTITION p202301 VALUES LESS THAN ( TO_DAYS('2023-02-01 00:00:00') ),
PARTITION p202302 VALUES LESS THAN ( TO_DAYS('2023-03-01 00:00:00') ),
PARTITION p202303 VALUES LESS THAN ( TO_DAYS('2023-04-01 00:00:00') ),
PARTITION p202304 VALUES LESS THAN ( TO_DAYS('2023-05-01 00:00:00') ),
PARTITION p202305 VALUES LESS THAN ( TO_DAYS('2023-06-01 00:00:00') ),
PARTITION p202306 VALUES LESS THAN ( TO_DAYS('2023-07-01 00:00:00') ),
PARTITION p202307 VALUES LESS THAN ( TO_DAYS('2023-08-01 00:00:00') ),
PARTITION p202308 VALUES LESS THAN ( TO_DAYS('2023-09-01 00:00:00') ),
PARTITION p202309 VALUES LESS THAN ( TO_DAYS('2023-10-01 00:00:00') ),
PARTITION p202310 VALUES LESS THAN ( TO_DAYS('2023-11-01 00:00:00') ),
PARTITION p202311 VALUES LESS THAN ( TO_DAYS('2023-12-01 00:00:00') ),
PARTITION p202312 VALUES LESS THAN ( TO_DAYS('2024-01-01 00:00:00') ),
PARTITION p202401 VALUES LESS THAN ( TO_DAYS('2024-02-01 00:00:00') ),
PARTITION p202402 VALUES LESS THAN ( TO_DAYS('2024-03-01 00:00:00') ),
PARTITION p202403 VALUES LESS THAN ( TO_DAYS('2024-04-01 00:00:00') ),
PARTITION p202404 VALUES LESS THAN ( TO_DAYS('2024-05-01 00:00:00') ),
PARTITION p202405 VALUES LESS THAN ( TO_DAYS('2024-06-01 00:00:00') ),
PARTITION p202406 VALUES LESS THAN ( TO_DAYS('2024-07-01 00:00:00') ),
PARTITION p202407 VALUES LESS THAN ( TO_DAYS('2024-08-01 00:00:00') ),
PARTITION p202408 VALUES LESS THAN ( TO_DAYS('2024-09-01 00:00:00') ),
PARTITION p202409 VALUES LESS THAN ( TO_DAYS('2024-10-01 00:00:00') ),
PARTITION p202410 VALUES LESS THAN ( TO_DAYS('2024-11-01 00:00:00') ),
PARTITION p202411 VALUES LESS THAN ( TO_DAYS('2024-12-01 00:00:00') ),
PARTITION p202412 VALUES LESS THAN ( TO_DAYS('2025-01-01 00:00:00') ),
PARTITION p202501 VALUES LESS THAN ( TO_DAYS('2025-02-01 00:00:00') ),
PARTITION p202502 VALUES LESS THAN ( TO_DAYS('2025-03-01 00:00:00') ),
PARTITION p202503 VALUES LESS THAN ( TO_DAYS('2025-04-01 00:00:00') ),
PARTITION p202504 VALUES LESS THAN ( TO_DAYS('2025-05-01 00:00:00') ),
PARTITION p202505 VALUES LESS THAN ( TO_DAYS('2025-06-01 00:00:00') ),
PARTITION p202506 VALUES LESS THAN ( TO_DAYS('2025-07-01 00:00:00') ),
PARTITION p202507 VALUES LESS THAN ( TO_DAYS('2025-08-01 00:00:00') ),
PARTITION p202508 VALUES LESS THAN ( TO_DAYS('2025-09-01 00:00:00') ),
PARTITION p202509 VALUES LESS THAN ( TO_DAYS('2025-10-01 00:00:00') ),
PARTITION p202510 VALUES LESS THAN ( TO_DAYS('2025-11-01 00:00:00') ),
PARTITION p202511 VALUES LESS THAN ( TO_DAYS('2025-12-01 00:00:00') ),
PARTITION p202512 VALUES LESS THAN ( TO_DAYS('2026-01-01 00:00:00') ),
PARTITION p202601 VALUES LESS THAN ( TO_DAYS('2026-02-01 00:00:00') ),
PARTITION p202602 VALUES LESS THAN ( TO_DAYS('2026-03-01 00:00:00') ),
PARTITION p202603 VALUES LESS THAN ( TO_DAYS('2026-04-01 00:00:00') ),
PARTITION p202604 VALUES LESS THAN ( TO_DAYS('2026-05-01 00:00:00') ),
PARTITION p202605 VALUES LESS THAN ( TO_DAYS('2026-06-01 00:00:00') ),
PARTITION p202606 VALUES LESS THAN ( TO_DAYS('2026-07-01 00:00:00') ),
PARTITION p202607 VALUES LESS THAN ( TO_DAYS('2026-08-01 00:00:00') ),
PARTITION p202608 VALUES LESS THAN ( TO_DAYS('2026-09-01 00:00:00') ),
PARTITION p202609 VALUES LESS THAN ( TO_DAYS('2026-10-01 00:00:00') ),
PARTITION p202610 VALUES LESS THAN ( TO_DAYS('2026-11-01 00:00:00') ),
PARTITION p202611 VALUES LESS THAN ( TO_DAYS('2026-12-01 00:00:00') ),
PARTITION p202612 VALUES LESS THAN ( TO_DAYS('2027-01-01 00:00:00') ),
PARTITION p202701 VALUES LESS THAN ( TO_DAYS('2027-02-01 00:00:00') ),
PARTITION p202702 VALUES LESS THAN ( TO_DAYS('2027-03-01 00:00:00') ),
PARTITION p202703 VALUES LESS THAN ( TO_DAYS('2027-04-01 00:00:00') ),
PARTITION p202704 VALUES LESS THAN ( TO_DAYS('2027-05-01 00:00:00') ),
PARTITION p202705 VALUES LESS THAN ( TO_DAYS('2027-06-01 00:00:00') ),
PARTITION p202706 VALUES LESS THAN ( TO_DAYS('2027-07-01 00:00:00') ),
PARTITION p202707 VALUES LESS THAN ( TO_DAYS('2027-08-01 00:00:00') ),
PARTITION p202708 VALUES LESS THAN ( TO_DAYS('2027-09-01 00:00:00') ),
PARTITION p202709 VALUES LESS THAN ( TO_DAYS('2027-10-01 00:00:00') ),
PARTITION p202710 VALUES LESS THAN ( TO_DAYS('2027-11-01 00:00:00') ),
PARTITION p202711 VALUES LESS THAN ( TO_DAYS('2027-12-01 00:00:00') ),
2 机器分布图
3 region 监控
4 硬盘实际空间占用
【问题描述】:
1 数据库只有单一的写入操作,同时向两张表中写入数据,每张表写入数据量5000万~7000万,每次写入5000条数据, 表分区后,按照实际业务场景,每个分区的数据量在 100万~200万 之间
2 数据量与之前相同,但是分区后速度下降的很明显,不知道是不是表分区的原因造成的
3 我改如何改善目前面临的问题