blob이 포함된 테이블의 mysql 쿼리 속도가 파일 시스템 캐시에 따라 다름

blob이 포함된 테이블의 mysql 쿼리 속도가 파일 시스템 캐시에 따라 다름

BLOB(각 항목의 크기가 1MB 이하, 일반적으로 훨씬 작음)가 있는 필드가 포함된 약 120k 행의 테이블이 있습니다.문제는 이 테이블의 열(BLOB는 포함하지 않음)을 묻는 쿼리를 실행할 때마다 파일 시스템 캐시가 비어 있으면 완료하는 데 약 40"이 걸린다는 것입니다.동일한 테이블의 모든 후속 쿼리에는 1" 미만의 값이 필요합니다(명령줄 클라이언트에서 서버 자체에서 테스트).쿼리에서 반환되는 행 수는 빈 집합에서 60k+로 다양합니다.

쿼리 캐시를 제거했기 때문에 아무 관련이 없습니다.테이블은 제 isam 이지만 innodb로 변경(및 ROW_FORMAT= compact 설정)을 시도했지만 아무런 행운이 없었습니다.

BLOB 열을 제거하면 쿼리가 항상 빠릅니다.

따라서 서버가 디스크(또는 일부)에서 블롭을 읽고 파일 시스템이 블롭을 캐싱한다고 가정합니다.문제는 트래픽이 많고 메모리가 제한된 서버에서는 파일 시스템 캐시가 가끔 새로 고쳐지기 때문에 이 특정 쿼리로 인해 계속 문제가 발생한다는 것입니다.

그래서 제 질문은, 테이블에서 블롭 컬럼을 제거하지 않고, 일을 상당히 빠르게 할 수 있는 방법이 있을까요?

다음은 설명, 인덱스 및 테이블 정의와 함께 차례로 실행되는 2개의 예제 쿼리입니다.

mysql> SELECT ct.score FROM completed_tests ct where ct.status != 'deleted' and ct.status != 'failed' and score < 100;
Empty set (48.21 sec)
mysql> SELECT ct.score FROM completed_tests ct where ct.status != 'deleted' and ct.status != 'failed' and score < 99;
Empty set (1.16 sec)

mysql> explain SELECT ct.score FROM completed_tests ct where ct.status != 'deleted' and ct.status != 'failed' and score < 99;
+----+-------------+-------+-------+---------------+--------+---------+------+-------+-------------+
| id | select_type | table | type  | possible_keys | key    | key_len | ref  | rows  | Extra       |
+----+-------------+-------+-------+---------------+--------+---------+------+-------+-------------+
|  1 | SIMPLE      | ct    | range | status,score  | status | 768     | NULL | 82096 | Using where |
+----+-------------+-------+-------+---------------+--------+---------+------+-------+-------------+
1 row in set (0.00 sec)


mysql> show indexes from completed_tests;
+-----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table           | Non_unique | Key_name    | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| completed_tests |          0 | PRIMARY     |            1 | id          | A         |      583938 |     NULL | NULL   |      | BTREE      |         |
| completed_tests |          1 | users_login |            1 | users_LOGIN | A         |       11449 |     NULL | NULL   | YES  | BTREE      |         |
| completed_tests |          1 | tests_ID    |            1 | tests_ID    | A         |         140 |     NULL | NULL   |      | BTREE      |         |
| completed_tests |          1 | status      |            1 | status      | A         |           3 |     NULL | NULL   | YES  | BTREE      |         |
| completed_tests |          1 | timestamp   |            1 | timestamp   | A         |      291969 |     NULL | NULL   |      | BTREE      |         |
| completed_tests |          1 | archive     |            1 | archive     | A         |           1 |     NULL | NULL   |      | BTREE      |         |
| completed_tests |          1 | score       |            1 | score       | A         |         783 |     NULL | NULL   | YES  | BTREE      |         |
| completed_tests |          1 | pending     |            1 | pending     | A         |           1 |     NULL | NULL   |      | BTREE      |         |
+-----------------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

mysql> show create table completed_tests;
+-----------------+--------------------------------------
| Table           | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
+-----------------+--------------------------------------
| completed_tests | CREATE TABLE `completed_tests` (
  `id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `users_LOGIN` varchar(100) DEFAULT NULL,
  `tests_ID` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `test` longblob,
  `status` varchar(255) DEFAULT NULL,
  `timestamp` int(10) unsigned NOT NULL DEFAULT '0',
  `archive` tinyint(1) NOT NULL DEFAULT '0',
  `time_start` int(10) unsigned DEFAULT NULL,
  `time_end` int(10) unsigned DEFAULT NULL,
  `time_spent` int(10) unsigned DEFAULT NULL,
  `score` float DEFAULT NULL,
  `pending` tinyint(1) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `users_login` (`users_LOGIN`),
  KEY `tests_ID` (`tests_ID`),
  KEY `status` (`status`),
  KEY `timestamp` (`timestamp`),
  KEY `archive` (`archive`),
  KEY `score` (`score`),
  KEY `pending` (`pending`)
) ENGINE=InnoDB AUTO_INCREMENT=117996 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED
1 row in set (0.00 sec)

나는 원래 이것을 mysql 쿼리에 처음에는 빠르게 올렸지만 지금은 더 많은 정보가 있어서 다른 질문으로 다시 올립니다. 나도 이것을 mysql 포럼에 올렸지만 답장이 없습니다.

언제나처럼 미리 감사드립니다.

MySQL의 BLOB(=TEXT) 스토리지 설계는 완전한 결함이 있고 intuitive에 반하는 것 같습니다.저는 같은 문제에 몇 번 부딪혔지만 권위 있는 설명을 찾을 수 없었습니다.제가 마침내 찾은 가장 자세한 분석은 2010년의 이 게시물입니다: http://www.mysqlperformanceblog.com/2010/02/09/blob-storage-in-innodb/

일반적인 믿음과 기대는 BLOBs/TEXTs가 주 행 저장소 외부에 저장된다는 것입니다(예: 이 답변 참조).하지만 이것은 사실이 아닙니다.여기에는 몇 가지 문제가 있습니다(위 기사에 근거합니다).

1. BLOB 항목의 크기가 수KB인 경우 행 데이터에 직접 포함됩니다.따라서 비 BLOB 열만 선택해도 엔진은 디스크에서 모든 BLOB를 로드해야 합니다.예를 들어, 각 100바이트의 비블롭 데이터와 5000바이트의 블롭 데이터로 구성된 1M 행이 있습니다.모든 비블롭 열을 선택하면 MySQL이 행당 약 100-120바이트(BLOB 주소의 경우 +20)인 디스크에서 읽게 됩니다.그러나 실제로 MySQL은 모든 BLOB를 행과 동일한 Disk 블록에 저장하므로 사용하지 않더라도 모두 함께 읽어야 하므로 Disk에서 읽은 데이터의 크기는 약 5100MB = 5GB입니다. 이는 예상보다 50배 이상 크며 쿼리 실행 속도가 50배 느림을 의미합니다.

   물론 이 설계에는 장점이 있습니다. 블롭본을 포함한 모든 열이 필요할 때 외부에 저장될 때보다 블롭이 행과 함께 저장될 때 SELECT 쿼리가 더 빠릅니다. 행당 1개의 추가 페이지 액세스를 피할 수 있습니다.그러나 이는 BLOB의 일반적인 사용 사례가 아니며 DB 엔진이 이 사례에 최적화되어서는 안 됩니다.데이터가 너무 작아서 일렬로 들어맞고 필요한 경우나 그렇지 않은 경우에 상관없이 모든 쿼리에 로드해도 괜찮다면 BLOB/TEXT 대신 VARCHAR 유형을 사용할 수 있습니다.

2. 어떤 이유로(긴 행 또는 긴 블롭) BLOB 값이 외부에 저장되더라도 768바이트 접두사는 여전히 행 자체에 유지됩니다.앞의 예를 들어 보겠습니다. 각 행에 100바이트의 비블롭 데이터가 있지만 이제 블롭 열에는 각 1MB의 항목이 저장되므로 외부에 보관해야 합니다.비블롭 열의 선택은 행당 약 800바이트(비블롭 + 블롭 접두사)를 읽어야 하며, 이는 예상했던 것보다 7배 더 큰 디스크 전송이며, 쿼리 실행 속도는 7배 더 느립니다.

3. 외부 BLOB 스토리지는 16KB의 블록에 공간을 할당하고 단일 블록에는 여러 항목을 담을 수 없기 때문에 블롭이 작고 각각 8KB씩 차지하는 경우 할당된 실제 공간이 두 배나 더 큽니다.

언젠가 이 디자인이 고쳐지길 바랍니다.MySQL은 크고 작은 모든 블롭을 DB에 저장하는 접두사 없이 외부 스토리지에 저장하며, 모든 크기의 항목에 대해 외부 스토리지 할당이 효율적입니다.이 문제가 발생하기 전에 BLOB/TEXT 열을 분리하는 것이 유일하게 합리적인 해결책인 것 같습니다. 다른 테이블이나 파일 시스템(각 BLOB 값은 파일로 유지됨)으로 분리하는 것입니다.

[업데이트 2019-10-15]

InnoDB 문서는 이제 위에서 논의한 문제에 대한 궁극적인 해답을 제공합니다.

https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html

BLOB/TEXT 값의 768바이트 접두사를 인라인에 저장하는 경우는 COMPCT 행 형식에 실제로 적용됩니다.문서에 따르면 "각 비 NULL 가변 길이 필드(...)에 대해 내부 부품은 768바이트입니다."

대신 Dynamic 행 형식을 사용할 수 있습니다.다음 형식을 사용합니다.

InnoDB는 오버플로 페이지에 대한 20바이트 포인터만을 포함하는 클러스터 인덱스 레코드와 함께 긴 가변길이 열 값(...)을 완전히 오프페이지에 저장할 수 있습니다. (...) TEXT 및 BLOB 열은 40바이트 이하로 일렬로 저장됩니다.

여기서 BLOB 값은 최대 40바이트의 인라인 스토리지를 차지할 수 있습니다. 이는 COMPRATE 모드에서와 마찬가지로 768바이트보다 훨씬 나은 수준이며 테이블에서 BLOB 유형과 비 BLOB 유형을 혼합하고 여러 행을 상당히 빠르게 검색할 수 있는 경우에는 훨씬 합리적인 접근 방식으로 보입니다.또한 확장(20바이트 이상) 인라인 스토리지는 크기가 20-40바이트인 값의 경우에만 사용됩니다. 값이 큰 경우에는 COMPRAT 모드와 달리 20바이트 포인터만 저장됩니다(접두사 없음).따라서 확장된 40바이트 스토리지는 실제로 거의 사용되지 않으며 인라인 스토리지의 평균 크기를 20바이트(또는 BLOB에 20B 미만의 작은 값을 많이 유지하는 경우)로 가정할 수 있습니다.대체적으로 InnoDB에서 BLOB 열의 예측 가능한 성능을 높이기 위해서는 대부분의 경우 COMPRATE가 아닌 Dynamic 행 형식이 기본적인 선택이 되어야 합니다.

InnoDB에서 실제 물리적 스토리지를 확인하는 방법의 예는 다음과 같습니다.

https://dba.stackexchange.com/a/210430/177276

MyISAM의 경우, BLOB에 대한 오프페이지 스토리지를 전혀 제공하지 않는 것으로 보입니다(열선만 가능).자세한 내용은 여기에서 확인하십시오.

• https://dev.mysql.com/doc/refman/5.7/en/dynamic-format.html
• https://forums.mysql.com/read.php?24,105964,267596#msg-267596

저는 이 문제에 대해 한동안 연구를 하고 있었습니다.많은 사람들이 기본 키 하나만 별도의 테이블에 있는 blob을 사용하고 blob 메타 데이터를 blob 테이블에 대한 외부 키가 있는 다른 테이블에 저장할 것을 권장합니다.이것이 있으면 성능이 상당히 높아질 것입니다.

두 개의 관련 열에 복합 인덱스를 추가하면 테이블 데이터에 직접 액세스하지 않고도 이러한 쿼리를 실행할 수 있습니다.

CREATE INDEX `IX_score_status` ON `completed_tests` (`score`, `status`);

MariaDB로 전환할 수 있다면 테이블 제거 최적화를 최대한 활용할 수 있습니다.이렇게 하면 BLOB 필드를 자신의 테이블로 분할하고 보기를 사용하여 왼쪽 조인을 사용하여 기존 테이블 구조를 재생성할 수 있습니다.이렇게 하면 쿼리 실행에 명시적으로 필요한 경우에만 BLOB 데이터에 액세스할 수 있습니다.

블롭이 있는 테이블에 대해 WHERE 쿼리 후 사용되는 필드에 인덱스 또는 인덱스를 추가하기만 하면 됩니다.

예를 들어 이 필드가 있는 테이블이 두 개 있습니다.

users : USERID, NAME, ...
userphotos : BLOBID, BLOB, USERNO, ...

select * from userphotos where USERNO=123456; 

일반적으로 이것은 잘 작동합니다.대용량 이미지(예: BLOB, MEDIAL BLOB 또는 LONG BLOB 총 5GB 이상)가 많을 경우 BLOB에 비해 많은 시간(분 이상)이 소요됩니다.ID가 기본 키입니다.

WHERE 절에 BLOB 테이블 필드에 대한 인덱스가 없으면 MySQL은 이미지를 포함한 전체 데이터를 검색하고 있습니다.데이터가 점점 더 커지면 시간이 많이 걸립니다.USERNO 필드에 대한 인덱스를 생성하면 데이터베이스 속도가 빨라지고 전체 데이터 크기에 따라 독립적으로 처리됩니다.

해결책:

**Add Index to the USERNO at userphotos**

질문에 대한 답으로 ct.status에 대한 인덱스를 만들어야 합니다.

언급URL : https://stackoverflow.com/questions/9511476/speed-of-mysql-query-on-tables-containing-blob-depends-on-filesystem-cache