达梦全文索引

原创行之 2025-01-19

1211

达梦全文索引

全文索引必须在基表上定义，而不能在系统表、视图、临时表、列存储表、外部表上定义。
同一列只能创建一个全文索引
全文索引列的类型可为 CHAR、CHARACTER、VARCHAR、VARCHAR2、LONGVARCHAR、TEXT 或 CLOB
全文索引现只支持简体中文、英文语言
基表数据变化，需更新全文索引。
创建全文索引会新增四个辅助表，辅助表在user_tables视图下看不到，但能直接查询数据。如索引名INDEX_NAME，则四个辅助表为：
- CTI$INDEX_NAME$I，用于保存分词结果，I表会比较大。
- CTI$INDEX_NAME$P，用于保存基表发生数据变化的ROWID及操作类型，及用户自定义聚集索引
- CTI$INDEX_NAME$N，用于保存基表ROWID和分词docid 的映射关系，及用户自定义聚集索引
- CTI$INDEX_NAME$D，记录将被删除的 docid

一创建全文索引

CREATE CONTEXT INDEX cti_address ON person.address (address1) LEXER DEFAULT_LEXER SYNC [TRANSACTION];
-- SYNC 子句为可选参数，默认创建全文索引不会更新分词数据，全文索引创建后不可直接使用。
-- SYNC参数，在创建全文索引时执行一次全量更新，创建后即可使用全文检索
-- SYNC TRANSACTION 参数，在创建全文索引时执行一次全量更新，且每次事务提交都增量更新全文索引。

关键字 LEXER 指定分词算法：

CHINESE_LEXER，中文最少分词，
CHINESE_VGRAM_LEXER，中文机械双字分词；
CHINESE_FP_LEXER，中文最多分词，中文场景只有这个能用，推荐！！！
ENGLISH_LEXER，英文分词；
DEFAULT_LEXER，默认分词，为中文最少分词。

其中，中文分词可以切分英文，英文分词不能切分中文，所以中英混合列推荐中文分词。

中文分词依赖系统词库，该词库是只读的，不允许修改。

二删除全文索引

DROP CONTEXT INDEX cti_address ON person.address;

三更新全文索引

全文索引本质是借助辅助表存储索引数据。基表数据发生变化，需要用户主动同步数据以更新全文索引，或设置服务器自动更新

全文索引的更新包括两种方式：完全更新和增量更新。

判断是否需要更新

select count(1) from CTI$CTI_ADDRESS$I; -- 记录数大于0，说明已有分词信息。
SELECT COUNT(1) FROM CTI$CTI_ADDRESS$P; -- 记录数大于0，说明有数据更新，需要更新分词信息，更新后会重新变为0.
-- CTI_ADDRESS 替换为自己创建的全文索引名

完全更新
```
  ALTER CONTEXT INDEX cti_address ON person.address REBUILD;
```
- 完全更新会删除原有全文索引，重新扫描索引列并分词,代价较大，耗时较久。
- 完全更新可以修改分词算法；
- 增量更新失败可考虑完全更新。
增量更新,推荐！！！
```
  ALTER CONTEXT INDEX cti_address ON person.address INCREMENT;
```
- 增量更新只对新发生改变的数据进行重新分词并生成索引信息，代价较小，耗时较短。
- 增量更新失败，会回滚到之前的状态。
DMDSC 环境下更新全文索引前，需要手动将 dmdbms/bin/SYSWORD.UTF8.LIB 文件复制到库所在的 DMASM 路径下，否则会导致全文索引词库加载错误。

四进行全文检索

如：
SELECT * FROM person.address WHERE CONTAINS(address1, '江汉区发展大道');
SELECT * FROM person.address WHERE CONTAINS(address1, '洪山区' AND '太阳城');
SELECT * FROM person.address WHERE CONTAINS(address1, '洪山区' OR '太阳城');
SELECT * FROM person.address WHERE CONTAINS(address1, '洪山区' AND NOT '太阳城');
SELECT * FROM person.address WHERE CONTAINS(address1, '洪山区') AND city LIKE '%武汉市%';

五测试验证

1.创建测试表

create user cym identified by "Test_1234";
grant resource,public,soi,svi to cym;
conn cym/Test_1234
create table fulltext (id int IDENTITY(1,1) cluster primary key,name varchar(20),birth datetime(0),address varchar(100));

使用SQLark 生成 10w条数据

2.创建全文索引

drop context index cti_address on fulltext; -- 删除全文索引
CREATE CONTEXT INDEX cti_address ON fulltext (address) LEXER DEFAULT_LEXER;

3.检查

SQL> select table_name from user_tables;  -- user_tables 没看到辅助表
TABLE_NAME
----------
FULLTEXT
SQL> select * from ctisys.syscontextindexes;    -- ctisys.syscontextindexes 能查到已有的全文索引，其中 WSEG_TYPE 表示分词算法
NAME        ID          TABLEID     COLID       UPD_TIMESTAMP              TIID        TDID        TPID        TNID        TRID        WSEG_TYPE   RESVD
----------- ----------- ----------- ----------- -------------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------
CTI_ADDRESS 33555530    1068        3           2025-01-19 17:25:10.586854 1073        1075        1074        1076        NULL        0           NULL

SQL> select table_name,index_name,index_type,status from user_indexes order by table_name;  -- user_indexes 看到已有辅助表，全文索引类型为domain
TABLE_NAME        INDEX_NAME        INDEX_TYPE STATUS
----------------- ----------------- ---------- ------
CTI$CTI_ADDRESS$D INDEX33555533     CLUSTER    VALID
CTI$CTI_ADDRESS$I INDEX33555531     CLUSTER    VALID
CTI$CTI_ADDRESS$N INDEX33555534     CLUSTER    VALID
CTI$CTI_ADDRESS$N IDX$CTI_ADDRESS$N NORMAL     VALID
CTI$CTI_ADDRESS$P INDEX33555532     CLUSTER    VALID
FULLTEXT          CTI_ADDRESS       DOMAIN     VALID
FULLTEXT          INDEX33555523     CLUSTER    VALID

SQL> select count(1) from CTI$CTI_ADDRESS$I;    -- 分词信息，0条
COUNT(1)
--------------------
0

SQL> select * from fulltext where contains(address,'张家界');  -- 创建全文索引不能直接使用，需先填充。
未选定行
已用时间: 1.117(毫秒). 执行号:58810.

4.更新全文索引并检索

------------- 1.全量更新分词信息 -------------------
SQL> ALTER CONTEXT INDEX cti_address ON fulltext REBUILD;
SQL> select count(1) from CTI$CTI_ADDRESS$I;
COUNT(1)
--------------------
17034              -- 已经有分词信息

-------------- 2.进行全文检索 ---------------------
SQL> select COUNT(1) from fulltext where address LIKE '%张家界%';
COUNT(1)
--------------------
194
已用时间: 26.986(毫秒). 执行号:58839.     --作为对比，关键字匹配到 194条记录，耗时27ms

SQL> select * from fulltext where contains(address,'张家界');
未选定行
SQL> select * from fulltext where contains(address,'张家');   
ID          NAME   BIRTH                      ADDRESS
----------- ------ -------------------------- ------------------------------
8632        周曼婷 2013-08-01 05:36:43.000000 山东省淄博市张店区洪家楼64号
18567       柴诗涵 2003-11-24 06:02:11.000000 湖北省宜昌市伍家岗区张庄路77号
81334       汪智瑄 2018-11-17 17:50:32.000000 山东省淄博市张店区姚家76号

SQL> select * from fulltext where contains(address,'山东'); 
未选定行

默认分词算法完全不能用！！！

5.更换分词算法为最多分词

SQL> drop context index cti_address on fulltext;
SQL> CREATE CONTEXT INDEX cti_address ON fulltext (address) LEXER CHINESE_FP_LEXER SYNC; -- 创建全文索引的同时，更新分词信息。
SQL> select count(1) from CTI$CTI_ADDRESS$I;
COUNT(1)
--------------------
31196
SQL> select * from fulltext where contains(address,'张家界');
ID          NAME   BIRTH                      ADDRESS
----------- ------ -------------------------- --------------------------------
1           项欣怡 2015-06-17 16:26:43.000000 湖南省张家界市慈利县舜耕61号
546         甄向欢 1977-12-16 14:48:48.000000 湖南省张家界市慈利县中大槐树15号
916         胡永   2011-08-19 10:51:45.000000 湖南省张家界市永定区遥墙63号
1091        尹麟超 2015-12-22 08:23:43.000000 湖南省张家界市武陵源区七里山87号
...
194 rows got

SQL> select COUNT(1) from fulltext where contains(address,'张家界');
COUNT(1)
--------------------
194
已用时间: 2.228(毫秒). 执行号:58838.         -- 194条数据，检索结果符合预期。

SQL> select count(1) from fulltext where contains(address,'张家界' AND '大槐树');
COUNT(1)
--------------------
3
已用时间: 14.396(毫秒). 执行号:58844.

SQL> select count(1) from fulltext where contains(address,'张家界' AND NOT '大槐树');
COUNT(1)
--------------------
191     -- 这两个AND 和 AND NOT 之和是194，符合预期。
已用时间: 13.782(毫秒). 执行号:58845.

最多分词算法效果完美，效率比模糊匹配也快很多, 但注意条件越多耗时越久。

6.记录删除

SQL> select count(1) from fulltext where contains(address,'四川省');   --2897
SQL> DELETE FROM fulltext WHERE contains(address,'四川省');
影响行数 2897
SQL> select count(1) from fulltext where contains(address,'四川省');
COUNT(1)
--------------------
0

记录删除操作不需要更新全文索引。

7.记录新增

SQL> INSERT INTO fulltext(name,birth,address) VALUES('陈年酒诗',now(),'陈年酒诗');
SQL> INSERT INTO fulltext(name,birth,address) VALUES('陈年酒诗',now(),'陈年酒诗');
SQL> SELECT COUNT(1) FROM CTI$CTI_ADDRESS$P;                -- 2
SQL> SELECT * FROM FULLTEXT WHERE CONTAINS(ADDRESS,'陈年');  -- 0
SQL> ALTER CONTEXT INDEX ON FULLTEXT INCREMENT;             -- 增量刷新
SQL> SELECT * FROM FULLTEXT WHERE CONTAINS(ADDRESS,'陈年');
ID          NAME     BIRTH                      ADDRESS
----------- -------- -------------------------- --------
100002      陈年酒诗 2025-01-19 19:33:03.341247 陈年酒诗
100003      陈年酒诗 2025-01-19 19:41:11.213052 陈年酒诗

已用时间: 0.447(毫秒). 执行号:82614.
SQL> SELECT * FROM FULLTEXT WHERE ID IN (100002,100003);
ID          NAME     BIRTH                      ADDRESS
----------- -------- -------------------------- --------
100002      陈年酒诗 2025-01-19 19:33:03.341247 陈年酒诗
100003      陈年酒诗 2025-01-19 19:41:11.213052 陈年酒诗

已用时间: 0.366(毫秒). 执行号:82615.

记录新增需要更新全文索引。

8.占用空间对比

SQL> SELECT USER_USED_SPACE('CYM');
行号     USER_USED_SPACE('CYM')
---------- -----------------------
1          1120

SQL> DROP CONTEXT INDEX CTI_ADDRESS ON CYM.FULLTEXT;
操作已执行

SQL> SELECT USER_USED_SPACE('CYM');
行号     USER_USED_SPACE('CYM')
---------- -----------------------
1          256

全文索引占用空间较大，是一种以空间换时间的SQL优化方案。

参考

墨力计划达梦

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

达梦 全文索引

达梦 全文索引

一 创建全文索引

二 删除全文索引

三 更新全文索引

四 进行全文检索

1.创建测试表

2.创建全文索引

4.更新全文索引并检索

5.更换分词算法为最多分词

6.记录删除

7.记录新增

8.占用空间对比

参考

评论

达梦全文索引

达梦全文索引

一创建全文索引

二删除全文索引

三更新全文索引

四进行全文检索