更新记录
转载请注明出处。
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 | 表示索引类型 |