MySQL的函数索引
2023-09-05 10:05 abce 阅读(126) 评论(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子句中使用表达式的查询创建功能索引之前,有一些事情需要注意。添加功能索引时,会创建一个隐藏的虚拟列。因此,会产生以下影响:
·生成的列会计入允许的表的列数量限制。
·函数索引的表达式中只能使用允许生成列的函数。
·函数索引中不允许使用以下函数:
--子查询
--参数
--变量
--存储函数
--可加载函数
·虚拟生成列本身不需要存储空间。索引本身与其他索引一样占用存储空间。