mysql索引学习笔记

最近看到一个视频对于mysql索引的讲解非常好，于是将部分内容记录下来。

【编程不良人】这一次彻底搞定MySQL索引、从此不在恐惧面试_哔哩哔哩_bilibili

mysql中的常见索引：

主键索引：就是我们常用的primary key

普通索引：就是给某一列加上索引

唯一索引：与主键索引的区别就是可以为null

复合索引：使用多个类组成索引

索引的创建

下面我们使用sql语句在建表时创建索引

create table t_user (
	id varchar(20) primary key,	# 主键索引
	job_number varchar(20),
	name varchar(20),
	age int,
	key(name),                  # 普通索引
	unique(job_number),         # 唯一索引
	key(name, age)              # 复合索引
);

show index from t_user;

得到如下结果

可以看到，上表中的Index_type为BTREE，实际上这就是B+树创建的索引。

记录的存储方式

数据库中的数据在外存中是分页存储的，如果要查询数据的话，那么就需要访问外存。我们知道，相对于内存来说，外存的IO是相对较慢的。

假设没有索引，那么我们就只能用最原始的方式去查找记录。

 上图中是记录的存储方式，可以发现，记录是使用链表的方式存储的。我们这里使用单向链表做了简化，实际上的存储应该是双向循环链表。

那么在没有索引的情况下，我们需要从第1页的第一条记录开始查，逐个节点进行遍历，知道查找到我们需要的数据。需要注意的是，图中其实是有索引的，如果没有索引的话，记录的排列顺序会是混乱的，当我们添加了索引时，数据库会自动根据主键索引进行排序。

B+树索引

当然，仅仅是上面这样的数据结构进行查询的时候依旧会很慢，这是因为这些记录都是存在外存的，每次读一页数据都要进行磁盘IO，假如我们要查的数据是在第n页，并且这个n很大的时候，需要的IO次数是很多的，这就导致了查询非常慢。

那么mysql解决查询慢的问题的思路就是尽可能减少IO次数。再这样的背景下，就引入了B+树的索引结构。

 如上图所示，是B+树索引的简图。其中叶子结点是没变的，非叶子结点存储的是指向存储记录的内存页地址。这样的话，我们通过从根节点开始遍历这棵B+树就可以查询到相应的记录了。

其中B+树的每个结点都是一个内存页，也就是说对于一棵高为3的结点（包括根结点），我们每次查询记录需要2次磁盘IO（因为根结点是常驻内存的，所以就不需要磁盘IO了）。

我们假设一页的大小是16KB，一条记录的大小是160B，那么一页就可以存放约100条记录（为了方便计算，这里取近似）。对于非叶子结点，其中存储的只是索引和指针，假设一个索引项占16B，那么一个非叶子结点就能存放约1000个索引项（这里取近似）。那么根结点就可以索引约1000页的索引项，每个索引项可以索引1000页数据，那么两层的B+树大约能索引1e6个内存页，每个内存页存100条记录，那么一个两层的B+树索引结构可以存储大于1e8条记录。这个数量已经非常庞大了，假如B+树的层高增加到三层，那么非叶子结点可以索引1e9个内存页，也就是说一个三层的B+树索引结构可以存储大于1e11条记录。由此可见，B+树索引的效率是非常高的。

普通索引与主键索引

我们知道，通过主键索引可以直接找到相应的记录，因为主键索引的叶子结点就是记录本身。但是通过普通索引却不能直接找到记录，因为普通索引记录的是索引列与主键索引的映射。

 通过普通索引查找主要分为两步：

1.通过普通索引找到主键索引

2.通过主键索引找到相应记录

因此，通过普通索引查找相对于只通过主键索引查找，理论上磁盘的IO次数会更多，查找效率也会更低。