mysql索引说明

前言

项目采用开源数据库mysql比较多，使用数据库，绕不开索引，这里简单对mysql索引做个说明。

索引类型

mysql 索引有五种：
主键索引、 唯一索引、普通索引 、全文索引、组合索引。
来看看各自特点：

主键索引(PRIMARY)

它 是一种特殊的唯一索引，不允许有空值。

唯一索引(UNIQUE)

与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。

普通索引(INDEX)

最基本的索引，没有任何限制。

全文索引(FULLTEXT )

用于在一篇文章中，检索文本信息的, 针对较大的数据，生成全文索引很耗时好空间。

FULLTEXT 用于搜索很长一篇文章的时候，效果最好，用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。

支持注意

MySQL 5.6 以前的版本，只有 MyISAM 存储引擎支持全文索引；
MySQL 5.6 及以后的版本，MyISAM 和 InnoDB 存储引擎均支持全文索引;
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。

使用注意

和常用的模糊匹配使用 like + % 不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如

select * from table_name 
    where match(content) against('xxx xxx');

查询长度注意

MySQL 中的全文索引，有两个变量，最小搜索长度和最大搜索长度，对于长度小于最小搜索长度和大于最大搜索长度的词语，都不会被索引。通俗点就是说，想对一个词语使用全文索引搜索，那么这个词语的长度必须在以上两个变量的区间内。

这两个的默认值可以使用以下命令查看

show variables like '%ft%';

可以看到这两个变量在 MyISAM 和 InnoDB 两种存储引擎下的变量名和默认值

// MyISAM
ft_min_word_len = 4;
ft_max_word_len = 84;

// InnoDB
innodb_ft_min_token_size = 3;
innodb_ft_max_token_size = 84;

可以看到最小搜索长度 MyISAM 引擎下默认是 4，InnoDB 引擎下是 3，也即，MySQL 的全文索引只会对长度大于等于 4 或者 3 的词语建立索引

配置注意

全文索引的相关参数都无法进行动态修改，必须通过修改 MySQL 的配置文件来完成。修改最小搜索长度的值为 1，首先打开 MySQL 的配置文件 /etc/my.cnf，在 [mysqld] 的下面追加以下内容

[mysqld]
innodb_ft_min_token_size = 1
ft_min_word_len = 1

然后重启 MySQL 服务器，并修复全文索引。注意，修改完参数以后，一定要修复下索引，不然参数不会生效。

两种修复方式：
1、命令修复

repair table test quick;

2、直接删掉重新建立索引

两种全文索引

自然语言的全文索引

默认情况下，或者使用 in natural language mode 修饰符时，match() 函数对文本集合执行自然语言搜索，上面的例子都是自然语言的全文索引。

自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里，相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语的在超过 50% 的记录中都出现了，那么自然语言的搜索将不会搜索这类词语。因此，表中必须有 4 条以上的记录。

这个机制也比较好理解，比如说，一个数据表存储的是一篇篇的文章，文章中的常见词、语气词等等，出现的肯定比较多，搜索这些词语就没什么意义了，需要搜索的是那些文章中有特殊意义的词，这样才能把文章区分开。

布尔全文索引

在布尔搜索中，我们可以在查询中自定义某个被搜索的词语的相关性，当编写一个布尔搜索查询时，可以通过一些前缀修饰符来定制搜索。

MySQL 内置的修饰符，上面查询最小搜索长度时，搜索结果 ft_boolean_syntax 变量的值就是内置的修饰符，下面简单解释几个，更多修饰符的作用可以查手册

+ 必须包含该词
- 必须不包含该词
> 提高该词的相关性，查询的结果靠前
< 降低该词的相关性，查询的结果靠后
(*)星号 通配符，只能接在词后面

组合索引

为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。
举个例子来说，比如某张表有a、b、c三个字段。

ALTER TABLE my_index_test_table ADD INDEX i_test (a(10),b,c);

好比创建了三个索引：
a,b,c
a,b
a
如果某个条件不包含a，那么不会走索引。

啥是回表

mysql其他索引存储的是主键id ，需要通过主键索引找到数据地址，即通过其他索引找到主键索引，再主键索引中拿到数据地址，通过数据地址访问数据的过程，叫回表。

行锁实现机制

mysql行锁是通过索引上的索引项加锁实现

一些情况表现

mysql在日志归档过程中可能造成服务抖动