索引:个人的理就是，索引是一种加快查询数据的数据结构。

索引种类

1. 主键索引」（PRIMARY KEY）：一般在创建表的时候指定，「一个表只有一个主键索引」，特点是「唯一、非空」。

2. 「唯一索引」（UNIQUE）：特点是唯一性，可以在创建表的时候指定，也可以在创建表后创建。

3. 「普通索引」（INDEX）：唯一的作用就是加快查询。

4. 「组合索引」（ INDEX）：创建一个「多个字段的索引」，这个概念是相对于上上面的单列索引而言，组合索引遵循「最左前缀原则」。

5. 「全文索引」（FULLTEXT）：针对大的「文本字段」创建的索引，也称为「全文检索」。

6. 「聚簇索引」和「非聚簇索引」：聚簇索引和非聚簇索引的概念比上面的概念要大，属于包含和被包含的关系。Mysql主键索引使用的就是聚簇索引。

查看一个表的所有索引，可以执行"show index from 表名"来查看: Key_name表示索引的名字，Column_name表示索引的字段。

• 主键索引在InnoDB存储引擎中是最常见的索引类型，一个表都会有一个主键索引，它索引的字段不允许为空值，并且唯一。

• 在创建表的时候，可以通过RIMARY KEY指定主键索引，在InnoDB存储引擎中，若是创建表的时候没有主观创建主键索引，Mysql就会看表中是否有唯一索引，有，就会指定「非空的唯一索引」为主键索引；没有，就会默认生成一个6byte空间的自动增长主键作为主键索引，可以通过select _rowid from 表名查询的是对应的主键值.。

• MyISAM储存引擎是可以不存在主键索引，MyISAM和InnoDB储存数据的结构方式有明显的区别

• 唯一索引与主键索引的区别就是，唯一索引允许为空，若是在组合索引中，只要创建的列值是唯一的

• 唯一索引在实际中更多的是用来保证数据的唯一性，如仅仅要数据能够快速查询，可以使用普通索引

• 创建唯一索引的方式有三种。
  （1）在创建表的时候指定：

CREATE TABLE user( 
 id INT PRIMARY KEY NOT NULL, 
 name VARCHAR(16) NOT NULL, 
 UNIQUE unique_name (name(10)) 
);

• .
  （2）在表创建后创建：

CREATE UNIQUE INDEX unique_name ON user(name(10))；

• .
  （3）通过修改表结构创建，如下sql：

ALTER user ADD UNIQUE unique_name ON (name(10))

注意：创建的name字段长度是16字符，而创建的索引的长度是10字符，因为名字长度不会超过10个字符，所以减少索引长度，能够减少索引所占的空间的大小。

• 普通索引的唯一作用就是加快数据的查询，一般对查询语句WHERE和ORDER BY后面的字段创建普通索引。

• 创建普通索引的方式也有三种，和创建唯一索引基本一样，只是把UNIQUE换成INDEX：
  （1）创建表的时候创建

CREATE TABLE user( 
 id INT PRIMARY KEY NOT NULL, 
 name VARCHAR(16) NOT NULL, 
 INDEX index_name (name(10)) 
);

• .
  （2）创建表后创建

CREATE INDEX INDEX index_name ON user(name(10))；

• .
  （3）修改表结构创建

ALTER user ADD INDEX index_name ON (name(10))

删除索引，可以通过执行DROP INDEX index_name ON user进行删除 ;

• 组合索引即用多个字段创建一个索引，组合索引能够避免「回表查询」，相对于多字段的单列索引，组合索引的查询效率更高。

• 创建组合索引(联合索引)的方式和创建普通索引的方式一样，只不过字段的数目多了，如下sql创建，只列举修改表结构的方式：

ALTER TABLE employee ADD INDEX name_age_sex (name(10),age,sex);

回表查询

• 回表查询简单来说「通过二级索引查询数据，得不到完整的数据行，需要再次查询主键索引来获得数据行」。

• InnoDB存储引擎中，索引分为 「聚簇索引」和「二级索引」，主键索引就是聚簇索引，其它索引为二级索引。

• 聚簇索引中的叶子节点保存着完整的数据行，而二级索引的叶子节点并不是保存完整的数据行。

• 上面提到InnoDB表是一定要有主键索引的，虽然索引占据空间，但是索引符合二分查找的算法，查找数据非常的快。

• 假设一张表里面有主键索引id，和普通的索引name，那么在InnoDB中就会存在两棵B+Tree，一棵是主键索引树：（在主键索引树中的叶子节点存储的是完整的数据行）
  
  另外一棵是name字段的二级索引树：
  
  倘若执行：select name, age, sex from 表名 where id =‘as’;会先执行二级索引的查询，当查询name='as’时，得到主键为50，再根据主键查询主键索引树，得到完整的数据行，执行流程如下：
  

这个就是回表查询，回表查询会查询两次，这样会降低查询的效率，为了避免回表查询，只查询一次就能得到完整的数据呢？

索引覆盖

• 索引覆盖就是「索引的叶子节点已经包含了查询的数据，没必要再回表进行查询。」

• 常见的方式就是「建立组合索引（联合索引）「进行」索引覆盖」

• 假如还是执行：select name, age, sex from employee where id =‘as’;因为普通索引只有name字段才建立了索引，这必然会导致回表查询。

• 为了提高查询效率，就(name)「单列索引升级为联合索引」(name, age, sex)就不同了。

• 因为建立的联合索引，在二级节点的叶子阶段就会同时存在name, age, sex三个的值，一次性就会获得所需要的数据，这样就避免了回表，但是所有的方案都不是完美的。

• 若是这个联合索引哪一天某一个数据行的name值改变了或者age改变了，我就需要同时维护主键索引和联合索引两棵树，这样的维护成本就高了，性能开销也大了。

• 相比之前数据的改变，我只需要维护主键索引即可，联合索引的创建就导致了需要同时维护两棵树，这样就会影响插入、更新数据的操作，所以并没有哪种方案是完美的。

最左前缀原则

• 单列索引是按照索引列有序性的进行组织B+Tree结构的，联合索引其实也是按照创建索引的时候，最左边的进行最开始的排序，也就是「最左前缀原则」，如一个表中有如下数据：

• 上图所示，对于联合索引中name字段是放在最前面的，所以name是完全有序的，但是age字段就不是有序的，只有当name相同，例如：name='bc’此时age字段的索引排序才是完全有序的。

• 所以你会发现，在联合索引中你只有使用以下的规则的方式查询才会使用到索引：
  name,age,sex
  name,age
  name

• 因为Mysql的底层有查询优化器，会判断sql执行的时候若是使用全表扫描的效率比使用索引的效率更高，就会使用全表扫描。

• 假如查询的时候使用age>=23,sex='男';两个字段作为查询条件，但是没有使用name字段，因为在name不知情的条件下，对于age是无序的。

• 对于age>=23条件可能在很多的name不同中都有符合条件的出现，所以就没有办法使用索引，这也是索引实现的原因，一定要遵循「查找有序，充分的利用索引的有序性」。

• 假如你是分别在name，age，sex三个字段中分别建立三个单列索引，就相当于建立三颗索引树，那么它的查询效率，比我们使用一棵索引树查询效率就可想而知了。

• 有一种情况即使使用到了最左边的name字段也不会使用索引，例如：WHERE name like '%d%'；这种like条件的模糊查询会使索引失效。

• 我们可以这样理解，「查询字符串也是遵循最左前缀原则的」，字符串的查询是对字符串里面的字符一个一个的匹配，「若是字符串最左边为%表示一个不确定的字符串，那么是没办法利用到索引的有序性」。

• 但是若是修改为 ：WHERE name like 'd%'；就可以使用索引，因为最左边的字符串是确定的，这种称为「匹配列前缀」。

• 实际业务场景中联合索引的创建，「我们应该把识别度比较高的字段放在前面，提高索引的命中率，充分的利用索引」。

索引下推

• Mysql5.6版本提出了索引下推的原则，「用于查询优化，主要是用于like关键字的查询的优化」，什么是索引下推呢？
• 下面通过演示来说明以下他的概念，还是利用原来的employee测试表，假如我要执行下面的sql进行查询：SELECT * from user where name like '张%' and age=40；
  （1）假如没有索引下推，执行的过程如下图所示：
  
  查询会直接忽略age字段，将name查询的张开头的id=5、id=7的结果返回给Mysql服务器，再执行两次的回表查询。
  （2）若是上面的查询操作使用了索引下推，执行的过程如下：
  
  Mysql会将查询条件age=40的查询条件传递给存储引擎，再次过滤掉age=50的数据行，这样回表的次数就变为了一次，提高了查询效率。

总结起来索引下推就是在执行sql查询的时候，会将一部分的索引列的判断条件传递给存储引擎，由存储引擎通过判断是否符合条件，只有符合条件的数据才会返回给Mysql服务器。

• 全文索引也称为全文检索，可以通过以下sql建立全文索引：ALTER TABLE employee ADD FULLTEXT fulltext_name(name);或者CREATE INDEX的方式创建。
• 全文索引主要是针对CHAR、VARCHAR或TEXT这种文本类的字段有效，有人说不也可以使用like关键字来查询文本吗。
• 普通索引（单列索引）的查询只能加快字段内容中最前面的字符串的检索，若是对于多个单词组成文本的查询普通索引就无能为力了。

索引一经创建就没有办法修改，若是想要修改索引，必须重建，可以使用DROP INDEX fulltext_name ON employee来删除;

• 聚簇索引和非聚簇索引是相对于存储引擎的概念，范围比较大，包含上面所提到的索引类型。

• 「聚簇索引就是叶子节点中存储的就是完整的行数据，索引和数据存储在一起；而非聚簇索引的索引文件和数据文件是分开的，所以查询数据会多一次查询」。

• 因此聚簇索引的查询速度会快于非聚簇索引的查询速度，再Mysql的村相互引擎中，「InnoDB支持聚簇索引，MyISAM不支持聚簇索引，MyISAM支持非聚簇索引」。

索引原则和优化

• 要正确的使用索引，就要正确的创建索引，用索引正确的查询，不要使索引失效，因此索引的涉及和优化的原则应该遵循下面的几个原则：
  （1）索引列不要在表达式中出现，这样会导致索引失效。如：「SELECT … WHERE id+1=5」;
  （2）索引列不要作为函数的参数使用。
  （3）索引列尽量不要使用like关键字。如：「SELECT … WHERE name like ‘%d%’」;
  （4）数字型的索引列不要当作字符串类型进行条件查询。如：「SELECT … WHERE id = ‘35’」;（5）尽量不要在条件NOT IN、<>、!= 中使用索引。
  （6）在索引列的字段中不要出现NULL值，NULL值会使索引失效，可以用特殊的字符比如空字符串’ '或者0来代替NULL值。
  （7）联合索引的查询应该遵循最左前缀原则。
  （8）一般对于区别性比较大的字段建立索引，在联合索引中区别性比较大（识别度比较高）放在最前面，提高索引的命中率。
  （9）索引的大小要适度，不易过大，避免索引的冗余。

总结

• 索引是我们工作经常会使用到的数据查询方式，正确的使用索引可以大大提高查询的效率。
  （1）一方面索引减少了索引服务器需要扫描的数据行的数量，将原来的全表扫描，使用特定的数据结构，能够快速的定位数据行。
  （2）另一方面使用有序的索引，避免了排序，将原来的随机的IO操作，变成了顺序的IO操作，执行有序。
• 但是索引也不是十全十美的，也有自己的缺点，不正确的使用索引，将会导致索引大量的占据空间，索引并非是越多越好，索引文件会越发的膨胀，这样严重的影响查询的性能。
• 对于插入、更新 、删除数据，除了维护数据以外，还要维护索引文件，这样也会影响这些操作的性能，但是对于查询的频率远高于更新和插入数据的业务场景，索引是再适合不过了。