创建高性能的索引

在数据之外,数据库系统还维护着特定结构,这些数据结构以某种方式引用指向数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构,就是索引.

索引在MySQL中也叫做键,是存储引擎用于快速找到记录的一种数据结构,这是索引的基本功能

索引对于良好的性能十分关键,尤其是当表中的数据量越来越大时,索引对性能的影响越来越大.

索引优化是对查询性能优化最有效的手段了,索引能够轻易将查询性能提高好几个数量级.

索引基础

在MySQL中,现在索引中找到对应值,然后根据匹配的索引记录找到对应的数据行

索引可以包含一个或者多个列的值,如果索引包含多个列,列的顺序也十分重要,因为MySQL只能高效的使用索引的最左前缀列.

索引的类型

索引有很多类型,可以在不同的场景提供更好的性能,在MySQL中,索引是存储引擎层而不是在服务层实现的,不同存储引擎的索引的工作方式也不一样,也不是所有的存储引擎都支持所有类型的索引,即使多个存储因剧情支持同一种类型的索引,其底层的实现也可能不同.

Ｂ-tree 索引

默认的索引类型，很多存储引擎使用Ｂ+树来存储数据

存储引擎以不同的方式使用Ｂ-tree索引，性能也各有不同，各有优劣，例如MyISAM 使用前缀压缩技术使索引更小,但InnoDB则按照原数据格式进行存储,再如MYISAM索引通过数据的物理位置引用被索引的行,而InnoDB则根据主键引用被索引的行.

B-Tree 通常意味着所有的值按顺序存储,并且每一个叶子页到根的距离相同,

B-Tree索引能够加快访问数据的速度,因为存储引擎不需要进行全表扫描来获取需要的数据,取而代之的是从索引的根节点开始进行搜索,根节点的槽中存放了指向子节点的指针,存储引擎根据这些指针向下层查找,通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点,这些指针实际定义了子节点页中值的上限和下限,叶子节点比较特殊,他们的指针指向的是被索引的数据,而不是其他的节点页,

可以使用B-Tree 索引的查询类型有

• 全值匹配,即对索引中的所有列进行匹配
• 匹配最左前缀, 即只使用索引的第一列
• 匹配列前缀 即只匹配某一列的值的开头部分
• 匹配范围值
• 精确匹配某一列并范围匹配另外一列

因为索引数的节点是有序的,所以除了按值查找外,索引还可以用于查询中的Orderby 操作

B-Tree 索引的限制

• 如果不是按照索引的最左列开始查找,则无法使用索引
• 不能跳过索引中的列,也就是姓 ,名,生日的三行索引,这种索引不能找姓为XX 生日为XX的人,因为没有指定名.
• 如果查询中有某个列的范围查找

有些限制不是B_tree本身导致的,而是MYSQL优化器和存储引擎使用索引的方式导致的.

哈希索引

哈希索引基于哈希表实现,只有精确匹配索引所有列的查询才有效,对于每一行数据,存储引擎会对所有的索引列计算一个哈希码, 哈希码是一个较小的值,并且不同键值的行计算出来的哈希码也不一样,哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针.

如果多个列的哈希值一样,索引会以链表的方式存放多个记录指针到同一个哈希条目中.

因为索引自身只需存储对应的哈希值,所以索引的结构十分紧凑,也让哈希索引查找的速度十分快。

哈希索引的限制在于

• 哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行.
• 哈希索引数据不是按照索引值顺序存储的,所以也就无法用于排序
• 哈希索引也不支持部分索引列匹配查找
• 哈希索引只支持等值比较查询
• 访问哈希索引的数据非常快,除非有很多哈希冲突

所以,一旦适合哈希索引的话,带来的性能提升会十分的明显

InnoDB 引擎有”自适应哈希索引,当InnoDB注意到某些索引值被使用的十分频繁时,会在内存中基于B-tree 索引之上再创建一个哈希索引,这样也让B-tree索引有了哈希索引的一些优点.,比如快速的查找.这是一个完全自动,内部的行为,用户无法控制或者配置,,用户可以关闭此功能”

索引的优点

最常见的B-Tree 索引，按照顺序存储数据，所以MYSQL 可以用来做ORDER BY 和GROUP BY 操作，因为数据是有序的，所以Btree 也就会将相关的列值都存储在一起，因为索引中存储了实际的列值，所以某些查询只使用索引就能够完成全部查询。

• 索引大大减少了服务器需要扫描的数据量
• 索引可以帮助服务器避免排序和临时表
• 索引可以将随机IO变为顺序IO

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

图8

这里设表一共有三列，假设我们以Col1为主键，则图8是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

图9

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现

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

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

图10

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

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，图11为定义在Col3上的一个辅助索引：

图11

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

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

索引的分类

索引分为聚簇索引和非聚簇索引。

根据数据库的功能，有三种索引，分别是唯一索引，主键索引 和聚集索引。

唯一索引是不允许其中任何两行具有相同索引值的索引，当现有数据中存在重复的键值时，大多数数据库不允许将新创建的唯一索引与表一起保存。

主键索引 主键索引和唯一索引的特定类型，该索引要求主键中的每个值都唯一。

聚集索引 即聚簇索引。

高性能的索引策略

独立的列

独立的列指的是索引列不能是表达式的一部分,也不能是函数的参数

1

select actor_id from actor where actor_id +1 =5

上面的查询就不能使用索引,因为MySQL无法解析这个方程式,索引应该简化where条件

始终将索引列单独放在比较符号的一侧

前缀索引和索引选择性

通常可以索引开始的部分字符,这样可以大大的节约索引的空间,从而提高索引效率,但是这样也会降低索引的选择性.

对于BLOB,TEXT 或者更长的VARCHAR类型的列,必须使用前缀索引,因为MySQL不允许索引这些列的完整长度。

多列索引

将where条件的所有列都建立索引这种话是非常错误的,在多个列上单独建立独立的单列索引大部分情况下并不会提高MySQL的查询性能

选择合适的索引列顺序

正确的顺序依赖于使用该索引的查询,并且同时需要考虑如何更好的满足排序和分组的需要,在一个多行的B-tree索引中,索引列的顺序意味着索引首先按照最左列进行排序,其次是第二列.

聚簇索引

聚簇索引不是一种单独的索引类型,而是一种数据存储方式,具体的细节依赖于其实现方式,但是InnoDB的聚簇索引实际上在同一个结构中保存了BTree索引和数据行

术语聚簇表示数据行和相邻的键值紧凑的存储在一起，因为无法同时把数据行存放在两个不同的地方，所以一个表只能由一个聚簇索引。

聚簇索引也称为聚集索引，聚类索引，簇集索引，聚簇索引确定表中数据的物理顺序。聚簇索引类似于电话簿，后者按姓氏排列数据。由于聚簇索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚簇索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。汉语字典也是聚簇索引的典型应用，在汉语字典里，索引项是字母+声调，字典正文也是按照先字母再声调的顺序排列。

聚集索引是根据数据行的键值在表中排序存储数据行。索引定义中包含聚集索引列。每个表只能有一个聚集索引。只有当表包含聚集索引时，表中的数据行才按排序顺序存储。如果表具有聚集索引，则该表称为聚集表。集索引决定了表数据的存储顺序,如果表没有聚集索引，则其数据行存储在一个称为堆的无序结构中。

也就是说,聚簇索引会按照主键值重新在磁盘中对数据进行排序,这样磁盘中的物理数据也是有序的了.

覆盖索引

设计优秀的索引应该考虑到整个查询，而不单单是where条件部分。MYSQL可以使用索引来直接获取列的数据，这样就不再需要读取数据行。

如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就成为覆盖索引

不是所有类型的索引都可以成为覆盖索引,覆盖索引必须要存储索引列的值,而哈希索引,空间索引,和全文索引等都不存储索引列的值,索引MySQL只能使用Btree索引做覆盖索引.

使用索引扫描来做排序

MySQL有两种方式可以生成有序的结果,通过排序操作,或者按索引顺序扫描,如果EXPLAIN出来的type 列的值为index,则说明MySQL使用了索引扫描来做排序.

压缩(前缀压缩)索引

MyISAM 使用前缀压缩来减少索引的大小,从而让更多的索引可以放入内存中,这在某些情况下能极大的提高性能,默认只压缩字符串,但是通过参数设置也可以对整数做压缩。

MYISAM压缩每个索引块的方法是，先完全保存索引块中的第一个值，然后将其他值和第一个值进行比较得到相同前缀的字节数和剩余的不同后缀部分，把这部分存储起来即可。例如，索引块中的第一个值是“perform”，第二个值是“performance”，那么第二个值的前缀压缩后存储的是类似“7,ance”这样的形式，MYISAM对行指针也采用类似的前缀压缩方式。

压缩块使用更少的空间，代价是某些操作可能更慢，因为每个值的压缩前缀都依赖前面的值，所以MYISAM查找时无法在索引块使用二分查找而只能从头开始扫描，

冗余和重复索引

MYSQL允许在相同列上创建多个索引,无论有意还是无意的,MySQL需要单独维护重复的索引,并且优化器在优化查询的时候也需要逐个的进行考虑,这会影响性能

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引,应该避免这样创建重复索引,发现也应该立即移除.

冗余索引与重复索引有一些不同，如果创建了索引(A,B)，再创建索引(A)就是冗余索引，因为这是前一个索引的前缀索引。

索引和锁

索引可以让查询锁定更少的行，如果你的查询从不访问那些不需要的行，那么就会锁定更少的行，

InnoDB只有在访问行的时候才会对其加锁,而索引能够减少InnoDB访问的行数,从而减少锁的数量

<高性能MySQL>

blog.csdn.net/a724888/article/details/68936953