深入理解MySQL的索引（二）

MySQL实现

       对B-树，B+树和散列等数据结构的基本概念有了一些了解之后，我们就可以开始讨论MySQL通过支持它们的存储引擎如何实现不同的算法。同时每种实现也对磁盘和内存使用情况有不同的影响，这一点在大型数据库系统中是非常重要的考虑因素。

1.MyISAM的B-树

     MyISAM存储引擎使用B-树数据结构来实现主码索引、唯一索引以及非主码索引。在MyISAM实现数据目录和数据库模式子目录中，用户可以找到和每个MySQL表对应的.MYD和.MYI文件。数据库表上定义的索引信息就存储在MYI文件中，该文件的块大小是1024字节。这个大小是可以通过myisam-block-size系统变量分配。

    $  ls -1h /var/lib/mysql/book/source_words.MY*

    -rw-rw---- 1 mysql mysql  9.2M 2015-05-07 19:08

    source_words.MYD

   -rw-rw---- 1 mysql mysql  7.8M 2015-05-07 19:08

    source_words.MYI

     这些文件结构的内部格式可以从MySQL免费源代码中找到，也可以查看MySQL内部手册。

     在MyISAM中，非主码索引的B-树结构存储索引值和一个指向主码数据的指针，这是MyISAM和InnoDB的一个显著区别。这一点导致了两个存储引擎的索引的不同工作方式。

     MyISAM索引是在内存的一个公共缓存中管理的，这个缓存的大小可以通过key_buffer_size或者其他命名键缓存来定义。这是根据统计和规划的表索引的大小来设定缓存大小时主要的考虑因素。

2. InnoDB的B+树聚簇主码

      InnoDB存储引擎在它的主码索引（也被称为聚簇主码）中使用了B+树，这种结构把所有数据都和对应的主码组织在一起，并且在叶子节点这一层上添加额外的向前和向后的指针，这样就可以更方便地进行范围扫描。

      在文件系统层面，所有InnoDB数据和索引信息都默认在公共InnoDB表空间中管理，否则管理员就通过innodb_data_file_path这个变量指定文件路径。这是一个叫ibdatal文件。

      由于InnoDB用聚簇主码存储数据，底层信息占用的磁盘空间的大小很大程度上取决于页面的填充因子。对于按序排列的主码，InnoDB会用16K页面的15/16作为填充因子。对于不是按序排列的主码，默认情况下InnoDB会插入初始数据的时候为每一个页面分配50%作为填充因子。

      在改索引的实现方式中B+树的叶子节点上是data就是数据本身，key为主键，如果是一般索引的话，data便会指向对应的主索引。在B+树的每一个叶子节点上面增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的B+树。其目的是提高区间访问的性能。

3.InnoDB的B-树非主码

     InnoDB中的非主码索引使用了B-树数据结构，但InnoDB中的B-树结构实现和MyISAM中并不一样。在InnoDB中，非主码索引存储的是主码的实际值。而MyISAM中，非主码索引存储的包含主码值的数据指针。这一点很重要。首先，当定义很大的主码的时候，InnoDB的非主码索引可能回更大，随着非主码索引数量的增加，索引之间大小差别可能会变得很大。另一个不同点在于非主码索引当前可以包含主键的值，并且可以不是索引必须有的部分。

4.内存散列索引

    在默认MySQL的引擎索引中，只有MEMORY引擎支持散列数据结构，散列结构的强度可以表示为直接键查找的简单性，散列索引的相似度模式匹配查询比直接查询慢。也可以为MEMORY引擎指定一个B-树索引实现。

5.内存B-树索引

   对于大型MEMORY表来说，使用散列索引进行索引范围搜索的效率很低，B-树索引在执行直接键查询时确实比使用默认的散列索引快。根据B-树的不同深度，B-树索引在个别操作中的确可能比散列算法快。

6.InnoDB内部散列索引

    InnoDB存储引擎在聚簇B+树索引中存储主码：但在InnoDB内部还是使用内存中的散列表来更高效地进行主码查询。这个机制有InnoDB存储引擎来管理，用户只能通过innodb_adaptive_hash_index配置项来选择是否启用这个唯一的配置选项。