糟糕的数据设计导致糟糕的性能：从 105 分钟到 17 秒

症状：一个查询运行 105 分钟

一个客户紧急联系我。他们的夜间批处理任务（负责生成日报）耗时越来越长。一年前 10 分钟就能完成的工作，现在需要 105 分钟。数据量确实增长了，但还不足以解释执行时间增加十倍。

问题查询是一个经典的 JOIN，连接事务表和日历表：

SELECT
    t.transaction_id,
    t.amount,
    t.transaction_date,
    c.fiscal_year,
    c.fiscal_quarter
FROM transactions t
JOIN calendar c ON t.transaction_date = c.calendar_date
WHERE t.created_at >= '2024-01-01';

乍一看没什么特别的。两张表，一个日期上的连接，一个时间过滤。然而却要 105 分钟。

诊断：类型不匹配

执行计划分析（EXPLAIN）揭示了 calendar 表上的全表扫描。对于一个理应是主键的连接条件来说，这很奇怪。

检查表结构，问题一目了然：

-- 事务表
CREATE TABLE transactions (
    transaction_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    amount DECIMAL(10,2),
    transaction_date INT NOT NULL,  -- 以 YYYYMMDD 格式存储
    created_at DATETIME
);

-- 日历表
CREATE TABLE calendar (
    calendar_date DATE NOT NULL PRIMARY KEY,
    fiscal_year SMALLINT,
    fiscal_quarter TINYINT
);

transactions 表中的 transaction_date 列是 INT 类型，以 YYYYMMDD 格式存储日期（例如，2024 年 1 月 15 日存储为 20240115）。calendar 表中的 calendar_date 列是实际的 DATE 类型。

当 MariaDB / MySQL 执行 JOIN 时，它必须将 INT 与 DATE 进行比较。对于 transactions 中的每一行，引擎都要对 calendar 中的每一行进行隐式类型转换（将 DATE 转为 INT，或反过来）。这种隐式转换使得 calendar_date 上的索引无法使用。结果：对 transactions 中的每一行都要对 calendar 进行全表扫描。

以 200 万条事务和 10,000 行日历数据计算，这就是 200 亿次带类型转换的比较。

为什么不能直接修改类型？

显而易见的答案是将 transaction_date 列从 INT 转换为 DATE。但在生产系统的现实中：

• 该表有 15 GB。ALTER TABLE 将花费数小时并锁定表。
• 47 个存储过程和 12 个视图引用 transaction_date 作为 INT 使用。
• PHP 应用程序对该列使用算术比较（WHERE transaction_date > 20240101）。
• ETL 加载批处理从遗留系统以 INT 格式发送日期。

修改类型是正确的长期解决方案，但不是客户今晚需要的即时修复。

解决方案：虚拟生成列

MariaDB / MySQL 支持虚拟列（或生成列）。这些是根据其他列动态计算的列，可以不占用物理存储（VIRTUAL）或占用存储（STORED）。

ALTER TABLE transactions
ADD COLUMN transaction_date_real DATE AS (
    STR_TO_DATE(CAST(transaction_date AS CHAR(8)), '%Y%m%d')
) VIRTUAL;

这个列可以动态地将 INT 转换为 DATE。但仅有虚拟列并不能解决性能问题。还需要一个索引：

ALTER TABLE transactions
ADD COLUMN transaction_date_real DATE AS (
    STR_TO_DATE(CAST(transaction_date AS CHAR(8)), '%Y%m%d')
) STORED,
ADD INDEX idx_transaction_date_real (transaction_date_real);

我们使用 STORED 而非 VIRTUAL，以便能够创建索引。该列会被物理存储，索引在插入和更新时自动维护。

修正后的查询

SELECT
    t.transaction_id,
    t.amount,
    t.transaction_date,
    c.fiscal_year,
    c.fiscal_quarter
FROM transactions t
JOIN calendar c ON t.transaction_date_real = c.calendar_date
WHERE t.created_at >= '2024-01-01';

JOIN 现在是 DATE 与 DATE 的比较。索引可以被使用。执行计划显示 ref 而非全表扫描。

结果：17 秒

从 105 分钟到 17 秒。99.7% 的提升，无需更改现有 Schema，无需修改应用程序，无需触及存储过程。

为什么隐式转换是一个陷阱

这个案例说明了一个根本性问题：JOIN 和 WHERE 子句中的隐式类型转换是无声的性能杀手。

MariaDB / MySQL 在许多情况下执行隐式转换：

• INT 与 VARCHAR 比较：INT 被转换为 VARCHAR
• INT 与 DATE 比较：DATE 被转换为数字
• VARCHAR(utf8) 与 VARCHAR(latin1) 比较：字符集转换
• DECIMAL 与 FLOAT 比较：浮点转换

在每种情况下，转换都会使索引不可用，因为引擎无法在值需要先转换的情况下直接进行 B 树索引查找。

教训：数据设计是基础

数据库性能在设计阶段就已决定，而非调优阶段。没有任何索引、buffer pool 配置或硬件能弥补错误的数据类型选择。

基本规则：

1. 日期必须使用 DATE 或 DATETIME 存储，永远不要用 INT 或 VARCHAR。
2. JOIN 列必须具有相同的类型和相同的字符集/排序规则。
3. 系统性地使用 EXPLAIN 来验证你的 JOIN 是否使用了索引。
4. 注意隐式转换，可以使用 EXPLAIN ANALYZE 工具（MariaDB 10.1+）来检查。

数据设计并不光鲜。它不如调优系统变量或搭建 Galera 集群那样令人兴奋。但它是基础。当基础有问题时，其他一切都会崩塌 — 每次 105 分钟。

本文最初发表于 Medium。