Леонид Каганов: дневник

По советам умных людей перевез базы сайта с MyISAM на InnoDB, написав себе для этого всяких новых кнопок в админке баз движка (которая уже немного по функционалу приближается к PhpMyAdmin :). И немного уже пожалел, что переехал на InnoDB.

Да, проблема инкрементального бэкапа решилась, он стал умнее: /backup 1450M /inc 3701042
Я правда всё равно не очень понимаю, зачем мне прилетели 3,7 мегабайта данных всякий раз, когда в базе не изменилось ровным счетом ничего... Но это уже лучше, чем тупо копировать полтора гига. В любом случае я случайно нашел в коде движка собственный экспорт баз в своем формате (оказывается, я это делал когда-то), чуть подправил его, и теперь думаю делать бэкап баз средствами движка, потому что это точно будет умнее и компактнее. Например, то, что у MariaDB занимает 1450M, у меня в простом формате движка заняло 500M. Но дело не в том, это я как-нибудь сам сделаю, как будет время.

Проблема тут другая. У меня на сервере и раньше подтормаживал MySQL уже не первый год — сайт, как вы наверно не раз наблюдали, подвисает. А с переездом на InnoDB торможение стало таким сильным, что следующие полдня сайт вообще висел, загрузка CPU 400% и всё такое... Когда я поглядываю SHOW PROCESSLIST, вижу такое:

Из чего становится понятно, что тормозит вот этот запрос:

SELECT c.`id`,c.`unic`,c.`Name`,c.`Text`,c.`Parent`,c.`Time`,c.`whois`,c.`rul`,c.`ans`,c.`golos_plu`,c.`golos_min`,c.`scr`,c.`DateID`,c.`BRO`,c.`IPN`,
u.`capchakarma`,u.`mail`,u.`admin`,u.`openid`,u.`realname`,u.`login`,u.`img`,u.`time_reg`
FROM `dnevnik_comm` AS c
LEFT JOIN `db_unic` AS u
ON c.`unic`=u.`id`
WHERE c.`DateID`='$num'
ORDER BY c.`Time`

Это и правда самый сложный запрос в движке — формирование ленты комментариев, по крайней мере, очень частый. Он берет все комментарии из таблицы комментариев `dnevnik_comm`, относящиеся к номеру заметки $num, добавляет к ним по номеру автора его данные из таблицы посетителей `db_unic` (там этот номер называется `unic`, а тут исторически `id`), причем информации об авторе может не быть у комментариев 15-летней давности, там unic=0 Ну и сортирует по дате комментариев Time.

По индексам — у таблицы посетителей `db_unic` есть primary индекс `id`. У таблицы комментариев `dnevnik_comm` есть индексы PRIMARY `id`, `DateID` (`DateID`), `poset` (`unic`,`scr`) и `Parent` (`Parent`), который к нашей задаче сейчас не относится.

Вопрос специалистам: в этом моем запросе что-то не так? Его можно как-то оптимизировать? Или это нормально, что он выполняется долго и время от времени подвисает на длительное время?

Может, надо добавить индекс для `Time`, а иначе он ORDER BY `Time` не может толком сделать для результата (результаты-то выборки комментариев к одной заметке обычно не слишком велики)?

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

UPD: Ого, обнаружил сейчас в движке еще один запрос, начинающийся с тех же букв, только еще сложнее — он работает по адресу /comm и показывает ленту новых комметариев:

SELECT c.`id`,c.`unic`,c.`group`,c.`Name`,c.`Text`,c.`Parent`,c.`Time`,c.`whois`,c.`rul`,c.`ans`,c.`IPN`,
c.`golos_plu`,c.`golos_min`,c.`scr`,c.`DateID`,c.`BRO`,
u.`capchakarma`,u.`mail`,u.`admin`,
u.`realname`,u.`login`,u.`openid`,u.`img`,
«.($GLOBALS['admin']?»z.Access,z.num,»:'').»
z.`opt`,z.Access,z.`Date`,z.`DateDate`,z.`Header`,z.`view_counter`
FROM `dnevnik_comm` AS c
JOIN `dnevnik_zapisi` AS z
ON c.`DateID`=z.`num` " .(empty($acn)?'':" AND z.`acn`='$acn'"). "
LEFT JOIN «.$GLOBALS['db_unic'].» AS u ON c.`unic`=u.`id`
WHERE "
.($GLOBALS['admin']?»1":«z.`Access`='all' AND (c.`scr`='0' OR c.`unic`='«.$GLOBALS['unic'].»')")
.($mode=='one'?» AND c.`unic`='«.e($_GET['unic']).»'":"")
.» AND «.($ncom!='-'?»c.`Time`>'«.$lastcom.»' ORDER BY c.`Time`":«c.`Time`<'".$lastcom."' ORDER BY c.`Time` DESC")." LIMIT ".($lim+1)

Я не думаю, что кто-то пользуется лентой /comm Там 50 посетителей за все время было1, поэтому запрос редкий, и вряд-ли проблема в нем. Так навскидку-то он выглядит сильно ужаснее, потому что объединяет базу комментариев, посетителей и еще базу самих заметок `dnevnik_zapisi` (индексы для z.`Access`, z.`acn` и z.`num` в ней есть). Также, я гляжу, там используются из базы комментариев c.`scr` и c.`unic`, но у меня для этого там их общий индекс `poset` (`unic`,`scr`), вот только не уверен, что в запросе типа AND c.`scr`=0 OR c.`unic`=1 этот объединенный индекс чем-то поможет, наверно надо дополнительно продублировать его последнюю часть `scr`?