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

 表数据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。MySQL测试结果是6.7s，pg测试结果是4.4s。

 

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

 

 

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，create_month（年月分开）

-- 添加计算列 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);

 MySQL测试结果是4.4s，pg测试结果是2.0s。

 

2、PostgerSQL

2.1、使用原始列create_time的索引

首先创建索引

create index idx_create on tb_person(create_time);

执行以下，结果为4.4s

explain analyze SELECT TO_CHAR(create_time, 'YYYY-MM') AS month, COUNT(*)
FROM tb_person
WHERE EXTRACT(YEAR FROM create_time) = 2025
GROUP BY month
ORDER BY month;

执行以下，结果为4.0s

explain analyze SELECT TO_CHAR(create_time, 'YYYY-MM') AS month, COUNT(*)
FROM tb_person
WHERE create_time >= '2025-01-01' AND create_time < '2026-01-01'
GROUP BY month
ORDER BY month;

2.2、添加计算列 create_year，create_month（年月分开）

参考：PostgreSQL生成列

ALTER TABLE tb_person
ADD COLUMN create_year VARCHAR(7) GENERATED ALWAYS AS (EXTRACT(YEAR FROM create_time)) STORED;
ALTER TABLE tb_person
ADD COLUMN create_month VARCHAR(7) GENERATED ALWAYS AS (EXTRACT(MONTH FROM create_time)) STORED;

CREATE INDEX idx_year_month ON tb_person(create_year, create_month);

查询耗时2.0s

SELECT create_year AS year, create_month AS month, COUNT(*)
FROM tb_person
WHERE create_year = '2025'
GROUP BY create_year,create_month
ORDER BY create_year,create_month;