概述

  • 索引简介
  • 创建索引
  • 删除索引

一、索引简介

  1. 什么是索引？
  • 索引是对数据表中一列或多列的值进行排序的一种结构，可提高特定数据的查询速度。
  • 索引是一个单独的、存储在磁盘上的数据库结构，包含着数据表里所有记录的引用指针。
  1. 索引的优点
  • 通过创建唯一索引，可以保证数据表中每一行数据的唯一性；
  • 加快数据的查询速度，这是创建索引的主要原因；
  • 实现数据的参考完整性，加速表与表之间的连接；
  • 减少分组查询中分组和排序的时间。
  1. 索引的缺点
  • 创建、维护索引要耗费时间，随数据量的增减而增加；
  • 索引占用磁盘空间，索引文件可能比数据文件更快达到最大文件尺寸；
  • 对表中数据进行更新、修改和删除操作的时候，索引也要动态地维护，这会降低数据的维护速度。
  1. 索引的分类
  • 普通索引和唯一索引
    基本索引：允许索引列有重复值和空值。
    唯一索引：索引列的值必须唯一，但允许控制。主键索引属于特殊的唯一索引，列值不允许有空值。
  • 单列索引和组合索引

单列索引：一个索引只包含单个列，一个表可以有多个单列索引。

组合索引：多个字段组合上创建索引，使用时遵循最左前缀集合。

  • 全文索引：用于全文搜索，FULLTEXT，可重复，允许空值。
  • 空间索引：空间数据类型的字段上建立的索引，非空。
  1. 索引设计原则
  • 索引并非越多越好：占空间、影响INSERT/UPDATE/DELETE等操作；
  • 数据量小的表最好不使用索引，查询时间可能短于遍历索引的时间；
  • 尽可能在不同值多的列上建立索引，这有利于提高查询效率；
  • 唯一性列指定唯一索引；
  • 对频繁进行排序或分组的列（组合）上建立索引。

二、创建索引

  1. 创建表时建立索引（INDEX 或 KEY）
  • 创建普通索引
CREATE TABLE book
(
    bookid INT NOT NULL,
    bookname VARCHAR(225) NOT NULL,
    authors VARCHAR(225) NOT NULL,
    info VARCHAR(225) NULL,
    comment VARCHAR(225) NULL,
    year_pabulication YEAR NOT NULL,
    INDEX(year_pabulication)
);
-- 可使用EXPLAIN语句查看索引是否正确使用
  • 创建唯一索引（UNIQUE INDEX）
CREATE TABLE t1
(
    id INT NOT NULL,
    name CHAR(30) NOT NULL,
    UNIQUE INDEX UniqIdx(id)
);
-- 在id字段上创建一个名为UniqIdx的唯一索引
  • 创建单列索引
CREATE TABLE t2
(
    id INT NOT NULL,
    name CHAR(50) NULL,
    INDEX SingleIdx(name(20))
);
-- 在id字段上创建一个名为UniqIdx的单列索引，长度为20
  • 创建组合索引
CREATE TABLE t3
(
    id INT NOT NULL,
    name CHAR(30) NOT NULL,
    age INT NOT NULL,
    info VARCHAR(225),
    INDEX MutiIdx(iid,name,age(100))
);
-- 在iid,name,age上创建名为MutiIdx的组合索引
-- 组合索引使用时遵循“最左前缀”
  • 创建全文索引
CREATE TABLE t4
(
    id INT NOT NULL,
    name CHAR(30) NOT NULL,
    age INT NOT NULL,
    info VARCHAR(225),
    FULLTEXT INDEX FullTxtIdx(info)
);
-- 在info字段上创建名为FullTxtIdx的FULLTEXT索引
  • 创建空间索引
    必须在MyISAM类型的表中创建，非空
CREATE TABLE t5 
(
    g GEOMETRY NOT NULL,
    SPATIAL INDEX spatIdx(g)
);
-- 在g字段上创建名为spatIdx的空间索引
  1. 在已有表上建立索引（ALTER TABLE 或 CREATE INDEX）
  • 使用ALTER TABLE创建索引
-- 基本语法
ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]
  • 使用CREATE INDEX创建索引
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name (col_name[length],...) [ASC|DESC]

三、删除索引

  1. 使用ALTER TABLE删除索引
ALTER TABLE tabe_name DROP INDEX index_name;
-- 注：添加AUTO-INCREMENT约束字段的唯一索引不能被删除
  1. 使用DROP INDEX删除索引
DROP INDEX index_name ON table_name;