mysql 创建job_MySQL 索引创建

MySQL 索引创建

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

电话簿比喻

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

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

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

SELECT

phone_number

FROM

phone_book

WHERE

first_name = 'Bob' AND

last_name = 'Cat';

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

索引简介

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

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

当您使用主键或唯一键创建表时，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)

);

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

CREATE INDEX index_name ON table_name (column_list)

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

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

CREATE INDEX idx_c4 ON t(c4);

默认情况下，如果未指定索引类型，MySQL将创建B-Tree索引。以下显示了基于表的存储引擎的允许索引类型：

存储引擎

允许的索引类型

InnoDB

BTREE

MyISAM

BTREE

MEMORY / HEAP

HASH，BTREE

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

MySQL CREATE INDEX示例

以下声明查找职位名称为的员工Sales Rep：

SELECT

employeeNumber,

lastName,

firstName

FROM

employees

WHERE

jobTitle = 'Sales Rep';

这是输出：

+----------------+-----------+-----------+

| employeeNumber | lastName | firstName |

+----------------+-----------+-----------+

| 1165 | Jennings | Leslie |

| 1166 | Thompson | Leslie |

| 1188 | Firrelli | Julie |

| 1216 | Patterson | Steve |

| 1286 | Tseng | Foon Yue |

| 1323 | Vanauf | George |

| 1337 | Bondur | Loui |

| 1370 | Hernandez | Gerard |

| 1401 | Castillo | Pamela |

| 1501 | Bott | Larry |

| 1504 | Jones | Barry |

| 1611 | Fixter | Andy |

| 1612 | Marsh | Peter |

| 1619 | King | Tom |

| 1621 | Nishi | Mami |

| 1625 | Kato | Yoshimi |

| 1702 | Gerard | Martin |

+----------------+-----------+-----------+

17 rows in set (0.01 sec)

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

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

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 23 | 10.00 | Using where |

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.01 sec)

正如您所看到的，MySQL必须扫描包含23行的整个表，以查找具有Sales Rep职位的员工。

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

CREATE INDEX jobTitle ON employees(jobTitle);

并再次执行上述语句：

EXPLAIN SELECT

employeeNumber,

lastName,

firstName

FROM

employees

WHERE

jobTitle = 'Sales Rep';

输出是：

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

| 1 | SIMPLE | employees | NULL | ALL | jobTitle | NULL | NULL | NULL | 23 | 73.91 | Using where |

+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+

1 row in set, 1 warning (0.00 sec)

正如您所看到的，MySQL只需要从jobTitle键列中找到17行，   而不是扫描整个表。

要显示表的索引，请使用

SHOW INDEXES FROM employees;

这是输出：

+-----------+------------+------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |

+-----------+------------+------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

| employees | 0 | PRIMARY | 1 | employeeNumber | A | 23 | NULL | NULL | | BTREE | | |

| employees | 1 | reportsTo | 1 | reportsTo | A | 7 | NULL | NULL | YES | BTREE | | |

| employees | 1 | officeCode | 1 | officeCode | A | 7 | NULL | NULL | | BTREE | | |

| employees | 1 | jobTitle | 1 | jobTitle | A | 9 | NULL | NULL | | BTREE | | |

+-----------+------------+------------+--------------+----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

4 rows in set (0.00 sec)

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