表里有50W条数据 有一个sql select 了50多个字段,join 了10张表 where 条件中有个字段 IN 的值有120多个 sql查询时间40S 如何优化;

尼禄花嫁 发布于 01/19 18:35
阅读 1K+
收藏 0

开源之夏第三届火热来袭,高校学生参与赢万元奖金!>>>

相关代码

SELECT
appointment.appointment_id AS appointmentId,
appointment.deduct_count AS deductCount,
appointment.deduct_money AS deductMoney,
appointment.deduct AS deduct,
appointment.status AS status,
appointment.apply_time AS applyTime,
appointment.apply_reason AS applyReason,
appointment.applicant_id AS applicantId,
appointment.applicant_name AS applicantName,
appointment.approver AS approver,
appointment.approver_name AS approverName,
appointment.approve_time AS approveTime,
appointment.checker_id AS checkerId,
appointment.check_time AS checkTime,
appointment.check_name AS checkName,
appointment.type AS appointmentType,
appointment.commented AS commented,
appointment.comment_score AS commentScore,
appointment.created_at AS createdAt,
appointment.rank AS rank,
contact.mobile AS applicantMobie,
contact.relation AS applicantRelation,
mc.card_id AS cardId,
mc.card_type AS cardType,
mc.card_name AS cardName,
mc.remain_count AS cardRemainCount,
class.class_id AS classId,
class.name AS className,
class.temporary_enable AS temporaryEnable,
class.cancel_enable AS cancelEnable,
class.cancel_ahead_hour AS cancelAheadHour,
class.audition_enable AS auditionEnable,
classroom.classroom_id AS classroomId,
classroom.name AS classroomName,
course.course_id AS courseId,
course.name AS courseName,
teacher.employee_id AS teacherId,
teacher.name AS teacherName,
school.alias AS schoolName,
member.member_id AS memberId,
member.avatar AS memberAvatar,
member.name AS memberName,
member.contact_mobile AS contactMobile,
member.contact_relation AS contactRelation,
member.year_takeoff_count AS memberTakeoffCount,
member.year_absence_count AS memberAbsenceCount,
sch.start_date AS startDate,
sch.start_time AS startTime,
sch.end_time AS endTime,
sch.real_start_time AS realStartTime,
sch.real_end_time AS realEndTime,
sch.status AS scheduleStatus,
sch.consume_count AS consumeCount,
sch.schedule_id AS scheduleId,
sch.type AS scheduleType
FROM
school_member_appointment appointment
LEFT JOIN school school ON appointment.school_id = school.school_id
AND school.deleted_at = 0
LEFT JOIN school_member member ON member.member_id = appointment.member_id
LEFT JOIN school_member_contact contact ON contact.member_id = appointment.member_id
AND contact.user_id = appointment.applicant_id
AND appointment.applicant_id != ‘’
AND contact.deleted_at = 0
LEFT JOIN school_member_card mc ON mc.member_id = appointment.member_id
AND mc.card_id = appointment.card_id
AND mc.deleted_at = 0
LEFT JOIN school_class_schedule sch ON sch.schedule_id = appointment.schedule_id
LEFT JOIN school_classroom classroom ON sch.classroom_id = classroom.classroom_id
LEFT JOIN school_class class ON class.class_id = sch.class_id
LEFT JOIN school_course course ON sch.course_id = course.course_id
LEFT JOIN school_employee teacher ON teacher.employee_id = sch.teacher_id
WHERE
appointment.tenant_id = ‘********’
AND appointment.school_id IN (
‘294560465890320384’,
‘294573237441142784’,
‘302846663477956608’,
‘303571767275036672’,
‘314804445324251136’,
‘314819791569559552’,
‘314823736882892800’,
‘314825721606574080’,
‘314828955389796352’,
‘314836268578246656’,
‘314838539986804736’,
‘314843390363832320’,
‘315103140628402176’,
‘315122713582903296’,
‘315128814814629888’,
‘315134170374606848’,
‘315136906369441792’,
‘315165121699909632’,
‘315531167174762496’,
‘315539128269938688’,
‘316237565911113728’,
‘316256325174829056’,
‘317614360958865408’,
‘318421965314727936’,
‘318428745654603776’,
‘318431548724748288’,
‘319816787975999488’,
‘319822049310941184’,
‘329692354992939008’,
‘329693849377316864’,
‘329694896225259520’,
‘329695665645162496’,
‘329697811610472448’,
‘329698929467985920’,
‘329699980959027200’,
‘329700713158676480’,
‘329701662136737792’,
‘331526316379873280’,
‘331539418156638208’,
‘331542115911999488’,
‘331544564395679744’,
‘333312116885032960’,
‘340533420553932800’,
‘340534617671208960’,
‘340536794758254592’,
‘340539172861513728’,
‘343036760466657280’,
‘343043028942069760’,
‘350319021348102144’,
‘350320638445228032’,
‘353224449702760448’,
‘353902383220400128’,
‘355800309655937024’,
‘356068202964586496’,
‘358905146413158400’,
‘358906650452828160’,
‘359343291550142464’,
‘359344228641542144’,
‘360082046472953856’,
‘362911479932129280’,
‘363627583147085824’,
‘367276645637431296’,
‘374516521265008640’,
‘375700408930406400’,
‘377074786393923584’,
‘377076778201780224’,
‘378189634217451520’,
‘378253611551170560’,
‘378479287613005824’,
‘391988375365885952’,
‘392298514639753216’,
‘399506324708397056’,
‘402528249001025536’,
‘405770691145900032’,
‘405777753611440128’,
‘405779354489200640’,
‘422067035728781312’,
‘426084310408503296’,
‘427027624892833792’,
‘427030687791718400’,
‘428239672972677120’,
‘443445612365746176’,
‘444542138638934016’,
‘444547720628408320’,
‘446733604211662848’,
‘448517894855462912’,
‘449976314959761408’,
‘458590239674605568’,
‘460118975431581696’,
‘460127759495335936’,
‘485837509897756672’,
‘490101211757023232’,
‘490104341030768640’,
‘495259252517244928’,
‘501367809813778432’,
‘501422230585217024’,
‘503650868529467392’,
‘505701334797000704’,
‘509389889415159808’
)
AND appointment.type IN ( ‘IN_CLASS’, ‘TEMPORARY’, ‘AUDITION’ )
AND appointment.deleted_at = 0
ORDER BY
appointment.created_at DESC;

执行计划: 

希望大家能给点实际解决问题的优化方案;

加载中
0
二次元萌控森
二次元萌控森

嚯,好活,我也不会,出来记得通知我声。还有花嫁尼禄不应该是5星白色的吗?你这是4星的

尼禄花嫁
尼禄花嫁
4星才是本体
0
JH-Wong
JH-Wong

要不排序先去掉,看看是不是文件排序导致的,执行计划里面有一个文件排序,小白不太确定是不是

0
ArchitectureMaster
ArchitectureMaster

先where出120个条件的结果集,然后再在这个结果集的基础上去left join左连接外键查询,最后再在左连接的基础上order by。性能会提升不少。

先where 出学校,再然后去联表查会更好点。学校id应该是做了聚集索引的主键。不存在优化。如果主键schoolid都是数字的话换成long型速度比字符串又要快一点。

0
kakai
kakai

进来看看这种高级sql

0
ycwangrd
ycwangrd

你可以试一下把where和order都直接执行了,然后再执行left join开始查询试一下,原理就是先优化左表的数据量,然后再去关联查询,你试一试,

SELECT
    appointment.appointment_id AS appointmentId,
    appointment.deduct_count AS deductCount,
    appointment.deduct_money AS deductMoney,
    appointment.deduct AS deduct,
    appointment.STATUS AS STATUS,
    appointment.apply_time AS applyTime,
    appointment.apply_reason AS applyReason,
    appointment.applicant_id AS applicantId,
    appointment.applicant_name AS applicantName,
    appointment.approver AS approver,
    appointment.approver_name AS approverName,
    appointment.approve_time AS approveTime,
    appointment.checker_id AS checkerId,
    appointment.check_time AS checkTime,
    appointment.check_name AS checkName,
    appointment.type AS appointmentType,
    appointment.commented AS commented,
    appointment.comment_score AS commentScore,
    appointment.created_at AS createdAt,
    appointment.`rank` AS `rank`,
    contact.mobile AS applicantMobie,
    contact.relation AS applicantRelation,
    mc.card_id AS cardId,
    mc.card_type AS cardType,
    mc.card_name AS cardName,
    mc.remain_count AS cardRemainCount,
    class.class_id AS classId,
    class.NAME AS className,
    class.temporary_enable AS temporaryEnable,
    class.cancel_enable AS cancelEnable,
    class.cancel_ahead_hour AS cancelAheadHour,
    class.audition_enable AS auditionEnable,
    classroom.classroom_id AS classroomId,
    classroom.NAME AS classroomName,
    course.course_id AS courseId,
    course.NAME AS courseName,
    teacher.employee_id AS teacherId,
    teacher.NAME AS teacherName,
    school.alias AS schoolName,
    member.member_id AS memberId,
    member.avatar AS memberAvatar,
    member.NAME AS memberName,
    member.contact_mobile AS contactMobile,
    member.contact_relation AS contactRelation,
    member.year_takeoff_count AS memberTakeoffCount,
    member.year_absence_count AS memberAbsenceCount,
    sch.start_date AS startDate,
    sch.start_time AS startTime,
    sch.end_time AS endTime,
    sch.real_start_time AS realStartTime,
    sch.real_end_time AS realEndTime,
    sch.STATUS AS scheduleStatus,
    sch.consume_count AS consumeCount,
    sch.schedule_id AS scheduleId,
    sch.type AS scheduleType 
FROM (
  SELECT * FROM school_member_appointment 
    WHERE appointment.tenant_id = ' ********' 
        AND appointment.school_id IN (
            ' 294560465890320384',
            ' 294573237441142784',
            ' 302846663477956608',
            ' 303571767275036672',
            ' 314804445324251136',
            ' 314819791569559552',
            ' 314823736882892800',
            ' 314825721606574080',
            ' 314828955389796352',
            ' 314836268578246656',
            ' 314838539986804736',
            ' 314843390363832320',
            ' 315103140628402176',
            ' 315122713582903296',
            ' 315128814814629888',
            ' 315134170374606848',
            ' 315136906369441792',
            ' 315165121699909632',
            ' 315531167174762496',
            ' 315539128269938688',
            ' 316237565911113728',
            ' 316256325174829056',
            ' 317614360958865408',
            ' 318421965314727936',
            ' 318428745654603776',
            ' 318431548724748288',
            ' 319816787975999488',
            ' 319822049310941184',
            ' 329692354992939008',
            ' 329693849377316864',
            ' 329694896225259520',
            ' 329695665645162496',
            ' 329697811610472448',
            ' 329698929467985920',
            ' 329699980959027200',
            ' 329700713158676480',
            ' 329701662136737792',
            ' 331526316379873280',
            ' 331539418156638208',
            ' 331542115911999488',
            ' 331544564395679744',
            ' 333312116885032960',
            ' 340533420553932800',
            ' 340534617671208960',
            ' 340536794758254592',
            ' 340539172861513728',
            ' 343036760466657280',
            ' 343043028942069760',
            ' 350319021348102144',
            ' 350320638445228032',
            ' 353224449702760448',
            ' 353902383220400128',
            ' 355800309655937024',
            ' 356068202964586496',
            ' 358905146413158400',
            ' 358906650452828160',
            ' 359343291550142464',
            ' 359344228641542144',
            ' 360082046472953856',
            ' 362911479932129280',
            ' 363627583147085824',
            ' 367276645637431296',
            ' 374516521265008640',
            ' 375700408930406400',
            ' 377074786393923584',
            ' 377076778201780224',
            ' 378189634217451520',
            ' 378253611551170560',
            ' 378479287613005824',
            ' 391988375365885952',
            ' 392298514639753216',
            ' 399506324708397056',
            ' 402528249001025536',
            ' 405770691145900032',
            ' 405777753611440128',
            ' 405779354489200640',
            ' 422067035728781312',
            ' 426084310408503296',
            ' 427027624892833792',
            ' 427030687791718400',
            ' 428239672972677120',
            ' 443445612365746176',
            ' 444542138638934016',
            ' 444547720628408320',
            ' 446733604211662848',
            ' 448517894855462912',
            ' 449976314959761408',
            ' 458590239674605568',
            ' 460118975431581696',
            ' 460127759495335936',
            ' 485837509897756672',
            ' 490101211757023232',
            ' 490104341030768640',
            ' 495259252517244928',
            ' 501367809813778432',
            ' 501422230585217024',
            ' 503650868529467392',
            ' 505701334797000704',
            ' 509389889415159808' 
        ) 
        AND appointment.type IN ( ' IN_CLASS', ' TEMPORARY', ' AUDITION' ) 
        AND appointment.deleted_at = 0
        ORDER BY appointment.created_at DESC
    ) appointment
    LEFT JOIN school school ON appointment.school_id = school.school_id AND school.deleted_at = 0
    LEFT JOIN school_member member ON member.member_id = appointment.member_id
    LEFT JOIN school_member_contact contact ON contact.member_id = appointment.member_id 
    AND contact.user_id = appointment.applicant_id AND appointment.applicant_id != '' AND contact.deleted_at = 0
    LEFT JOIN school_member_card mc ON mc.member_id = appointment.member_id 
    AND mc.card_id = appointment.card_id AND mc.deleted_at = 0
    LEFT JOIN school_class_schedule sch ON sch.schedule_id = appointment.schedule_id
    LEFT JOIN school_classroom classroom ON sch.classroom_id = classroom.classroom_id
    LEFT JOIN school_class class ON class.class_id = sch.class_id
    LEFT JOIN school_course course ON sch.course_id = course.course_id
    LEFT JOIN school_employee teacher ON teacher.employee_id = sch.teacher_id;

尼禄花嫁
尼禄花嫁
关键还是school_id in 的值太多 要是school_id能走索引 还能快很多 这里还能优化吗?
尼禄花嫁
尼禄花嫁
:+1: 可以的 提升了20多S
0
蔺兜兜
蔺兜兜

先要判断你的返回结果数是不是很多,基础数据比较多的话就不是sql优化的问题了,因为你这个要排序;要是返回结果不是很多的话就只需要school_member_appointment  这个表加个将  school_id 、type、tenant_id deleted_at 建立一个联合索引就可以了

尼禄花嫁
尼禄花嫁
回复 @蔺兜兜 : 返回结果很多 了解到前同事为了省事 把34个接口的sql融合成了这一个大的 现在拆的话前后端改动非常多 但已经申请领导同意了;联合索引建立了 但没走索引 我猜是因为in后面的数据在数据表中超过30%走了全表扫描
蔺兜兜
蔺兜兜
你这个执行计划看起来主要是在school_member_appointment 这个表上耗时,关联不耗时,这个方向走就可以了
0
Rep
Rep

好活,我推荐这种情况就不要用sql了,做一个宽表吧,用ClickHouse或es

魔力猫
魔力猫
回复 @Rep : 所谓3表以上不考虑,那是因为早期MySQL的 表连接以及执行计划优化做的烂,8.0才支持hash join 。
Rep
Rep
3表以上的连接就不要再考虑了,数据量很大时,就flink同步数据到其他存储引擎上,再分组搜索
0
sprouting
sprouting

没的优化了,LEFT JOIN  超过3张表就要考虑设计的问题了,阿里的代码规范中也强烈要求不这么做

 

0
sxgkwei
sxgkwei

34个接口,领导同意了做优化。这种情况下,还折腾这玩意儿干嘛。先把34个接口分分类,然后,针对性的每个取重新实现背后的sql不就行了?

sxgkwei
sxgkwei
回复 @尼禄花嫁 : :yum:最好的优化,就是从删库到跑路
尼禄花嫁
尼禄花嫁
想学点优化知识
0
单手开法拉利
单手开法拉利

也有种方法可以不让前端改代码,就是分开查询不同业务,然后组成一个对象输出给前端。

OSCHINA
登录后可查看更多优质内容
返回顶部
顶部
返回顶部
顶部