MySQL CREATE INDEX示例

以下语句查找其职务为销售代表的员工：

SELECT 
    employeeNumber, 
    lastName, 
    firstName
FROM
    employees
WHERE
    jobTitle = 'Sales Rep';

这是输出：

我们有17行，表示17名员工的职务是销售代表。

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

如您所见，MySQL必须扫描整个表，该表由23行组成，以查找具有Sales Rep职务的员工。

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

CREATE INDEX jobTitle ON employees(jobTitle);

并再次执行以上语句：

EXPLAIN SELECT 
    employeeNumber, 
    lastName, 
    firstName
FROM
    employees
WHERE
    jobTitle = 'Sales Rep';

输出为：

如您所见，MySQL只需从key列中指示的jobTitle索引中定位17行，而无需扫描整个表。

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

SHOW INDEXES FROM employees;

这是输出：

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

电话簿类比

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

现在，如果电话簿中的名称不是按字母顺序排列的，则需要遍历所有页面，阅读其中的每个名称，直到找到Bob Cat。
这称为顺序搜索。
您遍历所有条目，直到找到具有您要查找的电话号码的人。

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

SELECT
    phone_number
FROM 
    phonebooks
WHERE 
    first_name = 'Bob' AND 
    last_name = 'Cat';

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

索引介绍

索引是一种数据结构，例如B-Tree，它以增加写入和存储以维护该表为代价，提高了表上数据检索的速度。

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

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

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

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

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

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) 
);

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

CREATE INDEX index_name ON table_name (column_list)

要为一列或一列列表创建索引，请指定索引名称，该索引所属的表以及列列表。

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

CREATE INDEX idx_c4 ON t(c4);

默认情况下，如果您不指定索引类型，MySQL会创建B树索引。
下面显示了基于表的存储引擎的允许索引类型：

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