MySQL之创建/修改/删除索引（CREATE/ALTER/DROP INDEX）
一、MySQL索引简介
索引是 MySQL数据库中的重要对象之一，用于快速找出某个列中有某一特定值的行。
为什么要使用索引
索引是 MySQL 中一种十分重要的数据库对象。它是数据库性能调优技术的基础，常用于实现数据的快速检索。
索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对应关系表，实质上是一张描述索引列的列值与原表中记录行之间一一对应关系的有序表。
在 MySQL 中，通常有以下两种方式访问数据库表的行数据：
1) 顺序访问
顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。这种方式实现比较简单，但是当表中有大量数据的时候，效率非常低下。例如，在几千万条数据中查找少量的数据时，使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能。
2) 索引访问
索引访问是通过遍历索引来直接访问表中记录行的方式。使用这种方式的前提是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针，根据指定的排序顺序对这些指针排序。
例如，在学生基本信息表 students 中，如果基于 student_id 建立了索引，系统就建立了一张索引列到实际记录的映射表，当用户需要查找 student_id 为 12022 的数据的时候，系统先在 student_id 索引上找到该记录，然后通过映射表直接找到数据行，并且返回该行数据。因为扫描索引的速度一般远远大于扫描实际数据行的速度，所以采用索引的方式可以大大提高数据库的工作效率。
索引的分类
索引的类型和存储引擎有关，每种存储引擎所支持的索引类型不一定完全相同。根据存储方式的不同，MySQL 中常用的索引在物理上分为以下两类。
1) B-树索引
B-树索引又称为 BTREE 索引，目前大部分的索引都是采用 B-树索引来存储的。B-树索引是一个典型的数据结构，其包含的组件主要有以下几个：
- 叶子节点：包含的条目直接指向表里的数据行。叶子节点之间彼此相连，一个叶子节点有一个指向下一个叶子节点的指针。
- 分支节点：包含的条目指向索引里其他的分支节点或者叶子节点。
- 根节点：一个 B-树索引只有一个根节点，实际上就是位于树的最顶端的分支节点。
基于这种树形数据结构，表中的每一行都会在索引上有一个对应值。因此，在表中进行数据查询时，可以根据索引值一步一步定位到数据所在的行。
B-树索引可以进行全键值、键值范围和键值前缀查询，也可以对查询结果进行 ORDER BY 排序。但 B-树索引必须遵循左边前缀原则，要考虑以下几点约束：
- 查询必须从索引的最左边的列开始。
- 查询不能跳过某一索引列，必须按照从左到右的顺序进行匹配。
- 存储引擎不能使用索引中范围条件右边的列。
2) 哈希索引
哈希（Hash）一般翻译为“散列”，也有直接音译成“哈希”的，就是把任意长度的输入（又叫作预映射，pre-image）通过散列算法变换成固定长度的输出，该输出就是散列值。
哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储引擎支持这类索引。其中，MEMORY 存储引擎可以支持 B- 树索引和 HASH 索引，且将 HASH 当成默认索引。
HASH 索引不是基于树形的数据结构查找数据，而是根据索引列对应的哈希值的方法获取表的记录行。哈希索引的最大特点是访问速度快，但也存在下面的一些缺点：
- MySQL 需要读取表中索引列的值来参与散列计算，散列计算是一个比较耗时的操作。也就是说，相对于 B- 树索引来说，建立哈希索引会耗费更多的时间。
- 不能使用 HASH 索引排序。
- HASH 索引只支持等值比较，如“=”“IN()”或“<=>”。
- HASH 索引不支持键的部分匹配，因为在计算 HASH 值的时候是通过整个索引值来计算的。
根据索引的具体用途，MySQL 中的索引在逻辑上分为以下 5 类：
1) 普通索引
普通索引是最基本的索引类型，唯一任务是加快对数据的访问速度，没有任何限制。创建普通索引时，通常使用的关键字是 INDEX 或 KEY。
2) 唯一性索引
唯一性索引是不允许索引列具有相同索引值的索引。如果能确定某个数据列只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字 UNIQUE 把它定义为一个唯一性索引。
创建唯一性索引的目的往往不是为了提高访问速度，而是为了避免数据出现重复。
3) 主键索引
主键索引是一种唯一性索引，即不允许值重复或者值为空，并且每个表只能有一个主键。主键可以在创建表的时候指定，也可以通过修改表的方式添加，必须指定关键字 PRIMARY KEY。
注意：主键是数据库考察的重点。注意每个表只能有一个主键。
4) 空间索引
空间索引主要用于地理空间数据类型 GEOMETRY。
5) 全文索引
全文索引只能在 VARCHAR 或 TEXT 类型的列上创建，并且只能在 MyISAM 表中创建。
索引在逻辑上分为以上 5 类，但在实际使用中，索引通常被创建成单列索引和组合索引。
- 单列索引就是索引只包含原表的一个列。
- 组合索引也称为复合索引或多列索引，相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。
提示：一个表可以有多个单列索引，但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。比如，在一个表中创建了一个组合索引(c1，c2，c3)，在实际查询中，系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1，c2)和多列索引(c1，c2，c3)。
为了提高索引的应用性能，MySQL中的索引可以根据具体应用采用不同的索引策略。这些索引策略所对应的索引类型有聚集索引、次要索引、覆盖索引、复合索引、前缀索引、唯一索引等。
索引的使用原则和注意事项
虽然索引可以加快查询速度，提高 MySQL 的处理性能，但是过多地使用索引也会造成以下弊端：
- 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
- 除了数据表占数据空间之外，每一个索引还要占一定的物理空间。如果要建立聚簇索引，那么需要的空间就会更大。
- 当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。
注意：索引可以在一些情况下加速查询，但是在某些情况下，会降低效率。
索引只是提高效率的一个因素，因此在建立索引的时候应该遵循以下原则：
- 在经常需要搜索的列上建立索引，可以加快搜索的速度。
- 在作为主键的列上创建索引，强制该列的唯一性，并组织表中数据的排列结构。
- 在经常使用表连接的列上创建索引，这些列主要是一些外键，可以加快表连接的速度。
- 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，所以其指定的范围是连续的。
- 在经常需要排序的列上创建索引，因为索引已经排序，所以查询时可以利用索引的排序，加快排序查询。
- 在经常使用 WHERE 子句的列上创建索引，加快条件的判断速度。
- 对于那些在查询中很少使用或参考的列不应该创建索引。因为这些列很少使用到，所以有索引或者无索引并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度，并增大了空间要求。
- 对于那些只有很少数据值的列也不应该创建索引。因为这些列的取值很少，例如人事表的性别列。查询结果集的数据行占了表中数据行的很大比例，增加索引并不能明显加快检索速度。
- 对于那些定义为 TEXT、IMAGE 和 BIT 数据类型的列不应该创建索引。因为这些列的数据量要么相当大，要么取值很少。
- 当修改性能远远大于检索性能时，不应该创建索引。因为修改性能和检索性能是互相矛盾的。当创建索引时，会提高检索性能，降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
二、MySQL创建索引（CREATE INDEX）
索引的建立对于 MySQL数据库的高效运行是很重要的，索引可以大大提升 MySQL 的检索速度。
基本语法
MySQL 提供了三种创建索引的方法：
1) 使用 CREATE INDEX 语句
可以使用专门用于创建索引的 CREATE INDEX 语句在一个已有的表上创建索引，但该语句不能创建主键。
语法格式：
CREATE <索引名> ON <表名> (<列名> [<长度>] [ ASC | DESC])
语法说明如下：
<索引名>：指定索引名。一个表可以创建多个索引，但每个索引在该表中的名称是唯一的。<表名>：指定要创建索引的表名。<列名>：指定要创建索引的列名。通常可以考虑将查询语句中在 JOIN 子句和 WHERE 子句里经常出现的列作为索引列。<长度>：可选项。指定使用列前的 length 个字符来创建索引。使用列的一部分创建索引有利于减小索引文件的大小，节省索引列所占的空间。在某些情况下，只能对列的前缀进行索引。索引列的长度有一个最大上限 255 个字节（MyISAM 和 InnoDB 表的最大上限为 1000 个字节），如果索引列的长度超过了这个上限，就只能用列的前缀进行索引。另外，BLOB 或 TEXT 类型的列也必须使用前缀索引。ASC|DESC：可选项。ASC指定索引按照升序来排列，DESC指定索引按照降序来排列，默认为ASC。
【实例1】在tb_stu_info表上创建索引，以列id为索引列
2) 使用 CREATE TABLE 语句
索引也可以在创建表（CREATE TABLE）的同时创建。在 CREATE TABLE 语句中添加以下语句。
语法格式：
CONSTRAINT PRIMARY KEY [索引类型] (<列名>,…)
在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的主键。
【实例2】创建一个表 tb_stu_info2，在该表的 id 字段上使用 PRIMARY KEY 关键字创建主键索引。
语法格式：
KEY | INDEX [<索引名>] [<索引类型>] (<列名>,…)
在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的索引。
【实例 3】创建一个表 tb_stu_info3，在该表的 height 字段创建一般索引。
语法格式：
UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)
在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的唯一性索引。
【实例 4】创建一个表 tb_stu_info4，在该表的 id 字段上使用 UNIQUE 关键字创建唯一索引。
语法格式：
FOREIGN KEY <索引名> <列名>
在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的外键。
【实例 5】创建一个表 tb_stu_info5，在该表的 dept_id字段上使用 FOREIGN KEY关键字创建外键索引。
在使用 CREATE TABLE 语句定义列选项的时候，可以通过直接在某个列定义后面添加 PRIMARY KEY 的方式创建主键。而当主键是由多个列组成的多列索引时，则不能使用这种方法，只能用在语句的最后加上一个 PRIMARY KRY(<列名>，…) 子句的方式来实现。
3) 使用 ALTER TABLE 语句
CREATE INDEX 语句可以在一个已有的表上创建索引，ALTER TABLE 语句也可以在一个已有的表上创建索引。在使用 ALTER TABLE 语句修改表的同时，可以向已有的表添加索引。具体的做法是在 ALTER TABLE 语句中添加以下语法成分的某一项或几项。
语法格式：
ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)
在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加索引。
【实例 5】创建一个表 tb_stu_info6，在该表的id字段添创建一般索引。
语法格式：
ADD PRIMARY KEY [<索引类型>] (<列名>,…)
在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加主键。
【实例 7】创建一个表 tb_stu_info7，在该表的 id 字段上使用 PRIMARY 关键字创建主键索引。
语法格式：
ADD UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)
在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加唯一性索引。
【实例 8】创建一个表 tb_stu_info8，在该表的 id 字段上使用 UNIQUE 关键字创建唯一索引。
语法格式：
ADD FOREIGN KEY [<索引名>] (<列名>,…)
在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加外键。
【实例 9】创建一个表 tb_stu_info9，在该表的 dept_id 字段上使用 FOREIGN关键字创建外键索引。
查看索引
在 MySQL 中，如果要查看已创建的索引的情况，可以使用 SHOW INDEX 语句查看表中创建的索引。
语法格式：
SHOW INDEX FROM <表名> [ FROM <数据库名>]
语法说明如下：
<表名>：要显示索引的表。<数据库名>：要显示的表所在的数据库。
显示数据库 mytest 的表 course 的索引情况。
mysql> SHOW INDEX FROM course FROM mytest;
该语句会返回一张结果表，该表有如下几个字段，每个字段所显示的内容说明如下。
- Table：表的名称。
- Non_unique：用于显示该索引是否是唯一索引。若不是唯一索引，则该列的值显示为 1；若是唯一索引，则该列的值显示为 0。
- Key_name：索引的名称。
- Seq_in_index：索引中的列序列号，从 1 开始计数。
- Column_name：列名称。
- Collation：显示列以何种顺序存储在索引中。在 MySQL 中，升序显示值“A”（升序），若显示为 NULL，则表示无分类。
- Cardinality：显示索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大。
- Sub_part：若列只是被部分编入索引，则为被编入索引的字符的数目。若整列被编入索引，则为 NULL。
- Packed：指示关键字如何被压缩。若没有被压缩，则为 NULL。
- Null：用于显示索引列中是否包含 NULL。若列含有 NULL，则显示为 YES。若没有，则该列显示为 NO。
- Index_type：显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。
- Comment：显示评注。
三、MySQL修改和删除索引（DROP INDEX）
在 MySQL中修改索引可以通过删除原索引，再根据需要创建一个同名的索引，从而实现修改索引的操作。
基本语法
当不再需要索引时，可以使用 DROP INDEX 语句或 ALTER TABLE 语句来对索引进行删除。
1) 使用 DROP INDEX 语句
语法格式：
DROP INDEX <索引名> ON <表名>
语法说明如下：
<索引名>：要删除的索引名。<表名>：指定该索引所在的表名。
2) 使用 ALTER TABLE 语句
根据 ALTER TABLE 语句的语法可知，该语句也可以用于删除索引。具体使用方法是将 ALTER TABLE 语句的语法中部分指定为以下子句中的某一项。
- DROP PRIMARY KEY：表示删除表中的主键。一个表只有一个主键，主键也是一个索引。
- DROP INDEX index_name：表示删除名称为 index_name 的索引。
- DROP FOREIGN KEY fk_symbol：表示删除外键。
注意：如果删除的列是索引的组成部分，那么在删除该列时，也会将该列从索引中删除；如果组成索引的所有列都被删除，那么整个索引将被删除。
删除索引
【实例 1】删除表 tb_stu_info 中的索引，输入的 SQL 语句和执行结果如下所示。
【实例 2】删除表 tb_stu_info3 中名称为 height的索引，输入的 SQL 语句和执行结果如下所示。
