【实验】
一次非常有意思的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 原文：mysql 5.6
用 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
虽然和原文很多不一致的地方，不过也算是一次加索引优化数据库查询的实际操作了
参考文章
