【实验】
一次非常有意思的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)
优化测试
1、直接查询
select * from student
where id in (
select student_id from student_score where
course_id=1 and score=100
);
不知道为什么 2.7s 就执行完了… 原文中说 执行时间：30248.271s
马上看了下版本号，难道是版本的问题：
我的 ： Server version: 5.7.21
用 explain 看执行计划 type=all
explain extended
select * from student
where id in (
select student_id from student_score where
course_id=1 and score=100
);
# 执行完上一句之后紧接着执行
mysql> show warnings;
SELECT
`demo`.`student`.`id` AS `id`,
`demo`.`student`.`name` AS `name`
FROM
`demo`.`student` semi
JOIN ( `demo`.`student_score` )
WHERE
(
( `<subquery2>`.`student_id` = `demo`.`student`.`id` )
AND ( `demo`.`student_score`.`score` = 100 )
AND ( `demo`.`student_score`.`course_id` = 1 )
)
2、增加索引
单条大概执行15s
alter table student_score add index INDEX_COURSE_ID(course_id);
alter table student_score add index INDEX_SCORE(score);
加完索引之后执行 0.027s ，速度快了 100倍（2.7 / 0.027）
3、使用 inner join
用了 0.26
select s.id, s.name from student as s inner JOIN student_score as ss
on s.id=ss.student_id
where ss.course_id=1 and ss.score=100
4、再次优化
执行也是 0.26, 并没有像所说的那样 0.001s…难道他的机器比我好？
select s.id, s.name from
(select * from student_score where course_id=1 and score=100 ) as t
inner join student as s
on s.id=t.student_id
虽然很多不一致的地方，不过也算是一次加索引优化数据库查询的实际操作了
参考文章
一次非常有意思的SQL优化经历:从30248.271s到0.001s
