MySQL（三）索引的操作

介绍

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

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

以下情况适合创建索引：

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

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

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

一、创建和查看普通索引

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

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

查看

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

#创建原始表
MariaDB [company]> CREATE TABLE t_dept(deptno INT,dname VARCHAR(20),loc VARCHAR(40));
MariaDB [company]> desc t_dept;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| deptno | int(11)     | YES  |     | NULL    |       |
| dname  | varchar(20) | YES  |     | NULL    |       |
| loc    | varchar(40) | YES  |     | NULL    |       |
+--------+-------------+------+-----+---------+-------+

#在已经存在的表创建普通索引
MariaDB [company]> CREATE INDEX index_deptno ON t_dept (deptno);

查看

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);

查看

二、创建和查看唯一索引

所谓唯一索引，就是在创建索引时，限制索引的值必须是唯一的。通过该类型的索引可以更快速地查询某条记录。在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

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);

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);

三、创建和查看全文索引

全文索引主要关联在数据类型为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;

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);

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);

四、创建和查看多列索引

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

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

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

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);

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);

五、删除索引

1、删除索引的语法形式

MariaDB [company]> DROP INDEX index_dname_loc ON t_dept;