mysql 创建列的索引_MySQL 生成列索引

MySQL支持在生成列上创建索引，例如:

mysql> create table t1(f1 int,gc int as (f1+1) stored,index(gc));

Query OK, 0 rows affected (0.11 sec)

mysql> insert into t1(f1) values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10);

Query OK, 10 rows affected (0.03 sec)

Records: 10  Duplicates: 0  Warnings: 0

mysql> select * from t1;

+------+------+

| f1   | gc   |

+------+------+

|    1 |    2 |

|    2 |    3 |

|    3 |    4 |

|    4 |    5 |

|    5 |    6 |

|    6 |    7 |

|    7 |    8 |

|    8 |    9 |

|    9 |   10 |

|   10 |   11 |

+------+------+

10 rows in set (0.00 sec)

生成列gc，它的定义为表达式f1+1。这个列gc也创建了索引因此在生成执行计划时优化器可以使用这个索引。下面的查询where条件引用了列gc并且优化器会考虑使用这个索引是否可以生成更有效的执行计划:

mysql> explain select * from t1 where gc>9\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t1

partitions: NULL

type: range

possible_keys: gc

key: gc

key_len: 5

ref: NULL

rows: 2

filtered: 100.00

Extra: Using index condition

优化器可以在生成的列上使用索引来生成执行计划，即使在查询中没有按名称直接引用这些列的情况下也是如此。如果where，order by 或group by子句引用的表达式与某些被索引的生成列相匹配就会出现这种情况。下面的查询没有直接引用生成列gc，但使用的表达式与生成列gc的定义匹配:

mysql> explain select * from t1 where f1+1>9\G;

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: t1

partitions: NULL

type: range

possible_keys: gc

key: gc

key_len: 5

ref: NULL

rows: 2

filtered: 100.00

Extra: Using index condition

优化器识别出了表达式f1+1与生成列gc相匹配并且gc列创建了索引，所以优化器在生成执行计划时考虑使用了该索引。

实际上，优化器已经用与表达式匹配的生成列的名称gc替换了表达式f1 + 1。这在执行show warnings命令所显示的扩展解释信息中可以很明显地看到重写查询语句确实用生成列替换了表达式。

mysql> show warnings\G;

*************************** 1. row ***************************

Level: Note

Code: 1003

Message: /* select#1 */ select `mysql`.`t1`.`f1` AS `f1`,`mysql`.`t1`.`gc` AS `gc` from `mysql`.`t1` where (`mysql`.`t1`.`gc` > 9)

1 row in set (0.00 sec)

优化器使用生成列索引有以下限制和条件:

.对于一个查询语句的表达式是否成生成列定义相匹配，那么表达式必须完全相同并且有相同的结果类型。例如，如果生成列表达式是f1+1，如果查询使用1+f1或者如查f1+1(一个整数表达式)与字符进行比较而不是数字那么优化器将不会认为是匹配的。

.对于这些操作:=,< ,<=,>,>=,between和in优化器处理是，对于不是between和in之外的其它操作符而言，任何一个操作数都可以被匹配的生成列所替换。对于between和in操作符，只有第一个参数可以被匹配的生成列替换，并且其它的参数必须要有相同的结果类型。between和in操作符目前还不支持对JSON的调用。

.生成列必须定义为至少包含一个函数调用或前一项中提到的一个运算符的表达式。表达式不能只是简单的引用其它列。例如，gc int as (f1) stored，这个生成列的定义只是简单的引用了一个列，因此在生成列gc上的索引不会被优化器考虑。

.为了比较字符串与调用JSON函数返回带引号的字符串的被索引的生成列，JSON_UNQUOTE()函数需要在生成列定义中用来删除函数返回值的引号(对于直接比较字符串与函数值，JSON比较器句柄会删了引号，但在索引查找时不会发生)。

.如果优化器未能选择所需的索引，则可以使用索引提示强制优化器做出不同的选择。