如何加快對具有大量列和過濾條件的表的計數和過濾？

我的數據庫中有很多列的 TABLE_A。

假設有 100 列：COLUMN_1、COLUMN_2、COLUMN_3、.. COLUMN_100

有這麼多列不是因為非規範化設計，而是因為每一行代表具有很多屬性的實體（在此範例中 - 具有 99 個屬性，第一列只是一個 id）

我的應用程序應該執行以下任務：

• 接收使用者的一組過濾條件
• 在數據庫中執行查詢，根據給定條件過濾數據，然後計算結果行數

過濾條件的數量沒有限制：

• 可能沒有過濾條件
• 表格的每一列都可能有過濾條件
• 表的某些列可能存在過濾條件（例如，總共有 50 列）

下面是我的應用程序執行的範例查詢：

SELECT 
   COUNT(*)
FROM 
   TABLE_A 
WHERE 
   COLUMN_1 = 'test'
   AND COLUMN_2 != 'q'
   AND COLUMN_45 > 5
   AND COLUMN_45 < 511
   AND COLUMN_92 LIKE '%ddd%'
   AND COLUMN_98 > 1000

TABLE_A 沒有任何索引 - 每列都可用於過濾，並且沒有比其他查詢更頻繁地執行的查詢集。

我也不會在任何級別上使用任何類型的記憶體：插入和更新操作發生的頻率不高，但比至少 2 個具有相同過濾條件的查詢發生的頻率更高。

因此，在每個查詢的情況下，都會執行順序搜尋。以前不是問題，但現在查詢的執行時間變得不可接受（表中的行數增加了很多）。

所以..

有沒有關於如何加快任務執行的解決方案？

任何建議都將不勝感激——我可以改變任何東西，除了一件事——我應該使用 SQL。目前我使用 PostgreSQL，但如果有幫助，我會更改 DBMS。

我感覺到你的痛苦，尼克。我們在這裡有一個類似的案例，並且一直在竭盡所能地處理大量索引。

我知道你說你需要堅持使用 SQL，但我真的認為你可能想要考慮一個列儲存 ( https://en.wikipedia.org/wiki/Column-oriented_DBMS )，也許一個類似 SQL 的東西足以讓你還是覺得可行。

MariaDB 現在似乎也接受了這一點： https ://mariadb.com/resources/blog/why-columnstore-important

沒有比其他查詢更頻繁地執行的查詢集。

這消除了“索引最常接觸的列，讓其餘列仍然掃描”的快速解決方案，因此唯一的另一個快速解決方案是在每個列上建立索引。這意味著幾乎所有查詢中至少有一部分被索引，只有那些沒有過濾器或所有非可解析過濾器的查詢仍需要執行完整掃描。查詢計劃器有望在每次減少行數時選擇最具選擇性的索引選項，而不是需要檢查其他所有內容。但是，這當然會佔用大量空間，並且可能會增加您的 RAM 需求，因為索引成為您希望盡可能多地保留在活動記憶體中的常用工作集的一部分。

正如 Kevin 所建議的，提供面向列儲存的數據庫可能會對此有所幫助。MS SQL Server 在更高版本中提供此類表類型，但如果您的數據庫大於 10Gb 或者您需要使用超過 2Gb 的 RAM 以提高效率（免費“快速”版本的限制），這可能會很昂貴。

除此之外，這可能是EAV 模式不是一個完全糟糕的想法的情況，但它可能仍然是。在此模型中，您的寬表變得類似於id:int, property_name:string, int_value:int, str_value:string在兩個值列上具有索引，但請注意，除非您的許多列通常是稀疏的（通常為 NULL），否則這兩個值索引不會比覆蓋的 99 個索引小很多你的寬表的列集。您的查詢將需要大幅更改，除非您將原始表複製為視圖並希望

$$ preferred deity $$數據庫的查詢計劃器可以用它做一些聰明的事情。

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