SQL 选择在具有 25M 记录的表上花费太多时间

Question

我需要从日期范围内有 25,000,000 行记录的LogTable中进行选择

SET STATISTICS TIME ON 
SET STATISTICS IO ON
SELECT LogId, LogDate FROM LogTable 
WHERE LogDate >= '10/4/2017 12:00:00 AM' and LogDate < '10/4/2018 9:32:29 AM'

SQL Server parse and compile time: 
CPU time = 0 ms, elapsed time = 0 ms.
(5258811 row(s) affected)
Table 'LogTable'. Scan count 1, logical reads 16974, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. 
SQL Server Execution Times:
CPU time = 2125 ms,  elapsed time = 30464 ms.

有哪些选项可以提高 MS SQL 2014 的性能？我读过关于表分区的信息，但我不确定这是否有帮助。

Answer 1

您正在从 25M 行中选择 5M 行：也就是说，您正在选择表的 20%。

典型的索引在如此高的百分比下不会真正起作用。

但是，您仍然可以使用可以使其更快的"覆盖索引"。试试看：

create index ix1 on LogTable (LogDate, LogId);