MySQL 实验 7：索引的操作

MySQL 实验 7：索引的操作

索引是对数据表中一列或多列的值进行排序的一种结构，索引可以大大提高 MySQL 的检索速度。合理使用索引，可以大大提升 SQL 查询的性能。

索引好比是一本书前面的目录，假如我们需要从书籍查找与 xx 相关的内容，我们可以从目录中查找，定位到 xx 内容所在页面，如果没有设置目录（索引），则只能逐字逐页阅读文本查找。

当执行查询操作时，如果不使用索引，MySQL 必须从第一条记录开始读完整个表，直到找出相关的行。如果表中查询的列有一个索引，MySQL 能够快速到达一个位置去搜索数据文件，而不必查看所有数据。

一、索引的优缺点

索引虽然可以提高检索的速度，但创建过多的、不必要的索引还会影响数据增、删、改的效率。

1、索引的优点

（1）索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因。

（2）通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。

（3）可以加速表和表之间的连接。

（4）在使用分组和排序子句进行数据检索时，可以减少查询中分组和排序的时间。

2、索引的缺点

（1）创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。

（2）索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大。

（3）对表中的数据进行增、删、改的时候，索引也要动态的维护，降低了数据的维护速度。

3、创建索引的原则

（1）在经常需要检索的列上创建索引可以加快检索的速度。

（2）在作为主键的列上创建聚簇索引可以保证该列的唯一性和组织表中数据的排列顺序。

（3）在经常用在连接的列上，一般是表中的外键创建索引，可以提高连接的速度。

（4）在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引。

（4）在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间。

（5）在经常使用在 WHERE 子句中的列上面创建索引，可以加快条件的判断速度。

（6）对于那些在查询中很少使用的列不应该创建索引。

（7）对于那些重复值过多的列也不应该增加索引。

（8）经常进行数据更新的列不应该创建索引。

二、索引的分类

MySQL 的索引有两种分类方式：逻辑分类和物理分类。

1、逻辑分类

（1）按功能划分索引可以分为四类：主键索引、唯一索引、普通索引和全文索引。

主键索引：主键索引默认创建为聚簇索引，主键索引会改变表中记录的物理顺序。一张表只能创建一个主键索引，主键索引所包含的列不允许重复、不允许为 NULL。

唯一索引：唯一索引包含的数据列不允许取重复值，但允许为 NULL 值。一张表可以创建多个唯一索引，索引列的值必须唯一，如果是组合索引，则唯一索引包含的所有列的组合必须取值唯一。

普通索引：一张表可以创建多个普通索引，普通索引允许数据重复，索引所包含的列允许取 NULL 值。

全文索引：查找文本中的关键词，主要用于全文检索。

（2）按索引包含的列数可以分为两类：单列索引和多列索引（又叫组合索引）。

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

组合索引：一个组合索引包含两个或两个以上的列。查询时遵循组合索引的【最左前缀】原则，即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。

2、物理分类

按索引的存储结构划分，可以把索引分为聚簇索引（也叫聚集索引）和非聚簇索引。

（1）聚簇索引：聚簇索引（clustered index）不是单独的一种索引类型，而是一种数据存储方式，每张表最多只能拥有一个聚簇索引。表中的数据其实就是按照聚簇索引的顺序进行排列。因此：按照聚簇索引查询速度更快。

（2）非聚簇索引：聚簇索引之外的索引称之为非聚簇索引，又称为辅助索引。查找数据时首先通过非聚簇索引找到主键值，然后到主键索引树中通过主键值找到数据行。

三、创建表的同时创建索引

（1）主键索引和唯一索引的创建请参见【MySQL 实验6：定义数据的完整性】。

（2）创建普通索引：可以使用关键字 key 或 index 创建普通索引。语法格式如下：

create table table_name(col_name data_type primary key,col_name data_type,... ,col_name data_type,INDEX|KEY [索引名] (列名[(长度)] , ...)
);
-- 说明：
（1）索引名：给创建的索引取一个新名称。如果不指定则采用字段名作为索引名。
（2）列名：指定索引对应的列的名称。列名可以有多个，即创建多列索引。
（3）长度：指索引的长度，字符串类型才可以使用。
（4）可以根据表达式创建索引。

例如：

create table emp02(e_id int primary key,e_name char(20),birth date,salary decimal(10,2),phone char(20),address varchar(200),dept_name char(30),key(e_name),                               -- 不指定索引名称index idx_salary(salary),                  -- 指定索引名称index idx_dept_salary(dept_name,salary),   -- 指定多列索引key idx_phone(phone(11))                   -- 指定索引长度
);-- 查看表结构
mysql> desc emp02;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| e_id      | int(11)       | NO   | PRI | NULL    |       |
| e_name    | char(20)      | YES  | MUL | NULL    |       |
| birth     | date          | YES  |     | NULL    |       |
| salary    | decimal(10,2) | YES  | MUL | NULL    |       |
| phone     | char(20)      | YES  | MUL | NULL    |       |
| address   | varchar(200)  | YES  |     | NULL    |       |
| dept_name | char(30)      | YES  | MUL | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
7 rows in set (0.00 sec)-- 查看索引
mysql> show create table emp02\G
*************************** 1. row ***************************Table: emp02
Create Table: CREATE TABLE `emp02` (`e_id` int(11) NOT NULL,`e_name` char(20) DEFAULT NULL,`birth` date DEFAULT NULL,`salary` decimal(10,2) DEFAULT NULL,`phone` char(20) DEFAULT NULL,`address` varchar(200) DEFAULT NULL,`dept_name` char(30) DEFAULT NULL,PRIMARY KEY (`e_id`),KEY `e_name` (`e_name`),KEY `idx_salary` (`salary`),KEY `idx_dept_salary` (`dept_name`,`salary`),KEY `idx_phone` (`phone`(11))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

00试环境进行据库

create table emp02(e_id int primary key,e_name char(20),birth date,salary decimal(10,2),phone char(20),address varchar(200),dept_name char(30),key(e_name),                               -- 不指定索引名称index idx_salary(salary),                  -- 指定索引名称index idx_dept_salary(dept_name,salary),   -- 指定多列索引key idx_phone(phone(11)) 
);

四、添加索引

数据表创建完成后可以使用 alter table 或 create index 命令添加所需的索引。

1、使用 alter table 添加索引

语法格式如下：

ALTER TABLE 表名 
ADD INDEX|KEY [索引名](字段名[(长度)], ...);

例如：

create table emp03(e_id int primary key,e_name char(20),birth date,salary decimal(10,2),phone char(20),address varchar(200),dept_name char(30)
);-- 添加索引，不指定索引名
alter table emp03 add index(e_name);-- 添加索引，指定索引名
alter table emp03 add index idx_salary(salary);-- 添加多列索引，指定索引名
alter table emp03 add index idx_dept_salary(dept_name, salary);-- 添加索引，指定长度
alter table emp03 add index idx_phone(phone(11));-- 查看表结构
mysql> desc emp03;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| e_id      | int(11)       | NO   | PRI | NULL    |       |
| e_name    | char(20)      | YES  | MUL | NULL    |       |
| birth     | date          | YES  |     | NULL    |       |
| salary    | decimal(10,2) | YES  | MUL | NULL    |       |
| phone     | char(20)      | YES  | MUL | NULL    |       |
| address   | varchar(200)  | YES  |     | NULL    |       |
| dept_name | char(30)      | YES  | MUL | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
7 rows in set (0.01 sec)-- 查看索引
mysql> show create table emp03\G
*************************** 1. row ***************************Table: emp03
Create Table: CREATE TABLE `emp03` (`e_id` int(11) NOT NULL,`e_name` char(20) DEFAULT NULL,`birth` date DEFAULT NULL,`salary` decimal(10,2) DEFAULT NULL,`phone` char(20) DEFAULT NULL,`address` varchar(200) DEFAULT NULL,`dept_name` char(30) DEFAULT NULL,PRIMARY KEY (`e_id`),KEY `e_name` (`e_name`),KEY `idx_salary` (`salary`),KEY `idx_dept_salary` (`dept_name`,`salary`),KEY `idx_phone` (`phone`(11))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

2、使用 create index 添加索引

语法格式如下：

CREATE INDEX 索引名 ON 表名(字段名[(长度)], ...);

例如：

create table emp04(e_id int primary key,e_name char(20),birth date,salary decimal(10,2),phone char(20),address varchar(200),dept_name char(30)
);-- 添加单列索引
create index idx_name on emp04(e_name);-- 添加多列索引
create index idx_dept_salary on emp04(dept_name,salary);-- 指定索引长度
create index idx_phone on emp04(phone(11));-- 查看表结构
mysql> desc emp04;
+-----------+---------------+------+-----+---------+-------+
| Field     | Type          | Null | Key | Default | Extra |
+-----------+---------------+------+-----+---------+-------+
| e_id      | int(11)       | NO   | PRI | NULL    |       |
| e_name    | char(20)      | YES  | MUL | NULL    |       |
| birth     | date          | YES  |     | NULL    |       |
| salary    | decimal(10,2) | YES  |     | NULL    |       |
| phone     | char(20)      | YES  | MUL | NULL    |       |
| address   | varchar(200)  | YES  |     | NULL    |       |
| dept_name | char(30)      | YES  | MUL | NULL    |       |
+-----------+---------------+------+-----+---------+-------+
7 rows in set (0.01 sec)-- 查看索引
mysql> show create table emp04\G
*************************** 1. row ***************************Table: emp04
Create Table: CREATE TABLE `emp04` (`e_id` int(11) NOT NULL,`e_name` char(20) DEFAULT NULL,`birth` date DEFAULT NULL,`salary` decimal(10,2) DEFAULT NULL,`phone` char(20) DEFAULT NULL,`address` varchar(200) DEFAULT NULL,`dept_name` char(30) DEFAULT NULL,PRIMARY KEY (`e_id`),KEY `idx_name` (`e_name`),KEY `idx_dept_salary` (`dept_name`,`salary`),KEY `idx_phone` (`phone`(11))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

五、删除索引

删除索引的语法格式如下：

-- 使用 alter table 命令删除索引
LTER TABLE 表名 DROP INDEX 索引名;
-- 使用 drop index 命令删除索引
DROP INDEX 索引名 ON 表名;

例如：

-- 查看 emp03 表中的索引
mysql> show create table emp03\G
*************************** 1. row ***************************Table: emp03
Create Table: CREATE TABLE `emp03` (`e_id` int(11) NOT NULL,`e_name` char(20) DEFAULT NULL,`birth` date DEFAULT NULL,`salary` decimal(10,2) DEFAULT NULL,`phone` char(20) DEFAULT NULL,`address` varchar(200) DEFAULT NULL,`dept_name` char(30) DEFAULT NULL,PRIMARY KEY (`e_id`),KEY `e_name` (`e_name`),KEY `idx_salary` (`salary`),KEY `idx_dept_salary` (`dept_name`,`salary`),KEY `idx_phone` (`phone`(11))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)-- 删除索引：e_name
alter table emp03 drop index e_name;-- 删除索引：idx_salary
drop index idx_salary on emp03;-- 重新查看 emp03 表中的索引
mysql> show create table emp03\G
*************************** 1. row ***************************Table: emp03
Create Table: CREATE TABLE `emp03` (`e_id` int(11) NOT NULL,`e_name` char(20) DEFAULT NULL,`birth` date DEFAULT NULL,`salary` decimal(10,2) DEFAULT NULL,`phone` char(20) DEFAULT NULL,`address` varchar(200) DEFAULT NULL,`dept_name` char(30) DEFAULT NULL,PRIMARY KEY (`e_id`),KEY `idx_dept_salary` (`dept_name`,`salary`),KEY `idx_phone` (`phone`(11))
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)