Azure MySQL慢查询优化：多表关联查询性能提升咨询

针对这条耗时3-5分钟的Azure MySQL查询，结合执行计划给出以下优化方案：

1. 修正User表的索引选择 #

执行计划显示User表使用了mdl_user_del_ix索引做全索引扫描，而非主键PRIMARY。主键索引的关联效率远高于普通索引，可强制优化器使用主键索引：

SELECT u.Id, cmc.completionstate, cmc.coursemoduleid
FROM User u FORCE INDEX(PRIMARY)
JOIN role_assignments ra ON u.id = ra.userid
LEFT JOIN course_modules_completion cmc ON u.id = cmc.userid

如果mdl_user_del_ix是用于标记删除状态的索引，可检查是否存在隐式类型转换、删除标记字段过滤条件缺失等导致优化器误选索引的情况。

2. 给course_modules_completion表创建覆盖索引 #

当前cmc表仅用userid字段的索引，但查询需要返回completionstate和coursemoduleid，每次查询都需回表读取数据，增加IO开销。创建包含所需字段的覆盖索引：

CREATE INDEX idx_cmc_userid_state_course ON course_modules_completion(userid, completionstate, coursemoduleid);

创建后，执行计划中cmc表的Extra会显示Using index，无需回表直接从索引获取数据，大幅提升查询速度。

3. 移除无意义的LIMIT语句 #

LIMIT 1000000000等同于不做限制，反而会让优化器做额外的行数计算，直接删除该语句即可。

4. 检查Azure MySQL实例配置与资源 #

• 调整innodb_buffer_pool_size至实例内存的50%-70%，让常用数据缓存到内存，减少磁盘IO。
• 查看实例CPU、内存使用率，若持续高负载，考虑升级Azure MySQL实例规格（增加vCore或内存）。
• 若业务允许，将查询路由到Azure MySQL只读副本，分担主库压力。

5. 优化关联逻辑（可选） #

内连接role_assignments会过滤掉无角色分配的用户，若role_assignments去重后的用户数远少于User表总数，可先筛选出有效用户再关联：

SELECT u.Id, cmc.completionstate, cmc.coursemoduleid
FROM (SELECT DISTINCT userid FROM role_assignments) ra
JOIN User u ON u.id = ra.userid
LEFT JOIN course_modules_completion cmc ON u.id = cmc.userid

需根据实际数据量测试效果。

内容的提问来源于stack exchange，提问作者S. gupta