MySQL高级第五篇：索引的创建与设计原则

一、索引的分类与使用

1. 索引的分类

• 从功能逻辑上来说，分为：普通索引、唯一索引、主键索引、全文索引。

普通索引：

不加任何限制条件，只为提高查询效率的索引。

唯一索引：

限制该索引的值必须是唯一的，但允许有空值，一张表可以有多个唯一索引。

主键索引

一种特殊的唯一索引，增加了不为空的约束，一张表最多只有一个主键索引。

全文索引：

利用分词技术等多种算法分析关键词的频率和重要性筛选，适合大型数据集。

• 按照物理实现来分，分为：聚簇索引 和 非聚簇索引。

聚簇索引

针对主键构建的索引，是一种数据存储方式，表的数据行都存放在索引树的叶子页中。

非聚簇索引

针对非主键构建的索引，它不保存完整记录，且按照索引列排序。

• 按照作用字段个数来分，分成：单列索引 和 联合索引。

单列索引：

单个列上创建的索引，可以是普通索引、唯一索引、主键索引等

联合索引：

多个字段组合创建的索引，使用联合索引时遵循 最左前缀原则。

2. 索引的创建和删除

创建表的时候创建索引：隐式

# 主键字段，唯一约束字段，外键自动创建索引
CREATE TABLE 表名(
	字段名 INT PRIMARY KEY AUTO_INCREMENT,
	字段名 VARCHAR(20) UNIQUE,
	字段名 INT,
	CONSTRAINT 索引名 FOREIGN KEY(字段名) REFERENCES 表名(字段名)
)

在已存在的表中创建索引：显式

CREATE TABLE table_name(
	[col_name data_type]
	[UNIQUE | FULLTEXT | SPATIAL][INDEX | KEY][index_name](col_name [length])[ASC | DESC]
	)

删除索引：

ALTER TABLE table_name DROP INDEX index_name

DROP INDEX index_name ON table_name

二、MySQL8.0索引新特性

1. 支持降序索引(仅限InnoDB引擎)

• MySQL在8.0版本之前创建的仍然是升序索引，使用时进行反向扫描，这大大降低了数据库的效率。
• 在某些场景下，降序索引意义重大。
• 例如：如果一个查询，需要对多个列进行排序，且顺序要求不一致，那么使用降序索将会避免数据库使用额外的文件排序操作，从而提高性能。

2.隐藏索引

• 在MySQL5.7版本及之前，只能通过显式的方式删除索引。
• 如果发现删除索引后出现错误，又只能通过显式创建索引的方式将删除的索引创建回来。操作成本非常高。
• 从MySQL8.x开始支持隐藏索引，只需要将待删除的索引设置为隐藏索引，使查询优化器不再使用这个索引，确认将索引设置为隐藏索引后系统不受任何影响再彻底删除。
• 如果你想验证某个索引删除之后的查询性能影响,就可以暂时先隐藏该索引。
• 但主键不能被设置为隐藏索引。

三、索引的设计原则

1. 适合创建索引的情况总结

• 1.唯一性约束的字段
• 2.频繁使用where查询的字段
• 3.经常GROUP BY 和 ORDER BY 的列
• 4.UPDATE，DELETE 时的 where 条件列
• 5.DISTINCT的字段
• 6.多表JOIN连接时创建索引注意
  • 连接表的数量尽量不超过3张，每加一张表就相当于嵌套一次循环
  • 对 where 条件创建索引
  • 用于连接的字段创建索引
• 7.尽量给数据类型小的列添加索引，节省空间，让一页存在更多记录，比较速度更快
• 8.使用区分度高的列作为索引
• 9.联合索引时，使用最频繁的列放在联合索引的左侧

2. 不适合创建索引的情况总结

• 1.where，group by ，orderby 条件使用不到的字段不要加索引
• 2.数据量小的表不要添加索引
• 3.有大量重复数据的列不要添加索引
• 4.经常更新的表尽量少创建索引
• 5.不建议使用无序的值作为索引
• 6.不再使用或很少使用的索引及时删除