MySQL索引原理之索引类型

　　MySQL索引原理之索引原理 - 池塘里洗澡的鸭子 - 博客园 (cnblogs.com)中介绍了索引的原理，那么根据索引的原理及其维护可以将索引分类，可参考下图：

　　　　

 　　索引可以提升查询速度，会影响where查询，以及order by排序。MySQL索引类型如下：

　　　　　　1）从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引、复合索引等

　　　　　　2）从索引键值类型划分：主键索引、辅助索引（二级索引）

　　　　　　3）从索引个数划分：单列索引、复合索引

　　　　　　4）从索引分区划分：非分区、分区索引等

　　　　　　5）逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

　　各类型索引详细介绍如下：

　　　　1、普通索引，这是最基本的索引类型，基于普通字段建立的索引，没有任何限制。创建普通索引的方法如下：

　　　　　　CREATE INDEX <索引的名字> ON tablename (字段名);
　　　　　　ALTER TABLE tablename ADD INDEX [索引的名字] (字段名);
　　　　　　CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名) )
　　　　2、唯一索引，与"普通索引"类似，不同的就是：索引字段的值必须唯一，但允许有空值 。在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引。创建唯一索引的方法如下：

　　　　　　CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);

　　　　　　ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);

　　　　　　CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (字段名)

　　　　3、主键索引，它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键。创建主键索引的方法如下：

　　　　　　CREATE TABLE tablename ( [...], PRIMARY KEY (字段名) );

　　　　　　ALTER TABLE tablename ADD PRIMARY KEY (字段名);

　　　　　　就MYSQL而言，B+Tree的叶子节点存放的是主键字段值就属于主键索引；如果存放的是非主键值就属于辅助索引（二级索引）。在InnoDB引擎中，主键索引采用的就是聚簇索引结构存储。

　　　　4、辅助索引：InnoDB辅助索引，也叫作二级索引，是根据索引列构建 B+Tree结构。但在 B+Tree 的叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多， 通常创建辅助索引就是为了提升查询效率。一个表InnoDB只能创建一个聚簇索引，但可以创建多个辅助索引。

　　　　　　　　　　　　

　　　　5、复合索引，单一索引是指索引列为一列的情况，即新建索引的语句只实施在一列上；用户可以在多个列上建立索引，这种索引叫做组复合索引（组合索引）。复合索引可以代替多个单一索引，相比多个单一索引复合索引所需的开销更小。索引同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超过2列的索引，设计索引的一个重要原则就是能用窄索引不用宽索引，因为窄索引往往比组合索引更有效。创建组合索引的方法如下：

　　　　　　CREATE INDEX <索引的名字> ON tablename (字段名1，字段名2...);

　　　　　　ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1，字段名2...);

　　　　　　CREATE TABLE tablename ( [...], INDEX [索引的名字] (字段名1，字段名2...) );

　　　　复合索引使用注意事项：

　　　　　　何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响。如果表已经建立了(col1，col2)，就没有必要再单独建立（col1）；如果现在有(col1)索引，如果查询需要col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。

　　　　6、全文索引，查询操作在数据量比较少时，可以使用like模糊查询，但是对于大量的文本数据检索，效率很低。如果使用全文索引，查询速度会比like快很多倍。在MySQL 5.6 以前的版本，只有MyISAM存储引擎支持全文索引，从MySQL 5.6开始MyISAM和InnoDB存储引擎均支持。创建全文索引的方法如下：

　　　　　　CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);

　　　　　　ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);

　　　　　　CREATE TABLE tablename ( [...], FULLTEXT KEY [索引的名字] (字段名) ;

　　　　和常用的like模糊查询不同，全文索引有自己的语法格式，使用 match 和 against 关键字，比如全文索引使用注意事项：

　　　　　　全文索引必须在字符串、文本字段上建立。

　　　　　　全文索引字段值必须在最小字符和最大字符之间的才会有效。（innodb：3-84；myisam：4-84）

　　　　　　全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa

　　　　　　全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a：

　　　　　　　　select * from user where match(name) against('a*' in boolean mode);

　　　　7、聚簇索引和非聚簇索引：聚簇索引是一种对磁盘上实际数据重新组织一按指定的一个或多个列的值排序的一种索引。就MYSQL数据库索引实现原理（具体参考MySQL索引原理之索引原理 - 池塘里洗澡的鸭子 - 博客园 (cnblogs.com)）而言，B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引；如果索引值和行记录分开存放就属于非聚簇索引。

　　　　　　InnoDB的聚簇索引就是按照主键顺序构建 B+Tree结构。B+Tree的叶子节点就是行记录，行记录和主键值紧凑地存储在一起。 这也意味着 InnoDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。通常说的主键索引就是聚集索引。

　　　　　　InnoDB的表要求必须要有聚簇索引：

　　　　　　　　如果表定义了主键，则主键索引就是聚簇索引；

　　　　　　　　如果表没有定义主键，则第一个非空unique列作为聚簇索引，否则InnoDB会从建一个隐藏的row-id作为聚簇索引

　　　　　　与InnoDB表存储不同，MyISAM数据表的索引文件和数据文件是分开的，被称为非聚簇索引结构。
　　　　　　　　　　　　

 　　　　对于聚簇索引与非聚簇索引是不是有些绕糊涂了？其实它们的不同之处很明确：

　　　　　　1）聚簇索引一个表只能有一个，而非聚簇索引一个表可以存在多个。

　　　　　　2）聚簇索引存储记录是物理上连续存在，物理存储安装索引排序；而非聚簇索引时逻辑上的连续物理存储上并不连续，即不要求物理存储按照索引排序。

　　　　　　3）聚簇索引查询数据比非聚簇索引速度快，插入数据速度慢；非聚簇索引反之。

　　　　　　4）聚簇索引的叶结点就是数据结点，非聚簇索引的叶结点仍然是索引结点，只不过有一个指针指向对应的数据库。

　　MySQL中的所有实在存储引擎层中实现的，而不是在服务器层实现的。所以，每种存储引擎的索引都不一定完全相同，也不是所有的存储引擎都支持所有的索引类型。