mysql索引实现原理分析

什么是索引

目前大部分数据库系统及文件系统都采用B-Tree(B树)或其变种B+Tree(B+树)作为索引结构。B+Tree是数据库系统实现索引的首选数据结构。在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的

什么是索引？

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

MySQL5.5以后InnoDB储引擎使用的索引数据结构主要用：B+Tree

Mark：

B+Tree可以对<，<=，=，>，>=，BETWEEN，IN，以及不以通配符开始的LIKE使用索引。（MySQL5.5后）

这些事实或许会颠覆你的一些认知，比如在你读过的其他文章或书中。以上这些都属于“范围查询”，都是不走索引的！

没错，早先5.5以前优化器是不会选择通过索引搜索的，优化器认为这样取出的行多与全表扫描的行，因为还要回表查一次嘛，可能会涉及I/O的行数更多，被优化器放弃。

经过算法（B+Tree）优化后，支持对部分范围类型的扫描（得利与B+Tree数据结构的有序性）。该做法同时也违反了最左前缀原则，导致范围查询后的条件无法用到联合索引，我们在后面详细说明。

索引的优缺点

1、优点

• 索引大大减小了服务器需要扫描的数据量
• 索引可以帮助服务器避免排序和临时表
• 索引可以将随机I/O变成顺序I/O

2、缺点

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。
建立索引会占用磁盘空间的索引文件。一般情况这个问题不算严重，但如果你在一个大表上创建了多种组合索引，且伴随大量数据量插入，索引文件大小也会快速膨胀。
如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
对于非常小的表，大部分情况下简单的全表扫描更高效；
  因此应该只为最经常查询和最经常排序的数据列建立索引。（MySQL里同一个数据表里的索引总数限制为16个）

数据库存在的意义之一就是是解决数据存储和快速查找的。那么数据库的数据存在哪？没错，是磁盘，磁盘的优点是啥？便宜！缺点呢？相比内存访问速度慢。

那么你知道MySQL索引主要使用的数据结构么？

B+树！你脱口而出。

那 B+树 是什么样的数据结构？MySQL索引又是为什么选择了B+树呢？

其实最终选用 B+树 是经历了漫长的演化：

二叉排序树 → 二叉平衡树 → B-Tree（B树） → B+Tree（B+树）

有小伙伴问我“B树 跟 B-树有什么区别”？这里普及一下，MySQL数据结构只有B-Tree（B树）和B+Tree（B+树），多只是读法不同罢了，“B-Tree” 一般统称为B树，你叫他B-树也行~~

还有小伙伴提到的红黑树，是编程语言中的存储结构，不是MySQL的；如Java的HashMap就是用的链表加红黑树。

mysql应用索引注意的几个问题

为什么要使用索引？

1. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2. 可以大大加快 数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。
3. 帮助服务器避免排序和临时表。
4. 将随机IO变为顺序IO
5. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

索引这么多优点，为什么不对表中的每一个列创建一个索引呢？

1. 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
2. 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
3. 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

使用索引的注意事项？

1. 在经常需要搜索的列上，可以加快搜索的速度；
2. 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
3. 在经常需要排序的列上创 建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
4. 对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引
5. 在经常用在连接的列上，这 些列主要是一些外键，可以加快连接的速度；
6. 避免 where 子句中对宇段施加函数，这会造成无法命中索引。
7. 在使用InnoDB时使用与业务无关的自增主键作为主键，即使用逻辑主键，而不要使用业务主键。
8. 将打算加索引的列设置为 NOT NULL ，否则将导致引擎放弃使用索引而进行全表扫描
9. 删除长期未使用的索引，不用的索引的存在会造成不必要的性能损耗 MySQL 5.7 可以通过查询 sys 库的 chema_unused_indexes 视图来查询哪些索引从未被使用
10. 在使用 limit offset 查询缓慢时，可以借助索引来提高性能

mysql索引结构

Mysql索引主要使用的两种数据结构

哈希索引

对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。

BTree索引

其实最终选用 B+树 是经历了漫长的演化：

二叉排序树 → 二叉平衡树 → B-Tree（B树） → B+Tree（B+树）
现在我们详细聊聊B+Tree索引的前世今生

1、二叉排序树

理解B+树之前，简单说一下二叉排序树，对于一个节点，它的左子树的孩子节点值都要小于它本身，它的右子树的孩子节点值都要大于它本身，如果所有节点都满足这个条件，那么它就是二叉排序树。

上图是一颗二叉排序树，你可以尝试利用它的特点，体验查找37的过程：

对比根节点比45小在左边找，比12大，右侧找37

一共比较了3次，那你有没有想过上述结构的优化方式？

2、AVL树 (自平衡二叉查找树)

特点

• 对于任意一个节点，左子树和右子树的高度差不能超过1。
• 高度和节点数量之间的关系也是O(logn)的。
• 加入/删除节点后，沿着节点维护平衡性。

3、B树（Balanced Tree）多路平衡查找树 多叉的

B树是一种多路自平衡搜索树，它类似普通的二叉树，但是B书允许每个节点有更多的子节点。B树示意图如下：值得注意的是，B树的非叶子节点和叶子结点的data数据都是分开存储的，那么针对范围查询、排序等常用特性就很不友好了。

B树的特点：

所有键值分布在整个树中
任何关键字出现且只出现在一个节点中
搜索有可能在非叶子节点结束
在关键字全集内做一次查找，性能逼近二分查找算法
  为了提升效率，要尽量减少磁盘I/O的次数。实际过程中，磁盘并不是每次严格按需读取，而是每次都会预读。

磁盘读取完需要的数据后，会按顺序再多读一部分数据到内存中，这样做的理论依据是计算机科学中注明的局部性原理：

由于磁盘顺序读取的效率很高(不需要寻址时间，只需很少的旋转时间)，因此对于具有局部性的程序来说，预读可以提高I/O效率.预读的长度一般为页(page)的整倍数。
MySQL(默认使用InnoDB引擎)，将记录按照页的方式进行管理，每页大小默认为16K(可以修改)。
B-Tree借助计算机磁盘预读机制:

每次新建节点的时候，都是申请一个页的空间，所以每查找一个节点只需要一次I/O;因为实际应用当中，节点深度会很少，所以查找效率很高.

那么最终版的 B+树 是如何做的呢？

4、B+ Tree (B+树是B树的变体，也是一种多路搜索树)

从图中也可以看到，B+树与B树的不同在于：

所有关键字存储在叶子节点，非叶子节点不存储真正的data，从而可以快速定位到叶子结点。
为所有叶子节点增加了一个链指针，意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同，很适合查找范围数据。说明支持范围查询和天然排序。
因此，B+Tree可以对<，<=，=，>，>=，BETWEEN，IN，以及不以通配符开始的LIKE使用索引。且如果用到了该索引，排序功能的消耗大大减少。

B+树的优点：

比较的次数均衡，减少了I/O次数，提高了查找速度，查找也更稳定。

B+树的磁盘读写代价更低
B+树的查询效率更加稳定
  要知道的是，你每次创建表，系统会为你自动创建一个基于ID的聚集索引（上述B+树），存储全部数据；你每次增加索引，数据库就会为你创建一个附加索引（上述B+树），索引选取的字段个数就是每个节点存储数据索引的个数，注意该索引并不存储全部数据。

四、为什么MySQL索引选择了 B+树 而不是 B树？

B+树更适合外部存储(一般指磁盘存储),由于内节点(非叶子节点)不存储data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用B+树单次磁盘I/O的信息量相比较B树更大，I/O效率更高。
mysql是关系型数据库，经常会按照区间来访问某个索引列，B+树的叶子节点间按顺序建立了链指针，加强了区间访问性，所以B+树对索引列上的区间范围查询很友好。而B树每个节点的key和data在一起，无法进行区间查找。

MyISAM和InnoDB实现BTree索引方式的区别

MyISAM

B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
MyISAM 的索引方式也叫做“非聚集索引”,之所以这么称呼是为了与 InnoDB的聚集索引区分。

InnoDB

其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”，而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，在走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。 PS：整理自《Java工程师修炼之道》

虽然 InnoDB 也使用 B+Tree 作为索引结构,但具体实现方式却与 MyISAM 截然不同。

1.第一个重大区别是 InnoDB 的数据文件本身就是索引文件。从上文知道,MyISAM 索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。

而在InnoDB 中,表数据文件本身就是按 B+Tree 组织的一个索引结构,这棵树的叶点data 域保存了完整的数据记录。这个索引的 key 是数据表的主键,因此 InnoDB 表数据文件本身就是主索引。
2.InnoDB 要求表必须有主键(MyISAM 可以没有),如果没有显式指定,则 MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,类型为长整形。

同时,请尽量在 InnoDB 上采用自增字段做表的主键。因为 InnoDB 数据文件本身是一棵B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。
这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。

2.第二个与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说,InnoDB 的所有辅助索引都引用主键作为 data 域。
聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引（回表）:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。

引申:为什么不建议使用过长的字段作为主键?

因为所有辅助索引都引用主索引,过长的主索引会令辅助索引变得过大。

聚簇索引与非聚簇索引

InnoDB 使用的是聚簇索引, 将主键组织到一棵 B+树中, 而行数据就储存在叶子节点上, 若使用"where id = 14"这样的条件查找主键, 则按照 B+树的检索算法即可查找到对应的叶节点, 之后获得行数据。 若对 Name 列进行条件搜索, 则需要两个步骤:
第一步在辅助索引 B+树中检索 Name, 到达其叶子节点获取对应的主键。
第二步使用主键在主索引 B+树种再执行一次 B+树检索操作, 最终到达叶子节点即可获取整行数据。

MyISM 使用的是非聚簇索引, 非聚簇索引的两棵 B+树看上去没什么不同, 节点
的结构完全一致只是存储的内容不同而已, 主键索引 B+树的节点存储了主键, 辅助键索引B+树存储了辅助键。 表数据存储在独立的地方, 这两颗 B+树的叶子节点都使用一个地址指向真正的表数据, 对于表数据来说, 这两个键没有任何差别。 由于索引树是独立的, 通过辅助键检索无需访问主键的索引树。

为了更形象说明这两种索引的区别, 我们假想一个表如下图存储了 4 行数据。 其中Id 作为主索引, Name 作为辅助索引。 图示清晰的显示了聚簇索引和非聚簇索引的差异

覆盖索引介绍

什么是覆盖索引

如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道InnoDB存储引擎中，如果不是主键索引，叶子节点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的，不做回表操作！

覆盖索引使用实例

现在我创建了索引(username,age)，我们执行下面的 sql 语句

select username , age from user where username = 'Java' and age = 22

在查询数据的时候：要查询出的列在叶子节点都存在！所以，就不用回表。

选择索引和编写利用这些索引的查询的3个原则

1. 单行访问是很慢的。特别是在机械硬盘存储中(SSD的随机I/O要快很多，不过这一点仍然成立）。如果服务器从存储中读取一个数据块只是为了获取其中一行，那么就浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引，用以提升效率。
2. 按顺序访问范围数据是很快的，这有两个原因。第一，顺序1/0不需要多次磁盘寻道，所以比随机I/O要快很多（特别是对机械硬盘）。第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GROUPBY查询也无须再做排序和将行按组进行聚合计算了。
3. 索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么存储引擎就
   不需要再回表查找行。这避免了大量的单行访问，而上面的第1点已经写明单行访
   问是很慢的。

为什么索引能提高查询速度

先从 MySQL 的基本存储结构说起

• 各个数据页可以组成一个双向链表
• 每个数据页中的记录又可以组成一个单向链表
  • 每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录
  • 以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

所以说，如果我们写select * from user where indexname = 'xxx’这样没有进行任何优化的sql语句，默认会这样做：

1. 定位到记录所在的页：需要遍历双向链表，找到所在的页
2. 从所在的页内中查找相应的记录：由于不是根据主键查询，只能遍历所在页的单链表了

很明显，在数据量很大的情况下这样查找会很慢！这样的时间复杂度为O（n）。

使用索引之后

索引做了些什么可以让我们查询加快速度呢？其实就是将无序的数据变成有序(相对)：

要找到id对应的记录简要步骤：

很明显的是：没有用索引我们是需要遍历双向链表来定位对应的页，现在通过 “目录” 就可以很快地定位到对应的页上了！（二分查找，时间复杂度近似为O(logn)）

其实底层结构就是B+树，B+树作为树的一种实现，能够让我们很快地查找出对应的记录。

最左前缀原则

MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city)，而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：

select * from user where name=xx and city=xx ; ／／可以命中索引
select * from user where name=xx ; // 可以命中索引
select * from user where city=xx ; // 无法命中索引            

这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的。

由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。

注意避免冗余索引

冗余索引指的是索引的功能相同，能够命中 就肯定能命中 ，那么 就是冗余索引如（name,city ）和（name ）这两个索引就是冗余索引，能够命中后者的查询肯定是能够命中前者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

MySQL 5.7 版本后，可以通过查询 sys 库的 schema_redundant_indexes 表来查看冗余索引

Mysql如何为表字段添加索引？？？

1.添加PRIMARY KEY（主键索引）

ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 

2.添加UNIQUE(唯一索引)

ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 

3.添加INDEX(普通索引)

ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)

ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 

5.添加多列索引

ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

MySQL索引
索引的优点
最典型的例子就是查新华字典，通过查找目录快速定位到查找的字

大大减少了服务器需要扫描的数量
帮助服务器避免排序和临时表
将IO变成顺序IO
尽可能的降低磁盘的寻址时间，也就是局部性原理，就是很大一部分数据在未来的一段时间被连续访问
在复制1G压缩包 和 1G小文件，前者的速度会大于后者
减少IO的量，例如写SQL语句的时候，不要写 select *
减少IO的次数，一次IO能搞定的事，不使用3次IO
索引的用处
快速查找匹配where子句的行
从consideration中消除行，如果可以在多个索引之间进行选择，mysql通常会使用栈找到最少行的索引
如果表具有多列索引，则优化器可以使用索引的最左匹配前缀来查找
当有表连接的时候，从其他表检测行数据
查找特定索引列min或max值
如果排序或分组是，在可用索引的最左前缀上完成的，则对表进行排序和分组
在某些清空下，可以优化查询以检索值而无需查询数据行
索引的分类
主键索引
如果你在创建索引的时候，使用的是主键这个值，那么就是主键索引，primary key

我们建表的时候，例如下面这个建表语句

CREATE TABLE t_blog_sort (
uid varchar(32) NOT NULL COMMENT ‘唯一uid’,
sort_name varchar(255) DEFAULT NULL COMMENT ‘分类内容’,
content varchar(255) DEFAULT NULL COMMENT ‘分类简介’,
create_time timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’ COMMENT ‘创建时间’,
update_time timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’ COMMENT ‘更新时间’,
status tinyint(1) unsigned NOT NULL DEFAULT ‘1’ COMMENT ‘状态’,
sort int(11) DEFAULT ‘0’ COMMENT ‘排序字段，越大越靠前’,
click_count int(11) DEFAULT ‘0’ COMMENT ‘点击数’,
PRIMARY KEY (uid)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=‘博客分类表’;
这里面有使用到 PRIMARY KEY (uid)，这就是主键索引

唯一索引
唯一索引 类似于普通索引，索引列的值必须唯一

唯一索引和主键索引的区别就是，唯一索引允许出现空值，而主键索引不能为空

create unique index index_name on table(column)
或者创建表时指定

unique index_name column
普通索引
当我们需要建立索引的字段，既不是主键索引，也不是唯一索引

那么就可以创建一个普通索引

create index index_name on table(column)
或者创建表时指定

create table(…, index index_name column)
全文索引
lunce、solr和ElasticSearch就是做全文检索的，里面涉及到了倒排索引的概念，mysql很少使用全文索引。

要用来查找文本中的关键字，不是直接与索引中的值相比较，像是一个搜索引擎，配合 match against 使用，现在只有char，varchar，text上可以创建索引，在数据量比较大时，先将数据放在一个没有全文索引的表里，然后在利用create index创建全文索引，比先生成全文索引在插入数据快很多。

组合索引
目前，在业务不是特别复杂的时候，可能使用一个列作为索引，或者直接采用主键索引即可，但是如果业务变得复杂的时候，就需要用到组合索引，通过对多个列建立索引。

组合索引的用处，假设我现在表有个多个字段：id、name、age、gender，然后我经常使用以下的查询条件

select * from user where name = ‘xx’ and age = xx
这个时候，我们就可以通过组合 name 和 age 来建立一个组合索引，加快查询效率，建立成组合索引后，我的索引将包含两个key值

在多个字段上创建索引，遵循最左匹配原则

alter table t add index index_name(a,b,c);
索引的使用与否
索引的使用
MySQL每次只使用一个索引，与其说 数据库查询只能用一个索引，倒不如说，和全表扫描比起来，去分析两个索引 B+树更耗费时间，所以where A=a and B=b 这种查询使用（A，B）的组合索引最佳，B+树根据（A，B）来排序。

主键，unique字段
和其他表做连接的字段需要加索引
在where 里使用 >, >=, = , <, <=, is null 和 between等字段。
使用不以通配符开始的like，where A like ‘China%’
聚合函数里面的 MIN()， MAX()的字段
order by 和 group by字段
何时不使用索引
表记录太少
数据重复且分布平均的字段（只有很少数据的列）；
经常插入、删除、修改的表要减少索引
text，image 等类型不应该建立索引，这些列的数据量大（加入text的前10个字符唯一，也可以对text前10个字符建立索引）
MySQL能估计出全表扫描比使用索引更快的时候，不使用索引
索引何时失效
组合索引为使用最左前缀，例如组合索引（A，B），where B = b 不会使用索引
like未使用最左前缀，where A like “%China”
搜索一个索引而在另一个索引上做 order by， where A = a order by B，只会使用A上的索引，因为查询只使用一个索引。
or会使索引失效。如果查询字段相同，也可以使用索引。例如 where A = a1 or A = a2（生效），where A=a or B = b （失效）
在索引列上的操作，函数upper()等，or、！ = （<>）,not in 等
面试技术名词
回表
首先我们需要知道，我们建立几个索引，就会生成几棵B+Tree，但是带有原始数据行的B+Tree只有一棵，另外一棵树上的叶子节点带的是主键值。

例如，我们通过主键建立了主键索引，然后在叶子节点上存放的是我们的数据

image-20200629094621998

当我们创建了两个索引时，一个是主键，一个是name，它还会在生成一棵B+Tree，这棵树的叶子节点存放的是主键，当我们通过name进行查找的时候，会得到一个主键，然后在通过主键再去上面的这个主键B+Tree中进行查找，我们称这个操作为 回表

image-20200629094800800

当我们的SQL语句使用的是下面这种的时候，它会查找第一颗树，直接返回我们的数据

select * from tb where id = 1
当我们使用下面这种查询的时候，它会先查找第二棵树得到我们的主键，然后拿着主键再去查询第一棵树

select * from tb where name = ‘gang’
回表就是通过普通列的索引进行检索，然后再去主键列进行检索，这个操作就是回表

但是我们在使用检索的时候，尽量避免回表，因为这会造成两次B+Tree的查询，假设一次B+Tree查询需要三次IO操作，那么查询两次B+Tree就需要六次IO操作。

索引覆盖
我们看下面的两个SQL语句，看看它们的查询过程是一样的么？

select * from tb where id = 1
select name from tb where name = zhou
答案是不一样的，首先我们看第二个语句，就是要输出的列中，就是我们的主键，当我们通过name建立的B+Tree进行查询的时候

image-20200629094800800

我们可以直接找到我们的数据，并得到主键，但是因为我们要返回的就是name，此时说明数据存在了，那么就直接把当前的name进行返回，而不需要通过主键再去主键B+Tree中进行查询。

这样一个不需要进行回表操作的过程，我们称为索引覆盖

最左匹配
这里提到的 最左匹配 和 索引下推 都是针对于组合索引的。

例如，我们有这样一个索引

name age：组合索引
必须要先匹配name，才能匹配到age。这个我们就被称为最左匹配

例如下面的几条SQL语句，那些语句不会使用组合索引

where name = ? and age = ?
where name = ?
where age = ?
where age = ? and name = ?
根据最左匹配原则，我们的 3 不会使用组合索引的。

那为什么4的顺序不一样，也会使用组合索引呢？

其实内部的优化器会进行调整，例如下面的一个连表操作

select * from tb1 join tb2 on tb1.id = tb2.id
其实在加载表的时候，并不一定是先加载tb1，在加载tb2，而是可能根据表的大小决定的，小的表优先加载进内存中。

索引下推
在说索引下推的时候，我们首先在举两个例子

select * from tb1 where name = ? and age = ?
在mysq 5.6之前，会先根据name去存储引擎中拿到所有的数据，然后在server层对age进行数据过滤

在mysql5.6之后，根据name 和 age两个列的值去获取数据，直到把数据返回。

通过对比能够发现，第一个的效率低，第二个的效率高，因为整体的IO量少了，原来是把数据查询出来，在server层进行筛选，而现在在存储引擎层面进行筛选，然后返回结果。我们把这个过程就称为 索引下推

优化器
CBO
基于成本的优化

RBO
基于规则的优化

image-20200629110258878

索引匹配方式
全值匹配
全值匹配指的是和索引中所有的列进行匹配

explain select * from staffs where name = ‘July’ and age = 23 and pos = ‘dev’
而我们建立了一个 包含 name、age、pos的组合索引，使用上面的SQL语句，就会进行全值匹配

匹配最左前缀
只匹配前面的几列

explain select * from staffs where name = ‘July’ and age = 23
这个时候，只使匹配了前面两个列，而没有使用第三个列

现在我们使用下面的SQL语句进行验证，但我们输出值只包含ID的时候

explain select id from staffs where id = 1
我们查看其任务计划，在某尾有 Extra字段，如果是Using index 表示是使用了覆盖索引

image-20200629144438346

然后我们在查看下面这条SQL语句

explain select * from staffs where id = 1
通过查看任务计划，发现extra字段是NULL，说明没有使用覆盖索引

image-20200629145948288

匹配列前缀
可以匹配某一列值的开头部分

explain select * from staffs where name = ‘J%’
explain select * from staffs where name = ‘%y’
匹配范围值
可以查找某个范围的数据

explain select * from staffs where name > ‘Mary’
精确匹配某一列并范围匹配另外一列
可以查询某一列的全部和第二列的部分

explain select * from staffs where name = “July” and age > 25
只访问索引的查询
查询的时候值需要访问索引，不需要访问数据行，本质上就是索引覆盖

explain select name,age,pos from staffs where name=“July” and age=25 and pos = “dev”
哈希索引
概念
基于哈希的实现，只有精确匹配索引所有的列的查询才有效，在mysql中，只有memory的存储引擎显式支持哈希索引，哈希索引自身只需存储对应的hash值，索引索引的结构十分紧凑，这让哈希索引查找的速度非常快。

哈希索引的限制
哈希索引值包含哈希值和行指针，而不存储字段值。索引不能使用索引中的值来避免读取行
哈希索引数据并不是按照索引值顺序存储的，所以无法进行排序
哈希索引不支持部分列匹配查找，哈希索引是使用索引列的全部内容来计算哈希值
哈希索引支持等值比较查询，也不支持任何范围查询
访问哈希索引的数据非常快，除非有很多哈希冲突，当出现哈希冲突的时候，存储引擎必须遍历链表中的所有行指针，逐行进行比较，知道找到所有符合条件的行
哈希冲突比较多的话，维护的代价也会很高
聚簇索引和非聚簇索引
聚簇索引
InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，聚簇索引就是按每张表的主键构造一棵B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚簇索引的叶子节点称为数据也，这个特性就决定了索引组织表中的数据也是索引的一部分。

一句话来说：将索引和数据放在一起的，就称为聚簇索引

我们日常的工作中，根据实际情况自行添加的索引，都是辅助索引或者称为普通索引，辅助索引就是为了查找主键索引的二级索引，先找到主键索引然后再通过主键索引找数据，但是可能会存在回表的问题。

聚簇索引的优点
数据访问更快，因为聚簇索引将索引和数据保存在一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
聚簇索引对主键的排序和范围查找速度非常快
聚簇索引的缺点
插入速度严重依赖于排序，按照主键的顺序插入是最快的方式，否者会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列作为主键
更新主键的代价很高，因为将会导致被更新的行移动，因此，对于InnoDB表，我们一般定义主键不可更新
二级索引访问需要两次索引查找，第一次找到主键值，第二次 根据主键值查找行数据，一般我们需要尽量避免出现索引的二次查找，这个时候，用到的就是索引的覆盖
非聚簇索引
非聚簇索引也被称为辅助索引，辅助索引在我们访问数据的时候总是需要两次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到主键值，然后在通过主键值找到数据行的数据页，在通过数据页中的Page Directory找到数据行。

InnoDB辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了行数据的聚簇索引建。辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在InnoDB中有时也称为辅助索引为二级索引

image-20200629113413737

组合索引
当包含多个列为索引，需要注意的是正确的顺序依赖于该索引的查询，同时需要考虑如何更好的满足排序和分组的需要

image-20200629160704401

第4个不走索引，是因为不满足最左匹配原则

第5个，因为跨过了b，所以只走a的索引

优化细节
当使用索引列进行查询的时候，尽量不要使用表达式，把计算放到业务层而不是数据库层

select actor_id from actor where actor_id = 4
select actor_id from actor where actor_id+1 = 5
第一条语句走索引

image-20200629161629049

而第二条语句没有走主键索引

image-20200629161641522

尽量使用主键查询，而不是其它索引，因为主键查询不会触发回表操作

使用前缀索引

有时候需要索引很长的字符串，这会让索引变得大且满，通常情况下可以使用某个列开始的部分字符串，这样大大的节约了索引空间，从而提高索引效率，但这会降低索引的选择性，索引的选择性是指不重复的索引值和数据表记录总数的比值，范围从1/#T 到 1 之间，索引的选择性越高，则查询效率越高，因为选择性更高的索引可以让mysql在查找的时候过滤掉更多的行。

一般情况下，某个列前缀的选择性也是足够高的，足以满足查询的性能，但是对应BLOG，TEXT，VARCHAR类型的列，必须要使用前缀索引，因为mysql不允许索引这些列的完整长度，使用该方法的诀窍在于选择足够长的前缀以保证较高的选择性，通过又不能太长 。

使用索引扫描来进行排序
union、all、in、or都能使用索引，但是推荐使用in
explain select * from actor where actor_id = 1 union all select * from actor where actor_id = 2

explain select * from actor where actor_id in (1,2);

explain select * from actor where actor_id = 1 or actor_id = 2;

– 关于or到底走不走索引，必须根据实际情况进行考虑
范围列可以使用到索引
例如 范围条件是：<、<=、>、>=、between

范围列可以用到索引，但是范围列后面的列无法用到索引，索引最多用于一个范围列，所以一般如果我们使用组合索引的时候，最好不要使用范围查找

image-20200629160704401

如倒数第一个所示，因为中间b使用了范围查找，所以后面的c是无法使用索引的，只能是a和b才能使用索引

强制类型转换会让索引失效，进行全表查询
例如下面这样一个例子所示，我们对 phone字段进行了强制类型转换

explain select * from user where phone = 13800001234 – 不会触发索引（触发了字符串到整型转换）
explain select * from user where phone = ‘13800001234’ – 触发索引
更新十分频繁，数据区分度不高的字段上不宜建立索引
更新会变更B+树，更新 频繁的字段建立索引会大大降低数据库性能

类似于性别这列的区分度不高的字段，建立索引是没有意义的，不能有效的过滤数据

一般区分度在百分80以上的时候，就可以建立索引，区分度可以使用 count(distinct(列名)) / count(*) 来进行计算

创建索引的列，不允许为null，可能会得到不符合预期的结果
当需要进行表连接的时候，最好不要超过三张表，因为需要join的字段，数据类型必须一致（阿里规约）
允许数据的冗余，从而加快查询的效率
目前是范式和反范式的混合使用
能使用limit的时候，尽量使用limit
单表索引建议控制在5个以内
单索引字段不允许超过5个（组合索引）
创建索引的时候应该尽量避免以下错误的概念
索引不是越多越好，不要在不了解系统的情况下进行优化