SQL 100万数据查询问题求助

[zf_1987]

大家好，我是一个新人，有个问题困扰好久，我建立了一个表'发票对照'，里边大概有100万条数据，我现在直接执行
select * from 发票对照 结果速度非常慢，大概要30多秒才能出现结果，
我增加了聚集索引在其中的单据号列，但是效果几乎没有。还是30多秒。请问我改如何优化。图是查询计划。

[RINK_1]

你这个表里的字段的类型都是什么呢

[吉普赛的歌]

现在中国有 14 亿人。数一遍需要 20 年。
你觉得太久了， 给每个人发了身份证， 每个人都有身份证号， 结果数一遍还是需要 20 年。
为什么有了身份证， 全部数一遍还是需要 20 年？？？

[zf_1987]

引用 1 楼 RINK_1 的回复:

你这个表里的字段的类型都是什么呢

大神，详见下图

[吉普赛的歌]

上面我说的问题， 你能自己想通， 就是知识了， 比直接告诉你结果更好。
关键在于： 身份证的作用是什么？

[zf_1987]

引用 2 楼 yenange 的回复:

现在中国有 14 亿人。数一遍需要 20 年。
你觉得太久了， 给每个人发了身份证， 每个人都有身份证号， 结果数一遍还是需要 20 年。
为什么有了身份证， 全部数一遍还是需要 20 年？？？

引用 2 楼 yenange 的回复:

现在中国有 14 亿人。数一遍需要 20 年。
你觉得太久了， 给每个人发了身份证， 每个人都有身份证号， 结果数一遍还是需要 20 年。
为什么有了身份证， 全部数一遍还是需要 20 年？？？

版主大大，但是我建立的是聚集索引啊，我之前看其他大神回复，说聚集索引是物理存储的，会将相近的数据存在一起，这样在存取的时候，会减少硬盘寻址的时间啊。我另一个表建立了一个聚集索引后，就快了很多。另外我这索引也不是主键，不是不重复的值，也不是完全重复的值，我这表有100万数据，但是我只distinct 查询单据号的话，只有10万条左右。

[zf_1987]

引用 4 楼 yenange 的回复:

上面我说的问题， 你能自己想通， 就是知识了， 比直接告诉你结果更好。
关键在于： 身份证的作用是什么？

版主大大，我冒昧的说下我的理解，
我如果不发身份证，我就需要去一家一家的采访，才知道中国有多少人。
但是我发了身份证，我就可以去警察局查了啊。
我理解的对吗？

[吉普赛的歌]

引用 6 楼 zf_1987 的回复:

[quote=引用 4 楼 yenange 的回复:]
上面我说的问题， 你能自己想通， 就是知识了， 比直接告诉你结果更好。
关键在于： 身份证的作用是什么？

版主大大，我冒昧的说下我的理解，
我如果不发身份证，我就需要去一家一家的采访，才知道中国有多少人。
但是我发了身份证，我就可以去警察局查了啊。
我理解的对吗？[/quote]
换个更简单的说法吧。
小学生的学号。
数一个班总共有多少人， 编学号和不编学号有区别吗？

[吉普赛的歌]

注意， 我说的是实际上来了的人数。

[zf_1987]

引用 8 楼 yenange 的回复:

注意， 我说的是实际上来了的人数。

我明白了，版本大大，你的意思是，如果是一一对应的话，我直接执行全表查询，没有where条件，实际上结果是一样的。
版本大大，我举个例子哈，比如一班有15人，每5个人分一组，分三排坐。我要清点全班人数，那么第一个我比较蠢，我数了1排的第一个人，然后数3排的第5个人，然后数2排的第一个....总之就是乱数，虽然最后没数错，但是中间走了不少路，数的很慢。
第二个我呢，比较聪明，先数完1组5个人，再数2组5个。再3组5个。这样少走很多路，效率高了不少。
我百度索引的时候，说聚集索引是有这个指路的逻辑的（可以优化数据在硬盘的逻辑位置，减少物理读盘时间）。也就是，我虽然都是全表查询，但是明显算法是可以优化的，现在我用一个不用索引的查询和一个加索引的查询，结果效率差不多。我很迷惑，关键是两种的速度都很慢，才提问的，
版主大大，我可能话比较多，但是是真心请教的。

[吉普赛的歌]

引用 9 楼 zf_1987 的回复:

[quote=引用 8 楼 yenange 的回复:]
注意， 我说的是实际上来了的人数。

我明白了，版本大大，你的意思是，如果是一一对应的话，我直接执行全表查询，没有where条件，实际上结果是一样的。
版本大大，我举个例子哈，比如一班有15人，每5个人分一组，分三排坐。我要清点全班人数，那么第一个我比较蠢，我数了1排的第一个人，然后数3排的第5个人，然后数2排的第一个....总之就是乱数，虽然最后没数错，但是中间走了不少路，数的很慢。
第二个我呢，比较聪明，先数完1组5个人，再数2组5个。再3组5个。这样少走很多路，效率高了不少。
我百度索引的时候，说聚集索引是有这个指路的逻辑的（可以优化数据在硬盘的逻辑位置，减少物理读盘时间）。也就是，我虽然都是全表查询，但是明显算法是可以优化的，现在我用一个不用索引的查询和一个加索引的查询，结果效率差不多。我很迷惑，关键是两种的速度都很慢，才提问的，
版主大大，我可能话比较多，但是是真心请教的。[/quote]

如果你认真读过其它人写的执行计划的文章， 就知道：
聚集索引扫描， 和 表扫描 是一回事。

学生上课了， 老师要统计哪些人进教室了， 一个个地数和学生报学号， 效率是等同的， 没有区别。
学号的作用， 是快速找到某个学生， 不是为了找全部的学生。

[吉普赛的歌]

一次性查 100 万数据， 有这个必要么？
如果只是给放在网站上给用户看， 那你可以分页， 每页只显示 20 条左右的记录。

[zf_1987]

引用 11 楼 yenange 的回复:

一次性查 100 万数据， 有这个必要么？
如果只是给放在网站上给用户看， 那你可以分页， 每页只显示 20 条左右的记录。

哈，版主大大莫怪，小弟也是在探索中，最近由于我们程序到几十万条数据的时候，执行速度就会很慢，客户意见很大，我们小公司，又没有特别懂数据库的。我也是硬着头皮上了。我总觉得光查询单个表的速度都这么慢，那与之关联的所有视图是不是都会很慢啊。
并且我改了一个表，建了一个聚集索引，速度真的有明显提升，，我这才心血来潮改别的表试下的。
那顺便我在问下版本大大，我这查询10万数据，耗时30秒因为是全表查询，所以可以忽略是where 语句的问题，那我这个表性能这么地下的瓶颈在哪儿呢？

[吉普赛的歌]

引用 12 楼 zf_1987 的回复:

[quote=引用 11 楼 yenange 的回复:]
一次性查 100 万数据， 有这个必要么？
如果只是给放在网站上给用户看， 那你可以分页， 每页只显示 20 条左右的记录。

哈，版主大大莫怪，小弟也是在探索中，最近由于我们程序到几十万条数据的时候，执行速度就会很慢，客户意见很大，我们小公司，又没有特别懂数据库的。我也是硬着头皮上了。我总觉得光查询单个表的速度都这么慢，那与之关联的所有视图是不是都会很慢啊。
并且我改了一个表，建了一个聚集索引，速度真的有明显提升，，我这才心血来潮改别的表试下的。
那顺便我在问下版本大大，我这查询10万数据，耗时30秒因为是全表查询，所以可以忽略是where 语句的问题，那我这个表性能这么地下的瓶颈在哪儿呢？[/quote]
没有怪你， 大家都是这么过来的，
客户实际用到的哪个 sql 慢， 就直接把那个 sql 拿出来分析， 不要自己加工。

where 不能去掉， 否则分析没有意义。

所有表必须有 真正的主键 ， 这个是必须的。

在服务器上执行下面的SQL, 把慢 SQL 拿出来分析：
但不要包括你的那些测试的不带 where 的语句

SELECT TOP 10 OBJECT_NAME(qt.objectid, qt.dbId)  AS procName,
       DB_NAME(qt.dbId)                   AS [db_name],
       qt.text                            AS SQL_Full,
       SUBSTRING(
           qt.text,
           (qs.statement_start_offset / 2) + 1,
           (
               (
                   CASE statement_end_offset
                        WHEN -1 THEN DATALENGTH(qt.text)
                        ELSE qs.statement_end_offset
                   END 
                   - qs.statement_start_offset
               ) / 2
           ) + 1
       )                                  AS SQL_Part --统计对应的部分语句
       ,
       qs.creation_time,
       qs.last_execution_time,
       qs.execution_count,
       qs.last_elapsed_time / 1000000     AS lastElapsedSeconds,
       qs.last_worker_time / 1000000      AS lastCpuSeconds,
       CAST(
           qs.total_elapsed_time / 1000000.0 / (
               CASE 
                    WHEN qs.execution_count = 0 THEN -1
                    ELSE qs.execution_count
               END
           ) AS DECIMAL(28, 2)
       )                                  AS avgDurationSeconds,
       CAST(qs.last_logical_reads AS BIGINT) * 1.0 / (1024 * 1024) * 8060 AS 
       lastLogicReadsMB,
       qs.last_logical_reads,
       qs.plan_handle
FROM   sys.dm_exec_query_stats qs
       CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) AS p
WHERE  qs.last_execution_time >= CONVERT(CHAR(10),GETDATE(),120)+' 08:00'	--今天8点之后的慢SQL
       AND qs.last_elapsed_time >= 3 * 1000 * 1000							--只取执行时间大于 3 秒的记录
       AND qt.[text] NOT LIKE '%Proc_DBA%'
ORDER BY
       qs.last_worker_time DESC

[sywcf]

首先,你如果直接在sqlserver中查询语句显示100W数据,那么30秒出来 是正常的.
但实际上,你直接全部显示这100W数据有意义吗? 实际你只需分页显示就够了. 这不能说明就是性能快与慢.

[xiaoxiangqing]

查询100w的数据出来，就需要时间

[weixin_41664753]

直接查询100W条数据，本身就需要时间，主键索引只对于检索有效。

[mzwhq]

引用 8 楼 yenange 的回复:

注意， 我说的是实际上来了的人数。

版主帮我看看我的问题好不好

[丁心驿]

本想想说两句的，
但这个点，看各位大神的代码都眼花了！
Good luck!

[hgwyl]

1、30秒没毛病，因为数据量就是100W
2、但是实际操作中没人显示那么多，请分页、加上查询条件，并且不要 select *，减少数据量
然后你就舒服了~