1 대 n의 관련 테이블에서 여러 특정 매칭이 있는 테이블에서 Select를 수행하려면 더 빠른 솔루션이 필요합니다.

1 대 n의 관련 테이블에서 여러 특정 매칭이 있는 테이블에서 Select를 수행하려면 더 빠른 솔루션이 필요합니다.

개요

시스템 내에는 다음과 같은 구조가 있으며, 사용자가 작업에 속성을 저장하고 사용자가 직접 이름을 지정할 수 있습니다.1개의 Atribute에는 2개의 값(사용자 값과 시스템 값)이 있습니다.사용자 값 또는 시스템 값 중 하나가 히트할 수 있는 속성을 기준으로 작업을 필터링하려고 합니다.

현황

테이블 작업

아이디	company_id	상황
1	2	활동적인
2	2	창조했다
3	3	창조했다
4	12	활발하지 않은

테이블 작업_데이터

작업_id	들판	값_사용자	value_xml
1	도시	베를린
1	전화	1234567
1	유형	풀타임의
2	도시	뉴욕
2	전화	33333333
2	유형	파트타임
3	도시		베를린
3	전화		123
3	유형		풀타임의

인덱스:

테이블	Key_name	Column_name	대조
작업	기본적인	아이디	A
작업_데이터	job_data_data_id_foreign	작업_id	A

사용자는 예를 들어 "show me all jobs with city Berlin and phone 123"과 같은 여러 속성을 필터링해야 합니다.둘 다 도시 값 중 하나에 베를린이 있고 전화 값 중 하나에 123과 같은 전화번호가 있기 때문에, 이것은 직업 1과 3을 나타냅니다.

저는 몇 가지 솔루션을 사용하고 있었습니다만, 지금은 데이터베이스에 120,000개의 활성 작업이 있습니다.아트리뷰트는 100만개가 넘습니다.코드의 속도는 충분하지 않습니다.델의 현재 솔루션은 다음과 같습니다.

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;

출력:

아이디	company_id	상황
1	2	활동적인
3	3	창조했다

이것은 간략화되어 6가지 필터의 가능성이 있습니다만, 모두 같은 종류이기 때문에, 2개 밖에 투고하지 않았습니다.

질문.

어떻게 하면 더 빨리 할 수 있을까?현재 1개 선택에는 5~10초가 필요합니다.물론 필터링을 빠르게 하기 위해 데이터베이스를 재구성할 수 있지만, 구축 중인 더 큰 시스템이기 때문에 이를 방지하기 위해 노력하고 있습니다.

잘 부탁드립니다.

두 가지 문제가 있습니다.

• 다중 값 필드.
• like '%word%'색인이 까다롭습니다.

dr 데모

---

스키마를 수정하다

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;

이제 잘못된 데이터를 방지하기 위해 값과 소스에 늘이 아닌 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;

이것에 의해, 많은 문제가 해결됩니다.

• 원하는 만큼 많은 소스를 얻을 수 있습니다.
• 값이 null이 아님을 확인할 수 있습니다.
• 중복을 방지할 수 있습니다.
• 두 곳에서 값을 확인할 필요는 없습니다.
• 인덱싱이 더 쉽습니다.

---

쿼리

이 문제를 해결하면 문의가 훨씬 쉬워집니다.

두 필드/값 쌍에 일치하는 작업을 찾으려면 일반적으로 를 수행합니다. 그러나 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에서는 단순 인덱스를 사용하지 않습니다. 각 행을 일치하는 필드로 스캔해야 합니다. 단순 색인은 에 대해서만 작동합니다. 다른 데이터베이스에는 이 작업에 대한 특수 색인이 있습니다.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);

의 각및각 집약되는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 IN:연과 IN 과 the the the the the the the the the the the the the the the the the the the the the the the the the the s와 함께 합니다.

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불평등은 코드에서 제거될 수 있지만, 나는 명확성을 위해 그것들을 남겨두었다.

예를 들어 '%...'와 같은 수신 불가 조건을 사용하는 경우 도움이 되는 것은 별로 없습니다.다만, 다음의 조작으로 CPU와 IO를 불필요하게 소비하고 있습니다.count(*)검색된 각 기준에 대해.

옵티마이저가 이것을 검출해, 기존의 조작으로서 내부적으로 최적화할 가능성이 있습니다만, 항상 명시적인 것을 추천합니다.

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%")
) 

엔티티 속성 모델의 경우 모든 값이 동일한 열에 있으면 더 효율적입니다.이 경우 초기에 구현하기에는 너무 많은 변경 사항이 발생할 수 있으므로 또는 를 사용하는 대신 각 열을 개별적으로 확인하는 여러 기준을 사용해야 합니다.이것은 각각의 인덱스를 사용하는 것이 가장 편리합니다(필드, 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%") 

각각 인덱스를 사용하여 도시의 행만을 검색한 후 일치하는 것이 있을 때까지 스캔할 수 있기 때문에 성능이 향상될 수 있습니다.기껏해야 거의 즉시 존재할 수 있고, 최악의 경우 기존 테이블 스캔보다 나쁘지 않습니다.

부분 솔루션:

대신

( SELECT COUNT(*) ... ) > 0 )

쓰다

EXISTS ( SELECT 1 ... )

후자는 첫 번째 발생이 발견되면 스캔을 중지합니다.(전자는 표 전체를 봐야 합니다.)

진짜 악당은

• OR--최적화가 잘 되지 않는다.
• LIKE '%...'-- '선행 와일드카드'는 인덱스를 사용할 수 없도록 합니다.

제공해주세요SHOW CREATE TABLE어떤 인덱스가 존재하는지 확인할 수 있습니다.

또 다른 문제는 어설프고 비효율적인 "Entity-Attribute-Value" 스키마 패턴의 사용입니다.

그리고 'XML'이 걱정이에요.

이제 좀 더 격려가 되는 얘기를 해보죠.aFULLEXT검색 중인 데이터가 들어 있는 열과MATCH(col) AGAINST('+Martinhaven +omnis' IN BOOLEAN MODE)정말 빠를 것 같아요.

언급URL : https://stackoverflow.com/questions/70644566/i-need-a-faster-solution-to-perform-a-select-on-a-table-with-multiple-specific-m