测试2000万单表按日期统计的性能（MySQL，PostgreSQL）

表数据tb_person，参考测试java程序插入单表2000万条数据（MySQL,PostgreSQL,达梦）

 

场景：统计2025年每月用户注册数，以create_time为注册时间

 

 

1、MySQL

1.1、使用原始列create_time的索引

首先创建索引

create index idx_create on tb_person(create_time);

查询条件 WHERE YEAR(create_time) = 2025，因为查询的字段都包含在索引中（覆盖索引），故explain结果显示type为index

 

使用查询条件 WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01'，explain结果显示type为range，但实际速度没什么提升。

 

 

1.2、使用函数索引

使用函数索引（MySQL 8.0+）‌：允许直接对函数结果创建索引

CREATE INDEX idx_year_month ON tb_person( (DATE_FORMAT(create_time, '%Y-%m')) );

使用结果如下

 

1.3、添加计算列 create_year_month

首先删除前面场景的索引

drop index idx_year_month on tb_person;

添加计算列和索引

-- 添加计算列 create_year_month
ALTER TABLE tb_person
ADD create_year_month VARCHAR(7) AS (DATE_FORMAT(create_time, '%Y-%m'));
-- 建立索引
CREATE INDEX idx_year_month ON tb_person(create_year_month);

 

 

1.4、

-- 添加计算列 create_year
ALTER TABLE tb_person
ADD create_year VARCHAR(7) AS (DATE_FORMAT(create_time, '%Y'));
-- 添加计算列 create_month
ALTER TABLE tb_person
ADD create_month VARCHAR(7) AS (DATE_FORMAT(create_time, '%m'));
-- 建立索引
CREATE INDEX idx_year_month ON tb_person(create_year, create_month);

 

 

 

 

2、