关于一次 mysql 慢查询想到的几点前言背景知识点 1 关于 left join 的驱动表选择知识点2 explain 中 rows 大的不一定就慢知识点3索引真的有用后记参考前言最近在做一个需求的时候有一个涉及到 mysql 的慢查询。这里简单落笔分析下希望可以便人便己。背景我们经常会有这样的场景有一张主表数据量可能很大后续有一些新的业务由于主表数据量比较大或者业务特点不适合不太适合继续往主表里加字段加信息就会产生出一个派生的表两个表之间通过一个 id 来进行关联。这里说的就是这样两张表。先说说表结构。一张完整的服务记录总表记录了应用 ID、会话 ID、服务的时长、服务人员、服务评价、结束时间等等。 表有一个结束时间的索引、会话 ID联合索引。CREATETABLEt_service_record(idbigintNOTNULLAUTO_INCREMENTCOMMENT自增主键,app_idbigintNOTNULLCOMMENT应用 ID,session_idvarchar(64)COLLATEutf8mb4_general_ciNOTNULLCOMMENT会话 ID,duration_secintNOTNULLDEFAULT0COMMENT服务时长秒,agent_idvarchar(64)COLLATEutf8mb4_general_ciNOTNULLDEFAULTCOMMENT服务人员 ID,ratingtinyintNOTNULLDEFAULT0COMMENT服务评价1-50 表示未评价,service_typetinyintNOTNULLDEFAULT0COMMENT服务类型1 在线 / 2 电话 / 3 视频,end_timedatetimeNOTNULLCOMMENT结束时间,create_timedatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT创建时间,modify_timedatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT更新时间,PRIMARYKEY(id),KEYidx_end_time(end_time),KEYidx_session(session_id))ENGINEInnoDBAUTO_INCREMENT130100001DEFAULTCHARSETutf8mb4COLLATEutf8mb4_general_ci上面这是一个主要的服务记录表按照 end_time 和 session_id 进行索引。表中大概总共几亿条数据。还有一张单独的在线服务详情表记录了在线服务的相关记录详情比如说会话 ID、 渠道来源、用户 ID、机器人 ID(支持机器人提供服务)、结束的阶段、结束时间等等。CREATETABLEt_chat_detail(idbigintNOTNULLAUTO_INCREMENTCOMMENT自增主键,session_idvarchar(64)COLLATEutf8mb4_general_ciNOTNULLCOMMENT会话 ID关联 t_service_record.session_id,app_idbigintNOTNULLCOMMENT应用 ID,channel_sourcevarchar(32)COLLATEutf8mb4_general_ciNOTNULLDEFAULTCOMMENT渠道来源,user_idvarchar(64)COLLATEutf8mb4_general_ciNOTNULLDEFAULTCOMMENT用户 ID,robot_idvarchar(64)COLLATEutf8mb4_general_ciNOTNULLDEFAULTCOMMENT机器人 ID空串表示人工服务,end_stagetinyintNOTNULLDEFAULT0COMMENT结束阶段1 用户退出 / 2 机器人转人工 / 3 超时 ...,end_timedatetimeNOTNULLCOMMENT结束时间【无索引】,create_timedatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT创建时间,modify_timedatetimeNOTNULLDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMPCOMMENT更新时间,PRIMARYKEY(id),KEYidx_session(session_id))ENGINEInnoDBAUTO_INCREMENT2501925DEFAULTCHARSETutf8mb4COLLATEutf8mb4_general_ci在线服务详情表有一个按session_id 维度的二级索引注意在线服务详情表里的结束时间没有索引这张表大概有几百万的数据量。两张表通过会话 ID 来关联前者包含了所有服务类型的记录在线、电话、视频后者的服务记录是前者的一个子集。数据量如下图示。需求是根据筛选条件服务的时长、服务评价等拉取指定时间范围内的在线服务列表支持排序、分页等最终按照时间排序返回。知识点 1 关于 left join 的驱动表选择我们知道当使用 a left join b mysql 会把左表的表a记录全部返回b 中如果条件不匹配的join on 条件的会返回NULL 值。还有一点要注意的是一般来说mysql 会选择左表的表a 作为驱动表注意只是一般来说并不是绝对的。如上图所示前者以 t_chat_detail 作为驱动表后者以t_service_record作为驱动表。但是如果加一些限制条件的话mysql 会根据实际的数据情况对 left join 进行改写选择它认为最合适的表作为驱动表。比如说如下图所示t_service_record是左表但是由于增加了筛选和排序条件 而且t_service_record数据量比较多一亿三千万行 mysql 认为选择小表t_chat_detail更划算些t_chat_detail的数据量只有两百多万。更极端一点哪怕是搜索条件和排序都按照 t_service_record.end_time 来要知道t_service_record.end_time 可是有二级索引的。但是结果依然是使用t_chat_detail作为驱动表。原因无他mysql 认为小表驱动更划算。但是实际上有些时候myql 自动选择的并不是最优的结果这个时候可以使用STRAIGHT_JOIN由用户来指定想要哪张表作为驱动表。知识点2 explain 中 rows 大的不一定就慢explain 中的 rows代表了在执行的过程中mysql预计要扫描多少行记录聚簇索引或者二级索引的记录。一般来说如果这个值过大的话则需要关注会不会影响查询性能。但是某些时候也并不一定最终的结果会很慢。比如说下面这个查询这个查询只是为了举例实际上查询的不是在线服务的记录使用了t_service_record.end_time的二级索引range 扫描但是explain 估计要扫描三百多万行。感觉有点多害怕执行有点慢 如下图其实执行还好因为有 limit 限制, 命中了 end_time, 按照 range 从后往前扫描, 还是很快的。但是如果要是 offset 过多的话即使是扫描索引由于要跳过指定的offset 条记录查询耗时就上来了。这也就是深度分页的问题如果有深度分页的问题一个解法是使用搜索游标。好在一般情况下用户都不会往下看这么多的页。知识点3索引真的有用有了上述的铺垫看看最终的 SQL 查询所有的在线服务列表。SELECTd.session_id,d.app_id,d.channel_sourceASservice_market,d.user_id,d.robot_id,d.end_stage,d.end_timeASchat_end_time,r.agent_idASservice_person,r.service_type,r.duration_sec,r.rating,r.end_timeASrecord_end_timeFROMt_chat_detail dJOINt_service_record rONr.session_idd.session_idWHEREd.end_timeBETWEENFROM_UNIXTIME(1778984821)ANDFROM_UNIXTIME(1779244021)ORDERBYd.end_timeDESClimit10;explain 一下可以看到全表扫描两百多万条记录。看看结果1s多时间稍微有点长。加个索引explain 看下。看看加了索引之后的查询结果。没毛病索引真的有效果。后记花了一些时间谈了一些老生常谈的东西但每一次记录都会有些新的理解。也算有收获吧。参考《MySQL是怎样运行的从根儿上理解MySQL 2》神奇的 SQL 之 联表细节 → MySQL JOIN 的执行过程一