MySQL系列教程 --- 40、如何在 MySQL 中创建索引

索引是一种数据结构，它允许我们在现有表中添加索引。它使您能够更快地检索数据库表上的记录。它为索引列的每个值创建一个条目。我们使用它来快速查找记录，而无需在访问表时搜索数据库表中的每一行。我们可以通过使用表的一列或多列来创建索引，以便有效地访问记录。

当使用主键或唯一键创建表时，它会自动创建一个名为PRIMARY的特殊索引。我们称这个索引为聚集索引。PRIMARY 索引以外的所有索引都称为非聚集索引或二级索引。

需要在 MySQL 中建立索引

假设我们有一个包含用户姓名和手机号码的通讯录。在这本通讯录中，我们想找到 Martin Williamson 的手机号码。如果通讯录是无序格式意味着通讯录的名称不是按字母顺序排序的，我们需要翻阅所有页面并阅读每个名称，直到我们找不到所需的名称为止。这种类型的搜索名称称为顺序搜索。

要从表contactbooks 中查找用户的姓名和联系人，通常我们执行以下查询：

mysql> SELECT mobile_number FROM contactbooks WHERE first_name = 'Martin' AND last_name = 'Taybu';  

这个查询非常简单和容易。虽然它可以快速找到用户的电话号码和姓名，但数据库会搜索表的整行，直到找不到您想要的行。假设，contactbooks 表包含数百万行，那么在没有索引的情况下，数据检索需要花费大量时间才能找到结果。在这种情况下，数据库索引在返回所需结果和提高查询的整体性能方面起着重要作用。

MySQL CREATE INDEX 语句

一般我们在数据库中建表的时候就创建一个索引。以下语句创建一个表，其索引包含两列 col2 和 col3。

mysql> CREATE TABLE t_index(  
   col1 INT PRIMARY KEY,  
   col2 INT NOT NULL,  
   col3 INT NOT NULL,  
   col4 VARCHAR(20),  
   INDEX (col2,col3)   
);  

如果我们想在表中添加索引，我们将使用 CREATE INDEX 语句，如下所示：

mysql> CREATE INDEX [index_name] ON [table_name] (column names)  

在该语句中，index_name是索引的名称，table_name是索引所属的表的名称，column_names是列的列表。

让我们为列 col4 添加新索引，我们使用以下语句：

mysql> CREATE INDEX ind_1 ON t_index(col4);  

默认情况下，如果我们没有指定索引类型，MySQL允许索引类型为BTREE。下表显示了基于表的存储引擎的不同类型的索引。

例子

在此示例中，我们将创建一个表student并对该表执行 CREATE INDEX 语句。

表名：student

现在，执行以下语句以返回类为CS 分支的学生的结果：

mysql> SELECT studentid, firstname, lastname FROM student WHERE class = 'CS';  

此语句将提供以下输出：

在上表中，我们可以看到四行表示所在班级为 CS 分支的学生。

如果要查看 MySQL 在内部如何执行此查询，请执行以下语句：

mysql> EXPLAIN SELECT studentid, firstname, lastname FROM student WHERE class = 'CS';  

你会得到下面的输出。在这里，MySQL 扫描包含 7 行的整个表以查找其班级为 CS 分支的学生。

现在，让我们使用以下语句为类列创建索引。

mysql> CREATE INDEX class ON student (class);  

执行上述语句后，索引创建成功。现在，运行以下语句以查看 MySQL 在内部如何执行此查询。

mysql> EXPLAIN SELECT studentid, firstname, lastname FROM student WHERE class = 'CS';  

上面的语句给出了输出，如下图：

在此输出中，MySQL 从类索引中找到四行，而无需扫描整个表。因此，它提高了在数据库表上检索记录的速度。

如果要显示表的索引，请执行以下语句：

mysql> SHOW INDEXES FROM student;  

它将给出以下输出。