概述

我在系統中有以下結構，用戶可以在其中將屬性保存到作業，他可以自己命名。一個屬性可以有 2 個可能的值，一個用戶值和一個系統值。我正在嘗試根據屬性過濾作業，其中用戶值或系統值都可以命中。

現在的情況

表作業

表作業資料

索引：

用戶現在必須過濾多個屬性，例如：“向我顯示柏林城市和電話 123 的所有作業”。這將顯示作業 1 和 3，因為它們在其中一個城市值中都有柏林，并且在其中一個電話值中具有像 123 這樣的電話號碼。

我有幾個可行的解決方案，但現在我們在資料庫中有 120,000 個活動作業，具有超過一百萬個屬性，而且我們的代碼不夠快。這是我們目前的解決方案：

SELECT * FROM jobs
WHERE
(
    SELECT count(*) FROM job_data
    WHERE job_data.job_id = jobs.id
    AND job_data.field = "city" AND (job_data.value_xml LIKE "%Martinhaven%" OR job_data.value_user LIKE "%Martinhaven%")
) > 0
AND
(
    SELECT count(*) FROM job_data
    WHERE job_data.jobposting_id = jobs.id
    AND job_data.field = "category" AND (job_data.value_xml LIKE "%omnis%" OR job_data.value_user LIKE "%omnis%")
) > 0;

輸出：

這已經被簡化了，我們有大約 6 個過濾器的可能性，但它們都是同一種，所以我只發布了其中的 2 個。

問題

Can anyone tell me, how I can do this a lot faster? We currently need 5-10 seconds for one select. Ofc we could restructure the database to make it faster to filter, but we are still trying to prevent that, since it is a bigger system in construction.

Thanks a lot in advance.

uj5u.com熱心網友回復：

不要使用為每個條件和表的每一行聚合的 1 個相關子查詢，而是使用jobs一次條件聚合：

SELECT job_id 
FROM job_data
GROUP BY job_id
HAVING SUM(field = 'city' AND (value_xml LIKE '%Martinhaven%' OR value_user LIKE '%Martinhaven%')) > 0
   AND SUM(field = 'category' AND (value_xml LIKE '%omnis%' OR value_user LIKE '%omnis%')) > 0
   AND .....

獲取job_id您想要的所有s 并將它們與運算子 IN 一起使用：

SELECT * FROM jobs
WHERE job_id IN (
  SELECT job_id 
  FROM job_data
  GROUP BY job_id
  HAVING SUM(field = 'city' AND (value_xml LIKE '%Martinhaven%' OR value_user 
  LIKE '%Martinhaven%')) > 0
     AND SUM(field = 'category' AND (value_xml LIKE '%omnis%' OR value_user 
  LIKE '%omnis%')) > 0
     AND .....
);

如果job_id子查詢回傳的s個數比較少我相信這個查詢的性能會更好。

所有的> 0不等式都可以從代碼中洗掉，但為了清楚起見，我把它們留在那里。

uj5u.com熱心網友回復：

你有兩個問題。

• 多個值欄位。
• like '%word%' 很難索引。

tl;博士示范

修復架構

更改job_data為單個值和一個源。

job_id  field  value   source
1       city   Berlin  user
3       city   Berlin  xml

-- Add value and source columns, nullable for now.
alter table job_data add value varchar(255), add source varchar(255);

-- If value_user is not null or blank, add it to value with a source of user.
update job_data
set value = value_user, source = 'user'
where coalesce(value_user, '') <> '';

-- Same for value_xml, source of xml.
update job_data
set value = value_xml, source = 'xml'
where value_xml is not null;

-- Drop the old value columns.
alter table job_data
  drop value_xml, drop value_user;

現在我們可以對 value 和 source 強制執行 not null 以避免錯誤資料。

alter table job_data
  modify value varchar(255) not null,
  modify source varchar(255) not null;

我們還可以強制一個作業的每個欄位只能有一個值。或按欄位和來源。

-- If a job can have multiple sources for a field.
alter table add unique(job_id, field, source);

-- If it cannot.
alter table add unique(job_id, field);

如果您需要保持兼容性，請查看。

create view old_job_data as
  select
    job_id,
    field,
    case source when 'user' then value end as value_user,
    case source when 'xml' then value end as value_xml
  from job_data;

這解決了許多問題。

• 您可以擁有任意數量的來源。
• 您可以確保該值不為空。
• 您可以避免重復。
• 您不需要在兩個地方檢查值。
• 索引更容易。

查詢

有了這個問題，查詢就簡單多了。

要查找與欄位/值對匹配的作業，通常您會執行intersect. 但是 MySQL 沒有intersect，所以我們用自連接來模擬它。

select * from jobs
where job_id in (
  select distinct j1.job_id
  from job_data j1
  inner join job_data j2 on j1.job_id = j2.job_id
  where
    (j1.field = 'city' and j1.value like '%York%')
    and
    (j2.field = 'type' and j2.value like '%time%')

)

你不具備改變表，你可以做j1.field = 'city' and (j1.value_xml like '%York%' or j1.value_user like '%York%')，但它使得它容易得多。

索引

在 MySQL 中，like '%word%'不會使用簡單的索引。它必須使用匹配欄位掃描每一行。簡單索引僅適用于like 'word%'. 其他資料庫對此有特殊的索引。我不知道 MySQL 的解決方案。

至少，索引field以便您至少可以field快速找到匹配的行。

create index job_data_field_idx on job_data(field);

現在不是掃描每一行，而是只掃描與該欄位匹配的行。

確定您是否真的需要完整的通配符搜索。或者您是否需要在插入資料之前清理資料？如果您可以擺脫通配符索引，該索引將使搜索幾乎是即時的。

create index job_data_field_vaue_idx on job_data(value, field);

uj5u.com熱心網友回復：

當使用像 '%...' 這樣的不可分割的條件時，這并沒有什么幫助，但是您通過count(*)為每個搜索的條件執行一個不必要的消耗CPU 和 IO 。

這是可能的優化可以發現這一點，作為一個內部優化它存在的操作，但它始終是最好要明確

select * 
from jobs j
where exists (
    select 1 job_data d
    where d.job_id = j.id
    and d.field = "city" and (d.value_xml like "%Martinhaven%" or d.value_user like "%Martinhaven%")
) 

使用物體屬性模型，如果所有值都在同一列中，效率會更高。最初實施的更改可能太大，因此您還應該嘗試多個條件來分別檢查每一列，而不是使用or。對于每個索引（在欄位上， Value_xml和欄位上， value_user），這將是最有益的。

select * 
from jobs j
where exists (select 1 job_data d  where d.job_id = j.id and d.field = "city" and d.value_xml like "%Martinhaven%") 
union
select * 
from jobs j
where exists (select 1 job_data d  where d.job_id = j.id and d.field = "city" and d.value_user like "%Martinhaven%") 

您可能會發現這會產生更好的性能，因為每個人都可以使用索引來查找城市行，然后掃描直到匹配；充其量它幾乎可以立即存在，最壞的情況它不比現有的表掃描差。

標籤：

上一篇：從自定義對話框插入新資料時，Listview無法更新

下一篇：選擇連接匹配值串列的位置