MySQL索引使用及其数据结构原理

什么是索引

索引相当于表的目录，就是把无序的数据变成有序的查询

1. 把创建了索引的列的内容进行排序
2. 对排序结果生成倒排表
3. 在倒排表内容上拼上数据地址链
4. 在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据

MySQL中索引的分类

• 普通索引：同表中的普通列作为索引，没有任何限制
• 唯一索引：建立索引列的值必须是唯一的，可以为空
• 主键索引：根据主键建立索引，不允许重复，不能为空
• 全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
• 组合索引[覆盖索引]：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

索引的优缺点

• 优点[作用]：
  *加快查询速度
• 缺点:
  创建和维护索引需要耗费时间
  降低了增,删,改的速度。
  索引需要耗费物理空间，增大了表的文件大

MySQL存储引擎分类

MyISM索引

有单独的索引文件
MySQL5.5之前使用
不支持事务，不支持外键，表锁，插入数据时，锁定整个表，
查表总行数时，不需要全表扫描
由于不支持事务与锁，奔溃后无法安全恢复
叶子节点中保存的是索引+物理行地址的
查找的时候，先判断查询是否走了索引,先查询索引文件,找到物理行地址

再由地址直接定位到数据表.

InnoDB索引

索引与数据文件合二为一
MySQL5.5之后使用
支持事务，支持外键，行锁，
查表总行数时，全表扫描  
索引和数据->数据文件中 -> 聚簇索引

什么情况适合添加索引

• 这个列经常进行搜索操作
• 经常需要范围搜索的列，因为索引已经排序，其指定的范围是连续的。
• 主键上，能组织表中数据的排列结构
• 外键上，能加快连接的速度。
• 经常需要排列的列，索引已经排序，能加快排序速度

不适合添加索引

• 表记录太少。
• 经常增删改的表。
• 数据重复且分布平均的表字段，因此应该只为最经常查询和经常排序的数据列建立索引
• 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果

建立索引的规则

• 表的主键、外键必须有索引；
• 数据量超过300的表应该有索引；
• 经常与其他表进行连接的表，在连接字段上应该建立索引；
• 经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
• 索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
• 复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替
• 频繁进行增删改的表，不要建立太多的索引；
• 删除无用的索引，避免对执行计划造成负面影响；

创建索引、修改索引、删除索引的命令语句

查看表中已经存在 索引

show index from 表名;

创建索引

// 创建普通索引
alter table 表名 add index 索引名字 (列1 , 列2) ;
// 创建唯一索引
alter table 表名 add unique 索引名字(列1 , 列2) ;
// 创建主键索引
alter table 表名 add primary key 索引名字(列1 , 列2) ;

增加索引

// 创建普通索引
create index 索引名字 on 表名(列1 , 列2) ;
// 创建唯一索引
create unique index 索引名字 on 表名(列1 , 列2) ;

删除索引

drop index 索引名字 on 表名;
alter table 表名 drop index 索引名字;
#因为主键只有一个所以可以不用写索引名字
alter table 表名 drop primary key ;

查看是否使用到索引

explain select * from 表名 where 字段 = '值';

索引失效的情况

• 索引了多列，要遵守最左前缀原则。查询要从索引的最左前列开始并且不跳过索引中的列。
• 使用like进行模糊查询
• 索引列使用函数
• 范围之后索引列也会失效
• 索引列参加计算
• 利用索引列查询出来的数据超过整张表的30%.
• 语句中使用is not null 或者 is null会导致无法使用索引
• 语句条件中有or
• 字符串不加单引号索引失效

索引的数据结构

InnoDB 存储引擎中的 B+ 树索引。要介绍 B+ 树索引，就不得不提二叉查找树，平衡二叉树和 B 树这三种数据结构。B+ 树就是从他们仨演化来的。

索引文件和数据文件 - innodb中 - 合二为一的.
索引文件和数据文件 - myisam中 - 分开独立的.

二叉树查找

节点中存储了键（key）和数据（data）。键对应 user 表中的 id，数据对应 user 表中的行数据。

左子节点的键值都小于当前节点的键值右子节点的键值都大于当前节点的键值

平衡二叉树

如果上面二叉树id是从小到大顺序排列的, 那么就会成为一个链表。就相当于全表扫描。

平衡二叉树特点：
在满足二叉查找树特性的基础上，要求 每个节点的左右子树的高度差不能超过 1。

B树

一般数据都会存储在磁盘这种设备中，但是从磁盘读取数据的速度特别慢。
如果我们使用平衡二叉树来作为索引，那么每一次查找节点都是一次磁盘IO，如果数据量特别大，那么数的高度就会特别高，就需要进行许多次磁盘IO，效率就会贴别低。

所以我们应该尽可能的让树变矮变胖，让一个树节点可以存放多个键值对。这就诞生了B树。

B树的节点叫页，每一页都是一个磁盘块，每一个页都有多个键值对，

同时每一个节点都会有更多的子节点，这样就能让树的高度变得很低。

B 树查找数据读取磁盘的次数将会很少，数据的查找效率也会比平衡二叉树高很多。

假如我们要查找 id=28 的用户信息，那么我们在上图 B 树中查找的流程如下：

• 先找到根节点也就是页 1，判断 28 在键值 17 和 35 之间，那么我们根据页 1 中的指针 p2 找到页 3。
• 将 28 和页 3 中的键值相比较，28 在 26 和 30 之间，我们根据页 3 中的指针 p2 找到页 8。
• 将 28 和页 8 中的键值相比较，发现有匹配的键值 28，键值 28 对应的用户信息为（28，bv）。

B+树

B+ 树是对 B 树的进一步优化
B+ 树和 B 树的不同点:

• B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据。
  因为在数据库中页的大小是固定的，InnoDB 中页的默认大小是 16KB。
  如果不存储数据，那么就会存储更多的键值，相应的树的阶数（节点的子节点树）就会更大，树就会更矮更胖，进行磁盘的 IO 次数又会再次减少。

B+ 树索引的所有数据均存储在叶子节点，而***且数据是按照顺序排列的。***
B+ 树中各个页之间是通过双向链表连接的，叶子节点中的数据是通过单向链表连接的。
我们通过数据页之间通过双向链表连接以及叶子节点中数据之间通过单向链表连接的方式可以找到表中所有的数据。

聚簇索引和非聚簇索引

聚簇索引：
以主键作为 B+ 树索引的键值而构建的 B+ 树索引，我们称之为聚集索引。
非聚簇索引
以主键以外的列值作为键值构建的 B+ 树索引，我们称之为非聚集索引。

非聚集索引与聚集索引的区别:

• 非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键，想要查找数据我们还需要根据主键再去聚集索引中进行查找，这个再根据聚集索引查找数据的过程，我们称为回表。

聚簇索引查找流程

select * from user where id>=18 and id <40

其中 id 为主键，具体的查找过程如下：

①一般根节点都是常驻内存的，也就是说页 1 已经在内存中了，此时不需要到磁盘中读取数据，直接从内存中读取即可。

从内存中读取到页 1，要查找这个 id>=18 and id <40 或者范围值，我们首先需要找到 id=18 的键值。

从页 1 中我们可以找到键值 18，此时我们需要根据指针 p2，定位到页 3。

②要从页 3 中查找数据，我们就需要拿着 p2 指针去磁盘中进行读取页 3。

从磁盘中读取页 3 后将页 3 放入内存中，然后进行查找，我们可以找到键值 18，然后再拿到页 3 中的指针 p1，定位到页 8。

③同样的页 8 页不在内存中，我们需要再去磁盘中将页 8 读取到内存中。

将页 8 读取到内存中后。因为页中的数据是链表进行连接的，而且键值是按照顺序存放的，此时可以根据二分查找法定位到键值 18。

此时因为已经到数据页了，此时我们已经找到一条满足条件的数据了，就是键值 18 对应的数据。

因为是范围查找，而且此时所有的数据又都存在叶子节点，并且是有序排列的，那么我们就可以对页 8 中的键值依次进行遍历查找并匹配满足条件的数据。

我们可以一直找到键值为 22 的数据，然后页 8 中就没有数据了，此时我们需要拿着页 8 中的 p 指针去读取页 9 中的数据。

④因为页 9 不在内存中，就又会加载页 9 到内存中，并通过和页 8 中一样的方式进行数据的查找，直到将页 12 加载到内存中，发现 41 大于 40，此时不满足条件。那么查找到此终止。

最终我们找到满足条件的所有数据，总共 12 条记录：

(18,kl), (19,kl), (22,hj), (24,io), (25,vg) , (29,jk), (31,jk) , (33,rt) , (34,ty) , (35,yu) , (37,rt) , (39,rt) 。

非聚集索引查找数据

非聚集索引使用的不是主键

非聚集索引的叶子节点不存储表中的数据，而是存储该列对应的主键
比如name=Tom ，会先根据这个name值找到它对应的主键值，然后再根据这个主键用聚簇索引去查询。也就是回表。

MyISAM - 索引

索引文件和数据文件是分开的B+树结构.

在 MyISAM 中，聚集索引和非聚集索引的叶子节点都会存储数据的文件地址。