1. 首页
  2. mysql教程

32-三十二、MySQL 索引

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

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 添加主键

主