原先有按月分的 12 张表,每张表里大概有 2000w 左右的数据
...
CREATE TABLE `parcel_with_no_partition_m05` (
...
)
CREATE TABLE `parcel_with_no_partition_m06` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '数据主键,毫无意义',
`equip_id` int(11) DEFAULT NULL,
`scan_id` int(11) DEFAULT NULL,
`create_datetime` datetime DEFAULT NULL,
`minute_of_hour` int(11) DEFAULT NULL,
`hour_of_day` int(11) DEFAULT NULL,
`day_of_month` int(11) DEFAULT NULL,
`week_of_year` int(11) DEFAULT NULL,
`month_of_year` int(11) DEFAULT NULL,
`parcel_length` int(11) DEFAULT NULL
PRIMARY KEY (`id`)
) ENGINE=innodb AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `parcel_with_no_partition_m07` (
...
)
...
现在想尝试用 mysql8 的 partition 来进行分表,集中存储了 12 个月的一共 2 亿多条数据,每个分区 2000w 条左右的数据,与之前的表数据一致
CREATE TABLE `parcel_with_partition` (
`equip_id` int(11) DEFAULT NULL,
`scan_id` int(11) DEFAULT NULL,
`create_datetime` datetime DEFAULT NULL,
`minute_of_hour` int(11) DEFAULT NULL,
`hour_of_day` int(11) DEFAULT NULL,
`day_of_month` int(11) DEFAULT NULL,
`week_of_year` int(11) DEFAULT NULL,
`month_of_year` int(11) DEFAULT NULL,
`parcel_length` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ENGINE=InnoDB PARTITION BY RANGE(`month_of_year`) (
PARTITION `JAN` VALUES LESS THAN (2),
PARTITION `FEB` VALUES LESS THAN (3),
PARTITION `MAR` VALUES LESS THAN (4),
PARTITION `APR` VALUES LESS THAN (5),
PARTITION `MAY` VALUES LESS THAN (6),
PARTITION `JUN` VALUES LESS THAN (7),
PARTITION `JUL` VALUES LESS THAN (8),
PARTITION `AUG` VALUES LESS THAN (9),
PARTITION `SEP` VALUES LESS THAN (10),
PARTITION `OCT` VALUES LESS THAN (11),
PARTITION `NOV` VALUES LESS THAN (12),
PARTITION `DEC` VALUES LESS THAN (13)
);
我尝试执行了一条业务 sql,发现效率下降了一倍
#从 mysql 自带的分区中取数据
select sql_no_cache count(*) as num,`day_of_month` as day,`hour_of_day` as hour,`scan_id` as scanId from `parcel_with_partition` partition(`JUN`) where `equip_id` = 3 and `create_datetime` >'2020-06-01' and `create_datetime` <= '2020-06-29' group by `scan_id`,`day_of_month`,`hour_of_day`
2018 rows in set (59.56 sec)
#从原始分表中取数据
select sql_no_cache count(*) as num,`day_of_month` as day,`hour_of_day` as hour,`scan_id` as scanId from `parcel_with_no_partition_m06` where `equip_id` = 3 and `create_datetime` >'2020-06-01' and `sort_datetime` <= '2020-06-29' group by `scan_id`,`day_of_month`,`hour_of_day`
2018 rows in set, 1 warning (29.13 sec)
又进一步用 explain 和 performance schema 分别观察了两种 sql
mysql 自带分区查询详情
explain:
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+----------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+----------+----------+------------------------------+
| 1 | SIMPLE | parcel_with_partition | JUN | ALL | NULL | NULL | NULL | NULL | 20481855 | 1.11 | Using where; Using temporary |
+----+-------------+-----------------------+------------+------+---------------+------+---------+------+----------+----------+------------------------------+
table i/o:
select * from table_io_waits_summary_by_table where count_star>0 \G;
*************************** 1. row ***************************
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test
OBJECT_NAME: parcel_with_partition
COUNT_STAR: 22123040
SUM_TIMER_WAIT: 55709022384855
MIN_TIMER_WAIT: 55709022384855
AVG_TIMER_WAIT: 2517879
MAX_TIMER_WAIT: 55709022384855
COUNT_READ: 22123040
SUM_TIMER_READ: 55709022384855
MIN_TIMER_READ: 55709022384855
AVG_TIMER_READ: 2517879
MAX_TIMER_READ: 55709022384855
COUNT_WRITE: 0
SUM_TIMER_WRITE: 0
MIN_TIMER_WRITE: 0
AVG_TIMER_WRITE: 0
MAX_TIMER_WRITE: 0
COUNT_FETCH: 22123040
SUM_TIMER_FETCH: 55709022384855
MIN_TIMER_FETCH: 55709022384855
AVG_TIMER_FETCH: 2517879
MAX_TIMER_FETCH: 55709022384855
COUNT_INSERT: 0
SUM_TIMER_INSERT: 0
MIN_TIMER_INSERT: 0
AVG_TIMER_INSERT: 0
MAX_TIMER_INSERT: 0
COUNT_UPDATE: 0
SUM_TIMER_UPDATE: 0
MIN_TIMER_UPDATE: 0
AVG_TIMER_UPDATE: 0
MAX_TIMER_UPDATE: 0
COUNT_DELETE: 0
SUM_TIMER_DELETE: 0
MIN_TIMER_DELETE: 0
AVG_TIMER_DELETE: 0
MAX_TIMER_DELETE: 0
file i/o:
FILE_NAME: /home/wedo/mysql/data/mysql/test/parcel_with_partition#p#jun.ibd
EVENT_NAME: wait/io/file/innodb/innodb_data_file
OBJECT_INSTANCE_BEGIN: 140081009323904
COUNT_STAR: 422002
SUM_TIMER_WAIT: 3965120084812
MIN_TIMER_WAIT: 5739112
AVG_TIMER_WAIT: 9395831
MAX_TIMER_WAIT: 498159493
COUNT_READ: 422002
SUM_TIMER_READ: 3965120084812
MIN_TIMER_READ: 5739112
AVG_TIMER_READ: 9395831
MAX_TIMER_READ: 498159493
SUM_NUMBER_OF_BYTES_READ: 6914080768
COUNT_WRITE: 0
SUM_TIMER_WRITE: 0
MIN_TIMER_WRITE: 0
AVG_TIMER_WRITE: 0
MAX_TIMER_WRITE: 0
SUM_NUMBER_OF_BYTES_WRITE: 0
COUNT_MISC: 0
SUM_TIMER_MISC: 0
MIN_TIMER_MISC: 0
AVG_TIMER_MISC: 0
MAX_TIMER_MISC: 0
原始分表查询详情
explain:
+----+-------------+-----------------------------+------------+------+---------------+------+---------+------+----------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------------------------+------------+------+---------------+------+---------+------+----------+----------+------------------------------+
| 1 | SIMPLE | parcel_with_no_partition_m06 | NULL | ALL | NULL | NULL | NULL | NULL | 22123040 | 1.11 | Using where; Using temporary |
+----+-------------+------------------------------+------------+------+---------------+------+---------+------+----------+----------+------------------------------+
table i/o:
OBJECT_TYPE: TABLE
OBJECT_SCHEMA: test
OBJECT_NAME: parcel_with_no_partition_m06
COUNT_STAR: 22123040
SUM_TIMER_WAIT: 23182194259854
MIN_TIMER_WAIT: 23182194259854
AVG_TIMER_WAIT: 1047813
MAX_TIMER_WAIT: 23182194259854
COUNT_READ: 22123040
SUM_TIMER_READ: 23182194259854
MIN_TIMER_READ: 23182194259854
AVG_TIMER_READ: 1047813
MAX_TIMER_READ: 23182194259854
COUNT_WRITE: 0
SUM_TIMER_WRITE: 0
MIN_TIMER_WRITE: 0
AVG_TIMER_WRITE: 0
MAX_TIMER_WRITE: 0
COUNT_FETCH: 22123040
SUM_TIMER_FETCH: 23182194259854
MIN_TIMER_FETCH: 23182194259854
AVG_TIMER_FETCH: 1047813
MAX_TIMER_FETCH: 23182194259854
COUNT_INSERT: 0
SUM_TIMER_INSERT: 0
MIN_TIMER_INSERT: 0
AVG_TIMER_INSERT: 0
MAX_TIMER_INSERT: 0
COUNT_UPDATE: 0
SUM_TIMER_UPDATE: 0
MIN_TIMER_UPDATE: 0
AVG_TIMER_UPDATE: 0
MAX_TIMER_UPDATE: 0
COUNT_DELETE: 0
SUM_TIMER_DELETE: 0
MIN_TIMER_DELETE: 0
AVG_TIMER_DELETE: 0
MAX_TIMER_DELETE: 0
file i/o:
FILE_NAME: /home/wedo/mysql/data/mysql/test/parcel_with_no_partition_m06.ibd
EVENT_NAME: wait/io/file/innodb/innodb_data_file
OBJECT_INSTANCE_BEGIN: 140081009372480
COUNT_STAR: 424687
SUM_TIMER_WAIT: 4045883911263
MIN_TIMER_WAIT: 5301220
AVG_TIMER_WAIT: 9526557
MAX_TIMER_WAIT: 861143891
COUNT_READ: 424687
SUM_TIMER_READ: 4045883911263
MIN_TIMER_READ: 5301220
AVG_TIMER_READ: 9526557
MAX_TIMER_READ: 861143891
SUM_NUMBER_OF_BYTES_READ: 6958071808
COUNT_WRITE: 0
SUM_TIMER_WRITE: 0
MIN_TIMER_WRITE: 0
AVG_TIMER_WRITE: 0
MAX_TIMER_WRITE: 0
SUM_NUMBER_OF_BYTES_WRITE: 0
COUNT_MISC: 0
SUM_TIMER_MISC: 0
MIN_TIMER_MISC: 0
AVG_TIMER_MISC: 0
MAX_TIMER_MISC: 0
在 explain 和 file i/o 中都没有看出来异常情况,但是在 table i/o 时 内部分区 使用的时间远高于 原始的分表
这是不是说明多浪费的时间的罪魁祸首是逻辑 i/o 部分?为什么分表后会出现这样的结果呢?希望大神们多多指教
补充一下环境: 系统:centos7 存储引擎:mysql8 服务器配置:12 核 2.5Ghz 64G 内存 硬盘:机械硬盘 在执行查询 sql 时最大的内存占用 3G
这是一个专为移动设备优化的页面(即为了让你能够在 Google 搜索结果里秒开这个页面),如果你希望参与 V2EX 社区的讨论,你可以继续到 V2EX 上打开本讨论主题的完整版本。
V2EX 是创意工作者们的社区,是一个分享自己正在做的有趣事物、交流想法,可以遇见新朋友甚至新机会的地方。
V2EX is a community of developers, designers and creative people.