在MySQL8.0数据库中，有五种索引：聚集索引(主键索引 PRIMARY)、普通索引(KEY)、唯一索引(UNIQUE) 、 空间索引(SPATIAL)以及我们这里将要介绍的全文索引(FULLTEXT INDEX)
主键索引 PRIMARY
primary key 有两个作用，一是约束作用（constraint），用来规范一个存储主键和唯一性，但同时也在此key上建立了一个主键索引； PRIMARY KEY 约束：唯一标识数据库表中的每条记录； 主键必须包含唯一的值； 主键列不能包含 NULL 值； 每个表都应该有一个主键，并且每个表只能有一个主键。（PRIMARY KEY 拥有自动定义的 UNIQUE 约束）
普通索引 KEY
key 是数据库的物理结构，它包含两层意义和作用， 一是约束（偏重于约束和规范数据库的结构完整性）， 二是索引（辅助查询用的）。
唯一索引 UNIQUE
unique key 也有两个作用，一是约束作用（constraint），规范数据的唯一性，但同时也在这个key上建立了一个唯一索引； UNIQUE 约束：唯一标识数据库表中的每条记录。 UNIQUE 和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证。 （每个表可以有多个 UNIQUE 约束，但是每个表只能有一个 PRIMARY KEY 约束） foreign key也有两个作用，一是约束作用（constraint），规范数据的引用完整性，但同时也在这个key上建立了一个index；
空间索引 SPATIAL
OGC 发布了OpenGIS® Implementation Standard for Geographic information - Simple feature access - Part 2: SQL option ， 这个文档建议了几种方法扩展RDBMS来支持空间数据。 文档查看： http://www.opengeospatial.org/standards/sfs. 遵循OGC文档，MySQL以 SQL with Geometry Types（几何类型SQL）环境的一个子集实现了空间扩展。这个环境是指SQL环境扩展的一系列几何类型。 一个 geometry-valued（几何-值）列由一个带有几何类型的列实现。规格文档描述了一系列的SQL 几何类型，以及用于创建，分析几何数值的方法。 在最新发布的MySQL 5.7.4实验室版本中，InnoDB存储引擎新增了对于几何数据空间索引的支持。 在此之前，InnoDB将几何数据存储为BLOB（二进制大对象）数据，在空间数据上只能创建前缀索引，当涉及空间搜索时非常低效，尤其是在涉及复杂的几何数据时。 在大多数情况下，获得结果的唯一方式是扫描表。 新版本MySQL中，InnoDB支持空间索引，通过R树来实现，使得空间搜索变得高效。 InnoDB空间索引也支持MyISAM引擎现有的空间索引的语法，此外，InnoDB空间索引支持完整的事务特性以及隔离级别。 目前，InnoDB空间索引只支持两个维度的数据，MySQL开发团队表示有计划支持多维。此外，开发团队正在做更多关于性能方面的工作，以使其更加高效。 CREATE TABLE tb_geo( id INT PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(128) NOT NULL, pnt POINT NOT NULL, SPATIAL INDEXspatIdx(pnt) )ENGINE=MYISAM DEFAULT CHARSET=utf8;
全文索引 FULLTEXT
在MySQL 5.7.6中，MySQL提供了支持中文、日文和韩文（CJK）的内置全文ngram解析器，以及用于日文的可安装MeCab全文解析器插件
全文索引只能用于InnoDB或MyISAM表，只能为CHAR、VARCHAR或文本列创建 使用全文索引需要注意的是：(基本单位是词) 分词，全文索引以词为基础的，MySQL默认的分词是所有非字母和数字的特殊符号都是分词符,需要其他解析器支持
MySQL中与全文索引相关的几个变量 mysql> SHOW VARIABLES LIKE 'ft%'; #ft就是FullText的简写 ft_boolean_syntax + -><()~*:""&| #改变IN BOOLEAN MODE的查询字符，不用重新启动MySQL也不用重建索引 ft_min_word_len 4 #最短的索引字符串，默认值为4，(通常改为1)修改后必须重建索引文件。重新建立索引命令：repair table tablename quick ft_max_word_len 84 #最长的索引字符串，默认值为84，修改后必须重建索引文件 ft_query_expansion_limit 20 #查询括展时取最相关的几个值用作二次查询 ft_stopword_file (built-in) #全文索引的过滤词文件，具体可以参考：MySQL全文检索中不进行全文索引默认过滤词
ft_boolean_syntax (+ -><()~*:”“&|)使用的例子 + : 用在词的前面，表示一定要包含该词，并且必须在开始位置 eg: +aaa - : 不包含该词，所以不能只用「-yoursql」这样是查不到任何row的，必须搭配其他语法使用 eg: -aaa : 空(也就是默认情况)，表示可选的，包含该词的顺序较高 > :提高该字的相关性，查询的结果会排在比较靠前的位置 < :降低相关性，查询的结果会排在比较靠后的位置 可以通过括号来使用字条件 eg: +aaa +(>bbb)
全文检索按分值排序(默认)
select id,name,author,release_at,info,cover_url,
MATCH (name,detail) AGAINST ('一拳超人') as score
from `tbl_news_info` where `status` = "1" AND
match(name,detail) AGAINST ("一拳超人")
#order by `id` DESC ,`score` DESC
limit 10至少使用两个字段建立联合索引
中文必须使用 ngram 解释器
//创建全文索引 至少使用两个字段建立联合索引
CREATE TABLE articles (
id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR (200),
body TEXT,
FULLTEXT (title, body) WITH PARSER ngram
) ENGINE = INNODB DEFAULT CHARSET=utf8mb4 COMMENT='文章表';
//使用alter语句建立索引
ALTER TABLE articles ADD FULLTEXT INDEX title_body_index (title,body) WITH PARSER ngram;
ALTER TABLE tbl_video_info ADD FULLTEXT INDEX name_author_actor (name,author,actor) WITH PARSER ngram;
//加入数据
INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase …'),
('How To Use MySQL Well','After you went through a …'),
('Optimizing MySQL','In this tutorial we will show …'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. …'),
('MySQL vs. YourSQL','In the following database comparison …'),
('MySQL Security','When configured properly, MySQL …');
//查询模式一共有四种:
search_modifier:
{
| IN NATURAL LANGUAGE MODE
| IN BOOLEAN MODE
| IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION
| WITH QUERY EXPANSION
}
1.自然语言全文(默认) 自然语言搜索将搜索字符串解释为自然语言中短语。
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' IN NATURAL LANGUAGE MODE);
2.布尔全文索引
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('+MySQL -YourSQL' IN BOOLEAN MODE);
select * from test where match (name,brand,en) against ('北京');
3.扩展查询
//当搜索短语太短时，这通常很有用，这通常意味着用户依赖于全文搜索引擎缺乏的隐含知识。例如，搜索“database”的用户可能真的意味着“MySQL”、“Oracle”、“DB2”和“RDBMS”都是应该与“database”匹配并且也应该返回的短语。
SELECT * FROM articles WHERE MATCH (title,body) AGAINST ('database' WITH QUERY EXPANSION); 算法
BTREE BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中，相信学过数据结构的童鞋都对当初学习二叉树这种数据结构的经历记忆犹新，反正愚安我当时为了软考可是被这玩意儿好好地折腾了一番，不过那次考试好像没怎么考这个。如二叉树一样，每次查询都是从树的入口root开始，依次遍历node，获取leaf。 Hash 利用这一列或几列的值通过一定的算法计算出一个hash值，对应一行或几行数据 1.Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。 由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。 2.Hash 索引无法被用来避免数据的排序操作。 由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算； 3.Hash 索引不能利用部分索引键查询。 对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。 4.Hash 索引在任何时候都不能避免表扫描。 前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。 5.Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。 对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。 RTREE RTREE在mysql很少使用，仅支持geometry数据类型，支持该类型的存储引擎只有MyISAM、BDb、InnoDb、NDb、Archive几种。 相对于BTREE，RTREE的优势在于范围查找。 R树是B树在高维空间的扩展，是一棵平衡树。每个R树的叶子结点包含了多个指向不同数据的指针，这些数据可以是存放在硬盘中的，也可以是存在内存中。 根据R树的这种数据结构，当我们需要进行一个高维空间查询时，我们只需要遍历少数几个叶子结点所包含的指针（即缩小到某个区域下去进行查询，还是采用缩小范围的思想），查看这些指针指向的数据是否满足要求即可。这种方式使我们不必遍历所有数据即可获得答案，效率显著提高。下图1是R树的一个简单实例：
以餐厅为例，假设我要查询广州市天河区天河城附近一公里的所有餐厅地址怎么办？ 打开地图（也就是整个R树），先选择国内还是国外（也就是根结点）。 然后选择华南地区（对应第一层结点），选择广州市（对应第二层结点）， 再选择天河区（对应第三层结点）， 最后选择天河城所在的那个区域（对应叶子结点，存放有最小矩形），遍历所有在此区域内的结点，看是否满足我们的要求即可。 R树的查找规则跟查地图很像吧？对应下图：
多个单列索引和联合索引的区别详解
联合索引
我们为userId, mobile, billMonth三个字段添加上联合索引！
我们选择 explain 查看执行计划来观察索引利用情况：
1.查询条件为 userid 可以通过key看到，联合索引有效 2.查询条件为 mobile 可以看到联合索引无效 3.查询条件为 billMonth 联合索引无效 4.查询条件为 userid and mobile 联合索引有效 5.查询条件为 mobile and userid 在4的基础上调换了查询条件的顺序，发现联合索引依旧有效 6.查询条件为 userid or mobile 把 and 换成 or，发现联合所索引无效！ 7.查询条件为 userid and billMonth 这两个条件分别位于联合索引位置的第一和第三，测试联合索引依旧有效！ 8.查询条件为 mobile and billMonth 这两个条件分别位于联合索引位置的第二和第三，发现联合索引无效！ 9.查询条件为 userid and mobile and billMonth 所有条件一起查询，联合索引有效！
联合索引本质： 当创建(a,b,c)联合索引时，相当于创建了(a)单列索引，(a,b)联合索引以及(a,b,c)联合索引 想要索引生效的话,只能使用a和a,b和a,b,c三种组合；当然，我们上面测试过，a,c组合也可以，但实际上只用到了a的索引，c并没有用到！
单列索引
1.查询条件为 userid and mobile and billMonth 我们发现三个单列索引只有 userid 有效（位置为查询条件第一个），其他两个都没有用上。 那么为什么没有用上呢？按照我们的理解，三个字段都加索引了，无论怎么排列组合查询，应该都能利用到这三个索引才对！ 其实这里其实涉及到了mysql优化器的优化策略！当多条件联合查询时，优化器会评估用哪个条件的索引效率最高！ 它会选择最佳的索引去使用，也就是说，此处userid 、mobile 、billMonth这三个索引列都能用， 只不过优化器判断只需要使用userid这一个索引就能完成本次查询，故最终explain展示的key为userid。 当然，如果优化器判断本次查询非要全使用三个索引才能效率最高，那么explain的key就会是userid 、mobile 、billMonth，都会生效！ 2.查询条件为 mobile and billMonth 我们发现此处两个查询条件只有 mobile 生效（位置也为查询条件第一个） 3.查询条件为 userid or mobile 这次把 and 换成 or，发现两个查询条件都用上索引了！
多个单列索引在多条件查询时优化器会选择最优索引策略，可能只用一个索引，也可能将多个索引全用上！ 但多个单列索引底层会建立多个B+索引树，比较占用空间，也会浪费一定搜索效率，故如果只有多条件联合查询时最好建联合索引！
