可以知道行存储索引的使用量,但是全文呢?
我在任何 GUI 或 DMV 中都找不到任何内容。甚至 dbatools 也不支持它。
我继承了一个具有一些全文索引的服务器,但我不知道是否需要它们。这个问题是我尝试获取所需的数据,以便在丢弃它们时感到安全。
可以知道行存储索引的使用量,但是全文呢?
我在任何 GUI 或 DMV 中都找不到任何内容。甚至 dbatools 也不支持它。
我继承了一个具有一些全文索引的服务器,但我不知道是否需要它们。这个问题是我尝试获取所需的数据,以便在丢弃它们时感到安全。
假设我有一张桌子。它的主键是聚集的并且仅由一列组成。是否有理由在同一列上创建非聚集索引,并且非聚集索引的任何部分中都没有其他列?
我目前正在为一个项目构建一些功能,客户可以通过 UI 更改他们的数据库架构,并且我们处理运行更新数据库 (PostgreSQL) 所需语句的所有细节。
我对一些陷阱可能是什么进行了一些研究,我们遇到的陷阱之一是索引名称,尤其是创建UNIQUE
约束时自动生成的索引名称。
我们允许用户声明他们想要对哪一列建立索引,但我们没有意识到,现在每当他们indexed
在列上设置为 true时,我们就会创建两个索引UNIQUE
。
现在这个问题已经解决了,但是这提出了一个问题:重命名列时会发生什么。就我们的测试而言,重命名列不会更改索引名称,然后当使用先前占用的名称创建新列时,会创建一个附加数字的索引:
CREATE TABLE users (
user_id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL
);
ALTER TABLE "users" RENAME COLUMN "email" TO "email_one";
ALTER TABLE "users" ADD COLUMN "email" INTEGER UNIQUE;
/*
postgres@localhost:postgres> \d users
+-----------+------------------------+----------------------------------------------------------+
| Column | Type | Modifiers |
|-----------+------------------------+----------------------------------------------------------|
| user_id | integer | not null default nextval('users_user_id_seq'::regclass) |
| email_one | character varying(255) | not null |
| email | integer | |
+-----------+------------------------+----------------------------------------------------------+
Indexes:
"users_pkey" PRIMARY KEY, btree (user_id)
"users_email_key" UNIQUE CONSTRAINT, btree (email_one)
"users_email_key1" UNIQUE CONSTRAINT, btree (email)
*/
由于我不是数据库专家,也没有维护过大型数据库,所以我不确定这是否会导致问题。我问了我的一个朋友,他给了我以下功能,我可以使用它来代替RENAME TO
:
CREATE OR REPLACE FUNCTION rename_column_and_index(
tbl_name TEXT,
old_col_name TEXT,
new_col_name TEXT
) RETURNS void AS $$
DECLARE
index_record RECORD;
old_index_suffix TEXT;
new_index_name TEXT;
BEGIN
-- Find all indexes that contain the old column name
FOR index_record IN
SELECT indexname
FROM pg_indexes
WHERE tablename = tbl_name
AND indexdef LIKE old_col_name || '%'
LOOP
-- Start a savepoint
EXECUTE 'SAVEPOINT before_rename';
BEGIN
-- Extract the suffix from the old index name
old_index_suffix := substring(index_record.indexname from length(quote_ident(old_col_name)) + 1);
-- Create the new index name
new_index_name := quote_ident(new_col_name) || old_index_suffix;
-- Rename the column
EXECUTE 'ALTER TABLE ' || quote_ident(tbl_name) ||
' RENAME COLUMN ' || quote_ident(old_col_name) ||
' TO ' || quote_ident(new_col_name);
-- Rename the index
EXECUTE 'ALTER INDEX ' || quote_ident(index_record.indexname) ||
' RENAME TO ' || new_index_name;
EXCEPTION
WHEN OTHERS THEN
-- Rollback to the savepoint in case of error
EXECUTE 'ROLLBACK TO SAVEPOINT before_rename';
RAISE;
END;
-- Release the savepoint
EXECUTE 'RELEASE SAVEPOINT before_rename';
END LOOP;
END;
$$ LANGUAGE plpgsql;
-- SELECT rename_column_and_btree_index('users', 'email', 'email_one');
现在我的问题是:我是否太害怕这个问题了?这个功能有什么好处/让以后的维护更容易吗?或者我可以完全不用担心自动索引吗?
首先,对篇幅表示歉意——这是一个奇怪的问题,所以我试图提供足够的细节以使其可重现。
因此,我认为在两个 MS SQL Server 之间导出数据层应用程序*.bacpac
以迁移 Indeo ProGet 数据库实例时发现了一个问题,这会创建不一致的 bacpac。尝试导入此 bacpac 会导致错误,我希望能有第二双眼睛来关注这个问题,以确保我没有做任何愚蠢的事情......
我认为根本原因是“导出数据层应用程序”过程中的一个可能的错误,当有多个架构列使用相同的用户定义类型时,并且 UDT 绑定到验证规则 - 结果似乎是一个损坏的*.bacpac
文件如果不先进行一些手动调整,就无法导入。
我的问题是:
下面从 Inedo ProGet 数据库重新创建了一小部分架构来演示该问题。(FWIW,我目前使用的是 SQL Server 2022 v16.0.1105.1,但我很确定它也会出现在其他版本中)。
它基本上是这样做的:
YNINDICATOR
Y
将其绑定到将值限制为和 的规则N
YNINDICATOR
(请参阅[dbo].[CustomLanguage].[Active_Indicator]
和[dbo].[ClusterNodes].[Primary_Indicator]
)CREATE TYPE [dbo].[YNINDICATOR] FROM [char](1) NULL
GO
CREATE RULE [dbo].[YNINDICATOR_Domain]
AS
@Ind COLLATE Latin1_General_BIN IN ('Y', 'N');
EXEC sp_bindrule 'YNINDICATOR_Domain', 'YNINDICATOR'
GO
CREATE TABLE [dbo].[CustomLanguages](
[CustomLanguage_Id] [int] IDENTITY(1,1) NOT NULL,
[Culture_Name] [varchar](50) NOT NULL,
[Language_Name] [nvarchar](100) NOT NULL,
[Active_Indicator] [dbo].[YNINDICATOR] NOT NULL,
[CustomLanguage_Xml] [xml] NOT NULL,
CONSTRAINT [PK__CustomLanguages] PRIMARY KEY CLUSTERED
(
[CustomLanguage_Id] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
CREATE TABLE [dbo].[ClusterNodes](
[Server_Name] [nvarchar](64) NOT NULL,
[NodeType_Code] [char](1) NOT NULL,
[LastUpdated_Date] [datetime] NOT NULL,
[Primary_Indicator] [dbo].[YNINDICATOR] NOT NULL,
[Node_Configuration] [xml] NOT NULL,
CONSTRAINT [PK__ClusterNodes] PRIMARY KEY CLUSTERED
(
[Server_Name] ASC,
[NodeType_Code] ASC
)WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[ClusterNodes] WITH CHECK ADD CONSTRAINT [CK__ClusterNodes__NodeType_Code] CHECK (([NodeType_Code]='S' OR [NodeType_Code]='W'))
GO
ALTER TABLE [dbo].[ClusterNodes] CHECK CONSTRAINT [CK__ClusterNodes__NodeType_Code]
GO
将上述架构导出到数据层应用程序 bacpac 文件中完成,没有任何错误,但尝试将其作为新数据库重新导入时会出现以下错误:
微软 SQL Server 管理工作室
无法导入包。
错误 SQL72014:框架 Microsoft SqlDataClient 数据提供程序:MSG 2714,级别 16,状态 3,过程 YNINDICATOR_Domain,第 1 行 数据库中已存在名为“YNINDICATOR_Domain”的对象。
错误 SQL72045:脚本执行错误。执行的脚本:
创建规则 [dbo].[YINDICATOR_Domain]
AS @Ind COLLATE Latin1_General_BIN IN ('Y', 'N')
(微软.SqlServer.Dac)
我解压了 bacpac,发现当使用 UDT 定义多个YNINDICATOR
列时,model.xml
bacpac 内的文件包含类似这样的内容,其中定义了两个 SqlRule
元素[dbo].[YNINDICATOR_Domain]
- 第一个包含列引用,第二个包含 UDT参考:
bacpac 损坏 - 两个列引用和一个 UDT 引用 -> 两个 SqlRule 元素
... snip ...
<Element Type="SqlRule" Name="[dbo].[YNINDICATOR_Domain]">
<Property Name="ExpressionScript">
<Value><![CDATA[@Ind COLLATE Latin1_General_BIN IN ('Y', 'N')]]></Value>
</Property>
<Relationship Name="BoundTargets">
<Entry>
<References Name="[dbo].[CustomLanguages].[Active_Indicator]" />
</Entry>
<Entry>
<References Name="[dbo].[ClusterNodes].[Primary_Indicator]" />
</Entry>
</Relationship>
<Relationship Name="Schema">
<Entry>
<References ExternalSource="BuiltIns" Name="[dbo]" />
</Entry>
</Relationship>
</Element>
<Element Type="SqlRule" Name="[dbo].[YNINDICATOR_Domain]">
<Property Name="ExpressionScript">
<Value><![CDATA[@Ind COLLATE Latin1_General_BIN IN ('Y', 'N')]]></Value>
</Property>
<Relationship Name="BoundTargets">
<Entry>
<References Name="[dbo].[YNINDICATOR]" />
</Entry>
</Relationship>
<Relationship Name="Schema">
<Entry>
<References ExternalSource="BuiltIns" Name="[dbo]" />
</Entry>
</Relationship>
</Element>
... snip ...
这可能是错误的原因,already an object named 'YINDICATOR_Domain'
因为它尝试创建 SqlRule 两次 - 每个创建一次Element
。
如果我使用 UDT删除任一列YNINDICATOR
并重新导出数据层应用程序,则 bacpac 包含一个Element Type="SqlRule"
节点,其中包含该列和UDT 引用:
工作 bacpac - 一列引用和 UDT 引用 -> 一个 SqlRule 元素
... snip ...
<Element Type="SqlRule" Name="[dbo].[YNINDICATOR_Domain]">
<Property Name="ExpressionScript">
<Value><![CDATA[@Ind COLLATE Latin1_General_BIN IN ('Y', 'N')]]></Value>
</Property>
<Relationship Name="BoundTargets">
<Entry>
<References Name="[dbo].[CustomLanguages].[Active_Indicator]" />
</Entry>
<Entry>
<References Name="[dbo].[YNINDICATOR]" />
</Entry>
</Relationship>
<Relationship Name="Schema">
<Entry>
<References ExternalSource="BuiltIns" Name="[dbo]" />
</Entry>
</Relationship>
</Element>
... snip ...
如果我使用sp_unbindrule
删除绑定,则 bacpac 仅包含一个绑定Element
- 它不再为 UDT 绑定创建第二个绑定,因为它显然不再存在:
工作 bacpac - 两列引用,无UDT 引用 -> 一个 SqlRule 元素
<Element Type="SqlRule" Name="[dbo].[YNINDICATOR_Domain]">
<Property Name="ExpressionScript">
<Value><![CDATA[@Ind COLLATE Latin1_General_BIN IN ('Y', 'N')]]></Value>
</Property>
<Relationship Name="BoundTargets">
<Entry>
<References Name="[dbo].[CustomLanguages].[Active_Indicator]" />
</Entry>
<Entry>
<References Name="[dbo].[ClusterNodes].[Primary_Indicator]" />
</Entry>
</Relationship>
<Relationship Name="Schema">
<Entry>
<References ExternalSource="BuiltIns" Name="[dbo]" />
</Entry>
</Relationship>
</Element>
虽然我无法真正使用这两个选项中的任何一个,因为它与 ProGet 应用程序的实际数据库模式不匹配,但有趣的是要注意列和 UDT 绑定如何根据是否有一个或多个进行序列化使用 UDT 的列。
如果我手动修改model.xml
原始 bacpac 文件中的 并合并两个 SqlRule Element
,这样只有一个包含列和规则绑定,我可以很好地导入它,并且它会创建所有架构对象而不会出现错误:
但这并不是很有用,因为我不想每次迁移数据库时都必须继续执行此操作(我不会执行多次,但足以让这是一个痛苦的解决方法)。
工作 bacpac - 两个列引用和一个 UDT 引用被黑入一个SqlRule 元素
<Element Type="SqlRule" Name="[dbo].[YNINDICATOR_Domain]">
<Property Name="ExpressionScript">
<Value><![CDATA[@Ind COLLATE Latin1_General_BIN IN ('Y', 'N')]]></Value>
</Property>
<Relationship Name="BoundTargets">
<Entry>
<References Name="[dbo].[CustomLanguages].[Active_Indicator]" />
</Entry>
<Entry>
<References Name="[dbo].[ClusterNodes].[Primary_Indicator]" />
</Entry>
<Entry>
<References Name="[dbo].[YNINDICATOR]" />
</Entry>
</Relationship>
<Relationship Name="Schema">
<Entry>
<References ExternalSource="BuiltIns" Name="[dbo]" />
</Entry>
</Relationship>
</Element>
旁注:如果我重新导出该数据库,它将恢复为 2 个 SqlRule 元素的原始形式 - 一个包含列,另一个包含规则绑定。
Az CLI 也会出现此问题:
PS> az version
{
"azure-cli": "2.56.0",
"azure-cli-core": "2.56.0",
"azure-cli-telemetry": "1.1.0",
"extensions": {
"storage-preview": "1.0.0b1"
}
}
PS> az sql db export `
--subscription "My Subscription" `
--resource-group "my-resource-group" `
--server "my-sql-server" `
--name "ProGet" `
--admin-user "my-admin-user" `
--admin-password "my-admin-password" `
--storage-uri "https://mystorageaccount.blob.core.windows.net/proget-db-backups/proget.bacpac" ``
--storage-key-type "StorageAccessKey" `
--storage-key "my storage key"
下载 blob 并解压它会显示多个 SqlRule 的问题Element
。
如果您已经读到这里,那么感谢您的阅读,回顾一下,我在顶部“tl;dr”中的问题是:
非常感谢任何帮助。
我在 Linux 上使用 MariaDB 10.24.8,遇到了这个语法错误,在过去的几个小时里一直困扰着我:
ERROR 1064 (42000) at line 7: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 6
使用此 SQL 查询来创建存储过程:
USE getranke;
DROP PROCEDURE IF EXISTS insert_scan;
-- param scan INT: Barcode Value
-- out-param result INT: 0 = registered; 1 = DB is in inventory mode
CREATE PROCEDURE insert_scan(
IN scan INT,
OUT result INT
)
BEGIN
DECLARE stateName TEXT;
SELECT Name INTO stateName
FROM StateIDs
JOIN SystemState
ON StateIDs.ID = SystemState.FK_StateIDs_ID
WHERE SystemState.ID = 1;
IF stateName = 'Inventory' THEN
SET result = 1;
ELSE
SELECT scan;
INSERT INTO Scans (FK_Drinks_barcode, when_datetime)
VALUES (scan, NOW());
SET result = 0;
END IF;
END;
让我特别恼火的是 Rust-Rover 设法毫无问题地执行这个查询,但是如果我使用 mysql/mariadb 控制台运行它,它会给我这个错误。其他前端(例如 phpmyadmin)也会出现这种情况。
如果您需要更多信息,我将非常乐意提供。谢谢。
IndexOptimize
我有一个简单的问题:运行统计更新SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
以避免如下阻塞是否安全?
USE [DBA]
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; -- turn it on
EXECUTE [dbo].[IndexOptimize]
@Databases = 'XYZ',
@FragmentationLow = NULL,
@FragmentationMedium = NULL,
@FragmentationHigh = NULL,
@UpdateStatistics = 'ALL',
@Indexes = 'XYZ.dbo.XYZ',
@StatisticsSample = 30,
@MaxDOP = 8,
@LogToTable = 'Y';
GO
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- turn it off
Apache Cassandra 稳定版和通用版之间有什么区别。这是否意味着通用版本可能比稳定版本有更多错误?或者是别的东西。请澄清这种隔离的标准是什么
今天是个好日子。最近,我们重新组织索引的维护计划一直抛出错误,这是因为某些索引上的页级锁因未知原因被禁用。该作业在 2 个索引失败后停止,因此我必须继续运行该作业来找出哪些查询禁用了页锁。似乎只有两个数据库禁用了索引页锁。
是否可以通过查询对数据库的所有索引启用页级锁?有很多表,手动操作很痛苦。
我有两张这样的桌子;
发票
id | total_amount | currency | discount
付款
id | invoice | amount
正如您所猜测的,付款表保存对特定发票的付款记录。
我需要查询以获取已部分支付的发票;
SELECT
invoices.*,
SUM(payments.amount) as paid
FROM
invoices
LEFT JOIN
payments ON payments.invoice = invoices.id
GROUP BY
invoices.id
我能够通过此查询获得付款金额,但我也需要做类似的事情
WHERE paid < total_amount AND paid > 0
但这表示“未知列在‘where 子句’中‘付费’”。我怎样才能得到这份工作?
对于 MySQL 8 和 SQLite 3.39,所有这 3 个请求都返回 NULL:
SELECT 1 - NULL;
SELECT NULL - 1;
SELECT NULL - NULL;
但对于 PostgreSQL 15,最后一个失败并显示:
error: operator is not unique: unknown - unknown
在最后一种情况下,让 PostgreSQL 返回 NULL 的最短代码是什么?
(请注意,与SELECT a.x - a.y FROM a;
、
x
和y
可能都为 NULL。PostgreSQL 不会抱怨,因为它可以根据列定义确定类型。)
一个简单的解决方案就是:
SELECT CASE
WHEN x IS NULL THEN NULL
WHEN y IS NULL THEN NULL
ELSE x - y
END;