Работа с большими таблицами.

[mexmatius]

Работа с большими таблицами.

Добрый день.
Необходимость привела к работе с таблицами размерами от 10 тыс строк до 7-8 млн.
Все поля постоянной длинны (CHAR,INT), все служебные поля - уникальны, проиндексированы и т.п. В принципе поиск по отдельным таблицам проходит быстро...
Однако, необходимо между такими таблицами довольно частенько выполнять запросы LEFT JOIN. Так вот, с горечью, иногда наблюдаю полное зависание MySQL сервера. Разобъешь запрос на два, с промежуточным хранением данных работает, целый - нет. Если нужен пример, то вот он:
две таблицы - "A"( 6 млн.) . и B (10 тыс зап.)
A: A1;A2;A3. B:B1,B2.
UPDATE A LEFT JOIN B ON A1 = B1 set A1 = B2 WHERE B2 IS NOT NULL.
На рабочей машине запрос прошел.. На сервере - завис полностью и повесил БД... Версии Mysql разные (раб. - 4.1. сервер - 4.0.23). Думаю не стоит грешить на старость версии и т.п (ведь люди и с 3.* когда то работали...).
Плачевность ситуации - после зависания БД, портятся иногда данные и т.п. Как застраховаться от этого? Ведь таблицы только растут а уже такие проблемы. Не копировать же их перед каждым запросом? Где вычитать соответствующие настройки? ХОСТЕР - НЕКОМПЕТЕНТЕН!

 

[chira]

для приведённого запроса точно нужен LEFT JOIN?
много ли записей удовлетворяют условию A1=B1?
если часто нужно обновлять большое количество записей, может стоит подумать над другим алгоритмом?

 

[Steamroller]

Очень похоже, что он не нужен, т. е. если не находится строка с нужным значением B1, то B2 будет null.

 

[ONK]

Очень интересно, кто мне объяснит суть этого запроса?

UPDATE A LEFT JOIN B ON A1 = B1 set A1 = B2 WHERE B2 IS NOT NULL

Особенно впечатляет первавя половина.

 

[Роберт]

> Думаю не стоит грешить на старость версии и т.п (ведь люди и с 3.* когда то...
Был у меня случай , надо было найти ID последних товаров в таблице из 6 тысячь записей по последней дате поставки... Подумал , вроде не сложно , и составил запрос типа:
select ... from Baza
where x in (select ... from Baza where y in (select ... from Baza))
Запустил и прождав примерно пол часа убил процесс который всё это время загружал мой Pentium4 2800MHz на 100%.
Запустил отдельно третий селект (select ... from Baza) - он выполнился за пол секунды и выдал 40 значений.
Вписал эти 40 значений во второй селескт:
select ... from Baza where y in (245,158,856, ... ,321))
и запрос опять же отработал за неполную секунду.
А когда запустил второй селект вместе с третьим (без первого):
select ... from Baza where y in (select ... from Baza)
ждал более 7 минут пока он выполнился. Причём все поля под индексами были. Я не понял такого поведения базы данных , и просто придумал другой алгоритм (по отдельности запускал 3 запроса , и полученные результаты подставлял в седующий). В результате на выборку уходила где-то секунда. Это всё было на пол года назад на последнем тогдашнем релизе MySQL 4.1 , а позже когда вышла стабильная MySQL 5 - я попробывал старый трёх-селектовый запрос на новой базе и он выполнился за 15 секунд...
Эта история не даст тебе ответа на твой вопрос. Просто я склонен согласиться с chira , который посоветовал придумать другой алгоритм.

 

[mexmatius]

Спасибо за ответы. Но здесь как раз и приведен новый алгоритм. На самом деле нужно изменить одно поле в таблице A и поставить новое (из B) соответствующее тому же товару , но в таблице B. Можно было бы соединить обе таблицы по двум полям (A2,A3) и аналогичным из (B3,B4 - я их не указал) - но этот запрос оч. долго выполняется, поэтому я добавил в B - B2 (ключ INT из A) и стыковал уже по нему а не по A2=B3(INT) AND A3 = B4 (CHAR). Все строки из B есть в A. Часто наблюдаю туже картину что и Роберт. Кстати, давайте поподробнее опишу ситуацию. Есть таблица товаров (ID (A1), ID_PR (A2) (ид - производителя), A3 (артикул, номер товара). Таблица B содержит изначально три аналогичных поля , описывающих товары, которые содержатся в A, НО c другими ID. Таким образом мне нужно обновить ID в A и поставить их равными из B. C этой задачей я решил справиться запросом UPDATE A LEFT JOIN B (по двум полям) - не проходит , зависает и т.п. Тогда я добавил в B поле ID_A и схожим запросом , но уже B LEFT JOIN A (т.е. как понимаю стукуются к 10 тыс. строкам, а не к 6 млн. как в первом) и этот запрос выполнился. Таким образом поля описывающие товар мы можем забыть. Есть просто ОДНО поле A и два в B (одно из которых связывающее).
Для Steamroller - B2 никогда не будет NULL в данном запросе (см, концовку запроса is NOT NULL).

 

[chira]

mexmatius
зачем вообще нужна таблица B?
это какой-то буфер для загрузки пакетных данных?

 

[mexmatius]

Это исправление ошибки, которая была допущена в базе. Т.е. необходимо переприсвоить ID.

-~{}~ 06.11.05 16:24:

Сейчас столкнулся со след. проблемой :
UPDATE IGNORE A LEFT JOIN B ... прерывается при нарушении уникальности в A (DUPLICATE ENTRY и т.д.). Довольно странно для меня, ведь установлен атрибут IGNORE. Кстати, применив Explain к схожему запросу, но SELECT увидел что mysql использует нужный индекс. Поиск по документации ни к чему не привел...

 

[Steamroller]

Для Steamroller - B2 никогда не будет NULL в данном запросе (см, концовку запроса is NOT NULL).

Я про это и говорил как раз.
Указывая LEFT JOIN ты просишь базу для всех строк А при отсутствии нужной строки в B - заполнить NULL'ами. И дальше в условии WHERE ты все эти строки отфильтровываешь. Зачем тогда вообще делать LEFT JOIN? Тут вполне хватит INNER JOIN, который быстрее.