空~

6. 索引

空~2022年8月26日
  • MySQL
大约 15 分钟

6. 索引

索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中有某一特定值的记录。本节将详细讲解索引的含义、作用和优缺点。

通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列。否则，数据库系统将读取每条记录的所有信息进行匹配。

可以把索引比作新华字典的音序表。例如，要查“库”字，如果不使用音序，就需要从字典的 400 页中逐页来找。但是，如果提取拼音出来，构成音序表，就只需要从 10 多页的音序表中直接查找。这样就可以大大节省时间。

因此，使用索引可以很大程度上提高数据库的查询速度，还有效的提高了数据库系统的性能。

索引的优缺点

索引有其明显的优势，也有其不可避免的缺点。

优点：

索引的优点如下：

  • 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。
  • 可以给所有的 MySQL 列类型设置索引。
  • 可以大大加快数据的查询速度，这是使用索引最主要的原因。
  • 在实现数据的参考完整性方面可以加速表与表之间的连接。
  • 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间

缺点：

增加索引也有许多不利的方面，主要如下：

  • 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
  • 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
  • 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

使用索引时，需要综合考虑索引的优点和缺点。

索引可以提高查询速度，但是会影响插入记录的速度。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响会更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后，再创建索引。

索引类型

索引的类型和存储引擎有关，每种存储引擎所支持的索引类型不一定完全相同。MySQL 索引可以从存储方式、逻辑角度和实际使用的角度来进行分类。

逻辑区分：

根据索引的具体用途，MySQL 中的索引在逻辑上分为以下 5 类：

  1. 普通索引

    普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。

    普通索引允许在定义索引的列中插入重复值和空值。

    创建普通索引时，通常使用的关键字是 INDEX 或 KEY。

  2. 唯一索引

    唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。

    唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。

    创建唯一索引通常使用 UNIQUE 关键字。

  3. 主键索引

    顾名思义，主键索引就是专门为主键字段创建的索引，也属于索引的一种。

    主键索引是一种特殊的唯一索引，不允许值重复或者值为空。

    创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。

  4. 空间索引

    空间索引是对空间数据类型的字段建立的索引，使用 SPATIAL 关键字进行扩展。

    创建空间索引的列必须将其声明为 NOT NULL，空间索引只能在存储引擎为 MyISAM 的表中创建。

    空间索引主要用于地理空间数据类型 GEOMETRY。对于初学者来说，这类索引很少会用到。

  5. 全文索引

    全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。

    全文索引允许在索引列中插入重复值和空值。

    不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。

    创建全文索引使用 FULLTEXT 关键字。

实际使用区分：

索引在逻辑上分为以上 5 类，但在实际使用中，索引通常被创建成单列索引和组合索引。

  1. 单列索引

    单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引，单列索引只根据该字段进行索引。

    单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。

  2. 多列索引

    组合索引也称为复合索引或多列索引。相对于单列索引来说，组合索引是将原表的多个列共同组成一个索引。多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

    例如，在表中的 id、name 和 sex 字段上建立一个多列索引，那么，只有查询条件使用了 id 字段时，该索引才会被使用。

提示：一个表可以有多个单列索引，但这些索引不是组合索引。一个组合索引实质上为表的查询提供了多个索引，以此来加快查询速度。比如，在一个表中创建了一个组合索引(c1，c2，c3)，在实际查询中，系统用来实际加速的索引有三个：单个索引(c1)、双列索引(c1，c2)和多列索引(c1，c2，c3)。

索引的创建

创建索引是指在某个表的一列或多列上建立一个索引，可以提高对表的访问速度。创建索引对 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。

  2. 使用 CREATE TABLE 语句

    索引也可以在创建表（CREATE TABLE）的同时创建。在 CREATE TABLE 语句中添加以下语句。

    语法格式：

    CONSTRAINT PRIMARY KEY [索引类型] (<列名>,…)

    在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的主键。

    语法格式：

    KEY | INDEX [<索引名>] [<索引类型>] (<列名>,…)

    在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的索引。

    语法格式：

    UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)

    在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的唯一性索引。

    语法格式：

    FOREIGN KEY <索引名> <列名>

    在 CREATE TABLE 语句中添加此语句，表示在创建新表的同时创建该表的外键。

    在使用 CREATE TABLE 语句定义列选项的时候，可以通过直接在某个列定义后面添加 PRIMARY KEY 的方式创建主键。而当主键是由多个列组成的多列索引时，则不能使用这种方法，只能用在语句的最后加上一个 PRIMARY KRY(<列名>，…) 子句 的方式来实现。

  3. 使用 ALTER TABLE 语句

    CREATE INDEX 语句可以在一个已有的表上创建索引，ALTER TABLE 语句也可以在一个已有的表上创建索引。在使用 ALTER TABLE 语句修改表的同时，可以向已有的表添加索引。具体的做法是在 ALTER TABLE 语句中添加以下语法成分的某一项或几项。

    语法格式：

    ADD INDEX [<索引名>] [<索引类型>] (<列名>,…)

    在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加索引。

    语法格式：

    ADD PRIMARY KEY [<索引类型>] (<列名>,…)

    在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加主键。

    语法格式：

    ADD UNIQUE [ INDEX | KEY] [<索引名>] [<索引类型>] (<列名>,…)

    在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加唯一性索引。

    语法格式：

    ADD FOREIGN KEY [<索引名>] (<列名>,…)

    在 ALTER TABLE 语句中添加此语法成分，表示在修改表的同时为该表添加外键。

创建普通索引

创建普通索引时，通常使用 INDEX 关键字。

示例：在创建 tb_stu_info 表时添加普通索引

CREATE TABLE tb_stu_info

(

id INT NOT NULL,

name CHAR(45) DEFAULT NULL,

dept_id INT DEFAULT NULL,

age INT DEFAULT NULL,

height INT DEFAULT NULL,

INDEX (height)

);

创建唯一索引

创建唯一索引，通常使用 UNIQUE 参数。

示例：创建一个表 tb_stu_info2，在该表的 id 字段上使用 UNIQUE 关键字创建唯一索引。

CREATE TABLE tb_stu_info2
(
 id INT NOT NULL,
 name CHAR(45) DEFAULT NULL,
 dept_id INT DEFAULT NULL,
 age INT DEFAULT NULL,
 height INT DEFAULT NULL,
 UNIQUE INDEX (height)
);

查看索引

索引创建完成后，可以利用 SQL 语句查看已经存在的索引。在 MySQL 中，可以使用 SHOW INDEX 语句查看表中创建的索引。

查看索引的语法格式如下：

SHOW INDEX FROM <表名> [ FROM <数据库名>]

语法说明如下：

  • <表名>：指定需要查看索引的数据表名。

  • <数据库名>：指定需要查看索引的数据表所在的数据库，可省略。比如，SHOW INDEX FROM student FROM test; 语句表示查看 test 数据库中 student 数据表的索引。

    示例：查看学生索引

    SHOW INDEX FROM student FROM stumis;

    202208261703568

主要参数说明

参数说明
Table表示创建索引的数据表名，这里是 tb_stu_info2 数据表。
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 语句或 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. 选择唯一性索引

    唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

  2. 为经常需要排序、分组和联合操作的字段建立索引

    经常需要 ORDER BY、GROUP BY、DISTINCT 和 UNION 等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

  3. 为常作为查询条件的字段建立索引

    如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

    提示

    常查询条件的字段不一定是所要选择的列，换句话说，最适合索引的列是出现在 WHERE 子句中的列，或连接子句中指定的列，而不是出现在 SELECT 关键字后的选择列表中的列。

  4. 限制索引的数目

    索引的数目不是“越多越好”。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。在修改表的内容时，索引必须进行更新，有时还可能需要重构。因此，索引越多，更新表的时间就越长。

    如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL 在生成一个执行计划时，要考虑各个索引，这也要花费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL 选择不到所要使用的最佳索引。

  5. 尽量使用数据量少的索引

    如果索引的值很长，那么查询的速度会受到影响。例如，对一个 CHAR(100) 类型的字段进行全文检索需要的时间肯定要比对 CHAR(10) 类型的字段需要的时间要多。

  6. 数据量小的表最好不要使用索引

    由于数据较小，查询花费的时间可能比遍历索引的时间还要短，索引可能不会产生优化效果。

  7. 尽量使用前缀来索引

    如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT 和 BLOG 类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

  8. 删除不再使用或者很少使用的索引

    表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。应该定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。