Virtual Columns in MySQL and MariaDB

请加入费德里科·拉佐利(Federico Razzoli),这位顾问展示了“MySQL和Mariadb中的虚拟专栏”。
Mariadb5.2和MySQL5.7引入了虚拟列,具有不同的实现。
它们的特点和局限性是相似的,但不完全相同。主要的区别是只有MySQL允许您在非持久列上构建索引。
在本文中,我们将介绍虚拟列的一些用例,例如查询简化和基于SQL表达式的唯一约束。特别是,我们将看到如何使用虚拟列来索引MySQL中的JSON数据,或者Mariadb中的动态列。
还将讨论性能和限制。

展开查看详情

1.Virtual Columns in MySQL and MariaDB

2.$ whoami • My name is Federico Razzoli • Percona Consultant • MariaDB Community Ambassador • federico.razzoli@percona.com

3.What is a Virtual Column? It’s a… column. But: • Its value is generated automatically • Can be stored on disk or computed on the fly • VIRTUAL, STORED • Cannot be changed directly by the user (UPDATE) • Changed automatically by DBMS

4.Supported in… Syntax and semantics are not standard • DB2 (Expression Generated Columns) • Sql Server (Computed, Persisted) • Oracle 11g (only virtual) Open source world: • Firebird (Computed Columns) • PostgreSQL has functional indexes

5.What about the MySQL world? • MariaDB 5.2 (2010), improved in 10.2 • MySQL 5.7 (2015)

6.MySQL syntax <type> [GENERATED ALWAYS] AS ( < expression > ) [VIRTUAL | STORED] [UNIQUE [KEY]] [COMMENT < text >] [NOT NULL | NULL] [[PRIMARY] KEY] Example: taxed_price INTEGER UNSIGNED AS (price + (price / 100 * 20)) VIRTUAL NOT NULL

7.MariaDB syntax <type> [GENERATED ALWAYS] AS ( <expression> ) [VIRTUAL | PERSISTENT | STORED] [UNIQUE [KEY]] [COMMENT <text>] Example: taxed_price INTEGER UNSIGNED AS (price + (price / 100 * 20)) VIRTUAL

8.Virtual Column values • Simple SQL expression • No Stored Functions • No Subqueries • No Variables

9.Virtual Column types Virtual: • Generated when needed (SELECT) • No overhead on writes Stored/Persistent: • Written on disk • No delay on SELECT

10.Alternatives: Views Pros: • Any expression is allowed • Value can be NULL (MariaDB) Cons: • Administration is harder • Cannot index the value

11.View Example CREATE VIEW v_product AS ( SELECT id, price, name, description, (price + (price / 100 * 20)) AS taxed_price FROM v_product );

12.Alternatives: Triggers Pros: • More flexible Cons: • Complex triggers are slow • Administration is harder

13.Triggers Example CREATE TRIGGER … BEFORE INSERT … BEGIN SET NEW.taxed_price := NEW.price + (price / 100 * 20); SET NEW.label := IFNULL(name, id); END CREATE TRIGGER … BEFORE UPDATE … BEGIN SET NEW.taxed_price := NEW.price + (price / 100 * 20); SET NEW.label := IFNULL(name, id); END

14.Triggers Example CREATE TRIGGER … BEFORE INSERT … BEGIN SET NEW.taxed_price := NEW.price + (price / 100 * (SELECT … FROM tax WHERE ...)); END CREATE TRIGGER … BEFORE UPDATE … BEGIN SET NEW.taxed_price := NEW.price + (price / 100 * (SELECT … FROM tax WHERE ...)); END

15.Alternatives: DEFAULT in MariaDB 10.2 • Can be the right choice • Depending if you want the value to be immutable and updated automatically

16.DEFAULT Example taxed_price INTEGER UNSIGNED NOT NULL DEFAULT (price + (price / 100 * 20))

17.Summary of Alternatives Virtual: • Views Stored: • Triggers • DEFAULT (MariaDB 10.2)

18.More Virtual Columns Examples name VARCHAR(50) NOT NULL, normalized_name VARCHAR(50) AS (REPLACE(name, ' ', ''))

19.More Virtual Columns Examples birth_date DATE NOT NULL, death_date DATE, is_alive BOOL AS (death_date IS NULL)

20.More Virtual Columns Examples age INTEGER AS (DATEDIFF(NOW(), birth_date) DIV 365) STORED

21.Indexes on Virtual Columns MySQL: • Both STORED and VIRTUAL columns can be indexed MariaDB: • Only STORED columns can be indexed

22.Indexed Virtual Columns Examples name VARCHAR(50) NOT NULL, normalized_name VARCHAR(50) AS (REPLACE(name, ' ', '')) UNIQUE

23.Indexed Virtual Columns Examples label VARCHAR(10) NOT NULL, prefix VARCHAR(3) AS (LEFT(label, 3)) UNIQUE

24.MySQL Limitations A generated value: • Cannot refer to an AUTO_INCREMENT column • Can refer to a virtual column if it appears earlier in CREATE TABLE • Has to be deterministic • Cannot use variables Syntax: • Cannot specify DEFAULT value in an INSERT • Cannot use: INSERT INTO t SELECT * FROM t

25.MariaDB Limitations • No binary compatibility with MySQL • Dump is required to switch between MySQL and MariaDB • MariaDB virtual columns cannot be NULLable • and NOT NULL syntax is not supported • Cannot be used as Primary Keys • STORED keyword not supported before MariaDB 10.2 • Many functions not supported before MariaDB 10.2

26.MariaDB 10.2 Limitations • Can refer to a virtual column if it appears before in CREATE TABLE • To avoid circular references • Non-STORED columns need to be deterministic

27.Optimizer CREATE TABLE person ( id INT AUTO_INCREMENT PRIMARY KEY, first_name VARCHAR(50), last_name VARCHAR(50), initials VARCHAR(2) AS (UPPER(CONCAT(LEFT(first_name, 1), LEFT(last_name, 1)))) PERSISTENT, INDEX idx_initials (initials) ); SELECT * FROM person WHERE UPPER(CONCAT(LEFT(first_name, 1), LEFT(last_name, 1))) = 'MJ‘;

28.MySQL Optimizer mysql> EXPLAIN SELECT * FROM person -> WHERE UPPER(CONCAT(LEFT(first_name, 1), LEFT(last_name, 1))) = 'MJ' \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: person partitions: NULL type: ref possible_keys: idx_initials key: idx_initials key_len: 5 ref: const rows: 1 filtered: 100.00

29.Storage Engines The following engines support Virtual Columns: • InnoDB • MyISAM • Aria (MariaDB) • CONNECT (MariaDB)