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 组合索引