У меня есть таблица с примерно 120k строк, которая содержит поле с BLOB (не более 1 МБ каждая запись в размере, как правило, гораздо меньше). Моя проблема в том, что всякий раз, когда я запускаю запрос, запрашивающий какие-либо столбцы в этой таблице (не включая BLOB), если кэш файловой системы пуст, для его завершения требуется приблизительно 40 ''. Для всех последующих запросов к одной и той же таблице требуется меньше 1 '' (тестирование из клиента командной строки, на самом сервере). Количество строк, возвращаемых в запросах, варьируется от пустого набора до 60k+
Я удалил кеш запросов, чтобы он не имел к этому никакого отношения. Таблица myisam, но я также попытался изменить ее на innodb (и установив ROW_FORMAT = COMPACT), но безуспешно.
Если я удаляю столбец BLOB, запрос всегда выполняется быстро.
Поэтому я бы предположил, что сервер читает двоичные объекты с диска (или их частей), а файловая система их кэширует. Проблема в том, что на сервере с большим трафиком и ограниченной памятью кэш файловой системы обновляется время от времени, поэтому этот конкретный запрос продолжает вызывать у меня проблемы.
Итак, мой вопрос, есть ли способ значительно ускорить процесс, не удаляя столбец 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, но не получил ответа
Заранее спасибо как всегда
Я занимался исследованиями по этому вопросу некоторое время. Многие люди рекомендуют использовать blob с одним первичным ключом в отдельной таблице и хранить метаданные blobs в другой таблице с внешним ключом в таблице blob. При этом производительность будет значительно выше.
Конструкция хранилища BLOB (= TEXT) в MySQL выглядит совершенно некорректной и нелогичной. Я пару раз сталкивался с одной и той же проблемой и не смог найти какого-либо авторитетного объяснения. Самый подробный анализ, который я наконец нашел, - это сообщение 2010 года: http://www.mysqlperformanceblog.com/2010/02/09/blob-storage-in-innodb/
Общее мнение и ожидание состоит в том, что BLOB/TEXT хранятся вне основного хранилища строк (например, см. этот ответ). Это не правда, хотя. Здесь есть несколько проблем (я опираюсь на статью, приведенную выше):
Если размер элемента BLOB составляет несколько КБ, он включается непосредственно в данные строки. Следовательно, даже если вы ВЫБИРАЕТЕ только столбцы, отличные от BLOB, движок все равно должен загрузить все ваши BLOB-объекты с диска. Скажем, у вас есть 1M строк со 100 байтами данных без BLOB-объектов и 5000 байтов данных BLOB-объектов. Вы выбираете все столбцы, не являющиеся BLOB-объектами, и ожидаете, что MySQL будет читать с диска около 100-120 байт на строку, что в целом составляет 100-120 МБ (+20 для адреса BLOB). Однако реальность такова, что MySQL хранит все BLOB-объекты в одних и тех же дисковых блоках в виде строк, поэтому все они должны читаться вместе, даже если они не используются, и поэтому размер данных, считываемых с диска, составляет около 5100 МБ = 5 ГБ - это в в 50 раз больше, чем вы ожидаете, и означает в 50 раз медленнее выполнения запроса.
Конечно, у этого дизайна есть преимущество: когда вам нужны все столбцы, включая блоб, запрос SELECT выполняется быстрее, когда капли хранятся со строкой, а не внешне: вы избегаете (иногда) 1 дополнительного доступа к странице на строку. Однако это не типичный вариант использования BLOB-объектов, и механизм DB не следует оптимизировать в этом случае. Если ваши данные настолько малы, что помещаются в ряд, и вы можете загружать их в каждом запросе, независимо от того, нужны они или нет - тогда вы бы использовали тип VARCHAR вместо BLOB/TEXT.
Даже если по какой-то причине (длинный ряд или длинный BLOB-объект) значение BLOB сохраняется извне, его 768-байтовый префикс все еще сохраняется в самой строке. Давайте возьмем предыдущий пример: у вас есть 100 байтов данных без BLOB-объектов в каждой строке, но теперь столбец BLOB-объектов содержит элементы размером 1 МБ каждый, поэтому они должны храниться извне. SELECT из столбцов без больших двоичных объектов должен будет считывать примерно 800 байт на строку (без больших двоичных объектов + префикс BLOB-объектов) вместо 100-120 - это снова в в 7 раз больше передачи диска, чем вы ожидаете, и в 7 раз медленнее выполнения запроса.
Внешнее хранилище больших двоичных объектов неэффективно при использовании дискового пространства: оно выделяет пространство в блоках по 16 КБ, и один блок не может содержать несколько элементов, поэтому, если ваши большие объекты имеют небольшой размер и занимают, например, 8 КБ каждый, фактическое выделенное пространство составляет в два раза больше, чем.
Я надеюсь, что однажды эта схема будет исправлена: MySQL будет хранить ВСЕ большие и маленькие объекты во внешнем хранилище без префиксов, сохраняемых в БД, при этом выделение внешнего хранилища будет эффективным для элементов любого размера. Прежде чем это произойдет, разделение столбцов BLOB/TEXT кажется единственным разумным решением - разделение на другую таблицу или файловую систему (каждое значение BLOB хранится в виде файла).
[ОБНОВЛЕНИЕ 2019-10-15]
Документация InnoDB теперь дает окончательный ответ на вопрос, рассмотренный выше:
https://dev.mysql.com/doc/refman/8.0/en/innodb-row-format.html
Случай хранения встроенных 768-байтовых префиксов значений BLOB/TEXT действительно относится к формату строки COMPACT. Согласно документам, "Для каждого поля переменной длины, отличного от NULL (...) внутренняя часть составляет 768 байт".
Однако вместо этого вы можете использовать ДИНАМИЧНЫЙ формат строки. С этим форматом:
"InnoDB может хранить длинные значения столбцов переменной длины (...) полностью вне страницы, причем запись кластерного индекса содержит только 20-байтовый указатель на страницу переполнения. (...) TEXT и BLOB-столбцы, размер которых меньше или равен 40 байтам, хранятся в строке. "
Здесь значение BLOB может занимать до 40 байт встроенного хранилища, что намного лучше, чем 768 байт, как в режиме COMPACT, и выглядит как более разумный подход в случае, когда вы хотите смешать BLOB и не BLOB-типы в таблице, и при этом они могут сканировать несколько строк довольно быстро. Кроме того, расширенное (более 20 байт) встроенное хранилище используется ТОЛЬКО для значений размером от 20 до 40 байт; для больших значений сохраняется только 20-байтовый указатель (без префикса), в отличие от режима КОМПАКТ. Следовательно, расширенное 40-байтовое хранилище на практике используется редко, и можно смело предположить, что средний размер встроенного хранилища составляет всего 20 байтов (или меньше, если вы склонны хранить много маленьких значений менее 20 Б в своем BLOB). В общем, кажется, что формат строки DYNAMIC, а не COMPACT, должен быть выбором по умолчанию в большинстве случаев для достижения хорошей предсказуемой производительности BLOB-столбцов в InnoDB.
Пример того, как проверить фактическую физическую память в InnoDB, можно найти здесь:
https://dba.stackexchange.com/a/210430/177276
Что касается MyISAM, то он, очевидно, НЕ вообще обеспечивает стороннее хранилище для больших двоичных объектов (просто встроенное). Проверьте здесь для получения дополнительной информации:
Добавление составного индекса в двух соответствующих столбцах должно позволять выполнять эти запросы без прямого доступа к данным таблицы.
CREATE INDEX `IX_score_status` ON `completed_tests` (`score`, `status`);
Если вы можете переключиться на MariaDB, вы можете максимально использовать оптимизацию устранения таблицы. Это позволит вам разбить поле BLOB на свою собственную таблицу и использовать представление для воссоздания существующей структуры таблицы с помощью LEFT JOIN. Таким образом, он будет получать доступ только к данным BLOB, если это явно требуется для выполняемого запроса.
Просто добавьте индекс или индексы к полям, используемым после запроса WHERE для таблицы с BLOB-объектами.
например У вас есть 2 таблицы с этими полями
users : USERID, NAME, ...
userphotos : BLOBID, BLOB, USERNO, ...
select * from userphotos where USERNO=123456;
Нормально это работает нормально. Если у вас много больших изображений (например, BLOB, MEDIUMBLOB или LONGBLOB объемом более 5 ГБ), это займет много времени (более минут), пока BLOBID является первичным ключом.
Каким-то образом MySQL ищет целые данные, включая изображения, если в предложении WHERE нет индекса поля поля BLOB. Когда ваши данные становятся все больше и больше, это занимает много времени. Если вы создадите индекс для поля USERNO, это ускорит вашу базу данных и будет зависеть от размера целых данных.
Решение:
**Add Index to the USERNO at userphotos**
В качестве ответа на свой вопрос вы должны создать индекс для ct.status