慢查询引发对mysql索引的探索

维度	类别
数据结构	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可以减少扫描的二级索引纪录行数