1. 首页
  2. mysql教程

32-三十二、MySQL 索引

• 2019年12月29日 16:24 • mysql教程 • 阅读 1046

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 [ycbbs]> 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      |         |               |
+--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
 row in set (0.00 sec)

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


MariaDB [ycbbs]> 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: 
 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 语句在 ycbbs 数据库中准备测试数据


DROP DATABASE IF EXISTS `ycbbs`;
CREATE DATABASE ycbbs default character set utf8mb4 collate utf8mb4_unicode_ci;
USE ycbbs;
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 [ycbbs]> 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: 
 rows in set (0.00 sec)

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


MariaDB [ycbbs]> 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 [ycbbs]> 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: 
 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 [ycbbs]> 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: 
 rows in set (0.00 sec)

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


MariaDB [ycbbs]> 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 [ycbbs]> 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: 
 rows in set (0.00 sec)

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

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


DROP DATABASE IF EXISTS `ycbbs`;
CREATE DATABASE ycbbs default character set utf8mb4 collate utf8mb4_unicode_ci;
USE ycbbs;
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 [ycbbs]> 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    |       |
+------------+------------------+------+-----+---------+-------+
 rows in set (0.01 sec)

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


MariaDB [ycbbs]> 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 [ycbbs]> 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    |       |
+------------+------------------+------+-----+---------+-------+
 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 [ycbbs]> 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    |       |
+------------+------------------+------+-----+---------+-------+
 rows in set (0.02 sec)

可以看到 name 是主键

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


MariaDB [ycbbs]> 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 [ycbbs]> 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    |       |
+------------+------------------+------+-----+---------+-------+
 rows in set (0.01 sec)

希望读者能够给小编留言，也可以点击[此处扫下面二维码关注微信公众号](https://www.ycbbs.vip/?p=28 "此处扫下面二维码关注微信公众号")

看完两件小事

如果你觉得这篇文章对你挺有启发，我想请你帮我两个小忙：

  1. 关注我们的 GitHub 博客，让我们成为长期关系
  2. 把这篇文章分享给你的朋友 / 交流群，让更多的人看到，一起进步，一起成长！
  3. 关注公众号 「方志朋」，公众号后台回复「666」 免费领取我精心整理的进阶资源教程
    4. JS中文网,Javascriptc中文网是中国领先的新一代开发者社区和专业的技术媒体,一个帮助开发者成长的社区，是给开发者用的 Hacker News，技术文章由为你筛选出最优质的干货，其中包括：Android、iOS、前端、后端等方面的内容。目前已经覆盖和服务了超过 300 万开发者，你每天都可以在这里找到技术世界的头条内容。

    本文著作权归作者所有，如若转载，请注明出处

    转载请注明：文章转载自「 Java极客技术学习 」https://www.javajike.com

    标题：32-三十二、MySQL 索引

    链接：https://www.javajike.com/article/1595.html

赞 (0)
打赏 微信扫一扫 微信扫一扫
0 0
« 33-三十三、CREATE TEMPORARY TABLE 创建临时表
31-三十一、MySQL ALTER 命令»

相关推荐

QR code