動態數據透視表過濾和性能

我有這些表，我正在嘗試將訂閱者旋轉到列表以水平並根據多個和/或條件過濾其結果，如下所示：

WHERE  first_name LIKE 'm%' AND email LIKE '%com'

這是小提琴

這是我的兩張桌子：

欄位表

+----+------------+
| id |label       |
+----+------------+
|  1 | email      |
|  2 | first_name |
|  3 | last_name  |
+-----------------+

訂閱者欄位表

+----+--------------+----------+---------------+-------------------+
| id | mail_list_id | field_id | subscriber_id | value             |
+----+--------------+----------+---------------+-------------------+
|  1 |            1 |        1 |             1 | mark@examble.com  |
|  2 |            1 |        2 |             1 | Mark              |
|  3 |            1 |        3 |             1 | Wood              |
|  4 |            1 |        1 |             2 | luan@domain.com   |
|  3 |            1 |        2 |             2 | Luan              |
|  4 |            1 |        3 |             2 | Charles           |
|  5 |            1 |        1 |             3 | marry@domain.com  |
|  6 |            1 |        2 |             3 | Anna              |
|  7 |            1 |        3 |             3 | Marry             |
|  8 |            2 |        1 |             4 | kevin@domain.com  |
|  9 |            2 |        2 |             4 | Kevin             |
| 10 |            2 |        3 |             4 | Faustino          |
| 11 |            2 |        1 |             5 | frank@examble.com |
| 12 |            2 |        2 |             5 | Frank             |
| 13 |            2 |        3 |             5 | Denis             |
| 14 |            2 |        1 |             6 | max@example.com   |
| 15 |            2 |        2 |             6 | Max               |
| 16 |            2 |        3 |             6 | Ryan              |
+----+--------------+----------+---------------+-------------------+

這是我嘗試過的，但它導致了 email 和 first_name 返回 0 而不是 value 的問題。它也不適用於AND操作員：

select 
 subscriber_id,
 MAX(case when field_id = '1' then value else 0 end) as email,
 MAX(case when field_id = '2' then value else 0 end) as first_name,
 MAX(case when field_id = '3' then value else 0 end) as last_name
from test_fields_table
WHERE (field_id = 3 AND value LIKE 'm%') OR (field_id = 1 AND value = '%com')
group by subscriber_id limit 100;

如果我刪除WHERE條件，則查詢性能良好。

我還嘗試在子查詢中添加我的查詢，給它一個別名，然後使用別名欄位名稱而不是欄位 id 搜尋生成的虛擬表，但在這種情況下，我必須從子查詢中刪除限制參數才能能夠不僅在前 100 條記錄中搜尋全表，這會導致性能非常差，因為該表將超過 100-5 億條記錄，我需要在 4 秒內獲得查詢結果。

您可以使用HAVING過濾您創建的列：

select 
 subscriber_id,
 MAX(case when field_id = '1' then value else 0 end) as email,
 MAX(case when field_id = '2' then value else 0 end) as first_name,
 MAX(case when field_id = '3' then value else 0 end) as last_name
from test_fields_table

group by subscriber_id
HAVING email LIKE '%com' 
AND last_name LIKE 'M%'
limit 100;

查看結果

您的 field_1 架構可能比 EAV 或 JSON 慢。例如，Wordpress 使用 EAV 模式模式——WP 使用者經常在這個論壇和其他論壇上抱怨性能不佳。JSON 有利有弊。

為了提高性能，您必須在具有合適數據類型的單個表中擁有更常見的搜尋列。不太常見的搜尋列可以隱藏在 EAV 或 JSON 中並由應用程序測試。

要允許客戶添加常用搜尋的列，需要教他一些數據類型（日期、日期時間、貨幣、浮點數、整數、字元串），並製作一個ALTER以將該列添加到表中。添加索引變得更加混亂，因為它應該涉及多個列。例如，INDEX(last_name), INDEX(first_name)如果您只搜尋其中一列，這很方便。但是，如果使用者通常在兩列上進行搜尋，那麼您需要INDEX(last_name, first_name). 這很難預料。

如果您的客戶只有一千行，那麼這些對性能都沒有多大關係。但是，早在一百萬行之前，表上的所有方法都會受到性能的影響。

告訴我更多關於應用程序空間的資訊。（文件/一般產品/特定產品（例如相機）/天氣感測器/地理位置/ …）也許我可以提供一些更具體的提示。

通過經緯度“查找最近的咖啡店”特別棘手；它需要自己的討論。它的性能優化不適用於其他應用程序，反之亦然。

對您的 SQL 的評論：

WHERE (field_id = 3 AND value LIKE 'm%')
  OR (field_id = 1 AND value = '%com')
group by subscriber_id
limit 100;

筆記：

• OR尤其難以優化；它可能會導致全表掃描，檢查每一行。
• value LIKE %com``INDEX(value)由於前導萬用字元，無法使用。(REVERSE()可能是解決方法的一部分。
• 因為，GROUP BY在到達之前會掃描整個表LIMIT。也就是說，無論LIMIT.
• LIMIT沒有ORDER BY也沒有說你會得到哪些行。
• “field_N”技術無法輕鬆測試數字數據。數字 1,2,3,15,26,108 將排序為 1,108,15,2,26,3。（+0是一種解決方法，但它無法使用任何索引。WP 有這個問題。）

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