{imgicourl}{zamok}
<< предыдущая заметкаследующая заметка >>
21 ноября 2020
Mysql задолбало

Сервер lleo.me время от времени подтормаживает, в основном из-за Mysql. Я уже чинил всякие запросы, индексы. Вчера я страницу https://lleo.me/dnevnik/comm сильно доработал по вашим просьбам, теперь там видно, на чей коммент кто отвечал, а если на ваш, то еще и красным показывает. При этом заметил: если нажимаю ссылку «предыдущие комменты», то сервер подвисает в половине случаев уж точно. Стал разбираться и увидел сложную конструкцию. Ну, как сложную... Сильно упрощая, выглядит так:

SELECT c.*,u.*,z.*
FROM `comments` AS c
JOIN `publications` AS z ON c.`num`=z.`num`
LEFT JOIN `users` AS u ON c.`user`=u.`user`
WHERE 1 AND c.`Time`<'1605909120' ORDER BY c.`Time` DESC LIMIT 50


Логика проста: движок берет 50 комментариев от некого момента во времени, но к каждому нужна бы дополнительная информация из других таблиц. Поэтому пытается подсосать информацию о заметке, к которой комментарий относится (известен номер заметки), а также информацию об авторе комментария (известен его номер, хотя в базе может не оказаться). Все мыслимые индексы есть для всех таблиц.

Не понимаю, почему Mysql с этим нехитрым на мой взгляд вопросом так туго справляется. Короче, я психанул и сделал вместо этой конструкции просто три разных запроса. Сперва вынимаю 50 комментариев, затем информацию об их заметках (обычно 50 комментариев относятся к двум последним заметкам), затем инфо об авторах (авторов тоже раза в четыре меньше, чем комментариев). И внезапно тормозить перестало! Либо я был неверного мнения о способностях Mysql оптимизировать запросы, либо запрос был как-то совсем неверно написан с точки зрения Mysql, и выполнять оно начинало его с другого конца, производя миллиард обратных сравнений по дате комментария. Сейчас я и процедуру комментов под заметкой так же переделал — там, правда, стыковались не все три базы, а всего две (информация о заметке и так известна), и работа выполнялась не с каждым посетителем, а густо кэшировалась в memcache (в отличие от страницы /comm, изначально предполагалось, что я туда хожу один). В общем, тоже сделал два запроса.

Если будут глюки — пишите lleo@lleo.me

<< предыдущая заметка следующая заметка >>
пожаловаться на эту публикацию администрации портала
архив понравившихся мне ссылок
Оставить комментарий
Windows Firefox
 Москва
0
0
Dimonius
А какие индексы? Пока видно, что сказано "возьми все старые комментарии, до сегодняшеней даты, потом отсей те, которые не от этой публикации, потом оставшееся отсортируй по дате и добавь авторов". Естественно, что это будет выполнятся ОЧЕНЬ долго...
Linux Ubuntu Firefox
 Москва
0
0
LLeo
А есть способ написать этот запрос как-то иначе?

Собственно мне надо-то взять 50 комментариев из диапазона и отсортировать их. А затем к каждому добавить информацию из второй таблицы по
соответствующему номеру id и из третьей (если найдется) по номеру user.

Неужели наука Mysql не придумала другого способа, кроме как быдлокодить на PHP всякие отдельные запросы:
$sql=ms("SELECT * FROM comments LIMIT 50")
$R=implode( ',' , array_unique( array_column($sql,'user') ) );
$sql_1=(SELECT * FROM users WHERE `id` IN ($R));
...
А потом еще бегать по массиву $sql и расставлять в него недостающие значения?
Mac Safari
 Washington
0
0
L&M
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Леонид, добавь для начала к этому запросу в начало EXPLAIN и покажи результат.

Ради интереса можешь заодно попробовать поменять местами таблицы
SELECT FROM publications LEFT JOIN comments
Linux Ubuntu Firefox
 Москва
0
0
LLeo
Не уверен, что правильно обратился, но что-то типа такого:

Array
(
[0] => Array
(
[id] => 1
[select_type] => SIMPLE
[table] => c
[type] => range
[possible_keys] => DateID,Time
[key] => Time
[key_len] => 4
[ref] =>
[rows] => 151086
[Extra] => Using where
)

[1] => Array
(
[id] => 1
[select_type] => SIMPLE
[table] => z
[type] => eq_ref
[possible_keys] => num
[key] => num
[key_len] => 4
[ref] => dnev.c.DateID
[rows] => 1
[Extra] => Using where
)

[2] => Array
(
[id] => 1
[select_type] => SIMPLE
[table] => u
[type] => eq_ref
[possible_keys] => PRIMARY
[key] => PRIMARY
[key_len] => 4
[ref] => dnev.c.unic
[rows] => 1
[Extra] =>
)

)
Linux Safari Chrome
 Киев
0
0
diggya6
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
во первых explain select

во вторых что вы хотели от конторы, делающей *две* базы данных
Windows Safari Chrome
 Самара
1
0
jaralet2
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Кроме собственно Oracle Database и MySQL Ораклу принадлежат как минимум ещё 3 активные БД: InnoDB, BerkeleyDB и Rbd. Так что минимум 5 :o)
Windows Firefox
 Владимир
0
0
SPQR_Voldi
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Ничего удивительного, у SAPа их столько же, например.
Windows Safari Chrome
 Одесса
5
0
Сергей63
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Тоже сталкивался. Проблема с JOIN в том, что конструкцию
select from table as t join...
надо писать так:
select from (table as t) join...
иначе джойны цепляются сначала друг к другу, а потом уже к основной таблице
Linux Ubuntu Firefox
 Москва
1
0
LLeo
О, спасибо, не знал!
Safari Chrome
 Mt Laurel
0
0
Dan Bry
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Сергей прав. Как написано Optimizer начнет с конца и выгладить это будет так: в зать все из user, join on publication, потом select потом where
Linux Ubuntu Firefox
 Москва
0
0
LLeo
Ох... Спасибо, я понял.
Всё. Только чистый PHP и минимум сложных запросов! :)
В это вникать при моих скромных потребностях из трех таблицы - себе дороже.
Safari Chrome
 Mt Laurel
3
0
Dan Bry
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
It's not that complicated .... Key to join - select first dataset (define as small as possible) then select 2nd dataset (define as small as possible) and then join - will work fast. There are SQL debuggers, and they will show you query path and how many rows you are getting from each table. Sorry for english but it's easier :)
Windows Safari Chrome
 Одесса
5
0
Сергей63
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Я на этом 25 долл заработал, когда оптимизировал запросы на самописном сайте ))
Windows Safari Chrome
 Киев
1
0
Кондыбас
А можете это продемонстрировать двумя эксплейнами - так и этак?
Windows Firefox
 Москва
1
0
Dimonius
А какие индексы есть на comments (самое интересное), на publications и на и на users?
Linux Ubuntu Firefox
 Москва
0
0
LLeo
Да все, что упоминаются хоть где-то.
Linux Safari Chrome
 Washington
0
0
Гена
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
> А какие индексы есть на comments (самое интересное), на publications и на и на users?

... так Леонид узнал про индексирование..
Windows Firefox
 Москва
0
0
Dimonius
Если на comments есть индекс по time:

SELECT `c`.*, `u`.*, `p`.*
FROM `comments` as `c`
LEFT JOIN `publications` as `p` ON `p`.`num` = `c`.`num`
LEFT JOIN `users` as `u` ON `u`.`user` = `c`.`user`
WHERE `c`.`time`>='time_stamp неделя или две назад' AND `c`.`time`<='текущий timestamp'
ORDER BY `c`.`time` DESC

Каким образом мы выбираем комменты, а потом уже ДОБАВЛЯЕМ данные из топиков и юзеров. А не делаем полное связывание (как просто JOIN у вас первоначально) всей выборки и только потом фильтр...

При этом в любом случае сильно уменьшаем количество подходящих записей в comments - меньше памяти, больше скорость.
Linux Ubuntu Firefox
 Москва
0
0
LLeo
Ну вот выше пишут, что MySQL пойдет набирать из других таблиц сперва...
Строгое ограничение "две недели" - это немного за пределами логики движка. А если движок мало комментируемый и посетитель заходит раз в месяц, ему не покажет всю пачку последних комментариев только потому, что они были три недели назад и отвалились по искусственно принятому условию?
Linux Ubuntu Firefox
 Россия
0
0
папа карло
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
если движок мало комментируемый, то у него в базе нет столько данных, чтобы возникла та проблема, которую мы сегодня наблюдаем. в этом случае у него должны быть свои "две недели", которых с одной стороны достаточно чтобы набиралось 50 комментариев, с другой стороны чтобы не пришлось сканировать и джойнить слишком много строк.
Linux Safari Chrome
 Москва
0
0
LLeo
Почему, данных там предостаточно. Я же не создаю на binoniq 500 таблиц для каждого из пользователей, таблица общая и может быть громадной, только номер acn конкретного малопосещаемого журнала отдельный. И тогда это все равно вопросы выборки.
Windows Safari Chrome
 Киев
0
0
Кондыбас
Не пойдет, ерунда там написана.
Linux Safari Chrome
 Москва
0
0
LLeo
Я теперь вообще думаю: может мне быстрее будет сделать 100 отдельных запросов по каждому юзеру с его дополнительными данными? Тогда каждый из них надолго ляжет в memcache и впредь будет исполняться мгновенно, без обращения к MySQL даже...
Windows Safari Chrome
 Киев
2
0
Кондыбас
У мемкеша внутри скиплист вместо б-трее. Те же яйца, только в аксонометрии. Учитывая, что мускль давно умеет держать БД в ОЗУ, выигрыша особого не ожидаю.
Windows Firefox
 Москва
0
0
Dimonius
А тормозят то не юзеры, их то как раз по LEFT JOIN потом быстро прикрепить (при услоавии что на users.user стоит PRIMARY ключ или хотя бы какой-то индекс)
Windows Firefox
 Москва
0
1
Dimonius
Не, в моём запросе не пойдёт, так как LEFT/RIGHT JOIN выполняются ПОСЛЕ основного запроса. А вот FULL OUTER JOIN он же просто JOIN или FROM `table1`, `table2` WHERE... и тд - там да, будет выбирать из всех этих таблиц одновременно, а потом пытаться скрестить.

Я тест сделал, план запроса посмотрел. Насчёт ограничения на 2 недели да, достаточно плохой момент, но он очень сильно оптимизирует количество просмотренных строк.

Опять же, можно сдвигать окно даты в зависимости от последней даты в комментах, что получить очень просто

Опять же, если из php вызывать функцию получения с параметрами типа from / till и если не набралось нужное количество, сдвигать окно в более старые даты.
Windows Firefox
 Новая Зеландия
1
0
Torvin
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
«In MySQL, JOIN, CROSS JOIN, and INNER JOIN are syntactic equivalents» https://dev.mysql.com/doc/refman/8.0/en/join.html

откуда вы взяли что JOIN == FULL OUTER JOIN?
Mac Safari Chrome
 Москва
0
0
dnevnik
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
А временные таблицы в mysql есть? (вроде погуглил - есть).
С ними все также, как вы сделали - работа с отдельными таблицами. А вот дальше по другому - положили ее во временную, проиндексировали уже ее по нужным в дальнейших соединениях / отборах полям.

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

Т.к. запрос вы строите динамически на php, то всегда сможет создать нужные ВТ-ки и индексы на них.
Mac Safari Chrome
 Москва
0
0
dnevnik
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
https://riptutorial.com/ru/mysql/example/20331/создать-временную-таблицу

как я понял, в mysql они еще могут выступать чем-то вроде кэша, т.к. могут повторно использоваться клиентом, т.к. уничтожаются только при завершении сеанса или закрытии соединения (ну или принудительно).

Но требуется MySQL версии 3.23 и выше.
Mac Safari Chrome
 Германия
0
0
karash_l
> Но требуется MySQL версии 3.23 и выше.

С подключением вас. MySQL 3.23 вышла в 2001 году.

https://web.archive.org/web/20010815175526/http://www.mysql.[...]
Mac Safari Chrome
 Москва
0
0
dnevnik
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Я в своей работе с MySQL не сталкиваюсь, поэтому как там что и когда - не в курсе.

Запомнилось только в июньском обсуждении про переход lleo c MyISAM на InnoDB, что MySQL (MyISAM) не поддерживал транзакции. Поэтому, всего можно ожидать от этой чудо-sql.
Linux Ubuntu Firefox
 Россия
0
0
папа карло
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
способность mysql оптимизировать свою деятельность не надо переоценивать. надо смотреть explain, там будет и порядок джойнов и используемые индексы.

написанный вами на клиенте код делает немного другой запрос, а именно
elect *
from (select * from comments order by time desc limit 50) as c
join publications
left join users
и вообще говоря ответ может отличаться от первого, в зависимости от организации связей comments с publications и users. в вашем случае эти запросы эквивалентны потому что связь n-1 и n-(0..1), но если сервер в этом не уверен, то он не может переписать запрос таким образом (выполнить pushdown order by+limit в подзапрос). или в нем может быть в принципе не поддержана такая оптимизация.

без фильтра по юзеру, публикации это "глобальный" запрос по всей таблице, а точнее по трем таблицам, которые все могут быть "большие", т.е. расти с временем, и фильтр по дате здесь не помогает, т.к. оставляет всю историю. т.е. это потенциально тяжелая конструкция, которая может рвануть, может нет, начиная с некоторого объема. вам повезло: база выросла настолько, что это начало иметь значение.
Linux Ubuntu Firefox
 Москва
0
0
LLeo
В итоге вы бы мне что посоветовали - оставить три запроса из PHP и не париться (особенно если включить долгий кэш для редко меняющихся таблиц), либо все-таки пытаться сконструировать более толковый сложный запрос в MySQL?

Результат, понятное дело, может отличаться, поскольку там запрашивается 50 комментов на страницу, а по итогам двух других запросов из них может часть отвалиться (например, комментарии оказались к закрытой пока заметке, и их нельзя показывать всем, только френдам и т.п. - на самом деле там много всяких условий). При едином запросе коммментариев бы выбиралось все равно ровно 50. Не то, чтоб в моем случае это было важно, просто разница очевидна.
Linux Ubuntu Firefox
 Россия
0
1
папа карло
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
я бы посмотрел скорость выполнения запроса
SELECT c.*,u.*,z.*
FROM (SELECT * FROM `comments` WHERE `Time`<'1605909120' ORDER BY `Time` DESC LIMIT 50) AS c
JOIN `publications` AS z ON c.`num`=z.`num`
LEFT JOIN `users` AS u ON c.`user`=u.`user`
WHERE 1 --AND c.`Time`<'1605909120' ORDER BY c.`Time` DESC LIMIT 50
и выдает ли он 50 строк.
если это не дает ускорения, то работающий код, делающий то же самое более сложным но оптимальным способом, у вас уже есть, видимо придется остановиться на таком варианте. судя по explain, оригинальный запрос джойнит всё в разумном порядке, но это не помогает, возможно из-за limit. какая у вас версия mysql?

не имеет отношения к обсуждаемому вопросу, но. у вас в запросе написано Time < '1605909120', но сам Time при этом не строковый же тип, я надеюсь?
Linux Ubuntu Firefox
 Москва
0
0
LLeo
В двух местах DESC LIMIT 50 писать?
Mysql у меня MariaDB, версию не помню, но достаточно свежая.
Time у меня, если не ошибаюсь, исторически INT(11) UNSIGNED, с ней во многих случаях работать удобнее и быстрее. Не строка конечно, но и не TIMESTAMP во многих таблицах.
Firefox
 Киев
0
0
LintruderX
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
В конце limit 50 можно не писать, а вот order by ... desc, я бы оставил вконце ибо "порядок строк не гарантируется без явного его указания"
Windows Safari Chrome
 Киев
0
0
Кондыбас
Покажите SHOW CREATE TABLE xxxxx; для всех таблиц, задействованых в запросе, и сам запрос полностью, без сокращений.
Linux Ubuntu Firefox
 Москва
1
0
LLeo
Мне бы не хотелось тратить зря ваше время, таблицы достаточно большие и сложные. Сам запрос тоже показать сложно - это гигантская простыня кода, в которую на PHP добавляет разные условия в зависимости от фазы луны, номера посетителя, флажков доступности к разным видам контента и так далее. Я показал самый простой случай для админа, когда никаких условий нет, но все равно создавалась нереальная нагрузка.

Мне уже понятно, что правильней было сделать в три запроса, как я сделал в итоге (да и как было когда-то, лет 15 назад, честно говоря).
Windows Safari Chrome
 Киев
3
0
Кондыбас
Наше дело предложить.

Мне, вообще, за это деньги платят :) Причем, не за фулл-стек, а очень узкопрофильно за ДБА - именно мускля/марии.

Запросы посмотреть несложно. Сделайте
SET GLOBAL slow_query_log 1;
SET GLOBAL long_query_time=5.0;

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

Если вдруг передумаете, мыло мое у вас есть.
Mac Safari Chrome
 Германия
0
0
karash_l
Я не вижу в этом никакой проблемы. Иногда проще прагматичный подход "в лоб", который намного понятней разработчику и работает без проблем.
Императивный код (PHP) зачастую намного более понятней всяких декларативных (SQL).
Да, наверняка можно надеть штаны через голову, перелопатить структуры таблиц, понагородить кучу индексов и всё это будет работать с одним запросом. Но скорее всего это будет потом сложнее для понимания и работать всего на 0.37% быстрее. И два с половиной задрота будут удовлетворены решением задачи "как в учебнике".
Linux Ubuntu Firefox
 Россия
0
0
папа карло
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
еще одна идея: возможно, вам не обязательно нужны 50 комментариев за произвольный период в прошлое, а достаточно 50 комментариев, но не старше чем на неделю(день, три дня). тогда можно в исходный запрос добавить фильтр and c.`Time` >= '1605304320' и это должно улучшить его характеристики.
Linux Ubuntu Firefox
 Москва
0
0
LLeo
Ну это же скрипт постраничного листания.
Есть у меня, скажем, аккаунт lleo.binoniq.net, я там сто лет не писал, и комментарии там приходили редко, ну допустим последний был в марте, и я его так пока и не видел. И вот я решу зайти в /comm и полистать, какие были. И что? На первой странице он мне не покажет ничего. По кнопке "предыдущие 50 штук" опять не покажет ничего. И так далее, не долистать :)
Linux Ubuntu Firefox
 Россия
0
0
папа карло
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
то есть вам надо не просто последние 50 комментариев, а честное листание в прошлое, сопряженное с примененим acl и возможно какой-то дополнительной логики. тогда вам действительно не поможет ни ограничение по дате снизу, ни подзапрос с лимитом, т.к. подзапросы с лимитами не будут хорошо коммутировать с дополнительными условиями на джойн. подзапрос даже будет время от времени выдавать какие-то комментарии, но их количество будет непредсказуемым.
в этом случае не очень понятно, как у вас получилось требуемое поведение реализовать на PHP, разве что вы ходите в базу за новыми и новыми кандидатам на комментарии, пока не заполните для выдачи очередную страницу в 50 штук.

кстати, вам на lleo.aha.ru это возможно не пригодится, но если этот же движок используется для нескольких сотен binoniq аккаунтов, каждый из которых занимает не очень большую долю в единой базе, и если работа с данными для каждого аккаунта происходит в запросах отдельно, то добавление в начало ключей account_id и фильтрации по нему при всех соответствующих действиях могло бы что-то людям поускорять. например, индекс в виде (account_id, publication_id) для публикации, (account_id, publication_id, comment_id) для комментария, итд. и потом при чтении данных добавлять во все возможные места account_id = 'lleo' and %остальное%. одному жирному аккаунту на всю базу это конечно не поможет.
Linux Safari Chrome
 Москва
0
0
LLeo
В принципе так и сделано. AccountID называется 'acn', а так все как вы написали.
Windows Safari Chrome
 Киев
0
0
Кондыбас
Для `comments` нужен составной индекс (num,user,time).

Конструкция WHERE 1 AND ... хоть и корректна синтаксически-семантически, и даже парсер-оптимайзер ее почистит при генерации бинкода, но в эстетическом плане это даже хуже, чем рифма "кровь-любовь".
Linux Ubuntu Firefox
 Москва
0
0
LLeo
Тогда придется много составных индексов делать, потому что разные запросы, это только один из вариантов для страницы /comm

WHERE 1 AND выглядит глупо, но это потому, что я делал запрос как админ, который обязан видеть все комментарии. В реальности у посетителя вместо этого наматывается куча разных условий, какие можно показывать комментарии: к закрытым заметкам нельзя, скрытые комметарии не показывать, но если это был твой комментарий или ответ на твой, то показывать даже если он скрытый, и так далее. По каким причинам мне лет 10 назад было проще оставить WHERE, но дописывать 1 AND, я уже сейчас не вспомню. Это обычно в пределах одной таблицы и мало влияет на общую проблему - я-то получал перегруз системы даже без дополнительных условий.
Linux Ubuntu Firefox
 Россия
1
0
папа карло
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
where 1 удобно для запросов генерируемых строками из кода, т.к. можно несколько раз написать query +" and %cond%" в любом порядке и не думать, первый это фрагмент или нет. особенно полезно, когда фрагменты опциональны.

"без дополнительных условий" может оказываться как раз вырожденным случаем, который тормозит только у вас, а у всех остальных за счет этих условий падает кардинальность промежуточных результатов и запрос отрабатывает быстрее. хотя, т.к. в целом у вас система достаточно открытая, то это вряд ли.
Windows Safari Chrome
 Киев
0
0
Кондыбас
Да, увы, всякому достаточно сложному запросу нужен свой собственный индекс со своим, персональным порядком полей.

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

Общее правило таково:
Все поля таблицы, используемые в запросе для
-- JOIN .. ON
-- WHERE
-- ORDER BY
должны быть компонентами составного индекса. Поля должны включаться в индекс в порядке убывания селективности. ID юзера более селективен, чем его пол, поэтому (id, gender), а не (gender,id). Иногда поля с низкой селективностью можно вообще не включать в индекс, но если в базе меньше 10М записей, то лучше не жлобиться.
Linux Ubuntu Firefox
 Москва
0
0
LLeo
Ну тогда это вообще не мой случай.
У меня там куча разных сравнений бывает добавляется к запросу всякий раз в зависимости от группы посетителя, открытости комментариев, доступности заметки, авторства комментария и так далее. Создать 30 разных составных индексов на каждый из возможных разновидностей запроса нереально.
Linux Safari Chrome
 Россия
0
0
edo1
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
так не надо на каждый, надо на на самые востребованные, с учётом кардинальности полей и всё такое.

> Все мыслимые индексы есть для всех таблиц.

а ничего хорошего в этом нет, mysql может выбрать не самый подходящий индекс и выполнение запроса замедлится на порядки
Windows Safari Chrome
 Обнинск
0
0
Wgent.com
LLeo, отсеки большую часть таблицы по одному из ключей. Например, по id, но можно и по времени.
У тебя же там, небось, многолетние залежи копролитамментариев. Вычисли приблизительно, за какое время в среднем набигаютъ 50 комментов, удесятери этот период и добавь в WHERE AND c.`Time`>=(1605909120-ЭТОТСАМЫЙПЕРИОД).
У меня, например, новостной сайт с ~30K новостей за 15 лет. Так что я сделал: весь сайт работает с AND id>25000, и только в специальном и мало кем посещаемом разделе "архив" этого ограничения нет. В результате скорость выборки новостей по {тэгу,геотэгу,рубрике,автору} резко возросла!
Ну или если у тебя там полная постраничная история, то пусть это ограничение отключается с какой-то там страницы, не вижу особой проблемы.
Linux Safari Chrome
 Russia Moscowa Tverskaya-Yamskaya1
0
1
Johnyуууу
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?

http://lleo.me/dnevnik/user/8512244/330268.jpg

Linux Safari Chrome
 Москва
0
0
telepatus
Да я просто браузер тестирую на вашем сайте. А для этого нужна картинка.
Увы загрузка картинок иногда барахлит до сих пор.
А на видео с Ютуба у вас даже сослаться нельзя. Какой-то ужас поститься вместо ссылки.
Linux Ubuntu Firefox
 Нижний Новгород
0
0
rekcuFniarB
Оптимизатор иногда тупит, иногда помогает добавление STRAIGHT_JOIN после SELECT (SELECT STRAIGHT_JOIN ...), чтобы джойнил в указанном порядке. Ну или не джойнить вообще тоже вариант.
Linux Ubuntu Firefox
 Великобритания
0
0
Пользователь с таким именем
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Леонид, это классическая классика.

Проблема в том, что условие WHERE 1 AND c.`Time`<'1605909120' ORDER BY c.`Time` DESC LIMIT 50

отрабатывает _после_ джойна. То есть, этот гад сначала делает джойн на всю таблицу, а потом оставляет 50 самых новых строчек. надо сделать условие в подзапросе, что-то в духе нижеприведенного (прости за возможные синтаксические ошибки, сейчас не на чем проверить).

select c.*, u.*, z.* from (select * from comments where `Time`<'1605909120' ORDER BY c.`Time` DESC LIMIT 50) AS c JOIN `publications` AS z ON c.`num`=z.`num`
LEFT JOIN `users` AS u ON c.`user`=u.`user`;
Mac Safari Chrome
 Москва
0
0
dnevnik
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Чего-то komm в итоге совсем работать перестал, показывает только это, на 4 команды со всех боков про 70-й год не реагирует.

https://lleo.me/dnevnik/user/4833413/330279.png

Mac Safari Chrome
 Москва
0
0
dnevnik
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
А при попытке запостить этот скриншот, в начале выдал таймаут-еррор и дал отправить только со 2-го раза.

Да и вот этот коммент, без картинки, как-то подозрительно долго отправлял.
Mac Safari Chrome
 Москва
0
0
dnevnik
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Заработало вроде.
Linux Ubuntu Firefox
 Москва
1
0
LLeo
Я там нашел небольшую ошибку, даже две, правил.

array_merge в PHP должен соединять несколько массивов, но если один из них вдруг не массив (не нашлось в базе доп.параметров и ответ false), то обнуляется весь результат, несмотря на остальные массивы.

Ну а также у меня дата старта считалась по 0 элементу массива, а теперь нулевого коммента может не быть, не прошел контроль, а массив начинается например с 3-го. Поэтому приходится ключи массива обнулять $r=array_values($r);
Windows Safari Chrome
 Иваново
0
1
aakhamef
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Несколько запросов вместо одного -- не всегда плохо. А иногда даже хорошо. И я бы посоветовал какой-нибудь ОРМ. Проблема версионирования и поддержки решается клонированием репозитория. А можно написать свой лисапед (натырив кода, например)
Linux Ubuntu Firefox
 Москва
1
0
LLeo
Что такое ОРМ для Mysql?
Windows Safari Chrome
 Санкт-Петербург
0
1
bolshakovdmitry
Linux Safari Chrome
 Москва
1
0
LLeo
Не, не у меня ровно 20 лет движок работает с MySQL. А этому фреймворку сколько лет и ещё через сколько он рассчитывает закрыться?
Mac Safari Chrome
 Германия
0
0
karash_l
Прошу вас, даже на милисекунду не пытайтесь подумать, что ORM решит вашу задачу, указанную в посте.
ORM решит что угодно, но только не кастомную оптимизацию запроса.
Если вам захочется поговорить об ORM, то создайте отдельный пост, который не будет иметь ничего общего с этим.
Linux Safari Chrome
 Москва
1
0
LLeo
Хорошо ;)
Вообще у меня по жизни обычно очень простые запросы. Это только в этом одном месте движка он оказался сложный.
Но фреймворков лишних я реально боюсь, работает MySQL десятилетиями - и пусть. Мне уже хватило переезда с mysql на mysqli с сохранением обратной совместимости, когда эти кретины решили в php7 все поменять...
Mac Safari Chrome
 Германия
0
0
karash_l
Если вкратце, то ORM абстрагирует работу объектов с базой данных. Например, вы говорите, что вам нужен юзер с именем "LLeo" и оно вам его выдаёт, по пути, автоматически генерируя запрос "SELECT ...", и вы этого даже не видите.
Потом вы говорите, что, у этого загруженного из БД юзера, нужно поменять имя и сохранить в базу и оно вам автоматически генерирует запрос "UPDATE ...", и вы, опять же, не видите никаких запросов в БД, они сами генерируются.

В теории и на практике это работает достаточно хорошо до того момента, пока запросы не начинают обрастать разными условиями, которые требуют другие таблицы и сущности. В конечном итоге это ведёт к тому, что нужно вручную формировать запросы (native query), чтобы они были более или менее контроллируемые и быстрыми. Чем вы, по сути, и занимаетесь в данном посте.

P.S. дабы меня не закидали ссаными тряпками. Я сам пользуюсь ORM в работе и считаю этот концепт полезным. Но к данному посту это не имеет никакого отношения.
Linux Safari Chrome
 Москва
0
0
LLeo
А, ну в каком-то смысле, я тоже с этого начинал когда-то пятнадцать лет назад: у меня в движке есть ряд полезных функций по добавлению/обновлению/удалению/выборке из таблиц. Типа:
msq_add_update('users',array('id'=>4,'name'=>'Леопольд Кронштадтский'),'id');
Типа изменить в таблице данные юзера, найдя его по id, а если такого не было, то добавить.

Но довольно быстро оказалось, что любое, чуть более сложное, все равно приходится писать на языке mysql.

Единственно что - с ответом mysql я все равно никогда сам не работаю, брезгую ;) Все эти assoc-парсинги результатов у меня где-то написаны для тех разных случаев и двух моделей mysql/mysqli, а мне возвращается всегда уже готовый массив или строка, чтоб не думать.
Linux Safari Chrome
 Москва
0
0
никого нет
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Как только проект перестает быть статичным и начинает динамично развиваться - все это ОРМ очень быстро накрывается.
Всего-то добавить пару атрибутов - и вот уже надо пересоздавать всю схему, тестировать, исправлять неожиданные логические баги...
Mac Safari Chrome
 Германия
0
0
karash_l
Вот всегда удивляюсь тому, насколько бестолковые советы люди дают. Никогда не перестаю удивляться.
ОРМ и оптимизация запросов - это прям крайне противоположные понятия. ОРМ отлично подходит для простейших CRUD-запросов и абсолютно не подходит для тяжёлых запросов. Об этом знают абсолютно все, кто хоть раз имел дело с ORM.
Зачем об этом упоминать в этой теме, заведомо зная, что речь идёт о нетривиальной задаче?
"Аж трисёт", если честно.
Mac Safari Chrome
 Финляндия
0
1
гость 9000
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
ОРМ не подходит для тяжелых запросов? А мужики-то в своем энтерпрайзе не знали.

Конкретно в данном случае было бы что-то вроде "$publication->getLatestComments()" без каких-либо проблем как раз из-за правильного джойна или отдельного селекта.
Mac Safari Chrome
 Германия
1
0
karash_l
> Конкретно в данном случае было бы что-то вроде "$publication->getLatestComments()" без каких-либо проблем как раз из-за правильного джойна или отдельного селекта.

Ну вы же понимаете, что магии не бывает? Все селекты и джойны по пути генерируются в зависимости от устройства конкретной БД. И очень часто там нужно влезать руками. Иначе, зачем бы в каждом ORM фреймворке существовали бы native queries?
Mac Safari Chrome
 Финляндия
0
0
гость 9000
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Я-то как раз прекрасно понимаю, потому что по долгу службы сам разрабатываю сложную корпоративную систему документооборота и решения на ее основе.

Если при использовании ORM для блога/форума (или чего посложнее не более чем на порядок) вам приходится "очень часто" делать запросы руками, значит, либо вам нужно ознакомиться с нормализацией, либо одно из двух.
Linux Safari Chrome
 Москва
0
0
никого нет
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
В энтерпрайзе бывают очень разные мужики и очень разные задачи ))

У кого-то система с плоской таблицей и 50 000 000 записей в ней, в которых неспешно копается сотня операторов, а у кого-то 400 запросов только в одну секунду, которые надо принять и обработать с минимальной задержкой.
Mac Safari Chrome
 Финляндия
0
0
гость 9000
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Почему время в кавычках, оно строкой хранится?
Linux Safari Chrome
 Москва
0
0
LLeo
Int
Mac Safari Chrome
 Финляндия
0
0
гость 9000
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Так делать ни в коем случае нельзя, возможно поэтому индекс и не используется.
Linux Ubuntu Firefox
 Москва
0
0
LLeo
Шо?!! В смысляяяя?
С каких пор MySQL не умеет строить индексы для INT и с каких, простите, пор для MySQL стало проблемой производить выборки WHERE X < 12345 ?!
Windows Firefox
 Киев
0
0
Fisher12345
Вопрос несколько в другом - почему в приведенном выражении константа 1605909120 в кавычках?

> c.`Time`<'1605909120'

Ведь если поле c.Time Int, то вполне вероятно, что тут на сцену выходит шаманство внутренного приведения типов, которому вполне может стукнуть в голову приводить не строку '1605909120' к целому, а наоборот, все значения, хранящиеся в поле c.Time, усердно привести к строкам, и затем уже сравнивать их все с указанной строковой константой. А поскольку правила сравнения целых чисел отличаются от правил сравнения строк (например, для integer 9 < 11, а для строк может быть наоборот: '9' > '11' (что в свою очередь зависит от ANSI-установок сравнения строк)), то индекс по integer-значениям поля c.Time оказывается бесполезным для сравнения их как строковых значений, и потому не используется.

Признайтесь, кто, в сговоре с кем, и с какой преступной целью поставил эти кавычки? И что будет, если их убрать?
Linux Safari Chrome
 Москва
1
0
LLeo
Я всегда ставлю кавычки, это избавляет от многих проблем, но дело не в этом. То, что вы говорите о внутренней логике MySQL, звучит чудовищно. Успокойте меня, скажите, что это ваша фантазия и на самом деле так быть не может.
Windows Firefox
 Сумы
1
0
Fisher123
Да, это была моя фантазия. Беда в том, что она оказалась недостаточно чудовищной, чтобы быть правдой.

На самом деле ответ кроется на этой страничке:

https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

и сводится к этому кошмару:

In all other cases, the arguments are compared as floating-point (real) numbers. For example, a comparison of string and numeric operands takes place as a comparison of floating-point numbers.

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

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

Может, все же признаетесь, зачем ставили кавычки? И согласитесь их убрать?
Linux Safari Chrome
 Россия
0
0
LLeo
Уберу. Потрясен глубиной ада.
Windows Firefox
 Сумы
0
0
Fisher123
Ну и вишенкой на тортике - может случиться так, что не поможет.

Попробовал на MySQL (8.0.21-0ubuntu0.20.04.4) простой запрос с кавычками и без - так оно уверяет, что может использовать индекс в обеих случаях.

Даже не знаю, что тут сказать. Скорее всего, они уже прикрутили к сравнению чисел со строками какие-то усовершенствования, но в документацию еще не внесли. А усовершенствования эти, видимо, работают еще кривовато.

Карету мне ORACLE мне, ORACLE!...
Windows Safari Chrome
 Севастополь
2
0
hb
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Лично сталкивался с таким поведением. Обычно ж как? MySQL сконвертирует неизменную часть выражения в тип поля таблицы. И работает. Но вот иногда, в каких-то условиях, начинает конвертировать наоборот, значение полей таблицы. И тут да, ад адский по производительности...
Linux Ubuntu Firefox
 Telefonica Germany GmbH & Co. OHG
0
1
gehrmann
Кавычки в MySQL - это не признак строки, это признак данных.
Linux Ubuntu Firefox
 Киев
0
1
Lintruder_0
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Возможно, речь идет о неявном приведении типов. Надо курить мануал, как MySQL сравнивает в случае, если цифровое поле сравнивается со строкой.
И, вероятно, в таких случаях индекс и не используется.
Linux Safari Chrome
 Москва
2
0
LLeo
Вы это серьезно? Как цифровое поле INT может сравниваться со СТРОКОЙ? А если бы я поле INT сравнивал с говном, MySQL бы превратило полмиллиона записей в говно ради такого сравнения?!
Linux Safari Chrome
 Австралия
0
0
Тыгыдык
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
А что тебя удивляет?
К какому-то одному типу обе половинки должно привести, верно?
Насчёт говна не знаю, но документация MySQL гласит, что:

In all other cases, the arguments are compared as floating-point (real) numbers. For example, a comparison of string and numeric operands takes place as a comparison of floating-point numbers.

https://dev.mysql.com/doc/refman/8.0/en/type-conversion.html

Так что да, происходит превращение типов. И не в int, а в float. О чем там дальше по той же ссылке навалено куча всего.

Может ли при этом продуктивно использоваться индекс - не знаю, извини.
Linux Ubuntu Firefox
 Telefonica Germany GmbH & Co. OHG
1
1
gehrmann
Любые данные в MySQL без проблем обрамляются кавычками. Это один из способов обезопаситься от SQL-инъекций - квотить кавычку и данные обрамлять в неё.
Mac Safari Chrome
 Германия
1
0
karash_l
Сейчас бы вместо prepared statements использовать кавычки для предотвращения инъекций.
С подключением вас. А у нас тут уже 2020 наступил.
Windows Firefox
 Сумы
0
0
Fisher123
Боюсь, когда SQL-оптимизатор увидел эту вашу строку " c.`Time`<'1605909120' ", у него в голове произошел именно такой диалог.

PS. Какая версия MySQL?
Linux Ubuntu Firefox
 Telefonica Germany GmbH & Co. OHG
0
1
gehrmann
'123' в MySQL - это не строка.
Linux Ubuntu Firefox
 Telefonica Germany GmbH & Co. OHG
0
0
gehrmann
Обычно такие запросы анализируются командой DESCRIBE (пишется перед запросом). Там показано, какие именно индексы используются. Иногда на основе этой информации нужно немного пошаманить с порядком условий или сгенерить комплексный индекс, и всё зашевелится.
Linux Ubuntu Firefox
 Telefonica Germany GmbH & Co. OHG
1
2
gehrmann
Капец, почитал комментарии. Куча народу в MySQL не шарит, но убедительно топит за то, что '123' - это строка.
Mac Safari Chrome
 Германия
1
0
karash_l
А что вы скажете на это?

set @a:=123;
drop temporary table if exists foo;
create temporary table foo select @a;
desc foo;

+-------+--------+------+-----+---------+-------+
Field Type   Null Key | Default | Extra |
+-------+--------+------+-----+---------+-------+
| @
a    bigint YES  |     | NULL    NULL  |
+-------+--------+------+-----+---------+-------+


Против
set @a:=&#39;123';
drop temporary table if exists foo;
create temporary table foo select @a;
desc foo;

+-------+----------+------+-----+---------+-------+
Field Type     Null Key | Default | Extra |
+-------+----------+------+-----+---------+-------+
| @
a    longtext YES  |     | NULL    NULL  |
+-------+----------+------+-----+---------+-------+


Edit: во втором случае в первой строке стоит "set @a:='123';". Это блок кода одинарные кавычки изуродовал.
Windows Firefox
 Сумы
2
0
Fisher123
А что же это тогда?

9.1.1 String Literals

A string is a sequence of bytes or characters, enclosed within either single quote (') or double quote (") characters. Examples:

'a string'
"another string"

https://dev.mysql.com/doc/refman/8.0/en/string-literals.html
Linux Ubuntu Safari Chrome
 Киев
0
1
па x0m
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
Дежурно предложу переписать всё на популярной сейчас датабазе и языке программирования.

В этот раз правильно, как в умных книжках учат.
Linux Safari Chrome
 Москва
1
0
LLeo
Боюсь, я сам не справляюсь, всё рухнет, и вы больше не сможете оставлять свои мудрые комментарии...
Linux Firefox
 Санкт-Петербург
1
0
sadko4u
Этот человек не загрузил свой юзерпик, и я подобрал ему этот. Человек, пишущий такое, должен именно так выглядеть, верно?
1. Выборку записей вынести в подзапрос и join'ить остальное уже к выдаче с этого подзапроса.
2. Добавить сортировку, без неё не гарантируется порядок выборки и перечень выбираемых данных.
3. Соблюдать типы данных в аргументах '123456' - это не время, а строка. В таком случае ваши индексы перестают работать, т.к. движок пытается сначала превратить время в строку, а потом уже сравнить его с вашей строкой на входе, а это - полный проход по таблице от начала до конца. Типичная ошибка, кстати, для тех, кто начинает работать с базами данных, где '123' и 123 - совсем разные типы данных и по-разному влияют на планировщик запросов.
4. explain позволяет найти узкие места и переосмыслить алгоритм работы запроса.
Mac Safari
 Санкт-Петербург
0
0
TI_Eugene
> А какие индексы?
Честно прочитал всё полотенце камментов, но внятного ответа на этот вопрос так и не увидел.
Ну, кроме
> Да все, что упоминаются хоть где-то.
но поиска по полю "да все" тоже не увидел (возможно есть в запросе "хоть где-то", но этот запрос не процитирован).
Linux Safari Chrome
 Санкт-Петербург
0
0
LLeo
У всех полей, которые упоминаются, есть свои индексы.

всего комментариев: 101

<< предыдущая заметка следующая заметка >>

рекламная лирика