Здравствуйте.

Есть большая база данных MySQL
(записей до 10000, но есть большие BLOB-поля, которые нужно изменять,
поэтому база занимает на диске больше 3 Гб).

Нужно реализовать чтение всех записей таблицы
и БЫСТРОЕ изменение BLOB-поля многих записей.

ОБЯЗАТЕЛЬНОЕ ТРЕБОВАНИЕ:
Никакие компоненты для работы с БД, такие как DataSet не используются
(как здесь http://www.delphikingdom.com/asp/articles_forum.asp?ArticleID=1318 ),
поэтому метод Edit стандартных компонентов для редактирования
текущей записи использовать не получается,
то есть можно использовать MySQL.Query(...) и MySQL.ExecSQL(...).

===
Задачу я решил, но моё решение работает очень медленно:
за 12 часов обработалось примерно 10% базы данных.
И во время работы программы другие программы не могут нормально работать
с этой базой данной, так как всё тормозит
(систему нагружает MySQL - его процесс занимает почти всё процессорное время,
моя программа занимает до 5%).

Прошу предложить оптимизацию моего решения:

авторЭто Delphi?
Delphi, но вопрос по правильности SQL-запросов на изменение BLOB-поля текущей записи.

var MySQL: IMySQL;

Подключаемые файлы брал здесь:
http://www.delphikingdom.com/asp/viewitem.asp?catalogid=1318

авторMySQL.Query - Это что такое?
SQL-запрос к базе данных

авторЯ бы сделал так:

Цикл по запросу вида
SELECT CODE, NUM FROM MyTable ORDER BY CODE, NUM
до Eof.
Действительно SELECT "CODE, NUM" выдаст записи без поля BLOB.
Возможно оперативной памяти хватит.
Надо попробовать.

Но если ключевые поля не эти, то сработает ли "ORDER BY CODE, NUM"?

авторВ цикле запросы:
SELECT IMAGE FROM MyTable WHERE CODE=... AND NUM=...
UPDATE MyTable SET MyTable.IMAGE=... WHERE CODE=... AND NUM=...
Не понимаю зачем первая строка,
ведь вторая строка не использует результат первой?
Или это один запрос?

авторОбязательно должен быть индекс из полей CODE, NUM. В данном случае порядок полей в индексе не важен. Для других задач может быть важен.
Базу данных не я проектировал.
Какие в ней ключи не знаю.
Структуру базы данных изменять нельзя.

авторИмхо, у вас большая часть времени бессмысленно теряется в OFFSET-ах.
Без них никак.
Если использовать "SELECT *" без "LIMIT 10",
то программе не хватает памяти и она завершается с ошибкой

авторИ, кстати, LIMIT без ORDER BY - рулетка.
Возможно важное замечание.
Но разве записи выдаются не в порядке добавления в таблицу?

авторРискуете некоторые записи два раза обработать, а некоторые ни разу.
Это не проблема. Я могу программу 10 раз запустить.

Тем более, что после изменения записи, записи повторно быстро обрабатываются.
(После обработке обычно их размер уменьшается в разы)

автора вообще, здаёться мне лучше будет так

1)код который выгрузит нам нужные блоб поля в файлы на шдд
2)код на делфи обрабатывает эти файлы, и чтото там меняет сохраняя
Через файлы ещё медленнее.

автор3)потом идёт серия апдейтов указывая вкачестве значения поля LOAD FILE fname
Приведите пример SQL запроса с загрузкой файла в BLOB-поле.

Подозреваю, что возможны ошибки SQL или зависания,
так как моя программа отправит 1000 запросов
на изменение BLOB-полей из 1000 файлов по 1 Мб,
и что в каком порядке и когда изменится не известно.

авторесли действительно хочеться оптимизировать... то надо избежать конвертаций бинарных данных в строку и обратно, как на стороне мускла так и на стороне клиента.
Это работает быстро.
Систему нагружает MySQL - его процесс занимает почти всё процессорное время,
моя программа занимает до 5%

авторобход записей в базе для обработки, обычно делаеться либо курсором либо по типу описано выше.
Как это?

авторибо
не будете же вы
ни молиться чтоб никто в базу ничего не вставил /не удалил
ни блокировать всю базу на время этой обработки
Я программу на ночь запустить могу, когда никто с базой данных не работает.

Проблема в том, что сейчас программа не может успеть за ночь.

авторлюбые селекты что вы изобретаете, не дадут ожидаемый результат если появяться новые записи, или удаляться старые. либо курсор, либо снимок базы
В моём случае этого можно избежать.

авторпро файлы, я не говорю что файлы быстрее, я говорю что ему один чорт надо все сначало выгрузить, обрабатывать, потом загружать назад, а не надеяться на чесное пионерское, что за эти 10 часов никто не поменяет число записей в таблице(вставка, удаление)
Мне надо быстрее.
Можно организовать, что за эти 10 часов никто не поменяет число записей.

автора вообще меня смущает постановка вопроса... blob тип это как бы не для того чтоб брать и 10 байт в нём менять.
Меняется не 10 байт, а абсолютно все байты и длина всего BLOB.

авторавтор
Я бы сделал так:

Цикл по запросу вида
SELECT CODE, NUM FROM MyTable ORDER BY CODE, NUM
до Eof.

Действительно SELECT "CODE, NUM" выдаст записи без поля BLOB.
Но мне нужно поле BLOB , чтобы на основании его рассчитать новое значение.

А если одним SELECT получить CODE и NUM,
а вторым получать по ним IMAGE,
то не потеряется ли результат первого SELECT (если всё-таки использовать OFFSET),
даже если я для результатов первого и второго SELECT заведу разные переменные?

автороптимизировать можно исключив limit
и выбирать по одной записи, лимит может вносить существенное торможение
даже при увеличении обращений к серверу это сэкономит время
Как без "LIMIT 1" с помощью "SELECT" получить 1 запись?

И есть ли в SQL команда на изменение i-ой записи в таблице без "WHERE"?

И в какой момент данные передаются в программу:
автор
query_result := MySQL.Query('SELECT * FROM MyTable LIMIT 10 OFFSET ' + IntToStr(offset1));
или
авторquery_result.FetchRow
?

Базу данных не я проектировал.
Структуру базы данных изменять нельзя и другие изменения мне вносить нельзя.

Про ключи узнал:
PRIMARY KEY (`CODE`,`NUM`,`DATE1`).


авторНе знаю как у используемого компонента, а у нормальных Query можно ограничить размер потребляемой памяти буфером хоть на одну запись. Так что лимит сам по себе не нужен.
Программа начинала занимать 900Мб и т.д. пока "Out of memory" не появилось.

авторавторавтор
Действительно SELECT "CODE, NUM" выдаст записи без поля BLOB.
расшифруй
IMAGE - BLOB-поле, которое занимает много байт.
Если его в SELECT не ставить, то меньше информации передаётся SELECT.

Ошибки при работе появились.

Возможно нужно разные объекты MySQL для MySQL.Query
создавать для запроса
авторЦикл по запросу вида
SELECT CODE, NUM FROM MyTable ORDER BY CODE, NUM
до Eof.
и
авторВ цикле запросы:
SELECT IMAGE FROM MyTable WHERE CODE=... AND NUM=...
UPDATE MyTable SET MyTable.IMAGE=... WHERE CODE=... AND NUM=...

miksoft,
Только результаты Query разные переменные,
или и объекты для подключения к БД разные
и для каждого нужно отдельно указывать IP, Port, User, Password и делать Connect?

У меня не находит IMAGE по ID и CODE.
Может нужны кавычки где-нибудь
или индекс не правильно указал?

miksoft,
авторКстати, если движок таблиц InnoDB и автокоммит выключен, то коммит делать не забывайте.
Что такое автокоммит и как делать коммит?

Заработал поиск IMAGE без сортировки.

Теперь тормозит всё даже на моей маленькой базе с 20 записями.

А я столько кода уже переписал...

miksoft,

а индекс правильно указан:


C пробелами, запятыми и без общих кавычек?

Порядок полей важен?

Может индекс можно только по имени указывать, а не перечислением полей?

авторORDER BY - это не индекс, это указание как сортировать результат запроса. Оно может выполняться и при наличии индекса, и без него. Порядок полей был бы важен, если бы сам этот ORDER BY был важен.

В первом запросе порядок важен



Индекс автоматически задействуется, если ORDER BY указан по полям как в индексе, или нет?

А не должны ли обязательно быть в SELECT перечислены все те поля, которые есть в ORDER BY?

авторПоле DATE1 из ORDER BY уберите, все равно оно не идет в результат. Да и на сортировку не влияет.

А вот зачем тут LIMIT - не понятно.
Поле DATE1 в ORDER BY есть, так как оно есть в индексе.

LIMIT на всякий случай ставлю из Edit.
Если будет переполнение памяти, то поставлю 1 и проверю сразу же.

авторавторА не должны ли обязательно быть в SELECT перечислены все те поля, которые есть в ORDER BY?
Нет, вообще эти списки полей могут даже не пересекаться.
Но почему то не работал:

miksoft,

Забыл.

===
А LIMIT 1 не ускорит запрос, если запись всегда 1?

Ладно, на сегодня всё.

Моя программа по крайней мере у меня стала грузить процессор намного сильнее.

Завтра проверю на реальной базе.

Если что интересное получится - напишу.

Всем спасибо за помощь.

В общем после оптимизации всё стало намного медленнее работать.
1 запись изменяется по 2 минуты.
И за месяц так не обработается.


Я везде комментарии с сообщениями поставил.
Тормозит запрос



Думаю сортировка по индексу не применяется,
и тратятся ресурсы на поддержку сортировки по 3 полям,
хотя ЭТОТ ЗАПРОС ВСЕГДА ВОЗВРАЩАЕТ 1 ЗАПИСЬ (200 Кб - 3 Мб).
Наверно лучше без сортировки.

А есть ли метод "FIND FIRST" в языке SQL чтобы использовать вместо SELECT многих записей?

И какой наиболее быстрый способ загрузки BLOB-поля из файла,
с учётом, что я могу программу локально на сервере запустить?

===
Подозреваю, что есть какое-то ограничение на скорость передачи данных,
ведь все данные передаются по сетевому протоколу хоть и внутри 1 компьютера,
а интернет у нас не быстрый).

Сортировку убрал, скорость почти не изменилась.

Но когда добавил "LIMIT 1" скорость заметно возросла
(но сильно уменьшается при приближении к концу таблицы)

авторЗатем подключитесь к MySQL консольным или GUI-клиентом или PHPMyAdmin-ом и посмотрите план этого запроса. После этого показывайте нам запрос и его план.
Это как?