MySQL聚集索引

聚集索引或聚簇索引（Clustered Index）是一种对磁盘上实际数据重新组织并按指定的一个或多个列的值排序。数据行的物理顺序与列值（一般是主键那列）的逻辑顺序相同，一个表中只能有一个聚集索引（因为只能以一种物理顺序存放）

InnoDB就是用的聚集索引，它的表中的数据都会有一个主键，即使你不创建主键，InnoDB会选取一个Unique键作为主键，如果表中连Unique键都没有定义的话，InnoDB会为表添加一个名为row_id的隐藏列作为主键

也就是说我们通过InnoDB把数据存放到B+树中，而B+树中的键值就是主键，那么在B+树中的叶子节点存储的就是表中的所有数据（即该主键对应的整行数据），数据文件和索引文件是同一个文件，找到了索引便找到了数据，所以我们称之为聚集索引。

 

聚集索引更新代价高。插入新行或更新主键时会强制将每个被更新的行移动到新的位置（因为要按主键排序），而移动行可能还会面临页分裂问题（即页已满），存储引擎会将该页分裂成两个页面来容纳，页分裂会占用更多磁盘空间。即索引重排，造成资源浪费。

聚集索引适合范围查询。聚集索引查询速度很快，特别适合范围检查（between、<、<=、>、>=）或group by、order by的查询。因为聚集索引找到包含第一个值的行后，后续索引值的行在物理上毗连在一起而不必进一步搜索，避免大范围扫描，大大提高查询速度。

 

 

 

比如查询id>=19并且id<30的数据：通常根节点常驻在内存中（即页1已在内存），首先在页1找到了键值19及其对应指针P2，通过P2读页3（此时页3不在内存中，需要从磁盘中加载），然后在页3查找键值19的指针P1，又定位到页8（同样的从磁盘加载到内存），因为数据是按链表进行顺序链接的，可以通过二分找到键值19对应数据。

找到键值19后，因为是范围查找，这时可以在叶子节点里进行链表的查询，依次遍历并匹配满足的条件，一直找到键值21，到最后一个数据仍不能满足我们的要求，此时会拿着页8的指针P去读取页9的数据，页9不在内存中同样需要磁盘加载读进内存，然后依此类推，直到匹配到键值34时不满足条件则终止，这就是通过聚集索引查找数据的一种方法。

 

非聚集索引
非聚集索引或非聚簇索引(Secondary Index)就是以主键以外的列作为键值构建的B+树索引，索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。在InnoDB中处了主键索引外其他索引都可以称为辅助索引或二级索引。

MySQL中的MyISAM使用的就是非聚集索引。表数据存储顺序与索引数据无关，叶节点包含索引字段值及指向数据页数据行的逻辑指针（其行数量与数据表数据量相同），所以想要查找数据还需要根据主键再去聚集索引中查找，根据聚集索引查找数据的过程就称为回表。

比如定义一张数据表test，他是由test.frm、tsst.myd和test.myi组成的：

.frm：记录了表定义语句
.myd：记录了真实表数据
.myi：记录了索引数据
再检索数据时，先到索引树test.myi中进行查找，取到数据所在test.myd的行位置，拿到数据。所以MyISAM引擎的索引文件和数据文件是独立分开的，找到索引不等于找到数据，即非聚集索引。

一个表可以有不止一个非聚集索引，实际上每个表最多可以建立249个非聚集索引，但是每次给字段建一个新索引，字段中的数据就会被复制出来一份用于生成索引，因此给表添加索引会增加表的体积，占据大量磁盘空间和内存。所以若磁盘空间和内存有限，应限制非聚集索引数量。

此外每当你改变了一个建立非聚集索引的表中数据时，必须同时更新索引，所以非聚集索引会降低插入和更新速度。

 

比如查找数据36，是用两个数字表示，前面那个数字36代表的是索引的键值，后面那个64代表的是数据的主键。所以说我们找到36后，并没有拿到数据，还要根据它对应的主键去到聚集索引表中去查找数据。

更多精彩内容，关注我们▼▼