数据库索引
为表创建索引的目的是为了加快查询过程,从而更快的响应请求。
而创建索引时,实际上会增加表所占的存储空间,是一直以空间换时间的理念。
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];
适合创建索引的多种情况
- 字段的属性值存在唯一性约束
- 条件查询中经常出现的字段
- 经常GROUP BY 和 ORDER BY的字段
- UPDATE和DELETE的条件字段
- DISTINCT字段
- 多表查询时的连接字段(类型需要一致)
- 相同情况下,使用列类型较小的字段(节省存储空间)
- 使用字符串前缀(节省空间,但需要分析确认前缀长度)
- 区分度高的字段,既记录的数量与字段值的数量相近
- 使用最频繁的字段放在索引的最左侧,与B+树结构有关
- 多字段的联合索引要优于多字段的单索引
不适合创建索引的多种情况
- 条件查询中不使用的字段
- 数据量小的表中不需要创建索引
- 字段的值存在大量重复(与上述的情况9相反)
- 经常更新的字段不需要创建索引(增加系统开销)
- 无序字段不需要创建索引
- 经常使用的索引建议删除
- 冗余和重复的索引