【数据库】Mysql创建数据库索引

数据库索引

为表创建索引的目的是为了加快查询过程，从而更快的响应请求。
而创建索引时，实际上会增加表所占的存储空间，是一直以空间换时间的理念。
Mysql中，若使用InnoDB作为存储引擎，那么默认的索引存储方式就是B+树。
因为创建索引的目的是为了加快查询过程，若有大量的更新和插入过程，可先去除索引，因为索引在插入或更新记录的时候也会动态的更新，这将增加插入和更新数据的执行时间。

建表时创建索引

-- 主键索引
CREATE TABLE table_name(
	param type PRIMARY KEY AUTO_INCREMENT,
	...
);

-- 唯一索引
CREATE TABLE table_name(
	param type UNIQUE,
	...
);

-- 外键索引
CREATE TABLE table_name(
	param type,
	...,
	CONSTRANT foreing_key_name FOREING KEY (param) REFERENCES table_name(key);
);
-- 一般索引, 只有Mysql 8.0以上版本支持索引升降序排列 
CREATE TABLE table_name(
	param type,
	...,
	INDEX[KEY] index_name param ASC[DESC]
);

建表后添加索引

-- 方式一
ALTER TABLE table_name ADD INDEX[KEY] index_name(param);
-- 方式二
CREATE INDEX index_name ON table_name(param);

删除索引

-- 方式一
ALTER TABLE table_name DROP INDEX[KEY] index_name;

-- 方式二
DROP INDEX index_name ON table_name;

-- 注意！！！ 有**AUTO_INCREMENT** 定义的索引无法删除

隐藏索引

与删除索引不同，隐藏索引的目的是为了让索引在查询是失效，但不从存储空间中真实的删除。
直接在定义索引的最后添加 VISIBLE 或 INVISIBLE 关键字即可，一般使用在修改的过程。

ALTER TABLE table_name ALTER INDEX[KEY] INVISIBLE[VISIBLE];

适合创建索引的多种情况

1. 字段的属性值存在唯一性约束
2. 条件查询中经常出现的字段
3. 经常GROUP BY 和 ORDER BY的字段
4. UPDATE和DELETE的条件字段
5. DISTINCT字段
6. 多表查询时的连接字段（类型需要一致）
7. 相同情况下，使用列类型较小的字段（节省存储空间）
8. 使用字符串前缀（节省空间，但需要分析确认前缀长度）
9. 区分度高的字段，既记录的数量与字段值的数量相近
10. 使用最频繁的字段放在索引的最左侧，与B+树结构有关
11. 多字段的联合索引要优于多字段的单索引

不适合创建索引的多种情况

1. 条件查询中不使用的字段
2. 数据量小的表中不需要创建索引
3. 字段的值存在大量重复（与上述的情况9相反）
4. 经常更新的字段不需要创建索引（增加系统开销）
5. 无序字段不需要创建索引
6. 经常使用的索引建议删除
7. 冗余和重复的索引