mysql两种特殊的索引结构全文索引和前缀索引

发表于： 2018年4月16日 2018年4月16日
分类： MySQL

1、全文索引
其它索引：昨天介绍的4种索引，都是把列的值作为索引。
全文索引：是把列里面的值进行分词，然后把每一个分词作为索引。
文章：标题，作者，内容，发布时间
内容从几千到几万字不等，存储内容需要使用大文本字段。
搜索内容里面含有某一个词的文章，曾经学习的搜索方式唯有使用like ‘%词%’进行匹配。
改善搜索的查询速度，需要对文章内容里面的每一个词，进行索引。让查询的时候可以使用到索引，提升查询的性能。
myisam引擎支持的全文索引，就可以完成这样的功能。该引擎支持英文，不支持中文！
英文分词：空格进行分词
添加全文索引：

alter table table_name add fulltext 取个名称(字段)

删除全文索引：

alter table table_name drop index 取的名称

搜索：

select * from table_name where match(’字段’) against(’搜索词’)

1）创建全文索引表：

create table `fulltext`(
    id int(11) unsigned not null auto_increment comment 'id',
    content text comment 'hello world',
    primary key(id)
)engine=myisam charset=utf8;

请记住，一定要启动mysql服务器，不然连接不上的！
启动：

/usr/local/mysql/support-files/mysql.server start

客户端：登录

/usr/local/mysql/bin/mysql -u root -p

2）插入数据：

INSERT INTO `fulltext` (`content`) VALUES 
('Great minds have purpose, others have wishes.'),
('Being single is better than being in an unfaithful relationship.'),
('If you find a path with no obstacles, it probably doesn’t lead anywhere.'),
('Getting out of bed in winter is one of life’s hardest mission.'),
('The future is scary but you can’t just run to the past cause it’s familiar.'),
('I love it when I catch you looking at me then you smile and look away.'),
('Having a calm smile to face with being disdained indicates kind of confidence.'),
('Success is the ability to go from one failure to another with no loss of enthusiasm.'),
('Not everything that is faced can be changed, but nothing can be changed until it is faced.'),
('A guy who whispers in your ears, saying ” It’s alright, I’m here.” Holds you when you’re sad, and treasures everything about you. That’s the guy I want to give my heart to.'),
('Dream what you want to dream; go where you want to go; be what you want to be, because you have only one life and one chance to do all the things you want to do.'),
('We all have moments of desperation. But if we can face them head on, that’s when we find out just how strong we really are.'),
('If they throw stones at you, don’t throw back, use them to build your own foundation instead.'),
('If your happiness depends on what somebody else does, I guess you do have a problem.'),
('When there’s no expectation, losing won’t bring hurt, gaining makes you surprised.'),
('Sometimes your plans don’t work out because God has better ones.'),
('How much truth of heart in one’s life is told in a joke?'),
('A relationship should be between two people, not the whole world.'),
('You can’t have a better tomorrow if you don’t stop thinking about yesterday.'),
('Today, give a stranger one of your smiles. It might be the only sunshine he sees all day.');

3）创建全文索引：

alter table `fulltext` add fulltext full(content);

进行搜索：

select * from `fulltext` where match(content) against('you');

搜索词：Dream

select * from `fulltext` where match(content) against('Dream');

删除全文索引：

alter table `fulltext` drop index full;

总结：
搜索不到的词，每篇文章中出现的机会都很多，搜索的时候就没有意义。分词就过滤掉了这些词，不对这些词建立索引。过滤词是全文索引定义的，可以不必在意这些。

搜索到的词，能够表达一些出现机会不多的含义，能够对文章进行区分。

中文文章内容搜索是可以实现的，需要使用中间件（第三方工具），来帮助mysql完成功能。

2、前缀索引
前缀索引：是把列前面的一部份值作为索引。

索引覆盖：是查询的返回值，都是建立索引的字段，就会使用索引覆盖的效果。
使用explain时，出现using index信息。

建立索引的数据是字段的前面一部分，查询返回值是需要一个完整的字段值。查询返回值是前缀索引时，为了返回一个完整的字段值，就必需要去数据区找到值。返回值包含前缀索引字段时，是没有办法使用索引覆盖的。

前缀索引制作的要求：
字段值前面的一部分，能够很好的代表整个字段的时候，才有必要制作前缀索引。

什么样的字段适合选为索引？

计算公式：字段不重复值的总数 / 字段值的总数 = 0 - 1
select count(distinct 字段值) / count(字段) = 0 -1

前缀索引的选择：

计算公式：字段前面的几个值不重复的总数 / 字段值的总数 = 0 - 1
select count(distinct left(字段值, number)) / 字段值的总数 = 0 - 1

number的确定：

前缀索引的选择性结果  === 索引的选择性结果

添加前缀索引：前缀索引很多时候都有重复的值，使用普通索引即可！

alter table table_name add key 取个索引名(字段(number))

number ：表示取字段值的前面多少个！是由前缀索引的选择性计算出来的值！
1）创建一个表：

create table prefix(
    id int(11) unsigned not null auto_increment comment 'id',
    name varchar(20) not null comment 'name',
    passwd char(32)  not null comment 'passwd',
    primary key(id)
)engine=innodb charset=utf8;

2）脚本介绍：

<?php

    $db = mysqli_connect('127.0.0.1', 'root', '123456', 'test');
    mysqli_query($db, "set names utf8");

    $sql = "insert into prefix (`name`, `passwd`) select concat('xiao', convert(round(rand() * 20000), char)) as name, 
            md5(
                substring('qwerop[]\|{PLKJH}asdfghjkl;:zxc567vbnm,./<>?`~1234890-=!@tyui#$%^&*()_+……%QWERTYUIOGFDSAZXCVBNM<>?:;[]F86892652jdmjwolbvxzm2', 
                    ceiling( rand() * 128), 
                    ceiling( rand() * 50 + rand() * 30 - 1)
                )
            ) as passwd";

    for($i = 0; $i < 4444; ++$i){
        mysqli_query($db, $sql);
    }

3）插入数据：
在站点根目录下运行这个PHP脚本
验证数据：

数据已经有了！
4）索引的选择性：

5）前缀索引的选择性：

6）添加前缀索引：

确定我们在passwd(6)；这个6是我们前缀索引的选择性出来的结果，与索引的选择性出来结果一样的时候，确定的那个值！
查看表的结构：

7）确定搜索的值：

8）使用值进行搜索：

explain select * from prefix where passwd='b0c23a206066a9390cd9f72a1401f4cf'\G

确定前缀索引的长度是：18
9）删除前缀索引：

查看表结构

10）创建普通索引：

查看表结构：

11）使用普通索引进行搜索：
使用普通索引进行搜索的SQL语句与前缀索引进行搜索的SQL语句保持一致！
explain select * from prefix where passwd=’b0c23a206066a9390cd9f72a1401f4cf’\G

12）结果总结：
使用前缀索引的长度是：18;
使用普通索引的长度是：96
前缀索引是普通索引的 5倍速！