MySQL 索引机制原理

1.索引简介

1.1什么是索引？

在创建表时可以为某些合适的字段添加索引，它可以在查询数据时提高效率。

1.2为什么要有索引？

当面对海量数据时如果没有索引，我们的查询会变得非常慢，加索引可以有效解决这个问题。

2.理解索引

2.1为什么查找慢？

我们都知道MySQL要进行持久化存储，势必要将内存的数据放在磁盘上。但是面对海量数据的查找时，难道MySQL会将所有数据全部加载到内存中吗？显然不可能，内存空间是宝贵的资源而且这么做效率太低。实际上MySQL是将数据以一定大小加载到内存上的例如16 KB

所以普通的查询可能会导致大量的磁盘和内存的IO交互。即如何减少IO就成了提高查找效率的关键了。

2.2索引的原理

页目录
我们在看《谭浩强C程序设计》这本书的时候，如果我们要看<指针章节>，找到该章节有两种做法

• 从头逐页的向后翻，直到找到目标内容
• 通过书提供的目录，发现指针章节在234页(假设)，那么我们便直接翻到234页。同时，查找目录的
• 方案，可以顺序找，不过因为目录肯定少，所以可以快速提高定位
• 本质上，书中的目录，是多花了纸张的，但是却提高了效率
• 所以，目录，是一种“空间换时间的做法”

MySQL在创建索引时也引入了目录，Page是MySQL与磁盘交互的基本单位(与操作系统的Page区分)。具体结构如下图。

实际上这就是一个B+树结构，为何选择B+树？

• 节点不存储data，这样一个节点就可以存储更多的key。可以使得树更矮，所以IO操作次数更少。
• 叶子节点相连，更便于进行范围查找

聚簇索引 VS 非聚簇索引

MyISAM 存储引擎-主键索引
MyISAM 引擎同样使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址。下图为 MyISAM
表的主索引， Col 1 为主键。

其中， MyISAM 最大的特点是，将索引Page和数据Page分离，也就是叶子节点没有数据，只有对应数据
的地址 ，MyISAM 这种用户数据与索引数据分离的索引方案，叫做非聚簇索引

当然， MySQL 除了默认会建立主键索引外，我们用户也有可能建立按照其他列信息建立的索引，一般这
种索引可以叫做辅助（普通）索引。
对于 MyISAM ,建立辅助（普通）索引和主键索引没有差别，无非就是主键不能重复，而非主键可重复。

同样， InnoDB 除了主键索引，用户也会建立辅助（普通）索引，我们以上表中的 Col 3 建立对应的辅助
索引如下图：

可以看到， InnoDB 的非主键索引中叶子节点并没有数据，而只有对应记录的key值。

所以通过辅助（普通）索引，找到目标记录，需要两遍索引：首先检索辅助索引获得主键，然后用主键
到主索引中检索获得记录。这种过程，就叫做回表查询

为何 InnoDB 针对这种辅助（普通）索引的场景，不给叶子节点也附上数据呢？原因就是太浪费空间
了。

索引创建原则

• 比较频繁作为查询条件的字段应该创建索引
• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
• 更新非常频繁的字段不适合作创建索引
  比较频繁作为查询条件的字段应该创建索引
• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
• 更新非常频繁的字段不适合作创建索引
• 不会出现在where子句中的字段不该创建索引