全部问题(dba)

我在生产实例中遇到了这个问题，因此尝试了小规模的本地重新创建并成功完成。

我有一个简单的测试表

CREATE TABLE `test` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `val` varchar(255) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index2` (`val`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci

我正在使用8.0.26带有innodb存储引擎的Mysql 版本

我在这个表中填充了大约 400 万行随机数据。从这个表中选择 5000 行大约需要 50 毫秒。然后我使用DELETE FROM test;

删除所有行后，如果我再运行SELECT * FROM test LIMIT 5000;此查询，则需要 1 分钟以上才能运行。当选择查询最终完成运行时，它返回 0 行（如预期的那样）。

在继续之前，我只想澄清一下，我知道运行optimize table将修复该问题，但我有兴趣了解幕后到底发生了什么。以下是我的一些想法

1. 大约一小时后，查询再次变为即时查询。这让我相信某种垃圾收集或清理正在幕后进行。但我在文档中找不到有关此内容的任何信息。
2. 删除后，查询速度明显变慢。由于我在选择查询中未使用任何未索引子句，因此在删除前大约需要 50 毫秒才能解决，因此删除后速度会慢 1500 倍。
3. 我研究过“幽灵记录”的概念，但是我能找到的所有结果都只涉及 SQL Server，而不是 Mysql，这让我相信“幽灵记录”的概念可能不存在于 Mysql 中。我在 Mysql 文档中找不到关于此内容的任何提及。

我尝试了慢查询日志，希望能看到一堆扫描的数据，即使它已被删除（幽灵记录），但除了持续时间之外，我看不到任何奇怪或意外的东西

# Time: 2025-02-11T10:29:24.510606Z
# User@Host: root[root] @  [172.17.0.1]  Id: 20978
# Query_time: 69.680895  Lock_time: 0.000135 Rows_sent: 0  Rows_examined: 0 Thread_id: 20978 Errno: 0 Killed: 0 Bytes_received: 0 Bytes_sent: 130 Read_first: 1 Read_last: 0 Read_key: 1 Read_next: 0 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_disk_tables: 0 Created_tmp_tables: 0 Start: 2025-02-11T10:28:14.829711Z End: 2025-02-11T10:29:24.510606Z
SET timestamp=1739269694;
select * from test
LIMIT 0, 5000;

有人能详细解释一下这里到底发生了什么吗？理想情况下，如果可能的话，我想知道的两件事是：

1. 到底发生了什么事？
2. 是否可以通过查看一些内部 Mysql 统计数据来查看/诊断此问题？例如，innodb 引擎状态/information_schema/performance_schema。基本上，我可以查看并判断某个表是否存在此问题，而无需知道刚刚运行了删除操作

以下是选择的解释 -SELECT * FROM test LIMIT 5000

删除前（快速 50 毫秒）

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'test', NULL, 'index', NULL, 'index2', '1022', NULL, '6958223', '100.00', 'Using index'

删除后（慢 69 秒） - 我运行analyze table test更新统计信息，然后重新运行选择并解释

id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'test', NULL, 'index', NULL, 'index2', '1022', NULL, '1', '100.00', 'Using index'

我有一张包含玩家 ID 和姓名的表格。每轮比赛，表格都会被打乱，分成两半，然后并排摆放。处理每组中行数不等的情况不属于本问题的范围。

我唯一需要实现的是 (上半部分 + 下半部分) 或 (表 a + 表 b) 并排。具体来说，表 a 原样 + b 的两列，彼此之间没有任何引用 - 逐行。

我准备了一些简单的小提琴：

https://dbfiddle.uk/vkahsaFj

https://dbfiddle.uk/ASM405Co

一些参考资料：

https://stackoverflow.com/questions/7983369/how-do-i-combine-a-mysql-query-and-subquery-when-theres-multiple-rows

结果一定是这样的：

我尝试了所有类型的连接、临时表、分组等，但都没有成功。

分区的好处文档列出了以下内容作为分区表的第一个性能优势

当分区列与表连接的列相同时，查询优化器可以更快地处理两个或多个分区表之间的等值连接查询。

同一页面稍后会就此主题进行更详细的说明，但这只是告诉您，连接未以完全相同方式分区的分区表不会获得与连接等效分区表相同的优化。当然，这与前面部分的说法无关；两种分区表方式之间的任何性能比较都与分区与不分区相比的优势无关。

这让我产生了一个疑问：连接等效分区表是否比连接具有相同前导聚集索引键的两个表更快？我很乐意接受实验性的答案或使用相关理论得出结论的答案。

我读过内部原理，也读过很多白皮书和博客，但我认为我没有在任何地方看到过这个问题。我对内部原理的理解让我得出结论，分区方法应该比非分区方法慢。毕竟，分区实际上所做的就是将另一个索引键放在键列表的前面。我自己的实验也发现了同样的结果。

我注意到，对于 PostgreSQL 中的表（或视图或物化视图），pg_depend 中有一行将表的 pg_class 行与其模式的 pg_namespace 行链接起来。

但是，我想知道是否存在这样的情况：pg_depend 中的一行可以将 pg_class 行链接到它不在其中的模式的 pg_namespace 行。

这是在如何在不顺序扫描 pg_class 的情况下列出模式中的表？的上下文中，我试图找到一个有效的查询来查找模式中的表，我想知道我是否必须处理这种情况。

PostgreSQL 有一个用于表、函数、视图、规则等的“ OR REPLACE”变体（如“ CREATE OR REPLACE ____”），但是它没有用于类型的变体。

我可以去提交错误报告，但我想这是维护人员的故意决定。我的问题是：为什么？

换句话说，类型有何不同使得它们不需要OR REPLACE（即使表、函数等需要）？

PS 我刚刚发现它确实有一个DROP TYPE IF EXISTS... 这让我更加困惑。它似乎真的CREATE TYPE被挑出来不获得IF EXISTS，我只是不明白为什么。

在 PostgreSQL 中，我想列出模式中的表。我有以下针对公共模式执行此操作的查询，例如：

SELECT
  relname
FROM
  pg_class
WHERE
  relnamespace = 'public'::regnamespace
  AND relkind = 'r';

但是，在我的系统上，这需要大约 400-500 毫秒（有时可能需要更长时间）。我怀疑这是因为 pg_class 有超过 500 万行，大小接近 5GB。

查看查询的解释分析，我看到了以下内容：

                                                        QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------
 Gather  (cost=1000.00..356932.33 rows=14 width=64) (actual time=2.190..418.126 rows=120 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Parallel Seq Scan on pg_class  (cost=0.00..355930.92 rows=6 width=64) (actual time=5.318..375.222 rows=40 loops=3)
         Filter: ((relnamespace = '2200'::oid) AND (relkind = 'r'::"char"))
         Rows Removed by Filter: 1792453
 Planning Time: 0.070 ms
 Execution Time: 418.162 ms

我发现它对 pg_class 进行了顺序扫描。查看 pg_class 上的索引，这并不奇怪 - relnamespace 列上没有索引（至少，没有以 relnamespace 作为其第一列的索引）。

那么，在尝试查找模式中的表时，如何避免对 pg_class 进行顺序扫描？这可能吗？

在 Neo4j 中，当我使用以下查询时：

MATCH (p:Person)-[:ACTED_IN]->(m) WHERE 'Neo' IN r.roles RETURN p

那么它只返回一个 Person 节点。

但是当我将查询更改为：

MATCH (p:Person)-[:ACTED_IN]->(m) WHERE 'Neo' IN r.roles RETURN p.name

然后它返回 3 行。

这对我来说很奇怪，因为我预计只应该返回一行？

对于兼容性测试，我们需要 MySQL v8.0 中可用的所有数据类型的完整列表，以确保 MySQL 生态系统之外的目标软件能够正确处理来自 MySQL 源的所有数据类型。

ChatGPT 让我尝试以下查询：

SELECT DISTINCT DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS
ORDER BY DATA_TYPE;

但它只返回我们连接的 MySQL 实例上使用的类型。例如，我们的查询返回了 24 种类型，而 ChatGPT 后来告诉我 MySQL 总共支持 36 种类型。

我们也参考了以下官方在线文档，但是它并没有提供整体的列表，而且我们也担心该文档可能在实现方面有所欠缺。

• https://dev.mysql.com/doc/refman/8.0/en/data-types.html

如果我们想以编程方式解决问题，请给我们举一个例子。

或者，我们是否可以参考一个开源存储库。请给我们一些指点，因为我们不熟悉开源世界的存储库。

我们非常感谢任何提示和建议。

我们有一个可用性组，由 42 个数据库组成，其中一个为同步副本，另一个为异步副本。同步副本能够跟上主副本（根据定义），但异步副本中的大多数数据库在工作日开始时会冻结其进度，直到工作日结束前都不会取得进展。以下图表显示了数据库的 AG 滞后，由last_commit_timesys.dm_hadr_database_replica_states计算得出

对于大多数数据库来说，该last_commit_time时间大约在上午 9:00 左右，直到工作日结束。以下引述来自故障排除：异步提交可用性组副本可能带来的数据丢失

以下部分介绍了异步提交辅助副本可能出现高数据丢失风险的常见原因，假设您的服务器实例中没有与可用性组无关的系统性性能问题。

1. 网络延迟高或网络吞吐量低导致主副本上日志累积

2. 磁盘 I/O 瓶颈减慢了辅助副本上的日志强化

大约一半的数据库使用Microsoft Azure 中的 SQL Server 数据文件功能。这些数据库的 I/O 性能非常差，以至于我有时会在错误日志中看到 15 秒的 I/O 警告。让我感到困惑的是，大多数延迟最严重的数据库都不在 Azure 存储上。我能看到的唯一模式是，具有最佳 I/O 性能的数据库似乎具有最大的延迟。

如果可用性组中有多个数据库，那么一个 I/O 性能较差的数据库是否可能导致另一个 I/O 性能良好的数据库滞后？

如果需要的话，请提供一些有关我的情况的额外技术细节：

• SQL Server 版本是 Microsoft SQL Server 2019 (RTM-CU30)。

• 托管异步副本的数据中心距离主数据中心 1800 英里。两个数据中心都在美国。

• 从异步副本到主副本的 Ping 时间为 40-50 毫秒。我多次被告知，我们没有达到某种整体网络吞吐量限制。netttcp 测试显示带宽容量为 25-30Mbit/秒。

• 根据hadr_database_flow_control_action扩展事件，工作日内未取得进展的数据库实际上 100% 的时间都在等待流量控制。滞后较少的数据库无需花时间等待流量控制。

• 根据hadr_transport_flow_control_action扩展事件，传输层上没有任何等待流量控制的情况。Sends to Transport/sec性能计数器似乎证实了这一点。

• 跟踪标志 12310 无论如何都已启用并且没有明显的效果。

• 对于存储在 Azure 之外的异步数据库，写入延迟在 1-10 毫秒之间。对于存储在 Azure 中的数据库，写入延迟高达 400 毫秒。

• 使用Bytes Sent to Replica/secperfmon 计数器，异步副本能够保持到上午 8:30 左右。然后它就落后了，只有在几个小时后出现一些巨大的进展后才能赶上。

• perfmon计数器Log Bytes Flushed/sec（一天中部分时间的数据丢失）似乎与我们看到的发送字节数相符。最终用户活动在下午 4:30 左右开始下降，此时异步副本在追赶方面取得了更多进展。

• 我于 2025 年 2 月 6 日生成了一份 AG 延迟报告，瓶颈在于主服务器上的发送和流量控制。我不知道如何跟进这些结果：

• 我知道将使用 Microsoft Azure 中的 SQL Server 数据文件功能的数据库放入异步 AG 可能是一种不寻常的设置。我们有效地将数据发送到 1800 英里外只是为了将其发送到云端。微软关于该功能的文档表明这可能没有必要：

高可用性和灾难恢复优势：使用 Microsoft Azure 中的 SQL Server 数据文件功能可能会简化高可用性和灾难恢复解决方案。例如，如果 Microsoft Azure 中的虚拟机或 SQL Server 实例崩溃，您只需重新建立与 blob 的链接即可在新的 SQL Server 实例中重新创建数据库。

我目前面临 SQL Server 中参数化查询的问题，我不知道它的根源在哪里。

我将其分解为一个简单的例子：

假设有一张表，其中包含有关某个子实体的数据以及 ，以及parent_id上的相应索引parent_id。基于此访问数据，parent_id但通过视图访问，该视图除了表数据外，还包含一列，用于计算row_number按 分区的所有条目parent_id。

可重复的设置

创建表、索引和视图如下：

CREATE TABLE dbo.test (id BIGINT IDENTITY(1,1), text NVARCHAR(255), parent_id BIGINT);
GO

CREATE NONCLUSTERED INDEX idx_test_parent_id
       ON dbo.test (parent_id);
GO

CREATE VIEW dbo.test_view
AS
  SELECT *, ROW_NUMBER() OVER (PARTITION BY parent_id ORDER BY id) AS row_num
  FROM dbo.test
GO

现在将一些数据放入表中：

DECLARE @i BIGINT = 0
WHILE @i < 200000
BEGIN
    SET @i = @i + 1
    INSERT INTO dbo.test (text, parent_id)
    VALUES ('test 1', @i), ('test 2', @i), ('test 3', @i);
END

问题

当通过视图中的参数化查询访问数据时，SQL Server 将对表进行完整扫描。

DECLARE @parent_id BIGINT = 123456
SELECT *
FROM dbo.test_view
WHERE parent_id = @parent_id

而当直接访问数据（不使用参数）时，我们将得到预期的索引查找。

SELECT *
FROM dbo.test_view
WHERE parent_id = 123456

我尝试过

搜索不同的论坛，我不太明白这里发生了什么。我发现了类似的问题，参数的数据类型错误，因此性能很差，但这对我来说不是问题。我也读过有关参数嗅探的问题，但我不认为这是问题，因为我不通过存储过程或函数访问数据。

此外，当我使用参数化查询直接从表中访问数据时，不会发生此问题。即使使用参数，也会进行索引查找。

当我将OPTION (RECOMPILE)查询添加到使用参数化查询访问视图的查询时，也会发生同样的情况，SQL Server 最终会执行索引查找。

问题

有人能解释一下这里的问题吗？为什么这是视图的问题而不是表本身的问题？我真的需要row_number在插入/删除期间摆脱以不同方式计算的视图吗？

设置

• 在 Docker 容器中运行的 SQL Server 2022 v16.0.4165
• Docker 映像：mcr.microsoft.com/mssql/server:2022-latest

当然，实际的表有一个主键。但它也有很多列，而不仅仅是text列。将所有这些列都包含在索引中是可能的。不过，从表本身进行选择时不会出现此问题，所以在我看来，这似乎不是索引的问题。

我不知道我正在以兼容模式运行数据库。在生产环境中我甚至得到了CardinalityEstimationModelVersion="140"。我不认为我故意在任何地方设置了它。

执行计划

• 直接选择索引查找
• 带全表扫描的参数化选择
• 使用全表扫描QUERY_OPTIMIZER_COMPATIBILITY_LEVEL_150