回表、聚簇索引和非聚簇索引详解
回表、聚簇索引和非聚簇索引详解
回表
什么是回表
回表（英文名："Lookup" 或 "Ref"）是数据库中一种查询优化方式，通常用于处理使用非聚集索引（Non-Clustered Index）的查询。
通俗的讲就是，如果select所需获得列中有非索引列，一次索引查询不能获取所有信息，需要到表中找到相应的列的信息。这就叫回表。也就是说，需要从辅助索引（Secondary Index）中获取额外的数据列，需要通过辅助索引回到主键索引（Primary Index）中进行查找。
什么情况会触发回表
当使用非聚集索引查询时，数据库引擎首先通过该索引找到匹配的行，然后使用行指针（Row Pointer）到表中查找相应的行数据。这个过程中就需要进行一次额外的访问（即“回表”），以获取完整的行数据。
如何避免回表
回表操作的代价较高，因为需要额外的IO操作，增加了查询的开销，尤其在大型数据表中查询的代价更为明显。因此，对于经常需要查询的列，建议将其包含在非聚集索引中（覆盖索引），这样可以避免回表操作，提高查询性能
注意
回表操作并不适用于使用聚集索引的查询，因为聚集索引包含了整个表的数据，查询时无需再回到表中查找数据
聚簇索引
什么是聚簇索引
聚簇索引（Clustered Index）是一种对表中数据进行物理存储排序的索引。它决定了数据在磁盘上的物理存储顺序。具体来说，聚簇索引按照索引键的顺序来存储表中的数据,也将聚簇索引的叶子节点称为数据页。也就是说，表中行的物理顺序与键值的逻辑（索引）顺序相同。一个表只能有一个聚簇索引，因为数据行的物理顺序只能有一种。
一般建表会用一个自增主键做聚簇索引，没有的话MySQL会默认创建，但是这个主键如果更改代价较高，故建表时要考虑自增ID不能频繁update这点。
我们日常工作中，根据实际情况自行添加的索引都是辅助索引，辅助索引就是一个为了需找主键索引的二级索引，先找到主键索引再通过主键索引找数据；
MySQL数据库中innodb存储引擎，B+树索引可以分为聚簇索引（也称聚集索引，clustered index）和辅助索引（有时也称非聚簇索引或二级索引，secondary index，non-clustered index）。这两种索引内部都是B+树，聚集索引的叶子节点存放着一整行的数据。
Innobd中的主键索引是一种聚簇索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。
Innodb使用的是聚簇索引，MyISam使用的是非聚簇索引
聚簇索引的优缺点
优点：
1.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
2.聚簇索引对于主键的排序查找和范围查找速度非常快
缺点：
1.插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。
辅助索引（非聚簇索引）
在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置，而是主键值。通过辅助索引首先找到的是主键值，再通过主键值找到数据行的数据页，再通过数据页中的Page Directory找到数据行。
Innodb辅助索引的叶子节点并不包含行记录的全部数据，叶子节点除了包含键值外，还包含了相应行数据的聚簇索引键。
辅助索引的存在不影响数据在聚簇索引中的组织，所以一张表可以有多个辅助索引。在innodb中有时也称辅助索引为二级索引。
两者区别
聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。
聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续
聚集索引:物理存储按照索引排序；索引的键值逻辑顺序决定了表数据行的物理存储顺序。
非聚集索引:物理存储不按照索引排序；非聚集索引则就是普通索引了，仅仅只是对数据列创补充内容建相应的索引，不影响整个表的物理存储顺序
补充内容
前缀索引
指对字符类型字段的前几个字符或对二进制类型字段的前几个bytes建立的索引，而不是在整个字段上建索引
优点：可以节约空间，提高查询效率
缺点：降低选择性。索引选择性（INDEX SELECTIVITY）是不重复的索引值（也叫基数）和表中所有行数（T）的比值，数值范围为 1/T ～1。对于前缀索引而言，前缀越长往往会得到好的选择性，但是短的前缀会节约空间，所以实操的难度在于前缀截取长度的抉择。
使用场景：当需要索引很长的字符列时，前缀索引是一个有效的策略，但前提是此前缀的标识度高。