本文最后更新于 2024-08-08，文章内容可能已经过时。

索引

索引是一种特殊的数据结构，用于加速数据库查询操作。它通过将列值与行位置进行映射，使得在查找数据时可以快速定位符合条件的行。

在MySQL中，常见的索引类型包括普通索引、唯一索引、主键索引和全文索引等。下面分别介绍这些索引类型的创建、添加和删除方法。

创建索引

在MySQL中，可以在创建表的时候指定索引，也可以使用ALTER TABLE语句为已存在的表添加索引。

在创建表时指定索引

可以在CREATE TABLE语句中，为表的一个或多个列指定索引。例如：

CREATE TABLE mytable (
  id INT NOT NULL PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age INT,
  INDEX idx_name (name)
);

其中，id列被指定为主键（PRIMARY KEY），自动创建了一个唯一索引；name列被指定为普通索引，索引名称为idx_name。

使用ALTER TABLE语句添加索引

使用ALTER TABLE语句可以添加、修改或删除表的索引。具体语法如下：

ALTER TABLE table_name
ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name(column_list),
DROP INDEX index_name;

其中，ADD表示添加索引，UNIQUE、FULLTEXT和SPATIAL分别表示唯一索引、全文索引和空间索引，index_name是索引名称，column_list是要创建索引的列名列表，用逗号分隔；DROP INDEX表示删除索引。

例如，为名为mytable的表添加一个名为idx_age的普通索引，可以使用以下SQL语句：

ALTER TABLE mytable ADD INDEX idx_age (age);

如果要为多个列创建联合索引，可以在括号内同时指定多个列，例如：

ALTER TABLE mytable ADD INDEX idx_name_age (name, age);

这样就会为name和age两列同时创建一个联合索引。

删除索引

使用ALTER TABLE语句可以删除表的索引。具体语法如下：

ALTER TABLE table_name
DROP INDEX index_name;

其中，DROP INDEX表示删除索引，index_name是要删除的索引名称。

例如，要删除名为idx_age的索引，可以使用以下SQL语句：

ALTER TABLE mytable DROP INDEX idx_age;

查询索引

在MySQL中，可以使用以下命令查询索引列表：

SHOW INDEX FROM table_name;

其中，table_name是要查询索引的数据表名。执行该命令后，MySQL将返回包含表中所有索引信息的结果集，包括索引名称、索引类型、所属字段等。

例如，如果要查询名为students的数据表中的索引列表，可以执行以下命令：

SHOW INDEX FROM students;

执行该命令后，MySQL将返回类似于以下格式的结果集：

Table    Non_unique    Key_name      Seq_in_index     Column_name
-------  -----------  ------------  --------------  -----------------
students           0  PRIMARY                    1  id
students           1  idx_name                   1  name
students           1  idx_age                    1  age

其中，每行代表一个索引，各列的含义如下：

Table：索引所在的数据表名。
Non_unique：表示该索引是否允许重复值。0表示不允许，1表示允许。
Key_name：索引的名称。
Seq_in_index：索引中列的顺序。
Column_name：索引所涉及的列名。

通过这样的方式，你可以方便地查询表中所有的索引信息，以便分析和优化数据库性能。

常见问题

MySQL索引是提高查询性能的重要手段，但在使用索引的过程中容易出现以下几个常见错误：

错误地选择索引：不同类型的查询需要不同类型的索引来支持。如果选错了索引或者没有为查询添加任何索引，那么查询性能就会受到影响。
索引列类型不匹配：索引列的数据类型应该与查询条件的数据类型一致，否则可能会导致无法使用索引。
多列索引顺序错误：多列联合索引的列顺序非常重要，应该根据查询条件的先后顺序来确定列的顺序。如果列的顺序不正确，可能会导致某些查询无法使用索引。
过度索引：有时候为了提高查询性能，会创建大量的索引。但是，过度索引会降低写操作的性能，并且会占用大量的磁盘空间。应该仅创建必要的索引，以避免这些问题。
忘记更新索引：当修改数据表中的数据时，如果忘记更新相应的索引，就会导致索引和实际数据的不一致，进而降低查询性能。
不定期优化：随着数据量的增大，索引的性能可能会下降。因此，应该定期对索引进行优化和重建，以确保其性能和有效性。

索引效果

使用索引可以提高数据库查询的性能，特别是在处理大量数据时。一般来说，索引可以带来以下几方面的好处：

快速查找：当使用WHERE子句过滤记录时，索引可以加速查询，避免扫描整个表；
加速排序：当使用ORDER BY子句排序结果集时，索引可以加速排序操作；
避免重复：当使用UNIQUE索引时，可以避免插入重复的记录；
全文搜索：当使用FULLTEXT索引时，可以支持全文搜索操作。

需要注意的是，索引并不是越多越好，过多或不必要的索引会增加写入操作的成本（因为每次写入都需要更新索引），同时也会占用存储空间。因此，在创建、添加和删除索引时需要根据实际情况选择合适的索引策略。