索引(一)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索引。 以下显示了基于表的存储引擎的允许索引类型：

 

四:MySQL CREATE INDEX 举例

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

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只需要在键列中指示的jobTitle索引中找到17行而不扫描整个表。

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

 SHOW INDEXES FROM employees;

返回结果如下的索引

image.png

这篇文章中我们介绍了MySQL索引以及如何为表中的列添加索引，希望大家可根据文末提供的数据源来自己敲写代码

作者：数据蛙datafrog
链接：https://www.jianshu.com/p/f588c41f1cb5
来源：简书