SQL添加order by后性能非常慢（也有可能是查询条件）！求高手帮忙优化下。。。

2013-01-06 05:12:56 2.6k 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

hackerws2013 墨衍会员 · AI 创作全网分发关注 + 加入社区

记一次神奇的SQL查询经历，group by、order by慢查询优化 mysql的默认存储引擎是innodb,我们创建了innodb后，可以进入到mysql的数据目录下，可以发现，每个聚簇索引的表，都包含两个文件，其中一个表的定义文件，一个是.ibd的文件，这个文件实际上存储的数据就是一个表的索引和数据了，而聚簇索引的意思就是索引和数据放在同一个文件的，就叫做聚簇索引，比如innodb存储引擎的主键索引，就是聚簇索引，也是一棵B+tree结构。myisam存储引擎创建的表，我们去到数据目录下，会发现有三个文件，其中一个是表的定义文件，一个是数据文件，还有一个是索引文件。阅读详情

SELECT TOP 50 * FROM vw_App_Appointment WHERE 1 = 1 AND user_department_id = 1000 ORDER BY appointment_id DESC
此句代码时候后，执行时间非常慢，大于需要40多S。

SELECT TOP 50 * FROM vw_App_Appointment WHERE 1 = 1 AND user_department_id = 1000
SELECT TOP 50 * FROM vw_App_Appointment ORDER BY appointment_id DESC
而当这两句分别执行时候，大约也就1S左右。

为什么性能会相差这么多呢？该怎么优化第一句SQL？

...全文

MySql学习之慢SQL优化和慢SQL案例慢查询日志记录慢SQL explain查看分析SQL的执行计划 profile分析执行耗时 Optimizer Trace分析详情确定慢SQL问题并采用相应的方案隐式转换、最左匹配、深分页问题、in元素过多、order by 走文件排序、索引字段上使用（！= 或者）索引字段上使用is null， is not null，索引可能失效、左右连接，关联的字段的编码格式不一样、group by使用临时表、delete + in子查询不走索引、其它索引失效场景，如like '%xxx' 阅读详情

hackerws2013

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

hackerws2013 2013-01-07

打赏
举报

哦，谢谢了，结贴。。。

Mr_Nice 2013-01-07

打赏
举报

引用 11 楼 hackerws2013 的回复:

呃。。。如9楼所说，为表Acc_User表添加了覆盖索引，就好了，但是不知道原理是为什么。。。

因为利用原索引找到USER_ID 的时候，还需要找到user_real_name，但索引中没有，就得全表扫描一下。建个覆盖索引，就不用再扫描全表，速度就上来了。

hackerws2013 2013-01-07

打赏
举报

呃。。。如9楼所说，为表Acc_User表添加了覆盖索引，就好了，但是不知道原理是为什么。。。

Mr_Nice 2013-01-07

打赏
举报

另外，lz图片下面应该还有vw_Acc_User 的相关执行计划。实在图片费劲，lz把文本的发上来看看也行。 SET STATISTICS PROFILE ON select ... --lz的视图执行一下，结果中会出现相应的执行计划。这个发上来也行。

Mr_Nice 2013-01-07

打赏
举报

SELECT  dbo.App_Appointment.appointment_id ,
        dbo.App_Appointment.appointment_state ,
        CASE WHEN dbo.App_Appointment.appointment_state = 'N' THEN '未到'
             ELSE '已到'
        END AS appointment_state_text ,
        CASE WHEN dbo.App_Appointment.appointment_visit = '' THEN 'N'
             ELSE 'Y'
        END AS appointment_visit_state ,
        CASE WHEN dbo.App_Appointment.appointment_visit = '' THEN '未回访'
             ELSE '已回访'
        END AS appointment_visit_state_text ,
        CASE WHEN dbo.App_Appointment.appointment_visit_arrive IS NULL
             THEN 'N'
             ELSE dbo.App_Appointment.appointment_visit_arrive
        END AS appointment_visit_arrive ,
        dbo.App_Appointment.appointment_user_name ,
        dbo.App_Appointment.appointment_user_sex ,
        dbo.App_Appointment.appointment_user_age ,
        dbo.App_Appointment.appointment_user_phone ,
        dbo.App_Appointment.appointment_user_qq ,
        dbo.App_Appointment.appointment_specialist_number ,
        dbo.App_Appointment.appointment_is_consume ,
        CASE WHEN dbo.App_Appointment.appointment_is_consume = 'N' THEN '未消费'
             ELSE '已消费'
        END AS appointment_is_consume_text ,
        dbo.App_Appointment.appointment_keywords ,
        dbo.App_Appointment.appointment_advisory ,
        dbo.App_Appointment.appointment_note ,
        dbo.App_Appointment.appointment_ailment ,
        dbo.App_Appointment.appointment_remark ,
        dbo.App_Appointment.appointment_visit ,
        dbo.App_Appointment.appointment_time_arrive ,
        dbo.App_Appointment.appointment_time_create ,
        dbo.App_Appointment.appointment_source_id ,
        dbo.App_Appointment.appointment_area_id ,
        dbo.App_Appointment.user_id ,
        dbo.App_Source.source_name ,
        dbo.App_Area.area_name ,
        dbo.vw_Acc_User.user_real_name ,
        dbo.vw_Acc_User.user_department_id ,
        dbo.vw_Acc_User.department_name ,
        dbo.App_Appointment.appointment_abteilung_id ,
        dbo.Abt_Abteilung.abteilung_name ,
        dbo.vw_Acc_User.user_hospital_id ,
        dbo.vw_Acc_User.hospital_name ,
        dbo.App_Appointment.appointment_to_qq ,
        CASE WHEN dbo.App_Appointment.appointment_to_qq = 'N' THEN '未转'
             ELSE '已转'
        END AS appointment_to_qq_text ,
        dbo.App_Appointment.appointment_to_phone ,
        CASE WHEN dbo.App_Appointment.appointment_to_phone = 'N' THEN '未转'
             ELSE '已转'
        END AS appointment_to_phone_text ,
        dbo.App_Appointment.appointment_doctor_id ,
        dbo.Acc_User.user_real_name AS doctor_name ,
        dbo.App_Appointment.appointment_evaluation ,
        dbo.App_Appointment.appointment_clinic_number
FROM    dbo.App_Appointment  --该表字段提取很多，执行计划中 键查找的部分，也表明了这点，lz也可以考虑使用覆盖索引，用空间换时间
        LEFT OUTER JOIN dbo.Acc_User ON dbo.App_Appointment.appointment_doctor_id = dbo.Acc_User.USER_ID   --Acc_User 索引扫描，占了13% ，但上面输出只有一列user_real_name，lz可以建里一个覆盖索引 USER_ID和user_real_name
        LEFT OUTER JOIN dbo.Abt_Abteilung ON dbo.App_Appointment.appointment_abteilung_id = dbo.Abt_Abteilung.abteilung_id
        LEFT OUTER JOIN dbo.App_Source ON dbo.App_Appointment.appointment_source_id = dbo.App_Source.source_id
        LEFT OUTER JOIN dbo.App_Area ON dbo.App_Appointment.appointment_area_id = dbo.App_Area.area_id
        LEFT OUTER JOIN dbo.vw_Acc_User ON dbo.App_Appointment.user_id = dbo.vw_Acc_User.USER_ID  --如果是视图的话，看看能否做成索引视图，如果不是注意user_id列的索引状况。

hackerws2013 2013-01-07

打赏
举报

视图：
SELECT dbo.App_Appointment.appointment_id, dbo.App_Appointment.appointment_state,
CASE WHEN dbo.App_Appointment.appointment_state = 'N' THEN '未到' ELSE '已到' END AS appointment_state_text,
CASE WHEN dbo.App_Appointment.appointment_visit = '' THEN 'N' ELSE 'Y' END AS appointment_visit_state,
CASE WHEN dbo.App_Appointment.appointment_visit = '' THEN '未回访' ELSE '已回访' END AS appointment_visit_state_text,
CASE WHEN dbo.App_Appointment.appointment_visit_arrive IS NULL THEN 'N' ELSE dbo.App_Appointment.appointment_visit_arrive END AS appointment_visit_arrive,
dbo.App_Appointment.appointment_user_name, dbo.App_Appointment.appointment_user_sex, dbo.App_Appointment.appointment_user_age,
dbo.App_Appointment.appointment_user_phone, dbo.App_Appointment.appointment_user_qq, dbo.App_Appointment.appointment_specialist_number,
dbo.App_Appointment.appointment_is_consume,
CASE WHEN dbo.App_Appointment.appointment_is_consume = 'N' THEN '未消费' ELSE '已消费' END AS appointment_is_consume_text,
dbo.App_Appointment.appointment_keywords, dbo.App_Appointment.appointment_advisory, dbo.App_Appointment.appointment_note,
dbo.App_Appointment.appointment_ailment, dbo.App_Appointment.appointment_remark, dbo.App_Appointment.appointment_visit,
dbo.App_Appointment.appointment_time_arrive, dbo.App_Appointment.appointment_time_create, dbo.App_Appointment.appointment_source_id,
dbo.App_Appointment.appointment_area_id, dbo.App_Appointment.user_id, dbo.App_Source.source_name, dbo.App_Area.area_name,
dbo.vw_Acc_User.user_real_name, dbo.vw_Acc_User.user_department_id, dbo.vw_Acc_User.department_name, dbo.App_Appointment.appointment_abteilung_id,
dbo.Abt_Abteilung.abteilung_name, dbo.vw_Acc_User.user_hospital_id, dbo.vw_Acc_User.hospital_name, dbo.App_Appointment.appointment_to_qq,
CASE WHEN dbo.App_Appointment.appointment_to_qq = 'N' THEN '未转' ELSE '已转' END AS appointment_to_qq_text, dbo.App_Appointment.appointment_to_phone,
CASE WHEN dbo.App_Appointment.appointment_to_phone = 'N' THEN '未转' ELSE '已转' END AS appointment_to_phone_text,
dbo.App_Appointment.appointment_doctor_id, dbo.Acc_User.user_real_name AS doctor_name, dbo.App_Appointment.appointment_evaluation,
dbo.App_Appointment.appointment_clinic_number
FROM dbo.App_Appointment LEFT OUTER JOIN
dbo.Acc_User ON dbo.App_Appointment.appointment_doctor_id = dbo.Acc_User.user_id LEFT OUTER JOIN
dbo.Abt_Abteilung ON dbo.App_Appointment.appointment_abteilung_id = dbo.Abt_Abteilung.abteilung_id LEFT OUTER JOIN
dbo.App_Source ON dbo.App_Appointment.appointment_source_id = dbo.App_Source.source_id LEFT OUTER JOIN
dbo.App_Area ON dbo.App_Appointment.appointment_area_id = dbo.App_Area.area_id LEFT OUTER JOIN
dbo.vw_Acc_User ON dbo.App_Appointment.user_id = dbo.vw_Acc_User.user_id

發糞塗牆 2013-01-06

打赏
举报

贴的不全，把百分比最大的那部分贴上来。

hackerws2013 2013-01-06

打赏
举报

hackerws2013 2013-01-06

打赏
举报

SQL SERVER 2008里，怎么查看执行计划呢？

hackerws2013 2013-01-06

打赏
举报

我创建索引的时候会提示创建失败，视图中不能带有OUTER。。

nzperfect 2013-01-06

打赏
举报

看上去像是一个视图。。。如果这两个列同在一个表，那么建一个索引会提高效率：类似下面的：

create index ix_test on vw_App_Appointment (user_department_id, appointment_id)

KevinLiu 2013-01-06

打赏
举报

第一条执行计划贴一下

hackerws2013 2013-01-06

打赏
举报

自己顶一下，数据量大概是4W左右。。。

SQL添加order by后性能非常慢（也有可能是查询条件）！求高手帮忙优化下。。。

相关推荐