我在 PostgreSQL 数据库中有一个文本字段,其中存储了 XML 数据。现在我想知道以缩小版本或漂亮版本存储 XML 是否有区别。
根据http://bytesizematters.com,文本本身的大小确实有所不同:
现在我想知道这是否会影响数据库的大小和性能。也许 PostgreSQL 已经对文本字段进行了如此多的优化,以至于实际上并不重要。
出乎意料的是,我在互联网上没有找到任何有关该主题的内容。因此,如果您有一些相关链接,我将非常乐意阅读和了解有关数据库优化的更多信息。
我在 PostgreSQL 数据库中有一个文本字段,其中存储了 XML 数据。现在我想知道以缩小版本或漂亮版本存储 XML 是否有区别。
根据http://bytesizematters.com,文本本身的大小确实有所不同:
现在我想知道这是否会影响数据库的大小和性能。也许 PostgreSQL 已经对文本字段进行了如此多的优化,以至于实际上并不重要。
出乎意料的是,我在互联网上没有找到任何有关该主题的内容。因此,如果您有一些相关链接,我将非常乐意阅读和了解有关数据库优化的更多信息。
这似乎是一个常见问题的变体,即对查询中 LIMIT 子句的微小更改会将查询计划更改为性能极差的计划。在本例中,我在 PostgreSQL 13 数据库中有两个表:
assets
有 ~140 行;asset_data
大约有 1.41 亿行。我的查询如下:
SELECT
a.dp as dp,
a.at as at,
a.dt as dt,
a.it as it,
a.ai as ai,
ad.idt as idt,
ad.data as data
FROM assets a
JOIN asset_data ad ON a.id = ad.asset_id_fk
WHERE
a.dp = 'kr'
AND a.at = 'fr'
AND a.dt = 'oh'
AND a.it = '1m'
AND a.ai = 'st'
ORDER BY idt desc
LIMIT 8000
上有一个索引idt
。
当限制时,8000
它只是使用idt
索引进行排序;当限制时,9000
它在连接后执行排序。
净性能从 3 秒缩短到近 12 分钟。
在阅读了其中一些类型的问题后,我尝试了一个VACUUM ANALYZE
,这改变了查询计划,但没有任何重要的方式。
更新:
我还尝试设置idt
和asset_id_fk
列的统计信息,但这不起作用1000
,而且对我来说它应该起作用并不明显。
idt 本身并不独特
idt + asset_id_fk 是唯一的并且有相应的约束
资产表仅返回一行
asset_id_fk 和 idt 的组合上有一个索引
idt上有单独的索引
关于解决此问题的适当方法有什么建议吗?
创建表语句:
CREATE TABLE IF NOT EXISTS public.asset_data
(
id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 2147483647 CACHE 1 ),
dp character varying(40) COLLATE pg_catalog."default",
at character varying(40) COLLATE pg_catalog."default",
it character varying(10) COLLATE pg_catalog."default",
ai character varying(40) COLLATE pg_catalog."default",
idt timestamp without time zone NOT NULL,
data jsonb NOT NULL,
inserted timestamp without time zone DEFAULT now(),
updated timestamp without time zone DEFAULT now(),
dt character varying(20) COLLATE pg_catalog."default",
asset_id_fk integer NOT NULL DEFAULT 1,
CONSTRAINT asset_data_pkey PRIMARY KEY (id),
CONSTRAINT asset_data_1_idx UNIQUE (dp, at, it, idt, dt, ai),
CONSTRAINT idx_asset_data_2_unique UNIQUE (asset_id_fk, idt)
)
CREATE INDEX IF NOT EXISTS asset_data_it_aif_idx
ON public.asset_data (idt, asset_id_fk);
CREATE INDEX IF NOT EXISTS idx_asset_data_asset_fk
ON public.asset_data (asset_id_fk);
CREATE INDEX IF NOT EXISTS idx_asset_data_asset_fk_idt_index
ON public.asset_data (asset_id_fk, idt);
CREATE INDEX IF NOT EXISTS idx_asset_data_idt_idx
ON public.asset_data (idt);
解释分析,限制为 8000:
Limit (cost=0.57..4170458.87 rows=8000 width=173) (actual time=0.160..3677.025 rows=8000 loops=1)
Buffers: shared hit=61448 read=14657 dirtied=756
-> Nested Loop (cost=0.57..507975375.06 rows=974426 width=173) (actual time=0.159..3675.290 rows=8000 loops=1)
Join Filter: (a.id = ad.asset_id_fk)
Rows Removed by Join Filter: 474053
Buffers: shared hit=61448 read=14657 dirtied=756
-> Index Scan Backward using idx_asset_data_idt_idx on asset_data ad (cost=0.57..505855992.43 rows=141291824 width=146) (actual time=0.051..3437.070 rows=482053 loops=1)
Buffers: shared hit=61446 read=14657 dirtied=756
-> Materialize (cost=0.00..5.27 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=482053)
Buffers: shared hit=2
-> Seq Scan on assets a (cost=0.00..5.26 rows=1 width=35) (actual time=0.052..0.067 rows=1 loops=1)
Filter: (((dp)::text = 'kr'::text) AND ((at)::text = 'fr'::text) AND ((dt)::text = 'oh'::text) AND ((it)::text = '1m'::text) AND ((ai)::text = 'st'::text))
Rows Removed by Filter: 144
Buffers: shared hit=2
Settings: effective_cache_size = '1507160kB'
Planning:
Buffers: shared hit=4
Planning Time: 0.445 ms
Execution Time: 3679.005 ms
解释分析,限制为 9000:
Limit (cost=4269756.79..4269779.29 rows=9000 width=173) (actual time=700091.606..700094.588 rows=9000 loops=1)
Buffers: shared hit=133 read=1538340, temp read=74205 written=112013
-> Sort (cost=4269756.79..4272192.85 rows=974426 width=173) (actual time=700091.604..700093.738 rows=9000 loops=1)
Sort Key: ad.idt DESC
Sort Method: external merge Disk: 304680kB
Buffers: shared hit=133 read=1538340, temp read=74205 written=112013
-> Nested Loop (cost=0.57..4200885.77 rows=974426 width=173) (actual time=1.190..693283.441 rows=1687735 loops=1)
Buffers: shared hit=133 read=1538340
-> Seq Scan on assets a (cost=0.00..5.26 rows=1 width=35) (actual time=0.032..0.050 rows=1 loops=1)
Filter: (((dp)::text = 'kr'::text) AND ((at)::text = 'fr'::text) AND ((dt)::text = 'oh'::text) AND ((it)::text = '1m'::text) AND ((ai)::text = 'st'::text))
Rows Removed by Filter: 144
Buffers: shared hit=2
-> Index Scan using idx_asset_data_asset_fk on asset_data ad (cost=0.57..4190011.91 rows=1086860 width=146) (actual time=1.151..691172.001 rows=1687735 loops=1)
Index Cond: (asset_id_fk = a.id)
Buffers: shared hit=131 read=1538340
Settings: effective_cache_size = '1507160kB'
Planning:
Buffers: shared hit=4
Planning Time: 0.317 ms
Execution Time: 700245.659 ms
更新2:
切换到以下查询后:
SELECT a.dp, a.at, a.dt, a.it, a.ai, ad.idt, ad.data
FROM (
SELECT a.id, a.dp, a.at, a.dt, a.it, a.ai
FROM assets a
WHERE a.dp = 'kr'
AND a.at = 'fr'
AND a.dt = 'oh'
AND a.it = '1m'
AND a.ai = 'st'
LIMIT 1 -- make sure the planner understands
) a
JOIN asset_data ad ON ad.asset_id_fk = a.id
ORDER BY ad.idt DESC
LIMIT 8000;
唯一的区别是查询计划在 9000 和 10000 之间切换:
Limit (cost=0.57..4206410.37 rows=9000 width=173) (actual time=0.139..432.265 rows=9000 loops=1)
Buffers: shared hit=82159
-> Nested Loop (cost=0.57..507975395.07 rows=1086860 width=173) (actual time=0.138..431.358 rows=9000 loops=1)
Join Filter: (a.id = ad.asset_id_fk)
Rows Removed by Join Filter: 533354
Buffers: shared hit=82159
-> Index Scan Backward using idx_asset_data_idt_idx on asset_data ad (cost=0.57..505856012.43 rows=141291824 width=146) (actual time=0.049..164.873 rows=542354 loops=1)
Buffers: shared hit=82158
-> Materialize (cost=0.00..5.28 rows=1 width=35) (actual time=0.000..0.000 rows=1 loops=542354)
Buffers: shared hit=1
-> Subquery Scan on a (cost=0.00..5.27 rows=1 width=35) (actual time=0.032..0.035 rows=1 loops=1)
Buffers: shared hit=1
-> Limit (cost=0.00..5.26 rows=1 width=35) (actual time=0.032..0.033 rows=1 loops=1)
Buffers: shared hit=1
-> Seq Scan on assets a_1 (cost=0.00..5.26 rows=1 width=35) (actual time=0.031..0.032 rows=1 loops=1)
Filter: (((dp)::text = 'kr'::text) AND ((at)::text = 'fr'::text) AND ((dt)::text = 'oh'::text) AND ((it)::text = '1m'::text) AND ((ai)::text = 'st'::text))
Rows Removed by Filter: 96
Buffers: shared hit=1
Settings: effective_cache_size = '1507160kB'
Planning Time: 0.322 ms
Execution Time: 432.904 ms
Limit (cost=4278529.50..4278554.50 rows=10000 width=173) (actual time=702389.569..702392.909 rows=10000 loops=1)
Buffers: shared hit=350 read=1538221, temp read=74241 written=112019
-> Sort (cost=4278529.50..4281246.65 rows=1086860 width=173) (actual time=702389.568..702392.118 rows=10000 loops=1)
Sort Key: ad.idt DESC
Sort Method: external merge Disk: 304704kB
Buffers: shared hit=350 read=1538221, temp read=74241 written=112019
-> Nested Loop (cost=0.57..4200885.78 rows=1086860 width=173) (actual time=1.267..695545.975 rows=1687836 loops=1)
Buffers: shared hit=350 read=1538221
-> Limit (cost=0.00..5.26 rows=1 width=35) (actual time=0.071..0.074 rows=1 loops=1)
Buffers: shared hit=1
-> Seq Scan on assets a (cost=0.00..5.26 rows=1 width=35) (actual time=0.071..0.071 rows=1 loops=1)
Filter: (((dp)::text = 'kr'::text) AND ((at)::text = 'fr'::text) AND ((dt)::text = 'oh'::text) AND ((it)::text = '1m'::text) AND ((ai)::text = 'st'::text))
Rows Removed by Filter: 96
Buffers: shared hit=1
-> Index Scan using idx_asset_data_asset_fk on asset_data ad (cost=0.57..4190011.91 rows=1086860 width=146) (actual time=1.190..693414.817 rows=1687836 loops=1)
Index Cond: (asset_id_fk = a.id)
Buffers: shared hit=349 read=1538221
Settings: effective_cache_size = '1507160kB'
Planning Time: 0.301 ms
Execution Time: 702526.728 ms
让我们想象一下以下情况:用户可以下订单。订单由项目组成。货物被发送给用户。发货由物品组成。
为了优化供应链,允许将来自不同订单的物品包装在一个共同的装运中,前提是这些订单都与同一用户相关联
我很难理解对情况进行建模的最佳方法是什么,以优雅地适应上一段中斜体所示的约束,而无需复制数据或到处进行检查。
通过零重复数据,我可以轻松得出以下模式:
CREATE TABLE users(
id serial primary key
)
CREATE TABLE orders(
id serial primary key,
user_id int not null references users
)
CREATE TABLE shipments(
id serial primary key
)
CREATE TABLE items(
id serial primary key,
order_id int not null references orders,
shipment_id int references shipments
)
虽然它确实实现了订单和商品以及发货和商品之间的 1-N 关系,但这并不妨碍执行以下操作:
INSERT INTO orders(id,user_id) VALUES (1,1);
INSERT INTO orders(id,user_id) VALUES (2,2);
INSERT INTO shipments(id) VALUES (1)
INSERT INTO items(order_id,shipment_id) VALUES (1,1);
INSERT INTO items(order_id,shipment_id) VALUES(2,1);
这会导致货件 #1 具有与不同用户相关联的两个项目(通过他们各自的订单)的情况。
我找到了解决我的问题的两种解决方案,两种方法都有效,但对我来说似乎都不行:
ALTER TABLE items ADD user_id int not null references users;
ALTER TABLE shipments ADD user_id int not null references users;
ALTER TABLE orders ADD UNIQUE (id,user_id); -- For some reason postgres wants this ?
ALTER TABLE shipments ADD UNIQUE (id, user_id);
ALTER TABLE items ADD FOREIGN KEY (order_id,user_id) references orders(id,user_id);
ALTER TABLE items ADD FOREIGN KEY (shipment_id,user_id) references shipments(id,user_id);
解决方案 1 保持了干净的架构,但引入了程序复杂性。我还听说标量用户定义函数在性能方面不是很好。
解决方案 2 对我来说绝对感觉更强大,但我不喜欢我重复(如果我诚实的话,是三倍) user_id 数据和添加的要求。
这看起来是一个简单的情况,我认为更有经验的 RDBMS 用户会发现这微不足道......但我很遗憾地报告,我在关系数据库方面非常缺乏经验,我怀疑我无法找到我的答案问题本质上来自于我无法用正确的术语表达它......我很尴尬地问这样一个愚蠢的问题,这可能是重复的,但我的谷歌这次失败了!
我正在使用 SQL Server,我有两个表,table1
并且table2
. 两个表都有一个DATETIME
表示为 的列dt
。我不仅需要根据某些条件连接这些表,还要确保匹配的行具有列中相同的日期(此处不考虑时间)dt
。
这是我当前使用的查询:
select *
From table1 a inner join table2 b
on a.id = b.a_id
and convert(date, a.dt) = convert(date, b.dt)
该查询可以获取我需要的结果,但我担心它的性能,尤其是当表大小增加时。
我想知道是否有更高效的方法可以达到相同的结果?是否有技术或 SQL Server 功能可以帮助我优化此查询,尤其是日期比较部分?
任何意见或建议将不胜感激。先感谢您。
下面的代码为我提供了嵌套在另一个表中的表所需的基本输出,请参阅屏幕截图
但是,如果不添加代码行,我就无法获得正确的输出
Set @BodyTxt =(select REPLACE(replace (@BodyTxt,'<','<'),'>','>'))
因为原始代码被转义为“<”和“>”。
有没有更干净或更好的方法来做到这一点?
请注意,使用 dB 邮件只是查看 HTML 输出的快速方法(有更好的方法吗?)。如果您使用我的代码,您需要配置 dB 邮件并更改电子邮件配置文件和地址
/***********************************************/
/*** set up test data */
/***********************************************/
DROP TABLE IF EXISTS dbo.Units
DROP TABLE IF EXISTS dbo.Document
CREATE TABLE Units(
DocNum int,
DocRow int,
Serial varchar(255),
Element varchar(255),
ManDate datetime)
INSERT dbo.Units
Values (50009,0,'Serial1','Element1','2021-01-04 00:00:00'),
(50009,0,'Serial2','Element2','2021-01-04 00:00:00'),
(50009,1,'Serial4','Element2','2021-01-06 00:00:00'),
(50006,0,'Serial3','Element2','2021-01-07 00:00:00')
CREATE TABLE Document(
DocNum int,
DocRow int,
Part varchar(255),
Qty int,
DocDate datetime)
INSERT dbo.Document
Values (50009,0,'PartA',2,'2023-07-04 00:00:00'),
(50008,0,'PartC',1,'2023-06-28 00:00:00'),
(50007,0,'PartB',4,'2023-06-24 00:00:00'),
(50006,0,'PartA',1,'2023-01-04 00:00:00')
/******* End of Test Data set up *************/
declare @BodyTxt nvarchar(max)
Set @BodyTxt =(
SELECT
(SELECT 'Table I' FOR XML PATH(''),TYPE) AS 'caption',
(SELECT 'Doc' AS th, 'Line' AS th,'Part' as th,'Qty' as th,'Unit Details' as th FOR XML raw('tr'),ELEMENTS, TYPE) AS 'thead',
--(SELECT 'sum' AS th, 'twenty' AS th FOR XML raw('tr'),ELEMENTS, TYPE) AS 'tfoot',
(SELECT DocNum AS td, DocRow AS td,Part AS td,Qty AS td, (Select (Select Serial as td,Element as td,convert (varchar ,ManDate, 6) as td from dbo.Units U Where U.DocNum=D.DocNum and U.DocRow=D.DocRow FOR XML RAW('tr'), ELEMENTS, TYPE ) AS 'tbody'
FOR XML PATH(''), ROOT('table')) AS td FROM dbo.Document D FOR XML RAW('tr'), ELEMENTS, TYPE
) AS 'tbody'
FOR XML PATH(''), ROOT('table'))
Set @BodyTxt =(select REPLACE(replace (@BodyTxt,'<','<'),'>','>'))
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Send via 365'
,@recipients = '[email protected]'
,@subject = 'Test table'
,@body = @BodyTxt
,@body_format = 'HTML';
应用程序积极地将数据缓存在内存中,并且为了支持一致性(防止持久保存陈旧数据),它正在执行以下操作:
-- typical table structure:
create table t1 (
id varchar(16) primary key,
version_stamp int4,
....
)
-- typical update statement
update t1 set
version_stamp = version_stamp + 1,
col1 = ?,
col2 = ?,
...
where id = ? and version_stamp = ?
如果上面提到的更新表明没有更新任何行,则意味着应用程序已尝试保留陈旧数据并引发异常,主要思想是防止或至少最小化此类情况。为此,应用程序正在执行以下查询(每个请求、事务或方法调用):
select version_stamp from t1
where id = ?
如果没有返回行,则意味着该行已被删除,如果返回的行version_stamp
与version_stamp
内存中保存的行不同,则意味着我们正在处理陈旧的数据。
问题是:是否值得将主键定义为:
create unique index on t1(id) include(version_stamp)
或者在这种情况下则不然。此类查询的典型 RPS 约为每秒 10k。
json_merge_preserve接受两个或多个 JSON 文档并将它们合并为一个。然而,函数定义似乎只接受固定数量的参数。
所以如果我有一个像这样的表:
create table jsons (data longtext);
其中有任意行数,我该如何使用json_merge_preserve
?
尝试计算 FIFO 报告的值。有没有一种方法使用 SQL 对数据进行分组,以便将特定值的总和与每个组的不同值进行比较,包括在必要时仅取行的一部分?
请求:对于每个商品编号,按日期对已购买商品进行降序排序,并获取最大金额,使数量总和 = 该商品编号的已售出金额,如有必要,仅取最后一行的一部分获得匹配的数量。然后输出未售出剩余库存的结果,其中包含 ItemNumber、NumShipments 和 CostSum(CostPer 与 Quantity 的乘积之和)。
例如,考虑以下架构:
CREATE TABLE MYLIB.TOTAL_ITEM_SOLD (ITEM_NUMBER VARCHAR(15) NOT NULL NOT HIDDEN , QUANTITY_SOLD DECIMAL(5, 2) NOT HIDDEN , PRIMARY KEY (ITEM_NUMBER) ) NOT VOLATILE UNIT ANY KEEP IN MEMORY NO ;
CREATE TABLE MYLIB.ITEM_PURCHASE (ITEM_NUMBER VARCHAR(15) NOT NULL NOT HIDDEN , DATE_ORDERED TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP NOT NULL NOT HIDDEN , QUANTITY DECIMAL(4, 2) NOT NULL NOT HIDDEN , COST_PER DECIMAL(10, 2) NOT NULL NOT HIDDEN , PRIMARY KEY (DATE_ORDERED, ITEM_NUMBER) ) NOT VOLATILE UNIT ANY KEEP IN MEMORY NO ;
INSERT INTO MYLIB.TOTAL_ITEM_SOLD (ITEM_NUMBER, QUANTITY_SOLD) VALUES ('APPLE', 5);
INSERT INTO MYLIB.ITEM_PURCHASE (ITEM_NUMBER, QUANTITY, COST_PER) VALUES ('APPLE', 2, 1.23);
INSERT INTO MYLIB.ITEM_PURCHASE (ITEM_NUMBER, QUANTITY, COST_PER) VALUES ('APPLE', 4, 2.34);
INSERT INTO MYLIB.ITEM_PURCHASE (ITEM_NUMBER, QUANTITY, COST_PER) VALUES ('APPLE', 2, 5.55);
预期结果:
{ITEM_NUMBER: "APPLE", QTY_REMAINING: 3, COST_SUM: 13.44}
因为 5.55+5.55+2.34 = 13.44。
我必须将多个表从一个远程数据库移动到 PostgreSQL 中的另一个数据库。
据我了解,唯一的方法是通过pg_dump
.
pg_dump -h <source_db_host> -U <source_db_user> -d <source_db_name> -f database_dump.sql
psql -h <target_db_host> -U <target_db_user> -d <target_db_name> -f database_dump.sql
但是,它失败了,因为目标数据库中的架构与源数据库中的架构不同,因此我收到一条错误消息,表明源数据库中不存在特定架构。
有没有办法更改目标架构?
有没有其他更简单的方法来传输数据集?
我想在 Postgres 数据库上的原子事务中运行大量语句。BEGIN;
放在脚本的开头和COMMIT;
结尾是否足够?我想确保如果在事务期间发生错误,它会自动回滚。
例如,我有脚本 test.sql:
BEGIN;
SELECT
*
FROM
intentional_error;
CREATE TABLE faketable (fakecolumn TIMESTAMP);
COMMIT;
intentional_error
不是我的架构中的真实表,因此我预计事务中的第一个语句会出错。当它出错时,我想知道是否faketable
会被创建。
根据我的研究,我找不到明确说明 aROLLBACK;
在这种情况下是自动的 Postgres 文档。有一个Stack Exchange 答案指出这ROLLBACK;
是自动的,但我很想看到 Postgres 文档中的测试或其他内容。