深入理解MySQL的索引

创建合适的索引是SQL性能调优中最重要的技术之一.

MySQL索引各种可能的用途

索引不仅仅是在读取数据时优化mysql的性能.这些功能还包括:

• 保持数据完整性
• 优化数据访问性能
• 改进表的连接操作
• 对结果进行排序
• 简化聚合数据操作

数据完整性

MySQL用主键和唯一键来执行每个表中存储数据的唯一性等级.
主键:

• 每个表只能有一个主键
• 主键不能包含null值
• 通过主键可以获取表中任意特定行
• 如果定义了AUTO INCREMENT列,那么此列必须是主键的一部分.

唯一键:

• 表可以有多个唯一键
• 唯一键可以包含null值,并且每个null值都是唯一的(即NULL!=NULL)

外键是一种约束.除了InnoDB不要求使用外键约束时参照表和使用表都必须有索引外,其他引擎都要求具有索引才能用.

优化数据访问

索引可以让优化器在执行查询的时候不必检索表中所有数据.通过限制需要访问的行的数目,可以显著提高查询速度.这是索引最普遍的用途.

表连接

索引能快捷高效地在相关的表之间做连接操作.在需要连接的列上使用索引可以显著提升性能,并可以在另一个表中快速找到一个匹配的值.掌握创建正确的索引来高效执行表连接的操作是所有关系型数据库sql性能优化的基础.

结果排序

如果希望select语句的结果是有序的,那么就应该通过order by 关键字 进行排序.

聚合操作

索引还可以作为一种更方便的计算聚合结果的工具.例如在计算指定时期内所有账单的总和时,如果在日期和账单账户上添加合适的索引就可以更高效的执行

理解各种索引数据结构理论

MySQL有三种默认引擎:

• MyISAM :一种非事务性的存储引擎,是5.5之前版本的默认存储引擎
• InnoDB :最流行的事务性存储引擎,5.5版本开始作为默认存储引擎
• Memory :一种基于内存的,非事务性的以及非持久性的存储引擎

索引专业术语

专业术语	解释
索引技术	这个词是关于不同数据结构如何用不同的方法访问底层信息的理论.这些技术包括B-树、B+树、R-树以及散列。每一种技术都采用不同的概念来实现一种特定目标或数据结构的优势
索引实现	这个词是关于MySQL及各种存储引擎实现不同的数据结构技术的方法。例如MyISAM引擎实现B-树的方法和InnoDB实现B-树的方法就有所不同
索引类型	这个词描述索引的普遍用法的

各种存储引擎的索引实现方式

B-树

B-树数据结构支持数据插入、控制操作以及通过管理一系列树根状结构的彼此联通的结点中来做选择。B-树中有两种节点类型：索引节点和叶子节点。叶子节点是用来存储数据的，而索引节点则用来告诉用户存储在叶子节点中的数据的顺序，并帮助用户找到相应数据。

MyISAM的B-树

MyISAM存储引擎使用B-树数据结构来实现主码索引、唯一索引以及非主码索引。数据库表上定义的索引信息就存储在MYI文件中，该文件的块大小是1024字节。这个大小是通过MyISAM-block-size系统变量配置的。在MyISAM中,非主码索引的B树结构存储索引值和一个指向主码数据的指针.

InnoDB的B-树

InnoDB中的非主码索引使用了B-树数据结构,但InnoDB中的B-树结构实现和MyISAM中的并不一样.在InnoDB中,非主码索引存储的是主码的实际值,而在MyISAM中,非主码索引存储的是包含主码值的数据的指针.这意味着,当主码长度过大时(比如40字节)非主码索引也会变得很大.

Memory的B-树索引

对大型memory表来说,使用散列索引进行索引范围搜索的效率很低,这一点在之前的例子中也可以看出来.B-树索引的大小是散列索引的两倍.

B+树

B+树中底层数据是根据被提及的索引列进行排序的。B+树还通过在叶子节点之间的附加引用来优化扫描性能。

InnoDB的B+树聚簇主码

InnoDB存储引擎在它的主码索引(也被称为聚簇主码)中使用了B+树.这种结构把所有数据都和对应的主码组织在一起,并且在叶子节点这一层添加额外的向前和向后的指针,这样就可以更方便地进行范围扫描操作.
对于按序排列的主码,InnoDB会用16K页面的15/16作为填充因子.对于不是按序排列的主码,默认情况下InnoDB会在插入初始数据的时候为每一个页面分配50%作为填充因子.

散列

它将一种算法应用到给定值中以在底层数据存储系统中返回一个唯一的指针或位置。散列表的优点是始终以线性时间复杂度找到需要读取的行的位置，而不像B-树那样需要横跨多层节点来确定。

内存散列索引

只有MEMORY引擎支持散列数据结构,散列也是主码索引和非主码索引的默认结构.散列的强度可以表示为直接键查找的简单性

InnoDB的散列索引

InnoDB在B+树索引中存储主码,但在InnoDB内部还是使用内存中的散列表来进行更高效的主码查找.用户只能通过innodb_adaptive_hash_index配置项来选择是否启用这个唯一的配置选项.

通信R-树

R-树数据结构支持基于数据类型对几何数据进行管理。目前只有MyISAM使用R-树实现支持空间索引。使用空间索引也有很多限制，比如只支持唯一的NOT NULL列。

全文本

这种数据结构目前只有MyISAM存储引擎支持。不过在MySQL5.6及以后InnoDB也实现了全文本的数据结构。

分区的MySQL索引

一个已分区的表不支持全文本索引,空间索引以及外键索引.分区表上的主索引和唯一索引必须包含分区表达式中用到的所有列.
分区的一个优势就是使得执行SQL语句时启用分区精简MySQL可以通过控制分区来实现只扫描一些用到的索引而不是扫描所有索引.