mysql 索引的层数对查询效率影响大吗

一、索引的结构

索引是帮助mysql高效获取数据的排好序的数据结构

1.1 使用二叉树

使用二叉树的数据结构情况下对colum2列进行索引如下图：

如果对colum1列使用二叉树作为索引呢？

插入数据建立索引的时候退化成了一个链表

总结：对于单边增长的数据，使用二叉树提升的效率很低，依然是线性查找，不适合作为索引使用

1.2 红黑树

红黑树的特性:

（1）每个节点或者是黑色，或者是红色。

（2）根节点是黑色。

（3）每个叶子节点（NIL）是黑色。 [注意：这里叶子节点，是指为空(NIL或NULL)的叶子节点！]

（4）如果一个节点是红色的，则它的子节点必须是黑色的。

（5）从一个节点到该节点的子孙节点的所有路径上包含相同数目的黑节点。

对于单边增长的数据，查询性能略有提升。
如果数据量很庞大，树的高度依然很高，查询效率仍然很低。查询一个节点，需要做一次磁盘IO，效率依然很低。
总结：树的查询时间跟树的高度有关。对于大数据量的数据，树的高度不可控。

1.3 B-Tree

特点：

• 叶节点具有相同的深度，叶节点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排列

b树作为索引的结构为：

1.4 B+Tree（B树的变种）

特点：

• 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
• 叶子节点包含所有索引字段，包含所有的数据
• 叶子节点用指针连接，提高区间访问的性能

根节点作为一页，全部放入内存中进行查找，使用高效率的查找算法（比如二分查找），快速定位数据的位置。
查询页大小，默认16kb

mysql> SHOW GLOBAL STATUS LIKE 'Innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

与磁盘io耗费性能，在内存中查找数据的位置基本可以忽略不计。
一般根节点都是常驻内存的，也就是说页已经在内存中了，此时不需要到磁盘中读取数据，直接从内存中读取即可
那是否可以把索引数据全部放入根节点呢，放入内存中比对？数据量太大，内存不够，内存中几千万数据进行比对，效率也很低。

为什么是16kb?
以最大的bigint来计算，8字节，指向下一层的节点的磁盘空间地址 6字节，则一个节点能存储的数据量为
16384/(8+6)=1170个元素
假设底层一个节点为1kb数据，则存储16个元素
假设b+树 3层，则可以存储的元素为：1170117016=2190w

b+树作为索引的结构为：

面试题：为什么mysql选择b+树作为索引结构而不是b树？

• 对于树结构来说，影响查找元素的因素是树的高度，b树非叶子存储了索引和元素，则节点能存储的索引数量就更少，如果存储相同数量的元素，则b树的高度更高，查找效率更慢。非叶子节点能存储的索引数量决定了树的高度。比如：存储2000w元素，b+树 3层，而b树则为：将近6层 20,000,000/16^6
• B树查找性能是不稳定的（如果要查找的数据分别在根节点和叶子节点，他们的性能就会不同）。但B+树的每一次都是稳定的
• 叶子节点之间存在指针，便于范围查询

1.5 哈希索引

存储引擎对所有的索隐列计算出一个哈希码，将哈希码存储在索引中，同时哈希表中保存每个数据行的指针。这样，对于此种索引查找速度是非常快的。出现哈希值碰撞的话，索引会以链表的形式存放多个记录指针到同一个哈希条目中。
特点：

• 对索引的key进行一次hash计算就可以定位出数据存储的位置
• 很多时候Hash索引要比B+ 树索引更高效
• 仅能满足 “=”，“IN”，不支持范围查询
• hash冲突问题

哈希算法：MD5,CRC16

二、存储引擎索引实现

插件式存储引擎是 MySQL 数据库最重要的特性之一，用户可以根据应用的需要选择如何存储和索引数据、是否使用事务等。MySQL 默认支持多种存储引擎，以适用于不同领域的数据库应用需要，用户可以通过选择使用不同的存储引擎提高应用的效率，提供灵活的存储，用户甚至可以按照自己的需要定制和使用自己的存储引擎，以实现最大程度的可定制性。
MySQL 5.0 支持的存储引擎包括 MyISAM、InnoDB、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED 等，其中 InnoDB 和 BDB 提供事务安全表，其它存储引擎都是非事务安全表。
下面列出几种常见的存储引擎，并对比之间的区别。

特点	MyISAM	InnoDB	MEMORY	MERGE	NDB
存储限制	有	64TB	有	没有	有
事务安全		支持
锁机制	表锁	行锁	表锁	表锁	行锁
B 树索引	支持	支持	支持	支持	支持
哈希索引			支持		支持
全文索引	支持
集群索引		支持
数据缓存		支持	支持		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩	支持
空间使用	低	高	N/A	低	低
内存使用	低	高	中等	低	高
批量插入的速度	高	低	高	高	高
支持外键		支持

存储引擎是针对表级别生效的

2.1 MyISAM

MyISAM 不支持事务、也不支持外键，其优势是访问的速度快，对事务完整性没有要求或者以 SELECT、INSERT 为主的应用基本上都可以使用这个引擎来创建表。

每个 MyISAM 在磁盘上存储成 3 个文件，其文件名都和表名相同，但扩展名分别是：

.frm（存储表定义）

.MYD（MYData，存储数据）

.MYI（MYIndex，存储索引）

数据文件和索引文件可以放置在不同的目录，平均分布 IO，获得更快的速度。

索引文件和数据文件是分离的，非聚集

非聚集索引：索引的页节点不包含数据，索引和数据文件在不同的文件中

2.2 Innodb

InnoDB作为比较通用的存储引擎，其在高可用和高性能两方面作了较好的平衡，MySQL5.5.5 之后，InnoDB 作为默认存储引擎。相较于其他存储引擎，InnoDB有几点关键的优势：

• DML操作支持ACID模型，事务的提交，回滚，以及在服务器崩溃后的恢复能力；
• 具有的行级锁和Oracle风格的一致性读，以提升多用户的并发访问性能；
• InnoDB表会基于主键为查询操作作一些存储优化，每一个InnoDB表都具有一个叫作聚簇索引的主键索引，使得在进行主键查询时，可以最小化I/O操作；
• 为了保证数据完整性，InnoDB支持外键约束。通过外键，可以在进行数据插入，更新，删除时，对数据进行校验，以避免不同表之间的数据不一致问题。
• 数据存储在磁盘上为2个文件

• .frm 存储表定义
• .ibd 数据和索引，是放在一起的

聚集索引：叶节点包含了完整的数据记录，innodb的主键索引就是聚集索引，一个表只有一个聚集索引

二级索引：也是一颗b+树，叶节点存储的是主键的值，查询数据的时候先查询到主键值，在根据主键值在主键索引上做查询，类似回表操作

面试题1：为什么建议InnoDB表必须建主键，并且推荐使用整型的自增主键？
为什么建议InnoDB表必须建主键？
因为innodb引擎数据库设计的时候，.ibd文件需要用一颗b+树来组织，那使用什么字段合适？
首先，我们知道InnoDB采用B+树作为存储结构，那么必然需要一个列作为key，什么是key？
一个B+树的节点可以存储key、地址、行数据(仅叶子节点)，key 就是不重复的值且可以比较(确保树进行分裂时，可以确定是左孩子还是右孩子)。
我们知道主键的特定就是主键的值不可重复，也不可为空，正好符合B+树key的要求
聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引（确切说会选择一个唯一非空的列作为主键）代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。
mysql的资源是非常宝贵的，这种建立主键的操作应该由我们程序员来操作，减少mysql的操作。
为什么是整型？

• 查询元素的时候，是从根节点开始查找，进行数据的比对的操作。如果是整型，直接进行比较即可。如果是字符串，则需要根据ascii码进行逐位的比较，性能略低。
• 整型所占的空间更小。节约磁盘空间。

为什么是自增主键？

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。

聚簇索引的顺序和磁盘中数据的存储顺序是一致的，如果主键不是自增id，那么它会不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

举例：如果是自增，则元素都是往b树增长的方向进行增加

如果是非自增，比如先插入10，

在插入9，则索引的结构发生变化，需要维护索引的位置，效率是很低的

面试题2：为什么非主键索引结构叶子节点存储的是主键值？

• 一致性
• 节省存储空间

三、索引

3.1 联合索引

MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引

联合索引的底层存储结构

按照索引的顺序排好序组织数据

最左前缀匹配原则：在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

要想理解联合索引的最左匹配原则，先来理解下索引的底层原理。索引的底层是一颗B+树，那么联合索引的底层也就是一颗B+树，只不过联合索引的B+树节点中存储的是键值。由于构建一棵B+树只能根据一个值来确定索引关系，所以数据库依赖联合索引最左的字段来构建。

举例：创建一个（a,b）的联合索引，那么它的索引树就是下图的样子。

可以看到a的值是有顺序的，1，1，2，2，3，3，而b的值是没有顺序的1，2，1，4，1，2。但是我们又可发现a在等值的情况下，b值又是按顺序排列的，但是这种顺序是相对的。这是因为**MySQL创建联合索引的规则是首先会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后在对第二个字段进行排序。**所以b=2这种查询条件没有办法利用索引。