全文索引
现有的数据库系统，绝大多数是以结构化数据检索的主要目标，因此实现相对简单。比如数值检索，可以建立一张排序好的索引表，这样速度可以得到提高。但对于非结构化数据，即全文数据，要想实现检索，一般都是采用模糊查询的方式实现的，这种方式不仅速度慢，而且容易将汉字错误切分，于是产生了全文检索技术。
全文检索技术是智能信息管理的关键技术之一，其主要目的就是实现对大容量的非结构化数据的快速查找。
全文检索的中文分词依赖系统词库，该词库是只读的，不允许修改。学过solr的都知道分词器(可以将一段内容分成多个短语)。
全文索引不能达到like的效果，因为会分词，所以连着的两个字不一定正好会分成一个词。
1. 索引的准则
1.在表中插入数据后创建索引
一般情况下，在插入或装载了数据后，为表创建索引会更加有效率，如果装载数据之前创建一个或多个索引，在插入每行时都必须更改和维护每个索引，会使得插入效率降低。
2.索引正确的表和列
使用下面的准则决定何时创建索引：
1. 如果需要经常地检索大表中的少量的行，就为查询键创建索引
2. 为了改善多个表的链接性能，可以为连接列创建索引
3. 主键和唯一键自动有索引，外键很多情况也会自动创建索引
4. 小表不需要索引
选取索引列时考虑几点：
1.列中的值相对比较唯一；
2. 取值范围大，适合建立索引
3. CLOB和TEXT只能建立全文索引，BLOB不能建立任何索引(适用于ORACLE和国产的达梦数据库)
3. 为性能而安排索引列
在create index 语句中列的排序会影响查询的性能。通常将最常用的列放在最前面。
如果多个字段组合定位，不要为每个字段都建立索引，考虑组合索引。当两个或多个字段都是等值查询时，组合索引中各个列的前后关系是无关紧要的；但如果是非等值查询，要想有效利用组合索引，则应该按照等值字段在前、非等值字段在后的原则创建组合索引。
4.限制索引数量
一个表可以有任意数量的所有。但是索引越多修改数据的开销就越大。当一个表主要用于读时，索引多就有好处。
2. mysql全文索引的用法
0. 简介
全文索引以词为基础的，MySQL默认的分词是所有非字母和数字的特殊符号都是分词符，与索引有关的几个变量如下：
mysql> SHOW VARIABLES LIKE 'ft%'; +--------------------------+----------------+ | Variable_name | Value | +--------------------------+----------------+ | ft_boolean_syntax | + -><()~*:""&| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit | 20 | | ft_stopword_file | (built-in) | +--------------------------+----------------+ 5 rows in set, 1 warning (0.08 sec)
ft_boolean_syntax：改变IN BOOLEAN MODE的查询字符，不用重新启动MySQL也不用重建索引
ft_max_word_len : #最长的索引字符串，默认值为84，修改后必须重建索引文件
ft_min_word_len : #最短的索引字符串，默认值为4，(通常改为1)修改后必须重建索引文件
ft_query_expansion_limit: #查询括展时取最相关的几个值用作二次查询
ft_stopword_file (built-in):#全文索引的过滤词文件
1. mysql中全文索引
1.建表的时候创建全文索引
CREATE TABLE article ( id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(200), body TEXT, FULLTEXT(title, body) );
mysql> show create table article\G *************************** 1. row *************************** Table: article Create Table: CREATE TABLE `article` ( `id` int(11) NOT NULL AUTO_INCREMENT, `title` varchar(200) DEFAULT NULL, `body` text, PRIMARY KEY (`id`), FULLTEXT KEY `title` (`title`,`body`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)
2. 查看索引
SHOW INDEX FROM article;
3. 删除索引
DROP INDEX title ON article;
4. 修改表结构创建索引
ALTER TABLE `article` ADD FULLTEXT title_f (title,body);
或者
ALTER TABLE `article` ADD FULLTEXT INDEX title_f (title,body);
5. 直接创建索引 (重要)
CREATE FULLTEXT INDEX title_f ON article (title, body)
补充：上面建的是联合索引，下面创建单列索引
CREATE FULLTEXT INDEX body_f ON article ( body)
6. 使用索引(如果对没有全文索引的列进行match会报错)
使用全文索引的格式： MATCH (columnName) AGAINST ('string')
例如:
查询包含精忠报国的数据：
SELECT * FROM article WHERE MATCH(body ) AGAINST('精忠报国');
7. 全文本布尔操作符
| 布尔操作符 | 说明 |
| + | 包含，词必须存在 |
| - | 排除，词必须不出现 |
| > | 包含，而且增加等级值 |
| < | 包含，且减少等级值 |
| () | 把词组成子表达式（允许这些子表达式作为一个组被包含、排除、排列等） |
| ~ | 取消一个词的牌谑值 |
| * | 词尾的通配符。这个只能放在词后面 |
| "" | 定义一个短语（与单个词的列表不一样，它匹配整个短语以便包含或排除这个短语） |
例如：
+:查询必须包含精忠报国的：
SELECT * FROM article WHERE MATCH(body ) AGAINST('+精忠报国' IN BOOLEAN MODE );
-:必须不包含精忠报国的：
SELECT * FROM article WHERE MATCH(body ) AGAINST('-精忠报国' IN BOOLEAN MODE );
空格:查询包含史无前例或者大义灭亲的
SELECT * FROM article WHERE MATCH(body ) AGAINST('史无前例 大义灭亲' IN BOOLEAN MODE );
>:查询包含精忠报国的，如果包含大义灭亲的往前排
SELECT * FROM article WHERE MATCH(body ) AGAINST('精忠报国 >大义灭亲' IN BOOLEAN MODE );
<:查询包含精忠报国的，如果包含大义灭亲的往后排
SELECT * FROM article WHERE MATCH(body ) AGAINST('精忠报国 <大义灭亲' IN BOOLEAN MODE );
*:查询以大义灭亲结尾的
SELECT * FROM article WHERE MATCH(body ) AGAINST('+精忠报国*' IN BOOLEAN MODE );
3. Oracle全文索引用法
主要介绍三种自然语言的分析器：
basic_lexer（默认的分析器）:主要针对英语。有较高的处理效率，因为它只认空格和标点，所以对于汉语没有空格的情况不会分词
chinese_vgram_lexer: 专用汉语分析器，支持所有汉字字符集，因为采用了分词的方法，可以查到所有的分词，效率差
chinese_lexer: 新汉语分析器，只支持utf8, 支持识别大部分的分词，但是会过滤掉很多无法识别的分词，因为这个原因也导致效率很高，如果数据库是zhs16gbk字符集，则只能使用Chinese vgram lexer
注意：追求效率那么使用CHINESE_LEXER,如果追求准确度那么使用CHINESE_VGRAM_LEXER（推荐使用chinese_vgram_lexer）
1. 创建文本解析器
BEGIN ctx_ddl.create_preference ('my_lexer', 'chinese_vgram_lexer'); END;
2. 删除文本解析器
BEGIN ctx_ddl.drop_preference ('my_lexer'); end;
3. 创建全文索引
CREATE INDEX prop_2_f ON 合同2(档案属性) indextype is ctxsys.context parameters('lexer my_lexer');
4. 使用全文索引(如果对没有全文索引的列进行contains函数会报错)
包含：
select id from 合同2 where contains(档案属性, '测试档案属性') > 0
不包含：
select id from 合同2 where not contains(档案属性, '测试档案属性') > 0
5. 删除全文索引
drop index prop_2_f
6. 优化全文索引
begin ctx_ddl.optimize_index('prop_2_f','full'); END;
4. SQLServer全文索引用法
1、全文索引可对char、varchar、nchar、nvarchar、text、ntext、image、xml、varbinary 或 varbinary(max) 类型字段进行检索，是解决海量数据模糊查询的好办法。
2、一个表只能建立一个全文索引（但可以对多个字段）。
3. 全文索引并不能达到like的效果，因为会进行分词，不一定连着的两个正好是一个词
4. 建立全文索引的表表中必须有一个唯一性索引，并不需要是主键(建议是主键)。
SQLServer中一个表只能有一个全文索引，索引的列可以是多列。一个全文索引目录可以对应多个表(建议最好是一对一，便于删除或者进行其他操作)。
1. 首先开启全文索引服务
检查服务里面带有Full-text字样的服务是否存在并开启,如果不存在带有Full-text字样的服务的，确认是否安装了sqlserverFullTextSearch
2. 新建全文目录
create fulltext catalog ftc_test1 with accent_sensitivity = off;
3.建立全文索引
(1)查看表的主键索引名称
select convert(varchar(100), CONSTRAINT_NAME) from INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), 'IsPrimaryKey') = 1 and table_name = 'test1';
结果:
PK_test1
(2)建立全文索引(创建全文索引的时候必须指定一个唯一索引，所以建议用主键索引)
create fulltext index on test1 key index [PK_test1] on ftc_test1 with (CHANGE_TRACKING AUTO)
(3)全文索引中添加列
alter fulltext index on test1 add ([name] LANGUAGE [Simplified Chinese])
(4)查看
可以通过可视化工具查看:
4. 使用全文索引-如果对未建立全文索引的列使用contains会报错
包含: 名字包含梦想的
select * from test1 where contains(name , '梦想')
内容包含什么和结局的:
select * from test1 where contains(content, '什么 and 结局')
内容包含什么或结局的:
select * from test1 where contains(content, '什么 or 结局')
带有张*词语的
select * from test1 where contains(name, '"张*"')
包含韩国和首都，且这两个词紧邻
select * from test1 where contains(content, '韩国 near 首都')
name包含什么或者content包含什么的:
select * from test1 where contains((name, content), '什么')
全部索引列中查询包含结局的
select * from test1 where contains(*, '结局')
不包含:
select * from test1 where not contains(name , '梦想')
更多的语法参考：https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008/ms187787(v=sql.100)
5. 索引优化
(1)查看全文索引目录
select name from SYS.fulltext_catalogs
结果:
ftc_test1
(2)重新优化
alter fulltext catalog [ftc_test1] reorganize
6. 删除索引以及相关目-删除的时候与增加的顺序相反。
(1)从全文索引中移除
alter fulltext index on test1 drop ([name])
(2) 删除全文索引
drop fulltext index on test1
(3) 删除表
drop table test1
(4) 删除全文索引目录
drop fulltext catalog ftc_test1
