MySQL索引基础知识

saotolls

MySQL索引基础知识

定义

索引存在于对应的索引文件中，索引本质就是一组排好了顺序的一组数据

作用

• 索引数据中进行排序时，避免再次排序
• 在索引数据中对数据行进行定位时，避免全表扫描

索引基本操作

查看表文件中已经存在的索引

SHOW INDEX FROM <表名>;

创建索引

CREATE INDEX <索引名称> ON <表名> (<字段名>);

删除索引

DROP INDEX <索引名称> ON <表名>;

索引的分类

聚簇索引与非聚簇索引

聚簇索引

只能来自于采用Innodb存储引擎表的数据

• MySQL自动将采用了InnoDB存储引擎表中的主键建立索引，这个索引就是聚簇索引；
• 如果当前表中没有主键，MySQL将会选择添加一个唯一性约束的字段，作为聚簇索引；
• 如果当前表中既没有主键字段，也没有添加了唯一性约束字段，MySQL将随机选取一个字段作为聚簇索引；
• 在采用InnoDB存储引擎的表文件中，必然存在一个聚簇索引；
• 在采用InnoDB存储引擎的表文件中，只能有一个聚簇索引；
• 在表文件中其他字段上建立的索引都是非聚簇索引；

非聚簇索引

• 由开发人员自行创建
• 对于采用了InnoDB存储引擎的数据表文件，除了一个聚簇索引之外，其他字段上创建的索引都是非聚簇索引；
• 在采用了MYISAN存储引擎的数据表文件中，创建的所有索引都是非聚簇索引；

聚簇索引和非聚簇索引的区别

聚簇索引：数据节点上存储的当前数据所在行数以及当前数据所在数据行的内容；因为聚簇索引的这种结构，所以在查询时可以直接在定位数据节点上，读取当前数据所在数据行中字段信息，不需要使用I/O流到磁盘上表文件上进行读取，因此执行效率较快；

非聚簇索引：数据节点上存储的当前数据所在行数；由于数据节点存储的当前数据所在行数，没有其他相关内容，所以定位之后，需要使用I/O流到硬盘i上表文件中定位数据行其他字段内容，因此执行效率相对较慢；

主键索引，唯一性索引

主键索引
如果当前表文件中字段添加了主键约束，MySQL主动的将当前字段上数据进行排序，其生成的索引被称为主键索引；

唯一性索引
如果当前表文件中字段添加了唯一性约束，MySQL主动的将当前字段上的数据进行排序，其生成的索引被称为唯一性索引；

普通索引
如果当前表文件中字段上没有添加任何约束霍索引，此时在这个字段上创建的索引就是普通索引；

执行效率：主键索引 > 唯一性索引 > 普通索引

单个索引，复合索引

• 单字段索引的创建

  CREATE INDEX <索引名> ON <表名> (字段名);

• 复合索引的创建

  CREATE INDEX <索引名> ON <表名> (字段名1, 字段名2, 字段名3, ...);

hyy

催更催更催更