Пару вопросов про Mysql

52

Challenger

27 июля 2009, 08:51

885

Здравствуйте дорогие друзья :)

Сейчас работаю над проектом который включает в себя mysql таблицы с записями от 2 и более миллиона строк. Малейшая оптимизация и максимально правильные запросы дадут хороший прирост производительности. - поэтому решил обратиться к профессионал и людям с опытом, т.е. к Вам :)

Вопрос 1:

Возможно ли проапдейтить за один запрос большое кол-во строк, с разными ключами.

Сейчас у меня запросы выполняются в цикле так:

UPDATE table SET status=0 WHERE id=3;

UPDATE table SET status=1 WHERE id=70;

UPDATE table SET status=2 WHERE id=53;

UPDATE table SET status=0 WHERE id=50; и т.п.

Возможно ли проапдейтить эти все строки одним запросом?

Вопрос 2 - про вложенные таблицы

Есть две таблицы таблица данных и таблица сопоставлений

Таблица данных Table1 - Id первичный ключ

id | value1 | value2 | value3

Таблица сопоставления Table2 - Id первичный ключ

id | sub_id | value-

Каким оптимальным запросом можно получить 200 случайных строк из таблицы данных table1, при этом этих строк (id этой строки) не должно быть в таблицы сопоставлений table2 (поле sub_id)

Пробовал делать так:

SELECT * FROM table1 WHERE not exists (SELECT id FROM table2 WHERE sub_id=table1.id LIMIT 1) ORDER by RAND() LIMIT 200

Но такой запрос выполняется чудовищно долго :( Гораздо быстрее получить первым запросом и перебрать потом циклом второй.

Заранее спасибо за ваши ответы и комментарии:)

NS

54

nonSmoker

27 июля 2009, 09:10

#1

Challenger:
Здравствуйте дорогие друзья :)
Сейчас работаю над проектом который включает в себя mysql таблицы с записями от 2 и более миллиона строк. Малейшая оптимизация и максимально правильные запросы дадут хороший прирост производительности. - поэтому решил обратиться к профессионал и людям с опытом, т.е. к Вам :)

Вопрос 1:

Возможно ли проапдейтить эти все строки одним запросом?

можно конкатенировать и жахнуть одним запросом.

можно prepare($sql) и в цикле разные значения. если поддерэиваются транзакции то делать в одной, будет ощутимо быстрей.

Challenger:

Вопрос 2 - про вложенные таблицы
Есть две таблицы таблица данных и таблица сопоставлений

Таблица данных Table1 - Id первичный ключ
id | value1 | value2 | value3

Таблица сопоставления Table2 - Id первичный ключ
id | sub_id | value-

Каким оптимальным запросом можно получить 200 случайных строк из таблицы данных table1, при этом этих строк (id этой строки) не должно быть в таблицы сопоставлений table2 (поле sub_id)

Пробовал делать так:

SELECT * FROM table1 WHERE not exists (SELECT id FROM table2 WHERE sub_id=table1.id LIMIT 1) ORDER by RAND() LIMIT 200

Но такой запрос выполняется чудовищно долго :( Гораздо быстрее получить первым запросом и перебрать потом циклом второй.

Заранее спасибо за ваши ответы и комментарии:)

select t1.* from table1 t1 left join table2 t2 on t1.id=t2.sub_id where t2.sub_id is null order by rand() limit 200;

42

glab

27 июля 2009, 10:12

#2

По второму вопросу.

Как насчёт вложенных запросов?

SELECT * FROM table1 WHERE id NOT IN (SELECT id FROM table2) ORDER by RAND() LIMIT 200

Незнаю насколько быстрее чем EXISTS, но попробовать стоит.

J

53

jumash

27 июля 2009, 10:15

#3

по первому

UPDATE table SET field = CASE WHEN field1 = 1 THEN 3 WHEN field1 = 2 THEN 4 END WHERE field1 IN (1,2);

N

419

netwind

27 июля 2009, 13:32

#4

Займитесь сначала вторым запросом. order by rand() - плохо в любом виде, как тут не переписывай.

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

Первый запрос попробуйте скомпоновать так :

replace into table (status,id)

select 0,3

union

select 1,70

union

select 2,53

union

select 0,50;

Но это надо хорошенько упороться, чтобы увидеть тут узкое место. Я в этом сомневаюсь.

Если вы видите такие запросы висящими в show processlist, это еще не значит, что они являются причиной тормозов.

Может они ждут блокировки от другого запроса.

Кнопка вызова админа ()

eTarget 2011:Панельная дискуссия «Стратегия eTarget 2011: Круглый стол Могут ли «плохие» входящие

147

DyaDya

27 июля 2009, 13:46

#5

По первым запросам...

Ничего плохого в них нет.

http://www.mysql.ru/docs/man/Update_speed.html

Запросы UPDATE оптимизируются как запрос SELECT с дополнительными издержками на запись. Скорость записи зависит от размера обновляемых данных и количества обновляемых индексов. Индексы, которые не были изменены, обновлены не будут.

Существует и другой способ произвести операции обновления быстро: задерживать операции обновления, а потом делаеть сразу несколько обновлений. Несколько обновлений подряд выполняются намного быстрее, чем отдельные обновления если вы блокируете таблицу.

По второму запросу...

Уже дали пример классического решения с LEFT JOIN - это, пожалуй, оптимальный вариант. А вот когда лучше делать RAND(), здесь нужно анализ производительности в вашем конкретном случае и рекомендации спецов.

Обычно, лучше сделать RAND() сразу в запросе. Но бывают и исключения, в зависимости от загруженности сервера базы данных. Тогда придётся пожертвовать RAND и:

делать заранее подготовленную табличку с выборкой 200 записей. Которую просто можно время от времени обновлять.
делать рандомную сортироваку уже в PHP. Не лучшее решение. Но иногда, позволяющее сбалансировать нагрузку на сайт и сервер базы данных (особенно, когда они на разных физических серверах).

Золотая середина где-то рядом.

Выбирайте качественный хостинг (http://vashmaster.ru/informaciya/o_poleznyh_programmah/news83.php) и продвигайте сайты в СЕОПУЛЬТ (http://seopult.ru/ref.php?ref=72b5ed9561fe66a1). А на «SAPE» я в обиде :) Не упрекайте за очепятки, пишу вслепую (http://ergosolo.ru/) и также делаю сайты (http://www.vashmaster.ru/) ;)

Open AI тестирует память для ChatGPT

Переиграть и победить: как анализировать конкурентов для продвижения сайта