问题[query-performance](dba)

我有一个简单的查询，可以通过 query_plan_hash 和 query_hash显示前 10 个经过的时间：

PRINT '-- top 10 elapsed time  by query_plan_hash and query_hash --'

SELECT TOP 10 query_plan_hash
    ,query_hash
    ,sum(execution_count) AS 'execution_count'
    ,sum(total_worker_time) AS 'total_worker_time'
    ,SUM(total_elapsed_time) AS 'total_elapsed_time'
    ,SUM(total_logical_reads) AS 'total_logical_reads'
    ,max(REPLACE(REPLACE(REPLACE(SUBSTRING(CONVERT(NVARCHAR(4000), st.[text]), qs.statement_start_offset / 2 + 1, CASE 
                            WHEN qs.statement_end_offset = - 1
                                THEN LEN(st.[text])
                            ELSE qs.statement_end_offset / 2 - qs.statement_start_offset / 2 + 1
                            END), CHAR(13), ' '), CHAR(10), ' '), CHAR(09), ' ')) AS sample_statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
GROUP BY query_plan_hash
    ,query_hash
ORDER BY sum(total_elapsed_time) DESC;
GO

我如何计算：

• 总工作时间
• 总经过时间
• 逻辑读取总数

以分钟而不是毫秒为单位？

我有一个对非常大的表执行聚簇索引扫描的查询，该扫描在某些情况下会导致超时。需要帮助理解为什么它不使用定义的非聚集索引。

这是查询：

DECLARE @StartDate datetime = '2023-03-16 00:00:00';

DECLARE @TerminalIds [dbo].[udtBigInt]; -- user defined table with a BIGINT col
INSERT INTO @TerminalIds ([Id])
SELECT [EquipmentId]
FROM #mechanicsTerminal;

SELECT [DataRecId]
    , [RawData]
    , [RecordingTime]
    , [EquipmentId]
FROM [dbo].[Data]
WHERE [EquipmentId] IN (SELECT [Id] FROM @TerminalIds)
AND [RecordingTime] >= @StartDate
ORDER BY [DataRecId] DESC
OFFSET 0 ROWS FETCH NEXT 50 ROWS ONLY;

这是表定义：

CREATE TABLE [dbo].[Data](
    [DataRecId] [bigint] IDENTITY(1,1) NOT NULL,
    [RawData] [nvarchar](max) NOT NULL,
    [CreatedDateUTC] [datetime] NOT NULL,
    [RecordingTime] [datetime] NOT NULL,
    [EquipmentId] [bigint] NOT NULL,
    [DataSetId] [uniqueidentifier] NULL,
    [SourceType] [nvarchar](50) NULL,
    [Name] [nvarchar](100) NULL,
PRIMARY KEY CLUSTERED ( DataRecId] ASC)
GO
ALTER TABLE [EJ].[Data]  WITH CHECK ADD  CONSTRAINT [chk_Data_RawData] CHECK  ((isjson([RawData])=(1)))
GO

以下是索引：

CREATE INDEX [nc_Data_DataSetId_includes] 
ON [dbo].[Data] ( [DataSetId] ) INCLUDE ( [DataRecId], [RawData], [RecordingTime]);
GO
CREATE INDEX [nc_Data_EquipmentId_includes] 
ON [dbo].[Data] ( [EquipmentId] ) INCLUDE ( [DataSetId], [RawData]);
GO
CREATE INDEX [nc_Data_EquipmentId_RecordingTime_Name_includes] 
ON [dbo].[Data] ( [EquipmentId], [RecordingTime], [Name] ) INCLUDE ( [DataRecId], [RawData]);
GO

这是实际的执行计划：

https://www.brentozar.com/pastetheplan/?id=B1oq7TDD3

使用此特定数据，查询将在亚秒级执行。

然而，有一种情况是 中只有三个记录@TerminalIds，而 中没有匹配的记录[dbo].[Data]，查询永远不会完成。这是 45 秒后的计划。

https://www.brentozar.com/pastetheplan/?id=rJJMRavDn

我试过的：

• 更新统计数据并重新编译主过程
• 继续而不是用子句做子INNER JOIN查询@TerminalIdsIN

在收到如何获得有效排名计数器的答案后 ，我适应了我自己的系统。但是现在，我遇到了性能问题。我所有的请求都非常快（大多数请求不到 0.0005 秒），但是当使用ROW_NUMBER()多个模式时，它需要超过 0.2 秒。

这是一个完整的例子：

模式 1，名为sanctions，有一个名为的表bans，由以下内容组成：

• id, 自增字段
• uuid, 带有索引的 varchar
• 其他内容与问题无关

该表实际有 400 多行。

模式 2，名为stats，有一个名为的表players，由以下内容组成：

• id, 自增字段
• uuid, 带有索引的 varchar
• coins， 双倍的
• 其他内容与问题无关

该表实际有 2000 多行。

我的完整查询是这样的：

SELECT
   uuid,
   (SELECT count(*) FROM sanctions.bans WHERE uuid = p.uuid) as nb,
   row_number() OVER (order by coins DESC) counter
FROM stats.players p;

大约需要 0.22 秒。

现在，让我们逐部分检查：

运行时SELECT count(*) FROM arkbans.litebans_bans WHERE uuid = p.uuid（并用p.uuid一个值替换），我从不超过 0.0002 秒。

运行时：

SELECT
   uuid,
   row_number() OVER (order by coins DESC) counter
FROM stats.players p;

大约需要 0.0017 秒。

用ANALYZE钥匙：

随着ANALYZE FORMAT=JSON：在这里

查询分析：

WITH Bans AS
(
    SELECT uuid, COUNT(*) AS BanCount
    FROM sanctions.bans
    GROUP BY uuid
)
 
SELECT
   p.uuid,
   COUNT(b.BanCount) as nb,
   row_number() OVER (order by MAX(p.coins) DESC) counter
FROM stats.players p
LEFT JOIN Bans b ON p.uuid = b.uuid

我该如何解决这个性能问题？

注意：这里的“硬币”栏是一个例子。实际上，超过 60 个列将使用此请求。所以像这样为每一列添加索引对我来说不是一个选项（索引太多，值太多）

注意 2：抱歉，不能做 db fiddle，因为它在多个数据库上并且有很多数据。

我正在尝试提高以下查询的性能，该查询需要 1 分钟以上的时间才能执行：

SELECT *
FROM   test
WHERE  ( created_at < '2023-3-31 06:10:20.871' )
       AND ( ( id > '2a95048f' )
              OR ( id = '2a95048f'
                   AND created_at > '2022-12-27 23:53:24.958' ) )
ORDER  BY id ASC,
          created_at ASC
LIMIT  1000; 

我通过切换 ORDER BY 中的顺序来更改查询，查询在 549 毫秒内返回结果：

SELECT *
FROM   test
WHERE  ( created_at < '2023-3-31 06:10:20.871' )
       AND ( ( id > '2a95048f' )
              OR ( id = '2a95048f'
                   AND created_at > '2022-12-27 23:53:24.958' ) )
ORDER  BY created_at ASC,
          id ASC
LIMIT  1000; 

以下是有关索引的一些信息：

• ID - 基数 680 万，唯一
• CREATED_AT - 基数 710 万，不是唯一的

为什么一个比另一个性能显着？

更新 - “显示来自测试的索引”

我正在使用 Microsoft SQL Server 2017，最近我们遇到了一种情况，我们看到等待类型 IO_COMPLETION 贡献了 60%。执行更新统计后，这个问题就消失了。有一个使用表变量的过程，我们正在观察单个查询级别的 IO_COMPLETION 等待类型。对数据库执行更新统计是否会解决该特定过程中发生的 IO_COMPLETION 问题？

我正在使用 Postgres 12.10 AWS RDS。我使用 group by 查找最小日期的查询比日期上的常规最小值要快。我希望常规 min 也一样快，但不确定我是否输入了错误的索引或者我需要调整另一个参数。

我有一张桌子

CREATE TABLE IF NOT EXISTS public.ed
(
    isd character varying(90) COLLATE pg_catalog."default" NOT NULL,
    e_id character varying(32) COLLATE pg_catalog."default" NOT NULL,
    d_date timestamp with time zone NOT NULL,
    CONSTRAINT ed_pkey PRIMARY KEY (isd, e_id)
)

指数：

CREATE INDEX IF NOT EXISTS ix_ed_d_date
    ON public.ed USING btree
    (d_date ASC NULLS LAST)
    TABLESPACE pg_default;

CREATE INDEX IF NOT EXISTS ix_ed_e_id
    ON public.ed USING btree
    (e_id COLLATE pg_catalog."default" ASC NULLS LAST)
    TABLESPACE pg_default;

查询只需min3 分钟：

select min(d_date)
from ed
where e_id = '62e2032b029b036ba25c73cf';

解释分析查询：

Result  (cost=171.70..171.71 rows=1 width=8) (actual time=186940.968..186941.463 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.56..171.70 rows=1 width=8) (actual time=186940.963..186940.964 rows=1 loops=1)
          ->  Index Scan using ix_ed_d_date on ed  (cost=0.56..2214942.25 rows=12943 width=8) (actual time=186940.961..186940.962 rows=1 loops=1)
                Index Cond: (d_date IS NOT NULL)
                Filter: ((e_id)::text = '62e2032b029b036ba25c73cf'::text)
                Rows Removed by Filter: 30539883
Planning Time: 0.195 ms
Execution Time: 186941.491 ms

虽然使用 group by 的查询不到一秒钟：

select min(d_date)
from ed
where e_id in ('62e2032b029b036ba25c73cf')
group by e_id;

解释分析：

GroupAggregate  (cost=0.56..5365.73 rows=2319 width=33) (actual time=92.093..92.095 rows=1 loops=1)
  Group Key: e_id
  ->  Index Scan using ix_ed_e_id on ed  (cost=0.56..5277.83 rows=12943 width=33) (actual time=6.753..90.622 rows=6698 loops=1)
        Index Cond: ((e_id)::text = '62e2032b029b036ba25c73cf'::text)
Planning Time: 0.098 ms
Execution Time: 92.127 ms

我得到相同的结果，但为什么使用d_date索引更简单的查询？我怎样才能使简单min(d_date)而又不group by具有使用的性能group by？

我使用的应用程序使用 SQL Server 数据库，其中包括许多保存单行配置数据的表，在针对更传统的多行表的查询中有时需要这些表。我见过的大多数代码在处理单个查询时通过连接访问这些表，但在最近的一次代码审查中，我看到了一种使用标量子查询的方法，大致如下：

Select T.Id
From dbo.SomeTable T
Where T.SomeValue > (Select Tolerance From dbo.Settings)

虽然它显然有效，但我最初的反应是假设它违反了我们的标准做法，但我对表单进行了一些试验，发现“子查询返回超过 1 个值。当子查询如下时，这是不允许的=、!=、<、<=、>、>= 或当子查询用作表达式时”错误。这使得这似乎避免了意外 1:n 连接导致不良行为的风险。（在实践中，这些单行表不应该担心，它们相当健壮，但我已经看到它出现在系统的其他地方。）

除了（可能非常便宜）Stream Aggregate 和 Assert 之外，我的简单测试用例的执行计划看起来非常相似，我认为它们负责查询引擎在多行案例中识别和抛出错误的能力。

使用这种表格是否有普遍接受的最佳实践？在选择方法时，我应该注意哪些主要优点和缺点？

（我知道使用变量来保存数据也是一种选择，但在我们的某些代码中这样做并不总是可行的，所以我想专注于比较这两种方法和/或任何其他方式将其折叠成一个查询。）

Azure SQL 数据库。

我有一个表，我需要从中获取第一行和最近的行，Col1并Col2基于CreateDate.

CREATE TABLE dbo.table1 (
    Id            INT    IDENTITY(1,1) PRIMARY KEY ,
    Col1        VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
    Col2        VARCHAR(255) COLLATE SQL_Latin1_General_CP1_CS_AS NOT NULL ,
    CreateDate    DATETIME NOT NULL
) ;

我有一个像这样的索引：

CREATE INDEX IX__table1_ASC
ON dbo.table1 (Col1, Col2, CreateDate );

我获取第一行的查询是（在此处计划）：

--Get the first row
SELECT    TOP (1) WITH TIES
        *
FROM    table1
ORDER BY ROW_NUMBER()
        OVER (PARTITION BY Col1, Col2
              ORDER BY CreateDate );

索引扫描使用的是IX__table1_ASC我创建的索引 ()，但为什么我得到一个排序？

我获取最新行的查询（在此处计划）：

--get latest row
SELECT    TOP (1) WITH TIES
        *
FROM    table1
ORDER BY ROW_NUMBER()
        OVER (PARTITION BY Col1, Col2
              ORDER BY CreateDate DESC); --desc here

同样，索引扫描使用的是索引 ( IX__table1_ASC)，但这次我得到了两种。索引扫描后的第一个。优化器还不够聪明，无法以相反的顺序读取索引吗？再说一次，第二类是干什么用的？

实际的表非常大，因此您可以想象排序的成本很高。我怎样才能在这里最好地优化？

在关于“索引对性能的影响”的课程中，讲师使用此示例向我们展示了准备索引如何提高首次查询的性能：

SELECT
    SOH.CustomerID,
    SOH.SalesOrderID,
    SOH.OrderDate,
    C.TerritoryID,
    ROW_NUMBER() OVER ( PARTITION BY SOH.CustomerID
                        ORDER BY SOH.OrderDate ) AS Row_Num
FROM Sales.SalesOrderHeader AS SOH
JOIN Sales.Customer AS C
    ON SOH.CustomerID = C.CustomerID;
GO

第二个查询：

WITH Sales
AS
(
    SELECT
        CustomerID,
        OrderDate,
        SalesOrderID,
        ROW_NUMBER() OVER ( PARTITION BY CustomerID
                            ORDER BY OrderDate ) AS Row_Num
    FROM Sales.SalesOrderHeader
)
SELECT
    Sales.CustomerID,
    Sales.SalesOrderID,
    Sales.OrderDate,
    C.TerritoryID,
    Sales.Row_Num
FROM Sales
JOIN Sales.Customer AS C
    ON C.CustomerID = Sales.CustomerID;
GO

两个查询都返回相同的输出，但第二个查询的成本非常低： 不幸的是，我无法理解成本差异的原因

我有一个数据库，由以下列组成

• id, 一个看起来像这样的字符串8b28347448d3fff（15 长）
• x, 小数 (8,6)
• y, 小数 (9,6)

所有列上都有索引。现在，我想找到匹配的对。在表侧foo，最多可以有 300k 行。我能想到的查询表的方法有两种。首先，这个：

使用WHERE ... IN. 在查询方面，可能有多达 11k 个元素possible_matching_indexes。

SELECT id FROM foo WHERE id IN (possible_matching_indexes);

另一个是这个，它只会在查询端产生四个值（ x1, x2, y1, ）y2

SELECT id FROM foo WHERE (x BETWEEN x1 and x2) AND (x BETWEEN y1 and y2);

哪一个更有可能表现更好？我正在使用 SQLite 数据库。但我想这可以从任何基于 SQL 的数据库中估计出来？