MySQL三表关联查询实现10万级数据查询（mysql 三表 10w）

MySQL三表关联查询实现10万级数据查询

随着数据量的增长，如何高效查询数据成为了开发人员面临的重要问题。MySQL作为一款广泛使用的关系型数据库，其三表关联查询功能可以很好地解决大规模数据量的查询问题。

本文将介绍如何使用三表关联查询实现10万级数据查询，并提供相应的示例代码。

一、创建数据表

本次示例将创建3个数据表，分别为学生表、课程表和成绩表。

学生表（student）：

“`sql

CREATE TABLE student (

id INT(11) NOT NULL AUTO_INCREMENT,

name VARCHAR(100) DEFAULT NULL,

age INT(11) DEFAULT NULL,

PRIMARY KEY (id)

) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

课程表（course）：

```sql
CREATE TABLE course (
  id INT(11) NOT NULL AUTO_INCREMENT,
  name VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

成绩表（score）：

“`sql

CREATE TABLE score (

id INT(11) NOT NULL AUTO_INCREMENT,

student_id INT(11) DEFAULT NULL,

course_id INT(11) DEFAULT NULL,

score INT(11) DEFAULT NULL,

PRIMARY KEY (id),

KEY idx_student_course (student_id,course_id),

CONSTRNT fk_student FOREIGN KEY (student_id) REFERENCES student (id),

CONSTRNT fk_course FOREIGN KEY (course_id) REFERENCES course (id)

) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

二、插入测试数据

为了测试三表关联查询的效果，我们需要先往三个数据表中插入一些测试数据。本次示例将插入10万条学生数据、10门课程数据和100万条成绩数据。

插入学生数据：

```sql
INSERT INTO `student` (`name`, `age`) VALUES ('张三', '18');
INSERT INTO `student` (`name`, `age`) VALUES ('李四', '19');
INSERT INTO `student` (`name`, `age`) VALUES ('王五', '20');
...

插入课程数据：

“`sql

INSERT INTO `course` (`name`) VALUES (‘语文’);

INSERT INTO `course` (`name`) VALUES (‘数学’);

INSERT INTO `course` (`name`) VALUES (‘英语’);

…

插入成绩数据（注意：该步骤需要执行100次）：

```sql
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES ('1', '1', '90');
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES ('1', '2', '80');
INSERT INTO `score` (`student_id`, `course_id`, `score`) VALUES ('1', '3', '70');
...

三、三表关联查询

完成数据插入后，我们可以通过三表关联查询语句来查看学生的成绩情况，例如查询“张三”的成绩：

“`sql

SELECT student.name, course.name, score.score FROM student

JOIN score ON student.id = score.student_id

JOIN course ON score.course_id = course.id

WHERE student.name = ‘张三’;

这条语句会查询到“张三”在所有课程中的成绩，结果如下：

+——–+——–+——-+

| name | name | score |

+——–+——–+——-+

| 张三 | 语文 | 90 |

| 张三 | 数学 | 80 |

| 张三 | 英语 | 70 |

+——–+——–+——-+

四、优化查询效率

在查询大规模数据量时，为了提高查询效率，可以采用以下方法：

1. 建立索引：对经常进行查询的字段建立索引，可以提高查询效率。

2. 优化语句：尽量避免使用子查询、全表扫描等效率较低的语句，可以使用EXPLN命令来查看查询语句的执行计划。

3. 分页查询：对于数据量较大的情况，可以采用分页查询来降低查询压力，例如LIMIT语句。

下面是使用分页查询的示例语句：

```sql
SELECT student.name, course.name, score.score FROM student 
JOIN score ON student.id = score.student_id 
JOIN course ON score.course_id = course.id
ORDER BY score.score DESC
LIMIT 0, 100;

该语句会查询出所有学生在所有课程中的成绩，并按照成绩降序排列，最后取出前100条结果。如果想查询后100条结果，可以改为LIMIT 100, 100。

五、总结

本文介绍了如何使用MySQL的三表关联查询来实现大规模数据量的查询，同时也提供了相应的示例代码。在实际开发中，需要针对具体情况进行查询语句的优化，以提高查询效率。