MySQL——索引

MySQL索引

索引简介

索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。

通俗地讲，索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。

为什么使用索引

字段名	数据类型	大小
id(Primary key)	Unsigned INT	4 bytes
firstName	Char(50)	50 bytes
lastName	Char(50)	50 bytes
emailAddress	Char(100)	100 bytes

使用char是为了指定准确的磁盘占用大小。假设这个数据库包含500万行，而且没有索引。将分析一些查询语句的性能：一个是使用主键id（有序）查询，一个是使用firstName（非关键无序字段）。

• 使用id

  数据库有r=5,000,000条记录，每条记录长度R=204字节，假设磁盘数据块大小为B=1024字节，每个数据块存放1024/204 = 5 条记录，保存这张表所需要的磁盘块为N =5000000/5 = 1,000,000 blocks。

  在id字段上的线性搜索平均需要N/2 = 500,000块访问来找到一条记录（假设id字段是查询关键值），不过既然id字段是有序的，可以执行一个二分查询，这样平均只需要访问log2 (1000000) = 19.93 = 20 个数据块。这样有了很大的提高。

• 使用firstName

  firstName字段既不是有序的，无法执行二分搜索，数值也不具有唯一性，所以对这张表的查找必须到最后一个记录即全表扫描N = 1,000,000个数据块访问。这就是索引用来改进的地方。

  假如索引记录只包含一个索引列以及一个指向原记录数据的指针，那么它显而易见会比原记录（多列）要小。所以索引本身所需要的磁盘块要更少，扫描数目也少。

  firstName索引表结构如下：

  字段名	数据类型	大小
  firstName	Char(50)	50 bytes
  （record pointer）	Special	4 bytes（假设）

  数据库有r = 5,000,000 条记录，建立了一个长R = 54字节的索引，假设磁盘块大小为1,024字节。那么每个数据块可以存放1024/54 = 18 条记录，容纳这个索引表总共需要的磁盘块为N =5000000/18 = 277,778 块。

  该索引的块因子为bfr = (B/R) = 1024/54 = 18 条记录每磁盘块。容纳这个索引表总共需要的磁盘块为N = (r/bfr) = 5000000/18 = 277,778 块。

  现在使用firstName字段来进行搜索就可以利用索引来提高性能。这允许使用一个二分查找，平均log2 (277778) = 18.08 -> 19次数据块访问。找到实际记录的地址，这需要进一步的块读取，这样总数达到19 + 1 = 20次数据块访问，这和非索引表的数据块访问次数有天壤之别。

优缺点

索引最大的优点是：

• 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

索引也有它的缺点：

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。
• 索引文件需要占据物理空间。可能会迅速膨胀。

索引分类

普通索引

普通索引是最基本的索引，它没有任何限制。加速查询。

唯一索引

与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值（注意和主键不同）。如果是组合索引，则列值的组合必须唯一。

主键索引

主键索引，也有唯一约束，为表定义主键将自动创建主键索引，主键索引不允许有空值，而且在数据库管理工具中“设计表”中的“索引”项也无法看到创建好的主键索引，使用语句可以看到主键索引：

SHOW INDEX FROM table_name;

组合索引

组合索引就是将多个列组合成一个索引

应用场景为：频繁的同时使用n列来进行查询，如：where n1 = ‘alex’ and n2 = 666

最左前缀匹配：

例如：create index ix_name_email on in3(name,email,phone);

查询条件：

• name and email and phone – 使用索引
• name and email – 使用索引
• name – 使用索引
• name and phone –会使用索引A，中间断了后面的不会被使用
• email and phone – 不使用索引 （因为email字段在右边）
• phone --不使用索引

对于同时搜索n个条件时，组合索引的性能好于索引合并

全文索引

仅可用于MyISAM和InnoDB，针对较大的数据，生成全文索引非常的消耗时间和空间。

对于文本的大对象，或者较大的CHAR类型的数据，如果使用普通索引，那么匹配文本前几个字符还是可行的，但是想要匹配文本中间的几个单词，那么就要使用LIKE %word%来匹配，这样需要很长的时间来处理，响应时间会大大增加，这种情况，就可使用时FULLTEXT索引了，在生成FULLTEXT索引时，会为文本生成一份单词的清单，在索引时及根据这个单词的清单来索引。

索引实现原理

索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引

Hash索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。

对于每一行数据，存储引擎都会对所有的索引列计算一个哈希值（hash code），哈希值是一个较小的值，并且不同键值的行计算出来的哈希值不一样。哈希索引将所有的哈希值存储在索引中，同时保存指向每个数据行的指针，这样就可以根据，索引中寻找对于哈希值，然后在根据对应指针，返回到数据行。

mysql中只有memory引擎显式支持哈希索引，innodb是隐式支持哈希索引的。

哈希索引限制：

• 哈希索引只包含哈希值和行指针，不存储字段值，所以不能使用"覆盖索引"的优化方式，去避免读取数据表。
• 哈希索引数据并不是按照索引值顺序存储的，索引也就无法用于排序。
• 哈希索引页不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容计算哈希值的。
• 哈希索引只支持等值比较查询，包括=，in(),<=>，不支持任何范围查询。列入where price>100。
• 访问哈希索引的数据非常快，但可能会出现哈希冲突，如果哈希冲突很多，一些索引维护操作的代价也会很高。

因为这些限制，哈希索引只适用于某些特定的场合。而一旦适合哈希索引，则它带来的性能提升将非常显著。

innodb引擎有一个特殊的功能“自适应哈希索引”，当innodb注意到一些索引值被使用的非常频繁时，且符合哈希特点（如每次查询的列都一样），它会在内存中基于B-Tree索引之上再创建一个哈希索引。这是一个完全自动的，内部行为。

创建自定义哈希索引：

可以使用很小的索引为超长的键创建索引。

思路：

在B-Tree基础上创建一个伪哈希索引。这和真正的哈希索引不是一回事，因为还是使用B-Tree进行查找，但是它使用哈希值而不是键本身进行索引查找。需要做的就是在查询的where 子句中手动指定使用哈希函数。

例子：

如果需要存储大量的url，并需要根据url进行搜索查找。如果使用B-Tree来存储URL，存储的内容就会很大，因为URL本身都很长。正常情况下会有如下查询：

mysql> select id from url where url='http://www.mysql.com';

若删除原来url列上的索引，而新增一个被索引的url_crc列，使用crc32做哈希。就可以实现一个伪哈希索引；查询就变成下面的方式：

mysql> select id from url where url='http://www.mysql.com'
        -> and url_crc=crc32("http://www.mysql.com");

这样性能会提高很多。

当然这样实现的缺陷是需要维护哈希值，就是url改变对应哈希值也应该改变。可以手动维护，当然最好是使用触发器实现。

B+树索引

B树和B+树

来源：程序员小灰

为什么不使用二叉查找树

从算法逻辑上考虑，二叉查找树的查找速度和比较次数都是最小的。但需要考虑磁盘IO。

数据库索引是存储在磁盘上的，当数据量比较大的时候，索引的大小可能有几个G甚至更多。当利用索引查询的时候，不可能把整个索引全部加载到内存，只能逐一加载每一个磁盘页，磁盘页对应着索引树的节点。

假如使用二叉树作为索引结构，假设树的高度为4，需要查找的值为10。流程如下：

第一次磁盘IO：

第二次磁盘IO：

第三次磁盘IO：

第四次磁盘IO：

可以看到最坏的情况下，磁盘IO次数等于索引树的高度。为了减少磁盘IO的次数，就要把原本”瘦高“的树变得”矮胖“。这就是B树的特征之一。

B树

B树是一种多路平衡查找树，每个节点最多包含k个孩子，k被称为B树的阶。k的大小取决于磁盘页的大小。

一个m阶的B树具有如下几个特征：

1. 根结点至少有两个子女。
2. 每个中间节点都包含k-1个元素和k个孩子，其中 m/2 <= k <= m
3. 每一个叶子节点都包含k-1个元素，其中 m/2 <= k <= m
4. 所有的叶子结点都位于同一层。
5. 每个节点中的元素从小到大排列，节点当中k-1个元素正好是k个孩子包含的元素的值域分划。

比如上图这个例子，它是一个3阶B树，假如要查找5：

第一次磁盘IO：

第二次磁盘IO：

第三次磁盘IO：

可以看到，实际上，B树的比较次数不比二叉树少（尤其是一个节点中元素很多的时候），但它的磁盘IO次数减少了，相比磁盘IO的速度，内存中的比较耗时几乎可以忽略。所以只要树的高度足够低，磁盘IO次数足够少，就能提高性能。

B树的插入和删除过程还是比较复杂的。

B树主要应用于文件系统以及部分数据库索引。比如非关系型数据库MongoDB。而大部分关系型数据库，如MySQL，则使用B+树作为索引。

B+树

B+树和B树有一些共同点，但B+树也具备一些新的特征。

一个m阶的B+树具有如下几个特征：

1. 有k个子树的中间节点包含有k个元素（B树中是k-1个元素），每个元素不保存数据，只用来索引，所有数据都保存在叶子节点。

2. 所有的叶子结点中包含了全部元素的信息，及指向含这些元素记录的指针，且叶子结点本身依关键字的大小自小而大顺序链接。

3. 所有的中间节点元素都同时存在于子节点，在子节点元素中是最大（或最小）元素。

可以看到，B+树节点之间含有重复元素（每个父节点的元素都出现在子节点中，是子节点最大或最小的元素），而且叶子节点之间还用指针连在一起。

因此，根节点的最大元素（比如15）是整个B+树的最大元素。所以无论插入或删除元素的时候，都要保持最大元素在根节点中。

叶子节点包含了全量元素信息，并且每一个叶子节点都带有指向下一个节点的指针，形成一个有序链表。

B+树还有一个特点，就是卫星数据的位置。

所谓卫星数据，指的是索引元素指向的数据记录，比如数据库中的某一行。

B树中，无论是中间节点还是叶子节点都带有卫星数据：

B+树中，只有叶子节点带有卫星数据，中间节点只是索引，与数据没有关联：

需要补充的是，在数据库的聚集索引（Clustered Index）中，叶子节点直接包含卫星数据。在非聚集索引（NonClustered Index）中，叶子节点带有指向卫星数据的指针。

B+树相对于B树的优点体现在查询性能上：单行查询、范围查询。

• 单行查询

  1. B+树的中间节点没有卫星数据，所以同样大小的磁盘页能容纳更多节点元素，数据量相同的情况下，B+树更“矮胖”，因此磁盘IO次数更少。
  2. B+树必须查找到叶子节点，因为数据在叶子节点上。而B树只要匹配到节点即可，不论是中间节点还是叶子节点。因此，B+树比B树的查找性能更稳定。

• 范围查询

  B树的范围查询：先自顶向下找到范围的下限，然后中序遍历，比较繁琐。

  B+树的范围查询：先自顶向下找到范围的下限，然后遍历链表即可，范围查询十分方便。

聚集索引和非聚集索引

非聚集索引

MyISAM索引方式为“非聚集索引”，使用的是B+Tree 作为索引结构,叶节点的 data 域存放的是数据记录的地址。如图：

聚集索引

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

InnoDB 的数据文件本身就是索引文件。从上文知道,MyISAM 索引文件和数据文件是分离的,索引文件仅保存数据记录的地址。

在InnoDB 中,表数据文件本身就是按 B+Tree 组织的一个索引结构,这棵树的叶点data 域保存了完整的数据记录。这个索引的 key 是数据表的主键,因此 InnoDB 表数据文件本身就是主索引。如图：

InnoDB和MyISAM区别：

1. InnoDB是聚集索引，MyISAM是非聚集索引。

2. InnoDB要求表必须有主键（MyISAM可以没有）。如果没有显式指定,则 MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL 自动为 InnoDB 表生成一个隐含字段作为主键,类型为长整形。

   • 同时,请尽量在 InnoDB 上采用自增字段做表的主键。因为 InnoDB 数据文件本身是一棵B+Tree,非单调的主键会造成在插入新记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整,十分低效,而使用自增字段作为主键则是一个很好的选择。如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。

3. 与 MyISAM 索引的不同是 InnoDB 的辅助索引 data 域存储相应记录主键的值而不是地址。换句话说,InnoDB 的所有辅助索引都引用主键作为 data 域。而MyISAM 辅助索引使用数据地址作为data域。

   • 聚集索引这种实现方式使得按主键的搜索十分高效,但是辅助索引搜索需要检索两遍索引:首先检索辅助索引获得主键,然后用主键到主索引中检索获得记录。如下图：

     

参考链接

参考链接：MySQL索引介绍

参考链接：mysql索引种类和建立索引

参考链接：MySQL索引

参考链接：什么是B树

参考链接：什么是B+树

参考链接：MySQL索引实现原理分析

参考链接：MySQL索引工作原理

参考链接：关于B树的思考