主表3万条数据，查询用了1：30秒。该如何优化

SELECT cu.PK_ID,
cu.*,
dicType.Name AS CustomerTypes,
dicStatu.Name as STATUSS
FROM dbo.Customer AS cu
LEFT JOIN dbo.SystemDictionary AS dicType
ON cu.CustomerType=dicType.PK_ID 
LEFT JOIN dbo.SystemDictionary AS dicStatu
ON cu.Status=dicStatu.PK_ID
LEFT JOIN dbo.Province AS pro ON cu.ProvinceId=pro.PK_ID
where 1=1 
/*
Customer  客户表只有三万多条数据

下面二个表的数据不多
CustomerType 客户类型表
SystemDictionary -字典表 
*

像这样的我该如何优化呢？

 

--我有针对客户表创建两个索引
CREATE INDEX  Index_CreateTime ON Customer(CreateTime)  创建时间与主键都有索引

11 个解决方案

#1

1：30 秒是多少？ 1 分 30 秒？
你这个查询， 最终出来多少条数据？至少有 10 万条吧？ 你需要显示那么多数据出来吗？显示大量数据非常消耗时间的了。

能优化的：
1. 所有的连接字段加索引。
2. 改成 innert join ;
3. 改成 分页 显示, 每次显示不要超过 100 条 ;
4. 改善硬件， 比如加内存， 换IO性能好的硬盘;

#2

LEFT JOIN dbo.Province AS pro ON cu.ProvinceId=pro.PK_ID

关联这个表没有读取数据？那就不要关联了。

#3

才查询几个字段，你用了三个left join，这样不行呢，赶紧好好查阅下sql优化的知识。
像这种的要连接好几张表却只查询一部分字段的，一般都会在各个涉及的表中增加冗余字段，这样就可以不用连接查询了。
还有三万条数据算是极少中的极少了，做分布式缓存吧，把数据直接加载到缓存中，用DataSet缓存起来，所有的操作在缓存的DataSet中进行，后台开一个线程按照一定的策略把DataSet提交到数据库。
还有说到索引，其实你也没有用好，int类型的索引效率最高，varchar类型的索引也比datatime类型的效率高，所以你应该把datetime转化成unix时间格式，在数据库中就是int存储，这样的索引效率的提升会很大。

#4

引用 1 楼 yenange 的回复:

1：30 秒是多少？ 1 分 30 秒？
你这个查询， 最终出来多少条数据？至少有 10 万条吧？ 你需要显示那么多数据出来吗？显示大量数据非常消耗时间的了。

能优化的：
1. 所有的连接字段加索引。
2. 改成 innert join ;
3. 改成 分页 显示, 每次显示不要超过 100 条 ;
4. 改善硬件， 比如加内存， 换IO性能好的硬盘;

赞成你建议！

#5

引用 3 楼 qq_37292482 的回复:

才查询几个字段，你用了三个left join，这样不行呢，赶紧好好查阅下sql优化的知识。
像这种的要连接好几张表却只查询一部分字段的，一般都会在各个涉及的表中增加冗余字段，这样就可以不用连接查询了。
还有三万条数据算是极少中的极少了，做分布式缓存吧，把数据直接加载到缓存中，用DataSet缓存起来，所有的操作在缓存的DataSet中进行，后台开一个线程按照一定的策略把DataSet提交到数据库。
还有说到索引，其实你也没有用好，int类型的索引效率最高，varchar类型的索引也比datatime类型的效率高，所以你应该把datetime转化成unix时间格式，在数据库中就是int存储，这样的索引效率的提升会很大。

补充一下，不常用或者这次查询没有用到的字段不要查询，查的字段越多越耗时，大字段特别影响效率还又不能建立索引，能不查大字段就不查大字段

#6

把条件列加上索引，表维护不频繁时，要用覆盖索引，要定时维护索引碎片
如上第二表创建方法：

CREATE INDEX IX_SystemDictionary_PK_ID ON SystemDictionary(PK_ID) INCLUDE(CustomerTypes)

#7

顶二楼的,重点还是分页

#8

在这几列上cu.CustomerType， dicType.PK_ID , dicStatu.PK_ID, pro.PK_ID 
分别添加index 已经FK

#9

客户表有3w记录 但有多少字段呢？你显示的 是cu.* 所有字段都显示了。

#10

你自己的那个索引有什么用呢？ 即不是条件，又不是用于排序
参考前面的那些建议，然后去补一下索引的知识吧

#11

加索引、会提高查询效率