索引分类总结

B+Tree索引结构根据存储形式分类

聚集索引（Clustered Index）

二级索引（Secondary Index）

根据索引特征分类

主键索引——一定是聚集索引

唯一索引——可以是聚集索引，也可以是二级索引

常规索引——二级索引

全文索引——Full-Tree索引结构

空间索引——R-Tree索引结构

根据索引的字段数量分类

单列索引——可聚集索引，也可二级索引

联合索引——二级索引

对索引进行优化的两种索引

前缀索引——二级索引

覆盖索引——索引查询的一种优化手段

索引语法总结

创建索引CREATE INDEX

查看索引SHOW INDEX

删除索引DROP INDEX

索引分类总结

按照B+Tree存储形式方式分类

聚集索引和二级索引（两种索引的存储格式）

按照索引的特征进行分类

主键索引、唯一索引、常规索引、全文索引、空间索引

其中主键索引、唯一索引、常规索引都为B+Tree索引结构

全文索引为Full-Text索引结构

空间索引为R-Tree索引结构

按照单个索引的字段个数分类

单列索引和联合索引

对于索引的优化又有两种索引

前缀索引和覆盖索引

在讲解索引的具体类型之前我们先了解一下索引的相关知识

什么是回表查询

根据二级索引找到对应的主键值，然后通过主键索引找到主键值所在行的数据

B+Tree索引结构根据存储形式分类

聚集索引（Clustered Index）

就是按照每张表的主键构造一棵B+树，其中叶子节点存放的数据为此索引对应的这行的记录数据；所有叶子节点的数据加起来就是整张表的记录数据

每张表必须有，并且只有一个聚集索引

聚集索引的选举规则——聚集索引一般就是主键索引

如果存在主键，主键索引就是聚集索引

如果不存在主键，将使用第一个唯一索引作为聚集索引

如果没有主键和唯一索引，则InnDB会自动生成一个DB_ROW_ID作为隐藏的聚集索引

二级索引（Secondary Index）

二级索引又可称为非主键索引，像唯一索引（如果唯一索引不是聚集索引的话）、联合索引等都可统称为二级索引

也是按照每张表的主键构造一棵B+树，不过叶子节点存放的数据为此索引对应的主键索引的值和该列的值

每张表可以有多个二级索引

根据索引特征分类

主键索引——一定是聚集索引

主键索引是针对于表中主键创建的索引，主键索引每张表只可以有一个

每张表最多只有一个主键，默认在创建主键时会自动为主键创建主键索引

主键索引必须满足的三个条件：

主键值必须唯一
不能包含Null值
一定保证该值是自增属性

创建主键索引--我们只需要为字段设置主键约束，就可以为该字段建立主键索引

#在创建表时设置主键

CREATE TABLE 表名(

字段1 字段1的类型 PRIMARY KEY AUTO_INCREMENT,

……

);

#表创建成功之后，设置表中的字段为主键

ALTER TABLE 表名 MODIFY 字段名字段类型 AUTO_INCREMENT PRIMARY KEY;

唯一索引——可以是聚集索引，也可以是二级索引

通过使用Unioue参数可以设置索引该索引为唯一性索引

唯一性就是不允许有重复的值，但是允许有Null值并且允许Null值重复

在一张表中唯一索引可以有多个

创建唯一索引

#在创建表时为字段设置唯一索引

CREATE TABLE 表名(

字段1 字段1的类型,

……,

UNIQUE KEY 索引名 INDEX (字段1)

);

#表创建成功后，为表中的字段设置唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名(字段1);

常规索引——二级索引

也成为非唯一索引，是MySQL中最基本的索引类型，其允许索引的列中存在重复的值

该索引的目的只是为了提高查询效率，快速定位特定数据，让数据库不需要全表扫描

可以创建在任何数据结构中；其值是否唯一和非空是由字段本身的约束来决定

创建常规索引

#创建表时为字段建立常规索引

CREATE TABLE 表名(

字段1 字段1 的类型,

……

KEY 索引名(字段1)

);

#表创建成功后，为表的字段建立常规索引（常用语法）

CREATE INDEX 索引名 ON 表名(字段1);

#表创建成功后，为表的字段建立常规索引（此语法不常用）

ALTER TABLE 表名 ADD INDEX 索引名(字段1);

全文索引——Full-Tree索引结构

通过Full-Tree索引结构建立的索引类型；建立倒排索引；查找文本中的关键词，而不是比较索引中的值

是目前搜索引擎使用的一种关键技术，能够利用分词技术等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能的筛选出我们想要的搜索结果

只可以创建在CHAR、VATCHAR、TEXT类型及其系列类型的字段上

一个表中可以建立多个全文索引个

创建全文索引FULLTEXT

#创建表时为字段建立全文索引（分词器为ngram）

CREATE TABLE 表名(

字段1 字段1的类型,

……

FULLTEXT INDEX (字段1) WITH PARSER 分词器类型(一般使用ngram)

);

#表创建成功后，为表的字段建立全文索引（常用语法）

CREATE FULLTEXT INDEX 索引名 ON 表名(字段1) WITH PARSER 分词器类型;

#表创建成功后，为表的字段建立全文索引（此语法不常用）

ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名(字段1) WITH PARSER 分词器类型;

全文索引查询

全文索引查询的关键字

MATCH：要匹配的列（建立了全文索引的列）

AGAINST：要查找的内容

全文索引查询语句（为以下此表中的name字段建立全文索引）

select * from test.emp where match(name) against('玩手机'); #对玩手机分词，将含有分词后的值提取出来

select * from test.emp where match(name) against('喜欢学习'); #对喜欢学习分词，将含有分词后的值提取出来

通过以上实验：我们可以了解到对于InnoDB存储引擎来说，其分词器ngram在建立索引时会对字段中的值进行分词；在进行查询时也会对要查找的内容分词

空间索引——R-Tree索引结构

通过R-Tree索引数据结构建立的索引类型；主要用于存储地理位置数据，使用较少

#创建表成功后，为字段建立空间索引

CREATE SPATIAL INDEX索引名称 ON 表名 (字段名);

根据索引的字段数量分类

单列索引——可聚集索引，也可二级索引

只要是对单列建立的索引就称为单列索引

以上的主键索引、唯一索引、常规索引、全文索引、空间索引都是单列索引的类型

联合索引——二级索引

针对表上的多个列建立一个索引，即一个索引包含多个列，就成为联合索引

在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，避免回表查询

该索引指向创建时对应的多个字段，在查询时要遵循最左匹配原则

创建联合索引

CREATE INDEX 索引名 ON 表名(字段1,字段2,……);

创建联合索引时，要注意顺序（根据使用频繁程度从左到右排序，使用频繁的放在最左边）

例如：先按照字段1进行排序，如果字段1的值一致，再按照字段2进行排序

为什么要这样做是因为联合索引需要遵循最左匹配原则

对索引进行优化的两种索引

前缀索引——二级索引

当字段类型为字符串（varchar，text等）时，有时候需要索引很长的字符串，这会让索引变得很大，查询时浪费大量的磁盘IO，影响查询效率

此时可以只将字符串的一部分前缀建立索引，大大节约索引空间，从而提高索引效率

创建前缀索引

CREATE INDEX 索引名称 ON 表名 (字段名(提取字符串的前几位));

如何选取前缀长度

可以根据索引的选择性来决定，选择性是指不重复的索引值(基数)和数据表的记录总数的比值

选择性越高则查询效率越高，唯一索引的选择性是1，是最好的索引选择性

如何计算索引选择性

SELECT COUNT(DISTINCT 字段) / COUNT(*) FROM 表名; #计算此字段的索引选择性

SELECT COUNT(DISTINCT SUBSTRING(字段,1,结束位n)) /count(*) FROM 表名; #计算字符串的前n位的索引选择性

覆盖索引——索引查询的一种优化手段

我们建立索引其实就是为该列建立索引

覆盖索引指的是查询时使用了索引进行条件判断，并且在查询返回的列在该索引列中或者为主键索引列

举例说明

对于一个名为user的表有id、name、age、job_number字段；对id列建立主键索引；对name和age建立联合索引；对于job_number建立唯一索引

select * from user where id = 2; #此时就是覆盖索引；

虽然返回的是id=2的这行的数据，但是由于id为主键索引，也就是聚集索引，其叶子节点保存到数据就是每行的数据

select * from user where job_number = 123456; #此时就不是覆盖索引，需要进行回表查询；

我们job_number建立的是唯一索引，此时全表已经有了聚集索引，所以此唯一索引就是二级索引，其叶子节点存储的数据就是主键索引值和job_number的键值；我们要分为的数据为job_number=123456这行的所有数据，此时就需要通过主键索引的值来根据主键索引生成的B+树来查找对应的行数据

select id,job_number from user where job_number = 123456; #此时就是覆盖索引

select name,age from user where name = ‘张三’; #此时就是覆盖索引;因为name和age建立了联合索引；非叶子节点存储的就是name和age的值以及主键索引值

索引语法总结

创建索引CREATE INDEX

创建主键索引--我们只需要为字段设置主键约束，就可以为该字段建立主键索引

#在创建表时设置主键

CREATE TABLE 表名(

       字段1 字段1的类型 PRIMARY KEY AUTO_INCREMENT,

       ……

);

#表创建成功之后，设置表中的字段为主键

ALTER TABLE 表名 MODIFY 字段名字段类型 AUTO_INCREMENT PRIMARY KEY;

创建唯一索引

#在创建表时为字段设置唯一索引

CREATE TABLE 表名(

       字段1 字段1的类型,

       ……,

       UNIQUE KEY 索引名 INDEX (字段1)

);

#表创建成功后，为表中的字段设置唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名(字段1);

创建常规索引

#创建表时为字段建立常规索引

CREATE TABLE 表名(

       字段1 字段1 的类型,

       ……

KEY 索引名(字段1)

);

#表创建成功后，为表的字段建立常规索引（常用语法）

CREATE INDEX 索引名 ON 表名(字段1);

#表创建成功后，为表的字段建立常规索引（此语法不常用）

ALTER TABLE 表名 ADD INDEX 索引名(字段1);

创建全文索引FULLTEXT

#创建表时为字段建立全文索引（分词器为ngram）

CREATE TABLE 表名(

       字段1 字段1的类型,

       ……

      FULLTEXT INDEX (字段1) WITH PARSER 分词器类型(一般使用ngram)

);

#表创建成功后，为表的字段建立全文索引（常用语法）

CREATE FULLTEXT INDEX 索引名 ON 表名(字段1) WITH PARSER 分词器类型;

#表创建成功后，为表的字段建立全文索引（此语法不常用）

ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名(字段1) WITH PARSER 分词器类型;

创建空间索引

CREATE SPATIAL INDEX索引名称 ON 表名 (字段名);

创建联合索引

CREATE INDEX 索引名 ON 表名(字段1,字段2,……);