Mysql索引专题

一、什么是MySQL索引？

• 官方上面说索引是帮助MySQL高效获取数据的数据结构，通俗点的说，数据库索引好比是一本书的目录，可以直接根据页码找到对应的内容，目的就是为了加快数据库的查询速度。
• 索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
• 一种能帮助mysql提高了查询效率的数据结构：索引数据结构。

1.1、索引原理

索引的存储原理大致可以概括为一句话：以空间换时间。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。

数据库在未添加索引进行查询的时候默认是进行全文搜索，也就是说有多少数据就进行多少次查询，然后找到相应的数据就把它们放到结果集中，直到全文扫描完毕

1.2、索引的分类

主键索引：primary key

• 设定为主键后，数据库自动建立索引，InnoDB为聚簇索引，主键索引列值不能为空（Null）。

唯一索引：

• 索引列的值必须唯一，但允许有空值（Null），但只允许有一个空值（Null）。

复合索引：

• 一个索引可以包含多个列，多个列共同构成一个复合索引。

全文索引：

• Full Text（MySQL5.7之前，只有MYISAM存储引擎引擎支持全文索引）。
• 全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找允许在这些索引列中插入重复值和空值。全文索引可以在Char、VarChar 上创建。

空间索引：

• MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型，MySQL在空间索引这方年遵循OpenGIS几何数据模型规则。

前缀索引：

• 在文本类型为char、varchar、text类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

1.3、索引的优缺点

优点：

• 大大提高数据查询速度。
• 可以提高数据检索的效率，降低数据库的IO成本，类似于书的目录。
• 通过索引列对数据进行排序，降低数据的排序成本降低了CPU的消耗。
• 被索引的列会自动进行排序，包括【单例索引】和【组合索引】，只是组合索引的排序需要复杂一些。
• 如果按照索引列的顺序进行排序，对order 不用语句来说，效率就会提高很多。

缺点：

• 索引会占据磁盘空间。
• 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改查操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。
• 维护索引需要消耗数据库资源。

综合索引的优缺点：

• 数据库表中不是索引越多越好，而是仅为那些常用的搜索字段建立索引效果最佳!

二、索引的数据结构

MySQL索引使用的数据结构主要有BTree索引和hash索引。

对于hash索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景建议选择BTree索引。

2.1、根据存储引擎的不同，实现方式也不同

MySQL的索引数据结构最常使用的是B树中的B+Tree，但对于主要的两种存储引擎的实现方式是不同的。

InnoDB中data域名存储的是行数据，而MyISAM中存储的是磁盘地址。

• MyISAM：

  B+Tree叶节点的 data域存放的是数据记录的地址。在索引检索的时候， 首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则根据data域中磁盘地址到磁盘中寻址定位到对应的磁盘块，然后读取相应的数据记录，这被称为“非聚簇索引”。

• InnoDB：

  其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其 表数据文件本身就是按照B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的Key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。

  • 在根据主索引搜索时，直接找到Key所在的节点即可取出数据；
  • 在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。（回表操作）
  • 因此在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

2.2、Hash表

Hash表，在Java中的HashMap，TreeMap就是Hash表结构，以键值对的形式存储数据。我们使用hash表存储表数据结构，Key可以存储索引列，Value可以存储行记录或者行磁盘地址。Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时只能通过扫描全表的方式，筛选出符合条件的数据。

显然这种方式，不适合我们经常需要查找和范围查找的数据库索引使用。

2.3、B+树：改造B树

B+树，作为B树的升级版，MySQL在B树的基础上继续进行改造，使用B+树构建索引。B+树和B树最主要的区别在于==非叶子节点是否存储数据==的问题。

• B树：叶子节点和非叶子节点都会存储数据。
• B+树：只有叶子节点才会存储数据，非叶子节点只存储键值key；叶子节点之间使用双向指针连接，最底层的叶子节点形成了一个双向有序链表。

B+树的最底层叶子节点包含了所有的索引项。可以看出，B+树在查找数据的时候，由于数据都存放在最底层的叶子节点上，所以每次查找都需要检索到叶子节点才能查询到数据。所以在查询数据的情况下每次的磁盘IO次数跟树的高度有直接的关系；但是从另一方面来说，由于数据都被存放到了叶子节点，所以存放索引的磁盘块，所存放的的索引数量会随之增加，所以相对于B树来说，B+树的树高理论情况下是比B树树高要矮的。
但是也存在索引覆盖查询的情况，在索引中数据满足了查询语句所需要的全部数据，此时只需要找到索引即可立刻返回，不需要检索到最底层的叶子节点。

由上述实例可知：B+树可以保证等值和范围查询的快速查找，MySQL的索引采用的就是B+树的结构。

假如我们要查询key为9对应的数据data，查询路径为：磁盘块1->磁盘块2->磁盘块6。

• 第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，9<15，走左子树，到磁盘寻址定位到磁盘块2。
• 第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，7<9<12，到磁盘中寻址定位到磁盘块6。
• 第三次磁盘IO：将磁盘块6加载到内存中，在内存中从头遍历比较，在第三个索引中找到9，取出对应的数据data，如果data存储的是行记录，直接取出data，查询结束；如果存储的是磁盘地址，还需要根据磁盘地址再次寻址定位到指定磁盘取出数据，查询终止。

只需要三次IO操作，即可以查找到对应数据

范围查询实例：

假如我们想要查找9和26之间的数据，查找路径为：磁盘块1->磁盘块2->磁盘块6->磁盘块7

• 前三次磁盘IO：首先查找到键值为9对应的数据（定位到磁盘块6），然后缓存大结果集中。这一步和前面等值查询流程一样，发生了三次磁盘IO。
• 继续查询，查找到节点15之后，底层的所有叶子节点是一个有序列表，我们从磁盘块6中的键值9开始向后遍历筛选出所有符合条件的数据。
• 第四次磁盘IO：根据磁盘块6的后继指针到磁盘中寻址定位到磁盘块7，将磁盘块7加载到内存中，在内存中从头遍历比较，9<25<26，9<26<=26，将数据data缓存到结果集中。
• 逐渐具备唯一性（后面不会再有<=26的数据），不需要再向后查找，查询结束，将结果集返回给用户。