32、MySQL 索引

MySQL 索引可以大大提高 MySQL 的检索速度

打个比方，如果合理的设计且使用索引的 MySQL 是一辆高速公路的话，那么没有设计和使用索引的 MySQL 就是一个人力山村泥泞路

索引分单列索引和组合索引

1、 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引；
2、 组合索引，即一个索引包含多个列；

创建索引时，需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录

过多的使用索引将会造成滥用

因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE和 DELETE 因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件

建立索引会占用磁盘空间的索引文件

显示索引信息

SHOW INDEX FROM tablename; 命令可以列出某个表中的相关的索引信息

比如下面的 SQL 命名用于列出 tbl_language 表的索引信息

SHOW INDEX FROM tbl_language;

运行结果如下

MariaDB [souyunku]> SHOW INDEX FROM tbl_language;
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|Table        | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
|tbl_language |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1row in set (0.00 sec)

不太直观，我们加上 \G 格式化一下

MariaDB [souyunku]> SHOW INDEX FROM tbl_language\G;
*********************** 1. row ***********************
    Table: tbl_language
Non_unique: 0
 Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
 Sub_part: NULL
   Packed: NULL
     Null: 
Index_type: BTREE
  Comment: 
Index_comment: 
1row in set (0.00 sec)

ERROR: No query specified

普通索引

普通索引是最基本的索引，它没有任何限制

普通索引有以下几种创建方式

1、 创建索引；

CREATE INDEX indexName ON mytable(username(length));

如果是CHAR，VARCHAR 类型，length 可以小于字段实际长度

如果是BLOB 和 TEXT 类型，必须指定 length

2、 修改表结构(添加索引)；

ALTER table tableName ADD INDEX indexName(columnName)

3、 创建表的时候直接指定；

CREATE TABLE IF NOT EXISTS tbl_language(
   id INT UNSIGNED AUTO_INCREMENT,
   name VARCHAR(64) NOT NULL,
   url VARCHAR(128) NOT NULL,
   founded_at DATE,
   INDEX name (name(64)),
   PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

删除索引的语法

DROP INDEX [indexName] ON tablename;

唯一索引

唯一索引 与普通索引类似，不同的就是：唯一索引的索引列的值必须唯一，但允许有空值

如果是组合索引，则列值的组合必须唯一

唯一索引有有以下几种创建方式

1、 创建索引；

CREATE UNIQUE INDEX indexName ON mytable(username(length))

2、 修改表结构；

ALTER table mytable ADD UNIQUE [indexName] (username(length))

3、 创建表的时候直接指定；

CREATE TABLE IF NOT EXISTS tbl_language(
   id INT UNSIGNED AUTO_INCREMENT,
   name VARCHAR(64) NOT NULL,
   url VARCHAR(128) NOT NULL,
   founded_at DATE,
   UNIQUE name (name(64)),
   PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

使用 ALTER 命令添加索引

有四种方式来添加数据表的索引

1、 ALTERTABLEtbl_nameADDPRIMARYKEY(column_list);；

添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

2、 ALTERTABLEtbl_nameADDUNIQUEindex_name(column_list);；

创建索引的值必须是唯一的 ( 除了 NULL 外，NULL 可能会出现多次)

3、 ALTERTABLEtbl_nameADDINDEXindex_name(column_list);；

添加普通索引，索引值可出现多次

4、 ALTERTABLEtbl_nameADDFULLTEXTindex_name(column_list);；

指定了索引为 FULLTEXT ，用于全文索引

准备测试数据

运行下面的 SQL 语句在 souyunku 数据库中准备测试数据

DROP DATABASE IF EXISTS souyunku;

CREATE DATABASE souyunku default character set utf8mb4 collate utf8mb4_unicode_ci;

USEsouyunku;

DROP TABLE IF EXISTS tbl_language;

CREATE TABLE IF NOT EXISTS tbl_language(
id INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(64) NOT NULL,
url VARCHAR(128) NOT NULL,
founded_at DATE,
PRIMARY KEY ( id )
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

可以使用 ALTER TABLE tbl_language ADD INDEX (name); 将 name 字段添加为索引

我们先用 SHOW INDEX FROM tbl_language\G; 查看下 tbl_language 表的索引信息

MariaDB [souyunku]> SHOW INDEX FROM tbl_language\G;
*********************** 1. row ***********************
    Table: tbl_language
Non_unique: 0
 Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
 Sub_part: NULL
   Packed: NULL
     Null: 
Index_type: BTREE
  Comment: 
Index_comment: 
1rows in set (0.00 sec)

然后运行 ALTER TABLE tbl_language ADD INDEX (name); 将 name 字段添加为索引

MariaDB [souyunku]> ALTER TABLE tbl_language ADD INDEX (name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

最后使用 SHOW INDEX FROM tbl_language\G; 查看下 tbl_language 表的索引信息，发现添加了一条 name 的索引

MariaDB [souyunku]> SHOW INDEX FROM tbl_language\G;
*********************** 1. row ***********************
    Table: tbl_language
Non_unique: 0
 Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
 Sub_part: NULL
   Packed: NULL
     Null: 
Index_type: BTREE
  Comment: 
Index_comment: 
*********************** 2. row ***********************
    Table: tbl_language
Non_unique: 1
 Key_name: name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
 Sub_part: NULL
   Packed: NULL
     Null: 
Index_type: BTREE
  Comment: 
Index_comment: 
2rows in set (0.00 sec)

使用 ALTER 命令删除索引

命名ALTER TABLE tablename DROP INDEX index_name 可以删除 tablename 表中的 index_name 索引

例如下面的语句用于删除 tbl_language 表中的 name 索引

ALTER TABLE tbl_language DROP INDEX name;

我们先使用 SHOW INDEX FROM tbl_language\G; 查看下 tbl_language 表的索引信息，发现有一条 name 的索引

MariaDB [souyunku]> SHOW INDEX FROM tbl_language\G;
*********************** 1. row ***********************
    Table: tbl_language
Non_unique: 0
 Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
 Sub_part: NULL
   Packed: NULL
     Null: 
Index_type: BTREE
  Comment: 
Index_comment: 
*********************** 2. row ***********************
    Table: tbl_language
Non_unique: 1
 Key_name: name
Seq_in_index: 1
Column_name: name
Collation: A
Cardinality: 0
 Sub_part: NULL
   Packed: NULL
     Null: 
Index_type: BTREE
  Comment: 
Index_comment: 
2rows in set (0.00 sec)

然后运行 ALTER TABLE tbl_language DROP INDEX name; 将索引 name 删除

MariaDB [souyunku]> ALTER TABLE tbl_language DROP INDEX name;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

最后使用 SHOW INDEX FROM tbl_language\G; 查看下 tbl_language 表的索引信息，发现 name 已经删除了

MariaDB [souyunku]> SHOW INDEX FROM tbl_language\G;
*********************** 1. row ***********************
    Table: tbl_language
Non_unique: 0
 Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 0
 Sub_part: NULL
   Packed: NULL
     Null: 
Index_type: BTREE
  Comment: 
Index_comment: 
1rows in set (0.00 sec)

使用 ALTER 命令添加或删除主键

我们先运行下面的 SQL 语句准备测试用的表

DROP DATABASE IF EXISTS souyunku;

CREATE DATABASE souyunku default character set utf8mb4 collate utf8mb4_unicode_ci;

USEsouyunku;

DROP TABLE IF EXISTS tbl_language;

CREATE TABLE IF NOT EXISTS tbl_language(
id INT UNSIGNED NOT NULL,
name VARCHAR(64) NOT NULL,
url VARCHAR(128) NOT NULL,
founded_at DATE
)ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

使用 ALTER 命令添加主键

ALTER TABLE tablename ADD PRIMARY KEY (fieldname) 命令用于给表 tablename 添加主键

主键只能作用于一个列上，添加主键索引时，你需要确保该主键默认不为空 (NOT NULL)

例如下面的 SQL 语句会把 name 添加为主键

ALTER TABLE tbl_language ADD PRIMARY KEY (name);

我们先用 desc tbl_language; 查看下 tbl_language 表的字段信息

MariaDB [souyunku]> desc tbl_language;
+------------+------------------+------+-----+---------+-------+
|Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
|id         | int(10) unsigned | NO   |     | NULL    |       |
|name       | varchar(64)      | NO   |     | NULL    |       |
|url        | varchar(128)     | NO   |     | NULL    |       |
|founded_at | date             | YES  |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+
4rows in set (0.01 sec)

然后运行 ALTER TABLE tbl_language ADD PRIMARY KEY (name); 添加 name 为主键

MariaDB [souyunku]> ALTER TABLE tbl_language ADD PRIMARY KEY (name);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

最后使用 desc tbl_language; 查看 tbl_language 表的字段信息，发现 name 已经是主键了

MariaDB [souyunku]> desc tbl_language;
+------------+------------------+------+-----+---------+-------+
|Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
|id         | int(10) unsigned | NO   |     | NULL    |       |
|name       | varchar(64)      | NO   | PRI | NULL    |       |
|url        | varchar(128)     | NO   |     | NULL    |       |
|founded_at | date             | YES  |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+
4rows in set (0.02 sec)

使用 ALTER 命令删除主键

ALTER TABLE tablename DROP PRIMARY KEY 可以用来删除表 tablename 的主键

删除主键时只需指定PRIMARY KEY，但在删除索引时，必须知道索引名

例如下面的 SQL 语句用于删除表 tbl_language 的主键

ALTER TABLE tbl_language DROP PRIMARY KEY;

我们先用 desc tbl_language; 查看下 tbl_language 表的主键

MariaDB [souyunku]> desc tbl_language;
+------------+------------------+------+-----+---------+-------+
|Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
|id         | int(10) unsigned | NO   |     | NULL    |       |
|name       | varchar(64)      | NO   | PRI | NULL    |       |
|url        | varchar(128)     | NO   |     | NULL    |       |
|founded_at | date             | YES  |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+
4rows in set (0.02 sec)

可以看到 name 是主键

然后执行 ALTER TABLE tbl_language DROP PRIMARY KEY; 删除主键

MariaDB [souyunku]> ALTER TABLE tbl_language DROP PRIMARY KEY;
Query OK, 0 rows affected (0.02 sec)               
Records: 0  Duplicates: 0  Warnings: 0

最后使用 desc tbl_language; 查看 tbl_language 表的字段信息，发现 name 不是主键了

MariaDB [souyunku]> desc tbl_language;
+------------+------------------+------+-----+---------+-------+
|Field      | Type             | Null | Key | Default | Extra |
+------------+------------------+------+-----+---------+-------+
|id         | int(10) unsigned | NO   |     | NULL    |       |
|name       | varchar(64)      | NO   |     | NULL    |       |
|url        | varchar(128)     | NO   |     | NULL    |       |
|founded_at | date             | YES  |     | NULL    |       |
+------------+------------------+------+-----+---------+-------+
4rows in set (0.01 sec)