MYSQL——索引

索引可以大大提高搜索的效率，假如有十万个数据，没有索引直接遍历查询可能需要5s左右，因为mysql需要频繁的进行磁盘读取，但是有索引就可以很快查到。那么索引的原理是什么呢？

索引原理

mysql的是以page为存储单位的，每个page大小16KB，page结构体中有两个page指针，用来寻找上一个和下一个page结构体，还存储了多条记录，记录按照索引值的大小排列。因此，每个page都存储了一定索引值范围的记录。

mysql中会有很多page结构体，即使使用这样的存储方式依然要遍历很多page，效率依然很低。所以mysql使用目录page结构体，这个结构体不存记录，只存储page结构体的索引范围，可以理解为page结构体的索引。所以要搜索记录就先在目录page寻找对应范围的page，再在page中寻找索引对应的记录。另外，page中也会有记录索引值的目录，为了图的简洁就不画出来了。

如果记录很多，也可以创建多层目录page，一般情况下，2层目录page就足够了。其实这样的结构就是B+树，为什么不使用其他数据结构呢？首先线性结构例如链表显然不可用，其次二叉树或者红黑树这样的树在存储庞大数据时是“瘦高”的，树每高一层就意味着多从磁盘中读一次数据，而B+树是一个多叉树，是“矮胖”的，意味着读取磁盘数据的次数更少。最后，hash结构确实可以用来搜索，但是遇到范围搜索就没有B+树效率高了，因为hash一次只能找一个数据，而B+树的page中存储了相邻索引的多个数据。

索引的种类

索引分为聚簇索引和非聚簇索引。

innoDB使用聚簇索引，叶节点的page中存储的是记录；而MyISAM使用非聚簇索引，叶节点的page中存储的是记录的地址。

 frm文件存储表结构，区别就在于innoDB将数据和索引都存在ibd文件，而MyISAM是分开存的，将数据放在MYD文件，将索引放在MYI文件。

索引的使用

索引在使用中也分为多种，主键索引，唯一键索引，普通索引和全文索引。

索引创建

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 user4(id int primary key, name varchar(30) 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);

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));
create index idx_name on user10(name)；

复合索引

也可以将多列作为索引，查找索引时虽然显示了两个索引，但是这两个索引是同一个，因为索引名字相同：

索引查询

show index from 表名;

除了索引时指定索引名，其他普通索引的名字Key_name都是列名。 

索引删除

--删除主键索引

alter table 表名 drop primary key;

--删除普通索引

--方法一
alter table 表名 drom index 索引名;
--方法二
drop index 索引名 on 表名;

原文地址：https://blog.csdn.net/weixin_74269833/article/details/143466947

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！