webew
Войти » Регистрация
 

Оптимизация запросов к MySQL

11 апреля 2016, 12:59
Автор: ElenaGr
Есть две таблицы. В одной - данные договоров, во второй - телефоны клиентов. Телефонов - около 8 млн. Задача - выбрать из таблицы договоров ИНН клиентов, договоры которых заканчиваются в определенный период - месяц или декаду. Затем найти телефоны из таблицы телефонов. Связь таблиц - по ИНН. Телефонов у клиента м.б. несколько, нужен только один. Затем эти телефоны вывести как csv для загрузки в другую программу (call-center).
Сейчас делаю так:
1. Выбираю уникальные ИНН за нужный период в нужном формате:
SELECT DISTINCT LPAD(AFM, 9, '0') as afm FROM `ysae_policies_athens` WHERE
`PolicyEnd` BETWEEN STR_TO_DATE('2016-05-01 00:00:00', '%Y-%m-%d %H:%i:%s') AND
STR_TO_DATE('2016-05-05 23:59:59', '%Y-%m-%d %H:%i:%s')
2. Делаю экспорт полученных записей в csv-файл.
3. Читаю построчно этот файл скриптом, находя телефоны, записываю их в другой csv, который потом можно будет скормить другой программе.

Если за один период выбираю, то нормально, только долго. А если надо объединить несколько периодов, то уже нет уверенности, что в csv попадут уникальные номера телефонов (у клиента м.б. и несколько договоров в разные периоды). Поэтому переделываю скрипт, чтоб телефоны сохранялись в новой таблице. Так можно будет отследить их уникальность и выбирать не все сразу, а группами. Искала, как правильно работать с большими объемами данных. Наткнулась на вашу библиотеку, захотела попробовать.
Добавить комментарий
Отображение комментариев: Древовидное | Плоское

1234ru

Много же у вас клиентов :)

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

Проблемы возникают именно из-за того, что, согласно постановке задачи, вам нужно выбрать все записи сразу. Всё упирается даже не в ORM, а в операцию более низкого уровня - выборку данных из MySQL в PHP. Конкретно вот в эту стадию: "читаю построчно этот файл скриптом".

Построчное чтение в данном случае не нужно.
В MySQL есть команда SELECT INTO OUTFILE, с её помощью можно выгрузить данные прямо в конечный (второй) csv-файл, минуя выборку в PHP. Это очень сильно ускорит процесс.

Если хотите, я расскажу подробно, как это сделать конкретно для вашего случая.
То, что не убивает нас, делает нас инвалидами.
11.04.2016, 13:39
Ответить
NO USERPIC

ElenaGr

Это не наши клиенты. Базу договоров мы пополняем по SOAP. Это автостраховки. Мы звоним тем, у кого подходит срок окончания. Обычно это 5-10 тыс. номеров в декаду. Все 11 млн. сразу мне не нужны.
Спасибо за посказку, попробую SELECT INTO OUTFILE. Помню, что пробовала с LOAD DATA, но потом отказалась от него.
11.04.2016, 13:52
Ответить
NO USERPIC

ElenaGr

Пострoчно я читаю из csv, когда вставляю данные. A для выборки использую phpMyAdmin. Select выполняется 2-3 минуты. Результат экспортирую в csv. Сделала SELECT INTO OUTFILE, по времени выигрыш небольшой, но больше заморочек с файлом. Легче сделать экспорт из PMA.
11.04.2016, 14:59
Ответить

1234ru

2-3 минуты для 10 т. записей - это многовато... У вас, видимо, сам запрос не до конца оптимизирован. Поэтому и разница между построчным разбором и SELECT INTO OUTFILE не видна.

В общем, если хотите быстрее - можем попробовать улучшить работу самого запроса.
То, что не убивает нас, делает нас инвалидами.
11.04.2016, 17:52
Ответить
NO USERPIC

ElenaGr

Наверно, можно убрать функцию LPAD(AFM, 9, '0'), а ведущие нули добавлять скриптом.
11.04.2016, 18:46
Ответить

1234ru

Вряд ли вызов LPAD() здесь имеет решающее значение. Скорее всего, СУБД долго ищет нужные данные из-за того, что не хватает нужных индексов.
Чтобы сказать наверняка, нужен EXPLAIN запроса (так и писать - EXPLAIN и дальше текст запроса без изменений) и структура задейстовованных в нем таблиц в виде SHOW CREATE TABLE имя_таблицы.

Примечание: т.к. обсуждение ушло в несколько другое русло, вынес его в отдельную тему.
То, что не убивает нас, делает нас инвалидами.
13.04.2016, 14:11
Ответить
NO USERPIC

ElenaGr

Оптимизирую таблицу. Хочу оставить по каждому клиенту только его последний по дате окончания договор. Индексы присутствуют по тем полям, по которым часто делаются запросы.Без функций (LPAD и STR_TO_DATE) запрос выполняется быстрее, по крайней мере PMA не выдает ошибку, что ему не хватило времени. Как добавить время выполнения запроса именно в PMA, не знаю. У меня wamp.
13.04.2016, 15:32
Ответить

1234ru

Если не можете привести тут EXPLAIN проблемного запроса и SHOW CREATE TABLE - можем разобрать.
То, что не убивает нас, делает нас инвалидами.
13.04.2016, 16:53
Ответить
Добавить комментарий
Отображение комментариев: Древовидное | Плоское
© 2008—2017 webew.ru, связаться: x собака webew.ru
Сайт использует Flede и соответствует стандартам WAI-WCAG 1.0 на уровне A.
Rambler's Top100