MySQL索引介绍

简介

索引：是一种特殊的文件，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

索引分类

• 普通索引

允许被索引的数据列包含重复的值。比如说，因为人有可能同名，所以同一个姓名在同一个“员工个人资料”数据表里可能出现两次或更多次。

给name字段添加普通索引：
CREATE INDEX ind_user_info_name ON user_info(name);

• 唯一索引

可以保证数据记录的唯一性。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字UNIQUE把它定义为一个唯一索引。这么做的好处：一是简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率；二是MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。事实上，在许多场合，人们创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

给pass字段添加唯一索引：
CREATE UNIQUE INDEX uni_user_info_pass ON user_info(pass); 

• 主键索引：它是一种特殊的唯一索引，不允许有空值。 
• 组合索引：为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。创建复合索引时应该将最常用（频率）作限制条件的列放在最左边，依次递减。（条件中不可以出现> < 之类的条件）
• 全文索引：仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时耗空间。                               
• 空间索引：仅可用于 MyISAM 表。                                

聚簇索引

强调的是物理分类。这种索引可以说是按照数据的物理存储进行划分的。对于一堆记录来说，使用聚簇索引就是对这堆记录进行堆划分。即主要描述的是物理上的存储。聚集索引是唯一的（因为聚集索引的划分依据是物理存储）。在一张表上最多只能创建一个聚集索引，因为真实数据的物理顺序只能有一种。

举个例子： 比如图书馆新进了一批书。那么这些书需要放到图书馆内。书如何放呢？一般都有一个规则，杂志类的放到101房间，文学类的放到102房间，理工类的放到103房间等等。这些存储的规则决定了每本书应该放到哪里。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。而这个例子中聚集索引为书的类别。 正是因为这种存储规则，才导致聚集索引的唯一性。

非聚簇索引

强调的是逻辑分类。可以说是定义了一套存储规则，而需要有一块控件来维护这个规则，这个被称之为索引表。

举个例子：

同学如果想去图书馆找一本书，而不知道这本书在哪里？那么这个同学首先应该找的就是 检索室吧。对于要查找一本书来说，在检索室查是一个非常快捷的的途径了吧。但是，在检索室中你查到了该书在XX室XX书架的信息。你的查询结束了吗？没有吧。你仅仅找到了目的书的位置信息，你还要去该位置去取书。

存储引擎

• InnoDB存储引擎：使用的是聚集索引，支持事务，支持外键约束，表锁、行锁，支持自动增长列
• MyISAM存储引擎：使用的是非聚集索引，不支持事务、不支持外键，表锁，优势是访问速度快
• MEMORY存储引擎：
• MERGE存储引擎：

Mysql 底层数据引擎以插件形式设计，最常见的是 Innodb 引擎和 Myisam引擎，用户可以根据个人需求选择不同的引擎作为 Mysql 数据表的底层引擎。我们刚分析了，B+树作为 Mysql 的索引的数据结构非常合适，但是数据和索引到底怎么组织起来也是需要一番设计，设计理念的不同也导致了 Innodb 和 Myisam 的出现，各自呈现独特的性能。

MyISAM 虽然数据查找性能极佳，但是不支持事务处理。Innodb 最大的特色就是支持了 ACID 兼容的事务功能，而且他支持行级锁。Mysql 建立表的时候就可以指定引擎，比如下面的例子，就是分别指定了 Myisam 和 Innodb 作为 user 表和 user2 表的数据引擎。

CREATE TABLE user2 (
id int(11) NOT NULL DEFAULT '0' ,
username varchar (255) NOT NULL,
PRIMARY KEY ( id )
) ENGINE=myisam DEFAULT CHARSET=utf8;

CREATE TABLE user (
id' int (11) NOT NULL DEFAULT 'O' ,
username varchar (255) NOT NULL,
PRIMARY KEY ( id)
) ENGINE= InnoDB DEFAULT CHARSET=utf8;

执行这两个指令后，系统出现了以下的文件，说明这两个引擎数据和索引的组织方式是不一样的。

Innodb 创建表后生成的文件有：

frm:创建表的语句
idb:表里面的数据+索引文件

Myisam 创建表后生成的文件有:

frm:创建表的语句
MYD:表里面的数据文件（myisam data）
MYI:表里面的索引文件（myisam index）

从生成的文件看来，这两个引擎底层数据和索引的组织方式并不一样，MyISAM 引擎把数据和索引分开了，一人一个文件，这叫做非聚集索引方式；Innodb 引擎把数据和索引放在同一个文件里了，这叫做聚集索引方式。下面将从底层实现角度分析这两个引擎是怎么依靠 B+树这个数据结构来组织引擎实现的。

InnoDB 引擎

InnoDB 是聚集索引方式，因此数据和索引都存储在同一个文件里。首先 InnoDB 会根据主键 ID 作为 KEY 建立索引 B+树，而 B+树的叶子节点存储的是主键 ID 对应的数据，比如在执行 select * from user_info where id=15 这个语句时，InnoDB 就会查询这颗主键 ID 索引 B+树，找到对应的 user_name='Bob'。

这是建表的时候 InnoDB 就会自动建立好主键 ID 索引树，这也是为什么 Mysql 在建表时要求必须指定主键的原因。当我们为表里某个字段加索引时 InnoDB 会怎么建立索引树呢？比如我们要给 user_name 这个字段加索引，那么 InnoDB 就会建立 user_name 索引 B+树，节点里存的是 user_name 这个 KEY，叶子节点存储的数据的是主键 KEY。注意，叶子存储的是主键 KEY。拿到主键 KEY 后，InnoDB 才会去主键索引树里根据刚在 user_name 索引树找到的主键 KEY 查找到对应的数据。

一个表里可能有很多个索引，InnoDB 都会给每个加了索引的字段生成索引树，如果每个字段的索引树都存储了具体数据，那么这个表的索引数据文件就变得非常巨大（数据极度冗余了）。从节约磁盘空间的角度来说，真的没有必要每个字段索引树都存具体数据，通过这种看似“多此一举”的步骤，在牺牲较少查询的性能下节省了巨大的磁盘空间，这是非常有值得的。

MyISAM 引擎

MyISAM 用的是非聚集索引方式，即数据和索引落在不同的两个文件上。MyISAM 在建表时以主键作为 KEY 来建立主索引 B+树，树的叶子节点存的是对应数据的物理地址。我们拿到这个物理地址后，就可以到 MyISAM 数据文件中直接定位到具体的数据记录了。

当我们为某个字段添加索引时，我们同样会生成对应字段的索引树，该字段的索引树的叶子节点同样是记录了对应数据的物理地址，然后也是拿着这个物理地址去数据文件里定位到具体的数据记录。

在进行 InnoDB 和 MyISAM 特点对比时谈到，MyISAM 查询性能更好，从上面索引文件数据文件的设计来看也可以看出原因：MyISAM 直接找到物理地址后就可以直接定位到数据记录，但是 InnoDB 查询到叶子节点后，还需要再查询一次主键索引树，才可以定位到具体数据。等于 MyISAM 一步就查到了数据，但是 InnoDB 要两步，那当然 MyISAM 查询性能更高。