数据库中的索引

一、索引的好处

首先，我们先看一个例子。

再上图中我们有一个8000000数据的表叫EMP，一开始我们没有主键查找一个值花了4.07秒，若加上了主键花的时间小于0.01秒，所以在大量数据中查找有没有索引就尤为重要。

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

二、认识磁盘

在磁盘中找数据：磁头（head，决定数据在哪一面）-> 磁道（track）柱面（cylinder）（决定在面上的哪个同心圈）-> 扇区（sector，决定在哪个区）

上述在硬件中查找数据方式：CHS

在操作系统中查找方式：LBA    虚拟地址->物理地址

系统读磁盘以块为单位，基本单位4KB

随机访问：本次IO的扇区地址与上次IO地址不连续，这样磁头两次IO之间要有较大的移动才能读写数据。

连续访问：本次IO的扇区地址与上次IO地址连续。

当然操作系统会对地址进行排序，尽量以连续方式进行IO

三、MySQL与磁盘交互的基本单位

上图中InnoDB中的page大小就是16KB

MySQL 中的数据文件，是以page为单位保存在磁盘当中的。

MySQL 的 CURD 操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。 而只要涉及计算，就需要CPU参与，而为了便于CPU参与，一定要能够先将数据移动到内存当中。

所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新 到磁盘。而这时，就涉及到磁盘和内存的数据交互，也就是IO了。

而此时IO的基本单位就是Page。 为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为 Buffer Pool 的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互。 为了更高的效率，一定要尽可能的减少系统和磁盘IO的次数。

四、page在MySQL中的理解

1、建模

上面我们提到MySQL中有大量数据存放在page中，所以MySQL中有大量的page，要把他们管理起来就要先描述再组织，在Buffer Pool中建模。

struct page

{

        struct page* prev;

        struct page* next;

        char buff[NUM];

}

2、为什么要用page进行IO交互

预加载机制有效利用局部性原理提高IO效率。

预加载机制：单个page大小16KB，可以加载大量数据。

局部性原理：加载周边数据，预测用户下次查找地址，减少IO次数，提高效率。

五、InnoDB中的索引结构

但是上述的page组织结构存在很大问题，由于是链表形式链接一个个page，导致查询结果是O(N)，是绝对不能接受的，所以引入页目录。然而我们知道数据的组织形式是page内部存有数据，page外部再用数据结构连接，所以要想提高效率就要从这两方面入手。

1、理解单个page

由于单个page中有大量数据，那么我们在其中设置目录就能范围查找，加快速度。

所以我们理解了为什么无序插入带有主键的表，出来的主键是有序的，那是因为只有有序才能在目录查找时是有意义的。

2、多页情况

这就像是B+树的结构，非叶子节点不存数据，存放叶子节点的索引，只有在最后一层才存放完整数据，并且用链表链接（上图是错的，非叶子节点是没有链表把page链接在一起的）

细节

（1）非叶子节点不存数据，可以储存更多的目录，管理更多的page

（2）查找时只要加载部分目录到内存即可完成查找，减少IO次数。

六、InnoDB与MyISAM引擎

MyISAM最大的特点就是索引page和数据page分离，叶子结点只有数据地址，是非聚簇索引。

而InnoDB是将数据和索引放一起模式聚簇索引。

值得一提的是在InnoDB中创建辅助索引，叶子结点并没有数据，只有主键值，然后用主键值在主键索引树中查到数据，叫做回表查询。

那为什么不放数据呢？没必要存多份数据，太浪费空间。

具体介绍可查看我的文章http://t.csdnimg.cn/D53Du

截图以供参考

七、索引操作

1、创建主键索引

（1）create table user1(id int primary key, name varchar(30));

（2）create table user2(id int, name varchar(30), primary key(id));

（3）create table user3(id int, name varchar(30));

          alter table user3 add primary key(id);

主键索引的特点

（1） 一个表中，最多有一个主键索引，当然可以使复合主键 主键索引的效率高（主键不可重复） （2）它的值不能为null，且不能重复。

（3）主键索引的列基本上是int

2、创建唯一索引

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

（2）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）如果一个唯一索引上指定not null，等价于主键索引。

3、创建普通索引 

（1）create table user8(id int primary key, name varchar(20), email varchar(30), index(name));

（2）create table user9(id int primary key, name varchar(20), email varchar(30));

           alter table user9 add index(name); 

（3）create table user10(id int primary key, name varchar(20), email varchar(30));

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

普通索引的特点

（1）一个表中可以有多个普通索引。

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

4、删除索引

（1）删除主键索引

alter table 表名 drop primary key;

（2）删除其他索引 

alter table 表名 drop index 索引名； 索引名就是show keys from 表名中的 Key_name 字段

drop index 索引名 on 表名 ;

5、查询索引

（1）show keys from 表名;

（2）show index from 表名;

（3）desc 表名；

6、全文索引

explain sql语句;    用来解释要怎么执行sql语句

举例

上图是创建了一个文本。

当我们用传统的where子句查询时发现key:NULL，说明没有用到索引查询，效率必然低。

语法

select * from 表名 where match (全文索引名) against ('关键字');

这时key:title就表示用title充当全文索引来查找关键字'database'

7、复合索引

语法

alter table 表名 add index (索引名1，索引名2....)

会以索引名1来创建B+树

上图我们就把name和email当做复合索引

我们知道用普通索引找一个值会找2次，但是用复合索引，并且索引绑定了我们要查找的值，我们就可以只找一次。

例如，index(name, email)后，我们要找张三的邮箱，只要name找到张三就行，返回绑定的邮箱数据，而这种未通过主键找到email的行为就是索引覆盖，覆盖的是主键。

在上述例子中，我们可以拿“张三”或“张三+邮箱”来查找数据，但是不能只拿“邮箱”查找数据，这就是​​​​​​​索引的最左匹配原则。