【实验】
一次非常有意思的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)
