MySQL的函数索引

2023-09-05 10:05 abce 阅读(139) 评论(0) 编辑收藏举报

MySQL 8.0.13引入了函数索引。

测试版本：

>select version();
+-----------+
| version() |
+-----------+
| 8.0.30-22 |
+-----------+

测试表：

root@localhost test>show create table myabc\G
*************************** 1. row ***************************
       Table: myabc
Create Table: CREATE TABLE `myabc` (
  `id` int NOT NULL AUTO_INCREMENT,
  `col1` int DEFAULT NULL,
  `col2` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2001 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.00 sec)
 
root@localhost test>select count(*) from myabc;
+----------+
| count(*) |
+----------+
|     2000 |
+----------+
1 row in set (0.00 sec)

如果，想查看哪些行的col1+col2的和是10，可以这样查询：

root@localhost test>select count(*) from myabc where col1 + col2 = 10;
+----------+
| count(*) |
+----------+
|      177 |
+----------+
1 row in set (0.00 sec)

从执行计划可以看到是走的全表扫描：

root@localhost test>explain select count(*) from myabc where col1 + col2 = 10;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | myabc | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 2000 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+

这里的执行效率还是可以接受的，毕竟记录数比较少，但是如果表中还有大量的记录数呢？这就可以考虑函数索引了。

root@localhost test>alter table myabc add index idx_sum((col1+col2));
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
root@localhost test>explain select count(*) from myabc where col1 + col2 = 10;
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | myabc | NULL       | ref  | idx_sum       | idx_sum | 9       | const |  177 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+---------+---------+-------+------+----------+-------+

函数索引可以提高查询性能，而无需重写查询来解决任何瓶颈问题。不过，我们需要遵循一些规则。
1.表达式必须用口号括起来。以与列区分
·INDEX((col1 + col2)) 与 INDEX( col1, col2)
·也可以创建一个包含了函数定义和非函数定义的函数索引：INDEX((col1 + col2), col1)

2.函数索引定义，不能只包含列名字，比如INDEX ((col1), (col2))会报错

3.不能在外键列上定义函数索引

4.只有查询使用了相同的表达式，才会使用到函数索引

函数索引的限制
在我们为所有在WHERE子句中使用表达式的查询创建功能索引之前，有一些事情需要注意。添加功能索引时，会创建一个隐藏的虚拟列。因此，会产生以下影响：
·生成的列会计入允许的表的列数量限制。
·函数索引的表达式中只能使用允许生成列的函数。
·函数索引中不允许使用以下函数：
　　--子查询
　　--参数
　　--变量
　　--存储函数
　　--可加载函数
·虚拟生成列本身不需要存储空间。索引本身与其他索引一样占用存储空间。