mysql执行优化器
mysql选错驱动表
这个ql看着给人感觉t表是驱动表,其实优化器优化后trc才是驱动表,因为t的查询条件筛选出来有50多万,二trc筛选只有几千条
explain select distinct t.`id` as "id", t.deleted as "deleted", t.no as "no", ru.name as "requester_name", tm.solve_minutes as "ticketMetric_solve_minutes" , t.subject as "subject", create_user.name as "user_name", t.priority as "priority", eu.name as "engineer_user_name", t.created_at as "created_at" , type.name as "ticketType_name", tm.solved_at as "ticketMetric_solved_at", tm.last_comment_at as "ticketMetric_last_comment_at", review_user.name as "reviewUser_name", ug.name as "requester_userGroup_name" , catalog.id as "serviceCatalog_id", tm.plan_solved_at as "ticketMetric_plan_solved_at", t.status as "status", t.updated_at as "updated_at", t.engineer_id as "engineer_id" , eu.id as "engineer_user_id", t.requester_id as "requester_id", ug.id as "requester_userGroup_id", t.ticket_type_id as "ticketType_id", t.ticket_metric_id as "ticketMetric_id" , t.user_id as "user_id", t.review_user_id as "reviewUser_id" from ticket t left join engineer eg on t.engineer_id = eg.id left join `user` eu on eg.user_id = eu.id inner join `user` ru on t.requester_id = ru.id left join user_group ug on ru.user_group_id = ug.id left join ticket_type type on t.ticket_type_id = type.id left join ticket_metric tm on t.ticket_metric_id = tm.id left join user create_user on t.user_id = create_user.id left join user review_user on t.review_user_id = review_user.id left join service_catalog catalog on t.service_catalog_id = catalog.id where t.provider_id = 20813 and (t.status is null or t.status not in ('deleted', 'suspended')) and (t.deleted = false or t.deleted is null) and tm.solved_at >= '2023-04-01 00:00:00' and tm.solved_at <= '2023-04-30 23:59:59' and t.id in ( select trc.ticket_id from ticket_r_cc trc where trc.service_desk_id in (29504) )
left jion和or导致选错驱动表
sql
explain SELECT DISTINCT tl.ticket_id FROM ticket_log tl LEFT JOIN ticket t ON t.id = tl.ticket_id WHERE tl.provider_id = 18502 AND t.provider_id = 18502 AND ( t.destroyed = 0 OR t.destroyed IS NULL ) AND tl.created_at >= '2023-06-14 00:00:00' AND tl.created_at < '2023-06-28 00:00:00' LIMIT 0, 10 tl表有 provider_id_created_at 组合索引 t表有 provider_id_destroyed_deleted_index 组合索引
看执行计划没问题,实际这条语句耗时30多秒
尝试拆解sql
实际t表查询有8万多条
实际or条件应该是以前兼容处理的
优化方案
方案1
这里的left join没有必要,改为jion 因为where 2个表都做了查询条件
方案2
去掉没必要的or
方案3
使用强制索引
结论
可能会死left join+or 让mysql 计算t表的预估扫描行数计算错误,mysql 遇到left join+jion就犯傻
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 全程不用写代码,我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· 记一次.NET内存居高不下排查解决与启示
· 物流快递公司核心技术能力-地址解析分单基础技术分享
· .NET 10首个预览版发布:重大改进与新特性概览!
2022-05-30 一次mysql驱动版本不一致导致的问题