三十二、MySQL 索引
云少三十二、MySQL 索引
MySQL 索引可以大大提高 MySQL 的检索速度
打个比方，如果合理的设计且使用索引的 MySQL 是一辆高速公路的话，那么没有设计和使用索引的 MySQL 就是一个人力山村泥泞路
索引分单列索引和组合索引
1、 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引
2、 组合索引，即一个索引包含多个列
创建索引时，需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录
过多的使用索引将会造成滥用
因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行 INSERT、UPDATE和 DELETE 因为更新表时，MySQL 不仅要保存数据，还要保存一下索引文件
建立索引会占用磁盘空间的索引文件
显示索引信息
SHOW INDEX FROM tablename; 命令可以列出某个表中的相关的索引信息
比如下面的 SQL 命名用于列出 tbl_language 表的索引信息
1
| SHOW INDEX FROM tbl_language;
|
运行结果如下
1
2
3
4
5
6
7
| MariaDB [test2]> 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 | | |
+
1 row in set (0.00 sec)
|
不太直观，我们加上 \G 格式化一下
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
| MariaDB [test2]> 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:
1 row in set (0.00 sec)
ERROR: No query specified
|
普通索引
普通索引是最基本的索引，它没有任何限制
普通索引有以下几种创建方式
1、 创建索引
1
| CREATE INDEX indexName ON mytable(username(length));
|
- 如果是 CHAR，VARCHAR 类型，length 可以小于字段实际长度
- 如果是 BLOB 和 TEXT 类型，必须指定 length
2、 修改表结构(添加索引)
1
| ALTER table tableName ADD INDEX indexName(columnName)
|
3、 创建表的时候直接指定
1
2
3
4
5
6
7
8
| 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;
|
删除索引的语法
1
| DROP INDEX [indexName] ON tablename;
|
唯一索引
唯一索引 与普通索引类似，不同的就是：唯一索引的索引列的值必须唯一，但允许有空值
如果是组合索引，则列值的组合必须唯一
唯一索引有有以下几种创建方式
1、 创建索引
1
| CREATE UNIQUE INDEX indexName ON mytable(username(length))
|
2、 修改表结构
1
| ALTER table mytable ADD UNIQUE [indexName] (username(length))
|
3、 创建表的时候直接指定
1
2
3
4
5
6
7
8
| 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、 ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);
1
| 添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
|
2、 ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);
1
| 创建索引的值必须是唯一的 ( 除了 NULL 外，NULL 可能会出现多次)
|
3、 ALTER TABLE tbl_name ADD INDEX index_name (column_list);
4、 ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);
准备测试数据
运行下面的 SQL 语句在 test2 数据库中准备测试数据
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
| DROP DATABASE IF EXISTS `test2`;
CREATE DATABASE test2 default character set utf8mb4 collate utf8mb4_unicode_ci;
USE test2;
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 表的索引信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| MariaDB [test2]> 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:
1 rows in set (0.00 sec)
|
然后运行 ALTER TABLE tbl_language ADD INDEX (name); 将 name 字段添加为索引
1
2
3
| MariaDB [test2]> 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 的索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
| MariaDB [test2]> 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:
2 rows in set (0.00 sec)
|
使用 ALTER 命令删除索引
命名 ALTER TABLE tablename DROP INDEX index_name 可以删除 tablename 表中的 index_name 索引
例如下面的语句用于删除 tbl_language 表中的 name 索引
1
| ALTER TABLE tbl_language DROP INDEX name;
|
我们先使用 SHOW INDEX FROM tbl_language\G; 查看下 tbl_language 表的索引信息，发现有一条 name 的索引
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
| MariaDB [test2]> 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:
2 rows in set (0.00 sec)
|
然后运行 ALTER TABLE tbl_language DROP INDEX name; 将索引 name 删除
1
2
3
| MariaDB [test2]> 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 已经删除了
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
| MariaDB [test2]> 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:
1 rows in set (0.00 sec)
|
使用 ALTER 命令添加或删除主键
我们先运行下面的 SQL 语句准备测试用的表
1
2
3
4
5
6
7
8
9
10
11
12
13
14
| DROP DATABASE IF EXISTS `test2`;
CREATE DATABASE test2 default character set utf8mb4 collate utf8mb4_unicode_ci;
USE test2;
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 添加为主键
1
| ALTER TABLE tbl_language ADD PRIMARY KEY (name);
|
我们先用 desc tbl_language; 查看下 tbl_language 表的字段信息
1
2
3
4
5
6
7
8
9
10
| MariaDB [test2]> 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 | |
+
4 rows in set (0.01 sec)
|
然后运行 ALTER TABLE tbl_language ADD PRIMARY KEY (name); 添加 name 为主键
1
2
3
| MariaDB [test2]> 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 已经是主键了
1
2
3
4
5
6
7
8
9
10
| MariaDB [test2]> 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 | |
+
4 rows in set (0.02 sec)
|
使用 ALTER 命令删除主键
ALTER TABLE tablename DROP PRIMARY KEY 可以用来删除表 tablename 的主键
删除主键时只需指定PRIMARY KEY，但在删除索引时，必须知道索引名
例如下面的 SQL 语句用于删除表 tbl_language 的主键
1
| ALTER TABLE tbl_language DROP PRIMARY KEY;
|
我们先用 desc tbl_language; 查看下 tbl_language 表的主键
1
2
3
4
5
6
7
8
9
10
| MariaDB [test2]> 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 | |
+
4 rows in set (0.02 sec)
|
可以看到 name 是主键
然后执行 ALTER TABLE tbl_language DROP PRIMARY KEY; 删除主键
1
2
3
| MariaDB [test2]> 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 不是主键了
1
2
3
4
5
6
7
8
9
10
| MariaDB [test2]> 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 | |
+
4 rows in set (0.01 sec)
|
