我认为最有效的方法是创建一个MEMORY表,我使用INSERT INTO将行的ID拉入,然后有一个cron函数定期运行以聚合视图ID计数并清除内存表,更新具有最新视图的原始计数.这样可以避免不断更新最可能被访问的表,因此我不会在每次查询时一次锁定150行(如果我使用的是MyISAM,则不是整个表).
但是,我当然希望在我提取记录信息以供查看的同时执行此操作,并且我希望避免运行第二个单独的查询,以便为其计数获取相同的数据集.
有没有办法选择数据集,返回该数据集,同时将该数据集中的单个列插入另一个表？
此外,您可以根据需要在Audit表中轻松存储更多信息,例如,用户ID来计算每个用户的统计信息.
要“自动”填充Audit表,我将创建一个存储过程.客户端代码将调用此存储过程而不是执行原始SELECT.存储过程将返回与原始SELECT完全相同的结果,但也会向Audit表透明地向客户端代码添加必要的详细信息.
所以,我们假设Audit表看起来像这样：
CREATE TABLE AuditTable
(
ID int
IDENTITY -- sql Server
SERIAL -- Postgres
AUTO_INCREMENT -- MysqL
NOT NULL,ItemID int NOT NULL,RequestDateTime datetime NOT NULL
)
你的主要SELECT看起来像这样：
SELECT ItemID,Col1,Col2,... FROM MainTable WHERE <complex criteria>
要在sql Server中的一个语句中执行INSERT和SELECT,我将使用OUTPUT子句,在Postgres – RETURNING子句中,在MysqL中 – ???我不认为它有这样的东西.所以,MysqL程序会有几个单独的语句.
首先执行SELECT并将结果插入到temporary(可能是内存)表中.然后将项目ID从临时表复制到Audit表中.然后从临时表中选择SELECT以将结果返回给客户端.
CREATE TEMPORARY TABLE TempTable
(
ItemID int NOT NULL,Col1 ...,Col2 ...,...
)
ENGINE = MEMORY
SELECT ItemID,...
FROM MainTable
WHERE <complex criteria>
;
INSERT INTO AuditTable (ItemID,RequestDateTime)
SELECT ItemID,NOW()
FROM TempTable;
SELECT ItemID,...
FROM TempTable
ORDER BY ...;
sql Server(只是为了取笑你.这个单个语句同时执行INSERT和SELECT)
MERGE INTO AuditTable
USING
(
SELECT ItemID,...
FROM MainTable
WHERE <complex criteria>
) AS Src
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT
(ItemID,RequestDateTime)
VALUES
(Src.ItemID,GETDATE())
OUTPUT
Src.ItemID,Src.Col1,Src.Col2,...
;
您可以按原样保留Audit表,也可以设置cron以定期对其进行汇总.这实际上取决于数据量.在我们的系统中,我们存储了一周的单个行,另外我们总结了每小时的统计数据并将其保留了6周,此外我们将每日摘要保存18个月.但是,重要的是,所有这些摘要都是单独的审计表,我们不会在主表中保留审计信息,因此我们不需要更新它.
Joe Celko在SQL Style Habits: Attack of the Skeuomorphs年解释得非常好：
Now go to any sql Forum text search the postings. You will find
thousands of postings with DDL that include columns namedcreatedby,
createddate,modifiedbyandmodifieddatewith that particular
Meta data on the end of the row declaration. It is the old mag tape
header label written in a new language! Deja Vu!The header records appeared only once on a tape. But these Meta data values appear over and over on every row in the table. One of the main reasons for using databases (not just sql) was to remove redundancy from the data; this just adds more redundancy. But now think about what happens to the audit trail when a row is deleted? What happens to the audit trail when a row is updated? The trail is destroyed. The audit data should be separated from the schema. Would you put the log file on the same disk drive as the database? Would an accountant let the same person approve and receive a payment?
