MySQL CREATE INDEX

摘要：在本教程中，您将了解索引以及如何使用 MySQL CREATE INDEX语句向表添加索引。

电话簿类比

假设您有一本电话簿，其中包含某个城市中人员的所有姓名和电话号码。假设您想查找 Bob Cat 的电话号码。知道姓名是按字母顺序排列的，您首先查找姓氏为Cat页面，然后查找Bob和他的电话号码。

现在，如果电话簿中的名字没有按字母顺序排序，您将需要浏览所有页面，阅读上面的每个名字，直到找到Bob Cat 。这称为顺序搜索。您检查所有条目，直到找到拥有您要查找的电话号码的人。

将电话簿与数据库表相关联，如果您有表phonebooks并且您必须找到Bob Cat的电话号码，您将执行以下查询：

SELECT
    phone_number
FROM 
    phonebooks
WHERE 
    first_name = 'Bob' AND 
    last_name = 'Cat';Code language: SQL (Structured Query Language) (sql)

这很容易。尽管查询速度很快，但数据库必须扫描表中的所有行，直到找到该行。如果表有数百万行，没有索引，数据检索将花费大量时间才能返回结果。

指数介绍

索引是一种数据结构，例如 B 树，它可以提高表上数据检索的速度，但需要额外的写入和存储来维护。

查询优化器可以使用索引来快速定位数据，而不必针对给定查询扫描表中的每一行。

当您创建带有主键或唯一键的表时，MySQL 会自动创建一个名为PRIMARY的特殊索引。该索引称为聚集索引。

PRIMARY索引比较特殊，因为索引本身与数据存储在同一个表中。聚集索引强制表中行的顺序。

PRIMARY索引以外的其他索引称为二级索引或非聚集索引。

MySQL CREATE INDEX语句

通常，您在创建表时创建索引。例如，以下语句创建一个新表，其索引由两列 c2 和 c3 组成。

CREATE TABLE t(
   c1 INT PRIMARY KEY,
   c2 INT NOT NULL,
   c3 INT NOT NULL,
   c4 VARCHAR(10),
   INDEX (c2,c3) 
);Code language: SQL (Structured Query Language) (sql)

要为一列或一组列添加索引，请使用CREATE INDEX语句，如下所示：

CREATE INDEX index_name ON table_name (column_list)Code language: SQL (Structured Query Language) (sql)

要为列或列列表创建索引，您需要指定索引名称、索引所属的表以及列列表。

例如，要为列 c4 添加新索引，请使用以下语句：

CREATE INDEX idx_c4 ON t(c4);Code language: SQL (Structured Query Language) (sql)

默认情况下，如果不指定索引类型，MySQL 将创建 B-Tree 索引。根据表的存储引擎，允许的索引类型如下所示：

请注意，上面的CREATE INDEX语句是 MySQL 引入的CREATE INDEX语句的简化版本。我们将在后续教程中介绍更多选项。

MySQL CREATE INDEX示例

以下语句查找职务为Sales Rep员工：

SELECT 
    employeeNumber, 
    lastName, 
    firstName
FROM
    employees
WHERE
    jobTitle = 'Sales Rep';Code language: SQL (Structured Query Language) (sql)

这是输出：

我们有 17 行，表明有 17 名职位为销售代表的员工。

要查看 MySQL 内部如何执行此查询，请在SELECT语句的开头添加EXPLAIN子句，如下所示：

正如您所看到的，MySQL 必须扫描由 23 行组成的整个表才能找到具有Sales Rep职位的员工。

现在，让我们使用CREATE INDEX语句为jobTitle列创建索引：

CREATE INDEX jobTitle ON employees(jobTitle);Code language: SQL (Structured Query Language) (sql)

并再次执行上面的语句：

EXPLAIN SELECT 
    employeeNumber, 
    lastName, 
    firstName
FROM
    employees
WHERE
    jobTitle = 'Sales Rep';Code language: SQL (Structured Query Language) (sql)

输出是：

正如您所看到的，MySQL 只需要从 key 列中指示的jobTitle索引中找到 17 行，而无需扫描整个表。

要显示表的索引，可以使用SHOW INDEXES语句，例如：

SHOW INDEXES FROM employees;Code language: SQL (Structured Query Language) (sql)

这是输出：

在本教程中，您了解了 MySQL 索引以及如何为表中的列添加索引。