MySQL系列---索引（中）

常见索引种类(应用层面)：

1.主键索引：主键索引是唯一的，通常以表的ID设置为主键索引,一个表只能有一个主键索引，这是他跟唯一索引的区别。

2.唯一索引：唯一索引主要用于业务上的唯一约束，他跟主键索引的区别是，一个表可以有多个唯一索引

3.单列索引：以某一个字段为索引

4.联合索引：两个或两个以上字段联合组成一个索引。使用时需要注意满足最左匹配原则！

例如：比如在(a,b,c)三个字段上建立联合索引，那么它能够加快a|(a,b)|(a,b,c)三组查询的速度，而不能加快b|(b,a)这种查询顺序。

另外，建联合索引的时候，区分度最高的字段在最左边。

聚簇索引与非聚簇索引：

什么是聚簇索引：
聚簇索引指的是他的 索引和行数据 在一起存储。也就是在一颗B+树的叶子结点上存储的不仅是他的索引值，还有对应的某一行的数据（聚簇索引不是一种索引，而是一种数据存储组织方式 ）。

crreate table test(
  col1 int not null,
  col2 int not null,
  PRIMARY KEY(col1),
  KEY(col2)
);

如上所示，表test 由两个索引，分别是主键 col1 和 普通索引 col2。那么这俩索引跟聚簇非聚簇有啥关系呢？

会生成一个聚簇索引和一个非聚簇索引(二级索引)，也就是说会组织两个索引树。主键索引会生成聚簇索引的树 以及以col2为索引的非聚簇索引的树。

InnoDb 将通过主键来实现聚簇索引 ，如果没有主键则会选选一个唯一非空索引来实现。如果没有唯一非空索引则会隐式生成一个主键。

下面看下聚簇索引和非聚簇索引在索引树上数据是怎么分布的，图片摘自《高性能Nysql》

下图是聚簇索引的数据组织方式。 col1为主键索引的聚簇索引树

索引列是主键 col1

 

可以看出叶子结点除了存储索引值 列col1 (3~99~4700)值 之外还存储了其他列的值，如列col2 (92~8~13),如果还有别的列的话也会存储，或者换句话说聚簇索引树 在叶子节点上存储某个索引值对应的一行数据。

 

下图是非聚簇索引(二级索引)的数据组织方式。

索引列是 col2

 

 

 

与聚簇索引不同的是非聚簇索引在索引树叶子节点上除了索引值之外只存了主键值。而聚簇索引则存了一行数据。

假如有一条sql 语句 select * from test where col2=93;
上面这条语句会经历两次从索引树查找过程

1.第一步从非聚簇索引的索引树上找到包含col2=93的叶子节点，并定位到行的主键 3
2.第二步 根据主键 3 在从聚簇索引定位包含 主键=3的叶子节点并返回全部行数据。

以上说的都是基于InnoDb存储引擎的,MyISAM是不支持聚簇索引的，因为他的数据文件和索引文件是相互独立存储的 MyISAM存储引擎的索引树的叶子节点不会寸主键值，而存一个指向对应行的地址或者说是指针，然后再从表数据文件里去找，如下面图所示。

 

 

结论：

• 聚簇索引：通常由主键或者非空唯一索引实现的，叶子节点存储了一整行数据；

• 非聚簇索引：又称二级索引，就是我们常用的普通索引，叶子节点存了索引值和主键值，在根据主键从聚簇索引查；

覆盖索引

覆盖索引就是指索引包含了所有需要查询的字段。

create table User(
`name` varchar(50) not null,
`uid` int(4) not null,
`gender` int(2) not null,
 key(`uid`,`name`)
);

假如表 User有三个字段 User (name,uid,gender),且有个联合索引 key(name,uid)那么 执行如下面这条sql查询时就用到了 覆盖索引。

select name,uid from User where name in ('a','b') and uid >= 98 and uid <=100 ;

上面这条sql语句使用了联合索引 key(name,uid),并且只需查找 name,uid两个字段，所以使用了覆盖索引。覆盖索引有什么好处呢？先看一下下面这个图

 

上面这个图就是 联合索引key(name,uid) 所对应的索引树，从图中可以看出，如果我们只需查询(name,uid)两个字段的话，从索引树就能得到我们需要查的数据。不需要找到索引值之后再从表数据文件定位对应的行数据了。

覆盖索引好处：
1.避免了对主键索引(聚簇)的二次查询
2.由于不需要回表查询(从表数据文件)所以大大提升了Mysql缓存的负载