【MySQL篇】索引特性-EW帮帮网

MySQL内部，将来Buffer pool缓冲区中一定需要并且存在大量的page，所以MySQL必须将这些page管理起来。通过“先描述，再组织”。所以page内部并不是单纯的存储数据，page内部也需要写入对应的管理信息。我们目前可以简单的理解成一个个独立的文件是由一个或者多个page构成的。

思考一下：为何MySQL和磁盘进行IO交互时候，要采用page的方案进行交互呢？为什么不是用多少加载多少呢？

因为预加载可以有效减少IO的次数。通过局部性原理，当前访问某些数据或代码的某一行时，下次访问可能会在这次访问的周边进行访问。

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

不同的page，再MySQL中都是16KB，使用prev和next构成双向链表。

上面的单个页，在查询数据的时候，直接将一整页的数据加载到内存，以减少硬盘IO次数，从而提高性能。但是，我们也可以看到，现在的页内部，实际上是采用了链表的结构，前一条数据指向后一条数据，本质上还是通过数据的逐条比较来取出特定的数据。
如果有1千万条数据，一定需要多个page页保存起来，多个page彼此采用双向链表连接起来，而每个page内部也是有链表来管理的。那么查找特定的一条记录，也一定是线性查找，效率太低了。

所以这时就需要引入目录了。

页目录：

就以书籍为例，每本数都有目录。我们如果要看指定的章节，找到章节有两种做法：

从头逐页的向后翻，直到找到目标内容。

通过提供的目录，找到指定的页数。当然在找目录的过程中，可以顺序查找，不过因为目录肯定少，所以可以快速提高定位。

本质上，书本中的目录是花了额外的纸做的，但却提高了效率。

所以，目录是一种空间换时间的做法。

单页情况：

针对之前链式结构的page页，我们可以引入页目录。

那么当前，在一个page内部，引入了页目录。比如，要查找id=4的记录，之前线性遍历4次，才能拿到结果。现在直接通过目录2，直接定位新的其实位置，提高了效率。

多页情况：

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

在单表数据不断被插入的情况下，MySQL会在容量不足的时候，自动开辟新的page来保存新的数据，然偶通过指针的方式马，将所有page组织起来。

这样我们就可以通过多个page的遍历，page内部通过目录快速定位数据。可是这样，貌似也有效率问题，在page之间，还是线性遍历，意味着还是需要大量的IO。将下一个page加载到内存，进行线性检测。这样就显得我们 page内部的目录有点杯水车薪了。

解决方案，给page也带上目录：

使用一个目录项来指向某一页，而这个目录项不存放数据，只存放它指向page中最小数据的键值
和页内目录不同，这种目录管理的是页，而页内目录管理的是数据。
其中，每个目录项的构成是键值+指针。

目录页（page目录）管理一个个的目录，目录页中的数据存放的就是指向那一页中最小的数据。通过该数据，与我们要查找的数据进行比较，找到访问哪个page。

对于一个page目录，它的大小是16KB，假设忽略掉前后指针，该page只存储一个数据和对应的指针，在64位环境下，16*1024/(4+8)=1365。即一个page目录，大概可以管理1365个page。也就是1365*1024/1024/1024=21MB，大概可以管理21MB的数据。

但是，我们的page目录也可能会产生线性遍历造成的多次IO，降低效率问题。同样，我们可以在上层再加一层目录page。

一般而言，两三层的设计已经足够了，可以管理特别大的数据。

这个结构就是B+树。

但是，实际存储的时候，除了叶子节点之间还会以链表的形式连接，其他节点都不会连接。这刚好符合B+树！

注意：

叶子节点保存有数据，其他节点不保存数据，只保存目录项。？？？

原因：非叶子节点不保存数据，那么就可以存储更多的目录项，目录页，就可以管理更多的page。换句话说，查找数据时，可以淘汰掉的目录页更多，进行 IO的次数就可以大大减少。在IO层面，提高了效率。同时，每一个page节点，都有目录项，大大提高了搜索效率。

叶子节点为什么全部链接起来？？？

首先，这是B+树的特点。所以MySQL使用这种数据结构。

方便进行范围查找。

上面的图，描述的就是MySQL innodb 下的索引结构。我们在建表的时候，就会生成这样一颗B+树，他会将我们表中的主键一列作为索引，而如果我们在建表的时候没有指明主键，系统会默认生成一个主键。一般我们插入数据的时候，就是在该结构下进行CURD的。

总结：

page分为目录页和数据页。目录页只存放各个下级page的最小键值。

查找的时候，自顶向下，只需加载部分目录页到内存，即可完成查找过程，大大较少了IO次数。

五，聚簇索引和非聚簇索引

前面所讲到的都是innodb存储引擎下的结构。

聚簇索引：innodb存储引擎下的结构就是聚簇索引，在叶子节点中，索引page和数据page放在一起存储。

非聚簇索引：MyISAM存储引擎下的结构就是非聚簇索引，在叶子节点中，索引page和数据page分开存储。也就是说叶子节点没有数据，只有对应数据的地址。

验证：

mysql> create table test1(
-> id int primary key,
-> name varchar(20) not null)engine=innodb;

mysql> create table test2( id int primary key, name varchar(20) not nuull)engine=MyISAM;

当然，MySQL除了默认会建立主键索引外，我们用户也有可能按照其他列信息建立索引，一般这种索引叫做普通索引。

对于MyISAM而言，建立主键索引和普通索引没有区别，主键索引的叶子节点存储的是指向数据的指针，那么创建普通索引的时候，就是再创建一个B+树，以指定列作为键值，叶子节点存储指向数据的指针即可。所以，建立普通索引和主键索引没有区别。

而对于Innode而言，我们知道主键索引对应的B+树，叶子节点会存放主键值和数据。在我们创建普通索引的时候，同样会创建一颗B+树，以指定列为键值，但是叶子节点中不存储数据，而是存储主键值。在查数据的时候，需要两边索引：首先通过普通索引（普通索引对应的B+树）查找到主键值，然后通过主键值在主键索引（主键对应的B+树）中查找数据。这种过程，叫做回表查询。

所以，建立索引本质就是以该列为键值，创建一颗B+树。

六，索引操作

查询索引

show keys from 表名；

show index from 表名；

创建主键索引

第一种方式

-- 在创建表的时候，直接在字段名后指定 primary 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);

主键索引的特点：

创建主键索引的列，它的值不能为null，且不能重复

主键索引的列基本上是int

一个表中，最多有一个主键索引，当然可以使用复合主键

主键索引的效率高（主键不可重复）

唯一索引的创建

第一种方式

-- 在表定义时，在某列后直接指定 unique 唯一属性。

create table user4(id int primary key, name varchar ( 30 ) unique);

第二种方式

-- 创建表时，在表的后面指定某列或某几列为 unique

create table user5(id int primary key, name varchar ( 30 ), unique(name));

第三种方式

create table user6(id int primary key, name varchar(30)）；

alter table user6 add unique(name);

唯一索引的特点：

一个表中，可以有多个唯一索引

查询效率高

如果在某一列建立唯一索引，必须保证这列不能有重复数据

如果一个唯一索引上指定not null，等价于主键索引

案列：

mysql> alter table test1 add unique(name);

普通索引的创建

第一种方式

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);//给索引起名字idx_name

普通索引的特点：

一个表中可以有多个普通索引，普通索引在实际开发中用的比较多

如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引

全文索引的创建

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。 MySQL 提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM ，而且默认的全文索引支持英文，不支持中文。

案列：

mysql> create table articles(
-> id int unsigned auto_increment not null primary key,
-> title varchar(200),
-> body text,
-> FULLTEXT(title,body))engine=myisam;

查询文章中有没有database数据

如果使用如下查询方式，虽然查询出数据，但是没有使用到全文索引：

mysql> select * from articles where body like '%database%';

可以使用 explain工具看一下，是否使用到索引

使用全文索引：

mysql> select * from articles where match(title,body) against ('database');

删除索引

删除主键索引

alter table 表名 drop primary key;

删除其他索引

alter table 表名 drop index 索引名；

//索引名就是show keys from表名结果种的key_name字段

drop index 索引名 on 表名；

索引创建原则

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

【MySQL篇】索引特性

一，初识索引

二，MySQL与磁盘交互的基本单位

三，MySQL中数据文件的特性

四，理解page和索引

五，聚簇索引和非聚簇索引

六，索引操作

查询索引

创建主键索引

唯一索引的创建

普通索引的创建

全文索引的创建

删除索引

索引创建原则

网站公告

今日签到

热门文章

最新发布