MySQL 创建索引

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

当使用主键或唯一键创建表时，它会自动创建一个名为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 。下表显示了基于表的存储引擎的不同类型的索引。

序列号	存储引擎	索引类型
1.	InnoDB	BTREE
2.	Memory/Heap	HASH，BTREE
3.	MYISAM	BTREE

三、MySQL CREATE INDEX 的示例

在此示例中，我们将创建一个表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;