在 MySQL 8 中，如果你經(jīng)常寫帶函數(shù)判斷的 SQL，例如：

WHERE WEEKDAY(creatime) < 5

你會(huì)發(fā)現(xiàn)：

• 索引無(wú)法使用
• 執(zhí)行計(jì)劃 type = ALL
• 大表查詢慢得像蝸牛

常見的數(shù)據(jù)計(jì)算，比如“是否工作日、是否有效”、“金額是否超過閾值”、“是否逾期”等，都容易寫成函數(shù)形式，導(dǎo)致索引無(wú)法命中。

在高并發(fā)、大數(shù)據(jù)量的場(chǎng)景下，這種寫法會(huì)拖垮整個(gè)系統(tǒng)

解決辦法是什么？

?? MySQL 生成列（Generated Column）+ STORED（存儲(chǔ)列） + 索引

一、什么是生成列（Generated Column）

MySQL 的生成列有兩種：

生成列的語(yǔ)法：

column_name data_type
GENERATED ALWAYS AS (表達(dá)式)
[VIRTUAL | STORED]

例如，根據(jù) creatime 自動(dòng)計(jì)算是否工作日：

is_workday TINYINT
GENERATED ALWAYS AS (
    CASE WHEN WEEKDAY(creatime) < 5 THEN 1 ELSE 0 END
) STORED

二、STORED 與普通字段有什么區(qū)別？

很多人不清楚為什么“用 STORED 很香”，下面用一個(gè)表格秒懂??

一句話總結(jié)：

STORED = 自動(dòng)計(jì)算的普通字段，可建索引，是 SQL 優(yōu)化神器。

三、為什么 STORED 列可以讓 SQL 飛起來(lái)？

來(lái)看經(jīng)典錯(cuò)誤寫法：

WHERE WEEKDAY(creatime) < 5

你對(duì) creatime 做了函數(shù)：

• creatime 索引用不了
• 強(qiáng)制全表掃
• 大數(shù)據(jù)量直接炸

而 STORED 生成列寫法：

WHERE is_workday = 1

它是普通字段：

• 可以建索引
• 非常高效
• 查詢極快

MySQL 查詢優(yōu)化器最喜歡：

字段 = 常量
字段 BETWEEN 區(qū)間
字段 IN (...)

生成列完美契合這一點(diǎn)。

四、一個(gè)醫(yī)院真實(shí)業(yè)務(wù)案例：統(tǒng)計(jì)工作日到訪人數(shù)

醫(yī)院表 t_visit：

CREATE TABLE t_visit (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    hospital_id INT,
    creatime DATETIME,
    visit_num INT
);

需求：

統(tǒng)計(jì)各醫(yī)院在工作日（周一到周五）的就診人數(shù)

錯(cuò)誤寫法：索引完全失效！

SELECT SUM(visit_num)
FROM t_visit
WHERE WEEKDAY(creatime) < 5;

解釋：

• Creatime 上套函數(shù) → 索引失效
• 查詢 100W 行 → 全表掃描
• 業(yè)務(wù)卡死

五、使用 STORED，企業(yè)級(jí)寫法來(lái)了

1）添加生成列

ALTER TABLE t_visit
  ADD COLUMN is_workday TINYINT
    GENERATED ALWAYS AS (
      CASE WHEN WEEKDAY(creatime) < 5 THEN 1 ELSE 0 END
    ) STORED,
  ADD INDEX idx_visit_workday (is_workday, creatime);

2）正確查詢寫法

SELECT
    hospital_id,
    SUM(visit_num)
FROM t_visit
WHERE
    is_workday = 1
    AND creatime BETWEEN '2025-01-01' AND '2025-02-01'
GROUP BY hospital_id;

EXPLAIN 顯示：

• type = range
• key = idx_visit_workday
• 幾萬(wàn)行 → 幾千行
• 性能提升 5～30 倍

六、為什么企業(yè)更喜歡 STORED 而不是 VIRTUAL？

大量業(yè)務(wù)都在用：

• 是否工作日
• 是否節(jié)假日
• 是否逾期
• 是否有效
• 金額區(qū)間分類（如大單、中單、小單）
• 年齡段分類
• 設(shè)備狀態(tài)派生字段

只要是某列可以推導(dǎo)出來(lái)的值，且要做過濾、排序、聚合，80% 的情況下會(huì)用 STORED。

七、STORED 生成列 + dim_date = 雙劍合璧最強(qiáng)方案

在 BI / 數(shù)倉(cāng)中常用維表：

CREATE TABLE dim_date (
    date_key DATE PRIMARY KEY,
    weekday TINYINT,
    is_workday TINYINT,
    is_holiday TINYINT,
    holiday_name VARCHAR(20)
);

事實(shí)表：

ALTER TABLE t_visit
ADD visit_date DATE GENERATED ALWAYS AS (DATE(creatime)) STORED,
ADD INDEX (visit_date);

查詢：

SELECT
    v.hospital_id,
    SUM(v.visit_num)
FROM t_visit v
JOIN dim_date d ON v.visit_date = d.date_key
WHERE 
    d.is_workday = 1
GROUP BY v.hospital_id;

優(yōu)勢(shì)：

• 超高性能
• 法定節(jié)假日、調(diào)休隨便改
• 報(bào)表、看板、數(shù)據(jù)集市都復(fù)用 dim_date
• 企業(yè)統(tǒng)一口徑

八、生產(chǎn)注意事項(xiàng)

1. 生成列不能手工 INSERT / UPDATE
2. 表插入非常頻繁時(shí)，STORED 會(huì)多一次計(jì)算成本（但一般可以接受）
3. 表過大時(shí)，修改表結(jié)構(gòu)添加 STORED 列要注意線上壓力
4. 建立索引時(shí)一定要注意前導(dǎo)列（選擇性越高越好）
5. 如果你的計(jì)算很復(fù)雜，可以考慮 STORED + 函數(shù)表達(dá)式預(yù)處理

九、總結(jié)：一句話記住 STORED

STORED 生成列，是 MySQL 自動(dòng)計(jì)算、自動(dòng)維護(hù)、可建索引的派生字段。
它讓復(fù)雜 SQL 拆分成“插入時(shí)算一次，查詢時(shí)用高速索引”，
是 OLTP 性能優(yōu)化最常用、最實(shí)用也最容易被忽略的武器。

到此這篇關(guān)于MySQL STORED 生成列(Generated Column) 的使用小結(jié)的文章就介紹到這了,更多相關(guān)MySQL STORED 生成列內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論