索引的理解
建立测试表
我们建立一个测试表user 设置id为主键
create table user( -> id int primary key, -> age int not null, -> name varchar(16) not null -> );
之后我们往表中插入几组数据
在上图中可以发现我们是乱序插入的 可是当我们查询插入结果的时候却变成有序的了
那么看到这里我们就会有两个问题产生
- 排序的工作是谁干的？
- 为什么要排序？
在回答上面两个问题之间我们再理解下page
为何IO交互的单位要是page
为什么IO交互的单位要是page呢 为什么不可以我们需要哪个数据就将哪个数据拿到内存中来
我们在前面说过 实际上IO的过程是十分缓慢的 如果说我们要拿十个数据 按照上面的方法就要交互十次
但是如果按照单位page进行交互 根据计算机的局部性原理 我们很可能IO的次数要小于十次 需要注意的是 局部性原理并不能确保一定减少IO的次数 这只是一个大概率事件
理解单个page
mysql在启动时会向系统申请128mb的内存空间（这也就是为什么有时候mysql会启动失败 因为系统没有这么多空间了）
一个page的大小是16kb 也就是说如果这些内存空间全部用来储存page的话就大概会有8000多个page页 既然有这么多的page页mysql肯定就要对它们进行管理 而管理的方法无法就是 先描述 再组织
所以说单个的page其实就是一个结构体
大概的格式如下图
每个page中有一个前驱和后继指针指向前后的page以方便管理 此外数据记录也存放在page中
那么到现在为止 我们就能够回答上面提出的两个问题了
是谁对于这些数据进行排序
mysql
为什么要进行排序
为了提高搜索效率
理解多个page
通过上面的分析我们可以知道 mysql中存在大量的page 并且它们之间是用双链表的形式连接起来的 如下图
但是仔细观察下我们就可以发现下面两点
- page页内的数据是通过链表连接起来的
- page页之间是通过链表连接起来的
在之前数据结构–链表章节的学习中 我们知道链表只能够线性的查找数据 也就是说虽然我们将这些数据和页表排序了 但是事实上搜索的效率并不会快多少
这个时候我们就引入了一个叫做页目录的方式来提高效率
页目录
假如我们现在看《深入理解计算机系统》这本书的时候 我们想要找到优化程序性能这一章节的内容有两种选择
- 一页页的翻书 直到找到该内容为止
- 根据目录找到这部分的内容
显然方法二的查询比方法一要快速许多 那么在page内和page之间 我们当然也可以引入目录的概念
单页目录
我们要查找id=4记录，之前必须线性遍历4次，才能拿到结果。现在直接通过目录2[3]，直接进行定位新的起始位置，提高了效率。现在我们可以再次正式回答上面的问题了，为何通过键值 MySQL 会自动排序
可以方便的建立目录
建立目录的行为本质是一种以空间换时间的做法 虽然目录占用了一定的空间 但是却可以大大提高我们的搜索效率
多页目录
前面我们说过 在mysql内部有着大量的page页由双链表连接 如下图
虽然说我们通过单页目录提高了页内的搜索效率 但是在实际搜索的过程中 我们还是会遇到下面的两个问题
- 我们无法定位出需要的数据在哪个page内 所以必须要遍历page才能够找到
- 由于不知道我们的数据在哪个page 所以说会和磁盘进行大量的IO来寻找
上面的两个问题如果不解决 我们页内目录做出的效率提升就毫无意义
那么应该如何解决呢？ 答案也是建目录
我们可以将单个的page页看作是一篇文章 它的代号就是page页中键值的最小值
之后我们再重新创建新的page页用来做目录 指向这些 “文章”
但是这样子的模型还是没有解决我们的问题
- 一开始要到哪里去找到有我们想要page页的目录呢？
这个问题的解决方案还是加目录
我们可以在刚刚建立完毕的目录page上层再加上一个page页作为它们的目录指向这些page页
这样子当我们需要找一个page页的时候只需要从最上层的目录开始往下依次查找就可以了
如下图
我们再看上面的结构 其实就是一颗B+树
为什么数据结构是B+树而不是其他数据结构
- 链表？线性遍历
- AVL &&红黑树？虽然是平衡或者近似平衡，但是毕竟是二叉结构，相比较多阶B+，意味着树整体过高，大家都是自顶向下找，层高越低，意味着系统与硬盘更少的IO Page交互。虽然你很秀，但是有更秀的。
- Hash？官方的索引实现方式中， MySQL 是支持HASH的，不过 InnoDB 和 MyISAM 并不支持.Hash跟进其算法特征，决定了虽然有时候也很快(O(1))，不过，在面对范围查找就明显不行，另外还有其他差别，有兴趣可以查一下。
也就是说我们选择B+数主要有两个方面的考虑
- 这是一颗矮胖形的树 决定了它和系统之间的IO次数会更少
- 它的范围查找很优秀
聚簇索引和非聚簇索引
这里直接给出解释
- 聚簇索引就是数据和索引放在一起
- 非聚簇索引就是数据和索引分开
其中innodb用的就是非聚簇索引 而myisam用的是聚簇索引
主键和辅助索引
mysql除了会建立默认的主键索引之外 用户也能按照其他列信息建立索引 而这些索引被叫做辅助索引
对于myisam来说辅助索引和默认的主键索引没有区别
而对于innodb来说 InnoDB的非主键索引中叶子节点并没有数据 而只有对应记录的key值
所以通过辅助（普通）索引 找到目标记录 需要两遍索引 首先检索辅助索引获得主键 然后用主键到主索引中检索获得记录 这种过程 就叫做回表查询
索引操作
创建主键索引
方法一： 在创建表的时候直接添加主键
create table user1(id int primary key, name varchar(30));
方法二： 在创建表的时候在最后指定主键
create table user2(id int, name varchar(30), primary key(id));
方式三： 在创建表后再添加主键
create table user3(id int, name varchar(30)); alter table user3 add primary key(id）;
创建唯一键索引
创建唯一键索引的语法和创建主键索引相同 这里就不过多赘述
创建普通索引
方式一：在创建表的最后指定索引
create table user8(id int primary key, name varchar(20), email varchar(30), index(name) --在表的定义最后，指定某列为索引 );
方式二：创建完表之后添加索引
create table user9(id int primary key, name varchar(20), email varchar(30)); alter table user9 add index(name); --创建完表以后指定某列为普通索引
全文索引
创建全文索引
FULLTEXT (title,body)--创建全文索引
使用全文索引
WHERE MATCH (title,body) AGAINST ('database');
索引查询
show index from 表名;
删除索引
删除主键索引
--方式一：删除主键索引 alter table 表名 drop primary key;
删除其他索引
alter table 表名 drop index 索引名;
什么字段可以创建索引
- 比较频繁作为查询条件的字段应该创建索引
- 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
- 更新非常频繁的字段不适合作创建索引
- 不会出现在where子句中的字段不该创建索引
