MySQL系列11——索引效率测试

MySQL索引效率测试

一、以mysql添加索引和不添加索引为例，测试数据的插入速度和查询速度。

1、首先创建数据表和数据库，在cmd命令行中执行：

create database TestDataSpeed charset=utf8;
use testdataspeed;
create table DataNoIndex(id int,name char(10),age int,address char(20),gender char(1)); #创建不带索引的数据表
create table DataNoIndex(id int primary key autoincrement,name char(10),age int,address char(20),gender char(1));  # 创建带主键索引的数据表

2、创建完数据库之后，使用pymysql操作分别插入1000万条数据：

首先分别向无索引和有索引的表中插入数据，在无索引的表中插入1000万条数据共耗时1134秒，在有索引的表中插入共耗时1138秒，插入数据时耗用时间相差不大。：

# -*- coding: utf-8 -*-
import pymysql
import time
from functools import wraps
import random
import traceback

def timer(func):
    @wraps(func)
    def inner(*args):
        start_time = time.time()
        func(*args)
        end_time = time.time()
        print(end_time-start_time)
    return inner

@timer
def insert_no_index(*args):
    try:
        db = pymysql.connect(user=user,password=password,database="testdataspeed")
        cursor = db.cursor()
        for i in range(1,10*1000*1000):
            age = random.randint(10,25)
            gender = random.choice(["男","女"])
            address = random.choice(["北京","上海","广州","杭州","深圳","成都","重庆"])
            sql = "INSERT INTO DATANOINDEX VALUE('{}','{}','{}','{}','{}');".format(i,"小明"+str(i),age,address,gender)
            # print(sql)
            cursor.execute(sql)
        db.commit()
        db.close()
    except:
        print(traceback.format_exc())

if __name__ == '__main__':
    user = "root"
    password = "root"
    insert_no_index(user,password)

3、比较查询速度

1. 比较查询所有数据的速度

   无索引情况：

   mysql> select count(gender) from datanoindex;
   +---------------+
   | count(gender) |
   +---------------+
   |       9999999 |
   +---------------+
   1 row in set (7.67 sec)
   

   有索引情况：

   mysql> select count(gender) from dataindex;
   +---------------+
   | count(gender) |
   +---------------+
   |       9999999 |
   +---------------+
   1 row in set (6.42 sec)
   

2. 比较使用where查询主键条件的速度

   无索引情况：

   mysql> select *  from datanoindex where id=1000;
   +------+----------+------+---------+--------+
   | id   | name     | age  | address | gender |
   +------+----------+------+---------+--------+
   | 1000 | 小明1000 |   25 | 广州    | 男     |
   +------+----------+------+---------+--------+
   1 row in set (8.10 sec)
   

   有索引情况：

   mysql> select * from dataindex where id=1000;
   +------+----------+------+---------+--------+
   | id   | name     | age  | address | gender |
   +------+----------+------+---------+--------+
   | 1000 | 小明1000 |   22 | 北京    | 男     |
   +------+----------+------+---------+--------+
   1 row in set (0.00 sec)
   

   可以看到，在添加索引之后，使用主键进行查询时，速度得到了显著提升。

3. 比较使用非索引键查询的速度

   无索引的情况：

   mysql> select *  from datanoindex where name="小明1000";
   +------+----------+------+---------+--------+
   | id   | name     | age  | address | gender |
   +------+----------+------+---------+--------+
   | 1000 | 小明1000 |   25 | 广州    | 男     |
   +------+----------+------+---------+--------+
   1 row in set (8.95 sec)
   

   有索引情况：

   mysql> select * from dataindex where name="小明1000";
   +------+----------+------+---------+--------+
   | id   | name     | age  | address | gender |
   +------+----------+------+---------+--------+
   | 1000 | 小明1000 |   22 | 北京    | 男     |
   +------+----------+------+---------+--------+
   1 row in set (7.15 sec)
   

   可以看到，在有索引的表中，即使不使用索引的字段进行查询，查询速度依然优于无索引的表，只是效率提升相对较少。

4. 向有1000万数据的表中添加主键索引

   向无索引的表中添加主键索引，测试需要花费的时间。

   mysql> alter table datanoindex add primary key(id);
   Query OK, 0 rows affected (2 min 45.44 sec)
   Records: 0  Duplicates: 0  Warnings: 0
   

   大约两分钟45秒可以把索引建立完成。此时再使用主键进行查询：

   mysql> select * from datanoindex where id=100000;
   +--------+------------+------+---------+--------+
   | id     | name       | age  | address | gender |
   +--------+------------+------+---------+--------+
   | 100000 | 小明100000 |   19 | 成都    | 女     |
   +--------+------------+------+---------+--------+
   1 row in set (0.10 sec)
   

   查询速度有了显著的提升，这就是索引的优点，当数据越大时，提升的效果越明显。