查询速度如此之慢

介绍

在本项目中，你将学习如何优化数据库查询以提高 LabEx 系统中的性能。LabEx 系统是一个管理用户学习记录、课程及其他相关数据的平台。该项目专注于优化系统中三个常用的查询。

👀 预览

🎯 任务

在本项目中，你将学习：

• 如何在 usercourse 表的 user 和 study_time 字段上添加复合索引以提高查询性能。
• 如何优化第一个查询，以查找特定用户在特定课程中的学习时间。
• 如何优化第二个查询，以按学习时间对用户课程的学习记录进行排序。
• 如何优化第三个查询，以检索用户学习过的所有课程。

🏆 成果

完成本项目后，你将能够：

• 理解索引在数据库优化中的重要性。
• 实现复合索引以提高复杂查询的性能。
• 分析查询执行计划以识别性能瓶颈。
• 通过利用适当的索引优化数据库查询。
• 应用你的知识来提高实际数据库应用程序的性能。

添加复合索引

在本步骤中，你将学习如何在 usercourse 表的 user 和 study_time 字段上添加复合索引。

1. 启动 MySQL 服务。

   sudo /etc/init.d/mysql start

2. 登录 MySQL 终端。

   mysql -uroot

3. 将提供的 initDatabase.sql 脚本中的数据导入到 MySQL 数据库：

SOURCE /home/labex/project/initDatabase.sql

4. 在 /home/labex/project 目录中创建 addIndex.sql 文件。

5. 在 addIndex.sql 文件中，添加以下 SQL 语句以创建复合索引：

   ALTER TABLE labex.usercourse ADD INDEX ix_user_study_time (user, study_time);

   此语句在 usercourse 表的 user 和 study_time 字段上创建一个名为 ix_user_study_time 的复合索引。

6. 保存 addIndex.sql 文件。

7. 在 MySQL 终端中，运行以下命令来执行 SQL 脚本：

   SOURCE /home/labex/project/addIndex.sql

   这将在数据库中创建复合索引。

8. 通过运行以下 SQL 语句验证索引创建：

   SHOW INDEX FROM usercourse;

   输出应显示新的复合索引。

   MariaDB [labex]> SHOW INDEX FROM usercourse;
   +------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
   | Table      | Non_unique | Key_name           | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Ignored |
   +------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
   | usercourse |          0 | PRIMARY            |            1 | ID          | A         |          10 |     NULL | NULL   |      | BTREE      |         |               | NO      |
   | usercourse |          1 | ix_user_study_time |            1 | user        | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
   | usercourse |          1 | ix_user_study_time |            2 | study_time  | A         |          10 |     NULL | NULL   | YES  | BTREE      |         |               | NO      |
   +------------+------------+--------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+
   3 rows in set (0.001 sec)

优化查询 1 - 查找用户 A 学习课程 B 的时间

在本步骤中，你将学习如何优化第一个查询，以查找用户 A 在课程 B 中的学习时间。

1. 打开 MySQL 终端。

2. 解释原始查询：

   EXPLAIN SELECT study_time
   FROM usercourse
   WHERE user = 'A' AND course = 'B';

   这将显示原始查询的执行计划，你可以使用它来识别潜在的性能瓶颈。

3. 修改查询以使用上一步中创建的复合索引：

   SELECT study_time
   FROM usercourse
   WHERE user = 'A' AND course = 'B';

   user 和 study_time 上的复合索引将帮助数据库有效地定位相关行并检索 study_time 值。

4. 解释修改后的查询：

   EXPLAIN SELECT study_time
   FROM usercourse
   WHERE user = 'A' AND course = 'B';

   输出应显示数据库正在使用 ix_user_study_time 索引来执行查询，这应该会带来更好的性能。

   MariaDB [labex]> EXPLAIN SELECT study_time
    -> FROM usercourse
    -> WHERE user = 'A' AND course = 'B';
   +------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
   | id   | select_type | table      | type | possible_keys      | key  | key_len | ref  | rows | Extra       |
   +------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
   |    1 | SIMPLE      | usercourse | ALL  | ix_user_study_time | NULL | NULL    | NULL | 10   | Using where |
   +------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
   1 row in set (0.000 sec)
   
   MariaDB [labex]> SELECT study_time
       -> FROM usercourse
       -> WHERE user = 'A' AND course = 'B';
   +------------+
   | study_time |
   +------------+
   | 50         |
   | 130        |
   | 60         |
   | 70         |
   | 80         |
   | 90         |
   | 100        |
   | 110        |
   | 120        |
   | 50         |
   +------------+
   10 rows in set (0.000 sec)
   
   MariaDB [labex]> EXPLAIN SELECT study_time
       -> FROM usercourse
       -> WHERE user = 'A' AND course = 'B';
   +------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
   | id   | select_type | table      | type | possible_keys      | key  | key_len | ref  | rows | Extra       |
   +------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
   |    1 | SIMPLE      | usercourse | ALL  | ix_user_study_time | NULL | NULL    | NULL | 10   | Using where |
   +------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
   1 row in set (0.000 sec)

优化查询 2 - 按学习时间对用户 A 的课程学习记录进行排序

在本步骤中，你将学习如何优化第二个查询，以按学习时间对用户 A 的课程学习记录进行排序。

1. 打开 MySQL 终端。

2. 解释原始查询：

   EXPLAIN SELECT *
   FROM usercourse
   WHERE user = 'A'
   ORDER BY study_time;

   这将显示原始查询的执行计划，你可以使用它来识别潜在的性能瓶颈。

3. 修改查询以使用第一步中创建的复合索引：

   SELECT *
   FROM usercourse
   WHERE user = 'A'
   ORDER BY study_time;

   user 和 study_time 上的复合索引将帮助数据库有效地定位相关行，并按 study_time 对它们进行排序。

4. 解释修改后的查询：

   EXPLAIN SELECT *
   FROM usercourse
   WHERE user = 'A'
   ORDER BY study_time;

   输出应显示数据库正在使用 ix_user_study_time 索引来执行查询，这应该会带来更好的性能。

   MariaDB [labex]> EXPLAIN SELECT *
    -> FROM usercourse
    -> WHERE user = 'A'
    -> ORDER BY study_time;
   +------+-------------+------------+------+--------------------+------+---------+------+------+-----------------------------+
   | id   | select_type | table      | type | possible_keys      | key  | key_len | ref  | rows | Extra                       |
   +------+-------------+------------+------+--------------------+------+---------+------+------+-----------------------------+
   |    1 | SIMPLE      | usercourse | ALL  | ix_user_study_time | NULL | NULL    | NULL | 10   | Using where; Using filesort |
   +------+-------------+------------+------+--------------------+------+---------+------+------+-----------------------------+
   1 row in set (0.000 sec)
   
   MariaDB [labex]> SELECT *
       -> FROM usercourse
       -> WHERE user = 'A'
       -> ORDER BY study_time;
   +----+------+--------+------------+
   | ID | user | course | study_time |
   +----+------+--------+------------+
   | 6  | A    | B      | 100        |
   | 7  | A    | B      | 110        |
   | 8  | A    | B      | 120        |
   | 10 | A    | B      | 130        |
   | 1  | A    | B      | 50         |
   | 9  | A    | B      | 50         |
   | 2  | A    | B      | 60         |
   | 3  | A    | B      | 70         |
   | 4  | A    | B      | 80         |
   | 5  | A    | B      | 90         |
   +----+------+--------+------------+
   10 rows in set (0.000 sec)
   
   MariaDB [labex]> EXPLAIN SELECT *
       -> FROM usercourse
       -> WHERE user = 'A'
       -> ORDER BY study_time;
   +------+-------------+------------+------+--------------------+------+---------+------+------+-----------------------------+
   | id   | select_type | table      | type | possible_keys      | key  | key_len | ref  | rows | Extra                       |
   +------+-------------+------------+------+--------------------+------+---------+------+------+-----------------------------+
   |    1 | SIMPLE      | usercourse | ALL  | ix_user_study_time | NULL | NULL    | NULL | 10   | Using where; Using filesort |
   +------+-------------+------------+------+--------------------+------+---------+------+------+-----------------------------+
   1 row in set (0.000 sec)

优化查询 3 - 查询用户 A 学习过的所有课程

在本步骤中，你将学习如何优化第三个查询，以检索用户 A 学习过的所有课程。

1. 打开 MySQL 终端。

2. 解释原始查询：

   EXPLAIN SELECT course
   FROM usercourse
   WHERE user = 'A';

   这将显示原始查询的执行计划，你可以使用它来识别潜在的性能瓶颈。

3. 修改查询以使用第一步中创建的复合索引：

   SELECT DISTINCT course
   FROM usercourse
   WHERE user = 'A';

   user 和 study_time 上的复合索引将帮助数据库有效地定位相关行，并检索不同的课程值。

4. 解释修改后的查询：

   EXPLAIN SELECT DISTINCT course
   FROM usercourse
   WHERE user = 'A';

   输出应显示数据库正在使用 ix_user_study_time 索引来执行查询，这应该会带来更好的性能。

   MariaDB [labex]> EXPLAIN SELECT course
    -> FROM usercourse
    -> WHERE user = 'A';
   +------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
   | id   | select_type | table      | type | possible_keys      | key  | key_len | ref  | rows | Extra       |
   +------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
   |    1 | SIMPLE      | usercourse | ALL  | ix_user_study_time | NULL | NULL    | NULL | 10   | Using where |
   +------+-------------+------------+------+--------------------+------+---------+------+------+-------------+
   1 row in set (0.000 sec)
   
   MariaDB [labex]> SELECT DISTINCT course
       -> FROM usercourse
       -> WHERE user = 'A';
   +--------+
   | course |
   +--------+
   | B      |
   +--------+
   1 row in set (0.000 sec)
   
   MariaDB [labex]> EXPLAIN SELECT DISTINCT course
       -> FROM usercourse
       -> WHERE user = 'A';
   +------+-------------+------------+------+--------------------+------+---------+------+------+------------------------------+
   | id   | select_type | table      | type | possible_keys      | key  | key_len | ref  | rows | Extra                        |
   +------+-------------+------------+------+--------------------+------+---------+------+------+------------------------------+
   |    1 | SIMPLE      | usercourse | ALL  | ix_user_study_time | NULL | NULL    | NULL | 10   | Using where; Using temporary |
   +------+-------------+------------+------+--------------------+------+---------+------+------+------------------------------+
   1 row in set (0.000 sec)

通过遵循这些步骤，你已经学会了如何添加复合索引并优化 LabEx 系统中的三个查询，以提高它们的性能。

总结

恭喜！你已完成本项目。你可以在 LabEx 中练习更多实验以提升技能。