发布于 由admin

MySQL高级讲座篇之：`Generated Columns`（生成列）在数据冗余和索引优化中的作用。

各位老铁，大家好！今天咱们聊点MySQL里挺有意思的玩意儿——Generated Columns（生成列）。这玩意儿啊，用好了能让你的数据库性能嗖嗖地往上涨，用不好嘛…就当是学了个新知识呗！（手动滑稽）

开场白：数据冗余和索引优化的爱恨情仇

咱们先聊聊数据库里一对老冤家：数据冗余和索引优化。

  • 数据冗余： 简单来说，就是一份数据存好几份。好处是查询快，坏处是更新麻烦，还占地方。就像你家冰箱里塞满了各种口味的冰淇淋，想吃啥拿啥方便，但冰箱空间也快没了，而且吃不完就过期了。
  • 索引优化： 就像字典里的目录，能帮你快速找到想要的数据。好处是查询快，坏处是创建索引要时间和空间，而且更新数据时还要维护索引。就像你给冰箱里的冰淇淋做了个详细的分类目录，找起来是快了，但每次买新冰淇淋都要更新目录，也挺累的。

那有没有办法让这两个老冤家握手言和，既能提高查询速度，又能减少数据冗余呢？答案就是：Generated Columns！

Generated Columns：你想要的，它都有！

Generated Columns，顾名思义，就是“生成”出来的列。它的值不是直接存储在表里，而是通过一个表达式计算出来的。这就像你家冰箱里有个自动冰淇淋机，你想吃什么口味，它就现场给你做，不用提前存好。

Generated Columns 的两种类型

Generated Columns 分为两种类型：

  1. VIRTUAL (虚拟列): 这种列的值不会实际存储在磁盘上。每次查询时，MySQL会动态计算它的值。就像你每次想吃冰淇淋时，冰淇淋机才开始制作。
  2. STORED (存储列): 这种列的值会实际存储在磁盘上。每次插入或更新数据时，MySQL会计算并存储它的值。就像冰淇淋机做好冰淇淋后，会把它们放到冰箱里。

语法结构

ALTER TABLE 表名
ADD COLUMN 列名 数据类型 GENERATED ALWAYS AS (表达式) VIRTUAL | STORED;
  • 表名: 要添加生成列的表名。
  • 列名: 生成列的名称。
  • 数据类型: 生成列的数据类型，必须与表达式的结果类型兼容。
  • 表达式: 用于计算生成列值的表达式。
  • VIRTUAL | STORED: 指定生成列的类型，VIRTUAL表示虚拟列，STORED表示存储列。

Generated Columns 的应用场景

  1. 简化复杂查询

假设你有一个订单表 orders，包含 price (单价) 和 quantity (数量) 两列。你想经常查询每个订单的总金额，每次都写 price * quantity 太麻烦了。这时，你可以创建一个生成列 total_amount：

ALTER TABLE orders
ADD COLUMN total_amount DECIMAL(10, 2) GENERATED ALWAYS AS (price * quantity) VIRTUAL;

-- 查询订单总金额，简单多了！
SELECT order_id, total_amount FROM orders;

这样，每次查询订单总金额，直接访问 total_amount 列就可以了，不用每次都计算。

  1. 支持复杂索引

MySQL 索引只能在实际存储的列上创建，不能直接在表达式上创建。但有了 Generated Columns，我们就可以先创建一个基于表达式的生成列，然后在该列上创建索引。

举个例子，假设你有一个用户表 users，包含 first_name 和 last_name 两列。你想经常根据用户的完整姓名进行查询，可以这样做：

ALTER TABLE users
ADD COLUMN full_name VARCHAR(255) GENERATED ALWAYS AS (CONCAT(first_name, ' ', last_name)) STORED;

CREATE INDEX idx_full_name ON users (full_name);

-- 根据完整姓名查询用户，速度嗖嗖的！
SELECT * FROM users WHERE full_name = '张 三';

这里，我们创建了一个存储列 full_name，并将 first_name 和 last_name 拼接起来。然后，我们在 full_name 列上创建了一个索引。这样，根据完整姓名查询用户时，MySQL 就可以直接使用索引，而不用每次都计算完整姓名。

  1. 数据校验

Generated Columns 可以用来进行数据校验，确保数据的完整性。

例如，你想确保订单表 orders 中的 quantity 列的值必须大于 0，可以这样做：

ALTER TABLE orders
ADD COLUMN valid_quantity BOOLEAN GENERATED ALWAYS AS (quantity > 0) STORED;

-- 插入非法数据会报错
INSERT INTO orders (price, quantity) VALUES (10, -1);
-- ERROR 3824 (HY000): Check constraint 'orders_chk_1' is violated.

这里，我们创建了一个存储列 valid_quantity，其值为 quantity > 0 的布尔值。MySQL 会自动检查 valid_quantity 列的值是否为 TRUE，如果不是，则拒绝插入或更新数据。

VIRTUAL vs STORED：选哪个？

选择 VIRTUAL 还是 STORED，取决于你的具体需求：

特性 VIRTUAL STORED
存储空间 不占用磁盘空间 占用磁盘空间
查询性能 查询时动态计算，可能影响查询性能 查询时直接读取，查询性能更好
更新性能 不影响更新性能 插入/更新数据时需要计算和存储，影响更新性能
使用场景 不经常查询，对存储空间要求高的场景 经常查询，对查询性能要求高的场景
表达式限制 表达式必须是确定性的，不能包含函数或子查询 表达式必须是确定性的，不能包含函数或子查询

简单来说，如果你的生成列不经常查询，或者对存储空间要求比较高，就选择 VIRTUAL。如果你的生成列经常查询，或者对查询性能要求比较高，就选择 STORED。

Generated Columns 的限制

Generated Columns 虽然好用，但也有一些限制：

  • 表达式的限制： 表达式必须是确定性的，即对于相同的输入，必须产生相同的输出。这意味着表达式不能包含函数、子查询或用户自定义变量。
  • 自引用限制： 生成列不能引用自身，也不能循环引用其他生成列。
  • 外键限制： 生成列不能作为外键的目标列。
  • 全文索引限制： 不能在生成列上创建全文索引。

一些实战案例

  1. 地理位置计算

假设你有一个店铺表 shops，包含 latitude (纬度) 和 longitude (经度) 两列。你想根据店铺的地理位置进行查询，可以这样做：

ALTER TABLE shops
ADD COLUMN location POINT GENERATED ALWAYS AS (POINT(latitude, longitude)) STORED;

CREATE SPATIAL INDEX idx_location ON shops (location);

-- 查询附近店铺
SELECT * FROM shops WHERE ST_Distance_Sphere(location, POINT(39.9087, 116.3975)) < 1000;

这里，我们创建了一个存储列 location，其值为 POINT(latitude, longitude)，表示店铺的地理位置。然后，我们在 location 列上创建了一个空间索引。这样，根据地理位置查询店铺时，MySQL 就可以直接使用空间索引，而不用每次都计算距离。

  1. JSON 数据提取

假设你有一个商品表 products，包含一个 properties 列，存储 JSON 格式的商品属性。你想根据商品属性进行查询，可以这样做：

ALTER TABLE products
ADD COLUMN color VARCHAR(255) GENERATED ALWAYS AS (properties ->> '$.color') VIRTUAL;

CREATE INDEX idx_color ON products (color);

-- 查询颜色为红色的商品
SELECT * FROM products WHERE color = 'red';

这里，我们创建了一个虚拟列 color，其值为 properties ->> '$.color'，表示商品颜色的值。然后，我们在 color 列上创建了一个索引。这样，根据商品颜色查询商品时，MySQL 就可以直接使用索引，而不用每次都解析 JSON 数据。

注意事项

  • 在创建或修改包含 Generated Columns 的表时，要仔细考虑表达式的性能影响。复杂的表达式可能会导致插入、更新和查询性能下降。
  • Generated Columns 的数据类型必须与表达式的结果类型兼容。
  • 在删除 Generated Columns 时，要确保没有其他对象依赖于该列。

总结

Generated Columns 是 MySQL 中一个非常强大的特性，它可以用来简化复杂查询、支持复杂索引和进行数据校验。合理使用 Generated Columns 可以提高数据库性能，减少数据冗余，并增强数据完整性。

好了，今天的讲座就到这里。希望大家能掌握 Generated Columns 的使用方法，并在实际工作中灵活运用。如果有什么问题，欢迎随时提问！下次有机会再跟大家聊聊 MySQL 的其他高级特性。（溜了溜了~）

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注