MySQL中的索引

在MySQL中，索引类似于书籍的目录，如果想要快速访问数据表中的特定信息，可以建立索引加快数据查询效率。

索引概述

索引是数据库中用来提高数据查询效率最常用的数据库对象，好比新华字典的音序表，通过音序表可以快速的查找内容。索引对数据表中一列或多列的值与记录行之间按照一定的顺序建立关系，以提高对数据表中数据的查询速度。

根据索引实现语法不同，MySQL中常见的索引大致分为5种，具体描述如下：

1、普通索引是MySQL中的基本索引类型，使用KEY或INDEX定义，不需要添加任何限制条件。

2、创建唯一性索引的字段，允许有NULL值，但需要保证索引对应字段中的值是唯一的。

3、主键索引是一种特殊的唯一性索引，用于根据主键自身的唯一性标识每一条记录。主键索引的字段不允许有NULL值。

4、全文索引主要用于提高在数据量较大的字段中的查询效率。

5、空间索引只能创建在空间数据类型的字段上。

上述5种索引可以在一列或多列字段上进行创建，根据创建索引的字段个数，可以将索引分为单列索引和复合索引，具体介绍如下：

1、单列索引指的是在表中单个字段上创建索引，它可以是普通索引、唯一索引或者全文索引，只要保证该索引只对应表中一个字段即可。

2、复合索引指的是在表中多个字段上创建一个索引，并且只有在查询条件中使用了这些字段中的第一个字段时，该索引才会被使用。

需要注意的是，虽然索引可以提高数据的查询速度，但索引会占用一定的磁盘空间，并且在创建和维护索引时，其消耗的时间是随着数据量的增加而增加的。因此，使用索引时，应该综合考虑索引的优点和缺点。

索引的创建

创建数据表的同时创建索引

CREATE TABLE 表名（字段名1 数据类型[完整性约束条件],
                   …
{INDEX | KEY} [索引名] [索引类型] (字段列表) 
| UNIQUE [INDEX | KEY] [索引名] [索引类型] (字段列表) 
| PRIMARY KEY  [索引类型] (字段列表) 
| {FULLTEXT | SPATIAL} [INDEX | KEY]  [索引名]  (字段列表) 
                   …
                   );

上述语法格式中各选项的含义如下所示：

（1）{INDEX | KEY}：INDEX 和KEY为同义词，表示索引，二者选一即可。

（2）索引名：可选项，表示为创建的索引定义的名称，不使用该选项时，默认使用建立索引的字段表示，复合索引则使用第一个字段的名称作为索引名称。

（3）索引类型：可选项，某些存储引擎允许在创建索引时指定索引类型，使用语法是USING {BTREE | HASH}，不同的存储引擎支持的索引类型也不同。

（4）UNIQUE：可选项，表示唯一性索引。

（5）FULLTEXT：表示全文索引。

（6）SPATIAL：表示空间索引。 

下面通过案例演示如何在创建数据表dept_index时，创建单列的普通索引、唯一性索引、主键索引、全文索引和空间索引。

CREATE TABLE dept_index(
	id INT,
	deptno  INT ,
	dname   VARCHAR(20),
	introduction VARCHAR(200),
	address GEOMETRY NOT NULL SRID 4326,
	PRIMARY KEY(id),        -- 创建主键索引
	UNIQUE INDEX (deptno),  -- 创建唯一性索引
	INDEX (dname),          -- 创建普通索引
	FULLTEXT (introduction),-- 创建全文索引
	SPATIAL INDEX (address) -- 创建空间索引
) ;

显示创建数据表index_normal的语句：

SHOW CREATE TABLE dept_index\G

 结果：

Table: dept_index
Create Table: CREATE TABLE `dept_index` (
  `id` int NOT NULL,
  `deptno` int DEFAULT NULL,
  `dname` varchar(20) DEFAULT NULL,
  `introduction` varchar(200) DEFAULT NULL,
  `address` geometry NOT NULL /*!80003 SRID 4326 */,
  PRIMARY KEY (`id`),
  UNIQUE KEY `deptno` (`deptno`),
  KEY `dname` (`dname`),
  SPATIAL KEY `address` (`address`),
  FULLTEXT KEY `introduction` (`introduction`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

上述案例只是为了演示创建数据表时创建单列索引，真实开发中一般不会为字段都加索引，需要避免过度使用索引，因为索引不仅会占用一定的物理空间，而且当对数据表中的数据进行增加、删除和修改时，也会需要动态维护索引，导致数据库的写性能降低和减缓数据表的修改速度。

下面对创建数据表时，创建复合索引进行演示。

例如，创建数据表index_multi，在数据表中的id和name字段上建立索引名为multi的普通索引，具体SQL语句如下所示。

CREATE TABLE index_multi(
	id INT NOT NULL,
	name VARCHAR(20) NOT NULL,
	score FLOAT,
	INDEX multi(id,name)
);

通过SHOW CREATE TABLE语句查看数据表index_multi的创建信息：

SHOW CREATE TABLE index_multi\G

 结果：

       Table: index_multi
Create Table: CREATE TABLE `index_multi` (
  `id` int NOT NULL,
  `name` varchar(20) NOT NULL,
  `score` float DEFAULT NULL,
  KEY `multi` (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

需要注意的是，复合索引中，多个字段的设置顺序要准守“最左前缀原则”，也就是在创建索引时，把使用频繁最高的字段放在索引字段列表的最左边，次频繁使用的字段放在设计索引字段列表的第二位，以此类推。 

在已有的数据表上创建索引

若想在一个已经存在的数据表上创建索引，可以使用CREATE INDEX语句，CREATE INDEX语句创建索引的具体语法格式如下所示。

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名
[索引类型] ON 数据表名 (字段列表);

在上述语法格式中，UNIQUE、FULLTEXT和SPATIAL都是可选参数，分别用于表示唯一性索引、全文索引和空间索引。

为了便于读者更好的观察CREATE INDEX语句创建索引的结果，下面先创建一个新数据表dept_index02，创建dept_index02表的SQL语句如下所示。

CREATE TABLE dept_index02(
id INT,
deptno  INT ,
dname   VARCHAR(20),
introduction VARCHAR(200)
);

 根据CREATE INDEX语句中字段列表的个数，可将创建的索引分为单列索引和复合索引，下面针对这两种情况分别进行讲解。

通过CREATE INDEX语句可以创建普通索引、唯一性索引、全文索引和空间索引。由于创建索引的格式都一样，此处以创建唯一性索引为例，演示单列索引的创建。

例如，在数据表dept_index02中的id字段上，建立一个名称为unique_id的唯一性索引，具体SQL语句及执行结果如下所示。

CREATE UNIQUE INDEX unique_id ON dept_index02(id);

下面通过SHOW CREATE TABLE语句查看数据表book的创建信息，以验证id字段上是否成功创建索引，具体SQL语句： 

SHOW CREATE TABLE dept_index02\G

下面使用CREATE INDEX语句创建复合索引。 例如，在dept_index02表中的deptno字段和dname字段上，创建一个名称为multi_index的复合索引，具体SQL语句如下所示：

CREATE INDEX multi_index ON dept_index02(deptno,dname);

验证：

 SHOW CREATE TABLE dept_index02\G

修改数据表的同时创建索引

在已经存在的数据表中创建索引，除了可以使用CREATE INDEX语句外，还可以使用ALTER TABLE语句。使用ALTER TABLE语句在修改数据表的同时创建索引，其基本语法格式如下所示。

ALTER TABLE 数据表名 
ADD {INDEX | KEY} [索引名] [索引类型] (字段列表) 
| ADD UNIQUE [INDEX | KEY] [索引名] [索引类型] (字段列表) 
| ADD PRIMARY KEY  [索引类型] (字段列表) 
| ADD {FULLTEXT | SPATIAL} [INDEX | KEY]  [索引名]  (字段列表) 

为了便于读者更好的查看ALTER TABLE语句创建索引的结果，下面创建一个新的数据表dept_index03，创建数据表dept_index03的SQL语句如下所示：

CREATE TABLE dept_index03(
id INT,
deptno  INT ,
dname   VARCHAR(20)
) ;

根据ALTER TABLE语句中索引作用字段列表的个数，可将创建的索引分为单列索引和复合索引，下面针对这两种情况分别进行讲解。

下面以创建唯一性索引为例，演示使用ALTER TABLE语句创建单列索引。 例如，在数据表dept_index03中的id字段上，创建名称为index_id的唯一性索引，具体SQL语句如下所示：

ALTER TABLE dept_index03 ADD UNIQUE INDEX index_id(id);

验证：

SHOW CREATE TABLE dept_index03\G

需要注意的是，创建唯一性索引时，需要确保数据表中的数据不存在重复的值，否则会出错。

上面使用ALTER TABLE语句创建的普通索引、唯一性索引、全文索引和空间索引都是对数据表中的单列字段设定的索引。下面使用ALTER TABLE语句演示复合索引的创建。 例如，在dept_index03表中的deptno字段和dname字段上，创建一个名称为multi_index的复合唯一性索引，具体SQL语句如下所示：

ALTER TABLE dept_index03 ADD UNIQUE INDEX multi_index(deptno,dname);

验证：

SHOW CREATE TABLE dept_index03\G

 查看索引及其使用情况

SHOW {INDEXES|INDEX|KEYS} FROM 数据表名;

上述语法格式中，使用INDEXES、INDEX、KEYS含义都一样，都可以查询出数据表中所有的索引信息。

举例：

SHOW INDEX FROM dept_index \G

结果：

… 此处省略了4行记录
*************************** 5. row ***************************
        Table: dept_index
   Non_unique: 1
     Key_name: introduction
 Seq_in_index: 1
  Column_name: introduction
    Collation: NULL
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment:
Index_comment:
      Visible: YES
   Expression: NULL
5 rows in set (0.00 sec)

 索引信息字段的含义

字段名	描述的含义
Table	索引所在的数据表的名称
Non_unique	索引是否可以重复，0表示不可以，1表示可以
Key_name	索引的名字，如果索引是主键索引，则它的名字为PRIMARY
Seq_in_index	建立索引的字段序号值，默认从1开始
Column_name	建立索引的字段
Collation	索引字段是否有排序，A表示有排序，NULL表示没有排序
Cardinality	MySQL连接时使用索引的可能性（精确度不高），值越大可能性越高
Sub_part	前缀索引的长度，如字段值都被索引，则Sub_part为NULL

字段名	描述的含义
Packed	关键词如何被压缩，如果没有被压缩，则为NULL
Null	索引字段是否含有NULL值，YES表示含有，NO表示不含有
Index_type	索引方式，可选值有FULLTEXT、HASH、BTREE、RTREE
Comment	索引字段的注释信息
Index_comment	创建索引时添加的注释信息
Visible	索引对查询优化器是否可见，YES表示可见，NO表示不可见
Expression	使用什么表达式作为建立索引的字段，NULL表示没有

在MySQL中除了可以查看数据表中的索引信息，还可以通过EXPLAIN关键字分析SQL语句的执行情况，例如分析SQL语句执行时是否使用了索引。EXPLAIN可以分析的语句有SELECT、UPDATE、DELETE、INSERT和REPLACE。

下面以查询数据表dept_index中id为1的部门信息为例分析语句的执行情况，具体如下。

（1）先往数据表dept_index中插入数据，具体SQL语句如下所示。

INSERT INTO dept_index VALUES
(1,'10','总裁办','决定公司发展的部门',ST_GeometryFromText('point(88 34)',4326)),
 (2,'20','研究院','研发公司核心产品的部门',ST_GeometryFromText('point(88 34)',4326));

 （2）使用EXPLAIN关键字查看查询语句的执行情况，具体SQL语句及如下所示。

EXPLAIN SELECT id FROM ems.dept_index WHERE id=1 \G

结果：

id: 1
  select_type: SIMPLE
        table: dept_index
   partitions: NULL
         type: const
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

分析执行语句的字段

字段名	描述
id	查询标识符，默认从1开始，如果使用了联合查询，则该值依次递增
select_type	查询类型，它的值包含多种，如SIMPLE表示简单SELECT，不使用UNION或子查询
table	输出行所引用的数据表的名称
partitions	匹配的分区
type	连接的类型，它的值有多种，如ref表示使用前缀索引或条件中含有运算符“=”或“<=>”等
key_len	索引字段的长度
ref	表示哪些字段或常量与索引进行了比较
rows	预计需要检索的记录数
filtered	按条件过滤的百分比
Extra	附件信息，如Using index表示使用了索引覆盖

索引的删除

由于索引会占用一定的磁盘空间，所以为了避免影响数据库性能，应该及时删除不再使用的索引。在MySQL中，可以使用ALTER TABLE语句或DROP INDEX语句删除索引。下面分别讲解这两种索引删除方式。

使用ALTER TABLE删除索引

ALTER TABLE 表名 DROP INDEX 索引名;

例如，通过ALTER TABLE语句删除删除数据表dept_index中名称为introduction的全文索引，具体SQL如下所示。

ALTER TABLE dept_index DROP INDEX introduction;

 使用DROP INDEX删除索引

DROP INDEX 索引名 ON 数据表名;

例如，删除数据表dept_index中名称为dname的索引，具体SQL语句如下所示。

DROP INDEX dname ON dept_index;

上机实践：图书管理系统中索引的应用

# 在图书表book的图书名称name上创建一个索引index_bookname

# 在图书名称name和图书状态state上建立一个索引index_bookname_state

# 删除索引index_bookname_state