MySQL 创建索引和索引效率验证

对于一张专门用于存储数据的表来说，数据量通常会很大。

项目中总会有一些表数据量非常大，并且在使用过程中，需要频繁地到这些表中查询数据。

数据量大的时候，查询速度会明显变慢，这时候就需要对查询速度进行优化了。

优化的方式很多，一个比较简单且低成本的方式就是创建索引。

一、索引简介

索引的目的是为了提高数据表的查询效率。

索引的作用类似于字典前面的拼音，笔画。拼音的顺序是固定的，在不知道一个字怎么写时，可以快速根据拼音来找到对应的字。看到一个字不知道怎么读时，通过前面的笔画，也可以快速找到对应的字。通过拼音或笔画找到一个字，与在一整本字典中找到一个字，这两种方式的速度差距是非常明显的。

索引可以通过层层的筛选，快速精确地定位到指定的数据，避免了查询数据时的全表扫描。这种定位速度是数量级的速度，数据量越大，效果越明显。

可见，索引的原理就是通过不断地缩小想要获得数据的范围来筛选出最终想要的结果。比如从1000中找到123，没有索引会从1开始逐一查找，直到找到123，有索引了先找到1开头的，再找第二个数是2的，然后找到123。索引的原理就类似这样，比全表扫描高效得多。

二、索引的使用

1. 连接数据库

先连接 mysql 数据库，进入到 MyDB_one 数据库中。

在 MyDB_one 中有一张表 Phone_table 。

2. 查看表中的索引

使用 show index from 表名; 查看表中的索引。

show index from Phone_table;

在上面的这张表中，现在还没有任何数据，所以也没有索引，查询结果为空 Empty 。

3. 批量插入数据

为了演示创建索引的方法和索引的效果，需要先在数据表中添加数据。而且，为了对比有无索引的效率差距，需要插入大量的数据，所以使用代码来批量插入数据到表中。

先使用 desc 表名; 查看表的字段。

desc Phone_table;

接下来使用 Python 的 pymysql 模块在数据库中插入数据。

使用 pymysql 进行数据库编程可以参考：

Python数据库编程pymysql

# coding=utf-8
from pymysql import connect


def insert_data():
    """
    在Phone_table中新增100000条数据
    """
    conn = connect(
        host="localhost",
        port=3306,
        user="admin",
        password="Mysql!123",
        database="MyDB_one",
        charset="utf8"
    )
    cursor = conn.cursor()
    for i in range(100000):
        sql = "insert into Phone_table values({id}, 'Phone{i}', 'black');".format(id=i, i=i)
        try:
            cursor.execute(sql)
            conn.commit()
        except Exception as e:
            print(e)
    cursor.close()
    conn.close()


if __name__ == '__main__':
    insert_data()

运行上面的代码，会在数据表 Phone_table 中插入十万条数据，运行需要一些时间，等待一会。

select * from Phone_table;

当数据量达到十万条时，查询所有数据，也花了一些时间。

4. 给数据表创建索引

使用 create index 索引名 on 表名(字段名称(长度)); 来创建索引。

如果指定的字段类型是字符串，需要指定长度，建议长度与数据表中定义字段时的长度一致。如果字段类型不是字符串，不用写长度。

create index phone_index on Phone_table(pid);

创建索引后，查看索引，可以看到刚创建的索引信息。

5. 删除索引

使用 drop index 索引名 on 表名; 来删除索引。

drop index phone_index on Phone_table;

删除索引后，数据表的索引为空。

三、验证索引的效率

有索引和没有索引的表查询效率差距是非常大的。在同一张表中，保持数据不变，查同一条数据，来对比有无索引的查询时间，就可以对比出效率的差异。

1. 开启 MySQL 运行时间监测

使用 set profiling=1; 开启 MySQL 运行时间检测，通过运行时间来对比有无索引的效率。

开启运行时间检测后，数据库操作的运行时间会保存在 information_schema 数据库的 profiling 表中，如果退出数据库连接，profiling 表中的数据会被自动删除。

set profiling=1;

设置之后，进行数据库操作的运行时间会被记录下来。

2. 没有创建索引时查询

select * from Phone_table where pid=77777;

从查询结果下面的时间可以看出是0.04秒。

3. 创建索引后查询

上面已经将刚才创建的索引删掉了，现在重新创建索引，然后执行相同的查询语句。

从查询结果下面的时间可以看到是0.00秒，所以说这个时间的精度不够，需要使用更精确的监测时间来查看。

4. 查看执行的时间

可以使用 show profiles; 查看运行时间。

也可以到 information_schema 数据库的 profiling 表中去查询，这个时间的精度比查询结果下显示的时间精度高很多，可以方便对比。

show profiles;

通过 show profiles; 查看，创建索引之前的查询时间是 0.03757600 秒，创建索引之后的查询时间是 0.00038250 秒，时间相差了 将近 100 倍，这就是索引带来的效率提升。(运行时间每次不一样，不过数量级一般不会变)

在实际的项目中，数据远比十万条要多，那样索引带来的效率提升效果会更明显。

当然，不是所有的表都需要建立索引。如果表中数据很少，没有必要建立索引。如果一个表中的数据增删很频繁，不适合索引 ，因为只要数据发生增减，索引就要重新建立，建立索引是很耗时的，频繁建立索引反而效率更低了。索引只适合查询操作频繁的表。