第十章 数据库索引
索引基本概念
-
索引的含义:
- 索引及存储在磁盘空间中的数据库结构,包含数据表中所有创建索引的记录
- 所有的存储引擎支持大于等于16索引
- 索引的长度为256字节以上
-
索引的特点
- 优点
- 创建唯一索引,可以保证数据表中的每行数据唯一
- 可以加快数据库的查询速度
- 加快表与表之间的连接
- 缺点
- 耗费时间
- 占用磁盘空间
- 优点
-
索引的类型
- BTREE
- 支持的引擎:MyISAM、InnoDB
- HASH
- 支持的引擎:MEMORY、HEAP
- BTREE
-
索引的分类
- 普通索引:MySQL数据库中最基本的索引,允许定义索引的列中出现重复值和空值
- 唯一索引:索引列的值可以为空,但必须唯一
- 组合索引:组合的字段值必须唯一
- 主键索引:不允许有空值
- 单列索引:一个索引只包含一个列,一个表可以有多个单列索引
- 组合索引:组合多个字段创建索引,使用最左侧字段才能使用索引
- 例如有字段(id,name,age),查询时遵循查询最左前缀查询,永远最先查询id,可以联合查询(id,name)或(id,age)
- 全文索引:支持全文查找,允许定义索引的列中出现重复值和空值
- 仅支持在CHAR,VARCHAR,TEXT类型的字段中创建全文索引
- 空间索引:对空间类型的字段进行索引
-
索引的设计要求
- 索引并非越多越好,否则会占用磁盘空间,降低管理语句的性能
- 避免对经常发生变动的数据表建立索引
- 若数据量比较小,无需创建索引
-
查询索引是否在使用的语句:
EXPLAIN SELECT * FROM 表名 WHERE 条件\G
- select_type:查询的方式
- type:定义了与数据表的关联管理
- possible_key:MySQL数据库在搜索时所用的所用的索引
- key:实际使用的索引
- key_len:索引长度,默认为1
普通索引的创建
- 在创建数据表的同时指定索引列:
CREATE TALBLE 表名 (字段1,字段2) INDEX|KEY (索引名) (字段名);
- 可以写index也可以写key,都是索引
- 字段名后可以括号,指定索引长度
- 例如,
CREATE TABLE TEST1 (ID INT(11),NAME VARCHAR(30),NIAN YEAR NOT NULL, INDEX (NIAN));
- 在已存在的数据表创建索引:
ALTER TABLE
- 在已存在的数据表添加索引:
CREATE INDEX
唯一索引的创建
- 在创建数据表的同时创建唯一索引:
CREATE TALBLE 表名 (字段1,字段2) UNIQUE INDEX (索引名) (字段名);
单列索引的创建
- 在创建数据表的同时创建单列索引:
CREATE TALBLE 表名 (字段1,字段2) INDEX (索引名) (字段名);
组合索引的创建
- 在创建数据表的同时创建组合索引:
CREATE TALBLE 表名 (字段1 NOT NULL,字段2 NOT NULL) INDEX (索引名) (字段名1,字段名2);
全文索引的创建
- 在创建数据表的同时创建全文索引:
CREATE TALBLE 表名 (字段1,字段2) FULLTEXT INDEX (索引名) (字段名);
空间索引的创建
- 在创建数据表的同时创建空间索引:
CREATE TALBLE 表名 (字段1,字段2) SPATIAL INDEX (索引名) (字段名);
索引的删除
- 指令1:
ALTER TABLE 表名 DROP INDEX 索引名;
- 指令2:
DROP INDEX 索引名 ON 表名;