RDS慢查询日志扫描行数远高于EXPLAIN结果的排查求助

我有一个通过后台脚本执行的存储过程procGetBookingForNoShowCharge，它出现在AWS Top Sql中，但在生产服务器手动执行EXPLAIN时，显示的扫描行数极少。慢查询日志显示该存储过程执行耗时15.6秒，扫描231570行但返回0行，问题反复出现，急需解决方案。

存储过程代码 #

DROP PROCEDURE IF EXISTS procGetBookingForNoShowCharge;
DELIMITER $$
CREATE PROCEDURE procGetBookingForNoShowCharge()
BEGIN
    SELECT
        t11.*
    FROM
    (
        SELECT
            t0.CalendarEventBookingId,
            t0.CalendarEventId,
            t0.BookedFor,
            t0.UserId,
            t0.IsGuest,
            t0.GuestOf,
            t0.Status,
            t0.FitnessCenterId,
            t0.Department,
            t1.BookingType,
            t1.BookingTypeId,
            t1.EventName,
            t1.StartDateTime,
            t1.EndDateTime,
            t1.StartDateTimeInUtc,
            t1.Duration,
            t1.Source,
            t1.SourceId,
            t2.Status AS AttendanceStatus,
            t2.AttendanceTime,
            t3.SendMessageToNoShowMember,
            t4.FitnessCompanyId,
            t4.FitnessCompanyName,
            t4.BackendSoftware,
            t4.BackendSoftwareUrl,
            t4.BackendSoftwareId,
            t6.AbcFinancialClubNumber,
            t4.ActiveMemberStatus,
            t4.StripeConnectAccountType,
            t4.StripeConnectAccountId,
            t4.StripeShcConvenienceFee,
            t6.FitnessCenterName,
            t6.TimeZone,
            IFNULL(t7.ResourceName, t1.LocationName) AS Location,
            t8.Email,
            t8.StripeDefaultCardId,
            t8.StripeCustomerId,
            t8.StripeStandardDefaultCardId,
            t8.StripeStandardCustomerId,
            t8.TimeFirstLogin,
            t8.ContactStatus,
            t8.IsAuthenticated,
            t8.BounceType,
            t9.FullName
        FROM
            CalendarEventBooking t0
            INNER JOIN CalendarEvent t1 ON t1.CalendarEventId = t0.CalendarEventId
            LEFT JOIN CalendarEventAttendance t2 ON t2.CalendarEventId = t0.CalendarEventId AND t2.AttendedFor = t0.BookedFor AND t2.UserId = t0.UserId AND t2.MemberId = t0.MemberId AND t2.UserName = t0.UserName AND t2.IsGuest = t0.IsGuest
            INNER JOIN BookingSetting t3 ON t3.BookingType = t1.BookingType AND t3.BookingSubType = t0.BookedFor AND t3.BookingTypeId = t1.BookingTypeId AND t3.FitnessCompanyId = t1.FitnessCompanyId
            INNER JOIN FitnessCompany t4 ON t4.FitnessCompanyId = t1.FitnessCompanyId
            LEFT JOIN BookingAndAttendancePolicy t5 ON t5.FitnessCompanyId = t4.FitnessCompanyId AND FIND_IN_SET(t0.Department, t5.Department) AND t5.Type = 'classes'
            INNER JOIN FitnessCenter t6 ON t6.FitnessCenterId = t0.FitnessCenterId
            LEFT JOIN FitnessCenterResource t7 ON t7.FitnessCenterId = t6.FitnessCenterId AND t7.ResourceId = t1.ResourceId
            INNER JOIN Users t8 ON t8.UserId = IF(t0.IsGuest, t0.GuestOf, t0.UserId)
            INNER JOIN Profile t9 ON t9.UserId = t8.UserId AND t9.FitnessCompanyId = t8.FitnessCompanyId
        WHERE
            t0.Status = 'confirmed'
            AND
            (
                t0.NoShowChargeStatus IS NULL
                OR t0.NoShowChargeStatus = 'pending'
            )
            AND t1.Source IN ('Google', 'AbcFinancial')
            AND t1.BookingType = 'classes'
            AND t1.EventStatus IN ('open', 'completed')
            AND t3.SendMessageToNoShowMember IS NOT NULL
            AND t1.StartDateTimeInUtc <= DATE_SUB(NOW(), INTERVAL t3.SendMessageToNoShowMember HOUR)
            AND t1.StartDateTimeInUtc >= t3.NoShowMemberStartDateTime
        UNION ALL
        SELECT
            t0.CalendarEventBookingId,
            t0.CalendarEventId,
            t0.BookedFor,
            t0.UserId,
            t0.IsGuest,
            t0.GuestOf,
            t0.Status,
            t0.FitnessCenterId,
            t0.Department,
            t1.BookingType,
            t1.BookingTypeId,
            t1.EventName,
            t1.StartDateTime,
            t1.EndDateTime,
            t1.StartDateTimeInUtc,
            t1.Duration,
            t1.Source,
            t1.SourceId,
            t2.Status AS AttendanceStatus,
            t2.AttendanceTime,
            t3.SendMessageToNoShowMember,
            t4.FitnessCompanyId,
            t4.FitnessCompanyName,
            t4.BackendSoftware,
            t4.BackendSoftwareUrl,
            t4.BackendSoftwareId,
            t6.AbcFinancialClubNumber,
            t4.ActiveMemberStatus,
            t4.StripeConnectAccountType,
            t4.StripeConnectAccountId,
            t4.StripeShcConvenienceFee,
            t6.FitnessCenterName,
            t6.TimeZone,
            IFNULL(t7.ResourceName, t1.LocationName) AS Location,
            t8.Email,
            t8.StripeDefaultCardId,
            t8.StripeCustomerId,
            t8.StripeStandardDefaultCardId,
            t8.StripeStandardCustomerId,
            t8.TimeFirstLogin,
            t8.ContactStatus,
            t8.IsAuthenticated,
            t8.BounceType,
            t9.FullName
        FROM
            CalendarEventBooking t0
            INNER JOIN CalendarEvent t1 ON t1.CalendarEventId = t0.CalendarEventId
            LEFT JOIN CalendarEventAttendance t2 ON t2.CalendarEventId = t0.CalendarEventId AND t2.AttendedFor = t0.BookedFor AND t2.UserId = t0.UserId AND t2.MemberId = t0.MemberId AND t2.UserName = t0.UserName AND t2.IsGuest = t0.IsGuest
            INNER JOIN BookingSetting t3 ON t3.BookingType = t1.BookingType AND t3.BookingSubType = t0.BookedFor AND t3.BookingTypeId = t1.BookingTypeId AND t3.FitnessCompanyId = t1.FitnessCompanyId
            INNER JOIN FitnessCompany t4 ON t4.FitnessCompanyId = t1.FitnessCompanyId
            LEFT JOIN BookingAndAttendancePolicy t5 ON t5.FitnessCompanyId = t4.FitnessCompanyId AND FIND_IN_SET(t0.Department, t5.Department) AND t5.Type = 'classes'
            INNER JOIN FitnessCenter t6 ON t6.FitnessCenterId = t0.FitnessCenterId
            LEFT JOIN FitnessCenterResource t7 ON t7.FitnessCenterId = t6.FitnessCenterId AND t7.ResourceId = t1.ResourceId
            INNER JOIN Users t8 ON t8.UserId = IF(t0.IsGuest, t0.GuestOf, t0.UserId)
            INNER JOIN Profile t9 ON t9.UserId = t8.UserId AND t9.FitnessCompanyId = t8.FitnessCompanyId
        WHERE
            t0.Status IN ('cancelled', 'transferred')
            AND t0.IsLateCancelled IS TRUE
            AND
            (
                (
                    t5.Id IS NOT NULL
                    AND t5.IsNoShowChargeForLateCancellation IS TRUE
                )
                OR
                (
                    t5.Id IS NULL
                    AND t4.IsNoShowChargeForLateCancellation IS TRUE
                )
            )
            AND
            (
                t0.NoShowChargeStatus IS NULL
                OR t0.NoShowChargeStatus = 'pending'
            )
            AND t1.Source IN ('Google', 'AbcFinancial')
            AND t1.BookingType = 'classes'
            AND t1.EventStatus IN ('open', 'completed')
            AND t3.SendMessageToNoShowMember IS NOT NULL
            AND t1.StartDateTimeInUtc <= DATE_SUB(NOW(), INTERVAL t3.SendMessageToNoShowMember HOUR)
            AND t1.StartDateTimeInUtc >= t3.NoShowMemberStartDateTime
    ) t11
    ORDER BY
        t11.StartDateTimeInUtc ASC
    LIMIT
        10
    ;
END$$
DELIMITER ;

慢查询日志记录 #

Query_time: 15.602992 Lock_time: 0.625710 Rows_sent: 0 Rows_examined: 231570
SET timestamp=1693998962;
CALL procGetBookingForNoShowCharge();

优化解决方案 #

1. 移除重复的表关联 #

原存储过程中重复关联了BookingSetting表（t3和t10），两者关联条件和过滤逻辑完全一致，可直接删除t10的关联及对应过滤条件，减少表连接开销。

2. 优化FIND_IN_SET函数的性能 #

LEFT JOIN BookingAndAttendancePolicy t5中使用的FIND_IN_SET(t0.Department, t5.Department)无法利用索引，建议：

• 若业务允许，拆分t5.Department多值字段为单独的关联表（如FitnessCompanyDepartmentPolicy），用等值连接替代FIND_IN_SET。
• 若无法修改表结构，可在t5.Department字段添加全文索引，或者预先整理Department的可能值列表，用IN条件匹配。

3. 创建复合索引 #

针对查询的过滤和连接条件，添加以下复合索引：

-- 优化CalendarEventBooking的过滤和连接
CREATE INDEX idx_cal_event_booking_status_noshow ON CalendarEventBooking (Status, NoShowChargeStatus, FitnessCenterId, CalendarEventId);

-- 优化CalendarEvent的过滤和连接
CREATE INDEX idx_cal_event_type_source_status ON CalendarEvent (BookingType, Source, EventStatus, StartDateTimeInUtc, FitnessCompanyId);

-- 优化BookingSetting的关联和过滤
CREATE INDEX idx_booking_setting_company_type_subtype ON BookingSetting (FitnessCompanyId, BookingType, BookingTypeId, BookingSubType, SendMessageToNoShowMember, NoShowMemberStartDateTime);

-- 优化Profile的关联
CREATE INDEX idx_profile_user_company ON Profile (UserId, FitnessCompanyId);

4. 替换UNION为UNION ALL #

两个子查询分别过滤不同的t0.Status值，结果无重复，使用UNION ALL避免默认的去重操作，提升查询效率。

5. 调整时间条件的写法 #

原条件t1.StartDateTimeInUtc <= DATE_SUB(NOW(), INTERVAL t3.SendMessageToNoShowMember HOUR)无法利用t1.StartDateTimeInUtc的索引，可改写为：

t1.StartDateTimeInUtc + INTERVAL t3.SendMessageToNoShowMember HOUR <= NOW()

确保t1.StartDateTimeInUtc为DATETIME或TIMESTAMP类型，让优化器可以使用索引扫描。

6. 同步统计信息并检查执行计划差异 #

• 执行ANALYZE TABLE更新各表的统计信息，让优化器生成准确的执行计划：

ANALYZE TABLE CalendarEventBooking, CalendarEvent, BookingSetting, FitnessCompany, BookingAndAttendancePolicy, FitnessCenter, Users, Profile;

• 检查后台脚本和手动执行的会话变量差异（如sql_mode、optimizer_switch），确保执行环境一致。
• 若使用MySQL 8.0+，执行EXPLAIN ANALYZE CALL procGetBookingForNoShowCharge();查看实际执行过程，定位性能瓶颈。

内容的提问来源于stack exchange，提问作者rajwardhan sinha