MySQL-初识索引

参考来源：MySQL官方文档

1、概述

• 索引是存储引擎快速找到数据记录的一种数据结构。就像字典一样，通过目录快速找寻具体汉字。SQL查询时先看是否’命中’某一条索引，符合则通过索引查询相关数据，不符合则需要全表扫描。
• 索引的本质：是数据结构，以某种方式指向数据，在该数据结构的基础上实现高级查找算法
• 存储引擎可以为每张表定义最大索引数（16个）和最大索引长度（256字节）

2、相关优缺点

• 优点：
  ①提高数据检索效率，降低数据库的IO成本（创建索引的主要原因）、
  ②通过创建唯一索引，可以保证数据库表中每一行数据的唯一性
  ③对于有依赖父子关系的相关表联合查询时，可以加速表与表之间的连接提高查询效率
  ④使用分组和排序子句时，可以减少查询中分组和排序的时间，降低CPU的消耗
• 缺点：
  ①创建和维护索引需要耗费时间，并且随着数据量的增加所耗费的时间增长
  ②索引需要占据磁盘空间，每一个索引需要占据一定物理空间存储在磁盘上，若存在大量索引，索引文件就可能比数据文件更快达到最大文件尺寸
  ③会降低更新表的速度，在对数据进行增删改时，索引也需要动态维护，降低数据的维护速度

3、常见索引分类

3.1聚簇索引

• 聚簇：表示数据行与相邻的键值聚簇在一起
• 并非单独索引类型，是一种数据存储方式（所有数据记录都存储在叶子节点），也就是索引即数据，数据即索引。
• 相关特点如下，将包含以下两个特性的B+树称为聚簇索引，InnoDB存储引擎会自动创建该聚簇索引
  • 使用记录主键值的大小进行记录和页的排序
    • 页内的记录是按照逐渐的大小顺序排成一个单向链表
    • 各个存放数据记录的页根据页中数据记录的主键大小顺序排序成一个双向链表
    • 存放目录项记录的页分为不同的层次，在同一层次中的页根据页中目录项记录的主键大小顺序排列成一个双向链表
  • B+树的叶子节点存储完整的数据记录（即数据记录中存储所有列的值包含隐藏列）
• 聚簇索引的优点：
  • 数据访问更快，将索引与数据保存在同一个B+树中，从聚簇索引获取数据比非聚簇索引更快
  • 对于主键的排序查找和范围查找速度非常快
  • 节省大量的IO操作
• 聚簇索引的缺点
  • 插入速度严重依赖于插入顺序，按照主键插入速度最快，否则会出现页分裂，影响性能
  • 更新主键的代价较高，每次更新会将被更新的行移动
  • 二级索引访问需要两次索引查找，第一次找主键，第二次根据主键值找对应的数据行。
• 聚簇索引的相关限制
  • MySQL数据库只有InnoDB存储引擎支持聚簇索引
  • 物理数据排序方式只有一种，每个表只能有一个聚簇索引（一般为主键列）
  • 如果没有定义主键，会选择非空的唯一索引代替，若没有则会隐式定义一个主键作为聚簇索引
  • 为充分利用聚簇索引的聚簇特性，主键列尽量选择有序的顺序ID

3.2、非聚簇索引（二级索引 或 辅助索引）

• 非聚簇索引的叶子节点存储的是数据位置，不会影响数据表的物理存储顺序
• 一张表中可以有多个非聚簇索引，即多个索引目录提供数据检索
• 对数据的增、删、改操作效率比聚簇索引高

与聚簇索引的不同点：

• 使用记录非主键列的大小进行记录和页的排序
  • 页内数据记录是按照非主键列的大小顺序排序成一个单向链表
  • 各个存放目录项记录的页也是根据页内数据记录的非主键列大小排序成一个双向链表
  • 存放目录项记录页分为不同的层次，在同一层次中的页是根据页中目录项记录的非主键列大小排序成一个双向链表
• B+树的叶子节点存储的是主键+非主键列的值
• 目录项记录中不在是主键+页号，而是非主键列+页号

3.3、联合索引

• 同时以多个列的大小作为排序规则，也就是同时为多个列创建索引，如以A，B列的大小进行排序，包含以下特点：
  • 先将各个数据记录和页按照A列进行排序
  • 在记录的A列相同的情况下，采用B列进行排序

B+ 树索引使用引擎说明

索引\存储引擎	MyISAM	InnoDB	Memory
B+树索引	支持	支持	支持

4、InnoDB引擎：B+树索引

• 形成过程：
  • 每当为某张表创建一个B+树索引时，都会为该创建一个根节点页。表结构初始化时，对应的根节点没有数据记录也没有目录项记录。
  • 当向表中插入数据时，会先把数据记录存储到根节点中
  • 当根节点中可用空间不够时，在向表中插入数据，此时根节点中的所有记录会赋值分配到新页（如A）中，然后对该页进行页分裂操作得到另一个新页（如B）。这是插入的数据记录根据键值（聚簇索引主键值或二级索引的索引列值）的大小分配到A或B中，根节点则变为存储目录项记录的页。
• 内（非叶子）节点中方目录项记录（除页号）的唯一性，有以下部分组成：
  • 索引列的值
  • 主键值
  • 页号
• 一个页中最少存储两条数据

5、MyISAM引擎：B+树索引

• 在MyISAM中索引和数据分开存储
  • 将数据记录按照插入顺序单独存放在一个文件中（即数据文件.MYD格式），该数据文件中并未将数据记录划分为若干页
  • MyISAM引擎中将索引信息存储到另外一个文件中（即索引文件.MYI格式），在索引文件中会为主键创建一个索引，索引的叶子节点中存储的是 主键值+数据记录地址 的组合（非完整数据记录）

InnoDB与MyISAM引擎对比

• 在InnoDB存储引擎中根据主键值对聚簇索引进行一次查找就能找到对应数据记录，而MyISAM中需要进行一次回表操作。
• InnoDB的数据文件本身就是索引文件，而MyISAM索引文件和数据文件是分开的，索引文件仅保存数据记录的地址
• InnoDB的非聚簇索引data域存储相应记录的主键值，而MyISAM索引记录的是地址值
• MyISAM回表操作时十分快速的，拿地址偏移量直接到数据文件中检索数据
• InnoDB要求表必须要有主键，若没有显示指定会自动选择一个非空且唯一标识数据列作为主键，若不存在则自动生成一个隐式字段作为主键