展开查看详情
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)
热门城市
