mysql索引

文章目录

磁盘，磁道，扇区

提炼一下这张图说的话：
每一圈是一个磁道，每一个磁道又被分为了一个个扇区。每个扇区存储的数据量是一样的，其中最靠中心的扇区数据密度最大
扇区是512字节的，但是现在其实已经是4kb的了。多大不重要，知道即可

CHS和LBA（定位数据）

查找文件的过程就是定位扇区的过程。如何定位扇区？

定位扇区的过程

1. 先定位盘面，盘面就是哪一个盘的哪一个面（每一个盘有上下两面）。
2. 定位这个盘面的磁道track（也可以叫柱面）
3. 找到track之后再定位扇区

这种磁盘数据定位方式叫CHS。（C是Cylinder，柱面，也就是磁道，H是heads，磁头，用哪个磁头就可以确定是哪个盘面，S是sector）

这是物理寻址方法，对于操作系统来讲，并不会采用CHS的寻址方式。因为如果使用了这种方式，操作系统就必须知道关于硬盘的一切信息。假如有一天硬盘换了，操作系统用原来的方式寻址就找不到数据了，要改操作系统的代码。**因此操作系统使用的是将物理转虚拟的方式寻址，叫LBA(Logical Block Address)**。

LBA是非常单纯的一种定址模式﹔从0开始编号来定位区块，第一区块LBA=0，第二区块LBA=1，依此类推。（就好像数组一样）

这其实就是解耦的过程。

再画一个图帮助理解一下（不一定对，但是和这个过程是差不多的）

读取数据

读取数据的时候，是按照一个扇区为基本单位来读取的吗？
答案并不是，因为一个扇区的数据有可能太小了，以前扇区大小是512kb。拿数据就要多次IO，效率低。
读取数据时默认按照块来读取，块的大小是4kb

磁盘随机访问和连续访问

IO最耗时的操作并不是传送数据，而是进行磁头寻址。磁头在找磁道的时候要动来动去的去找，找到磁道之后，磁盘还要转，直到转到对应扇区。机械运动肯定比电信号要慢得多了。

因此磁盘连续访问会快很多，因为连续访问磁头不用怎么动，但是随机访问的话磁头就要经常动

MySQL 与磁盘交互基本单位：Page

操作系统在io的时候一次读4kb，mysql其实也相当于一个特殊的系统。它一次io读取16kb。
怎么看这个值呢？用命令：
SHOW GLOBAL STATUS LIKE ‘innodb_page_size’;

16384byte / 1024 = 16kb

mysql称一次io的数据块大小为页（page）

为什么交互要用page为单位呢？不是查多少加载多少呢？

原因是为了减少IO次数。其实16kb可以存很多信息了，如果查多少加载多少，io次数就变多了。如果一次加载一个page进来，其实就只需要进行一次io，后面几次都只需要在内存中拿数据即可。

总结一下：

• MySQL 中的数据文件，是以page为单位保存在磁盘当中的。
• MySQL 的 CURD 操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。而只要涉及计算，就需要CPU参与，而为了便于CPU参与，一定要能够先将数据移动到内存当中。所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。而这时，就涉及到磁盘和内存的数据交互，也就是IO了。而此时IO的基本单位就是Page。
• 为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为 Buffer Pool 的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互。为了更高的效率，一定要尽可能的减少系统和磁盘IO的次数

可以把数据结构也存在文件当中，比如说第一次生成了一颗二叉树，不想下次再重新生成了，就把这个二叉树放到文件里面了，下次读取之后就不用重新生成这颗二叉树了。（

索引的理解

先说结论：索引是存储引擎用于快速查找记录的一种数据结构。在MySQL中，也叫作 “键(key)”。在innodb里面，用B+树实现这个数据结构

其中叶子节点放的是数据，其他节点都是放的是目录

具体组织形式如下：

单个page

我们知道，mysql数据基本单位是page，page长这个样子
重点有两个

1. 页内目录
2. 数据按照key值有序 不同的 Page ，在 MySQL 中，都是 16KB ，使用 prev 和 next 构成双向链表。 因为有主键的问题， MySQL 会默认按照主键给我们的数据进行排序，从上面的Page内数据记录可以看出，数据是有序且彼此关联的。

排序的原因其实就是B+树插入的时候的插入排序。为的就是保持B+树性质，提高搜索效率。

还需要看一个点，page里面会有一个页内目录，来提高线性遍历的速度。

多个page

如果多个page直接用线性结构连接的话，复杂度会变高，并且IO次数也变多了。因为你要找到300慢羊羊，你就必须把前面两张page都从磁盘加载到buffer pool里面。

因此要用B+树形式来组织。

普通页存放数据，是叶子节点。其他节点是目录页，存放下面最小的key值。比如第二行第一个目录页，放的就是1和101，最小的key值。

总结一下：

1. Page分为目录页和数据页。目录页只放各个下级Page的最小键值。
2. 查找的时候，自定向下找，只需要加载部分目录页到内存，即可完成算法的整个查找过程，大大减少了IO次数

目录页只放key值的好处在哪？这其实也是为什么要用B+树而不用B树的原因。

因为page的大小是有限的，如果放kv结构，目录页能管理的数据页太少，会增加树的高度。更深意味着io次数变多。如果只放key，目录页就能管理更多的数据页，高度就低了。

B+树不是全部被加载进内存的

并不是一下就把整个B+树加载进内存的（不管是聚簇索引还是非聚簇索引）
比如现在我要找1喜羊羊，先加载根节点，发现喜羊羊在左边的目录页，然后加载左边的目录页，又发现在最左边的数据页，然后再加载最左边的数据页。总共就加载了三个page。

查找1喜羊羊的时候，只需要加载圈里面的数据即可，io次数能被降低

与其他数据结构比较一下

1.avl和红黑树：avl和红黑树高度太高了，io次数多。
2.hash，hash不适合范围查找。比如现在要找id>30且<100的，hash就得至少映射70次（有可能冲突），和线性查找是一样的了。而用B+就很方便，直接找到起始的id，直接往后遍历即可
3.B树。B+树比B树好在两点：1.非叶子节点只存key，使目录page可以管理更多数据，让树的高度更低，让io次数减少。2.叶子节点相连，更便于进行范围查找

如何通过索引查找数据（重要）

索引是在内存中生成的，可以理解成有一个进程生成了一个B+树，然后写入到了一个文件里面。
当下次我想去查找数据的时候，我就把这个索引文件读取到内存当中，进行查找，找到我想要的数据page的地址时候，再把数据page读入到内存中。

这个过程在MyISam引擎中可以很清晰的看到

聚簇索引和非聚簇索引

聚簇索引是数据data和索引index放在一个文件里。
非聚簇索引是数据data和索引index分开放。

innodb存储引擎是聚簇索引。刚刚创建了一个一个table
frm是存放表结构的，比如列名，有多少列等等信息。
ibd是放索引和数据的。

MyISAM是非聚簇索引
MYD就是data文件，可以看到大小是0
MYI是index文件，其实现在我并没有主动的创建索引，因此可以得出结论，即使你不主动创建索引，mysql还是会帮你根据一些key来创建index的。

MyISAM和innodb创建索引的差别

索引有键值索引，比如主键和唯一键。就是以主键或者唯一键为key。
索引还有普通索引，拿一个普通的数据当作key建立B+树。

MyISAM是非聚簇索引，innodb是聚簇索引

键值索引

MyISAM由于是非聚簇索引，它的数据和索引是分离的。所谓分离就是叶子节点其实存的也不是实际的数据，只是地址，拿到地址之后，再把磁盘中相应数据加载进内存中。

有一个误区就是认为地址在索引里面是聚簇索引，是错误的。只有真正的数据和索引在一起才是聚簇索引。

数据放的并不是数据，而是指向数据的地址。
MyISAM 引擎同样使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址。下图为 MyISAM 表的主索引，Col1 为主键。

innodb就不是存的是地址，当访问到叶子节点，就拿到数据了。

再总结一下两种查找数据的方式：

在MyISAM，先把整个索引加载进内存（MYI），在内存中的索引里查找key值，最后找到叶子节点拿到数据的地址，再去磁盘把对应地址的数据加载进内存里面（MYD）

在innodb，先把索引加载进内存（ibd），然后根据key找val，找到叶子节点就可以拿到对应的数据了。

普通索引

先讲MyISAM，MyISAM存储普通索引的方式和键值索引的方式没有什么不同，同样的是以列为key，最后叶子放的是数据的地址。
图示和上面MyISAM的主键是一样的。

再讲innodb，innodb的存储普通索引方式是非聚簇索引的方式。采用了回表的机制。

innodb的普通索引里叶子节点存放的是主键的key值，找到主键的key之后，然后再去主键的B+树里面找数据。这个过程叫做回表。

为什么innodb的普通索引叶子节点存的是key而不是直接存数据的地址？

辅助索引使用主键作为"指针"而不是使用地址值作为指针的好处是，减少了当出现行移动或者数据页分裂时辅助索引的维护工作

举个例子，如果直接存放数据的地址，现在如果要把其中一行数据删掉，我们就要对主键的B+树进行删除，还要对普通索引的B+树进行删除操作。

存主键而不存数据的地址可以保证普通索引的B+shu永远不会被行移动影响，这是一个解耦的过程。

建立索引的操作

主键索引

创建主键索引：方法和创建主键是一样的，不讲了。
主键索引的特点：

1. 一个表中，最多有一个主键索引
2. 主键索引的效率高（主键不可重复）
3. 创建主键索引的列，它的值不能为null，且不能重复
4. 主键索引的列基本上是int

唯一键索引

和创建唯一键是一样的

1. – 在表定义时，在某列后直接指定unique唯一属性。 create table user4(id int primary key, name varchar(30) unique);
2. – 创建表时，在表的后面指定某列或某几列为unique create table user5(id int primary key, name varchar(30), unique(name));
3. create table user6(id int primary key, name varchar(30)）； alter table user6 add unique(name);

唯一索引的特点：

1. 一个表中，可以有多个唯一索引
2. 查询效率高
3. 如果在某一列建立唯一索引，必须保证这列不能有重复数据
4. 如果一个唯一索引上指定not null，等价于主键索引

普通索引

和前两种格式一样，把primary key,unique换成index即可。
语法：index(xxx)

第一种
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); --创建完表以后指定某列为普通索引

第三种
create table user10(id int primary key, name varchar(20), email varchar(30));
– 创建一个索引名为 idx_name 的索引
create index idx_name on user10(name);

普通索引的特点：

1. 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多
2. 如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

全文索引

全文索引的作用是在一大段文字里面找关键字。和模糊匹配的功能是一样的，但是全文索引快。

explain关键字

explain可以解析这一行命令的详细信息。

下面说了这次select用了empno这个key来索引。

删除索引

第一种方法-删除主键索引： alter table 表名 drop primary key;
第二种方法-其他索引的删除： alter table 表名 drop index 索引名； 索引名就是show keys from 表名中的Key_name 字段
mysql> alter table user10 drop index idx_name;