使用觸發器代替 count(*) 來計算相關實體的缺點是什麼？

1. 使用以下方法有什麼缺點？
2. 可以採取哪些措施來防止有人錯誤地更新計數？
3. 以下方法是反模式嗎？如果是，有什麼更好的方法存在？

---

假設：

應用程序是一個公共 wiki/論壇（如 stackexchange）

1. 該應用程序讀取量很大。選擇將是最終使用者提出的 99% 的查詢
2. 最終使用者不執行批量插入。它們可能僅用於開發/維護工作
3. 需要同時寫入表
4. 計數估計已經足夠好了。確切的數量將是最重要的

---

方法

考慮兩個實體，Students每個實體Courses都有對應的表。我們還有一個student_courses儲存多對多映射 b/w 學生和課程的表。

create table students (
 id bigserial primary key,
 name text
);

create table courses (
 id bigserial primary key,
 content text
);

create table student_courses (
 student_id bigint not null references students,
 course_id bigint not null references courses,
 primary key (student_id, course_id)
);
create index on student_courses (course_id);

要找到給定學生的課程數量，我們可以執行

select count(1)
from student_courses
where student_id = 123;

如果這些計數查詢很頻繁（假設您希望始終顯示學生姓名和課程計數），一種優化方法是在兩個實體中維護一個計數變數，然後放置插入和刪除觸發器。

alter table students add column course_count bigint default 0;
alter table courses add column student_count bigint default 0;

create function increment_student_course_count() returns trigger as
$$begin
 update students
   set course_count := course_count + 1
   where student_id = new.student_id;
 update courses
   set student_count := student_count + 1
   where course_id = new.course_id;
 return new;
end;$$

create trigger after_insert_update_counts
after insert
on student_courses
for each row execute procedure increment_student_course_count();


create function decrement_student_course_count() returns trigger as
$$begin
 update students
   set course_count := course_count - 1
   where student_id = new.student_id;
 update courses
   set student_count := student_count - 1
   where course_id = new.course_id;
 return new;
end;$$

create trigger after_delete_update_counts
after delete
on student_courses
for each row execute procedure decrement_student_course_count();

一旦到位，查找學生的課程數量很簡單

select course_count
from students where student_id = 123;

需要同時寫入表

除了Laurenz 已經提出的內容之外，如果事務包含對多行的操作，這會增加死鎖的可能性。由於您的觸發器鎖定了表中的一行和students表中的另一行，因此courses您最好確保所有可能交織在一起的寫訪問嚴格遵循某種通用排序順序。對於表格來說似乎特別成問題courses，因為許多學生可能在不同的交易中大約在同一時間註冊。如果這些事務不僅僅是在 中寫入單行student_courses，則可能會出現問題。

我沒有看到由於並發導致數據錯誤的危險，因為每個事務必須等待競爭事務完成才能更新有爭議的行 - 這突出了寫入操作的潛在成本損失。通過寫活動保持您的事務緊密。

另一個問題是重複寫入會使您的主表students和courses. 每個UPDATE寫入一個新的行版本。這些行越寬，影響就越大。尤其是面對並發寫入訪問時，可見性可能會受到影響（請參閱可見性圖）。autovacuum可能很難跟上。否則可能會禁用可能的僅索引掃描。表格可能開始膨脹。旨在加快讀取速度的全部努力最終可能會減慢讀取速度！

根據寫入頻率，考慮計數的“垂直分區”。如果許多查詢只獲取開始的計數並且不需要（寬？）主行的其餘部分，這也可能是相關的。基本上MATERIALIZED VIEW只關注計數，但使用觸發器手動管理而不是完全成熟的MATERIALIZED VIEW. 關鍵是寫入一個帶有小行的單獨表，以應對鎖爭用、可見性問題和膨脹。當然，還有更多額外的成本。看：

• 許多列與少數表 - 性能方面

使用 PK 和額外的索引(course_id)for student_courses，您已經為這兩個計數的索引或僅索引掃描設置了完美的設置。此查詢將非常快：

SELECT count(*) FROM student_courses WHERE student_id = 123;

毫秒的分數。結合應用程序級別的記憶體，這可能就是您所需要的。上述所有內容只有在寫入與讀取相比極為罕見的情況下才會發揮作用（所提到的99%可能還不值得付出努力），並且快速獲得計數至關重要。

在 Postgres**count(*)**中，與. count(<value>)前者甚至沒有開始盡可能快地查看表達式或行值。（count(1)只是稍微貴一點，因為常量也不涉及行值，但仍然。）

不要被在上下文中施加最大成本的*符號所迷惑，獲取所有 列。在這種情況下的效果實際上是相反的。SELECT *

引用自：https://dba.stackexchange.com/questions/303023