Java高级编程——MySQL索引实现及优化道理理会

在MySQL中，索引属于存储引擎级此外面念，差异存储引擎对索引的实现方法是差异的，本文首要接头MyISAM和InnoDB两个存储引擎的索引实现方法。

MyISAM索引实现MyISAM引擎行使B+Tree作为索引布局，叶节点的data域存放的是数据记录的地点。下图是MyISAM索引的道理图：

图 1

这里设表一共有三列，假设我们以Col1为主键，则图1是一个MyISAM表的主索引（Primary key）表示。可以看出MyISAM的索引文件仅仅生涯数据记录的地点。在MyISAM中，主索引和帮助索引（Secondary key）在布局上没有任何区别，只是主索引要求key是独一的，而帮助索引的key可以一再。假如我们在Col2上成立一个帮助索引，则此索引的布局如下图所示：

图 2

同样也是一颗B+Tree，data域生涯数据记录的地点。因此，MyISAM中索引检索的算法为起首凭证B+Tree搜刮算法搜刮索引，假如指定的Key存在，则取出其data域的值，然后以data域的值为地点，读取响应数据记录。MyISAM的索引方法也叫做“非聚积”的，之以是这么称号是为了与InnoDB的聚积索引区分。InnoDB索引实现固然InnoDB也行使B+Tree作为索引布局，但详细实现方法却与MyISAM截然差异。第一个重大区别是InnoDB的数据文件自己就是索引文件。从上文知道，MyISAM索引文件和数据文件是疏散的，索引文件仅生涯数据记录的地点。而在InnoDB中，表数据文件自己就是按B+Tree组织的一个索引布局，这棵树的叶节点data域生涯了完备的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件自己就是主索引。

图 3

图3是InnoDB主索引（同时也是数据文件）的表示图，可以看到叶节点包括了完备的数据记录。这种索引叫做聚积索引。由于InnoDB的数据文件自己要按主键聚积，以是InnoDB要求表必需有主键（MyISAM可以没有），假如没有显式指定，则MySQL体系会自动选择一个可以独一标识数据记录的列作为主键，假如不存在这种列，则MySQL自动为InnoDB表天生一个隐含字段作为主键，这个字段长度为6个字节，范例为长整形。第二个与MyISAM索引的差异是InnoDB的帮助索引data域存储响应记录主键的值而不是地点。换句话说，InnoDB的全部帮助索引都引用主键作为data域。譬喻，图11为界说在Col3上的一个帮助索引：

图 4

这里以英笔墨符的ASCII码作为较量准则。聚积索引这种实现方法使得按主键的搜刮异常高效，可是帮助索引搜刮必要检索两遍索引：起首检索帮助索引得到主键，然后用主键到主索引中检索得到记录。相识差异存储引擎的索引实现方法对付正确行使和优化索引都很是有辅佐，譬喻知道了InnoDB的索引实现后，就很轻易大白为什么不提议行使过长的字段作为主键，由于全部帮助索引都引用主索引，过长的主索引会令帮助索引变得过大。再譬喻，用非单调的字段作为主键在InnoDB中不是个好主意，由于InnoDB数据文件自己是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特征而频仍的破碎调解，异常低效，而行使自增字段作为主键则是一个很好的选择。下一章将详细接头这些与索引有关的优化计策。索引行使计策及优化MySQL的优化首要分为布局优化（Scheme optimization）和查询优化（Query optimization）。本章接头的高机能索引计策首要属于布局优化领域。本章的内容完全基于上文的理论基本，现实上一旦领略了索引背后的机制，那么选择高机能的计策就酿成了纯粹的推理，而且可以领略这些计策背后的逻辑。示例数据库为了接头索引计策，必要一个数据量不算小的数据库作为示例。本文选用MySQL官方文档中提供的示例数据库之一：employees。这个数据库相关伟大度适中，且数据量较大。下图是这个数据库的E-R相关图（引用自MySQL官方手册）：

图 5

MySQL官方文档中关于此数据库的页面为http://dev.mysql.com/doc/employee/en/employee.html。内里具体先容了此数据库，并提供了下载地点和导入要领，假若有乐趣导入此数据库到本身的MySQL可以参考文中内容。最左前缀道理与相干优化高效行使索引的主要前提是知道什么样的查询会行使到索引，这个题目和B+Tree中的“最左前缀道理”有关，下面通过例子声名最左前缀道理。这里先说一下连系索引的观念。在上文中，我们都是假设索引只引用了单个的列，现实上，MySQL中的索引可以以必然次序引用多个列，这种索引叫做连系索引，一样平常的，一个连系索引是一个有序元组<a1, a2, …, an>，个中各个元素均为数据表的一列，现实上要严酷界说索引必要用到相关代数，可是这里我不想接头太多相关代数的话题，由于那样会显得很死板，以是这里就不再做严酷界说。其它，单列索引可以当作连系索引元素数为1的特例。以employees.titles表为例，下面先查察其上都有哪些索引：

（编辑：河北网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!