为什么数据库分了表后性能会直线下降？

Question

为什么数据库分了表后性能会直线下降？

发布于
2020-06-17

新手上路，请多包涵

原先有按月分的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

mysql sql 数据库

阅读 1.7k

1 个回答

查询最好带上分区列，才能利用起来分区表的优势，否则这个性能反而可能不如从前。

撰写回答

你尚未登录，登录后可以

和开发者交流问题的细节
关注并接收问题和回答的更新提醒
参与内容的编辑和改进，让解决方法与时俱进

推荐问题

相似问题

找不到问题？创建新问题

为什么数据库分了表后性能会直线下降？

你尚未登录，登录后可以

Java 开发 URL 匹配问题？

为何无法连接阿里云轻量应用服务器上 docker 中的 mysql ？

mysql 死锁问题？

请问是否有什么方案实现不同用户之间本地数据库的同步呢？

MyBatis Plus 如何对敏感字段加解密（使用哪种加密方式）？

MySQL可重复读隔离级别下，为什么普通索引范围查询临键锁不退化为间隙锁？

lowdb可以在前端进行存储吗？

为什么数据库分了表后性能会直线下降？

你尚未登录，登录后可以

Java 开发 URL 匹配问题？

为何无法连接 阿里云轻量应用服务器上 docker 中的 mysql ？

mysql 死锁问题？

请问是否有什么方案实现不同用户之间本地数据库的同步呢？

MyBatis Plus 如何对敏感字段加解密（使用哪种加密方式）？

MySQL可重复读隔离级别下，为什么普通索引范围查询临键锁不退化为间隙锁？

lowdb可以在前端进行存储吗？

为何无法连接阿里云轻量应用服务器上 docker 中的 mysql ？