【MySQL】创建高性能的索引

目录

索引基础

索引类型

索引的优点

高性能的索引策略

维护索引和表

---

索引基础

MySQL先在索引上按值进行查找，然后返回所有包含该值的数据行。

  索引可以包含一个或多个列的值，如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效的使用索引最左前缀列。创建一个包含两个列的索引，和创建两个包含一列的索引是大不相同的。

索引类型

索引是在存储引擎层而不是服务器层实现的。不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

B-Tree索引

大多数MySQL引擎都支持这种索引。不同的存储引擎也可能使用不同的结构存储，NDB内部实际上使用T-Tree，InnoDB则使用的是B+Tree。

存储引擎以不同的方式使用B-Tree索引。例如：MyISAM使用前缀压缩技术使索引更小，InnoDB则按照原数据格式进行存储。MyISAM索引通过数据的物理位置引用被索引的行，而InnoDB则根据主键引用被索引的行。

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

 

图  B+树

B-Tree能快速访问数据，因为存储引擎不需要进行全表扫描来获取需要的数据，取而代之是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层节点

B-Tree对索引基于顺序存储，所以很适合查找范围数据。

B-Tree索引的限制:

1. 如果不是按照索引的最左列开始查找，则无法使用索引。
2. 不能跳过索引中的列。
3. 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。

B+ 树与 B 树差异的点，主要是以下这几点：

• 叶子节点（最底部的节点）才会存放实际数据（索引+记录），非叶子节点只会存放索引；
• 所有索引都会在叶子节点出现，叶子节点之间构成一个有序链表；
• 非叶子节点的索引也会同时存在在子节点中，并且是在子节点中所有索引的最大（或最小）。
• 非叶子节点中有多少个子节点，就有多少个索引

哈希索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行指针。只有Memory引擎显式支持哈希索引。

优缺点：索引结构紧凑，查询快，但是索引只包含哈希值和行指针，不能和BTree一样使用索引中的值来避免读取行。自然也无法用于排序。不支持部分索引列匹配查找。只支持等值比较查询。哈希冲突时必须遍历。

自适应哈希索引：InnoDB注意到某些索引值引用频繁时，会在内存中基于BTree索引之上再创建一个哈希查找（完全自动的，内部的行为）

创建自定义哈希索引：依然使用B-Tree查找，但是它使用哈希值而不是键本身进行索引查找

空间数据索引 R-Tree

MyISAM表支持，无须前缀索引，而是从所有维度来索引数据。查询时可以有效使用任意维度来组合查询。

全文索引

查找的是文本中的关键词，而非索引中的值。

其他索引

如TokuDB使用分形树索引。

索引的优点