1. 首页
  2. mysql教程

32-三十二、MySQL 索引

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

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

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

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

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

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

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

因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行 INSERTUPDATEDELETE 因为更新表时,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

« 33-三十三、CREATE TEMPORARY TABLE 创建临时表
31-三十一、MySQL ALTER 命令»

相关推荐

QR code