MySQL索引

索引的作用

众所周知，不同的数据结构对于增删查改的效率是不一样的。例如链表对于插入删除的效率是很高的，但是对于查找的效率就很低；连续空间的数组对于查找的效率就很高，但是对于增删因为要考虑后面的元素所以效率会很低。

而索引的作用就是对于数据表而言改变其存储的数据结构，增加查找的效率，但是同时会降低插入、更新、删除的效率。并且索引可以提高数据库的性能，只需要创建出正确的索引就可以使查询速度提高成百上千倍

假设现在有一个八百万个数据的员工表，在没有索引的情况下要求查找编号为100万的员工，看看现象

需要花费6秒才能将数据查出，这样的效率是非常低效的。那么加上索引之后会发生什么呢

加上索引后，查找的速度瞬间飞起。那么为什么会这样，索引又是什么呢？请继续往下观看

建立对索引的共识

1. MySQL的InnoDB引擎时使用16KB进行IO交互的，也就是说MySQL与磁盘进行数据交互的基本单位为16KB。这个基本数据单元叫做page
2. MySQL中的数据文件都是以page为单位保存在磁盘当中的
3. MySQL的操作都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据
4. 只要涉及计算，就需要CPU参与，而为了便于CPU参与，一定要能够先将数据移动到内存当中
5. 在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略刷新到磁盘。而这时就涉及到磁盘和内存的数据交互，也就是IO。此时IO的基本单位就是Page
6. 为了提高效率，本质上就是尽可能的减少系统和磁盘IO的次数

理解索引

首先创建一张简单的表，以id作为主键并且id的类型为int

create table stu(
id int primary key,
name varchar(20) not null
);
1
2
3
4

然后往表里插入几条数据，并且插入数据的id不以有序插入

insert into stu values(3, 'zhangsan');

insert into stu values(2, 'lisi');

insert into stu values(5, 'wangwu');

insert into stu values(1, 'zhaoliu');

insert into stu values(4, 'tianqi');
1
2
3
4
5
6
7
8
9

插入完成之后查看表中的数据

表中的数据竟然排好了序，这是为什么呢？继续往下观看

为什么IO交互要为page

为什么MySQL和磁盘交互时要一下子加载page呢，而不是需要一条再加载一条呢

如上的5条数据， 如果MySQL要查找id=2的记录，第一次加载id=1，第二次加载id=2，一次一条记录，那么就需要2次IO。如果要找id=5，那么就需要5次IO。

如果这5条(或者更多)都被保存在一个Page中(16KB，能保存很多记录),那么第一次IO查找id=2的时候，整个Page会被加载到MySQL的Buffer Pool中，这里完成了一次IO。但是往后如果在查找id=1,3,4,5等，完全不需要进行IO了，而是直接在内存中进行了。所以，就在单Page里面，大大减少了IO的次数

又因为往往IO效率低下的最主要矛盾不是IO单次数据量的大小，而是IO的次数

理解单个page

MySQL 中要管理很多数据表文件，而要管理好这些文件，就需要先描述，在组织，目前可以简单理解成一个个独立文件是又一个或者多个page构成的

每个page里都存放着数据，根据链表组织起来。

通过链表就可以在一个page里查找数据。

理解多个page

多个page也是通过链表组织起来的，每个page都有指向上一张page的指针和指向下一张page的指针，通过指针找到与之相连的page。

页目录

好比在看一本书时，需要翻到想查找的内容就可以现在目录上找到对应的页码然后直接翻到该页码即可。而对于查找page中的数据也同样可以利用目录去提高查找的效率

如图，通过目录1就可以找到数据1，目录2就可以找到数据3。

在没有目录时，如果需要查询数据4就需要线性的遍历4次。有了目录之后就可以通过目录2快速定位数据3再往下遍历一次即可，这样可以提高效率

多页情况

MySQL 中每一页的大小只有 16KB ，单个Page大小固定，所以随着数据量不断增大， 16KB 不可能存下所有的数据，那么必定会有多个页来存储数据。

因此就要对多页page进行管理起来，那么管理起来多页page后，是不是也可以创建一个目录用于快速定位page呢？

答案肯定是可以的，事实上MySQL也是这么做的，通过创建目录快速定位page再通过单个page内部的目录快速定位数据

但是如果目录页创建多了那也是要对目录页管理起来的，这时就可以再创建目录页用于管理多个目录页，一次循环

事实上三层结构就可以存储很多数据了，所以并不需要担心数的深度过高

B+树

上述所讲的存储的结构就是B+树

这个数据结构便是MySQL索引所使用的数据结构

对于InnoDB在建立索引结构管理数据时，选用别的数据结构不行的原因：

1. 链表：线性遍历
2. 二叉搜索树：退化问题，可能退化成为线性结构
3. AVL &&红黑树：虽然是平衡或者近似平衡，但是毕竟是二叉结构，相比较多阶B+，意味着树整体过高，大家都是自顶向下找，层高越低，意味着系统与硬盘更少的IO Page交互。
4. Hash：官方的索引实现方式中， MySQL 是支持HASH的，不过 InnoDB 和 MyISAM 并不支持.Hash跟进其算法特征，决定了虽然有时候也很快(O(1))，不过，在面对范围查找就明显不行

聚簇索引与非聚簇索引

MyISAM 存储引擎-主键索引
MyISAM 引擎同样使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址。

不过MyISAM的B+树并不把数据和索引放在一块，也就是说InnoDB的索引的子节点，也就是最后一层page是将目录和数据放在一块的，而MyISAM的索引最后一层page只存放地址，而数据另外存放，通过page中的地址找到数据

向InnoDB的索引就叫聚簇索引，MyISAM的索引就叫非聚簇索引

索引操作

创建索引

1. 主键索引

   如果在创建表的时候设置了主键，那么MySQL就会自动的创建好主键索引，如果没有设置主键，则只需要修改表属性添加上主键即可

   当然主键也可以由多列共同构建

   -- 创建表以后再添加主键
   alter table XXX add primary key(XXX);
   1
   2

2. 唯一索引

   唯一索引也就是以唯一键的属性创建索引，所以同主键索引一样，创建表时设置唯一键或者为表添加唯一键即可

   alter table XXX add unique(XXX);
   1

3. 普通索引

   可以在创建表的时候指明索引

   create table XXX(id int primary key,
   name varchar(20),
   email varchar(30),
   index(name) --在表的定义最后，指定某列为索引
   );
   1
   2
   3
   4
   5

   也可以在创建表之后在添加索引

   create table XXX(id int primary key, name varchar(20), email varchar(30));
   alter table XXX add index(name); --创建完表以后指定某列为普通索引
   1
   2

   还可以直接为表指定列创建索引

   create table XXX(id int primary key, name varchar(20), email varchar(30));
   -- 创建一个索引名为 idx_name 的索引
   create index idx_name on XXX(name);
   1
   2
   3

三个索引的特点：

1. 主键索引的特点：
   1. 一个表中，最多有一个主键索引，当然可以使符合主键
   2. 主键索引的效率高（主键不可重复）
   3. 创建主键索引的列，它的值不能为null，且不能重复
   4. 主键索引的列基本上是int
2. 唯一索引的特点：
   1. 一个表中，可以有多个唯一索引
   2. 查询效率高
   3. 如果在某一列建立唯一索引，必须保证这列不能有重复数据
   4. 如果一个唯一索引上指定not null，等价于主键索引
3. 普通索引的特点：
4. 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
5. 如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

创建索引的原则

1. 比较频繁作为查询条件的字段应该创建索引
2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件
3. 更新非常频繁的字段不适合作创建索引
4. 不会出现在where子句中的字段不该创建索引

查询索引

show keys from XXX;
1

show index from XXX;
1

desc XXX;
1

其上三种都可以查询表中的索引，不过desc的方式信息比较简略

删除索引

alter table XXX drop primary key;
1

alter table XXX drop index 索引名
1

drop index 索引名 on 表名
1

注意索引名是指 Key_name 字段