数据库之索引篇

概述

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

一、索引简介

1. 什么是索引？

   • 索引是对数据表中一列或多列的值进行排序的一种结构，可提高特定数据的查询速度。
   • 索引是一个单独的、存储在磁盘上的数据库结构，包含着数据表里所有记录的引用指针。

2. 索引的优点

   • 通过创建唯一索引，可以保证数据表中每一行数据的唯一性；
   • 加快数据的查询速度，这是创建索引的主要原因；
   • 实现数据的参考完整性，加速表与表之间的连接；
   • 减少分组查询中分组和排序的时间。

3. 索引的缺点

   • 创建、维护索引要耗费时间，随数据量的增减而增加；
   • 索引占用磁盘空间，索引文件可能比数据文件更快达到最大文件尺寸；
   • 对表中数据进行更新、修改和删除操作的时候，索引也要动态地维护，这会降低数据的维护速度。

4. 索引的分类

   • 普通索引和唯一索引

     基本索引：允许索引列有重复值和空值。

     唯一索引：索引列的值必须唯一，但允许控制。主键索引属于特殊的唯一索引，列值不允许有空值。

   • 单列索引和组合索引

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

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

   • 全文索引：用于全文搜索，FULLTEXT，可重复，允许空值。
   • 空间索引：空间数据类型的字段上建立的索引，非空。

5. 索引设计原则

   • 索引并非越多越好：占空间、影响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的空间索引
     

2. 在已有表上建立索引（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约束字段的唯一索引不能被删除
   

2. 使用DROP INDEX删除索引

   DROP INDEX index_name ON table_name;