函数索引

发表于 | 分类于 ， ， |
字数统计: | 阅读时长 ≈

函数索引

话不多说，先上官方文档链接 创建索引

背景

在做名字匹配的时候，考虑到内部数据有很多大小写的问题(比如JAVA java;mysql MYSQL MySQL Mysql）。数据库中的数据量比较多如果直接获取所有数据生成小写名字对应的map会造成需要大量的内存，所以决定采用的方式是先获取所有选择同步的名字，小写后去数据库进行匹配，但是数据库中的名字并不完全是小写的，在匹配的时候需要用到LOWER函数，使用LOWER函数的时候，就会因为修改了原数据的匹配规则导致的索引失效(可以直接链接访问)。

这种业务场景之前的办法是再维护一个列用于查询或者直接上ES(ElasticSearch)进行查询，但是前者需要的是

翻阅官网发现存在函数索引，于是就是函数索引的使用避坑的路线。

版本要求

mysql的版本要达到8.0.13及以上

使用方式

1
CREATE INDEX {索引名称} ON {数据表名称} ({方法函数})

这里需要注意

  1. 建立索引的时候其中的方法函数不能随意添加

    1
    CREATE INDEX idx2 ON t1 ((col1 + col2), (col1 - col2), col1);

    拿这个举例子

    其中的col1+col2是一个函数，所以必须添加()，但是后面的col1不是，所以col1不能添加()如果添加就会报错。

    根据官方的说法，()里面的就代表功能项，功能项不能由列名组成

    1
    2
    # 错误的写法
    INDEX ((col1), (col2))

  2. 如果使用到了函数部分，不能少了()

    也就是说，如果要使用函数，就必须添加（）

    1
    2
    3
    4
    5
    6
    # 错误的写法
    INDEX (col1 + col2, col3 - col4)
    INDEX (LOWER(name))
    # 正确的写法
    INDEX ((col1 + col2), (col3 - col4)
    INDEX ((LOWER(name))

    这里个坑其实很容易出现，确实也比较恼人，尤其是LOWER的部分，函数索引内使用函数必须带上（）保证处于功能项，估计也是为了区别最外层的括号采用的最简单的策略。

个人使用过程记录

这里再阐述一下我的解决方式，因为需要做名字匹配，并且匹配中有大小写的问题，所以在处理的过程中采用的是都统一采用小写进行匹配的方式。

首先拿到所有有关的名字的小写数据，批量查询数据库中对应的字段

1
2
3
4
5
6
7
8
9
10
11
select * from ... where LOWER(name) = "对应的名字"
# 实际的代码 xml
select * from ... 
<where>
	<if test = "nameList != null and nameList.size > 0">
		`name` IN 
		<foreach collection="nameList" index="name" item="item" open="(" separator="," close=")">
            #{name}
        </foreach>
	</if>
</where>

对应的数据库也要添加对应的索引

添加过程如下，刚开始没看完整

addFailed

添加括号后，成功创建

sql

本地测试能否走索引

ifIndex

成功走索引，完成！