• INDEX MERGE

• 索引的种类

• • B+树索引的类型及使用
• • 普通索引

• 唯一索引

• 前缀索引

• 多列联合索引

• 全文索引

• • 全文索引不得不说的事

• 哈希索引

• 索引信息分析

• • 关于Cardinality

• Cardinality的更新策略

• Cardinality的计算方式

• 索引的使用原则

• • 离散度

• 最左匹配原则

• • like和_的最左匹配方式

• 联合索引的最左匹配方式

• 其他无法使用索引场景

• 无法使用索引中的特例

• • <> 和not in特例

• 最左匹配原则特例

• 总结

MySQL系列文章汇总

========================================================================

| MySQL系列文章 | 飞机票 |

| — | — |

| MySQL索引为何选择B+树 | 登机入口 |

| 深入分析MySQL索引 | 登机入口 |

| MySQL执行计划EXPLAIN详细说明和举例 | 登机入口 |

| MySQL中事务以及MVCC的实现原理 | 登机入口 |

| 深入分析MySQL中锁并详解幻读问题 | 登机入口 |

| select语句和update语句的执行流程 | 登机入口 |

| InnoDB引擎存储结构及Change Buffer和Double Writer分析 | 登机入口 |

| InnoDB中四种行格式分析及对varchar和char的限制 | 登机入口 |

前言

===============================================================

上一篇，我们介绍了MySQL为什么最终选择了B+树来作为索引存储的数据结构，想要详细了解，请点击这里。本文将为大家介绍一下B+树在MySQL中是如何落地的，本文主要会对比常用的两种存储引擎InnoDB和MyISAM来进行比较分析。

存储引擎介绍

===================================================================

MySQL的存储引擎是插件式管理的，我们可以自由选择，MySQL中常用的存储引擎有很多种，但是最常用的就是InnoDB和MyISAM，其他存储引擎不在本文内容之列，就不做过多介绍，主要简单介绍一下InnoDB和MyISAM存储引擎。

MyISAM引擎

---

MyISAM存储引擎不支持行级锁，只有表级锁；不支持事务，也不支持外键，主要面向OLAP应用，是MySQL数据库5.5.8之前版本默认的存储引擎，MyISAM适用于不需要关心事务，读多写少的场景。每张MyISAM表在磁盘上会创建三个文件：.frm，.MYD和.MYI，其中.frm文件为表结构，每个存储引擎都会有这个文件，是用来存储表结构的，.MYD文件用来存储数据，.MYI用来存储索引，也就是说MyISAM的数据和索引是分开存储的，这一点和InnoDB不一样。

在MySQL5.0之前，MyISAM默认支持的表只有4GB，如果要修改默认表大小的话，需要修改参数MAX_ROWS和AVG_ROW_LENGTH的大小，不过这一点在MySQL5.0之后得到了改善，默认大小为256TB，这个大小在绝大部分应用应该都是可以满足要求的。

InnoDB引擎

---

InnoDB存储引擎支持事务，主要是为了面向在线事务处理(OLTP)的应用而生，支持行锁和外键，其通过使用多版本并发控制(MVCC)来提升高并发性能，实现了SQL标准的4种隔离级别，想要详细了解MySQL事务以及MVCC机制的，请点击这里。从MySQL数据库5.5.8版本开始，为MySQL默认存储引擎。每张 InnoDB表在磁盘上会创建两个文件：.frm 和.ibd，其中.frm文件和MyISAM引擎一样，用来存储表结构的，.ibd文件存储的是索引和数据，InnoDB中索引和数据放在同一个文件中。

MyISAM索引结构

=======================================================================

MyISAM的B+树里面，叶子节点存储的是当前索引的值以及当前数据文件对应的磁盘地址。所以如果从索引文件.MYI中找到键值后，会根据其存储的磁盘地址到数据文件.MYD 中获取相应的数据记录，在MyISAM引擎中，主键索引和非主键索引没有差别，都是一样存储，MyISAM索引大致结构如下图所示：

InnoDB索引结构

=======================================================================

InnoDB除了表结构.frm文件外，就只有一个.ibd 文件，索引和数据存储在一起，所以在InnoDB的B+树中叶子节点直接存储的是整条数据记录，而不是记录磁盘地址。InnoDB引擎和MyISAM引擎还有一个最大的不同就是InnoDB引擎是以主键索引来组织数据的（主键索引和非主键索引的存储结构是不同的），InnoDB存储引擎中这种组织数据的方式被称之为聚集索引组织表（clustered index organize table），主键索引也被称之为聚集索引。

聚集索引

---

聚集索引(又称之为聚簇索引),聚集的术语表示的是索引键值和数据紧凑的存储在一起。而数据又不会同时存在两个地方，所以InnoDB每张表都有且只有一个聚集索引，换言之，也就是说每张表都必须有且只有一个主键。说到这里可能很多人就要反问了，我建表的时候没有主键索引也可以建表成功，那么这又是为什么呢？

其实如果我们没有显示的指定主键，InnoDB会选择一个非空的唯一索引列作为主键，如果这个也没有，那么InnoDB就会选择一个选择其自己内置 的6字节长的ROWID自增列作为主键。InnoDB中聚集索引叶子节点直接存储的是整条数据，也就是说索引搜索到叶子节点之后就可以直接返回数据了，无需再去磁盘获取数据。

InnoDB中聚集索引大致结构如下图所示：

非聚集索引

---

除了主键索引之外的其他索引都是非聚集索引，既然聚集索引的索引键值和数据行存放在一起，而聚集索引又只有一个，那么非聚集索引又是怎么存储数据的呢？接下来要画重点了哈：

非聚集索引的叶子节点存储的是当前索引的键值和主键索引的键值。大致结构如下图所示：

所以非聚集索引查询数据和聚集索引查询数据是不同的，因为非聚集索引的叶子节点只有当前索引的键值和主键的键值，也就是说查询数据的时候获取到非聚集索引的叶子节点只能拿到当前索引值和主键索引值。

回表

---

什么是回表？回表指的就是非聚集索引从叶子节点拿到数据(主键的键值)之后，还需要再根据主键键值去扫描主键索引的B+树，这种操作就叫做回表，也就是说他需要扫描两颗B+树，这也就是为什么在InnoDB中主键索引的效率相比较其他索引是最高的。

覆盖索引

---

前面我们说到了回表操作，那么就还有有这么一种场景是不需要回表的：比如说我们一个查询只需要查询当前索引的值和主键的值，而不需要查其他数据，这时候就不需要回表了，直接就可以返回，这种也称之为覆盖索引，所以这也是为什么不要写select * 的原因，因为select * 肯定无法用到覆盖索引(除非整张表都是索引)，而覆盖索引可以少扫描一颗聚集索引的B+树，而且因为辅助索引不会存储整条数据，所以大小也要远小于聚集索引，故而对性能有较大的提升。需要注意的是，MyISAM引擎中如果查找的数据也包含在索引内，不需要去磁盘找数据，也认为是覆盖索引。

MySQL对索引的优化

---

Index Condition Pushdown(ICP)

Index Condition Pushdown中文含义为：索引条件下推。是在MySQL5.6版本之后引进的优化措施。如果没有ICP，存储引擎将遍历索引以定位基表中的行，并将它们返回给MySQLServer层，由Server层计算行的where条件。在启用ICP的情况下，如果WHERE条件的一部分可以通过只使用索引中的列来计算，那么MySQL服务器将这一部分WHERE条件下推到存储引擎。然后，存储引擎使用索引条目来计算已推入的索引条件，只有满足这个条件，才从表中读取行。ICP可以减少存储引擎必须访问基表的次数和MySQL服务器必须访问存储引擎的次数。

执行如下语句：

show variables like ‘optimizer_switch’;

会返回如下结果：

index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,subquery_materialization_cost_based=on,use_index_extensions=on

其中:index_condition_pushdown这个参数就是是否开启索引下推优化的，on表示开启，off表示关闭。

可以通过如下语句设置：

SET optimizer_switch=‘index_condition_pushdown=off’;

Multi-Range Read(MRR)

Multi-Range Read和Index Condition Pushdown一样，也是在MySQL5.6版本之后引进的优化措施。MRR优化的目的是为了减少磁盘的随机IO访问，并且将随机访问转化为顺序的数据访问，所以MRR优化措施对IO-bound型的SQL查询语句可能带来极大的性能提升。

和ICP一样，也是通过【optimizer_switch】变量查询，找到返回结果中的下面两个参数：

mrr=on

mrr_cost_based=on

mrr=on表示启用，mrr_cost_based 表示是否通过基于开销的方式来启用MRR，如果mrr_cost_based=on，则即使满足了使用MRR的条件，优化器也会视当前查询的开销来决定是否使用MRR，如果我们想总是开启MRR，则可以将mrr设置为on,mrr_cost_based设置为off，如下：

SET optimizer_switch=‘mrr=off,mrr_cost_based=off’;

MRR的工作方式

1、将查询得到的辅助索引键值存放于缓存之中，注意，这时候缓存中的数据是根据辅助索引的键值排序的。

2、将缓存中的数据根据row ID(主键)进行重排序。

3、然后再根据row ID(主键)的顺序去访问。

注意2,3中的row ID，《MySQL技术内幕 InnoDB存储引擎》一书中写的是RowID，我不太清楚作者当时想表达的是按照主键，还是MySQL隐藏列ROWID进行排序，但我个人认为如果写成主键会更容易理解，因为如果我们自己创表的时候显示的指定了主键，而且排序和ROWID不一致，那么就应该是按照我们的主键进行排序，否则就达不到实现顺序IO访问的结果，下面附上MySQL官网原文：

可以看到，官网用的是两个单词：row ID，也就是行id，个人认为是可以直接理解成主键的意思，而并不单单指的是MySQL隐藏列ROWID。这里如果我理解错了，欢迎给我留言或者私信。

我们想一想，如果我们通过辅助索引查找到了辅助索引的键值和主键的键值，这时候我们需要回表，假如辅助索引和主键索引顺序相差很大，那么回表查主键B+树的时候，就是随机访问磁盘，也就是随机IO操作，而如果使用了MRR，就会按照主键进行重排序，这时候再回表就是顺序IO，所以说MRR之所以能优化是因为顺序IO访问的效率是远远大于随机IO的。

INDEX MERGE

索引合并优化，MySQL在5.0及之后的版本引入了这种优化方案。这个意思就是我们在一个表中建立了很多单列索引，然后查询的时候同时用到了多列作为条件，MySQL能够识别并分别使用单列索引进行扫描，然后将结果合并。

这种算法一般用于以下三种情况：

• or条件的并集（union 或者 union all）

• and条件的交际

• 综合前面两种情况

注意：过多的单列索引大部分情况下并不能提高性能。《高性能MySQL》一书中的作者认为，索引合并虽然是MySQL的优化方案，但是出现了这种现象，更多是说明索引建的很糟糕。

索引的种类

---

创建索引语法为：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name

[index_type]

ON tbl_name (key_part,…)

[index_option]

[algorithm_option | lock_option] …

InnoDB引擎支持如下常见的三种索引：

B+树索引的类型及使用

B+树索引就是我们常见的主键索引，唯一索引等普通索引

普通索引

如：

CREATE INDEX name_index ON test2 (name);

唯一索引

如：

ALTER TABLE test2 DROP INDEX name_index; – 先删掉上面创建的索引

CREATE UNIQUE INDEX name_index ON test2 (name);

前缀索引

前缀索引只能用在CHAR, VARCHAR, BINARY,VARBINARY及TEXT等字符类型的列上。如下：

ALTER TABLE test2 DROP INDEX name_index; – 先删掉上面创建的索引

CREATE INDEX name_index ON test2 (name(10));

name(10)就表示只把name中前10位作为索引的列

多列联合索引

可以把多列作为共同索引，如下：

CREATE INDEX id_name_index ON test2 (id,name);

全文索引

每张表最多允许创建一个全文索引，目前只有InnoDB和MyISAM两种存储引擎支持全文索引。全文索引只能在字符类型的字段创建，比如 char、varchar、text等。如下：

ALTER TABLE test2 DROP INDEX name_index; – 先删掉上面创建的索引

CREATE FULLTEXT INDEX name_index ON test2 (NAME);

请注意，全文索引的查询语法和其他索引不一样，全文索引使用如下语法进行查询：

MATCH (col1,col2,…) AGAINST (expr [search_modifier])

其中:search_modifier有如下选项：

search_modifier:

{

IN NATURAL LANGUAGE MODE

| IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION

| IN BOOLEAN MODE

| WITH QUERY EXPANSION

}

如下示例：

CREATE TABLE articles (

id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

title VARCHAR(200),

body TEXT,

FULLTEXT (title,body)

) ENGINE=InnoDB;

INSERT INTO articles (title,body) VALUES

(‘MySQL Tutorial’,‘DBMS stands for DataBase …’),

(‘How To Use MySQL Well’,‘After you went through a …’),

(‘Optimizing MySQL’,‘In this tutorial we will show …’),

(‘1001 MySQL Tricks’,‘1. Never run mysqld as root. 2. …’),

(‘MySQL vs. YourSQL’,‘In the following database comparison …’),

(‘MySQL Security’,‘When configured properly, MySQL …’);

SELECT * FROM articles WHERE MATCH (title,body) AGAINST (‘database’ IN NATURAL LANGUAGE MODE);

注意：NATURAL LANGUAGE MODE 表示的是自然语言模式，也是默认的全文索引的查询模式，所以上面示例中的查询也可以直接这么写：

SELECT * FROM articles WHERE MATCH (title,body) AGAINST (‘database’);

全文索引不得不说的事

在MySQL 5.7.6之前，MySQL全文索引只支持英文全文索引，不支持中文全文索引(只能把整个中文当成一个词语搜索)，如果需要支持中文则需要使用插件ngram来实现，MySQL从5.7.6开始才内置了ngram全文解析器，用来支持中文、日文、韩文分词。

全文索引还有很多细节需要注意的地方，本文篇幅有限，就不进一步阐述了！

哈希索引

InnoDB中的哈希索引是一种自适应哈希索引，也就是说我们不能直接创建哈希索引，目前MySQL引擎中只有Memory引擎支持创建哈希索引

索引信息分析

---

我们知道，有些查询语句是用不到索引的，那么一句查询语句到底在什么情况下用到索引，什么情况下用不到索引呢？MySQL是如何选择的呢？

新建一张表test：

CREATE TABLE test (

id int(5) NOT NULL AUTO_INCREMENT,

name varchar(50) DEFAULT NULL,

company varchar(20) DEFAULT NULL,

age tinyint(2) DEFAULT NULL,

create_time datetime DEFAULT NULL,

PRIMARY KEY (id),

KEY name_index (name),

KEY name_age_index (name,age)

) ENGINE=InnoDB AUTO_INCREMENT=120 DEFAULT CHARSET=utf8

初始化一些数据，然后先让我们执行一条语句：

SHOW INDEX FROM test

返回结果如下：

注意：第三行和第四行是一个多列索引，这里的查询时按照列显示的

查询结果的字段含义如下：

• Table：表名
  自我介绍一下，小编13年上海交大毕业，曾经在小公司待过，也去过华为、OPPO等大厂，18年进入阿里一直到现在。

深知大多数Java工程师，想要提升技能，往往是自己摸索成长或者是报班学习，但对于培训机构动则几千的学费，着实压力不小。自己不成体系的自学效果低效又漫长，而且极易碰到天花板技术停滞不前！

因此收集整理了一份《2024年Java开发全套学习资料》，初衷也很简单，就是希望能够帮助到想自学提升又不知道该从何学起的朋友，同时减轻大家的负担。

既有适合小白学习的零基础资料，也有适合3年以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

最后

由于篇幅原因，就不多做展示了
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！
以上经验的小伙伴深入学习提升的进阶课程，基本涵盖了95%以上Java开发知识点，真正体系化！**

由于文件比较大，这里只是将部分目录截图出来，每个节点里面都包含大厂面经、学习笔记、源码讲义、实战项目、讲解视频，并且会持续更新！

如果你觉得这些内容对你有帮助，可以扫码获取！！（备注Java获取）

最后

[外链图片转存中…(img-Wx3Q6hLe-1713533385408)]

[外链图片转存中…(img-Nn7R0mWU-1713533385410)]

[外链图片转存中…(img-5q7jwPMF-1713533385412)]

[外链图片转存中…(img-d1UQUrVA-1713533385414)]

[外链图片转存中…(img-F9VZhdji-1713533385416)]

[外链图片转存中…(img-gl4IKzkN-1713533385418)]

由于篇幅原因，就不多做展示了
《互联网大厂面试真题解析、进阶开发核心学习笔记、全套讲解视频、实战项目源码讲义》点击传送门即可获取！