MySQL / MariaDB 전체 텍스트 검색 매우 느림
전체 텍스트 검색에는 몇 초가 아니라 몇 분이 걸립니다.테이블에는 50K 행이 있습니다.각 ocr_text에는 신문 전체 2페이지의 모든 텍스트인 많은 텍스트가 포함되어 있습니다.
MariaDB [scu_db]> describe frames;
+-------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| dbRollID | int(10) unsigned | NO | MUL | 0 | |
| frame_num | int(10) unsigned | NO | | 0 | |
| xLeadEdge | int(11) | NO | | 0 | |
| yTrailEdge | int(11) | NO | | 0 | |
| wLeftEdge | int(11) | NO | | 0 | |
| hRightEdge | int(11) | NO | | 0 | |
| ocr_text | mediumtext | NO | MUL | NULL | |
| ocr_rects | longblob | NO | | NULL | |
+-------------+------------------+------+-----+---------+----------------+
또한 데이터에 단어가 자주 나타날수록 쿼리 속도가 느려집니다.
이 쿼리는 10초 걸립니다.
SELECT dbRollID
FROM frames
WHERE MATCH (ocr_text) AGAINST ('+1912' IN BOOLEAN MODE)
ORDER BY id
일반적인 단어에 대한 이 쿼리는 2.5분이 걸립니다.
SELECT dbRollID
FROM frames
WHERE MATCH (ocr_text) AGAINST ('+john' IN BOOLEAN MODE)
ORDER BY id
추가해도 상관없습니다.LIMIT 50(어느 쪽이 그래야 한다고 생각합니까?)
이건 끔찍할 정도로 느린 것 같습니다.내가 여기서 뭘 잘못하고 있는 거지?
SHOW CREATE TABLE결과:
CREATE TABLE `frames` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`dbRollID` int(10) unsigned NOT NULL DEFAULT 0,
`frame_num` int(10) unsigned NOT NULL DEFAULT 0,
`xLeadEdge` int(11) NOT NULL DEFAULT 0,
`yTrailEdge` int(11) NOT NULL DEFAULT 0,
`wLeftEdge` int(11) NOT NULL DEFAULT 0,
`hRightEdge` int(11) NOT NULL DEFAULT 0,
`ocr_text` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
`ocr_rects` longblob NOT NULL,
PRIMARY KEY (`id`),
KEY `fk_roll_id_1` (`dbRollID`),
FULLTEXT KEY `ocr_text` (`ocr_text`),
CONSTRAINT `fk_roll_id_1` FOREIGN KEY (`dbRollID`) REFERENCES `scansettings` (`dbRollID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=474139 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
explain SELECT dbRollID FROM frames WHERE MATCH (ocr_text) AGAINST ('+john' IN BOOLEAN MODE) ORDER BY id결과:
+------+-------------+--------+----------+---------------+----------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+--------+----------+---------------+----------+---------+------+------+-----------------------------+
| 1 | SIMPLE | frames | fulltext | ocr_text | ocr_text | 0 | | 1 | Using where; Using filesort |
+------+-------------+--------+----------+---------------+----------+---------+------+------+-----------------------------+
편집/업데이트:
좋아요, 저는 가설을 가지고 있지만, 그것은 잘못된 가정에 근거한 것일 수도 있습니다.
따라서 텍스트 열에 전체 텍스트 키를 사용하면 MySQL이 해당 열에 대한 인덱스를 생성한 다음 전체 텍스트 검색을 수행할 때 해당 인덱스를 현명하게 사용한다는 말을 들었습니다(예: innodb 엔진에서 MATCH AUNG).그래서 이론적으로 저는 이것을 너무 많이 생각해서 저만의 색인 시스템을 만들 필요가 없습니다.이것이 사실입니까?
좋아요. 이것이 사실이라고 가정하면, MySQL은 이 단어 색인을 영리한 방법으로 만듭니다. 다양한 단어가 많지 않기 때문입니다.그렇죠?
그런데 제 문자는 OCR 문자입니다.그리고 그것은 매우 나쁜 OCR 텍스트입니다.그리고 그것은 수백만 개의 이미지에서 수행되는 매우 나쁜 OCR 텍스트입니다. 대부분은 OCR 가능한 텍스트를 포함하지 않고 필기체로 씁니다.그래서 제 글에는 수많은 쓰레기 단어들이 있습니다.아마도 그것은 대부분 쓰레기 같은 단어들일 것입니다.몰라.하지만 이것은 MySQL이 구축하는 지수가 크다는 것을 의미합니까?그리고 innodb의 메모리 버퍼에 캐시할 수 없을 정도로 충분히 큰가요?
생각은?이게 사실일까요?만약 그렇다면, 제가 제 문자에 있는 모든 쓰레기를 치울 수 있다면, 그것이 효과가 있을까요?
SHOW TABLE STATUS LIKE "frames";
+--------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+------------------+-----------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | Max_index_length | Temporary |
+--------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+------------------+-----------+
| frames | InnoDB | 10 | Dynamic | 51419 | 80792 | 4154245120 | 0 | 3997696 | 7340032 | 474566 | 2021-08-17 13:26:24 | 2021-08-19 18:03:18 | NULL | utf8mb4_unicode_ci | NULL | | | 0 | N |
+--------+--------+---------+------------+-------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+--------------------+----------+----------------+---------+------------------+-----------+
RDS에서 SELECT를 설명합니다(MariaDB가 아닌 다르지만 유사한 서버, MySQL).
explain SELECT dbRollID FROM frames WHERE MATCH (ocr_text) AGAINST ('+john' IN BOOLEAN MODE) ORDER BY id;
+----+-------------+--------+------------+----------+---------------+----------+---------+-------+------+----------+---------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+----------+---------------+----------+---------+-------+------+----------+---------------------------------------------------+
| 1 | SIMPLE | frames | NULL | fulltext | ocr_text | ocr_text | 0 | const | 1 | 100.00 | Using where; Ft_hints: no_ranking; Using filesort |
+----+-------------+--------+------------+----------+---------------+----------+---------+-------+------+----------+---------------------------------------------------+
추가 실험을 수행하는 2021년 8월 27일 업데이트:추가 WHERE 조건을 사용하거나 하위 쿼리를 사용하거나 LIMIT를 사용하여 쿼리 범위를 제한하는 것이 시간에 전혀 영향을 미치지 않는 이유를 이해할 수 없습니다.
SELECT dbRollID FROM frames
WHERE MATCH (ocr_text) AGAINST ('+john' IN BOOLEAN MODE)
ORDER BY dbRollID;
29219 rows in set (1 min 46.959 sec)
SELECT xLeadEdge FROM frames
WHERE dbRollID=110
AND MATCH (ocr_text) AGAINST ('+john' IN BOOLEAN MODE)
ORDER BY xLeadEdge;
340 rows in set (1 min 45.984 sec)
SELECT * FROM frames
WHERE dbRollID=110;
512 rows in set (0.272 sec)
SELECT xLeadEdge
FROM
(
SELECT * FROM frames WHERE dbRollID=110
) AS a
WHERE MATCH (ocr_text) AGAINST ('+john' IN BOOLEAN MODE)
ORDER BY xLeadEdge;
340 rows in set (1 min 47.044 sec)
SELECT dbRollID FROM frames
WHERE MATCH (ocr_text) AGAINST ('+john' IN BOOLEAN MODE)
ORDER BY dbRollID LIMIT 1;
1 row in set (1 min 46.575 sec)
SELECT xLeadEdge FROM frames
WHERE dbRollID=110 AND MATCH (ocr_text) AGAINST ('+john' IN BOOLEAN MODE)
ORDER BY xLeadEdge LIMIT 1;
1 row in set (1 min 46.939 sec)
(참고: 그리고 3분이 아닌 1분 45초는 원래 테스트보다 훨씬 큰 innodb 버퍼 세트를 가지고 있기 때문입니다.그러나 2분 가까이 걸리는 쿼리는 여전히 쓸데없이 느립니다.)
- 특별 지수? - 네.
- 사용했나요? - 네. 다음 문서에서 type=전체 텍스트를 참조하십시오.
EXPLAIN. - 백만 개의 가짜 단어들? - 물론입니다.
- 너무 커서 캐시할 수 없습니까? - 문제 없습니다. 캐시는 "블록"에 있습니다.
- 따라서 "John"을 찾는 경우 디스크에서 "John"이 있는 블록만 읽으면 됩니다.
- 이제 잠재적인 문제가 발생합니다." "John"이 각 행에 대해,
dbRollID찾아야 합니다.이를 위해서는 몇 개의 BTree에서 조회가 필요하지만, 행이 50K밖에 없기 때문에 두 개 모두 캐시가 잘 되어 있는 것 같습니다. - 하지만 진짜 문제는 "존"입니다.그는 29219개의 행에서 발생합니다. -- 행의 약 60%입니다.MyISAM에서 그것은 "FT 인덱스 건너뛰기"를 트리거할 것입니다. InnoDB가 동일한 "50% 규칙"을 가지고 있는지 모르겠습니다.
제 마지막 이론을 테스트하기 위해, 예를 들어 40%의 행에서 발생하는 단어를 검색해 주십시오.
좀 더 많은 통찰력을 얻으려면 를 실행하십시오.
SELECT last_update,
n_rows,
'Data & PK' AS 'Type',
clustered_index_size * 16384 AS Bytes,
ROUND(clustered_index_size * 16384 / n_rows) AS 'Bytes/row',
clustered_index_size AS Pages,
ROUND(n_rows / clustered_index_size) AS 'Rows/page'
FROM mysql.innodb_table_stats
WHERE table_name = 'frames'
UNION
SELECT last_update,
n_rows,
'Secondary Indexes' AS 'BTrees',
sum_of_other_index_sizes * 16384 AS Bytes,
ROUND(sum_of_other_index_sizes * 16384 / n_rows) AS 'Bytes/row',
sum_of_other_index_sizes AS Pages,
ROUND(n_rows / sum_of_other_index_sizes) AS 'Rows/page'
FROM mysql.innodb_table_stats
WHERE table_name = 'frames'
AND sum_of_other_index_sizes > 0
;
+---------------------+--------+-------------------+------------+-----------+--------+-----------+
| last_update | n_rows | Type | Bytes | Bytes/row | Pages | Rows/page |
+---------------------+--------+-------------------+------------+-----------+--------+-----------+
| 2021-08-26 13:03:21 | 35305 | Data & PK | 3476848640 | 98480 | 212210 | 0 |
| 2021-08-26 13:03:21 | 35305 | Secondary Indexes | 4194304 | 119 | 256 | 138 |
+---------------------+--------+-------------------+------------+-----------+--------+-----------+
2 rows in set (0.161 sec)
그리고.
SELECT * FROM mysql.innodb_index_stats
WHERE table_name = 'frames';
+---------------+------------+------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+---------------+------------+------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| scu_db | frames | FTS_DOC_ID_INDEX | 2021-08-26 13:03:21 | n_diff_pfx01 | 61745 | 20 | FTS_DOC_ID |
| scu_db | frames | FTS_DOC_ID_INDEX | 2021-08-26 13:03:21 | n_leaf_pages | 95 | NULL | Number of leaf pages in the index |
| scu_db | frames | FTS_DOC_ID_INDEX | 2021-08-26 13:03:21 | size | 160 | NULL | Number of pages in the index |
| scu_db | frames | PRIMARY | 2021-08-26 13:03:21 | n_diff_pfx01 | 35305 | 20 | id |
| scu_db | frames | PRIMARY | 2021-08-26 13:03:21 | n_leaf_pages | 189726 | NULL | Number of leaf pages in the index |
| scu_db | frames | PRIMARY | 2021-08-26 13:03:21 | size | 212210 | NULL | Number of pages in the index |
| scu_db | frames | fk_roll_id_1 | 2021-08-26 13:03:21 | n_diff_pfx01 | 112 | 20 | dbRollID |
| scu_db | frames | fk_roll_id_1 | 2021-08-26 13:03:21 | n_diff_pfx02 | 62851 | 20 | dbRollID,id |
| scu_db | frames | fk_roll_id_1 | 2021-08-26 13:03:21 | n_leaf_pages | 55 | NULL | Number of leaf pages in the index |
| scu_db | frames | fk_roll_id_1 | 2021-08-26 13:03:21 | size | 96 | NULL | Number of pages in the index |
+---------------+------------+------------------+---------------------+--------------+------------+-------------+-----------------------------------+
10 rows in set (0.055 sec)
일부 쿼리는 "왜" 최적화되지 않는지 자세히 알아봅니다.
WHERE x=1 -- INDEX(x) helps a lot
WHERE MATCH(s) AGAINST(..) -- Needs FULLTEXT
WHERE MATCH(s) AGAINST(..) AND x=1 -- (below)
를 때FULLTEXT또는SPATIAL그리고 '정규' 색인을 작성하면 내부가 복잡해집니다.간단히 말해서, 두 가지 모두를 할 수 있는 방법은 없습니다. 하나만 하고 다른 하나는 무차별 대입(지표 없음)하는 것이 실용적입니다.
따라서 세 번째 표현식은 두 가지 방법 중 하나로 수행할 수 있습니다.
x=1그 다음에 그다, 음에저.MATCH는 이의문제입니다.FULLTEXT테이블 전체만 사용할 수 있는 것 같습니다.즉, 이 주문은 할 수 없습니다.MATCH그 다음에 그다, 음에저.x=1따라서 이 방법이 선택된 방법입니다.에서, 의예에서당신,서,MATCH시간이 오래 걸립니다.그런 다음 결과를 필터링합니다.x모든 행을 확인해야 함에도 불구하고 비교적 빠릅니다.MATCH
"반적인경" 에서.FULLTEXT응용 프로그램에서 FT 필터링은 매우 적은 행으로 내려갑니다. 따라서 "match first"는 "충분히 좋습니다.
하위 쿼리 예제가 있습니다.난 그걸 설명할 수 없다.
ORDER BY 및/는LIMIT 마세요..MATCH
RAM이 더 많고 nodb_buffer_pool_size가 증가하면 테이블 크기와 주의 사항에 따라 큰 효과를 볼 수 있습니다.
내 로컬 테스트 서버에서 행의 절반이 테이블에 있고 버퍼가 2GB로 설정되어 있으면 다음과 같은 상황이 발생합니다.
+john에 대한 첫 번째 검색은 2분이 걸립니다.+john에 대한 연속적인 검색은 1초도 걸리지 않습니다.그러면 다른 일반적인 단어를 검색하는 데 10-15초밖에 걸리지 않습니다.서버를 재부팅하면 첫 번째 검색은 항상 2분이 걸립니다.연속적인 검색은 빠릅니다.인덱스가 캐시되고 있는 것 같습니다.따라서 서버가 재부팅될 때 첫 번째 검색은 항상 느려집니다.이 캐시가 플러시되고 검색 속도가 다시 느려지는 다른 조건은 무엇인지 궁금합니다.사용자들에게 이렇게 말하면 됩니까? "오, 당신이 매일 하는 첫 번째 검색은 끔찍하게 느릴 것입니다.그것을 기다리기만 하면 효과가 있을 것입니다!"업데이트 편집:여기서 또 다른 SOQ/a를 찾았습니다. mysqq/a는 첫 번째 쿼리에서 느리고, 그 다음 관련 쿼리에서 빠릅니다. 캐시를 워밍업하는 것이라고 합니다.
또한 코드에 시간 제한을 추가하여 이를 사용하는 웹 페이지가 잠기지 않습니다.하지만 이렇게 할 수 없습니다. 긴 2분의 첫 번째 검색 전에 시간이 초과되면 캐시가 채워지지 않고(실제로 이 경우) 모든 검색이 느려지고 항상 시간이 초과되기 때문입니다.
또한 RAM 및 innodb_buffer_pool_size에 비해 테이블이 너무 커지면 임계값이 초과되고 일반적인 단어 검색은 캐시가 너무 작은 것처럼 몇 분이 걸립니다.
그래서 그 해답은 아마도 더 강력한 서버와 더 많은 RAM이 필요하다는 것입니다.하지만 저는 여전히 이것이 답일 필요는 없다고 생각합니다.특히 생산 데이터 세트가 훨씬 더 커질 것이기 때문입니다.
약간 관련된 문제가 하나 더 있습니다.만약 내 SELECT가 더 많은 열, 특히 더 큰 열을 요청하면 이 모든 것이 중단되고 쿼리 속도가 훨씬 더 느려집니다.그래서 제가 이것을 감수해야 한다고 해도, 데이터를 빼내기 위해서는 한 번에 더 스마트하고 여러 개의 쿼리를 작성해야 합니다.
업데이트: 다음은 이 문제를 완화하기 위해 수행한 모든 작업의 전체 목록입니다.
- 시스템 RAM과 innodb_buffer_pool_size를 늘려 전체 인덱스를 캐시에 맞춥니다.
- 캐시 "warm up" - 서버가 다시 시작될 때마다 +john에 대한 전체 텍스트 검색을 수행하는 코드 쓰기
- 데이터에서 3자 이하의 단어를 모두 제거합니다(캐시에도 도움이 될 수 있습니다.
- 하위 작업을 수행할 수 있는 모든 쿼리 수정
- MATCH AUTHANG을 수행할 때 추가 열을 선택하지 않도록 쿼리 수정
- 추가 열을 가져오기 위해 후속 조치 쿼리를 수행하도록 쿼리 수정
- 최악의 경우 MySQL 시간 초과 추가
언급URL : https://stackoverflow.com/questions/68856942/mysql-mariadb-full-text-search-very-slow
'programing' 카테고리의 다른 글
| HTML 링크를 단추처럼 만들려면 어떻게 해야 합니까? (0) | 2023.08.12 |
|---|---|
| PowerShell DSC - 구성 매개 변수를 ScriptResources에 전달하는 방법은 무엇입니까? (0) | 2023.08.12 |
| Android: Android.content.res.리소스 $NotFoundException:문자열 리소스 ID #0x5 (0) | 2023.08.12 |
| Android 매니페스트의 versionCode 대 versionName (0) | 2023.08.12 |
| 어떻게 Git 충돌을 yarn.lock에서 해결합니까? (0) | 2023.08.12 |