MySQL索引

索引

索引：提高数据库的性能，索引是物美价廉的东西了。不用加内存，不用改程序，不用调sql，只要执行正确的 create index ，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO。所以它的价值，在于提高一个海量数据的检索速度。

常见索引分为：

1. 主键索引
2. 唯一键索引
3. 全文索引
4. 普通索引

MySQL 给用户提供存储服务，而存储的都是数据，数据在磁盘这个外设当中。
磁盘和内置之间进行IO的基本单位是4KB，但是这个单位太小就意味着要多次进行IO操作，效率就会低下。MySQL 作为一款应用软件，可以想象成一种特殊的文件系统。它有着更高的IO场景，所以，为了提高基本的IO效率， MySQL 进行IO的基本单位是 16KB，这个基本数据单元，在 MySQL 这里叫做page。

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

因为计算机中存在一个局部性原理，所以就算用来一个字节的数据加载了一个Page的大小也不过分，因为存在局部性原理。

理解索引

我们在表中插入数据时，如果存在主键，那么这个数据插进去就是有序的，是按照主键排的。如果没有主键，其实在表中还存在一个隐藏列，那么隐藏列就类似于主键也是有序的。

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

在查询某条数据的时候直接将一整页的数据加载到内存中，以减少硬盘IO次数，从而提高性能。

但是如果存在很多page的话，由于是双链表就只能线性查询，所以此时就需要页目录了。

页目录中存数据的主键和对应数据的地址，通过每个几个数据存一个，因为主键是有序的，可以通过比较找到就可以大概定位到那个数据附近的地址，可能还需要遍历，但是这次遍历的代价就很低了。这知识在一个page中，那如果有很多的oage，而我们要找的数据正好在最后怎么办，不能把从第一个page遍历到最后吧，这样效率也太低了。

这时我们就可以使用一个目录项来指向某一页，而这个目录项存放的就是将要指向的页中存放的最小数据的键值，和页内目录不同的地方在于，这种目录管理的级别是页，而页内目录管理的级别是行，其中，每个目录项的构成是：键值+指针。

存在一个目录页来管理页目录，目录页中的数据存放的就是指向的那一页中最小的数据。有数据，就可通过比较，找到该访问那个Page，进而通过指针，找到下一个Page。目录页的本质也是页，普通页中存的数据是用户数据，而目录页中存的数据是普通页的地址。这时就又存在一个问题，就是第二层的目录也太多了怎么办？
我们可以在上面再加一层。

此时也存在一个问题，就是第一层的也不够用怎么办，不同担心，一个page的大小是16KB，也就是说一个page大概可以存1000多个目录，这样指数级别下来，可以表示空间已经很大了。而这个结果就是B+树。现在查找的Page数一定减少了，也就意味着IO次数减少了，那么效率也就提高了。

1. 只有叶子节点保存有数据，路上节点没有，只有目录项，非叶子节点不存数据可以保存更多的目录，可以管理更多的叶子，所以这棵树一定是一个矮胖的，每一个节点都有目录项，可以大大的提高搜索效率，并且找到目标数据经过的节点少，需要的page减少，IO的次数也就减少了。由于以上的几点，这可数是可以提高效率的。
2. 叶子节点用链表链接起来，这是B+的特点，而且由于我们的目录不是每个数据都有的，我们也是需要范围查找的。

总结一下，索引就是数据结构B+;

聚簇索引 VS 非聚簇索引

MyISAM 引擎同样使用B+树作为索引结果，叶节点的data域存放的是数据记录的地址
MyISAM 最大的特点是，将索引Page和数据Page分离，也就是叶子节点没有数据，只有对应数据的地址。

正因为如此，以MyISAM为存储的表在磁盘中有三个文件，.frm是表的结构，.myi就是索引，因为索引中存的是地址，所以.myd中存放的才是数据.

MyISAM 这种用户数据与索引数据分离的索引方案，叫做非聚簇索引.

InnoDB 这种用户数据与索引数据在一起的。

所以它就只有两个文件。这种索引方案，叫做聚簇索引。

MySQL 除了默认会建立主键索引外，我们用户也有可能建立按照其他列信息建立的索引，一般这种索引可以叫做辅助（普通）索引。对于 MyISAM ,建立辅助（普通）索引和主键索引没有差别，无非就是主键不能重复，而非主键可重复。

索引的操作

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

1. 创建索引：

//1. 在创建表的时候，直接在字段名后指定 primary key，创建主键是系统会自动给我们创建主键索引
create table user1(id int primary key, name varchar(30));

//2. 在创建表的最后，指定某列或某几列为主键索引
create table user2(id int, name varchar(30), primary key(id));

//3.创建表以后再添加主键
alter table user3 add primary key(id);

//4. 在表定义时，在某列后直接指定unique唯一属性。创建唯一键时系统也会自动给我们创建索引，只不过是唯一键索引
create table user4(id int primary key, name varchar(30) unique);

//5. 创建表时，在表的后面指定某列或某几列为unique
create table user5(id int primary key, name varchar(30), unique(name));

// 6. 创建表以后再添加唯一键
alter table user6 add unique(name);

// 7. 创建普通索引
create table user8(id int primary key,
	name varchar(20),
	email varchar(30),
	index(name) --在表的定义最后，指定某列为索引
);

// 8. 创建完表以后指定某列为普通索引
alter table user9 add index(name); 

// 9.创建一个索引名为 idx_name 的索引
 create index idx_name on user10(name);

2. 查询索引

// 1
 show keys from 表名
// 2
show index from 表名;
// 3 信息很简略
desc 表名 
 

3. 删除索引

//1. 删除主键索引
alter table 表名 drop primary key;

//2. 索引名就是show keys from 表名中的 Key_name 字段
alter table 表名 drop index 索引名;

// 3
drop index name on user8;

创建索引的原则

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