摘要：MySQL中基于Generated Column的索引
MySQL支持基于Generated Column创建索引。我们先看看什么是Generated Column（生成列）？就是表中这列的值是从其它列计算的，这是MySQL 5.7的新特性。
比如，知道直角三角形两条直角边的长度，斜边的长度可以通过两条直角边计算而得。这时候就可以在数据库中只存放直角边，斜边使用Generated Column。
root@database-one 00:02: [gftest]> CREATE TABLE triangle (
sidea DOUBLE,
-> sidea DOUBLE,
-> sideb DOUBLE,
-> sidec DOUBLE AS (SQRT(sidea * sidea + sideb * sideb))
-> );
Query OK, 0 rows affected (0.16 sec)
root@database-one 00:02: [gftest]> INSERT INTO triangle (sidea, sideb) VALUES(1,1),(3,4),(6,8);
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0
root@database-one 00:02: [gftest]> SELECT * FROM triangle;
+-------+-------+--------------------+
| sidea | sideb | sidec |
+-------+-------+--------------------+
| 1 | 1 | 1.4142135623730951 |
| 3 | 4 | 5 |
| 6 | 8 | 10 |
+-------+-------+--------------------+
3 rows in set (0.00 sec)
Generated Column的语法为
col_name data_type [GENERATED ALWAYS] AS (expr)
[VIRTUAL | STORED] [NOT NULL | NULL]
[UNIQUE [KEY]] [[PRIMARY] KEY]
[COMMENT ‘string’]
AS（expr）表示生成列并定义用于计算列值的表达式，前面可以加上GENERATED ALWAYS，使列的生成性质更加明确。
VIRTUAL或STORED关键字指示如何存储列值：
- VIRTUAL：列值不存储，读取行时立即在BEFORE触发器之后计算。这种列不占用存储空间。
- STORED：在插入或更新行时计算并存储列值。需要占用存储空间，并且可以基于其创建索引。
如果未指定关键字，则默认值为VIRTUAL。MySQL允许在表中混合使用VIRTUAL列和STORED列。
说清了Generated Column，我们来看基于Generated Column的索引：
root@database-one 00:36: [gftest]> CREATE TABLE t1test (f1 INT, gc INT AS (f1 + 1) STORED, INDEX (gc)); Query OK, 0 rows affected (0.14 sec) root@database-one 00:39: [gftest]> explain SELECT * FROM t1test WHERE gc > 9; +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | t1test | NULL | range | gc | gc | 5 | NULL | 1 | 100.00 | Using index condition | +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.03 sec)
可以看到，生成的列gc被定义为f1+1，并创建了索引，优化器在生成执行计划时也使用了该索引。
如果WHERE、ORDER BY或GROUP BY引用的表达式与某个生成列的定义匹配，优化器生成执行计划时就会使用基于生成列的索引。
root@database-one 00:43: [gftest]> explain SELECT * FROM t1test WHERE f1 + 1 > 9; +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | t1test | NULL | range | gc | gc | 5 | NULL | 1 | 100.00 | Using index condition | +----+-------------+--------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.01 sec) root@database-one 00:43: [gftest]> show warnings; +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+ | Level | Code | Message | +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+ | Note | 1003 | /* select#1 */ select `gftest`.`t1test`.`f1` AS `f1`,`gftest`.`t1test`.`gc` AS `gc` from `gftest`.`t1test` where (`gftest`.`t1test`.`gc` > 9) | +-------+------+-----------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
可以看到，实际上，优化器是用与表达式匹配的生成列名称gc替换了表达式f1+1。
请注意，优化器使用生成列的索引时有下面这些限制：
- 查询表达式与生成列定义匹配，即表达式必须相同，并且必须具有相同的结果类型。例如，如果生成列的表达式是f1+1，而查询使用1+f1，或者如果f1+1（整数表达式）与字符串进行比较，优化器将无法识别匹配项。
- 适用于以下运算符：=，<，<=，>，>=，BETWEEN，and IN（）。
- 生成列必须至少包含函数调用或上一项中提到的运算符之一的表达式。表达式不能包含对另一列的简单引用。例如，gc INT AS（f1）STORED只包含一个列引用，因此不考虑gc上的索引。
- 如果优化器未能选择所需的索引，则可以使用hint来强制优化器做出不同的选择。
© 2020, morinson. 版权所有. 欢迎转载，但请保留作者及出处。