MySQL教程 - 索引（Index）

更新记录
转载请注明出处。
2022年9月2日 发布。
2022年9月2日 从笔记迁移到博客。

索引

说明#

是一种对数据表的一列或多列进行排序的数据结构

索引是单独的、存储在磁盘上的数据库结构

索引包含对表中记录的指针

索引的作用#

提高查询速度

索引的缺点#

占用磁盘空间、DML操作插入和更新速度减慢，随着数据增加越明显

索引与表#

每个表支持16个索引，最小长度为256字节

索引与存储引擎#

MySQL中索引的内部存储类型有 B-Tree 和 HashTable

InnoDB、MyISAM引擎只支持B-Tree

Memory引擎默认为HashTable，支持两种类型

索引分类#

普通索引#

普通索引是MySQL中的基本索引类型,允许在定义索引的列中插入重复值和空值

唯一索引#

索引列的值必须要唯一，但允许有空值

主键是一种特殊的唯一索引,不可以为空

使用UNIQUE关键字定义唯一索引

主键索引#

是一种特殊的唯一索引，不允许有空值，一张表只能有一个

组合索引#

在表的多个字段组合上创建的索引

只有在查询条件中使用了这些字段左边的字段时索引才会被使用

索引列的组合必须唯一

单列索引#

单个列上的索引

全文索引#

支持在文本中全文查找值

只可以在CAHR、VARCHAR、TEXT类型上使用

只有MyISAM支持

允许在这些索引列中插入重复值和空值

只有MyISAM支持

空间索引#

MySQL在5.7之后版本支持空间索引，而且支持OpenGIS几何数据模型

只有MyISAM支持

对空间数据类型字段建立索引

空间数据类型有4中,分别是,geometry,point,linesteing和polygon.

使用spatial关键字进行扩展

必须将其声明为not null

使用索引注意#

索引不是越多越好

经常查询的表可以使用索引

避免对经常更新的表进行过多的索引

数据量小的表可以不用索引

在表达式中经常用到的不同值较多的字段建立索引

在频繁排序或分组的字段上建立索引

创建常规索引#

CREATE TABLE table_name
(
    [UNIQUE | FULLTEXT | SPATIAL] INDEX <索引名>
    (字段 [(索引长度)] [ASC | DESC],...)
);

或者

ALTER TABLE 表名
ADD [UNIQUE | FULLTEXT | SPATIAL ]  INDEX <索引名>
（列名.. [(索引长度)] [ASC | DESC]);

或者

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX <索引名>
ON 表名(字段 [(索引长度)] [ASC | DESC],...);

创建唯一索引

CREATE UNIQUE INDEX user_index
ON user (id);

删除索引#

ALTER TABLE 表名 DROP INDEX <索引名>;

或者

DROP INDEX <索引名> ON 表名;

删除主键索引

ALTER TABLE <表名> DROP PRIMARY KEY;

查看索引#

SHOW INDEX FROM 表名;

查询结果解释：

字段名	解释
table	表示创建索引的表
non_unique	表示索引非唯一 1代表是非唯一索引 0代表唯一索引
key_name	表示索引名称
seq_in_index	字段在索引的位置
column_name	表示定义索引的列字段
sub_part	表示索引的长度
null	表示该字段是否能为空值
index_type	表示索引类型