MySQL优化---将理论付诸实践

前些天，整理了MySQL数据库开发规范，理论多，实践少。本文旨在通过实践案例，用真实数据来证明理论的可用性。

主要参考文章：一次非常有意思的sql优化经历

数据库设计：

MySQL版本：8.0.12

课程表 course

CREATE TABLE `course` (
  `c_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(10) CHARACTER DEFAULT NULL COMMENT '课程名称',
  PRIMARY KEY (`c_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='课程表'

学生表 student

CREATE TABLE `student` (
  `s_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(10) CHARACTER DEFAULT NULL COMMENT '学生姓名',
  PRIMARY KEY (`s_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='学生表'

分数表 score

CREATE TABLE `score` (
  `sc_id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '分数id',
  `s_id` int(11) COMMENT '学生id',
  `c_id` int(11) COMMENT '课程id',
  `score` int(11) COMMENT '分数',
  PRIMARY KEY (`sc_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='分数表'

数据库数据导入：

主要使用SpringBoot + MyBatis，将按照一定规则随机生成的数据，插入到数据库中；
课程表数据：25条，学生表数据：70000条，分数表数据：700000条；
如果想自己动手测试，可以下载代码：https://download.****.net/download/tian330726/11055862

优化过程

查询目的

查找计算机基础考100分的考生

查询语句

SELECT s.* FROM student s WHERE s.s_id IN (SELECT s_id FROM score sc WHERE sc.c_id = 1 AND sc.score = 100)
> OK
> 时间: 0.332s

查看查询计划

增加索引

CREATE index sc_c_id_index on score(c_id)
> OK
> 时间: 5.714s

CREATE index sc_score_index on score(score)
> OK
> 时间: 6.321s

重新查询

SELECT s.* FROM student s WHERE s.s_id IN (SELECT s_id FROM score sc WHERE sc.c_id = 1 AND sc.score = 100)
> OK
> 时间: 0.025s

查看查询计划

添加索引后，查询时间减少了十倍。

使用连接查询

SELECT s.* from student s INNER JOIN score sc on sc.s_id = s.s_id where sc.c_id=1 and sc.score=100
> OK
> 时间: 0.025s

查询查询计划

由于使用MySQL的版本（8.0.12）与参考文章中5.6版本差别较大，故结果相差较大，等待研究新版本特性，继续将文章更新。