MySQL第42课~不同类型的索引，前缀，全文和复合

所有内容收录在合集~MySQL入门到熟练。欢迎点赞关注我哦~

前缀索引 | Prefix Indexes

如果创建的索引列是一个字符串列，索引可能会占据很大的空间，效果也不理想。

这个时候可以使用前缀，类似缩写，只取前几个字母

指定类型的时候，索引中括号里包含的字符数，在VARCHAR ,CHAR是可选的，另外是必须写的。

create index idx_firstname on sakila.actor(first_name (3))

比如我随机设置这样的一个索引，这里我不关心最合适的字符是多少，我只想设置一个数值，然后我会发现这个数字其实不能很好进行识别，绝对会出现问题的那种，三个字母的重复率不是一般的高，这个时候我可以调整数值，找到最佳的数值长度。

第一步可以先找到表中有多少条记录

select count（*）from sakila.actor;

第二步进行尝试，看看有多少唯一值，从一个字母开始

select count（left（first_name,1)）from sakila.actor;

同时要避免重复值的出现，添加关键字

select count（DISTINCT left（last_name,1)）from sakila.actor;

执行得出23个

最后最大化索引中唯一值的数量，就是用不同的数据进行对比

得出结论，7是最佳前缀长度。

全文索引 | Full-text Indexes

用来制作快速有效的搜索引擎。

假设有一天，你的公司要建立一个社区什么的。社区就是有各种文案和活动，

比如有什么美妆，母婴，Java等，表格里有文章，id，标题，发布日期

再次提醒，%代表任意数量的字符。

那可以先这样写，这个时候的问题是没有索引，会导致文章表越来越大，查询会越来越慢

而且返回查找的内容必须是两个单词都有，还得按顺序连在一起，那有什么用呢？

说白了，写了也是白写，这个时候试试全文索引，包含所有的字符串列，而不是只存储前缀。同时自动忽视停止词，in ，on，the，既存储了词表，同时也存储了单词会出现的行或者记录。

先写全文索引，索引列名可以对应index后面的词，然后进行查询，使用内置函数支持全文索引。

MATCH，里面添加标题和正文，就是需要索引的列都添加在里面

AGAINST，里面添加搜索的字符串，这里面的单词既可以按顺序排列，也可以被一个多个单词分离。

并且搜索具有相关性，MySQL会自动计算相关性得分，介于0到1之间的浮点数，0表示没有相关性，可以直接进行计算展示

单独执行这一句，然后可以看到这些数据，按照从大到小排列

以上是自然语言模式，是默认情况下自动进行的，另一种是布尔模式，可以排除一些单词。

在要排除的单词加负号，输入布尔模式即可

反过来，添加加号，代表必须有这个单词，且不受顺序影响

复合索引 | Composite Indexes

在这个表中，有一个主索引和3个二级索引，

如果同时使用2个索引条件，可以看到前面的possible key 是2个索引，而后面的key只用了一个，也就是说，其实不管有多少，都只会使用一个，也就是说先用索引找到目标地的顾客，然后表扫描查看积分进行筛选

现在试试用复合索引建立合适的索引，优化查询

先写再查询，这里可以看到复合查询，以及查了1行

当然，索引也是可以直接删除的，我更喜欢直接右键一下，如果写出来是这样

复合索引中的列顺序 | Order of Columns in Composite Indexes

基本规则

1 对列进行排序，越频繁使用的越向前，缩小搜索范围

2 基数更高的列更向前，基数指索引中唯一值的数量

比如主键中有10个唯一值，比如如果用性别设置，就可以减少一半的搜索范围。用地域进行搜，也可以减少很多

对应实际的操作，可以先用count函数统计，然后进行排序

从这个数据看，应该使用的是姓氏

但实际操作中，姓氏的重名率远高于地域

调换顺序进行对比

在from后面，where前面，可以添加其他索引

总结：

1 在搜狗，谷歌，百度这些地方，文章，论文，等全文索引

2 前缀索引，适合名字，地址

3 复合索引，一般建议用这个，减少单独索引，避免占用空间

MySQL会自动将表的主键包含在每个二级索引中，占用时间和空间

一个索引最多可以占用16列，4-6之间就可以展示好的性能，根据不同的情况可以多多试验。