MySQL索引底层原理

索引

索引是帮助MySQL高效获取数据的排好序的数据结构

• 索引是对数据表中的一列或多列的值进行排序的一种数据结构。
• 索引可以大大提高MySQL的检索速度

索引数据结构

• 二叉树
• 红黑树
• Hash
  哈希索引采用一定的哈希算法，将键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立即定位到相应的位置，速度非常快。
  
  Hash索引仅仅能满足“=” “IN” “<=>” 查询，不能使用范围查询，也不支持任何范围查询，例如where price > 100;
  由于Hash索引比较的是进行Hash算法运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算之前完全一样
• B+树（采用）
  1.B树的变种
  2.非叶子节点不存储data,只存储索引（冗余）可以放更多的索引
  3.叶子节点包含所有索引字段 同时 存储data
  4.叶子节点之间用指针连接，提高区间访问的性能
  
• B树
  1.叶节点具有相同的深度，叶节点的指针为空
  2.所有索引元素不重复
  3.节点中的数据索引从左到右递增排列
  4.节点不仅存储索引 还会存储data
  

索引优点

-索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因
-索引可以帮助服务器避免排序和创建临时表
-索引可以将随机IO变成顺序IO
-索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组，提高了表访问并发性
-关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）
-通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性
-可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义
-在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
-通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能

索引缺点

• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
• 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大
• 对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度
• 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果
• 对于非常小的表，大部分情况下简单的全表扫描更高效

索引存储在文件系统中

索引是占据物理空间的，在不同的存储引擎中，索引存在的文件也不同。存储引擎是基于表的，以下分别使用MyISAM和Innodb存储引擎建立两张表。

MyISAM:
.frm:与表相关的元数据信息都存放在frm文件中，包括表结构的定义信息等
.MYD:存储数据
.MYI:存储索引
Innodb:
.frm:与表相关的元数据信息 包括表结构的定义信息等
.idb:存储数据 和 索引

索引分类

逻辑分类：
功能：
1.主键索引
2.唯一索引
3.普通索引
4.全文索引
列数：
1.单列索引
2.组合索引

物理分类：
聚簇索引 和 非聚簇索引

   聚簇索引：聚簇索引不是单独的一种索引类型，而是一种数据存储方式。这种存储方式是依靠B+树来实现的，根据表的主键构造一棵B+树且B+树叶子节点存放的都是索引 和 表行记录时，可称主键索引为聚簇索引。聚簇索引也可理解为将数据与索引存放在一起，找到了索引也就找到了数据。
   
   非聚簇索引：数据与索引是分开存放的，B+树叶子节点存放的不是数据表行记录，而是主键值或者数据表行记录的物理地址
   
   虽然Innodb和MyISAM存储引擎默认使用B+树结构存储索引，但是只有Innodb的主键索引才是聚簇索引，Innodb中的辅助索引以及MyISAM使用的都是非聚簇索引。每张表最多只能有一个聚簇索引。

聚簇索引的优缺点

优点：
1.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此聚簇索引中获取数据比非聚簇索引快
2.聚簇索引对于主键的排序查找 以及 范围查找速度非常快。主要原因是B+树叶子节点之间有指针指向下一个叶子节点

缺点：
1.插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂现象，严重影响性能。因此,对于Innodb表，我们一般都会定义一个自增的ID列为主键
2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于Innodb表，我们一般定义主键不可更新
3.辅助索引访问需要二次查找，第一次查找主键值，第二次根据主键值在主键索引中查找相应的数据行

MyISAM索引实现(非聚簇索引)

MyISAM主键索引(非聚簇索引)

叶子节点存放的是数据记录的地址 也就是说 索引 和 行数据记录是没有保存在一起的，所以MyISAM的主键索引是非聚簇索引

MyISAM辅助索引

在MyISAM中，主键索引和辅助索引在结构上没有任何区别，只是主键索引要求key是唯一的，而辅助索引的key是可以重复的。MyISAM的辅助索引也是非聚簇索引

Innodb索引实现（聚簇索引 + 非聚簇索引）

Innodb使用B+树存储数据，主键索引为聚簇索引，其它辅助索引均为非聚簇索引
一个表中只能存在一个聚簇索引（主键索引），但可以存在多个非聚簇索引
Innodb表的索引和数据是存储在一起的，.idb：存放索引和数据

聚簇索引（主键索引）

B+树中叶子节点包含索引的同时还包含数据表中的行记录就是聚簇索引(索引和数据存放在一起)

可以看到叶子节点包含了完整的数据记录，这就是聚簇索引。因为Innodb的数据文件(.idb)按主键聚集，所以Innodb存储引擎必须有主键，如果没有显示的指定主键，则选取首个唯一且非空的列作为主键索引，如果还没有具备，则MySQL自动为Innodb表生成一个隐含字段作为主键，这个字段的长度为6个字节，类型为长整型

主键索引结构分析

• B+树单个叶子节点内的行数据按主键顺序排列，物理空间是连续的(聚簇索引的数据的物理存放顺序和索引顺序是一致的)
• 叶子节点之间是通过指针连接的，相邻叶子节点的数据在逻辑上也是连续的(根据主键值排序)，实际存储时的数据页(叶子节点)可能相距甚远

非聚簇索引(二级索引，辅助索引)

在聚簇索引之外创建的索引(不是根据主键创建的)称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行数据记录，而是主键值。首先通过辅助索引找到主键值，再通过主键值在主键索引树中查找到相应数据行

拓展

• Innodb中主键不宜定义太大，因为辅助索引也会包含主键列，如果主键定义的比较大，其它辅助索引也将很大。尽量把主键定义的小一些，Innodb不会压缩索引
• Innodb中尽量不要使用非单调字段作为主键，因为Innodb数据文件本身是一颗B+树，非单调的主键会造成在插入新记录时，数据文件为了维持B+树的特性而频繁的分裂调整，十分低效，而使用自增的整数字段作为主键则是一个很好的选择

Innodb,MyISAM的索引检索过程

对于Innodb和MyISAM而言，主键索引是根据主关键字来构建B+树存储结构的，辅助索引则是根据辅助键来构造B+树存储结构的，彼此的索引树都是相互独立的。

聚簇索引 与 非聚簇索引的区别

• 聚簇索引的叶子节点存放的是数据行(主键值也是行内数据)，支持覆盖索引。辅助索引的叶子节点存放的是主键值 或者 数据行的地址
• 由于叶子节点(数据页)只能按照一棵B+树排序，所以一张表只能有一个聚簇索引。辅助索引的存在不影响聚簇索引中数据的组织，所以一张表可以有多个非聚簇索引(辅助索引)

操作索引

创建索引

索引名称index_name是可以省略的，省略后，索引的名称 和 索引列名相同

–创建普通索引
CREATE INDEX index_name ON table_name(col_name)

–创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name)

–创建普通组合索引
CREATE INDEX index_name ON table_name(col_name1,col_name2)

–创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name1,col_name2)

–修改表结构创建索引
ALTER TABLE table_name ADD INDEX index_name(col_name)

–创建表时直接指定索引
CREATE TABLE table_name(
ID INT NOT NULL,
col_name VARCHAR (16) NOT NULL,
INDEX index_name(col_name)
);

删除索引

–直接删除索引
DROP INDEX index_name ON table_name;

–修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;

其它相关命令

–查看表结构
desc table_name;

–查看生成表的SQL
show create table table_name

–查看索引信息(包括索引结构)
show index from table_name

–查看SQL执行时间
set profiling = 1;
SQL…
show profiles;

索引实战