MySQL:通过增加索引进行SQL查询优化

【实验】

一次非常有意思的SQL优化经历:从30248.271s到0.001s

数据准备

1、新建3张数据表

-- 课程表 数据 100条
drop table course;
create table course(
id int primary key auto_increment,
name varchar(10)
);
-- 学生表 数据 7w条
create table student(
id int primary key auto_increment,
name varchar(10)
);
-- 学生成绩表 数据 700w条
create table student_score(
id int primary key auto_increment,
course_id int,
student_id int,
score int
);

2、使用脚本生成数据

# -- coding: utf-8 --
"""
安装依赖包
pip install requests chinesename pythink pymysql

Windows 登陆mysql: winpty mysql -uroot -p
"""
import random

from chinesename import ChineseName
from pythink import ThinkDatabase

db_url = "mysql://root:123456@localhost:3306/demo?charset=utf8"
think_db = ThinkDatabase(db_url)

course_table = think_db.table("course")
student_table = think_db.table("student")
student_score_table = think_db.table("student_score")

# 准备课程数据
course_list = [{"name": "课程{}".format(i)} for i in range(100)]
ret = course_table.insert(course_list).execute()
print(ret)

# 准备学生数据
cn = ChineseName()
student_list = [{"name": name} for name in cn.getNameGenerator(70000)]
ret = student_table.insert(student_list).execute()
print(ret)

# 准备学生成绩
score_list = []
for i in range(1, 101):
for j in range(1, 70001):
item = {
"course_id": i,
"student_id": j,
"score": random.randint(0, 100)
}

score_list.append(item)

ret = student_score_table.insert(score_list, truncate=20000).execute()
print(ret)

think_db.close()

3、检查数据情况

mysql> select * from  course limit 10;
+----+-------+
| id | name |
+----+-------+
| 1 | 课程0 |
| 2 | 课程1 |
| 3 | 课程2 |
| 4 | 课程3 |
| 5 | 课程4 |
| 6 | 课程5 |
| 7 | 课程6 |
| 8 | 课程7 |
| 9 | 课程8 |
| 10 | 课程9 |
+----+-------+
10 rows in set (0.07 sec)

mysql> select * from student limit 10;
+----+--------+
| id | name |
+----+--------+
| 1 | 司徒筑 |
| 2 | 窦侗 |
| 3 | 毕珊 |
| 4 | 余怠 |
| 5 | 喻献 |
| 6 | 庾莫 |
| 7 | 蒙煮 |
| 8 | 芮佰 |
| 9 | 鄢虹 |
| 10 | 毕纣 |
+----+--------+
10 rows in set (0.05 sec)

mysql> select * from student_score order by id desc limit 10;
+---------+-----------+------------+-------+
| id | course_id | student_id | score |
+---------+-----------+------------+-------+
| 7000000 | 100 | 70000 | 24 |
| 6999999 | 100 | 69999 | 71 |
| 6999998 | 100 | 69998 | 33 |
| 6999997 | 100 | 69997 | 14 |
| 6999996 | 100 | 69996 | 97 |
| 6999995 | 100 | 69995 | 63 |
| 6999994 | 100 | 69994 | 35 |
| 6999993 | 100 | 69993 | 66 |
| 6999992 | 100 | 69992 | 58 |
| 6999991 | 100 | 69991 | 99 |
+---------+-----------+------------+-------+
10 rows in set (0.06 sec)

4、检查数据数量

mysql> select count(*) from student;
+----------+
| count(*) |
+----------+
| 70000 |
+----------+
1 row in set (0.02 sec)

mysql> select count(*) from course;
+----------+
| count(*) |
+----------+
| 100 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from student_score;
+----------+
| count(*) |
+----------+
| 7000000 |
+----------+
1 row in set (4.08 sec)