首页 新闻 搜索 专区 学院

SQL语句优化

0
悬赏园豆: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
悟行的主页 悟行 | 专家六级 | 园豆:12346
提问于:2017-08-16 12:53
< >
分享
最佳答案
0

GROUP BY lesson_plan_id ORDER BY count DESC

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

收获园豆:10
男人要爽 | 初学一级 |园豆:15 | 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

 

 
悟行 | 园豆:12346 (专家六级) | 2017-08-18 16:14
其他回答(2)
0

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

Daniel Cai | 园豆:10374 (专家六级) | 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) 悟行 | 园豆:12346 (专家六级) | 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) 悟行 | 园豆:12346 (专家六级) | 2017-08-18 16:16

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

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

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

@抽象ID: 楼上正解

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

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

高星星同学 | 园豆:148 (初学一级) | 2017-08-18 17:42
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册