数据库10-数据库引擎

第十章 数据库索引

索引基本概念

• 索引的含义：

  • 索引及存储在磁盘空间中的数据库结构，包含数据表中所有创建索引的记录
  • 所有的存储引擎支持大于等于16索引
  • 索引的长度为256字节以上

• 索引的特点

  • 优点
    • 创建唯一索引，可以保证数据表中的每行数据唯一
    • 可以加快数据库的查询速度
    • 加快表与表之间的连接
  • 缺点
    • 耗费时间
    • 占用磁盘空间

• 索引的类型

  • BTREE
    • 支持的引擎：MyISAM、InnoDB
  • HASH
    • 支持的引擎：MEMORY、HEAP

• 索引的分类

  • 普通索引：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 表名;