MySQL（三）索引的操作

朱雀 2023-07-08 02:38 130阅读 0赞

### 介绍 ###

数据库对象索引的出现，除了可以提高数据库管理系统的查找速度，而且还可以保证字段的唯一性，从而实现数据库表的完整性。MySQL支持6种索引，分别为：普通索引、唯一索引、全文索引、单列索引、多列索引和空间索引。

索引的创建有利有弊，创建索引可以提高查询速度，但过多的创建索引则会占据许多的磁盘空间。

以下情况适合创建索引：

*  经常被查询的字段，即在WHERE字句中出现的字段
 *  在分组的字段，即在GROUP BY字句中出现的字段
 *  存在依赖关系的字表和父表之间的联合查询，即主键或外键字段
 *  设置唯一完整性约束的字段

以下情况不适合约束的字段：

*  在查询中很少被使用的字段
 *  拥有许多重复值的字段

## 一、创建和查看普通索引 ##

1、方法一：普通索引就是在创建索引时，不附加任何限制条件（唯一、非空等限制）。该类型的索引可以创建在任何数据类型的字段上

MariaDB [company]> CREATE TABLE t_dept(deptno INT,dname VARCHAR(20),loc VARCHAR(40),INDEX index_deptno(deptno));

查看

![20200224161425510.png][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70][]

![20200225102319987.png][]

2、方法二：在已经存在的表上创建普通索引

查看

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70 1][]

3、方法三：通过SQL语句ALTER TABLE创建普通索引

#原始表
    MariaDB [company]> CREATE TABLE t_dept(deptno INT,dname VARCHAR(20),loc VARCHAR(40));
    
    #在原始表上通过alter table 创建普通索引
    MariaDB [company]> ALTER TABLE t_dept ADD INDEX index_deptno(deptno);

查看

![20200224165711205.png][]

## 二、创建和查看唯一索引 ##

所谓唯一索引，就是在创建索引时，限制索引的值必须是唯一的。通过该类型的索引可以更快速地查询某条记录。在MySQL中，根据创建索引方式，可以分为自动索引和手动索引两种。

自动索引是指数据库表里设置完整性约束时，该表会被系统自动创建索引。手动索引是指手动在表上创建索引，当设置表中的某个字段设置主键或唯一完整性约束时，系统就会自动创建关联该字段的唯一索引。

1、方法一：创建表时创建唯一索引

MariaDB [company]> CREATE TABLE t_dept(deptno INT UNIQUE,dname VARCHAR(20),loc VARCHAR(40),UNIQUE INDEX index_deptno(deptno));

查看执行结果显示，已经在数据库表t\_dept上创建了一个名为index\_deptno唯一索引，其关联的字段为deptno

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70 2][]

2、方法二：在已经存在的表上创建唯一索引

#创建原始表
    MariaDB [company]> CREATE TABLE t_dept(deptno INT,dname VARCHAR(20),loc VARCHAR(40));
    
    #在原始表上通过alter table 创建唯一索引
    MariaDB [company]> CREATE UNIQUE INDEX index_deptno ON t_dept (deptno);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70 3][]

3、方法三：通过SQL语句ALTER TABLE创建唯一索引

#创建原始表
    MariaDB [company]> CREATE TABLE t_dept(deptno INT,dname VARCHAR(20),loc VARCHAR(40));
    
    #在原始表上通过alter table 创建唯一索引
    MariaDB [company]> ALTER TABLE t_dept ADD UNIQUE INDEX index_deptno(deptno);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70 4][]

## 三、创建和查看全文索引 ##

全文索引主要关联在数据类型为CHAR、VARCHAR和TEXT的字段上，以便能够更加快速地查询数据量较大的字符串类型的字段。MySQL从3.23.23版本开始支持全文索引，只能在存储引擎为MyISAM的数据库表上创建全文索引。在默认的情况下，全文索引的搜索执行方式为不区分大小写，如果全文索引所关联的字段为二进制数据类型，则以区分大小写的搜索方式执行。

1、方法一：创建表时创建全文索引

MariaDB [company]> CREATE TABLE t_dept(deptno INT,dname VARCHAR(20),loc VARCHAR(40),FULLTEXT INDEX index_loc(loc))ENGINE=MyISAM;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70 5][]

2、方法二：在已经存在的表上创建全文索引

#创建原始表
    MariaDB [company]> CREATE TABLE t_dept(deptno INT,dname VARCHAR(20),loc VARCHAR(40))ENGINE=MyISAM;
    
    MariaDB [company]> CREATE FULLTEXT INDEX index_loc ON t_dept (loc);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70 6][]

3、方法三：通过SQL语句ALTER TABLE创建全文索引

#创建原始表
    MariaDB [company]> CREATE TABLE t_dept(deptno INT,dname VARCHAR(20),loc VARCHAR(40))ENGINE=MyISAM;
    
    MariaDB [company]> ALTER TABLE t_dept ADD FULLTEXT INDEX index_loc(loc);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70 7][]

## 四、创建和查看多列索引 ##

所谓多列索引，是指在创建索引时，所关联的字段不是一个字段，而是多个字段。虽然可以通过所关联的字段进行查询，但是只有查询条件中使用了所关联字段中的第一个字段，多列索引才会被使用。

1、创建表时创建多列索引

MariaDB [company]> CREATE TABLE t_dept(deptno INT,dname VARCHAR(20),loc VARCHAR(40),KEY index_dname_loc(dname,loc));

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70 8][]

2、方法二：在已经存在的表上创建多列索引

#创建原始表
    MariaDB [company]> CREATE TABLE t_dept(deptno INT,dname VARCHAR(20),loc VARCHAR(40));
    
    MariaDB [company]> CREATE INDEX index_dname_loc ON t_dept (dname,loc);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70 9][]

3、方法三：通过SQL语句ALTER TABLE创建多列索引

#创建原始表
    MariaDB [company]> CREATE TABLE t_dept(deptno INT,dname VARCHAR(20),loc VARCHAR(40));
    
    MariaDB [company]> ALTER TABLE t_dept ADD INDEX index_dname_loc (dname,loc);

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70 10][]

## 五、删除索引 ##

1、删除索引的语法形式

MariaDB [company]> DROP INDEX index_dname_loc ON t_dept;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70 11][]

[20200224161425510.png]: https://img-blog.csdnimg.cn/20200224161425510.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200224164152813.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv,size_16,color_FFFFFF,t_70
[20200225102319987.png]: https://img-blog.csdnimg.cn/20200225102319987.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200224164853759.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv,size_16,color_FFFFFF,t_70
[20200224165711205.png]: https://img-blog.csdnimg.cn/20200224165711205.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20210414083723131.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20210414101639503.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20210414101917669.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/2021041410343154.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/2021041410470580.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20210414104913308.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20210414105605331.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70 9]: https://img-blog.csdnimg.cn/20210414105842216.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70 10]: https://img-blog.csdnimg.cn/20210414110309223.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv_size_16_color_FFFFFF_t_70 11]: https://img-blog.csdnimg.cn/20210414110744570.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3RsYWRhZ2lv,size_16,color_FFFFFF,t_70