Mysql索引--简介和数据结构

奔跑的毛球
300 阅读5分钟

1、索引介绍

1.1、索引是什么

索引是帮助MySQL高效获取数据的数据结构，好比书的目录，可以加快数据库的查询速度。

索引分为：

  • 聚集索引--主键索引
  • 组合索引
  • 前缀索引
  • 唯一索引 默认都是B+树结构

1.2、索引的优缺点

优点：

  • 提高数据检索效率，降低IO成本
  • 可通过索引进行排序，降低数据排序成本 缺点：
  • 索引会占据磁盘空间
  • 会提高查询效率，但会降低更新表的效率

2.索引的使用

2.1、索引的类型

  • 主键索引：索引列中的值必须是唯一的，不允许有空值。
ALTER TABLE table_name ADD PRIMARY KEY (column_name);
  • 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。
ALTER TABLE table_name ADD INDEX index_name (column_name) ;
  • 唯一索引：索引列中的值必须是唯一的，但是允许为空值。
CREATE UNIQUE INDEX index_name ON table(column_name) ;
  • 全文索引：只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。
ALTER TABLE table_name ADD FULLTEXT INDEX index_name(column_name);

全文检索时用MATCH() ... AGAINST语法

SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('xxx');
  • 空间索引：MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。
  • 前缀索引：在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。
ALTER TABLE table_name ADD INDEX index_name (column1(length));

2.2、删除索引

DROP INDEX index_name ON table

2.3、查看索引

SHOW INDEX FROM table_name \G

3、索引的数据结构

3.1、索引的要求

索引需要支持等值查询和范围查询

3.2、数据结构

2.2.1、Hash表

以键值对的方式存储数据，Hash表在等值查询时效率很高，时间复杂度为O(1)；但是不支持范围快速查找，范围查找时还是只能通过扫描全表方式。

2.2.2、二叉查找树

二叉树特点：每个节点最多有2个分叉，左子树和右子树数据顺序左小右大。

二叉树的检索复杂度和树高相关

根节点需要选择尽量中间的数，否则左右树将不平衡。

若是将id列构建二叉树，二叉树退化为一个单向链表，查询数据需要全表扫描。

2.2.3、平衡二叉查找树

采用二分法思维，除了具备二叉树的特点，最主要的特征是树的左右两个子树的层级最多相差1。

在插入删除数据时通过左旋/右旋操作保持二叉树的平衡，不会出现左子树很高、右子树很矮的情况。

查询性能接近二分查找法。 存在的问题：

  • 时间复杂度和树高相关
  • 平衡二叉树不支持范围查询快速查找

2.2.4、B树：改造二叉树

每个节点可以有多个分支，分支的数量叫做度（Degree）,分支越多，效果越好。

B树是一种多叉平衡查找树，主要有以下特点：

  1. B树的节点中存储着多个元素，每个内节点有多个分叉。
  2. 节点中的元素包含键值和数据，节点中的键值从大到小排列。也就是说，在所有的节点都储存数据。
  3. 父节点当中的元素不会出现在子节点中。
  4. 所有的叶子结点都位于同一层，叶节点具有相同的深度，叶节点之间没有指针连接。

B树每个节点的大小为16K.每个节点的度大约为1000.

按顺序添加数据并且度为4的时候：

以下面的B树为例，我们的键值为表主键，具备唯一性。

下面我们看一下，如何使用B树查询数据。 假如我们查询值等于15的数据。查询路径磁盘块1->磁盘块2->磁盘块7。

  • 第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，15<17，走左路，到磁盘寻址磁盘块2。
  • 第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，12<15，到磁盘中寻址定位到磁盘块7。
  • 第三次磁盘IO：将磁盘块7加载到内存中，在内存中从头遍历比较，15=15，找到15，取出data，如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。

B树的高度一般2至3层就能满足大部分的应用场景，所以使用B树构建索引可以很好的提升查询的效率。

B树的缺点：

  1. B树不支持范围查询的快速查找，范围查询需要遍历。
  2. 如果data存储的是行记录，行的大小随着列数的增多，所占空间会变大。这时，一个页中可存储的数据量就会变少，树相应就会变高，磁盘IO次数就会变大。

2.2.5、B+树：改造B树

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

B+树的最底层叶子节点包含所有索引项。 以度为4为例，从1开始依次添加数据。 B+树查找数据，由于数据都存放在叶子节点，所以每次查找都需要检索到叶子节点，才能查询到数据。

查询数据： 等值查询：

假如我们查询值等于15的数据。查询路径磁盘块1->磁盘块2->磁盘块5。

  • 第一次磁盘IO：将磁盘块1加载到内存中，在内存中从头遍历比较，15<28，走左路，到磁盘寻址磁盘块2。
  • 第二次磁盘IO：将磁盘块2加载到内存中，在内存中从头遍历比较，10<15<17，到磁盘中寻址定位到磁盘块5。
  • 第三次磁盘IO：将磁盘块5加载到内存中，在内存中从头遍历比较，15=15，找到15，取出data，如果data存储的行记录，取出data，查询结束。如果存储的是磁盘地址，还需要根据磁盘地址到磁盘中取出数据，查询终止。

查询数据： 范围查询：

假如我们想要查找15和26之间的数据。查找路径是磁盘块1->磁盘块2->磁盘块5。

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

B+树可以保证等值和范围查询的快速查找。

MySQL的索引就采用了B+树的数据结构

avatar
文章
阅读
粉丝