Скорость MySQL

посмотрел у себя на сайтах логи запросов к базе и заметил инетресную закономерность:
запросы типа UPDATE и REPLACE занимают кучу времени. Буквально в десятки раз превышают обычные выборки из огромных таблиц

С чем это может быть связано?
Может это связано с тем, что когда я делаю UPDATE, то в условии ставлю указание на поле, которое не является индексом?

Вообще плохо понимаю суть и смысл этих индексов

Кто в курсе - растолкуйте, плиз

=ZeD= писал:

посмотрел у себя на сайтах логи запросов к базе и заметил инетресную закономерность: запросы типа UPDATE и REPLACE занимают кучу времени. Буквально в десятки раз превышают обычные выборки из огромных таблиц С чем это может быть связано?

Связано это собственно с MySQL-ной блокировкой. У тебя MySQL 3-ей ветки, чтоли? Или у тебя база до сих пор в MyIMAP?

Dragon писал:

Связано это собственно с MySQL-ной блокировкой. У тебя MySQL 3-ей ветки, чтоли? Или у тебя база до сих пор в MyIMAP?

вы все врёте!(с)
мускуль 4.1.14, MyISAM

не понимаю, что тебя удивляет.
попробуй понять физический смысл операции.

операция чтения (SELECT, etc):
1. скомпилить SQL
2. если есть индекс, то найти подходящий индекс
3. пойти в индекс, взять смешение
4. пойти в указанное место и прочитать данные

операции записи (UPDATE,etc.)
1. скомпилить SQL
2. найти место куда же мы всетаки писать-то будем (т.е. повторить операции 2-3 из селекта)
3. обновить индексы (!) - вот это самый трындец
4. посмотреть как там с локами и пр., дождаться в случае их наличия
5. записать данные.

иногда еще спрашивают почему update работает медленее чем insert (казалось бы, вспоминая времена дбф-ов, он должен "раздвигать" файл). так вот ничего insert не раздвигает, а просто пишет в конец и обновляет индекс (естественно зависит от БД, но мускуль - это хорошая БД). а вот в случае update, да еще в случае использования var полей (когда размер одной записи не известен заранее) - вот тут ваще свистопляски начинаются.

P.S.
так что меня лично не удивляет это ни капельки.

окей
а какие тогда бывают методы (может косвенные) оптимизации запросов серии UPDATE?

=ZeD= писал:

окей а какие тогда бывают методы (может косвенные) оптимизации запросов серии UPDATE?

первое самое простое, в случае _частых_ апдейтов базы - отказаться от использования полей TEXT и var*
второе - выполнять операции update не по одной, а по возможности пачкой. но тут логику программы уже менять надо.
ну и структуру базы перерабатывать. использование лукап-тейблов и форейн-кеев (не поймите меня буквально, я знаю что FK могут быть только в максдб, я имел ввиду "использовать идеологию FK").

ладно, пойду эксперементировать и анализировать
Всем спасибо

Вообще апдейты и инсерты тормозят когда в таблице большие индексы или их много (каждый апдейт/инсерт = перестройка индексов). По этому индексы надо делать умно и по надобности.

Возьмем скажем запрос
UPDATE table SET field='value' WHERE a='1' AND b='2' AND c='3'

Во-первых, если стоит по индексу на a,b,c - то все равно будет тормозить. В этом случае надо делать сложный индекс по трем колонкам:

ALTER TABLE `table` ADD INDEX `idx1` ( `a` , `b` , `c` )

При чем именно в той последовательности, в которой эти колонки идут в запросе после WHERE. В таком случае индекс будет эффективен.

Вся фишка в том, что в одном запросе одной таблицы может быть использован только один индекс, принадлежащий оной таблице. Т.е. три индекса мускуль не схавает. Он возьмет один из них, а для остальных будет "using temporary".

Вышеупомянутый сложный индекс idx1 так же прокатит и для запроса:

UPDATE table SET field='value' WHERE a='1' AND b='2'

А так же и для

UPDATE table SET field='value' WHERE a='1'

Но он не прокатит следующих запросов, т.к. последовательность полей нарушается:

UPDATE table SET field='value' WHERE a='1' AND c='3' AND b='2'
UPDATE table SET field='value' WHERE b='1'

Все вышеперечисленное касается и простых (без JOIN) запросов SELECT. Индекс используется в WHERE clause и ORDER BY clause.

[INNER|LEFT|RIGHT] JOIN - это уже другая тема для разговора.

Вообще, индексы делаются под конкретные потенциально-тормозящие запросы.

Исключением является установка индекса на FOREIGHN KEY в случае джойна таблиц. Это святое

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

Нехорошо это дропать индексы. Не пробовал, но жопой чувствую что это через жопу
Тогда уж лучше собрать в батч и юзать UPDATE LOW_PRIORITY и INSERT [LOW_PRIORITY | DELAYED]

Верно, нужно собирать бач.
Вообще, крайне мало задач требует апдейта именно в режиме "right now". Всегда можно придумать некое статическое приближение, когда даные будут закидываться в базу пакетно.

Simplex писал:

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

/agree

=ZeD= писал:

вы все врёте!(с) мускуль 4.1.14, MyISAM

Переводи базы в InnoDB. Там при апдейте лочится только запись, а не база целиком.

Dragon писал:

Переводи базы в InnoDB. Там при апдейте лочится только запись, а не база целиком.

отказать!
произвдительность (общая) упадет раз в 10

JpS писал:

отказать! произвдительность (общая) упадет раз в 10

Ну вот еще. Факты давай.

Dragon писал:

Ну вот еще. Факты давай. :)

пробуйте ;)

JpS писал:

пробуйте ;)

В случае "корпоративной" базы, где практически одни селекты - возможно. В случае сиджея, когда на 2 селекта приходится один апдейт - InnoDB лучше.

интересные мысли тут были высказаны

в моем же случае идут сложные селекты (много вложеных, а также join-ы), которые и занимают большенство времени работы скрипта. Доходит до 15-20 секунд для того, чтобы построить "ТОП3 за последние 30 дней по трем показателям". Подобный запрос вообще сложно оптимизировать

=ZeD= писал:

Доходит до 15-20 секунд для того, чтобы построить "ТОП3 за последние 30 дней по трем показателям". Подобный запрос вообще сложно оптимизировать

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

Simplex писал:

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

т.е. делать, например, один раз в день апдейт?

а это идея...

=ZeD= писал:

т.е. делать, например, один раз в день апдейт? а это идея...

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

Если селекты идут по 20 секунд, значит кривые запросы. Мускуль обладает многими средствами оптимизации объединения, джоинов и т.п. У меня сливка таблиц с более чем 1 000 000 записей в сумме и вложенным селектом из одной из этих таблиц занимается 0,2 сек, хотя сходу тоже было порядка 20 сек. Тут без понимания процесса никак.

Sokira писал:

Если селекты идут по 20 секунд, значит кривые запросы

+1

запрос с LIKE '%часть слова%' тоже будет 0,2 секунды ?
Пока запроса нет, нет и смысла пинать

А если при запросе еще и регеспы, работа со строками используется, хрен там даже за пару минут может успеть сделать.