【重难点】【MySQL 03】索引的分类和用法、复合索引详解、全文索引详解、聚簇索引和非聚簇索引详解
文章目录
- 【重难点】【MySQL 03】索引的分类和用法、复合索引详解、全文索引详解、聚簇索引和非聚簇索引详解
- 一、索引的分类和用法
- 1.索引简介
- 2.分类
- 3.用法
- 二、复合索引详解
- 三、全文索引详解
- 1.概念
- 2.用法
- 3.两种全文索引
- 4.几个注意点
- 四、聚簇索引和非聚簇索引详解
- 1.聚簇索引
- 2.非聚簇索引
一、索引的分类和用法
1.索引简介
索引是一种排好序的可以快速查找的数据结构，它帮助数据库高效地进行数据的检索。它的原理很简单，就是把无序的数据变成有序的查询。在数据之外，数据库系统还维护着满足特定查找算法的数据结构（需要额外的存储空间），这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高效地查找算法。这种数据结构就叫做索引
一般来说索引需要的存储空间不算小，不可能全部存储在内存中，因此往往以索引文件的形式存放在磁盘中
什么时候需要创建索引
表的数据量过大，而且业务中需要频繁查询，且查询速度很慢时
使用索引的条件
创建索引的列必须是 where 子句的条件，否则索引就不起作用
条件可以是 < 或 <= 或 = 或 > 或 >= 或 BETWEEN 或 IN，但是不要用 not in 和 <>
使用 LIKE 时，条件可以是 “xxx%”，但不可以是 “%xxx”
索引的缺点
索引对查询速度可以大幅提升，但不推荐滥用。创建索引后，对表数据进行 INSERT、UPDATE 和 DELETE 时，同样会对索引文件进行修改，从而影响性能
建立索引后还要建立索引表，相当于建了两份表。此外，建立索引还会占用磁盘空间
2.分类
- 从存储结构划分：BTree 索引、Hash 索引、全文索引、RTree 索引
- 从应用层次划分：单列索引、唯一索引、复合索引
- 根据数据的物理顺序与键值的逻辑（索引）顺序关系划分：聚簇索引、非聚簇索引
存储结构指索引存储时保存的形式，应用层次指索引使用过程中进行的分类
存储结构
MySQL 默认存储引擎 InnoDB 只显式支持 BTree 索引，对于频繁访问的表，InnoDB 会建立自适应 hash 索引，即在 BTree 索引基础上建立 hash 索引，可以显著提高查找效率
应用层次
- 单列索引：即一个索引只包含单个列，一个表可以有多个单列索引
- 唯一索引：索引列的值必须唯一，允许有 NULL 值
- 复合索引：复合索引是索引中功能最强大的一个. 索引能够同时覆盖多个数据列
聚簇索引与非聚簇索引
- 聚簇索引：表数据按照索引的顺序来存储，也就是说索引项的顺序与表中记录的物理顺序一致。对于聚簇索引，叶子结点存储了真实的数据行，不再有另外单独的数据页。在一张表上最多只能创建一个聚簇索引，因为真实数据的物理顺序只有一种
- 非聚簇索引：表数据存储顺序与索引顺序无关。对于非聚簇索引，叶子结点包含索引字段值及指向数据页数据行的逻辑指针，其行数量与数据表行数量一致
总结一下：聚簇索引是一种稀疏索引，数据页上一级的索引页存储的是页指针，而不是行指针。而对于非聚簇索引，则是密集索引，在数据页的上一级索引页存储的是行指针，它为每一个数据行存储一条索引记录
3.用法
创建索引
CREATE INDEX index_name
ON table_name (column_name)创建唯一索引
CREATE UNIQUE INDEX index_name
ON table_name (column_name)创建复合索引
CREATE INDEX index_name
ON table_name (column_name1,column_name2)删除索引
DROP INDEX index_name
ON table_name显示索引
SHOW INDEX
FROM table_name二、复合索引详解
最左前缀原则
MySQL 中的索引可以按一定顺序引用多列，这种索引就做复合索引（多列索引、联合索引）。比如，User 表的 name 和 city 加上复合索引就是（name,city）。而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左侧连续一列或几列，则次查询就可以命中索引。此外，遇到范围查询（>、<、BETWEEN）会导致索引失效
select * from user where name=xx and city=yy; //可以命中索引
select * from user where name=xx; //可以命中索引
select * from user where city=yy; //无法命中索引这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，比如 city=yy and name=xx，我们不用担心无法命中索引，因为 MySQL 会自动将其优化为匹配复合索引的顺序
由于最左前缀原则，在创建复合索引时，索引字段的顺序很重要，应该把字段值去重后数量较多的放在前面，提高命中率
为什么会形成最左前缀原则
首先我们要知道，最左前缀原则都是针对复合索引来说的，所以我们有必要了解一下复合索引的原理，了解了复合索引的原理，那么为什么会形成最左前缀原则也就很好理解了
索引的底层是一棵 B+树，复合索引自然也是，只不过复合索引的键值数量不是一个，而是多个。构建一棵 B+树只能根据单个值来构建，因此数据库依据复合索引最左边的字段来构建
例：创建一个（a,b）索引，它的底层结构是这样的
可以看到，a 的值是有序的（1，1，2，2，3，3），而 b 的值是无序的（1，2，1，4，1，2）。如果查询条件为 b = 2，就无法利用索引，因为复合索引是按 a 排序的
同时我们还可以发现，在 a 值相等的情况下，b 值是按顺序排列的，但是这种顺序不是绝对有序，所以最左前缀原则遇上范围查询就会导致索引失效。例如，a=1 and b=2，a 字段和 b 字段都可以使用索引，因为在 a 值确定的情况下，b 值是相对有序的。但是如果是范围查询，a>3 and b=2，a 字段可以匹配上索引，但是 b 值无法匹配，因为在 a>3 这个范围内 b 值的排列为 1，2，1，4，很明显是无序的
复合索引使用的目的是什么
形成覆盖索引
覆盖索引：即从非主键索引中就能查到的记录，而不需要查询主键索引中的记录，避免了回表查询（先定位主键值，再定位行记录），减少了树的搜索次数，显著提升性能
一个复合索引是否可以代替多个单列索引
复合索引有最左前缀原则，所以正常情况下无法代替多个单一索引
什么情况下适合使用复合索引
如果一个表中的数据在查询时有多个字段总是同时出现，那么这些字段就可以作为复合索引
什么情况下不适合使用复合索引
建立索引的目的是帮助查询，如果查询用不到的索引就没必要建立
三、全文索引详解
1.概念
通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询，但是，如果希望通过关键字的匹配来进行查询过滤，那么就需要基于相似度的查询，而不是原来的精确数值比较。全文索引就是为这种场景设计的
你可能会说，用 like + % 就可以实现模糊匹配了，为什么还要全文索引？like + % 在文本较少时是合适的，但是对于大量的文本数据检索，是不可想象的
全文索引就是为了使得关键词搜索功能更加高效，那么什么是全文索引呢？
假设现在有一个文章管理系统，所有的文章内容都是存在数据库的一个字段里面，这个字段最长可以存储 3000 个字节，文章的平均长度是 2000 个字节，文章总数 20 万篇，表结构简化如下：
文章ID | 文章标题 | 文章内容 |
1 | 代理模式 | Spring 的 AOP 技术就是动态代理的实现 |
你想要在这 20 万篇文章里找出有 “Spring 的 AOP” 的文章
上面说过 like 的效率很低，那么如何替换呢？如果可以为关键词建立一个索引文件，那么问题似乎会变得容易，索引文件的逻辑结构如下：
关键词 | 文章ID |
Spring 的 AOP | 1 |
当查询的时候，会先查询这个索引表，找到 “Spring 的 AOP”，然后你就获得了对应的文章 ID，这样查询会高效得多
我们再来看一个更复杂的例子，我们有如下数据表：
文章ID | 文章标题 | 文章内容 |
1 | 代理模式 | Spring 的 AOP 技术就是动态代理的实现 |
2 | 单例模式 | 单例模式的目的是保证一个类仅有一个实例 |
3 | 红黑树 | 红黑树是平衡二叉树的一种实现方式 |
4 | 索引 | 索引本质上是数据结构 |
5 | Spring 中的设计模式 | AOP 底层就是动态代理模式的实现 |
6 | HashMap | 一定条件下，链表会转化为红黑树 |
然后根据以上文章内容，建立一个索引文件，其逻辑结构如下：
关键词 | 文章ID |
AOP | 1，5 |
模式 | 2，5 |
红黑树 | 3，6 |
当我想要搜索 “红黑树” 的时候，这个索引文件就可以直接告诉我在文章 ID 为 1 和 5 的文章里有 “红黑树”
这个索引文件就是 “全文索引”
工作原理
- 索引程序从数据库读取数据，例如 SELECT article_id,article_title,article_content FROM table_article，索引程序就会读取数据库中文章的相关数据
- 索引程序调用分词程序对需要索引的内容进行分词
- 索引程序为分好词的一个个词条创建索引文件
这样，原来使用 like 需要到原表中的文章内容中查找，现在只需要在索引文件中查找
2.用法
创建表时建立全文索引
create table fulltext_test(
id int(11) NOT NULL AUTO_INCREMENT,
content text NOT NULL,
tag varchar(255),
PRIMARY KEY (id),
FULLTEXT KEY content_tag_fulltext(content,tag)
);在已存在的表上创建全文索引
create fulltext index content_tag_fulltext
on fulltext_text(content,tag);通过 SQL 语句 ALTER TABLE 创建全文索引
alter table fulltext_test
add fulltext index content_tag_fulltext(content,tag);使用 DROP INDEX 删除全文索引
drop index content_tag_fulltext
on fulltext_test通过 SQL 语句 ALTER TABLE 删除全文索引
alter table fulltext_test
drop index content_tag_fulltext;使用全文索引
select * from fulltext_test
where match(content,tag) against('xxx')注意：match() 函数中指定的列必须和全文索引中指定的列完全相同，否则就会报错，无法使用全文索引，这是因为全文索引不会记录关键字来自哪一列
3.两种全文索引
自然语言的全文索引
默认情况下，或者使用 in natural language mode 修饰符时，match() 函数对文本集合执行自然语言搜索，上面的例子都是自然语言的全文索引
自然语言搜索引擎将计算每一个文档对象和查询的相关度。这里的相关度是基于匹配的关键词的个数，以及关键词在文档中出现的次数。在整个索引中出现次数越少的词语，匹配时的相关度就越高。相反，非常常见的单词将不会被搜索，如果一个词语在超过 50% 的记录中都出现了，那么自然语言的搜索将不会搜索这类词语
这个机制也比较好理解，比如说，一个数据表存储的是一篇篇的文章，文章中的常见词、语气词等等，出现的肯定比较多，搜索这些词语就没什么意义，真正需要搜索的是那些文章中有特殊意义的词，这样才能区分不同的文章
布尔全文索引
使用 in boolean mode 修饰符使用布尔搜索，在布尔搜索中，我们可以在查询中自定义某个被搜索的词语的相关性，当编写一个布尔搜索查询时，可以通过一些前缀修饰符来定制搜索
例如：
select * from test where match(content) against('a*' in boolean mode);查询以 a 开头的文章内容
MySQL 内置的修饰符，上面查询最小搜索长度时，搜索结果 ft_boolean_syntax 变量的值就是内置的修饰符，下面简单解释几个，更多修饰符的作用可以查手册
- + 必须包含该词
- - 必须不包含该词
- > 提高该词的相关性，查询的结果靠前
- < 降低该词的相关性，查询的结果靠后
- (*) 通配符，只能接在词的后面
4.几个注意点
- 全文索引虽然快，但是可能存在精度问题
- 如果需要全文索引的是大量数据，建议先添加数据，再创建索引
- 全文索引的相关参数（比如最小搜索长度）无法动态修改，需要修改 MySQL 的配置文件
四、聚簇索引和非聚簇索引详解
聚簇索引和非聚簇索引并不是一种单独的索引类型，而是一种数据存储方式
聚簇索引和非聚簇索引有许多别名，聚簇索引又称为主键索引、聚集索引，而非聚簇索引也被称为二级索引、辅助索引，这两种索引的内部都是 B+ 树
1.聚簇索引
在 InnoDB 表中，表数据文件本身就是按 B+ 树组织的一个索引结构，聚簇索引就是按照每张表的主键构造的一棵 B+ 树，同时叶子结点中存放的就是整张表的行记录数据，所以聚簇索引的叶子结点也被称为数据页
如果没有定义主键，InnoDB 会选择非空的唯一索引代替。如果没有这样的索引，InnoDB 会隐式地定义一个主键来作为聚簇索引
优点：
- 数据访问更快，因为聚簇索引将索引和数据保存在同一个 B+ 树中，因此从聚簇索引中获取数据比非聚簇索引更快
- 聚簇索引对于主键的排序查找和范围查找速度非常快
缺点：
- 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会触发页分裂，严重影响性能。因此，对于 InnoDB 表，我们一般都会定义一个自增的 ID 列为主键
- 更新主键的代价很高，会导致被更新的数据行移动。因此，对于 InnoDB 表，我们一般定义主键为不可更新
假设你定义的主键是不会自增的，随着数据写入，就会导致后一个数据页中所有行的主键并不一定比前一个数据页中所有行大。这时就会触发页分裂的逻辑，调整数据行的相对顺序，保证后一个数据页中的所有行主键值比前一个数据页中主键值大。
2.非聚簇索引
我们在日常工作中，根据实际情况自行添加的索引都是非聚簇索引，非聚簇索引就是一个为了寻找主键的二级索引，它先寻找到主键索引，再通过主键索引去寻找数据
我们把非聚簇索引 “再通过主键索引去寻找数据” 这个过程称之为回表，但不是每次查询都需要回表，这取决于查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询
举个简单的例子，假设我们在员工表的年龄上建立了索引，那么当进行 select age from employee where age < 20 的查询时，在索引的叶子节点上，已经包含了 age 信息，不会再次进行回表查询
