Класс для безопасной работы с MySQL

[fixxxer]

Ну я бы как минимум с этим способом менял список выбираемых полей на select 1 и убирал order by.

 

[grigori]

WMix, ты просто не писал пангинацию с таблицами, в которых несколько миллионов записей -- твой вариант просто положит сервер, DBA или тимлид задумаются о твоей замене, а премию в следующие пол-года получит кто-то другой

 

[Вурдалак]

grigori, если это нельзя пофиксить в одном месте, то проблема вовсе не в SELECT COUNT(*) FROM (...) t, а в архитектуре кода. Если ты используешь Redis для очередей, то это не значит, что через год он будет справляться, может быть нужен будет RabbitMQ. Это не значит, что нужно лишать премии того, кто выбрал Redis, это значит, что нужно дописать адаптер для RabbitMQ в связи с новыми обстоятельствами.

UPD Если речь о существующей таблице, то да, бизнесу нужно напомнить, что реализация пагинации миллионов записей не имеет практического смысла и технически реализуется сложно. Но в любом случае где code review, QA на staging сервере и т.д.? Тимлид за что деньги получает?

 

[WMix]

WMix, ты просто не писал пангинацию с таблицами, в которых несколько миллионов записей -- твой вариант просто положит сервер

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

Ну я бы как минимум с этим способом менял список выбираемых полей на select 1 и убирал order by.

order by может и есть смысл убирать, да вот полько случайно order by в subselect'е не зацепи, select 1 тоже не всегда возможно бывает select field having field.

 

[grigori]

если это нельзя пофиксить в одном месте, то проблема вовсе не в SELECT. Если ты используешь Redis для очередей, то это не значит, что через год он будет справляться.

да вот полько случайно order by в subselect'е не зацепи, select 1 тоже не всегда возможно

у нас день подражания Кличко или я пропустил общую пьянку?

В крупных магазинах вроде Сотмаркета пол-миллиона товаров и миллионы записей в таблицах характеристик, в каталогах желтых страниц у меня было 40 млн компаний. Свойства товаров, адреса, детали юзеров - они не нужны при подсчете количества, но от запроса на выборку всех записей по ним без лимита падает 12-ядерный сервак с 128 гб памяти. Есть опыт решения таких проблем

 

[Вурдалак]

WMix, ты просто не писал пангинацию с таблицами, в которых несколько миллионов записей -- твой вариант просто положит сервер, DBA или тимлид задумаются о твоей замене, а премию в следующие пол-года получит кто-то другой

grigori, «ты просто не писал обработчиков очередей на несколько тысяч сообщений в секунду — твой вариант просто положит сервер, тимлид задумается о твоей замене, а в следующие полгода получит кто-то другой».

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

 

[grigori]

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

 

[Вурдалак]

А code review и staging серверов у тебя правда нет? То есть очень прикольно все сваливать на одного чувака-программиста, это очень профессионально и очень мотивирует работать в твоей команде.

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

 

[WMix]

пол-миллиона товаров надеюсь не нужно показывать за раз с пагинацией отсортированных случайным образом, в конкретной категории их становиться явно меньше. а если еще по лэйблу фильтрануть, там врятли более 1к останется. миллионы записей в таблицах характеристик да хоть 10м, на одну страничку выборка ~30 записей ну пусть по 10 характеристик на продукт 300 записей.
если запрос тормозной SQL_CALC_FOUND_ROWS врятли спасет ситуацию или?

 

[grigori]

а кто говорит о моей команде?
я работаю в маленьком проекте с базой на 100 мб, а Сотмаркет был давно, и там решались проблемы роста на расовом узбекском коде

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

 

[Фанат]

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

Погодь, а что там унутре датабазы-то происходит?
Выборка делается, или умный оптимизатор все видит или понимает?

 

[WMix]

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

select * from products where category_id = 42 /* order by price asc */

то подсчет строк

select count(*) from (
  select * from products where category_id = 42
) as t

первый запрос пишешь, второй генерит paginator. не глупо писать такой запрос, count которого, менее пару тыс. записей. это имелось в виду. и до тех пор пока запрос это обьект от билдера, order by удалить легко, но если этот запрос уже string вырезать order by становиться сложновато

 

[WMix]

https://github.com/zendframework/zf2/blob/master/library/Zend/Paginator/Adapter/DbSelect.php#L98
кстати вот оно

 

[grigori]

Фанат, результат подзапроса идет во временную таблицу, если результат больше tmp_table_size - пишется на диск, если есть order by - она сортируется, затем эта временная таблица подается на вход внешнего запроса count(*), и считается количество строк -- естественно, full scan без индексов. На время исполнения запроса лочатся подсчитываемые записи, а так же все, связанные с ними по join, конкурентные update/delete подвисают, за ними подвисают select-ы, что при должной нагрузке приводит к недоступности сервера.

 

[MiksIr]

но от запроса на выборку всех записей по ним без лимита падает 12-ядерный сервак с 128 гб памяти

Интересно, а как тут лимиты помогают. Лимиты все-равно применяются после сортировки, т.е разница лишь во времени доставки данных на клиент, разве нет?

 

[grigori]

когда запрос оптимизирован, сортировка выполняется по индексу мгновенно без чтения данных из таблиц, а затем для 10 записей из основной таблицы делается выборка всех данных по join из остальных таблиц;
если запрос с подзапросом без лимита -- скорее всего будет fullscan основной таблицы и 10 тысяч записей в temporary table потому что mysql подзапросы без временных таблиц практически не умеет,
а если я уже занялся оптимизацией подзапроса и правлю ручками -- я разделю подсчет количества и получение данных, чтобы бегало по индексам без временных таблиц

 

[grigori]

В особо сложных случаях, когда нужен и order by, и limit, и join больших таблиц, я делал все вручную: создавал временную таблицу, заполнял ее из основной таблицы через select into, создавал индекс, и уже на нее делал join справочников.
Смысл в том, что во временной таблице тоже можно сделать индексы. Так можно решить ограничение mysql использовать 1 индекс на таблицу в запросе: делать несколько запросов. По крупным таблицам композитные индексы могут быть неэффективны - быстрее создать новый индекс по маленькой временной таблице.

 

[NBK]

Всем здрасте. Начал использовать ваш скрипт.
Где-то читал что query() в данном классе может по предложенным данным выбрать делать update или insert как реализовать?
кусок кода в таблице поля C_1,C_2,C_3 - уникальны но могут отсутствовать.

$sql = "INSERT INTO main ".
                "(TIME_GET,NAME,C_1,C_2,C_3,NAME2,P_MET,P_CR,UID,NICK,STATUS,RAITING)".
                "VALUES ('$timeget','$pl[0]','$coord[0]','$coord[1]',
                '$coord[2]','$name2','$p_met','$p_cr','$uid','$nick','$status','$rating')";
                echo $sql."<br>";
                $query = $db->query($sql);

получаемый запрос.

INSERT INTO main (TIME_GET,NAME,C_1,C_2,C_3,NAME2,P_MET,P_CR,UID,NICK,STATUS,RAITING)VALUES ('2015-1-10 11:24:45','SUPERSTAR','1','1', '1','','0','1000','25996','sea','','370')

Проект пока личный а скрипт скорее всего останется личным навсегда посему особо над безопасностью не заморачиваюсь сразу.
Большое спасибо за помощь.

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

 

[karnas]

Всем здрасте. Начал использовать ваш скрипт.
Где-то читал что query() в данном классе может по предложенным данным выбрать делать update или insert как реализовать?

$data = array( 'data1'=>$data1, 'data2'=>$data2);
$sql = "UPDATE table SET ?u where .........";
$db->query($sql, $data);

$data = array( 'data1'=>$data1, 'data2'=>$data2);
$sql = "INSERT INTO table ?u";
$db->query($sql, $data);

А вообще, в мануале все подробно описано.

 

[Фанат]

Всем здрасте. Начал использовать ваш скрипт.

И сразу неправильно.
Если писать в него запрос по-старинке, то никакого смысла пользоваться им нет.
А если пользоваться, то любые переменные должны идти в запрос в обязательном порядке через плейсхолдеры
Для запросов INSERT и UPDATE есть специальный плейсхолдер, для которого надо передать массив, где ключ - это имя поля, а значение - передаваемая переменная:

$data = array(
'TIME_GET' => $timeget,
'NAME' =>$pl[0],
// и так далее
);

и после этого вызвать запрос, как показано выше:

$sql = "INSERT INTO table ?u";
$db->query($sql, $data);

С этим ясно?

Где-то читал что query() в данном классе может по предложенным данным выбрать делать update или insert как реализовать?

Ничего такого эта функция не умеет.
Умеет mysql, но не по предложенным данным, а по первичному ключу.
Если в таблице есть первичный ключ, то можно после запроса INSERT дописать ON DUPLICATE KEY UPDATE TIME_GET=values(TIME_GET), NAME=values(NAME) ...
в этом случае, при попытке вставить существующий первичный ключ, будут обновлены указанные в конце запроса поля
пример: http://phpfaq.ru/examples#onduplicate

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

http://phpfaq.ru/examples#multiinsert