三十二、MySQL 索引

文章永久连接：https://tech.souyunku.com/?p=4921

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      |         |               |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row 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: 
1 row 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、 ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);

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

2、 ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);

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

3、 ALTER TABLE tbl_name ADD INDEX index_name (column_list);

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

4、 ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);

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

准备测试数据

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

DROP DATABASE IF EXISTS `souyunku`;

CREATE DATABASE souyunku default character set utf8mb4 collate utf8mb4_unicode_ci;

USE souyunku;

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: 
1 rows 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: 
2 rows 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: 
2 rows 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: 
1 rows in set (0.00 sec)

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

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

DROP DATABASE IF EXISTS `souyunku`;

CREATE DATABASE souyunku default character set utf8mb4 collate utf8mb4_unicode_ci;

USE souyunku;

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    |       |
+------------+------------------+------+-----+---------+-------+
4 rows 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    |       |
+------------+------------------+------+-----+---------+-------+
4 rows 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    |       |
+------------+------------------+------+-----+---------+-------+
4 rows 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    |       |
+------------+------------------+------+-----+---------+-------+
4 rows in set (0.01 sec)

干货推荐

本站推荐：精选优质专栏

附录：MySQL 教程：系列文章

• 一、MySQLMariaDB 基础教程
• 二、MySQL 简介
• 三、MySQL MariaDB 安装
• 四、MySQL 管理
• 五、MySQL 日常管理
• 六、MySQL PHP 语法
• 七、MySQL 创建连接
• 八、MySQL 获取数据库列表
• 九、MySQL 创建数据库
• 十、MySQL 删除数据库
• 十一、MySQL 选择数据库
• 十二、MySQL 数据类型
• 十三、MySQL 列出数据表
• 十四、MySQL 创建数据表
• 十五、MySQL 删除表
• 十六、MySQL 插入数据
• 十七、MySQL 获取插入数据的 ID
• 十八、MySQL SELECT FROM 查询数据
• 十九、MySQL WHERE 子句有条件的查询数据
• 二十、MySQL UPDATE 更新数据
• 二十一、MySQL DELETE FROM 语句删除数据
• 二十二、MySQL 返回删改查受影响的行数
• 二十三、MySQL LIKE 子句模糊查询数据
• 二十四、MySQL UNION 操作符查询多张表
• 二十五、MySQL ORDER BY 排序
• 二十六、MySQL GROUP BY 分组查询数据
• 二十七、MySQL JOIN 进行多表查询
• 二十八、MySQL NULL 值处理
• 二十九、MySQL REGEXP 子句正则表达式查询
• 三十、MySQL 数据库事务
• 三十一、MySQL ALTER 命令
• 【当前读到】三十二、MySQL 索引
• 三十三、CREATE TEMPORARY TABLE 创建临时表
• 三十四、MySQL DROP TABLE 删除临时表
• 三十五、MySQL INSERT INTO SELECT 复制表
• 三十六、MySQL 获取服务器元数据
• 三十七、MySQL 自增序列 AUTO_INCREMENT
• 三十八、MySQL 处理重复数据
• 三十九、MySQL 安全及防止 SQL 注入攻击
• 四十、MySQL 导出数据
• 四十一、MySQL 导入数据