MySQL索引原理

索引类型

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

从索引存储结构划分： B Tree索引、Hash索引、FULLTEXT全文索引、R Tree索引
从应用层次划分：普通索引、唯一索引、主键索引、复合索引
从索引键值类型划分：主键索引、辅助索引(二级索引)
从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、非聚集索引（非聚簇索引）

普通索引

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

CREATE INDEX <索引名字> ON tablename (字段名字);
ALER TABLE tablename ADD INDEX[索引名字](字段名字);
CREATE TABLE tablename([…],INDEX[索引名字](字段名));

唯一索引

与普通索引类似，不同的是：索引字段的值必须是唯一的，但允许有空值。在创建或修改表时追加唯一约束，就会自动创建对应的唯一索引，创建唯一索引的方式如下：

CREATE UNIQUE INDEX<索引名字> ON tablename(字段名)
ALTER TABLE tableaname ADD UNIQUE INDEX[索引名字](字段名)
CREATE TABLE tablename([…],UNIQUE[索引名字](字段名))

主键索引

它是一种特殊的唯一索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有一个主键，其实我们在创建主键的时候就已经为主键创建了一个主键索引
创建主键索引的方法如下：

CREATE TABLE tablename([…],PRIMARY KEY(字段名));
ALTER TABLE tablename ADD PRIMARY KEY(字段名);

复合索引

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

CREATE INDEX<索引名字> ON tablename (字段名1，字段名2…);
ALTER TABLE tablename ADD INDEX[索引的名字](字段名1，字段名2…);
CREATE TABLE tablename([…],INDEX[索引的名字](字段名1,字段名2…));

复合索引使用注意事项：

复合索引字段是有顺序的，在查询使用时要按照索引字段的顺序使用。例如select * from user where name = XXX and age = XXX 匹配（name，age）组合索引，不匹配（age，name）
何时使用复合索引，要根据where条件建索引，主要不要过多使用索引，过多使用会对更新操作效率有很大的影响，因为更新数据的同时也会更新索引
如果表已经建立了(col1,col2)，就没有必要再单独建立(col1)；如果现在有(col1)索引，如果查询需要col1、col2条件，可以建立(col1,col2)复合索引，对于查询有一定的提高。

全文索引

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

CREATE FULLTEXT INDEX <索引的名字> ON tablename(字段名);
ALTER TABLE tanlename ADD FULLTEXT INDEX [索引的名字](字段名);
CREATE TABLE tablename([…],FULLTEXT KEY [索引的名字](字段名));

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

select * from ueer where match(name) against('aaa');

注意事项：

全文索引必须在字符串、文本字段上建立
全文索引字段值必须在最小字符和最大字符之间的才会有效。（innoDB：3-84,；myisam：4-84）可以通过show variables like '%ft%' 查看相关的参数
全文索引字段值要进行切词处理，俺syntax字符进行切割，例如b+aaa会被切分成b和aaa
全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab，ac。如果想匹配可以在布尔模式下搜索a*
select * from user where match(name) against('a*' in boolean mode);

索引原理

MySQL官网对索引的定义：是存储引擎用于快速查找记录的一种数据结构，需要额外开辟空间和数据维护工作。

索引是物理数据页存储，在数据文件中(InnoDB,ibd文件)，利用数据页(page)存储
索引可以加快检索速度，但是同时也会降低增删改操作的速度，索引维护需要代价。

索引涉及一些必要的算法知识：二分查找法、Hash和B+Tree。

数据结构

Hash结构

B+ Tree结构

聚簇索引与辅助索引

聚簇索引和辅助索引并不是同一个维度上进行划分的，聚簇索引与非聚簇索引是从数据存储维度划分的，聚簇索引的含义为索引列与数据存放在一起。而辅助索引与主键索引是从索引键值类型维度进行划分的。

聚簇索引和非聚簇索引：B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引；如果索引值和行记录分开存放就属于非聚簇索引
主键索引和辅助索引：B+Tree的叶子节点存放的是主键字段值就属于主键索引；如果存放的是非主键值就属于辅助索引(二级索引)
聚簇索引
聚簇索引是一种数据存错方式，InnoDB的聚簇索引就是按照主键顺序构建B+Tree结构的。B+Tree的叶子节点就是行记录，行记录和主键值紧凑的存储在一起。这也意味着InnoDB的主键索引就是数据表本身，它按主键顺序存放了一整张表的数据，占用的空间就是整个表数据量的大小。通常说的主键索引就是聚集索引。
InnoDB的表要求必须要有聚簇索引：
如果表定义了主键，则主键索引就是聚簇索引
如果表没有定义主键，则第一个非空unique列作为聚簇索引
否则InnoDB会从建一个隐藏的row-id作为聚簇索引

辅助索引

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

非聚簇索引

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

索引分析与优化

EXPLAIN操作符

MySQL提供了一个EXPLAIN命令，它可以对SELECT语句进行分析，并输出SELECT执行的详细信息，供开发人员有针对性的优化

select_type:表示查询的类型。常见的值如下：

回表查询

InnoDB索引有聚簇索引和辅助索引。聚簇索引的叶子结点存储行记录，InnoDB必须要有，且只有一个。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫两遍索引树。先通过辅助索引定位主键值，然后通过聚簇索引定位行记录，这就叫做回表查询，它的性能比扫一遍索引树低。通常我们可以通过EXPLAIN关键字中的Extra中的信息判断出有没有进行回表查询，如果出现Using where则表示查询需要通过索引回表查询数据。
总结：通过索引查询主键值，然后再去聚簇索引查询记录信息，这种操作叫做回表查询