数据库之索引（三）

目录

一、简述索引实现的原理

二、简述数据库索引的重构过程

三、为什么MySQL的索引使用B+树

四、简述联合索引的存储结构及其有效方式

五、MySQL的Hash索引和B树索引有何区别

---

一、简述索引实现的原理

        在MySQL中，索引是在存储引擎层实现的，不同存储引擎对索引的实现方式是不同的，下面我们讨论一下MyISAM和InnoDB两个存储引擎的索引实现方式。

        1、MyISAM索引实现

        MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址，MyISAM索引的原理图如下。这里假设表一共三列，假设我们以Col1为主键，则下图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

        如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示。同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

        2、InnoDB索引实现

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

        第一个重要区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键。因此，InnoDB表数据文件本身就是主索引。

        下图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显示指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整型。

        第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。下图为定义在Col3上的一个辅助索引。这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

        了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会使得辅助索引变得过大。再比如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新纪录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

二、简述数据库索引的重构过程

        1、什么时候需要重建索引？

        ①表上频繁发生update、delete操作；

        ②表上发生了alter table ..move操作（move操作导致了rowid变化）。

        2、如何判断索引是否应该重建？

        ①一般看索引是否倾斜的严重，是否浪费了空间，对索引进行结构分析；

analyze index index_name validate structure;

        ②在相同的session中查询index_stats表：

select height,DEL_LF_ROWS/LF_ROWS from index_stats;

        当查询的height>4（索引的深度，即从根到叶节点的高度）或DEL_LF_ROWS/LF_ROWS>0.2 的情况下，就应该考虑重建该索引。

        3、如何重建索引？

        ①drop原索引，然后再创建索引

drop index index_name;
create index index_name on table_name(index_column);

         这种方式相当耗时，一般不建议使用。

        ②直接重建索引

alter index indexname rebuild;
alter index indexname rebuild online;

        这种方法较快，建议使用。

        rebuild是快速重建索引的一种有效办法，因为它是一种使用现有索引项来重建新索引的方法。如果重建索引时有其他用户在对这个表操作，尽量使用带online参数来最大限度的减少索引重建时将会出现的任何加锁问题。由于新旧索引在建立时同时存在，因此，使用这种重建方法需要有额外的磁盘空间可供临时使用，当索引建完后将旧索引删除，如果没有成功，也不好影响原来的索引。利用这种办法可以用来将一个索引移到新的表空间。

        rebuild重建索引的过程：

        ①Rebuild以index fast full scan或table full scan方式（采用哪种方式取决于cost）读取原索引中的数据来构建一个新的索引，重建过程中有排序操作，rebuild online 执行表扫描获取数据，重建过程中有排序的操作；

        ②Rebuild会阻塞DML操作，rebuild online不会阻塞DML操作；

        ③Rebuild online时系统会产生一个SYS_JOURNAL_xxx的IOT类型的系统临时日志表，所有rebuild online时索引的变化都会记录在这个表中，当新的索引创建完成后，把这个表的记录维护到新的索引中去，然后drop掉旧的索引，rebuild online就完成了。

        重建索引过程中的注意事项：

        ①执行rebuild操作时，需要检查表空间是否足够；

        ②虽然rebuild online操作允许DML操作，但还是建议在业务不繁忙时间段进行；

        ③rebuild操作时会产生大量的Redo log。

三、为什么MySQL的索引使用B+树

        B+树由B树和索引顺序访问方法演化而来，它是为磁盘或其它直接存取辅助设备设计的一种平衡查找树，在B+树中，所有记录节点都是按照键值的大小存放在同一层的叶子节点，各叶子节点通过指针进行链接。如下图：

        B+树索引在数据库中的一个特点就是高扇出性，例如在InnoDB存储引擎中，每个页的大小为16KB。在数据库中，B+树的高度一般都在2~4层，这意味着查找某一键值最多只需要2到4次IO操作，这还不错。因为现在一般的磁盘每秒至少可以做100次IO操作，2~4次的IO操作意味着查询时间只需要0.02~0.04秒。

四、简述联合索引的存储结构及其有效方式

        从本质上讲，联合索引还是一颗B+Tree，不同的是联合索引的键值数量不是1，而是大于等于2，参考下图。此外，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用。所以使用联合索引时遵循最左前缀集合。

五、MySQL的Hash索引和B树索引有何区别

        Hash索引底层就是hash表，在进行查找时，调用一次hash函数就可以获取到相应的键值，之后进行回表查询获得实际数据。B+Tree底层实现是多路平衡查找树，对于每一次的查询都是从根节点出发，查找到叶节点方可以获得所查键值，然后根据查询判断是否需要回表查询数据。它们有以下的不同：

        1、Hash索引进行等值查询更快（一般情况下），但是无法进行范围查询。因为在hash索引中经过hash函数建立索引之后，索引的顺序与原顺序无法保持一致，不能支持范围查询。而B+Tree的所有节点遵循（左节点小于父节点，右节点大于父节点，多叉树也类似），天然支持范围。

        2、Hash索引不支持使用索引进行排序，原理同上。

        3、Hash索引不支持模糊查询以及多列索引的最左前缀匹配，原理也是因为hash函数的不可预测。

        4、Hash索引任何时候都避免不了回表查询数据，而B+Tree在符合某些条件（聚簇索引，覆盖索引等）的时候可以只通过索引完成查询。

        5、Hash索引虽然在等值查询上较快，但是不稳定，性能不可预测，当某个键值存在大量重复的时候，会发生hash碰撞，此时效率可能极差。而B+Tree的查询效率比较稳定，对于所有的查询都是从根节点到叶子节点，且树的高度较低。

        因此，在大多数情况下，直接选择B+Tree索引可以获得稳定且较好的查询速度，而不需要使用hash索引。