MySQL8.0 函数索引
MySQL8.0.13 开始支持 在索引中使用函数的值
支持降序索引
支持JSON 数据的索引
函数索引基于虚拟列功能实现
创建表与索引
在 t3 表 上建立索引,创建普通索引和函数的索引
CREATE TABLE `t3` (
`c1` varchar(10),
`c2` varchar(10)
);
mysql> create index idx_c1 on t3(c1);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
-- 创建函数索引
mysql> create index idx_func_c2 on t3( (UPPER(c2)) );
Query OK, 0 rows affected (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> explain select * from t3 where upper(c1) = 'ABC' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t3
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1
filtered: 100.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t3 where upper(c2) = 'ABC' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t3
partitions: NULL
type: ref
possible_keys: idx_func_c2
key: idx_func_c2
key_len: 43
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.01 sec)
可以看到 c2 这个字段 会走 函数的索引
针对JSON数据 创建索引
json 数据创建索引
CAST(x AS type) 可以将一个数据类型 转化为另一个数据类型。 type 取值有 BINARY, CHAR(n), DATE,TIME, DATETIME,DEMICAL,SIGNED,UNSIGNED 等
mysql> select cast(1100 as char(2));
+-----------------------+
| cast(1100 as char(2)) |
+-----------------------+
| 11 |
+-----------------------+
1 row in set, 1 warning (0.00 sec)
将 1100 数字 转为字符串类型，结果为 '11'
create table t4(data json, index((CAST(data ->> '$.name' as char(30) ) )));
mysql> show index from t4\G
*************************** 1. row ***************************
Table: t4
Non_unique: 1
Key_name: functional_index
Seq_in_index: 1
Column_name: NULL
Collation: A
Cardinality: 0
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
Visible: YES
Expression: cast(json_unquote(json_extract(`data`,_latin1\'$.name\')) as char(30) charset latin1)
1 row in set (0.00 sec)
使用 explain 分析 是否可以使用索引.
mysql> explain select * from t4 where CAST(data ->> '$.name' as char(30)) = 'abc' \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t4
partitions: NULL
type: ref
possible_keys: functional_index
key: functional_index
key_len: 33
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
发现可以使用到索引
创建一个虚拟列
来给 t3 表 增加一列 ，增加一个虚拟计算列
mysql> alter table t3 add column c3 varchar(10) generated always as (upper(c1));
mysql> select * from t3;
Empty set (0.00 sec)
mysql> insert into t3(c1,c2) values('aa','frank');
Query OK, 1 row affected (0.01 sec)
mysql> select * from t3;
+------+-------+------+
| c1 | c2 | c3 |
+------+-------+------+
| aa | frank | AA |
+------+-------+------+
1 row in set (0.00 sec)
创建一个普通的索引 idx_c3
create index idx_c3 on t3(c3);
mysql> explain select * from t3 where c3='aa';
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t3 | NULL | ref | idx_c3 | idx_c3 | 43 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
此时可以发现就走索引了. 相当于是 建立了一个函数索引
explain select * from t3 where upper(c1)='AAA';
mysql> explain select * from t3 where upper(c1)='AAA';
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t3 | NULL | ref | idx_c3 | idx_c3 | 43 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
小问题
有一个 员工表 ， id, 奖金 ， 薪水 三列, 表结构如下
create table employee(
id int auto_increment primary key,
salary int not null,
bonus int not null
);
经常需要 按照 salary 加上 bonus 的和值 进行查询并且 排序显示，如何设计索引？
create index idx_func on employee((salary + bonus));
show index from employee \G
这样做 是可以使用索引的
mysql> explain select * from employee where salary + bonus =100 \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: employee
partitions: NULL
type: ref
possible_keys: idx_func
key: idx_func
key_len: 8
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
官方文档例子
example1 指定索引表达式与JSON_UNQUOTE()相同的排序方式
Assign the indexed expression the same collation as JSON_UNQUOTE():
CREATE TABLE employees (
data JSON,
INDEX idx ((CAST(data->>"$.name" AS CHAR(30)) COLLATE utf8mb4_bin))
);
INSERT INTO employees VALUES
('{ "name": "james", "salary": 9000 }'),
('{ "name": "James", "salary": 10000 }'),
('{ "name": "Mary", "salary": 12000 }'),
('{ "name": "Peter", "salary": 8000 }');
SELECT * FROM employees WHERE data->>'$.name' = 'James';
explain SELECT * FROM employees WHERE data->>'$.name' = 'James';
The ->> operator is the same as JSON_UNQUOTE(JSON_EXTRACT(...)), and JSON_UNQUOTE() returns a string with collation utf8mb4_bin. The comparison is thus case-sensitive, and only one row matches:
example2 在查询中指定完整的表达式
CREATE TABLE employees (
data JSON,
INDEX idx ((CAST(data->>"$.name" AS CHAR(30))))
);
INSERT INTO employees VALUES
('{ "name": "james", "salary": 9000 }'),
('{ "name": "James", "salary": 10000 }'),
('{ "name": "Mary", "salary": 12000 }'),
('{ "name": "Peter", "salary": 8000 }');
explain SELECT * FROM employees WHERE CAST(data->>'$.name' AS CHAR(30)) = 'James';
SELECT * FROM employees WHERE CAST(data->>'$.name' AS CHAR(30)) = 'James';
CAST() returns a string with collation utf8mb4_0900_ai_ci, so the comparison case-insensitive and two rows match:
cast() 返回的 字符排序规则 是 utf8mb4_0900_ai_ci ，这个是大小写不敏感的，因此返回了两条数据。
+------------------------------------+
| data |
+------------------------------------+
| {"name": "james", "salary": 9000} |
| {"name": "James", "salary": 10000} |
+------------------------------------+
总结
函数索引的功能 是MySQL8.0 以后才有的功能 ，如果有了这个特性，可以在使用函数的时候 使用这个索引啦，并且还支持JSON 的字段索引，是不是很方便呢？
参考文档
create-index-functional-key-parts