SELECT 속도를 높이는 방법..MySQL의 LIKE 쿼리가 여러 열에 있습니까?

SELECT 속도를 높이는 방법..MySQL의 LIKE 쿼리가 여러 열에 있습니까?

자주 사용하는 MySQL 테이블이 있습니다.SELECT x, y, z FROM table WHERE x LIKE '%text%' OR y LIKE '%text%' OR z LIKE '%text%'문의합니다.어떤 종류의 지표라도 속도를 높이는 데 도움이 될까요?

테이블에는 수백만 개의 레코드가 있습니다.검색 속도를 높일 수 있는 것이 있으면 데이터베이스 파일에 의한 디스크 사용률 및 데이터 처리 속도에 심각한 영향을 미칩니까?INSERT그리고.DELETE스테이트먼트(없음)UPDATE실행한 적이 없음)

업데이트: 투고 후 바로 방법에 대한 많은 정보와 토론을 보았습니다.LIKE쿼리에 사용됩니다.솔루션은 반드시LIKE '%text%'(즉, 찾고 있는 텍스트가 선두에 추가되어 % 와일드카드가 부가됩니다).보안 등 여러 가지 이유로 데이터베이스는 로컬이어야 합니다.

텍스트 열의 경우 인덱스는 왼쪽부터 N자를 인덱싱하여 작동하므로 인덱스는 쿼리 속도를 높일 수 없습니다.LIKE '%text%'를 사용하면 텍스트 앞에 가변 문자 수가 있을 수 있으므로 색인을 사용할 수 없습니다.

당신이 해야 할 일은 그런 쿼리를 전혀 사용하지 않는 것입니다.대신 MySQL이 MyISAM 테이블에 지원하는 FTS(Full Text Search) 등을 사용해야 합니다.또한 이러한 인덱싱 시스템을 직접 제작하는 것은 매우 쉽습니다.ISAM 테이블은 단어와 해당 ID를 실제 테이블에 저장하는 별도의 인덱스 테이블만 있으면 됩니다.

갱신하다

MySQL 5.6+가 설치된 InnoDB 테이블에서 전체 텍스트 검색을 사용할 수 있습니다.

인덱스는 선행 와일드카드와 텍스트를 일치시키는 데 도움이 되지 않습니다. 인덱스는 다음 항목에 사용할 수 있습니다.

LIKE 'text%'

하지만 그렇다고 해서 끊기진 않을 것 같아.이러한 유형의 쿼리의 경우 검색할 수 있는 레코드의 양을 조정하려면 전체 텍스트 검색 공급자를 참조해야 합니다.제가 선호하는 프로바이더는 스핑크스입니다.매우 풀기능/빠른 속도 등입니다.루신도 볼만할 거야MyISAM 테이블의 전체 텍스트 색인도 작동하지만, 쓰기 횟수가 많은 데이터베이스에서는 MyISAM을 추구하는 것은 좋은 생각이 아닙니다.

인덱스는 검색 기준이 와일드카드로 시작하는 쿼리 속도를 높이는 데 사용할 수 없습니다.

LIKE '%text%'

색인은 양식의 검색어에 사용할 수 있습니다(선택성에 따라 사용 가능).

LIKE 'text%'

하고 를 사용합니다.MATCH() AGAINST().

.like쿼리, 특히 검색어 양쪽에서 와일드카드를 사용하는 쿼리.

열에 .MATCH() AGAINST()쿼리를 사용하여 전체 텍스트 색인을 검색합니다.

1. 필요한 열에 전체 텍스트 색인을 추가합니다.

   ALTER TABLE table ADD FULLTEXT INDEX index_table_on_x_y_z (x, y, z);
   

2. 그런 다음 다음 다음 열을 쿼리합니다.

   SELECT * FROM table WHERE MATCH(x,y,z) AGAINST("text")
   

시험 결과, 이 쿼리는 100만 개 이상의 레코드가 있는 테이블에서 약 1ms가 걸리는 것으로 나타났습니다.한 와일드 카드와 않습니다.LIKE %text%16,400밀리초

벤치마크

MATCH(x,y,z) AGAINST("text") 소요

LIKE %text% 16400ms 16400 리 takes

16400배 고속화!

보고 있는 필드가 자주 비어 있거나 일정한 것이 포함되어 있는 경우 like/rlike와 함께 인덱스를 사용하여 쿼리 속도를 높일 수 있습니다.

이 경우 고정값이 있는 "and" 절을 추가하여 인덱스를 사용하여 방문하는 행을 제한할 수 있습니다.

보통 태그가 많이 들어 있지 않은 큰 테이블에서 태그를 검색하기 위해 시도했습니다.

SELECT * FROM objects WHERE tags RLIKE("((^|,)tag(,|$))" AND tags!=''

태그에 인덱스가 있는 경우 검색되는 행을 제한하는 데 사용되는 것을 알 수 있습니다.

mysql5.1을 mysql5.7로 업그레이드해 보세요.

나는 약 7만 장의 음반을 가지고 있다.다음 SQL을 실행합니다.

select * from comics where name like '%test%'; 

mysql5.1에서는 2000ms가 소요됩니다.그리고 mysql5.7 또는 mysql5.6에서는 200ms가 소요됩니다.

다른 방법:

이러한 문자열 REVERSEd를 사용하여 계산된 열을 저장하고 다음을 사용할 수 있습니다.

SELECT x, y, z FROM table WHERE x LIKE 'text%' OR y LIKE 'text%' OR z LIKE 'text%' OR xRev LIKE 'txet%' OR yRev LIKE 'txet%' OR zRev LIKE 'txet%' 

저장된 지속 열을 추가하는 방법의 예

ALTER TABLE table ADD COLUMN xRev VARCHAR(N) GENERATED ALWAYS AS REVERSE(x) stored;

보세요.xRev,yRevsyslog.

테이블 전체의 스캔을 회피하는 또 다른 방법은 서브스트링을 선택하여having 스테이트먼트에서 체크하는 것입니다.

SELECT 
    al3.article_number,
    SUBSTR(al3.article_number, 2, 3) AS art_nr_substr,
    SUBSTR(al3.article_number, 1, 3) AS art_nr_substr2,
    al1.*
FROM
    t1 al1 
    INNER JOIN t2 al2 ON al2.t1_id = al1.id
    INNER JOIN t3 al3 ON al3.id = al2.t3_id
WHERE
    al1.created_at > '2018-05-29'
HAVING 
    (art_nr_substr = "FLA" OR art_nr_substr = 'VKV' OR art_nr_subst2 = 'PBR');

「」를 SELECT foo FROM bar WHERE baz LIKE 'ZOT%'query를 지정하면 인덱스 길이가 요청 문자 수와 일치해야 합니다.

다음은 방금 전의 실제 사례입니다.

다음은 쿼리입니다.

EXPLAIN SELECT COUNT(*) FROM client_detail cd
JOIN client_account ca ON cd.client_acct_id = ca.client_acct_id
WHERE cd.first_name LIKE 'XX%' AND cd.last_name_index LIKE 'YY%';

색인 없음:

+-------+
| rows  |
+-------+
| 13994 |
|     1 |
+-------+

먼저 4배 지수를 시험해 봅시다.

CREATE INDEX idx_last_first_4x4 on client_detail(last_name_index(4), first_name(4));

+------+
| rows |
+------+
| 7035 |
|    1 |
+------+

조금 낫지만 COUNT(*)는 결과가 102개밖에 없음을 나타냅니다.이제 2x 인덱스를 추가합니다.

CREATE INDEX idx_last_first_2x2 on client_detail(last_name_index(2), first_name(2));

수율:

+------+
| rows |
+------+
|  102 |
|    1 |
+------+

두 인덱스는 현재도 유효하며 MySQL은 이 쿼리에 대해 후자의 인덱스를 선택했습니다.그러나 보다 효율적인 경우에는 4x4 쿼리를 선택합니다.

인덱스 순서는 편리할 수 있습니다.4x4보다 전의 2x2 또는 그 반대의 순서로 테스트하여 고객의 환경에서 어떻게 동작하는지 확인해 주십시오.인덱스의 순서를 변경하려면 이전 인덱스를 삭제하고 다시 생성해야 합니다.

언급URL : https://stackoverflow.com/questions/2042269/how-to-speed-up-select-like-queries-in-mysql-on-multiple-columns