目录

一、索引分类

1.1 聚簇索引结构

1.2 非聚簇索引(二级索引)

1.3 主键索引

1.4 唯一索引

1.5 普通索引

1.6 前缀索引

1.7 联合索引

1.8 索引下推

1.9 索引区分度

二、优化索引的方法

2.1 索引的特点

2.2 适合创建索引的情况

2.3 不适合创建索引的情况

2.4 优化索引的方法

---

慢查询的原因之一就是索引使用不当，本文对索引的本质和结构来剖析如何使用索引

一、索引分类

索引是数据的目录，是一种数据结构，为了快速检索与查找数据

索引分类：

维度	类别
数据结构	B+tree索引、Hash索引、Full-text索引
物理存储	聚簇索引（主键索引）、二级索引（辅助索引）
字段特性	主键索引、唯一索引、普通索引、前缀索引
字段个数	单列索引、联合索引

MySQL 存储引擎默认是InnoDB，在InnoDB中必须有聚簇索引，这是由于InnoDB存储引擎的索引结构B+tree有关系。

在创建表时，会创建一个聚簇索引：

• 如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
• 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）
• 如果上面两个都没有，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）

1.1 聚簇索引结构

如图：B+树是一个多叉树，叶子节点存放数据，非叶子节点存放索引，每个节点都是按照主键顺序存放，叶子节点中的数据包括主键索引、数据、上一个节点的指针和下一个结点的指针

根据上面的图分析 select * from product where id=5

第一步：将id=5与根节点索引(1,10,20)比较，5在(1,10)之间，索引搜索找到第二层节点

第二步：将id=5与第二层节点(1,4,7)比较，5在(4,7)之间，找到第三层索引

第三步：在(4,5,6)之间查找，找到索引值=5的行数据

InnoDB的索引和数据都是存储在硬盘中的，每次读取数据都要进行一次硬盘IO操作，上述步骤一共进行了3个IO操作。

1.2 非聚簇索引(二级索引)

非聚簇索引，例如product值是二级索引，则用product创建的顺序索引结构如下：

其中非叶子节点是product索引值，而叶子节点放的是product索引值与主键数据

分析：select * from product where product = '0002'; 

第一步：在非聚簇索引中知道product=0002的主键索引值，id=2

第二步：回表查询，在聚簇索引中根据id=2查询行数据

回表：当查询使用非聚集索引（secondary index）时，需要先通过索引找到对应的数据行主键，然后再根据主键去聚集索引（primary index）中获取完整数据行的过程。

非回表查询：

select id from product where product = '0002'; 

查询的id就在非聚簇索引中，不需要回表去主键索引中查询，这种在二级索引中就可以查到结果的过程叫覆盖索引，查一个B+树就可以找到。

1.3 主键索引

建立在主键字段上，创建表的时候一起创建，一个表只有一个主键索引，主键索引列不能为空值

CREATE TABLE table_name{...PRIMARY KEY(id) USING BTREE
}

1.4 唯一索引

与主键索引不同的地方，一个表可以有多个唯一索引，唯一索引允许索引列有空值，但是唯一索引列的值必须唯一。

//创建表时创建索引
CREATE TABLE table_name(...UNIQUE KEY(column1,column2)
);//建表后增加索引
CREATE UNIQUE INDEX index_name 
ON table_name(column1,column2)或者
ALTER TABLE table_name 
ADD UNIQUE (column1, column2);

1.5 普通索引

建立在普通的字段上，不要求字段值非空，也不要求字段值唯一

//创建表时创建索引
CREATE TABLE table_name(...INDEX(column1,column2)
);//建表后增加索引
CREATE INDEX index_name 
ON table_name(column1,column2)或者
ALTER TABLE table_name 
ADD INDEX (column1, column2);

1.6 前缀索引

对字符类型字段的前几个字符创建索引，目的是为了节省索引所占的存储空间，提高查询效率

1.7 联合索引

由多个字段组成的索引为联合索引，例如productId和productName两个字段创建的索引，其索引结构如下：

联合索引的非叶子节点是用productId和name做值，索引先按照productId排序，productId相同时按照name排序，所以联合索引需要满足最左匹配原则，如果不满足，则该联合索引就会失效，无法利用索引提高查询效率。

如果语句where name=ipad8，则无法使用联合索引(productId,name)，因为name字段是局部有序，全局无序的，利用索引的前提是索引是全局有序的。

举例说明，哪些情况联合索引生效，联合索引(a,b)

语句	索引	说明
select * from table where a>1 and b=2	a字段用到了联合索引，b字段没有用到	a字段是有序的，可以定位到符合a>1的纪录，但是b是无序的
select * from table where a>=1 and b=2	a,b字段都用到了联合索引	a>=1索引可以很快定位到a=1的纪录，b=2可以将索引定位到a=1,b=2的纪录，然后往后扫描
select * from table where a between 2 and 8 and b=2	a,b字段都用到了联合索引	between and相当于a>=2 and a<=8,b=2可以进一步缩小索引扫描的行数
select * from table where a like 'j%' and b=2	a,b字段都用到了联合索引	当索引a字段值为‘j%’时，b=2可以减少扫描的二级索引纪录行数

1.8 索引下推

select * from table where a>1 and b=2，对于联合索引(a,b)，只有a字段用到了索引，那b字段是回表查询后进行判断呢还是在二级索引中判断呢？

mysql5.6后，引入了索引下推，可以在二级索引中之间判断其它字段是否满足条件，减少回表的次数，例如在二级索引中查询出a>2的纪录之后，会在二级索引中包含的字段b进行判断是否满足b=2

1.9 索引区分度

建立联合索引时，需要将区分度大的字段排在前面，区分度小的排在后面，区分度越大过滤的数据越多，区分度=一个字段值去重后的个数/该字段总数

二、优化索引的方法

2.1 索引的特点

优点：提高查询效率

缺点：占用物理空间，数量越大占用越多；创建和维护索引占用时间，索引越大占用时间越多；增加表增删改的效率，因为每次更新都要动态维护索引数据。

2.2 适合创建索引的情况

1. 字段有唯一性限制；
2. where中的字段，如果查询条件是多个字段，可以创建联合索引；
3. group by与order by中的字段，因为索引是排好序的，不需要重新排序；

2.3 不适合创建索引的情况

1. 字段区分度低的，例如性别，优化器会在区分度很低时，放弃索引，采用全表扫描；
2. where 、group by、order by中用不到的字段，索引的作用是快速定位，否则只会占用空间；
3. 经常更新的字段不适合创建索引，会增加维护索引的成本；
4. 数据量很少时不适合创建索引，数据量很少时全表扫描会更快；

2.4 优化索引的方法

1. 前缀索引优化：大字符做索引时，用前缀索引，减少索引字段大小，提高索引页中存储的索引数量
2. 覆盖索引优化：对于查询的字段可以创建联合索引，直接在二级索引中查询出所有的数据，不需要回表查询主键索引，减少IO操作
3. 主键索引自增：自增索引在增加数据时，直接开辟新的页存储数据，不需要移动其他的数据，否则会导致页分裂，造成内存碎片，索引结构不紧凑，影响查询效率。
4. 防止索引失效：

• 不符合最左匹配原则：例如like '%xx'或者like '%xx%'
• 对索引列进行计算、函数、类型转换
• OR条件，只有一个条件有索引时，也会索引失效

参考：索引常见面试题 | 小林coding