简述一下索引的匹配原则_mysql基础—索引

针对不同的应用场景创建合适的索引对于优化mysql查询速度非常关键，今天一起来学习一下mysql索引相关的原理性内容。

mysql索引的数据结构

如图是以二叉搜索树为数据结构建立起的索引，这样的索引数据结构树高是O(log2n), 我们知道mysql数据记录和索引树节点都是存在磁盘上的，mysql磁盘IO的次数是和索引树高成正比的。一张有1千万条记录的表索引树的树高大约是23，也就是近乎23次磁盘IO，以二叉搜索树为数据结构的索引树显然是不合适的。

1. B树

mysql并没有使用二叉搜索树组织索引，而是基于B树，B树是由二叉搜索树演变的，有以下几个特点：

• m叉搜索树
• 每个节点的key左侧分支都不大于key，右侧分支不小于key
• 每个节点n-1个key和n个指针(「m/2<=n<=m)
• 一个节点中key非递减排列
• 指针或指向另一个节点，或为null
• 叶子节点、非叶子节点都存储数据

由于B树在一个节点可以存储更多的key，因此B树索引树树高比二叉搜索树要低很多。但直接拿B树做索引还存在一些问题，比如SQL中的范围查询，直接用B树索引就无法很好的支持。

2. B+树

B+树满足以下性质：

• m叉搜索树
• 每个节点的key左侧分支都不大于key，右侧分支不小于key
• 每个节点n-1个key和n个指针(「m/2<=n<=m)
• 一个节点中key非递减排列
• 指针或指向另一个节点，或为null
• 叶子节点，非叶子节点都存储数据
• 非页节点不再存储数据，数据统一存储在叶子节点
• 叶节点之间增加了链表

相比于B树B+增加了两个比较重要关键的性质(加黑的两条)

“非叶子节点不再存储数据，数据统一存储在叶子节点”可以减小非叶子索引节点的大小，让一个磁盘页存储尽可能多的索引key，降低树高。

“叶子节点之间增加链表”可以使索引很好的支持SQL中的范围查询。

在总结为什么B+树索引效率高的之前回顾一下我们学操作系统时所学过的两个小知识：程序访问局部性原理和磁盘预读。

• 程序访问局部性原理：程序访问某个数据时，其下个指令大概率会访问其附近数据(局部性原理)
• 磁盘预读：磁盘数据按页组织，一页大小一般是4KB，操作系统一次加载磁盘中的一页或几页数据到内存，减少磁盘IO

3. 为什么B+树索引效率高

• 充分利用局部性原理和磁盘预读，将索引节点的大小设置为磁盘页(4K)的大小
• 树的出度通常很大，可达几百，因此索引树的高度很低，一般不超过3
• 能够很好的支持单点查询和范围查询

InnoDB和MyISAM存储引擎索引实现的差异

mysql常用的存储引擎主要是InnoDB和MyISAM，两种引擎分别有不同的特点。比如InnoDB支持事务，行锁，而MyISAM不支持事务，支持表级别锁等等。在两种存储引擎在索引实现上也存在一定的差异，下面我们一起来看下两种存储引擎在索引实现上差别在哪儿。

存储引擎对于主键字段和非主键字段在建立索引时会有不同，在分析存储引擎索引实现时需要分别看看它们的主键索引和普通索引(非主键索引)的差异。

1.MyISAM索引实现

• 主键索引(Primary index)

主键索引是在数据表主键上建立的索引，MyISAM主键索引有以下几个特点：

(1). 基于B+树实现

(2). 叶节点存储的是数据的地址

(3). 独立连续的区域存储数据行

• 辅助索引(Secondary index)

辅助索引是在非主键字段上建立的索引：

(1). 基于B+树实现

(2). 叶节点存储的是数据的地址

(3). 独立连续的区域存储数据行

可以看到MyISAM的主键索引和辅助索引在实现上是没有差别的，仅仅把索引的列改成对应的索引字段。

2.InnoDB索引实现

• 主键索引(Primary index)

(1) 基于B+树实现

(2) 叶节点存储的是数据行

• 辅助索引(Secondary index)

(1)基于B+树实现

(2)叶节点存储的是对应的主键

InnoDB辅助索引叶子节点存储的是记录对应的主键，然后利用主键去索引对应的记录。也就是说利用辅助去索引记录一定会用到InnoDB的主键索引，InnoDB一定会建立一个主键索引，不论表中是否指定了主键。

3.MyISAM和InnoDB索引差异总结

• MyISAM索引和数据分开存储(非聚簇索引)
• MyISAM索引的叶子存储指针，主键索引和辅助索引无太大差异
• InnoDB的主键索引存储的是数据内容，索引和数据存在一起 (聚簇索引)
• InnoDB辅助索引叶子节点存储记录的主键，再利用主键索引查询记录
• InnoDB有且只有一个聚簇索引

联合索引和最左前缀匹配

• 联合索引

前面两部分介绍的内容都是基于一个字段上的索引，mysql还支持建立联合索引，联合索引是在多个字段上建立起一个索引。例如在下面这个表上建一个的联合索引：

联合索引索引树的构建与索引字段的顺序有关。联合索引虽然是多个字段共同构成索引，但索引树和单列索引一样，仍然是一个，其实单列索引也是一种特殊的联合索引。例子中，联合索引最左索引字段是col3，因此索引树以col3字段构建，对于col3相同的字段再以col2字段做索引。逻辑类似于：group by col3 , clo2。

• 最左前缀匹配原则

最左前缀匹配原则：在mysql上建立联合索引会遵循最左匹配原则，即在建立索引时会以最左字段作为主索引，同时where查询条件中也须包含索引最左字段才能利用到联合索引。

我们通过一个例子对where查询条件中必须包含索引最左字段进行说明。

在tb_user表上建立了三列联合索引，最左字段为name。

查询条件中不包含name字段

查询条件中包含name字段

可以看到当我们在查询条件中不包含name(建立联合索引的最左字段)字段时，查询语句是没有用到联合索引的，包含name字段时查询语句就能够用到联合索引"ids_name_addr_pwd"。

建立一个联合索引，实际上相当于建立了(col1)、(col1,col2),(col1,col2,col3)三个索引，相比于建立多个单个索引而言使用联合索引可以大大减小维护索引的开销

索引使用建议

• 不是索引越多，性能就好(考虑索引对更新的影响)
• 强烈建议使用自增主键(主键不随机、性能保第一)
• 尽量不要在选择性不多的列上添加索引
• 索引列字段值不宜过长
• 避免在索引上使用like(like性能本身就很低，且like ‘%...’会导致全表扫描)
• 尽量使用联合(多列)索引、少创建多个单列索引