SQL语句优化

悬赏园豆：10 [已解决问题] 解决于 2017-08-21 09:17

SELECT  u.name AS '老师姓名' ,
        temp.count AS '讲议数量' ,
        ls.lesson_plan_id ,
        ls.student_id ,
        ls.teacher_id ,
        ls.subject_id
FROM    lesson_plan ls
        RIGHT  JOIN ( SELECT    lesson_plan_id ,
                                COUNT(1) AS count
                      FROM      lesson_plan_quiz
                      WHERE     lesson_plan_id IN (
                                SELECT  lesson_plan_id
                                FROM    lesson_plan
                                WHERE   date(adjust_start_time) = date('2017-07-13')
                                        AND solve_status = 5
                                        AND lesson_type = 1
                                        AND [STATUS] = 3 )
                      GROUP BY  lesson_plan_id
                      ORDER BY  count DESC
                    ) temp ON ls.lesson_plan_id = temp.lesson_plan_id
        LEFT  JOIN user_info u ON ls.teacher_id = u.USER_ID
GROUP BY ls.subject_id

请指出这SQL尽可能多的优化点；谢谢

SQL

悟行 | 专家六级 | 园豆：12559
提问于：2017-08-16 12:53

< >

最佳答案

GROUP BY lesson_plan_id ORDER BY count DESC

尽量少用group by 影响查询速度

收获园豆：10

男人要爽 | 初学一级 |园豆：6 | 2017-08-17 10:50

嗯，但是这个好像少不了；

帮我看看下面这个：

SELECT
    u. NAME AS '老师姓名',
    count(1) AS '讲议数量',
    ls.lesson_plan_id,
    ls.student_id,
    ls.teacher_id,
    ls.subject_id
FROM
    lesson_plan ls
INNER JOIN lesson_plan_quiz lq ON lq.lesson_plan_id = ls.lesson_plan_id 
-- AND ls.adjust_start_time LIKE '2017-07-13%' 
-- AND ls.adjust_start_time >= '2017-07-13'
-- AND ls.adjust_start_time <= '2017-07-13 23:59:59' 
-- AND DATE(ls.adjust_start_time) = '2017-07-13'
-- and DATEDIFF(ls.adjust_start_time,'2017-07-13')=0
-- AND ls.adjust_start_time BETWEEN '2017-07-13 00:00:00' AND '2017-07-13 23:59:59'
AND ls.solve_status = 5
AND ls.lesson_type = 1
AND ls.`status` = 3
AND DATE(ls.adjust_start_time) = '2017-07-13'
LEFT JOIN user_info u ON ls.teacher_id = u.user_id
GROUP BY
    u. NAME,
    ls.lesson_plan_id,
    ls.student_id,
    ls.teacher_id,
    ls.subject_id
ORDER BY
    count(1) DESC

悟行 | 园豆：12559 (专家六级) | 2017-08-18 16:14

其他回答(2)

优化看索引，你这句中只有date(adjust_start_time) = date('2017-07-13')是不合适的，不应该在字段上堆函数，这样会让任何神索引都被废掉。

Daniel Cai | 园豆：10424 (专家六级) | 2017-08-16 13:44

对，这个函数相当于全表检索了。

支持(0) 反对(0) loongchao | 园豆：208 (菜鸟二级) | 2017-08-16 13:49

@loongchao: 我用between and 更慢；帮我看看新的SQL语句优化

SELECT
    u. NAME AS '老师姓名',
    count(1) AS '讲议数量',
    ls.lesson_plan_id,
    ls.student_id,
    ls.teacher_id,
    ls.subject_id
FROM
    lesson_plan ls
INNER JOIN lesson_plan_quiz lq ON lq.lesson_plan_id = ls.lesson_plan_id 
-- AND ls.adjust_start_time LIKE '2017-07-13%' 
-- AND ls.adjust_start_time >= '2017-07-13'
-- AND ls.adjust_start_time <= '2017-07-13 23:59:59' 
-- AND DATE(ls.adjust_start_time) = '2017-07-13'
-- and DATEDIFF(ls.adjust_start_time,'2017-07-13')=0
-- AND ls.adjust_start_time BETWEEN '2017-07-13 00:00:00' AND '2017-07-13 23:59:59'
AND ls.solve_status = 5
AND ls.lesson_type = 1
AND ls.`status` = 3
AND DATE(ls.adjust_start_time) = '2017-07-13'
LEFT JOIN user_info u ON ls.teacher_id = u.user_id
GROUP BY
    u. NAME,
    ls.lesson_plan_id,
    ls.student_id,
    ls.teacher_id,
    ls.subject_id
ORDER BY
    count(1) DESC

支持(0) 反对(0) 悟行 | 园豆：12559 (专家六级) | 2017-08-18 16:15

帮我看看这个：

SELECT
    u. NAME AS '老师姓名',
    count(1) AS '讲议数量',
    ls.lesson_plan_id,
    ls.student_id,
    ls.teacher_id,
    ls.subject_id
FROM
    lesson_plan ls
INNER JOIN lesson_plan_quiz lq ON lq.lesson_plan_id = ls.lesson_plan_id 
-- AND ls.adjust_start_time LIKE '2017-07-13%' 
-- AND ls.adjust_start_time >= '2017-07-13'
-- AND ls.adjust_start_time <= '2017-07-13 23:59:59' 
-- AND DATE(ls.adjust_start_time) = '2017-07-13'
-- and DATEDIFF(ls.adjust_start_time,'2017-07-13')=0
-- AND ls.adjust_start_time BETWEEN '2017-07-13 00:00:00' AND '2017-07-13 23:59:59'
AND ls.solve_status = 5
AND ls.lesson_type = 1
AND ls.`status` = 3
AND DATE(ls.adjust_start_time) = '2017-07-13'
LEFT JOIN user_info u ON ls.teacher_id = u.user_id
GROUP BY
    u. NAME,
    ls.lesson_plan_id,
    ls.student_id,
    ls.teacher_id,
    ls.subject_id
ORDER BY
    count(1) DESC

支持(0) 反对(0) 悟行 | 园豆：12559 (专家六级) | 2017-08-18 16:16

@抽象ID: 不能在字段上用date，这样任何优化都没意义。

你直接换成....time between '2017-07-13' and '2017-07-14'

支持(0) 反对(0) Daniel Cai | 园豆：10424 (专家六级) | 2017-08-18 17:13

@抽象ID: 楼上正解

支持(0) 反对(0) loongchao | 园豆：208 (菜鸟二级) | 2017-08-21 22:03

count(1) 会影响性能最好使用 IF NOT EXISTS(SELECT TOP 1 1 FROM 表名 WHERE 条件）

高星星同学 | 园豆：148 (初学一级) | 2017-08-18 17:42

清除回答草稿

您需要登录以后才能回答，未注册用户请先注册。

欢迎，请先 登录 或者 注册 。

SQL语句优化

欢迎，请先登录或者注册。