MySql基础知识-----索引的操作

最新推荐文章于 2023-05-02 08:30:00 发布

最新推荐文章于 2023-05-02 08:30:00 发布 · 473 阅读

文章标签：

#mysql #索引操作

本文介绍了MySQL中的六种索引类型，包括普通索引、唯一索引、全文索引、单列索引、多列索引和空间索引，并详细讲解了如何创建、查看和删除这些索引。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 索引简介

数据库对象索引其实与书的目录非常相似，主要是为了提高从表中检索数据的速度。由于数据存储在数据库表中，所以索引是创建在数据库表对象上的，由表中的一个或者多个字段生成的键组成，这些键存储在数据结构中，通过MySql可以快速有效的查询与键值相关联的字段，适合创建索引的情有：

（1）经常被查询的字段，即在where子句中出现的字段。

（2）在分组的字段，即在group by子句中出现的字段

（3）存在依赖关系的子表和父表之间的联合查询，即主键或外键字段。

MySql支持6中索引，他们分别是普通索引，唯一索引，全文索引，单列索引，多列索引和空间索引。

2. 创建和查看索引

2.1 创建和查看普通索引

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

2.1.1 创建表时创建索引

语法形式如下：

create table table_name(属性名 属性类型，
属性名 属性类型，
......
属性名 属性类型，
index|key 【索引名】(属性名1 【(长度)】【desc|asc】));

示例：将t_dept设置为普通索引：

mysql> create table t_dept(deptno INT,deptname VARCHAR(20),deptleader VARCHAR(20),index index_deptno(deptno));
Query OK, 0 rows affected (0.02 sec)

mysql> desc t_dept;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| deptno     | int(11)     | YES  | MUL | NULL    |       |
| deptname   | varchar(20) | YES  |     | NULL    |       |
| deptleader | varchar(20) | YES  |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

为了检验索引是否被成功创建，可以执行SQL语句show create table，具体如下：

mysql> show create table t_dept \G
*************************** 1. row ***************************
       Table: t_dept
Create Table: CREATE TABLE `t_dept` (
  `deptno` int(11) DEFAULT NULL,
  `deptname` varchar(20) DEFAULT NULL,
  `deptleader` varchar(20) DEFAULT NULL,
  KEY `index_deptno` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

为了检验数据库t_dept中索引是否被使用，执行SQL语句EXPLAIN，具体语句如下：

mysql> explain select * from t_dept where deptno=1 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: t_dept
   partitions: NULL
         type: ref
possible_keys: index_deptno
          key: index_deptno
      key_len: 5
          ref: const
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

可以看到索引index_deptno已经被使用了。

2.1.2 在已经存在的表上创建索引

语法形式如下：

create index 索引名 on 表名(属性名【(长度)】 【asc|desc】);

示例：在表t_dept上使用deptname创建一个名为index_deptno的索引：

mysql> create index index_deptname on t_dept(deptname);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t_dept \G
*************************** 1. row ***************************
       Table: t_dept
Create Table: CREATE TABLE `t_dept` (
  `deptno` int(11) DEFAULT NULL,
  `deptname` varchar(20) DEFAULT NULL,
  `deptleader` varchar(20) DEFAULT NULL,
  KEY `index_deptno` (`deptno`),
  KEY `index_deptname` (`deptname`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

2.1.3 通过SQL语句ALTER TABLE创建普通索引

语法形式如下：

alter table table_name add index|key 索引名(属性名【(长度)】【asc|desc】);

示例：在表t_dept上使用deptleader创建一个名为index_deptleader的索引：

mysql> alter table t_dept add index index_deptleader(deptleader);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t_dept \G
*************************** 1. row ***************************
       Table: t_dept
Create Table: CREATE TABLE `t_dept` (
  `deptno` int(11) DEFAULT NULL,
  `deptname` varchar(20) DEFAULT NULL,
  `deptleader` varchar(20) DEFAULT NULL,
  KEY `index_deptno` (`deptno`),
  KEY `index_deptname` (`deptname`),
  KEY `index_deptleader` (`deptleader`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

2.2 创建和查看唯一索引

唯一索引：创建索引时，限制索引的值必须时唯一的，通过该类型创建的索引可以更快速的查询某条记录。根据创建索引的方式，可以分为自动索引和手动索引。自动索引就是指在在数据库里设置完整性约束时，该表会被系统自动床架索引。手动索引就是指手动在表上创建索引。当在表中设置某个字段为主键或者唯一完整性约束时，系统就会自动创建关联该字段的唯一索引。

2.2.1 创建表示创建唯一索引：

语法形式：

create table table_name(属性名 属性类型，
属性名 属性类型，
......
属性名 属性类型，
unique index|key 【索引名】(属性名1 【(长度)】【desc|asc】));

示例如下：

mysql> create table t_dept(deptno INT,deptname VARCHAR(20),deptleader VARCHAR(20),unique index index_deptno(deptno));
Query OK, 0 rows affected (0.02 sec)

mysql> show create table t_dept \G
*************************** 1. row ***************************
       Table: t_dept
Create Table: CREATE TABLE `t_dept` (
  `deptno` int(11) DEFAULT NULL,
  `deptname` varchar(20) DEFAULT NULL,
  `deptleader` varchar(20) DEFAULT NULL,
  UNIQUE KEY `index_deptno` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

2.2.2 在已存在的表上创建唯一索引

语法形式如下：

create unique index 索引名 on 表名(属性名【(长度)】 【asc|desc】);

示例略。

2.2.3 通过SQL语句ALTER TABLE创建唯一索引

语法形式如下：

alter table table_name add unique index|key 索引名(属性名【(长度)】【asc|desc】);

示例略。

2.3 创建和查看全文索引

全文索引：主要关联在数据类型为CHAR，VARCHAR和TEXT的字段上，以便能够更加快速的查询数据量较大的字符串类型的字段。

2.3.1 在创建表时创建全文索引：

语法形式如下：

create table table_name(属性名 属性类型，
属性名 属性类型，
......
属性名 属性类型，
fulltext index|key 【索引名】(属性名1 【(长度)】【desc|asc】));

示例略。

2.3.2 在已存在的表上创建全文索引

语法形式如下：

create fulltext index 索引名 on 表名(属性名【(长度)】 【asc|desc】);

示例略。

2.3.3 通过SQL语句ALTER TABLE创建全文索引

语法形式如下：

alter table table_name add fulltext index|key 索引名(属性名【(长度)】【asc|desc】);

示例略。

2.4 创建和查看多列索引

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

2.4.1 在创建表时创建多列索引

语法形式如下：

create table table_name(属性名 属性类型，
属性名 属性类型，
......
属性名 属性类型，
index|key 【索引名】(属性名1 【(长度)】【desc|asc】),
....
(属性名n 【(长度)】【desc|asc】),

示例略。

2.4.2 在已存在的表上创建索引

语法形式如下：

create fulltext index 索引名 on 表名(属性名【(长度)】 【asc|desc】,
.......
属性名【(长度)】 【asc|desc);

示例略。

2.4.3 通过SQL语句ALTER TABLE创建多列索引

语法形式：

alter table table_name add fulltext index|key 索引名(属性名【(长度)】【asc|desc】
......
属性名【(长度)】【asc|desc】);

示例略。

2.5 删除索引

语法形式：

drop index index_name on table_name;

示例：删除t_dept的index_deptno索引：

mysql> show create table t_dept \G
*************************** 1. row ***************************
       Table: t_dept
Create Table: CREATE TABLE `t_dept` (
  `deptno` int(11) DEFAULT NULL,
  `deptname` varchar(20) DEFAULT NULL,
  `deptleader` varchar(20) DEFAULT NULL,
  UNIQUE KEY `index_deptno` (`deptno`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> drop index index_deptno on t_dept;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table t_dept \G
*************************** 1. row ***************************
       Table: t_dept
Create Table: CREATE TABLE `t_dept` (
  `deptno` int(11) DEFAULT NULL,
  `deptname` varchar(20) DEFAULT NULL,
  `deptleader` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql>