MySQL聚集索引和⾮聚集索引
介绍⼀
MySQL的Innodb存储引擎的索引分为聚集索引和⾮聚集索引两⼤类，理解聚集索引和⾮聚集索引可通过对⽐汉语字典的索引。汉语字典提供了两类检索汉字的⽅式，第⼀类是拼⾳检索（前提是知道该汉字读⾳），⽐如拼⾳为cheng的汉字排在拼⾳chang的汉字后⾯，根据拼⾳到对应汉字的页码（因为按拼⾳排序，⼆分查很快就能定位），这就是我们通常所说的字典序；第⼆类是部⾸笔画检索，根据笔画到对应汉字，查到汉字对应的页码。拼⾳检索就是聚集索引，因为存储的记录（数据库中是⾏数据、字典中是汉字的详情记录）是按照该索引排序的；笔画索引，虽然笔画相同的字在笔画索引中相邻，但是实际存储页码却不相邻。
正⽂内容按照⼀个特定维度排序存储，这个特定的维度就是聚集索引；
Innodb存储引擎中⾏记录就是按照聚集索引维度顺序存储的，Innodb的表也称为索引表；因为⾏记录只能按照⼀个维度进⾏排序，所以⼀张表只能有⼀个聚集索引。
⾮聚集索引索引项顺序存储，但索引项对应的内容却是随机存储的；
举个例⼦说明下：
create table student (
`id` INT  UNSIGNED AUTO_INCREMENT,
`name`  VARCHAR(255),
PRIMARY KEY(`id`),
KEY(`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
该表中主键id是该表的聚集索引、name为⾮聚集索引；表中的每⾏数据都是按照聚集索引id排序存储的；⽐如要查name='Arla'和
name='Arle'的两个同学，他们在name索引表中位置可能是相邻的，但是实际存储位置可能差的很远。name索引表节点按照name排序，检索的是每⼀⾏数据的主键。聚集索引表按照主键id排序，检索的是每⼀⾏数据的真实内容。
也就是说查询name='Arle'的记录时，⾸相通过name索引表查到Arle的主键id（可能有多个主键id，因为有重名的同学），再根据主键id的聚集索引到相应的⾏记录；查看mysql索引
Mysql聚集索引和⾮聚集索引最简单的表述：
聚集索引⼀般是表中的主键索引，如果表中没有显⽰指定主键，则会选择表中的第⼀个不允许为NULL的唯⼀索引，如果还是没有的话，就采⽤Innodb存储引擎为每⾏数据内置的6字节ROWID作为聚集索引。
每张表只有⼀个聚集索引，因为聚集索引在精确查和范围查⽅⾯良好的性能表现（相⽐于普通索引和全表扫描），聚集索引就显得弥⾜珍贵，聚集索引选择还是要慎重的（⼀般不会让没有语义的⾃增id充当聚集索引）。
从宏观上分析下聚集索引和普通索引的性能差异，还是针对上述student表：
（1）select * from student where id >5000 and id <20000;
（2）select * from student where name > 'Alie' and name < 'John';
第⼀条SQL语句根据id进⾏范围查询，因为(5000, 20000)范围内的记录在磁盘上按顺序存储，通过顺序读取磁盘很快就能读到这批数据。
第⼆条SQL语句查询（'Alie', 'John'）范围内的记录，主键id分布可能是离散的1，100，20001，
；增加了随机读取数据页⼏率；所以普通索引的范围查询效率被聚集索引甩开⼏条街都不⽌；⾮聚集索引的精确查询效率还是可以的，⽐聚集索引查询只增加了⼀次IO开销。
介绍⼆
我们先了解下InnoDB引擎表的⼀些关键特征：InnoDB引擎表是基于B+树的索引组织表(IOT)。
每个表都需要有⼀个聚集索引(clustered index)；
所有的⾏记录都存储在B+tree的叶⼦节点(leaf pages of the tree)；
基于聚集索引的增、删、改、查的效率相对是最⾼的；
如果我们定义了主键(PRIMARY KEY)，那么InnoDB会选择其作为聚集索引；
如果没有显式定义主键，则InnoDB会选择第⼀个不包含有NULL值的唯⼀索引作为主键索引；
如果也没有这样的唯⼀索引，则InnoDB会选择内置6字节长的ROWID作为隐含的聚集索引(ROWID随着⾏记录的写⼊⽽主键递增，这个ROWID不像ORACLE的ROWID那样可引⽤，是隐含的)。
综上总结，如果InnoDB表的数据写⼊顺序能和B+树索引的叶⼦节点顺序⼀致的话，这时候存取效率是
最⾼的，也就是下⾯这⼏种情况的存取效率最⾼：
使⽤⾃增列(INT/BIGINT类型)做主键，这时候写⼊顺序是⾃增的，和B+数叶⼦节点分裂顺序⼀致；
如果该表不指定⾃增列做主键，同时也没有可以被选为主键的唯⼀索引(上⾯的条件)，这时候InnoDB会选择内置的ROWID作为主键，写⼊顺序和ROWID增长顺序⼀致；
如果⼀个InnoDB表⼜没有显⽰主键，⼜有可以被选择为主键的唯⼀索引，但该唯⼀索引可能不是递增关系时(例如字符串、
UUID、多字段联合唯⼀索引的情况)，该表的存取效率就会⽐较差。
聚集索索和⾮聚集索引
每个InnoDB表都有⼀个称为聚集索引的特殊索引，其中存储了⾏的数据。通常，聚集索引与主键同义。
1. mysql的innodb表，就是索引组织表，表中的所有数据⾏都放在索引上，这就约定了数据是严格按照顺序存放的，所以不管插⼊的先后
顺序，它在那个物理上的那个位置与插⼊的先后顺序⽆关。
2. 聚集索引：叶⼦节点存的是整⾏数据，直接通过这个聚集索引的键值到某⾏
3. 聚集索引：数据的物理存放顺序与索引顺序是⼀致的，即：只要索引是相邻的，那么对应的数据⼀定也是相邻地存放在磁盘上的。
4. 聚集索引：数据⾏和相邻的键值紧凑地存储在⼀起，因为⽆法同时把数据⾏存放在两个不同的地⽅，所以⼀个表只能有⼀个聚集索
引。
5. ⾮聚集索引，叶⼦节点存的是字段的值，通过这个⾮聚集索引的键值到对应的聚集索引字段的值，再通过聚集索引键值到表的某
⾏，类似oracle通过键值到rowid，再通过rowid到⾏
6. mysql的innodb表，其聚集索引相当于整张表，⽽整张表也是聚集索引。默认通过主键聚集数据，如果没有定义主键，则选择第⼀个⾮
空的唯⼀索引，如果没有⾮空唯⼀索引，则选择rowid来作为聚集索引
7. mysql的innodb表，因为整张表也是聚集索引，select出来的结果是顺序排序的，⽐如主键字段的数据插⼊顺序可以是5、3、4、2、1,
查询时不带order by得出的结果也是按1、2、3、4、5排序
8. 通俗理解
聚集索引：类似新华字典正⽂内容本⾝就是⼀种按照⼀定规则排列的⽬录
⾮聚集索引：这种⽬录纯粹是⽬录，正⽂纯粹是正⽂的排序⽅式
每个表只能有⼀个聚集索引，因为⽬录只能按照⼀种⽅法进⾏排序。
================
基于主键索引和普通索引（⼆级索引）的查询有什么区别？
主键索引的叶⼦节点存的是整⾏数据。在 InnoDB ⾥，主键索引也被称为聚集索引（clustered index）。
⾮主键索引的叶⼦节点内容是主键的值。在 InnoDB ⾥，⾮主键索引也被称为⼆级索引（secondary ind
ex）。
如果语句是 select * from T where ID=500，即主键查询⽅式，则只需要搜索 ID 这棵 B+树；
如果语句是 select * from T where k=5，即普通索引查询⽅式，则需要先搜索 k 索引树，得到 ID的值为 500，再到 ID 索引树搜索⼀次。这个过程称为回表。B+树为了维护索引有序性，在插⼊新值的时候需要做必要的维护。以上⾯为例，
如果插⼊新的⾏ ID 值为 700，则只只需要在 R5 的记录后⾯插⼊⼀个新记录。
如果新插⼊的 ID值为 400，就相对⿇烦了，需要逻辑上挪动后⾯的数据，空出位置。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。