MySQL 索引有哪几种？详解（主键、唯一、普通、组合，全文、前缀索引）

1.1索引是怎样分类的？

逻辑应用维度

  1. 主键索引：主键索引是一种特殊的唯一索引，不允许有空值（唯一索引允许值为空）。
  2. 普通索引或者单列索引：每个索引只包含单个列，一个表可以有多个单列索引。
  3. 多列索引（复合索引、联合索引）：复合索引指多个字段上创建的索引，只有在查
    询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合
  4. 唯一索引:表中字段的值不能出现重复的，但允许为null值。
  5. 空间索引：空间索引是对空间数据类型的字段建立的索引，空间索引只能在存储引擎为 MYISAM 的表中创建。

物理存储维度

  1. 聚簇索引(clustered in dex )：在 InnoDB 中一张表只有一个聚簇索引(一般可以理解为主键索引)，索引和数据是在一起的。
  2. 非聚簇索引(non-clustered index)：也 叫 辅 助 索 引（ s e co nd a ry in d ex）非聚簇索引是索引和数据分离的。

数据存储结构维度

  1. B+树索引。（最重要,innodb默认）
  2. Hash 索引。
  3. Full-Text 全文索引。
  4. R-Tree 索引。

1.2如何创建索引

1.2.1 普通索引

普通索引为 MySQL 中的基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

创建普通索引的方式如下：

CREATE TABLE tablename (…,INDEX[索引名字](字段名));
CREATE INDEX 索引的名字 ON tablename(字段名);
ALTER TABLE table_name ADD INDEX index_name (column_name);

1.2.2 唯一索引

唯一索引与普通索引类似，只是索引列中的值必须是唯一的，但是允许为空值。

CREATE TABLE tablename (…,UNIQUE[索引名字](字段名));
CREATE UNIQUE INDEX 索引的名字 ON tablename(字段名);
ALTER TABLE table_name ADD UNIQUE INDEX index_name (column_name);

1.2.3 主键索引

主键索引是一种特殊的唯一索引，索引列中的值必须是唯一的，并且不允许有空值。

CREATE TABLE table_name (…,PRIMARY KEY (字段名))
ALTER TABLE table_name ADD PRIMARY KEY (column_name);

1.2.4 组合索引

组合索引又称为复合索引，在表中的多个字段组合上创建的索引,组合索引的使用，需要遵循最左前缀原则（最左匹配原则）。一般情况下，建议使用组合索引代替单列索引（主键索引除外）

CREATE TABLE table_name (…,INDEX index_name (column1,column2,…))
CREATE INDEX index_name ON table_name (column1,column2,…))
ALTER TABLE table_name ADD INDEX index_name (column1,column2,…);

说明：

  1. 复合索引的字段是有顺序的，在查询使用时要按照定义时索引字段的顺序使用，例如select * from test where name=xxx and phone=xxx;匹配(name,age)组合索引，不匹配(age,name)索引。
  2. 如果表已经建立了(col1,col2)，就没有必要再单独建立(col1);

1.2.5 全文索引

只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行 like 模糊查询时效率比较低，这时可以创建全文索引。

数据量大或者并发度高的时候一般是用专业的工具 lucene,es，solr。

CREATE TABLE table_name (…,FULLTEXT KEY index_name (column)); CREATE FULLTEXT INDEX index_name ON table_name (column);
ALTER TABLE `t_fulltext` ADD FULLTEXT INDEX`idx_content`(`content`);

1.2.6 前缀索引

在文本类型如 CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。

ALTER TABLE table_name ADD INDEX index_name (column1(length));