一个常见的场景是查询对涉及某种函数表达式的列使用过滤条件,这种情况下,无法使用该列上的索引。
从MySQL8.0.13开始支持函数索引。
假设有个产品信息表products,包含一个列create_time,类型是timestamp。如果想统计某个月内,产品的平均价格,你可以这样写:
mysql> SELECT AVG(price) FROM products WHERE MONTH(create_time)=10; +------------+ | AVG(price) | +------------+ | 202.982582 | +------------+
查询返回了正确的结果。但是如果查看执行计划会发现:
mysql> EXPLAIN SELECT AVG(price) FROM products WHERE MONTH(create_time)=10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: products partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 99015 filtered: 100.00 Extra: Using where
查询走的是全表扫描。
现在我们在create_time列上创建一个索引:
mysql> ALTER TABLE products ADD INDEX(create_time); Query OK, 0 rows affected (0.71 sec) Records: 0 Duplicates: 0 Warnings: 0
mysql> explain SELECT AVG(price) FROM products WHERE MONTH(create_time)=10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: products partitions: NULL type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 99015 filtered: 100.00 Extra: Using where
仍然是全表扫描,创建的索引没有效果。
为了优化整个查询,需要重写语句,才能使用上索引。
mysql> SELECT AVG(price) FROM products WHERE create_time BETWEEN '2019-10-01' AND '2019-11-01'; +------------+ | AVG(price) | +------------+ | 202.982582 | +------------+
mysql> EXPLAIN SELECT AVG(price) FROM products WHERE create_time BETWEEN '2019-10-01' AND '2019-11-01'\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: products partitions: NULL type: range possible_keys: create_time key: create_time key_len: 5 ref: NULL rows: 182 filtered: 100.00 Extra: Using index condition
现在可以使用上索引了。
很简单的一个解决方案,但并不是任何时候都可以这样修改代码的。
从MySQL8.0.13开始,MySQL开始支持函数索引了。
来创建一个函数索引:
mysql> ALTER TABLE products ADD INDEX((MONTH(create_time))); Query OK, 0 rows affected (0.74 sec) Records: 0 Duplicates: 0 Warnings: 0
注意这里是双括号。
语法是正确的,因为表达式必须用括号括起来以将其与列或列前缀区分开来。
不然会报错:
mysql> ALTER TABLE products ADD INDEX(MONTH(create_time)); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'create_time))' at line 1
再来执行上面的查询:
mysql> SELECT AVG(price) FROM products WHERE MONTH(create_time)=10; +------------+ | AVG(price) | +------------+ | 202.982582 | +------------+
mysql> EXPLAIN SELECT AVG(price) FROM products WHERE MONTH(create_time)=10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: products partitions: NULL type: ref possible_keys: functional_index key: functional_index key_len: 5 ref: const rows: 182 filtered: 100.00 Extra: NULL
查询变快了,也不再走全表扫描了。函数索引functional_index被使用了。只检索了182行记录。
哪些函数索引被支持
函数索引可以包含各种表达式。比如:
·index((col1+col2))
·index((func(col1)+col2-col3))
甚至可以使用asc、desc。如:
·index( (month(col1)) desc)
可以包含多个函数部分,每个都用括号括起来:
·index( ( col1 + col2 ), ( FUNC(col2) ) )
·index((func(col1)), col2, (col2+col3), col4)
当然也存在一些限制:
·函数键不能只是包含单个列,比如index((col1), (col2))是不被支持的
·主键不能被包含在函数键中
·外键不能被包含在函数键中
·spatial和fulltext索引不能被包含在函数键中
·函数键不能引用列的前缀
函数索引的内部原理
函数索引是通过
函数索引是以隐藏的虚拟生成列(generated columns)的方式实现的。因此,可以在MySQL5.7上,模拟函数索引,通过显式的虚拟列。
这里可以做一个测试,先删除已经创建的索引。
mysql> SHOW CREATE TABLE products\G *************************** 1. row *************************** Table: products Create Table: CREATE TABLE `products` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `description` longtext, `price` decimal(8,2) DEFAULT NULL, `create_time` timestamp NULL DEFAULT NULL, PRIMARY KEY (`id`), KEY `create_time` (`create_time`), KEY `functional_index` ((month(`create_time`))) ) ENGINE=InnoDB AUTO_INCREMENT=149960 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
mysql> ALTER TABLE products DROP INDEX `create_time`, DROP INDEX `functional_index`; Query OK, 0 rows affected (0.03 sec)
现在再来创建虚拟的生成列:
mysql> ALTER TABLE products ADD COLUMN create_month TINYINT GENERATED ALWAYS AS (MONTH(create_time)) VIRTUAL; Query OK, 0 rows affected (0.04 sec)
在虚拟列上创建索引:
mysql> ALTER TABLE products ADD INDEX(create_month); Query OK, 0 rows affected (0.55 sec)
mysql> SHOW CREATE TABLE products\G *************************** 1. row *************************** Table: products Create Table: CREATE TABLE `products` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `description` longtext, `price` decimal(8,2) DEFAULT NULL, `create_time` timestamp NULL DEFAULT NULL, `create_month` tinyint GENERATED ALWAYS AS (month(`create_time`)) VIRTUAL, PRIMARY KEY (`id`), KEY `create_month` (`create_month`) ) ENGINE=InnoDB AUTO_INCREMENT=149960 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
执行之前的查询,看看是否效果一样
mysql> SELECT AVG(price) FROM products WHERE MONTH(create_time)=10; +------------+ | AVG(price) | +------------+ | 202.982582 | +------------+
mysql> EXPLAIN SELECT AVG(price) FROM products WHERE MONTH(create_time)=10\G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: products partitions: NULL type: ref possible_keys: create_month key: create_month key_len: 2 ref: const rows: 182 filtered: 100.00 Extra: NULL
一样的行为效果。
由于函数索引被实现为隐藏的虚拟列,因此数据不需要额外的空间,只会将索引空间添加到表中。
顺便说一句,这与用于在 JSON 文档的字段上创建索引的技术相同。