MySQL索引分类

一）普通索引：

基本的索引，它没有任何限制。

创建方式：

//标准语句：
ALTER TABLE table_name ADD INDEX index_name (column_list)
CREATE INDEX index_name ON table_name (column_list); 
//还有建表的时候创建亦可
CREATE TABLE table_name ( 
ID INT NOT NULL, 
column_listVARCHAR(16) NOT NULL,
INDEX [index_name ] 
(column_list(length)) 
);  

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

例子：假如length为10，也就是索引这个字段的记录的前10个字符。

二）唯一索引：

与前面的普通索引类似，不同的就是：MySQL数据库索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

它有以下几种创建方式：

ALTER TABLE table_name ADD UNIQUE (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
//还有建表时创建
CREATE TABLE table_name (
 ID INT NOT NULL, 
 column_list VARCHAR(16) NOT NULL, 
 UNIQUE [index_name ]  
 (column_list(length)) 
 );  

三）主键索引：

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

CREATE TABLE table_name ( 
ID INT NOT NULL,
 [column] VARCHAR(16) NOT NULL,
 PRIMARY KEY(ID)  
 );  

四）全文索引：（FULLTEXT）

定义：

全文检索是对大数据文本进行索引，在建立的索引中对要查找的单词进行进行搜索，定位哪些文本数据包括要搜索的单词。因此，全文检索的全部工作就是建立索引和在索引中搜索定位，所有的工作都是围绕这两个来进行的。

此索引关键：

建立全文索引中有两项非常重要，一个是如何对文本进行分词，一是建立索引的数据结构。分词的方法基本上是二元分词法、最大匹配法和统计方法。索引的数据结构基本上采用倒排索引的结构。分词的好坏关系到查询的准确程度和生成的索引的大小。

应用：

FULLTEXT索引仅可用于 MyISAM 表；他们可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加。

但是要注意：对于较大的数据集，将你的资料输入一个没有FULLTEXT索引的表中，然后创建索引，其速度比把资料输入现有FULLTEXT索引的速度更为快。不过切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法。因为！！插入修改删除表的同时也要针对索引做一系列的处理。

创建方法：

//针对content做了全文索引：
CREATE TABLE `table` (
`id` int(11) NOT NULL AUTO_INCREMENT ,
`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
PRIMARY KEY (`id`),
FULLTEXT (content)
);

SQL使用全文索引的方法：首先必须是MyISAM的数据库引擎的数据表

如果是其他数据引擎，则全文索引不会生效。

SELECT * FROM article WHERE MATCH( content) AGAINST('想查询的字符串')

此外，MySQL自带的全文索引只能对英文进行全文检索，目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索，我们需要采用Sphinx(斯芬克斯)/Coreseek技术来处理中文。

注意：

目前，使用MySQL自带的全文索引时，如果查询字符串的长度过短将无法得到期望的搜索结果。MySQL全文索引所能找到的词的默认最小长度为4个字符。另外，如果查询的字符串包含停止词，那么该停止词将会被忽略。

如果可能，请尽量先创建表并插入所有数据后再创建全文索引，而不要在创建表时就直接创建全文索引，因为前者比后者的全文索引效率要高。

五）单列索引与多列索引（其实是相当于一个用法技巧）

单列索引，就是平常的只索引一个一个的字段的方式

//例子为name列的头10个字符创建一个索引：
CREATE TABLE test (
name CHAR(200) NOT NULL,
KEY index_name (name(10))
);

多列索引（也叫组合索引）：

相关概念（适用多列索引的原因）：

MySQL能在多个列上创建索引。一个索引可以由最多15个列组成。（在CHAR和VARCHAR列上，你也可以使用列的前缀作为一个索引的部分)。

一个多重列索引可以认为是包含通过合并(concatenate)索引列值创建的值的一个排序数组。

多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个单列索引中选择一个限制最为严格（获得结果集记录数最少）的索引。

当你为在一个WHERE子句索引的第一列指定已知的数量时，MySQL以这种方式使用多重列索引使得查询非常快速，即使你不为其他列指定值。

适用场景：

1.全字段匹配

2.匹配部分最左前缀

3.匹配第一列

4.匹配第一列范围查询(可用用like a%,但不能使用like %b)

5.精确匹配某一列和和范围匹配另外一列

例子：

//假设只使用单列索引名字
 ALTER TABLE people ADD INDEX name (name);
 //使用多列索引：
  ALTER TABLE people ADD INDEX height_name_age (height,name,age);
  //相当于创建了(height)单列索引，(height,name)组合索引以及(height,name,age)组合索引
/*
注意：
注：在mysql中执行查询时，只能使用一个索引，如果我们在name,age上分别建索引,执行查询时，只能使用一个索引，mysql会选择一个最严格(获得结果集记录数最少)的索引。
*/

注意：

在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。

组合索引（多列索引）的原则：

原则：

最左前缀：顾名思义，就是最左优先

平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引（多列索引）。例如上面使用的例子就相当于创建了(height)单列索引，(height,name)组合索引以及(height,name,age)组合索引。

---

此外，补充一个概念对比，那就是聚集索引和非聚集索引：

1）聚集索引：相关概念说法取自此处

定义：

该索引中键值的逻辑顺序决定了表中相应行的物理顺序。

聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。

注意事项：

定义聚集索引键时使用的列越少越好。

使用的场景：

一）包含大量非重复值的列。

二）使用下列运算符返回一个范围值的查询：BETWEEN、>、>=、< 和 <=。

三）被连续访问的列。

四）返回大型结果集的查询。

五）经常被使用联接或 GROUP BY 子句的查询访问的列；一般来说，这些是外键列。对 ORDER BY 或 GROUP BY 子句中指定的列进行索引，可以使 SQL Server 不必对数据进行排序，因为这些行已经排序。这样可以提高查询性能。

六）OLTP 类型的应用程序，这些程序要求进行非常快速的单行查找（一般通过主键）。

缺点：请看此博客

不适用于：

频繁更改的列 。这将导致整行移动（因为 SQL Server 必须按物理顺序保留行中的数据值）。这一点要特别注意，因为在大数据量事务处理系统中数据是易失的。

宽键 。来自聚集索引的键值由所有非聚集索引作为查找键使用，因此存储在每个非聚集索引的叶条目内。

2）非聚集索引：

定义：

数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置。

非聚集索引中的项目按索引键值的顺序存储，而表中的信息按另一种顺序存储（这可以由聚集索引规定）。对于非聚集索引，可以为在表非聚集索引中查找数据时常用的每个列创建一个非聚集索引。有些书籍包含多个索引。例如，一本介绍园艺的书可能会包含一个植物通俗名称索引，和一个植物学名索引，因为这是读者查找信息的两种最常用的方法。

两者的区别此处有个很清晰的例子：请点此处

选择使用的场景：

关于聚集索引以及非聚集索引的几个问题：

一）聚集索引的约束是唯一性，是否要求字段也是唯一的呢？

一般我们指定一个表的主键，如果这个表之前没有聚集索引，同时建立主键时候没有强制指定使用非聚集索引，SQL会默认在此字段上创建一个聚集索引，而主键都是唯一的，所以理所当然的认为创建聚集索引的字段也需要唯一。

聚集索引可以创建在任何一列你想创建的字段上，这是从理论上讲，实际情况并不能随便指定，否则在性能上会是恶梦。

二）|主键就是聚集索引？？？

这样有时会对聚集索引的一种浪费。Innodb将通过主键聚集数据，如果没有定义主键，Innodb会选择第一个非空的唯一索引代替，如果没有非空唯一索引，Innodb会隐式定义一个6字节的rowid主键来作为聚集索引。innodb只聚集在同一个页面中的记录，包含相邻键值的页面可能会相距甚远。

因为每个表中只能有一个聚集索引的规则，这使得聚集索引变得更加珍贵。

使用聚集索引的最大好处就是能够根据查询要求，迅速缩小查询范围，避免全表扫描。在实际应用中，因为 ID号是自动生成的，我们并不知道每条记录的ID号，所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主键作为聚集索引成为一种资源浪费。其次，让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则；当然，这种情况只是针对用户经常修改记录内容，特别是索引项的时候会负作用，但对于查询速度并没有影响。

三）是不是聚集索引就一定要比非聚集索引性能优呢？？？

如果想查询学分在60-90之间的学生的学分以及姓名，在学分上创建聚集索引是否是最优的呢？

答：否。既然只输出两列，我们可以在学分以及学生姓名上创建联合非聚集索引（也就是多列索引），此时的索引就形成了覆盖索引，即索引所存储的内容就是最终输出的数据，这种索引在比以学分为聚集索引做查询性能更好。

四）在数据库中通过什么描述聚集索引与非聚集索引的？

索引是通过二叉树的形式进行描述的，我们可以这样区分聚集与非聚集索引的区别：聚集索引的叶节点就是最终的数据节点，而非聚集索引的叶节仍然是索引节点，但它有一个指向最终数据的指针。

五）在主键是创建聚集索引的表在数据插入上为什么比主键上创建非聚集索引表速度要慢？

在有主键的表中插入数据行，由于有主键唯一性的约束，所以需要保证插入的数据没有重复。我们来比较下主键为聚集索引和非聚集索引的查找情况：聚集索引由于索引叶节点就是数据页，所以如果想检查主键的唯一性，需要遍历所有数据节点才行，但非聚集索引不同，由于非聚集索引上已经包含了主键值，所以查找主键唯一性，只需要遍历所有的索引页就行，这比遍历所有数据行减少了不少IO消耗。这就是为什么主键上创建非聚集索引比主键上创建聚集索引在插入数据时要快的真正原因。