您当前的位置：首页 > 计算机 > 软件应用 > 数据库 > MySQL

MySQL索引数据结构底层（全）

时间：09-14来源：作者：点击数：

读者忠告

由于本文内容篇幅较长，涵盖了大家学习上、工作上的绝大多数索引，建议大家每一小节都认真阅读并理解透彻，如有疑问可在评论区留言探讨；

文章目录

读者忠告
二、索引
三、索引的类型
四、总结
- 好了，本篇就说到这里了，看完觉得有帮助的童鞋记得点赞！点赞！点赞！（重要的事情说三遍！）

二、索引

2.1 索引概述

索引是帮助数据库快速查询数据的一种数据结构，在数据库中，数据库系统除了存储数据之外还维护着一种特殊的数据结构，这种数据结构以某种方式指向数据，这样就可以在这种数据结构上实现高级算法来查询数据，这种数据结构就是索引。

索引示意图：

如图所示，索引能够帮我们快速的定位到数据的具体位置，高效查询。一般来说，索引本身也很大，不可能全部存储在内存当中，因此索引往往以索引文件的形式存储在磁盘上。索引是用来提供高性能数据库的常用工具。

2.2 索引的优缺点

优点

1）类似于书籍的目录索引，提高数据检索的效率，降低数据库的IO成本。

2）通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

缺点

1） 实际上索引也是一张表，该表中保存了主键与索引字段，并指向实体类的记录，所以索引列也是要占用空的。

2）索引虽然大大提升了查询效率，但同时也降低了更新表的速度，如果对表进行了insert/update/delete等语句时，数据库不仅要保存数据，还需更新索引文件，每次更新添加了索引列的字段，都会调整索引的结构。

2.3 索引的类型

索引是在MySQL的存储引擎层中实现的，而不是在服务器层实现的。所以每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型的。

2.3.1 数据结构角度

B-Tree：常见的索引类型，B+Tree就是在此数据结构上做的优化
Hash：是基于哈希表实现的，只有精确匹配索引所有列的查询才会生效。对于每一行数据，存储引擎都会对所有的索引列计算一个hash code,并将有的hash code存储在索引中，同时在哈希表中保存指向每个数据行的指针。
R-Tree：R-Tree是B-Tree在高维空间的扩展，也叫空间索引，主要用于地理空间数据类型，存储高维数据的平衡树。
Full-text：Full-text索引就是我们常说的全文索引，类似于Lucene的文本全文检索技术，比like要高效许多。

MyISAM、InnoDB、Memory三种存储引擎对各种索引类型的支持

索引	InnoDB	MyISAM	Memory
B-Tree	支持	支持	支持
Hash	不支持	不支持	支持
R-Tree	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

我们平常所说的索引，如果没有特别指明，都是指B+Tree（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用 B+Tree 索引，统称为索引。

2.3.2 Hash

当表中的某一列建立了hash索引时，首先使用hash算法将列的键值计算为hash值，然后根据计算的hash值来选择存储对应的位置；hash索引底层采用一张hash表建立索引与列的关系；通常情况下，使用hash索引查询一次即可定位要查询的行，但是当hash冲突时hash表的某个槽位将会存储很多的键值，查询性能会变低；

1）新增数据时，先根据键值计算出hash值，通过hash值一个映射到一个槽位中；当多个数据计算的hash值一致时（hash冲突），可能映射到同一个槽位，这个时候可以通过链表来解决hash冲突问题；

2）当根据hash索引查询数据时，首先计算hash值，通过hash值去hash表中查询数据；

2.3.3 B+Tree

2.3.3.1 二叉树

B+Tree中的B代表平衡（balance），而不是二叉（binary），因为B+Tree是从最早的平衡二叉树演化而来的。在讲B+Tree之前必须先了解二叉查找树、平衡二叉树（AVLTree）和平衡多路查找树（B-Tree），B+Tree即由这些树逐步优化而来。

数据结构可视化网址：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html

特点：左子树的键值小于右子树的键值。

对该二叉树的节点进行查找发现深度为1的节点的查找次数为1，深度为2的查找次数为2，深度为n的节点的查找次数为n，因此其平均查找次数为 (1+2+2+3+3+3+3) / 7 = 2.428次

二叉查找树可以任意地构造，同样是2,3,5,6,7,8这六个数字，也可以按照下图的方式来构造：

计算这颗树的平均查找次数：(1+2+3+4+5+6+6)/7=3.857次

这棵二叉树的查询效率明显就低了。因此若想二叉树的查询效率尽可能高，需要这棵二叉树是平衡的，从而引出新的定义——平衡二叉树，或称AVL树。

2.3.3.2 平衡二叉树

平衡二叉树（AVL树）在符合二叉查找树的条件下，还满足任何节点的两个子树的高度最大差为1。

我们按照顺序依次插入6,3,7,2,4,1,5数据，观察AVL树与普通二叉树的演变：

2.3.3.3 B-Tree

B-Tree又叫多路平衡搜索树，一颗m叉的B-Tree特性如下：

树中每个节点最多包含m个孩子。
除根节点与叶子节点外（非根叶子节点），每个节点至少有[ceil(m/2)]个孩子。
若根节点不是叶子节点（说明树的层级最少为2），则至少有两个孩子。
所有的叶子节点都在同一层。
每个非叶子节点由n个key与n+1个指针组成，其中[ceil(m/2)-1] <= n <= m-1

B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree：

每个节点都会占用一个磁盘块的磁盘空间，当在数据检索时，会将此磁盘块加载到内存中，每个非叶子点上都有两个升序排序的键值和三个指向子树的指针，该指针存储的是子节点所在磁盘块的内存地址

以磁盘块1为例，假设现在加载了磁盘块1到内存中，我们通过磁盘块的三个指针（P1、P2、P3）就能够判断，P1指针指向的子树的数据范围为小于26，P2指针指向的子树的数据范围为26~37，P3指针指向的子树的数据范围为大于35。

模拟查找关键字21的过程：

1）首先加载根节点，根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】

2）发现21比26小，根据磁盘块1中的P1指针找到对应的磁盘2，读入内存。【磁盘I/O操作第2次】

3）发现21比18大，根据磁盘块2中的P3指针找到磁盘块7，读入内存。【磁盘I/O操作第3次】

4）根据磁盘块7中的数据找到21。

2.3.3.4 B+Tree

MySQL索引数据结构对经典的B-Tree进行了优化。在原B-Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。并且B+Tree数据结构将数据全部存储在最底层的叶子节点，这样可以保证每个磁盘块能够存储更多的指针，查询数据更广。而B-Tree想要存储B+Tree同样的数据智能增加树的高度，导致性能降低；

通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。

2.3.2 InnoDB数据页

操作系统的局部性原理

当我们要做数据处理时，需要把磁盘中的数据调出到内存中来进行处理，这个过程会产生磁盘的IO（输入输出），由于磁盘IO是非常昂贵的操作，所以计算机操作系统对此做了优化。每一次IO时，不仅仅把当前磁盘地址的数据加载到内存，同时也把相邻数据也加载到内存缓冲区中；

计算机磁盘存储数据最小单元是扇区，一个扇区通常大小为512字节；而文件系统（NTFS/EXT/FAT等）的最小的操作单位是块（页），一个块的大小是4K；也就是说我所使用的文件系统中1个块是由连续的8个扇区组成；每次磁盘IO读取数据时，其大小为一个块，也就是4K；

通过如下指令可以查询：

fsutil fsInfo ntfsInfo C:

InnoDB数据页管理

在InnoDB中，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，在MySQL中可通过如下命令查看页的大小：

show variables like 'innodb_page_size';

Tips：若已经初始化mysql实例，仍要进行修改，则会无法启动mysql服务，需要删除已经存在的所有的ibdata和ib_logfile文件

而系统一个磁盘块的存储空间往往没有这么大（4K），因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块（4个）来达到页的大小16KB。InnoDB在把磁盘数据读入到磁盘时会以页为基本单位，在查询数据时一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。

B-Tree：

B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。

B+Tree：

在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

Tips：InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为10^3）。也就是说一个深度为3的B+Tree索引可以维护10^3 _ 10^3 _ 10^3 = 10亿条记录。

2.3.3 物理存储角度

2.3.3.1 聚集索引与非聚集索引

索引按照物理存储结构划分分为聚集索引和非聚集索引

**聚集索引：**也叫聚簇索引（ClusterIndex），一般来说是以主键创建的索引，一张表只能有一个聚集索引，而且只有InnoDB能够创建聚集索引。

非聚集索引：也叫普通索引、辅助索引（Secondary Index）、二级索引等，除了聚集索引外的索引都是非聚集索引。

聚集索引的B+Tree叶子节点上存储的是整行的数据，而非聚集索引的B+Tree索引树上只会存储当前索引列的数据与主键索引列的数据，并不会存放整行数据，当需要通过非聚集索引去检索一行数据时，首先非聚集索引通过索引树找到主键，然后通过主键去主键建立的B+Tree上查询出整行的数据；

聚集索引与非聚集索引示意图：

回表查询需要把聚集索引加载进内存，首先加载的应该是根节点，而不是直接定位到叶子节点

通过上面的索引图我们能够很直观的发现，普通索引（非聚集索引）的查询如果查询到了其他列的数据，那么需要借助主键索引来查询整行的数据，这个过程也称为回表查询；

2.3.3.2 索引组织表

其实，MySQL中的表的所有的数据都是按照主键排序的方式存储在一颗B+Tree上（InnoDB引擎），这颗B+Tree也叫聚集索引，所以MySQL叫索引组织表（InnoDB引擎）；

索引组织表的数据按主键排序手段被存储在B+Tree索引中，除了存储主键列值外还存储非主键列的值。普通索引只存储索引列，索引组织表就是索引（主键索引）存储表的所有列的值

在MySQL中，聚集索引就是我们看到的表，表就是一颗聚集索引；

2.3.3.3 聚集索引说明

关于聚集索引还有一个定义：数据行的物理排列顺序与该列值（主键）的逻辑顺序相同，并且一个表中只能拥有一个聚集索引；

测试表：

DROP TABLE IF EXISTS `t_user`;
CREATE TABLE `t_user`  (
  `id` int(11) NOT NULL,
  `username` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of t_user
-- ----------------------------
INSERT INTO `t_user` VALUES (1, '小龙', 20);
INSERT INTO `t_user` VALUES (2, '小明', 29);
INSERT INTO `t_user` VALUES (3, '小刚', 30);
INSERT INTO `t_user` VALUES (4, '小美', 24);
INSERT INTO `t_user` VALUES (5, '小军', 26);
INSERT INTO `t_user` VALUES (6, '小龙', 19);
INSERT INTO `t_user` VALUES (7, '小康', 25);
INSERT INTO `t_user` VALUES (8, '小红', 20);

什么是"数据行的物理排列顺序"？

数据行的物理排列顺序就是我们眼睛直观看到的列的排列顺序

一般来说我们主键都是自增的，大小都是从小到大，因此会忽略一个问题：数据行的物理排列顺序与聚集索引的逻辑排列顺序是保持一致的

我们把"小军"的ID改为50，重新查看数据库表，发现排列顺序如下：

发现数据行的物理排列顺序默认是和主键顺序保存一致的；

这也是聚集索引在一张表中只能有一个的原因，为什么呢？因为我数据表的逻辑排列顺序要与聚集索引的排列顺序保持一致！那如果有多个聚集索引我到底跟谁保持一致呢？？

而且我们知道MySQL是索引组织表的，如果聚集索引有多份，那么数据是否也会存在多份呢？这样存储效率明显下降

2.3.3.4 MySIAM引擎索引底层实现原理图

虽然MyISAM和InnoDB的索引底层采用的都是B+Tree数据结构，但MyISAM和InnoDB索引的底层实现稍有不同：

MyISAM没有聚集索引，MyISAM建立的都是普通索引（即使是主键）；

MyISAM引擎是没有聚集索引的，因为MyISAM引擎不属于索引组织表，即有单独的空间存储表数据，表数据并不是和主键建立的B+Tree存储在一起的；MyISAM表通过任何索引来查询数据都需要回表查询（前提是查询到了索引列之外的数据）；

2.3.3.5 InnoDB的主键策略

我们知道InnoDB表是属于索引组织表，整表的数据都需要根据主键（聚集索引）来排序，并且数据也是存储在主键（聚集索引）的B+Tree上的；那么万一我们在表中没有创建主键（聚集索引）那么怎么办呢？

答：InnoDB必须要有且只有一个（聚集索引），不能没有（聚集索引）

主键（聚集索引）对于InnoDB实在是太重要了，InnoDB不能没有他，如果你不创建他会根据规则来选出较为合适的一列来做聚集索引，实在不行他就帮你创建一个隐藏的列作为聚集索引，规则如下：

（1）如果表定义了主键，则该列就是聚集索引；

（2）如果表没有定义主键，则第一个**not null unique**列是聚集索引；

（3）以上条件都不满足：InnoDB会创建一个隐藏的**row-id**作为聚集索引；

2.4 其他索引

2.4.1 覆盖索引

2.4.1.1 覆盖索引概念

覆盖索引（或称索引覆盖）：查询的数据刚好在索引树上，不需要回表查询。很显然，聚簇索引就是一种覆盖索引，因为聚簇索引中包含了数据行的全部数据，而非聚集索引的话，要看SQL语句查询的列是否在索引树上，如果不在则需要回表查询；简单的说就是查询的列要被所使用的索引覆盖，换句话说就是查询的列要在索引树上，不需要回表查询。

2.4.1.2 覆盖索引应用

使用覆盖索引的SQL语句：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。

我们给username列加上索引：

create index idx_name on t_user(username);

执行如下SQL：

-- username创建的B+Tree上有值（使用了覆盖索引）
explain select username from t_user where username='xxx';
-- username创建的B+Tree上有值（使用了覆盖索引）
explain select id,username from t_user where username='xxx';
-- username创建的B+Tree上没有值,age列需要回表到聚集索引上去查询（没有使用覆盖索引）
explain select id,username,age from t_user where username='xxx';

2.4.1.3 覆盖索引总结

覆盖索引：查询的数据被索引树覆盖了，即：查询的数据在索引树上，不需要回表查询；

2.4.2 前缀索引

2.4.2.1 前缀索引概念

前缀索引也是一种概念，或者说是操作索引的一种技巧；当索引列的数据是非常大时，那么该列建立的索引会非常大，而且检索速度也会很慢，这个时候我们考虑能否让该列的前面几个字符拿出来建立索引，而不是整列是数据建立索引，因此前缀索引的概念就由此产生；

我们知道前缀索引其实就是拿出该列数据的前几个字符出来建立索引以降低索引的大小，以及加快索引的速度的一种==技巧性索引，但是毕竟前面几个字符不能够代替整列数据，有可能重复，我们应该尽量的减低重复的概率，降低重复概率==，这样的前缀索引检索速度更快；

2.4.2.2 前缀索引的应用

数据准备：

CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  `birthday` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB ;
-- ----------------------------
-- Records of student
-- ----------------------------
INSERT INTO `student` VALUES (1, '小明', '1999-10-20');
INSERT INTO `student` VALUES (2, '小军', '1999-02-21');
INSERT INTO `student` VALUES (3, '小龙', '1999-01-19');
INSERT INTO `student` VALUES (4, '小刚', '1999-06-06');
INSERT INTO `student` VALUES (5, '小红', '1999-02-05');

计算重复率公式：去重后的数据 / 总的数据

-- 只查询birthday这一列
select birthday from student
-- 只查询birthday列左边num个字符
select left(birthday,7) from student
-- 在列的最左边去除num个字符后去重后,查询记录
select distinct left(birthday,num) from student;
-- 在列的最左边去除num个字符后去重后,统计总条数
select 1.0*count(distinct left(birthday,num)) from student;
-- 将去重后的数据的总条数 / 表中的总条数 = 不重复率
select 1.0*count(distinct left(birthday,num))/count(*) from student;

建立前缀索引就是计算数据到了前缀的第几个字符后，数据没有重复的；我们就可以以前面几个字符来建立索引，这样索引的大小就可以得到压缩，查询速度可以加快；

案例：

mysql> select 1.0*count(distinct birthday)/count(*) from student;
+---------------------------------------+
| 1.0*count(distinct birthday)/count(*) |
+---------------------------------------+
|                               1.00000 |
+---------------------------------------+
1 row in set (0.00 sec)
mysql> select 1.0*count(distinct left(birthday,1))/count(*) from student;		# 1 / 5 = 0.2
+-----------------------------------------------+
| 1.0*count(distinct left(birthday,1))/count(*) |
+-----------------------------------------------+
|                                       0.20000 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> select 1.0*count(distinct left(birthday,2))/count(*) from student;		# 1 / 5 = 0.2
+-----------------------------------------------+
| 1.0*count(distinct left(birthday,2))/count(*) |
+-----------------------------------------------+
|                                       0.20000 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> select 1.0*count(distinct left(birthday,3))/count(*) from student;		# 1 / 5 = 0.2
+-----------------------------------------------+
| 1.0*count(distinct left(birthday,3))/count(*) |
+-----------------------------------------------+
|                                       0.20000 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> select 1.0*count(distinct left(birthday,4))/count(*) from student;		# 1 / 5 = 0.2
+-----------------------------------------------+
| 1.0*count(distinct left(birthday,4))/count(*) |
+-----------------------------------------------+
|                                       0.20000 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> select 1.0*count(distinct left(birthday,5))/count(*) from student;		# 1 / 5 = 0.2
+-----------------------------------------------+
| 1.0*count(distinct left(birthday,5))/count(*) |
+-----------------------------------------------+
|                                       0.20000 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> select 1.0*count(distinct left(birthday,6))/count(*) from student;		# 2 / 5 = 0.4
+-----------------------------------------------+
| 1.0*count(distinct left(birthday,6))/count(*) |
+-----------------------------------------------+
|                                       0.40000 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> select 1.0*count(distinct left(birthday,7))/count(*) from student;		# 2 / 5 = 0.4
+-----------------------------------------------+
| 1.0*count(distinct left(birthday,7))/count(*) |
+-----------------------------------------------+
|                                       0.80000 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> select 1.0*count(distinct left(birthday,8))/count(*) from student;		# 4 / 5 = 0.8
+-----------------------------------------------+
| 1.0*count(distinct left(birthday,8))/count(*) |
+-----------------------------------------------+
|                                       0.80000 |
+-----------------------------------------------+
1 row in set (0.00 sec)
mysql> select 1.0*count(distinct left(birthday,9))/count(*) from student;		# 5 / 5 = 1
+-----------------------------------------------+
| 1.0*count(distinct left(birthday,9))/count(*) |
+-----------------------------------------------+
|                                       1.00000 |
+-----------------------------------------------+
1 row in set (0.00 sec)

发现不重复率在birthday字段在第9个字符时，达到100%，也就是说，在的倒数第二个字符时，数据没有重复的。

重复概率为0，不重复概率为100%

计算出重复率最高时是在该列的低9个字符，因此我们可以把第9个字符之外的数据不用建立索引，来降低索引的大小，提高索引的检索速度；

alter table student add key(birthday(9));

下次根据birthday字段查询，会发现使用了索引查询：

explain select birthday from student where birthday='1999-02-21';

如果建立的前缀索引有重复数据会怎么样？

我们刚刚建立前缀索引时，首先要计算重复率，然后再根据得出合适的位置来建立索引，那么如果不计算重复率会怎么样？？

我们把刚刚建立的索引删除，在第4个字符位置上建立索引：

alter table student drop index birthday;
alter table student add key(birthday(4));

执行如下SQL分析执行计划：

explain select birthday from student where birthday='1999';
explain select birthday from student where birthday='1999-02-21';
explain select birthday from student where birthday='1999-10-21';

我们随机执行了几条SQL语句，发现都没有走索引查询，而是全表顺序扫描，因为重复的数据太多了（占整表数据），MySQL优化器认为走索引还不如不走索引，因此选择顺序扫描查询；

我们切换个案例，把索引前缀字符切换为7：

alter table student drop index birthday;
alter table student add key(birthday(7));

执行如下SQL：

explain select birthday from student where birthday='1999-10-21';
explain select birthday from student where birthday='1999';

2.4.3 全文索引

2.4.3.1 全文索引概念

如果希望通过关键字的匹配度来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的。MySQL也提供有类似于Lucene的全文检索技术，用于处理大规模数据检索，其处理速度比like高效的多

全文索引使用说明：

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

创建全文索引：

create fulltext index index_name on table_name(col_name);
alter table table_name add fulltext index index_name(col_name);

使用全文索引：

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

match函数中指定全文索引列，asainst函数中指定具体要搜索的内容；

2.4.3.2 匹配长度

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

我们可以通过以下命令查询：

show variables like '%ft%';

其中前缀带有innodb的为innodb引擎变量，其余为myisam引擎变量

// MyISAM变量
ft_min_word_len = 4;
ft_max_word_len = 84;
// InnoDB变量
innodb_ft_min_token_size = 3;
innodb_ft_max_token_size = 84;

Tips：我们可以看出innodb的最小搜索长度为3，最大搜索长度为84；实际开发中最大搜索长度可能会设大

创建测试表：

create table test (
    id int(11) not null auto_increment,
    content varchar(30) not null,
    PRIMARY KEY (`id`) USING BTREE,
    FULLTEXT INDEX (`content`)
) engine=myisam default charset=utf8;
insert into test(content) values('a'),('aa'),('aaa'),('aaaa');

执行查询：

select * from test where match(content) against('a');
select * from test where match(content) against('aa');
select * from test where match(content) against('aaa');
select * from test where match(content) against('aaaa');

发现只有搜索aaa和aaaa时才会出现记录；

我们可以尝试修改mysql的系统配置（/etc/my.cnf）：

[mysqld]
innodb_ft_min_token_size = 1
ft_min_word_len = 1

重启mysql服务：

systemctl restart mysqld

修复索引：

repair table test quick;		-- myisam(修复表)
optimize table test;			-- innodb(优化表)

Tips：修改完参数以后，一定要修复下索引，因为当初建立索引是基于innodb_ft_min_token_size=3的

2.4.3.2 全文索引的模式

1）自然语言处理模式

in natural language mode：自然语言处理模式；默认情况下，MySQL使用 in natural language mode 修饰符，match() 函数对文本集合执行自然语言搜索，实现从一个文本集合中搜索给定的字符串

创建测试表：

CREATE TABLE `goods`  (
  `id` int(11) NOT NULL,
  `title` varchar(255),
  PRIMARY KEY (`id`) USING BTREE,
  FULLTEXT INDEX (`title`)
) ENGINE = InnoDB ;
INSERT INTO `goods`(`id`, `title`) VALUES (1, 'huawei 5G zhineng paizhao shouji');
INSERT INTO `goods`(`id`, `title`) VALUES (2, 'xiaomi 5G zhineng youxi shouji');
INSERT INTO `goods`(`id`, `title`) VALUES (3, 'xiaomi 4G fashao shouji');
INSERT INTO `goods`(`id`, `title`) VALUES (4, 'huawei 4G youxi qumianping shouji');

查询：

select * from goods where match(title) against('xiaomi 5G' in natural language mode);
select * from goods where match(title) against('xiaomi 5G');		-- 简写

2）布尔处理模式

in boolean mode：布尔处理模式；如果在against()函数中指定了in boolean mode模式，则MySQL会执行布尔全文搜索。在该搜索模式下，待搜索单词前或后的一些特定字符会有特殊的含义。

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

1）包含与不包含：

-- 分词查询
select * from goods where match(title) against('xiaomi 5G' in boolean mode);
-- 必须不包含5G词条
select * from goods where match(title) against('xiaomi -5G' in boolean mode);
-- 必须包含5G词条
select * from goods where match(title) against('xiaomi +5G' in boolean mode);

2）提高/降低权重（相关性）

select * from goods where match(title) against('xiaomi >4G' in boolean mode);
select * from goods where match(title) against('xiaomi <4G' in boolean mode);

3）通配符（模糊）查询：

select * from goods where match(title) against('xiao' in boolean mode);
select * from goods where match(title) against('xiao*' in boolean mode);

4）分组查询：

查询必须包含5G并且必须包含paizhao或youxi的商品，youxi商品权重降低

select * from goods where match(title) against('+5G +(paizhao <youxi)' in boolean mode);

2.4.3.3 N-gram中文分词

MySQL的全文搜索对于英文是基于空格的分词，由于中文没有空格，因此MySQL的全文索引对中文支持的不是很友好

中文查询测试：

INSERT INTO `test`.`goods`(`id`, `title`) VALUES (5, '华为5G智能拍照手机');
INSERT INTO `test`.`goods`(`id`, `title`) VALUES (6, '小米5G智能游戏手机');
INSERT INTO `test`.`goods`(`id`, `title`) VALUES (7, '小米4G发烧 手机');			-- 故意弄一个空格用于分词
INSERT INTO `test`.`goods`(`id`, `title`) VALUES (8, '华为4G游戏曲面屏手机');
select * from goods where match(title) against('华为');
select * from goods where match(title) against('小米');
select * from goods where match(title) against('手机');

使用ngram中文分词：

CREATE TABLE `article`  (
  `id` int(11) NOT NULL,
  `title` varchar(255) ,
  PRIMARY KEY (`id`) USING BTREE,
  FULLTEXT INDEX `title`(`title`) with parser ngram				-- 使用ngram中文分词器
) ENGINE = InnoDB ;
INSERT INTO `article`(`id`, `title`) VALUES (1, '华为5G智能游戏手机');
INSERT INTO `article`(`id`, `title`) VALUES (2, '小米5G全面屏拍照手机');
INSERT INTO `article`(`id`, `title`) VALUES (3, 'vivo美颜拍照手机');
INSERT INTO `article`(`id`, `title`) VALUES (4, '新款oppo高性能音乐拍照全网通');

执行查询：

select * from article where match(title) against("拍照");

MySQL全文索引官方手册：https://dev.mysql.com/doc/refman/5.7/en/innodb-fulltext-index.html

n-gram parser：https://dev.mysql.com/doc/refman/5.7/en/fulltext-search-ngram.html

2.4.4 一级索引与二级索引

关于一级索引的定义：索引和数据存储是在一起的，都存储在同一个B+Tree中的叶子节点。一般主键索引都是一级索引。

关于二级索引的定义：二级索引树的叶子节点存储的是本列索引值和主键值；而不是整行数据。在使用二级索引检索整行数据时，需要借助一级索引；也就是说，在找到索引后，得到对应的主键，再回到一级索引中找主键对应的数据记录。

一级索引就是聚集索引，二索引就是非聚集索引

2.4.5 辅助索引

辅助索引就是辅助我们查询的索引，一般指的就是非聚集索引（二级索引）

2.5 索引列的离散性

我们都知道MySQL底层是有优化器的，最终是否使用到索引，以及具体使用哪个索引是MySQL优化器根据一系列成本计算最终得出的结果，如果==列的离散性越高，证明列的不重复性越高==，优化器更容易选择，如果列的离散性非常底，那么优化器在选择列的时候也会有选择困难症，降低检索速度，如果列的离散性非常低，那么很有可能优化器就不选择索引，直接进行全表扫描了；

离散性计算公式：count(distinct col)/count(col)；

测试数据：

CREATE TABLE `student`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  `age` int(11) NULL DEFAULT NULL,
  `gender` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB;
INSERT INTO `test`.`student`(`id`, `name`, `age`, `gender`) VALUES (1, '张三', 20, '0');
INSERT INTO `test`.`student`(`id`, `name`, `age`, `gender`) VALUES (2, '李四', 18, '1');
INSERT INTO `test`.`student`(`id`, `name`, `age`, `gender`) VALUES (3, '王五', 19, '0');
INSERT INTO `test`.`student`(`id`, `name`, `age`, `gender`) VALUES (4, '赵六', 18, '1');
INSERT INTO `test`.`student`(`id`, `name`, `age`, `gender`) VALUES (5, '孙七', 20, '1');
INSERT INTO `test`.`student`(`id`, `name`, `age`, `gender`) VALUES (6, '周八', 20, '1');

计算name、age、gender的离散值：

select 
(select count(distinct name)/count(1) from student) name_col,
(select count(distinct age)/count(1) from student) name_col,
(select count(distinct gender)/count(1) from student) name_col

2.6 索引的分类

MySQL的索引实在是太多了，这么多索引我们该怎么记？？以及该如何区分？？

我们看待事物的角度不同，索引也可以分为以下角度：

数据结构角度
- B+Tree索引
- R-Tree索引（空间索引）
- Hash索引
- Full-text索引（全文索引）
物理存储角度
- 聚集索引（一级索引）
- 非聚集索引（二级索引）
逻辑角度
- 主键索引
- 普通索引（单列索引）
- 多列索引（复合索引）
- 唯一索引
- 非唯一索引
- 空间索引
业务角度
- 覆盖索引
- 前缀索引
- 辅助索引（非聚集索引）

2.7 索引的设计

索引的设计可以遵循一些已有的原则，创建索引的时候请尽量考虑符合这些原则，便于提升索引的使用效率，更高效的使用索引。

尽量选择离散性高的列建立索引（优化器选择性好）
采用联合索引（联合索引底层原理）
严格避免索引列的失效（索引失效）
注意最左前缀法则

三、索引的类型

到这里我们差不多已经了解了学习上、工作上绝大多数索引了，包括什么B-Tree索引、B+Tree索引、R-Tree索引、Hash索引、全文索引、聚集索引、唯一索引、二级索引、一级索引、覆盖索引、前缀索引。

除此之外我们应该还有听说过复合索引、唯一索引、普通索引、主键索引、非唯一索引…

这么多索引我们该怎么记？？以及该如何区分？？

我们看待事物的角度不同，索引也可以分为以下角度：

数据结构角度：
- B+Tree索引
- R-Tree索引（空间索引）
- Hash索引
- Full-text索引（全文索引）
物理存储角度
- 聚集索引（一级索引）
- 非聚集索引（二级索引）
逻辑角度
- 主键索引
- 普通索引（单列索引）
- 多列索引（复合索引）
- 唯一索引
- 非唯一索引
- 空间索引
业务角度
- 覆盖索引
- 前缀索引
- 辅助索引（非聚集索引）