MySQL 的索引

最新推荐文章于 2022-05-02 21:49:30 发布

*jpfly*

最新推荐文章于 2022-05-02 21:49:30 发布

阅读量157

点赞数

分类专栏：数据库 MySQL

本文链接：https://blog.csdn.net/jiangpengfei_love/article/details/108395029

版权

数据库同时被 2 个专栏收录

1 篇文章 0 订阅

订阅专栏

MySQL

1 篇文章 0 订阅

订阅专栏

1、MySQL 索引是什么？

在使用词典的时候，如果没有目录，那么查询一个字将会是多么的困难，词典（书）的目录就是索引。

MySQL 的索引用于快速找出在某个列中有一特定值的行，如果不使用索引，MySQL 必须从第一条记录开始一条一条的读数据，直到匹配到需要查询的数据，表越大，查询数据耗费的时间越多。如果查询的列有一个索引，MySQL 能快速到达某个位置去搜寻数据，而不必查看所有数据。

例如：有一张 User 表，有一个 u_phone 的字段，这个表里存了2W数据，现在要查询 u_phone=188xxxxxxxx 的数据，如果没有索引将会从第一条数据开始匹配，一直到匹配到数据，如果这一条数据靠后，这个过程将会非常耗时；如果有了索引，把 u_phone 字段以某种数据结构存储，能够快速查到 u_phone 指定的值，那么就不需要一条一条数据进行匹配。

索引是一个单独的、存储在磁盘上的数据库结构，它们包含着对数据表里所有记录的引用指针。使用索引用于快速找出在某个或多个列中有一特定值的行，所有 MySQL 列类型都可以被索引，对相关列使用索引是提高查询操作速度的最佳途径。

索引是在存储引擎中实现的，每种存储引擎的所以都不一定完全相同，并且每一种存储引擎也不一定支持所有的索引类型。根据存储引擎定义每个表的作答索引数和最大索引长度，所有存储引擎支持每个表至少16个索引，总索引长度至少为256字节，大多数存储引擎有更高的限制。MySQL 中索引的数据结构有两种：BTREE 和 HASH，具体和表的存储引擎相关；MyISAM 和 InnoDB 存储引擎只支持 BTREE 索引；MEMORY/HEAP 存储引擎可以支持 HASH 和 BTREE 索引。

2、索引的优缺点

2.1、索引的优点

通过创建唯一索引，可以保证数据库表中每一行数据的唯一性。
可以大大加快数据的查询速度，这也是创建索引的主要原因。
在实现数据的参考完整性方面，可以加快表和表之间的连接。
在使用分组和排序子句进行数据查询时，也可以显著减少查询中分组和排序的时间。

2.2、索引的缺点

创建索引和维护索引要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
索引需要占用磁盘空间，除了数据表需要占用数据空间之外，每一个索引还要占用一定的屋里空间，如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
当对数据表进行 insert，update，delete操作会动态更新索引文件，这样就降低了数据的维护速度。

3、索引的种类

3.1、单列索引

首先，单列索引只包含单个列，一个表可以有多个单列索引。

普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
唯一索引：索引列中的值必须是唯一的，但是允许为空值，
主键索引：是一种特殊的唯一索引，不允许有空值。

3.2、组合索引

组合索引：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

3.3、全文索引

全文索引：只有在 MyISAM 引擎上才能使用，只能在 CHAR,VARCHAR,TEXT 类型字段上使用全文索引，
就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"我们都是中国人 …" 通过"我们"，可能就可以找到该条记录。
全文索引类型为 FULLTEXT ，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。

3.4、空间索引

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。空间索引只能在 MyISAM 存储引擎上使用。

4、索引的创建

4.1、在创建表的语句 CREATE TABLE 中指定索引列

使用 CREATE TABLE 创建表的时候，除了可以定义列的数据类型，还可以定义主键约束，外键约束或者唯一性约束，而不论创建哪种约束，在定义约束的同时相当于在指定列上创建了一个索引。
语法格式：

CREATE TABLE table_name [col_name data_type] 
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] 
(col_name [length]) [ASC | DESC]

UNIQUE、FULLTEXT、SPATIAL 为可选参数，分别表示唯一索引、全文索引、空间索引；
INDEX 和 KEY 为同义词，两者作用相同，用来指定创建索引；
col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
index_name 指定索引的名称，为可选参数，如果不指定，MySQL 默认col_name 为索引名称；
length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
ASC 和 DESC 指定升序或者降序的索引值存储。

4.1.1、创建普通索引

最基本的数据类型，没有唯一性之类的限制，其作用只是加快对数据的访问速度。

在book表中的year_publication字段上建立普通索引，SQL语句如下：

CREATE TABLE book
(
bookid            	INT NOT NULL,
bookname          	VARCHAR(255) NOT NULL,
authors            	VARCHAR(255) NOT NULL,
info               	VARCHAR(255) NULL,
comment           	VARCHAR(255) NULL,
year_publication   	YEAR NOT NULL,
INDEX(year_publication)
);

该语句执行完毕之后，使用SHOW CREATE TABLE查看表结构：
在这里插入图片描述
由结果可以看到，book表的year_publication字段上成功建立索引，其索引名称year_publication为MySQL自动添加。使用EXPLAIN语句查看索引是否正在使用：

mysql> explain select * from book where year_publication=1990 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: book
   partitions: NULL
         type: ref
possible_keys: year_publication
          key: year_publication
      key_len: 1
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

mysql>

可以看到，possible_keys 和 keys 的值都为 year_publication，查询时使用了索引。

4.1.2、创建唯一索引

创建唯一索引的主要原因是减少查询索引列操作的执行时间，尤其是对比较庞大的数据表。它与前面的普通索引类似，不同的是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

创建一个表t1，在表中的id字段上使用UNIQUE关键字创建唯一索引。

CREATE TABLE t1
(
id    INT NOT NULL,
name CHAR(30) NOT NULL,
UNIQUE INDEX UniqIdx(id)
);

该语句执行完毕之后，使用SHOW CREATE TABLE查看表结构：

mysql> SHOW CREATE table t1 \G
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  UNIQUE KEY `UniqIdx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql>

由结果可以看到，id字段上已经成功建立了一个名为UniqIdx的唯一索引。

4.1.3、创建单列索引

单列索引是在数据表中的某一个字段上创建的索引，一个表中可以创建多个单列索引，前面创建的普通索引和唯一索引都是单列索引。

创建一个表t2，在表中的name字段上创建单列索引。

CREATE TABLE t2
(
id   INT NOT NULL,
name CHAR(50) NULL,
INDEX SingleIdx(name(20))
);

该语句执行完毕之后，使用SHOW CREATE TABLE查看表结构：

mysql> SHOW CREATE table t2 \G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` char(50) DEFAULT NULL,
  KEY `SingleIdx` (`name`(20))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql>

由结果可以看到，id字段上已经成功建立了一个名为SingleIdx的单列索引，索引长度为20。

4.1.4、创建组合索引

组合索引是在多个字段上创建一个索引。

创建表t3，在表中的id、name和age字段上建立组合索引，SQL语句如下：

CREATE TABLE t3
(
id    INT NOT NULL,
name CHAR(30) NOT NULL,
age  INT NOT NULL,
info VARCHAR(255),
INDEX MultiIdx(id, name, age)
);

该语句执行完毕之后，使用SHOW CREATE TABLE查看表结构：

mysql> SHOW CREATE table t3 \G
*************************** 1. row ***************************
       Table: t3
Create Table: CREATE TABLE `t3` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  `age` int(11) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  KEY `MultiIdx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql>

由结果可以看到，id、name和age字段上已经成功建立了一个名为MultiIdx的组合索引。
组合索引可起几个索引的作用，但是使用时并不是随便查询哪个字段都可以使用索引，而是遵从“最左前缀”：利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。例如这里由id、name和age 3个字段构成的索引，索引行中按id/name/age的顺序存放，索引可以搜索下面字段组合：（id, name, age）、（id, name）或者id。如果列不构成索引最左面的前缀，MySQL不能使用局部索引，如（age）或者（name,age）组合则不能使用索引查询。

在t3表中，查询id和name字段，使用EXPLAIN语句查看索引的使用情况：

mysql> explain select * from t3 where id=1 AND name='joe' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: NULL
         type: ref
possible_keys: MultiIdx
          key: MultiIdx
      key_len: 124
          ref: const,const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql>

可以看到，查询id和name字段时，使用了名称MultiIdx的索引，如果查询（name,age）组合或者单独查询name和age字段，结果如下：

mysql> explain select * from t3 where age=3 \G         
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

mysql>

此时，possible_keys和key值为NULL，并没有使用在t3表中创建的索引进行查询。
需要注意的是，如果使用id，age组合也是可以使用索引的：

mysql> explain select * from t3 where id=1 AND age=3 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t3
   partitions: NULL
         type: ref
possible_keys: MultiIdx
          key: MultiIdx
      key_len: 4
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.00 sec)

mysql>

4.1.5、创建全文索引

FULLTEXT （全文索引）可以用于全文搜索，只有 MyISAM 存储引擎支持 FULLTEXT 索引，并且只为 CHAR，VARCHAR 和 TEXT 数据类型的列创建索引。索引总是对整个列进行，不支持局部（前缀）索引。

创建表t4，在表中的info字段上建立全文索引，SQL语句如下：

CREATE TABLE t4
(
id    INT NOT NULL,
name CHAR(30) NOT NULL,
age  INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX FullTxtIdx(info)
) ENGINE=MyISAM;

因为MySQL5.6中默认存储引擎为InnoDB，在这里创建表时需要修改表的存储引擎为MyISAM，不然创建索引会出错。
语句执行完毕之后，使用SHOW CREATE TABLE查看表结构：


mysql> SHOW CREATE table t4 \G
*************************** 1. row ***************************
       Table: t4
Create Table: CREATE TABLE `t4` (
  `id` int(11) NOT NULL,
  `name` char(30) NOT NULL,
  `age` int(11) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  FULLTEXT KEY `FullTxtIdx` (`info`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql>

由结果可以看到，info字段上已经成功建立了一个名为FullTxtIdx的FULLTEXT索引。全文索引非常适合于大型数据集，对于小的数据集，它的用处可能比较小。

4.1.6、创建空间索引

空间索引必须在MyISAM类型的表中创建，且空间类型的字段必须为非空。

创建表t5，在空间类型为GEOMETRY的字段上创建空间索引，SQL语句如下：

CREATE TABLE t5 
( g GEOMETRY NOT NULL, 
SPATIAL INDEX spatIdx(g) 
)ENGINE=MyISAM;

该语句执行完毕之后，使用SHOW CREATE TABLE查看表结构：

mysql> SHOW CREATE table t5 \G
*************************** 1. row ***************************
       Table: t5
Create Table: CREATE TABLE `t5` (
  `g` geometry NOT NULL,
  SPATIAL KEY `spatIdx` (`g`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql>

4.2、使用 ALTER TABLE 语句在存在的表上创建索引

基本语法：

ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] 
[INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]

ADD 关键字表示向表中添加索引。

4.2.1、创建普通索引

在book表中的 bookname 字段上建立名为BkNameIdx的普通索引，添加索引之前，使用SHOW INDEX语句查看指定表中创建的索引：

mysql> SHOW INDEX FROM book \G
*************************** 1. row ***************************
        Table: book
   Non_unique: 1
     Key_name: year_publication
 Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

mysql>

主要字段含义：

Table：表示创建索引的表。
Non_unique：表示索引非唯一，1代表非唯一索引，0代表唯一索引。
Key_name：索引的名称。
Seq_in_index：表示该字段在索引中的位置，单列索引该值为1，组合索引- 为每个字段在索引中定义的顺序。
Column_name：定义索引的列字段。
Collation：
Cardinality：
Sub_part：表示索引的长度。
Packed：
Null：表示该字段是否能为空值。
Index_type：索引类型。
Comment：
Index_comment：

可以看到，book 表中已经存在一个索引，前面定义的 year_publication 索引。
下面使用 ALTER TABLE 在 bookname 字段上添加索引：

ALTER TABLE book ADD INDEX BkNameIdx( bookname(30) );

使用SHOW INDEX语句查看表中的索引：

mysql>  SHOW INDEX FROM book \G
*************************** 1. row ***************************
        Table: book
   Non_unique: 1
     Key_name: year_publication
 Seq_in_index: 1
  Column_name: year_publication
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: book
   Non_unique: 1
     Key_name: BkNameIdx
 Seq_in_index: 1
  Column_name: bookname
    Collation: A
  Cardinality: 0
     Sub_part: 30
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
2 rows in set (0.00 sec)

mysql>

4.2.2、创建唯一索引

在book表的bookId字段上建立名称为UniqidIdx 的唯一索引，SQL语句如下：

ALTER TABLE book ADD UNIQUE INDEX UniqidIdx ( bookId );

4.2.3、创建单列索引

在book表的comment字段上建立单列索引，SQL语句如下：

ALTER TABLE book ADD INDEX BkcmtIdx ( comment(50) );

4.2.4、创建组合索引

在book表的authors和info字段上建立组合索引，SQL语句如下：

ALTER TABLE book ADD INDEX BkAuAndInfoIdx ( authors(20),info(50) );

使用SHOW INDEX语句查看表中的索引：

mysql> SHOW INDEX FROM book 
    -> ;
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name         | Seq_in_index | Column_name      | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book  |          0 | UniqidIdx        |            1 | bookid           | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | year_publication |            1 | year_publication | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| book  |          1 | BkNameIdx        |            1 | bookname         | A         |           0 |       30 | NULL   |      | BTREE      |         |               |
| book  |          1 | BkcmtIdx         |            1 | comment          | A         |           0 |       50 | NULL   | YES  | BTREE      |         |               |
| book  |          1 | BkAuAndInfoIdx   |            1 | authors          | A         |           0 |       20 | NULL   |      | BTREE      |         |               |
| book  |          1 | BkAuAndInfoIdx   |            2 | info             | A         |           0 |       50 | NULL   | YES  | BTREE      |         |               |
+-------+------------+------------------+--------------+------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
6 rows in set (0.00 sec)

mysql>

4.2.5、创建全文索引

创建表t6，在t6表上使用ALTER TABLE创建全文索引，
首先创建表t6，语句如下：

CREATE TABLE t6
(
id    INT NOT NULL,
info  CHAR(255)
) ENGINE=MyISAM;

注意修改ENGINE参数为MyISAM，MySQL默认引擎InnoDB不支持全文索引。
使用ALTER TABLE语句在info字段上创建全文索引：

ALTER TABLE t6 ADD FULLTEXT INDEX infoFTIdx ( info );

使用SHOW INDEX语句查看索引：

mysql>  SHOW index from t6 \G
*************************** 1. row ***************************
        Table: t6
   Non_unique: 1
     Key_name: infoFTIdx
 Seq_in_index: 1
  Column_name: info
    Collation: NULL
  Cardinality: NULL
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: FULLTEXT
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

mysql>

4.2.6、创建空间索引

创建表t7，在t7的空间数据类型字段g上创建名称为spatIdx的空间索引，注意引擎为 MyISAM。

CREATE TABLE t7 ( g GEOMETRY NOT NULL )ENGINE=MyISAM;

使用ALTER TABLE在表t7的g字段建立空间索引：

ALTER TABLE t7 ADD SPATIAL INDEX spatIdx(g);

使用SHOW INDEX语句查看索引：

mysql> SHOW index from t7 \G
*************************** 1. row ***************************
        Table: t7
   Non_unique: 1
     Key_name: spatIdx
 Seq_in_index: 1
  Column_name: g
    Collation: A
  Cardinality: NULL
     Sub_part: 32
       Packed: NULL
         Null: 
   Index_type: SPATIAL
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

mysql>

4.3、使用 CREATE INDEX 语句在已存在的表上添加索引

CREATE INDEX 语句可以在已存在的表上添加索引，MySQL 中 CREATE INDEX 被映射到一个 ALTER TABLE 语句上，语法结构：

CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name [length],...) [ASC | DESC]

CREATE INDEX 和 ALTER TABLE 语句的语法基本一样，只是关键字不同。
先创建一个没有索引的 book1 表：

CREATE TABLE book1
(
bookid          	INT NOT NULL,
bookname        	VARCHAR(255) NOT NULL,
authors           	VARCHAR(255) NOT NULL,
info              	VARCHAR(255) NULL,
comment          	VARCHAR(255) NULL,
year_publication   	YEAR NOT NULL
);

4.3.1、创建普通索引

在 book1 表中的 bookname 字段上建立名为BkNameIdx的普通索引，SQL语句如下：

CREATE INDEX BkNameIdx ON book1(bookname);

4.3.2、创建唯一索引

在 book1 表的 bookId 字段上建立名称为 UniqidIdx 的唯一索引，SQL语句如下：

CREATE UNIQUE INDEX UniqidIdx  ON book1( bookId );

4.3.3、创建单列索引

在book表的comment字段上建立单列索引，SQL语句如下：

CREATE INDEX BkcmtIdx ON book1(comment(50) );

4.3.4、创建组合索引

在book表的authors和info字段上建立组合索引，SQL语句如下：

CREATE INDEX BkAuAndInfoIdx ON book1( authors(20),info(50) );

查询一下创建的索引：

mysql> SHOW index from book1
    -> ;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| book1 |          0 | UniqidIdx      |            1 | bookid      | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| book1 |          1 | BkNameIdx      |            1 | bookname    | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| book1 |          1 | BkcmtIdx       |            1 | comment     | A         |           0 |       50 | NULL   | YES  | BTREE      |         |               |
| book1 |          1 | BkAuAndInfoIdx |            1 | authors     | A         |           0 |       20 | NULL   |      | BTREE      |         |               |
| book1 |          1 | BkAuAndInfoIdx |            2 | info        | A         |           0 |       50 | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.00 sec)

mysql>

4.3.5、创建全文索引

删除表 t6，重新建立表 t6，在 t6 表中使用 CREATE INDEX 语句，在 CHAR 类型的 info 字段上创建全文索引；
删除 t6 表：

drop table t6;

创建 t6 表：

 CREATE TABLE t6
     (
     id    INT NOT NULL,
     info  CHAR(255)
     ) ENGINE=MyISAM;

使用 CREATE INDEX 在 t6 表的 info 字段上创建名称为 infoFTIdx 的全文索引：

CREATE FULLTEXT INDEX infoFTIdx ON t6(info);

4.3.6、创建空间索引

删除表t7，重新创建表t7，在t7表中使用CREATE INDEX语句，在空间数据类型字段g上创建名称为spatIdx的空间索引；

首先删除表t7，并重新建立该表：

mysql> drop table t7;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t7 ( g GEOMETRY NOT NULL )ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql>

使用CREATE INDEX语句在表t7的g字段建立空间索引：

CREATE SPATIAL INDEX spatIdx ON t7 (g);

语句执行完毕之后，将在t7表中创建名称为spatIdx的空间索引，该索引在g字段上创建。

5、索引的删除

5.1、用ALTER TABLE删除索引

语法格式：

ALTER TABLE table_name DROP INDEX index_name;

删除book表中的名称为UniqidIdx的唯一索引，首先查看book表中是否有名称为UniqidIdx的索引：

mysql> SHOW CREATE table book \G
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  UNIQUE KEY `UniqidIdx` (`bookid`),
  KEY `year_publication` (`year_publication`),
  KEY `BkNameIdx` (`bookname`(30)),
  KEY `BkcmtIdx` (`comment`(50)),
  KEY `BkAuAndInfoIdx` (`authors`(20),`info`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql>

查询结果可以看到，book表中有名称为UniqidIdx的唯一索引，该索引在bookid字段上创建，下面删除该索引，输入删除语句如下：

ALTER TABLE book DROP INDEX UniqidIdx;

语句执行完毕，使用SHOW语句查看索引是否被删除：

mysql> SHOW CREATE table book \G
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `year_publication` (`year_publication`),
  KEY `BkNameIdx` (`bookname`(30)),
  KEY `BkcmtIdx` (`comment`(50)),
  KEY `BkAuAndInfoIdx` (`authors`(20),`info`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql>

由结果可以看到，book表中已经没有名称为uniqidIdx的唯一索引，删除索引成功。
添加AUTO_INCREMENT约束字段的唯一索引不能被删除。

5.2、使用DROP INDEX语句删除索引

语法格式：

DROP INDEX index_name ON table_name;

删除book表中名称为BkAuAndInfoIdx的组合索引，SQL语句如下：

mysql> DROP INDEX BkAuAndInfoIdx ON book;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SHOW CREATE table book \G
*************************** 1. row ***************************
       Table: book
Create Table: CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `year_publication` (`year_publication`),
  KEY `BkNameIdx` (`bookname`(30)),
  KEY `BkcmtIdx` (`comment`(50))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)

mysql>

可以看到，book表中已经没有名称为BkAuAndInfoIdx的组合索引，删除索引成功。
删除表中的列时，如果要删除的列为索引的组成部分，则该列也会从索引中删除。如果组成索引的所有列都被删除，则整个索引将被删除。

6、索引的设计原则

索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好的性能非常重要，设计索引时，应该考虑以下原则：

索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，而且会影响 INSERT、DELETE、UPDATE 等语句的性能，因为当表中的数据更改时，索引也会进行调整和更新。
避免对经常更新的表进行过多的索引，并且索引中的列尽可能的少；而对经常用于查询的字段应该创建索引，但要避免添加不必要的字段。
数据量小的表最好不要是用索引，由于数据量少查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。
在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上不要建立索引。比如在学生表的"性别"字段上只有"男"、"女"两个不同值，因此就无需建立索引，如果建立索引不但不会提高查询效率，反而会严重降低数据更新速度。
当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定义的列的数据完整性，以提高查询速度。
在频繁进行排序或者分组（即进行 group by 或 order by 操作）的列上建立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

*jpfly*

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL 的索引

1、MySQL 索引是什么？在使用词典的时候，如果没有目录，那么查询一个字将会是多么的困难，词典（书）的目录就是索引。MySQL 的索引用于快速找出在某个列中有一特定值的行，如果不使用索引，MySQL 必须从第一条记录开始一条一条的读数据，直到匹配到需要查询的数据，表越大，查询数据耗费的时间越多。如果查询的列有一个索引，MySQL 能快速到达某个位置去搜寻数据，而不必查看所有数据。例如：有一张 User 表，有一个 u_phone 的字段，这个表里存了2W数据，现在要查询 u_phone=188xxx
复制链接

扫一扫

专栏目录