MariaDB超1亿数据用户表性能优化：分区与索引方案抉择

我有一张存储用户数据的表（client_inss_dados），目前已包含超1亿条数据。由于数据量庞大，涉及日期范围的查询速度极慢。该表中BIRTH字段被定义为varchar(255)（原本预期是date类型），我们频繁执行类似如下的查询：

SELECT * FROM client_inss_dados WHERE YEAR(CURDATE()) - YEAR(BIRTH) > 15

为提升此类查询性能，我考虑按年份将表划分为93个分区，拟定了两种方案：

方案一：范围分区 #

ALTER TABLE client_inss_dados
PARTITION BY range(year(BIRTH))(
PARTITION p_year_1930 VALUES LESS THAN (1930),
PARTITION p_year_1931 VALUES LESS THAN (1931),
...
PARTITION p_year_2023 VALUES LESS THAN (2023),
)

方案二：哈希分区 #

ALTER TABLE client_inss_dados
PARTITION BY hash(year(BIRTH))(
partitions 93
)

我的疑问：

1. 在此场景下分区是否为合理策略？
2. 是否存在更优的索引方案？
3. 针对上述WHERE子句的查询，范围分区与哈希分区在性能上是否存在差异？

补充信息 #

表结构

client_inss_dados | CREATE TABLE `client_inss_dados` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `SSN` varchar(50) DEFAULT NULL,
  `NAME` varchar(255) DEFAULT NULL,
  `SEX` varchar(255) DEFAULT NULL,
  `BIRTH` varchar(255) DEFAULT NULL,
  `WAGE` varchar(255) DEFAULT NULL,
  `COD_BANK` varchar(255) DEFAULT NULL,
  `ADDRESS` varchar(255) DEFAULT NULL,
  `CITY` varchar(255) DEFAULT NULL,
  `PHONE_01` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT current_timestamp(),
  `updated_at` timestamp NULL DEFAULT NULL ON UPDATE current_timestamp(),
  PRIMARY KEY (`ID`) USING BTREE,
  KEY `idx_SSN` (`SSN`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci

数据库版本

10.5.19-MariaDB

1. 分区是否为合理策略？ #

分区是可行的优化方向，但必须先修复BIRTH字段的类型问题。当前BIRTH是varchar(255)，直接对其调用YEAR()函数会触发全表扫描——数据库无法利用索引或分区裁剪，函数调用破坏了字段的有序性。只有将BIRTH转换成DATE或DATETIME类型，分区才能发挥实际作用。

如果不修复字段类型，即使分区，查询依然会扫描所有分区，性能提升有限甚至完全没有。

2. 更优的索引方案 #

优先修复字段类型+创建普通索引 #

第一步：将BIRTH字段转换为DATE类型，注意提前处理格式不规范的字符串数据，确保转换后无无效值：

-- 添加临时字段存储转换后的值
ALTER TABLE client_inss_dados ADD COLUMN BIRTH_DATE DATE NULL;
-- 根据实际BIRTH字符串格式调整STR_TO_DATE的参数，示例为'YYYY-MM-DD'
UPDATE client_inss_dados SET BIRTH_DATE = STR_TO_DATE(BIRTH, '%Y-%m-%d');
-- 验证数据无误后替换原字段
ALTER TABLE client_inss_dados DROP COLUMN BIRTH;
ALTER TABLE client_inss_dados CHANGE COLUMN BIRTH_DATE BIRTH DATE NOT NULL;

第二步：创建基于BIRTH的普通索引。你的查询本质是筛选BIRTH < DATE_SUB(CURDATE(), INTERVAL 15 YEAR)（等价于年龄>15），索引创建后数据库可快速定位符合条件的行：

CREATE INDEX idx_birth ON client_inss_dados(BIRTH);

对于1亿条数据的表，创建索引需要一定时间和空间，但这是性价比最高的优化方案——比分区实现更简单，维护成本更低。

生成列索引（可选） #

如果需要频繁按年龄查询，可创建存储年龄的生成列并建索引：

ALTER TABLE client_inss_dados ADD COLUMN AGE INT AS (TIMESTAMPDIFF(YEAR, BIRTH, CURDATE())) STORED;
CREATE INDEX idx_age ON client_inss_dados(AGE);

查询语句可简化为：

SELECT * FROM client_inss_dados WHERE AGE > 15;

注意：AGE为存储型生成列，仅在数据插入/更新时计算，不会随时间自动更新实时年龄，若需实时计算，仍建议使用BIRTH字段的索引方案。

3. 范围分区 vs 哈希分区的性能差异 #

假设已修复BIRTH字段类型：

• 范围分区：你的查询是筛选年龄>15，对应BIRTH < 2008-XX-XX（以2023年为例），范围分区可直接裁剪掉所有BIRTH >= 2008的分区，仅扫描符合条件的旧分区，性能提升显著。且范围分区数据按年份有序存储，天然适配范围查询场景。
• 哈希分区：哈希分区会将YEAR(BIRTH)的值哈希后均匀分配到93个分区中，你的查询需要扫描所有分区（哈希值无法对应范围条件），完全无法利用分区裁剪，性能与未分区表几乎无差异，甚至可能因分区额外开销变慢。

因此针对你的查询场景，范围分区远优于哈希分区。

总结优化优先级 #

1. 修复BIRTH字段为DATE类型；
2. 创建BIRTH字段的普通索引；
3. 若索引方案仍无法满足性能需求（如单分区数据量仍过大），再考虑范围分区；
4. 哈希分区不适配你的查询场景，直接排除。

内容的提问来源于stack exchange，提问作者Aks Jacoves