我有一个表table1，它的主键是：PRIMARY(year, month, id).
根据我对这个主键的理解，二叉树中的数据将按年、月、id彼此相邻地存储。

(2021 12, 1)
(2022, 12, 1)
(2022, 12, 2)
(2023, 1, 1)

CREATE TABLE `table1` (
  `id` int AUTO_INCREMENT NOT NULL,
  `entity_id` varchar(36) NOT NULL,
  `entity_type` varchar(36) NOT NULL,
  `score` decimal(4,3) NOT NULL,
  `raw` json DEFAULT NULL,
  `month` int NOT NULL,
  `year` int NOT NULL,
  `date` DATE NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`year`, `month`, `id`),
  KEY (`id`),
  KEY `table1_indx` (`year`, `month`,`score`,`entity_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

如果我有一个查询，重点是通过日期（月，年）搜索，这将是有效的，因为数据是聚集和组织在一起。

EXPLAIN
SELECT
  table1.entity_id AS entity_id,
  table1.entity_type,
  table1.score
FROM table1
WHERE table1.month = 12
  AND table1.year = 2022
  AND table1.score > 0
  AND table1.entity_type IN ('type1', 'type2', 'type3', 'type4');

• • 如果我关于上述内容的假设是正确的，并且数据的组织也是正确的，那么如果表按年分区并按月再分区，会发生什么不同？**

CREATE TABLE `table1` (
  `id` int AUTO_INCREMENT NOT NULL,
  `entity_id` varchar(36) NOT NULL,
  `entity_type` varchar(36) NOT NULL,
  `score` decimal(4,3) NOT NULL,
  `raw` json DEFAULT NULL,
  `month` int NOT NULL,
  `year` int NOT NULL,
  `date` DATE NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `deleted_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`year`, `month`, `id`),
  KEY (`id`),
  KEY `table1_indx` (`year`, `month`,`score`,`entity_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
PARTITION BY RANGE (`year`)
SUBPARTITION BY HASH (`month`)
(PARTITION p2021 VALUES LESS THAN (2022)
 (SUBPARTITION dec_2021 ENGINE = InnoDB,
  SUBPARTITION jan_2021 ENGINE = InnoDB,
  SUBPARTITION feb_2021 ENGINE = InnoDB,
  SUBPARTITION mar_2021 ENGINE = InnoDB,
  SUBPARTITION apr_2021 ENGINE = InnoDB,
  SUBPARTITION may_2021 ENGINE = InnoDB,
  SUBPARTITION jun_2021 ENGINE = InnoDB,
  SUBPARTITION jul_2021 ENGINE = InnoDB,
  SUBPARTITION aug_2021 ENGINE = InnoDB,
  SUBPARTITION sep_2021 ENGINE = InnoDB,
  SUBPARTITION oct_2021 ENGINE = InnoDB,
  SUBPARTITION nov_2021 ENGINE = InnoDB),
 PARTITION p2022 VALUES LESS THAN (2023)
 (SUBPARTITION dec_2022 ENGINE = InnoDB,
  SUBPARTITION jan_2022 ENGINE = InnoDB,
  SUBPARTITION feb_2022 ENGINE = InnoDB,
  SUBPARTITION mar_2022 ENGINE = InnoDB,
  SUBPARTITION apr_2022 ENGINE = InnoDB,
  SUBPARTITION may_2022 ENGINE = InnoDB,
  SUBPARTITION jun_2022 ENGINE = InnoDB,
  SUBPARTITION jul_2022 ENGINE = InnoDB,
  SUBPARTITION aug_2022 ENGINE = InnoDB,
  SUBPARTITION sep_2022 ENGINE = InnoDB,
  SUBPARTITION oct_2022 ENGINE = InnoDB,
  SUBPARTITION nov_2022 ENGINE = InnoDB),
 PARTITION p2023 VALUES LESS THAN (2024)
 (SUBPARTITION dec_2023 ENGINE = InnoDB,
  SUBPARTITION jan_2023 ENGINE = InnoDB,
  SUBPARTITION feb_2023 ENGINE = InnoDB,
  SUBPARTITION mar_2023 ENGINE = InnoDB,
  SUBPARTITION apr_2023 ENGINE = InnoDB,
  SUBPARTITION may_2023 ENGINE = InnoDB,
  SUBPARTITION jun_2023 ENGINE = InnoDB,
  SUBPARTITION jul_2023 ENGINE = InnoDB,
  SUBPARTITION aug_2023 ENGINE = InnoDB,
  SUBPARTITION sep_2023 ENGINE = InnoDB,
  SUBPARTITION oct_2023 ENGINE = InnoDB,
  SUBPARTITION nov_2023 ENGINE = InnoDB),
 PARTITION pmax VALUES LESS THAN MAXVALUE
 (SUBPARTITION dec_max ENGINE = InnoDB,
  SUBPARTITION jan_max ENGINE = InnoDB,
  SUBPARTITION feb_max ENGINE = InnoDB,
  SUBPARTITION mar_max ENGINE = InnoDB,
  SUBPARTITION apr_max ENGINE = InnoDB,
  SUBPARTITION may_max ENGINE = InnoDB,
  SUBPARTITION jun_max ENGINE = InnoDB,
  SUBPARTITION jul_max ENGINE = InnoDB,
  SUBPARTITION aug_max ENGINE = InnoDB,
  SUBPARTITION sep_max ENGINE = InnoDB,
  SUBPARTITION oct_max ENGINE = InnoDB,
  SUBPARTITION nov_max ENGINE = InnoDB))

最终，我试图了解primary(year,month,id)以及对数据进行分区是否"值得"。