MySQL-索引B+树

MySQL-索引

一、为什么使用索引

索引是存储引擎用于快速找到数据记录的一种数据结构。MySQL进行数据查找时，首先查看查询条件是否命中某条索引，符合则通过索引查找相关数据，如果不符合则需要全表扫描，即需要一条一条地查找记录，直到找到与条件符合的记录。

目的：减少磁盘I/O的次数，加快查询速率

二、索引及其优缺点

概述

本质：排好序的快速查找数据结构

索引是在存储引擎中实现的，因此每种存储引擎的索引不一定完全相同，并且每种存储引擎不一定支持所有索引类型。同时，存储引擎可以定义每个表最大索引数和最大索引长度。所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节。

优点

1. 降低数据库IO成本（主要原因）
2. 唯一索引可以保证数据库每行数据的唯一性
3. 加速表和表之间的连接，提高有依赖关系的父子表联合查询速度
4. 分组与排序字句进行数据查询时可以显著减少查询中分组和排序的时间，降低CPU的消耗

缺点

1. 创建索引和维护索引要消耗时间,并且随着数据量的增加，所耗费的时间也会增加。
2. 占用磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间。
3. 提高查询速度，同时降低更新表的速度。修改数据时，索引也需要动态维护降低更新速度。

提示：

需要大量修改插入数据时，可以先删除表中的索引，然后再修改插入数据，完成后再创建索引。

三、InnoDB中索引的推演

数据页模型

B+树

• 数据都在叶子节点中
• 节点内部是单向链表
• 同层节点之间是双向链表

通常我们用到的B+树不超过4层，每个页面内有Page Directory(页目录，部分值的目录)，页面内因此可以二分法快速定位记录。

常见索引概念

1. 聚簇(聚集)索引
   聚簇索引并不是一种单独的索引类型，而是一种数据存储方式（所有的用户记录都存储在了叶子节点），也就是所谓的索引即数据，数据即索引。

   术语“聚簇”表示数据行和相邻的键值聚簇的存储在一起。

   特点：

   1. 使用记录主键值的大小进行记录和页的排序
      • 页内的记录是按照主键的大小顺序排成一个单向链表。
      • 各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表。
      • 存放目录项的页分为不同层次，在同一层次中的页也是根据页中目录项的主键大小顺序排成一个双向链表。
   2. B+树的叶子节点存储的是完整的用户记录（包括隐藏列）。

   具有该两种特性的B+树称为聚簇索引，所有完整的用户记录都存放在这个聚簇索引的叶子节点处。不需要显示的使用INDEX语句去创建，InnoDB存储引擎会自动的创建聚簇索引。

   优点：

   • 数据访问更快，因为聚簇索引将索引与数据保存在同一个B+树中，隐藏从聚簇索引中获取数据比非聚簇索引更快。
   • 聚簇索引对于主键的排序查找和范围查找速度非常快
   • 按照聚簇索引排序顺序，查询显示一定范围数据时，由于数据紧密相连，数据库不需要从多个数据块中提取数据，所以节省了大量的IO操作。

   缺点：

   • 插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。
   • 更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
   • 二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据

   限制：

   • 对于MySQL数据库目前只有InnoDB数据引擎支持聚簇索引，而MyISAM并不支持聚簇索引。
   • 由于数据物理存储排序方式只能有一种，所以每个MySQL的表只能有一个聚簇索引。一般情况下就是该表的主键。
   • 如果没有定义主键，InnoDB会选择非空的唯一索引代替，如果没有这样的索引，InnoDB会隐式的定义一个主键来作为聚簇索引。
   • 为了充分利用聚簇索引的聚簇的特性，索引InnoDB表的主键列尽量选用有序的顺序id，而不见于用无序的id，比如UUID、MD5、HASH、字符串列作为主键无法保证数据的顺序增长。

2. 非聚簇(非聚集)索引[二级索引，辅助索引]

   存储：仅存储索引列与主键值，不存储其他列数据

   查找：首先通过当前索引找到叶子节点拿到主键值，再到聚簇索引中查找完整数据的叶子节点（回表）

   为什么要回表操作？直接把完整记录放在叶子节点不行嘛？

   回答：

   如果把完整记录放在叶子节点，会占用大量空间，相当于没建立一颗B+树都需要把所有的用户记录再拷贝一遍，这就有点太浪费存储空间。

   非聚簇索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个非聚簇索引。