mysql创建聚簇索引_MySQL进阶之索引

索引简介

索引是帮助数据库系统高效获取数据的一种数据结构，能加快数据的查询速度。大部分时候，索引会存储在磁盘上的文件中的，可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中。索引是在存储引擎中实现的，不同的存储引擎，其索引的数据结构可能不同(即可能使用不同数据结构去实现索引)。MySQL默认的存储引擎InnoDB，其使用的索引为B+树结构组织。B+树是B树的一种特殊类型，其区别在于B+树只有叶子节点存储数据，且叶子节点使用指针指向组成有序链表。

使用索引的优劣

适当的使用索引，可以加快数据的查询速度，但使用不当也会适得其反。使用索引究竟有什么好处和坏处呢?下面是使用索引的优劣对比说明。

优势

• 提高数据检索的效率，降低数据库的IO成本
• 使用索引排序，可以降低数据排序的成本以及CPU的消耗

被索引的列会自动排序，包括单列索引和组合索引，当使用order by按照列顺序进行排序时，可以提高效率

劣势

• 索引会占用磁盘空间
• 降低更新表的效率

每次对数据进行增删改，更新数据表数据时，也需要更新索引，所以效率有所降低

常用索引分类

从用户使用角度划分，索引分类包括单列索引、组合索引、全文索引和空间索引。比较常用的是单列索引和组合索引。

单列索引

单列索引又分为以下3种

• 普通索引：MySQL中基本的索引类型，索引列的值允许重复值和空值NULL
• 唯一索引：索引列的值必须唯一，不允许重复，但可以为NULL
• 主键索引：是一种特殊的唯一索引，不允许重复也不允许为NULL

组合索引

组合索引是在表中的多个字段组合上创建的索引，其使用遵循最左前缀原则(最左匹配原则)。除了主键索引外，一般情况下，建议使用一个组合索引代替多个单列索引，相较于多个单列索引，一个组合索引可以节省存储空间。

从储存引擎存储逻辑角度划分，可以分为主键索引、次要索引和组合索引，主键索引和次要索引存储的是单列索引。

索引操作

对索引的创建、删除等操作可以参考 快速入门MySQL

聚簇索引与非聚簇索引

指数据记录存储在主键索引中，通过主键索引，就可以获取记录所有的列，也就是说数据和索引是在一起(一个文件中)，这就是聚集索引。 InnoDB存储引擎其索引属于聚簇索引(主键索引)，即其索引树的叶子节点存储了数据记录(行)。

与聚簇索引相反，索引和数据分开文件存储的，称这类索引为非聚簇索引，MyISAM存储引擎属于此类。MyISAM索引树叶子节点存储的是数据记录在数据文件中的内存地址。

InnoDB索引存储结构

主键索引

InnoDB的主键索引为聚簇索引，它按照如下规则创建

• 当定义了主键后，InnoDB会利用主键来生成其聚簇索引
• 如果没有主键，InnoDB会选择一个非空的唯一索引来创建聚簇索引
• 如果非空的唯一索引也没有，InnoDB会隐式的创建一个自增的列来作为聚簇索引

以下是InnoDB主键索引的存储结构示意图，其中蓝色块的数字为主键id的值

InnoDB主键索引存储结构示例

辅助索引

辅助索引也称为次要索引，即非主键列创建的其它单例索引。其叶子节点存储的是主键id。查询时，通过辅助索引匹配到主键id后，再到主键索引中检索数据。辅助索引其存储结构如下图所示，其中蓝色块为非主键列的值

InnoDB辅助索引存储结构示例

组合索引

组合索引的存储结构和辅助索引类似，区别是辅助索引中的用于检索的key的值(即蓝色块)不是一列，而是由多个列组成，二者最终的叶子节点存储的都是主键id的值。