Всем доброго времени суток, делаю небольшую биллинговую систему для офисной АТС
требуется помощь в составлении запроса
на текущий момент есть запрос:

запрос выбирает данные с сервера и выдает сформированный ответ кто, куда , стоимость, страна и т.д.
столкнулся с проблемой при доработке запроса, надо выбрать нужную тарифную опцию по наиболее большому количеству совпадений с таблицей tariff-data на основании данных r.uid,sr.uid,i.uid , посредством гугла и других источников была сделана примерно такая конструкция:

она работает только с ручной заменой данных внутри FIND_IN_SET ,как можно доработать основной запрос с этим или подобным функционалом?

Это сообщение отредактировал(а) alligator - 26.8.2015, 19:58

Если вопрос в выборе номера тарифа -- то вписать его попросту в zoneinfo. Поскольку в примере запроса он только от записи в zoneinfo и зависит. В таком случае мне, правда, непонятно, почему Вы тариф называете тарифной опцыей --  тарифная опцыя по смыслу это что-то, что выставляет оператор при заведении абонента.

А если там действительно есть тарифные опцыи, разные для разных пользователей -- то всё равно вписать номер тарифа для направления в zoneinfo, а пользователю прописать номер его тарифной опцыи, и сделать полную таблицу, в которой содержалось бы дэкартово произведение всех тарифов по направлениям (их, дажэ с учётом всех имеющихся стран, всего в пределах двух сотен) и всех тарифных опцый пользователей (ну, их тожэ дажэ в крупных операторах в пределах нескольких сот) -- получится маленькая таблица с десятком тысяч записей о цэнах.

tzirechnoy, вы предлагаете использовать zoneinfo, это подходит только для одного направления а у меня их больше 4, когда я только начинал делать данный запрос он как раз использовал zoneinfo.

Не получиться сделать то что вы предлагаете, например у меня таблица zoneinfo содержит пример 14к записей * на кол-во направлений =4 и получим 56к записей

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

тоесть получаеться когда разбираю звонок у меня получается следующая информация посредством которой надо определить тариф, зону и стоимость звонка в зону.:
звонок на 903 123 45 67 относиться к направлению dst1, зоне Москва и МО(131), провайдер Вымпелком(8), подрегион и NULL

в общем :
dst1 , 131 , 8 , null

На основании полученной информации смотрим таблицу tariff в которой ищем тариф по конкретному направлению, затем получаем для тарифа список тарифицируемых зон в tariff-data и получаем что-то типа такого:

uid    tuid    name    price    isp    region    subregion    currency    description
1    1 [->]    Москва (фикс.)    0.58    NULL    50    NULL    NULL    NULL
2    1 [->]    ФСС (Москва,МО)    1.69    NULL    131,165    NULL    NULL    NULL
3    1 [->]    ФСС (Москва,МО) Вымпелком    1.28    8    131    NULL    NULL    NULL

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



Это сообщение отредактировал(а) alligator - 27.8.2015, 10:20

Это копейки. Кабы речь шла о сотнях миллионов записей - вот тогда можно было бы кивать на их количество.


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

Всех понял, поддерживаю, тогда может таблице tariff-data сделать вместо 3-х полей isp,region,subregion одно поле zones и вносить туда zoneinfo uid ,  а проверять FIND_IN_SET ,так будет оптимально?
Если да, то я затрудняюсь сказать какой длинны может получиться строка с zoneinfo uid для москвы к примеру, в какой тип можно выставить поле?


Это сообщение отредактировал(а) alligator - 27.8.2015, 10:54

Звучит разумно.


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

Как я понимаю ваше предложение хранить каждый zoneinfo uid в отдельной строке по каждой зоне тарификации, правильно?

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

И да, в zoneinfo я предлагал указывать как раз индэкс тарифа направления. Их, типично, штук 5 по РФ, возможно ещё 1-5 по своей зоне и штук 150 по остальному миру. Если  всё достаточно маньячно -- то будет около 100 по РФ (на каждый субъект) 150 по остальному миру. Если всё совсем маньячно, и надо описывать в каждом регионе несколько локальных зон -- то будет 400 по РФ. Как видите, никаких 15k нет и в помине -- хотя 15k направлений, действительно, можэт быть (можэт быть и большэ).

Ну и, 4 тарифных опцыи что на 500 тарифов по направлениям, что на 15k -- это совершэнно копеечная таблица, которая замечательно поместится в память (да и дажэ, вероятно, в кэш процэссора), и поиск в которой займёт максимум 100ns (десяток доступов к произвольной области паамяти, если она ещё не в кэшэ), а скорее и меньшэ.

Конечно.
А ещё точнее - я бы предложил провести анализ как положено и полностью нормализовать всю структуру.

Дополнил структуру, в итоге запрос получился таким:

Запрос стал отрабатывать чуток быстрее, но все же недостаточно быстро на текущий момент 43 секунды,  до этого было 52 =(
Сервер на базе виртуальной машины 4 ядра Intel® Core™ i7-4820K CPU @ 3.70GHz\ 4gb ram \ HDD 7200 (физика не нагружена другими виртуалками)
Как можно оптимизировать запрос? уж совсем медленно работает  (
P.S. когда проверяю некоторые запросы отдельно то работает практически мгновенно, но когда в один делаю долго
Explain

Во время выполнения запроса, процессор 100%




Это сообщение отредактировал(а) alligator - 27.8.2015, 15:52

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


Это сообщение отредактировал(а) alligator - 27.8.2015, 16:50

Провел небольшие тесты
Тестировал так - 36 сек


В таком виде - 19 сек


так - 9 сек


Добавил индексы + добавил (FORCE INDEX), убрал 1 join получилось 9 секунд (возможно сделать хотябы до 3 секунд?)


Это сообщение отредактировал(а) alligator - 28.8.2015, 11:50

Что-то у меня, как это часто бывает при встрече с MySQL (а последние 15 лет я стараюсь встречаться с ним поменьшэ, но всё-таки периодически приходится), цэнзурные слова закончились. Запрос -- тривиален, если бы не баги оптимизатора мыскля, оно бы из памяти за миллисекунды отрабатывало. Если бы хотя бы spatial index работали, можно было бы через них заставить, никуда бы не делось.

Я ещё подумаю, что можно предпринять. Наверняка что-нибудь придумается. В концэ концов можно попытаться codes в MyISAM выкинуть, и сделать spatial index и симитировать геометрию -- всё равно эта таблица статичная и в транзакцыях не участвует. В общем, подумаю.

Но можэт ты, раз только начал -- возьмёшь postgres? Я понимаю, опыта у окружающих мало, и понимаю, что если переносить в лоб большое приложэние MySQL -- то встретятся и замедления и всё, но он ужэ почти 20 лет в среднем лучшэ, и в нём добиться нетривиального результата в среднем проще, чем в MySQL примерно во всех областях, в которых MySQL применяется.

Ага, нашёл. Кривизну оптимизатора можно нагнуть с помощью хранимки.
Создаю функцыю:


Потом таким запросом выбираю:


И всё ужэ нормально работает. Ну, как нормально, без хранимой оно было бы в два раза быстрее, но хоть так.

Да, поскольку у меня сейчас нет MariaDB -- то сменил этот регексп на простой LIKE, это понятно как вернуть обратно. И добавил поле code_is_valid -- поскольку реально можэт оказаться, что номер не попадает в код. По хорошэму, ещё надо или в get_cuid получать какой-нибудь cuid дажэ если нет такого префикса ABC/DEF или сделать там LEFT OUTER JOIN вместо INNER JOIN на все остальные таблицы.

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

И да, тройной индэкс для abcdef, start, end безсмысленен -- можно оставить только первые два поля. То есть третье вообще ни в каких логичных случаях использоваться не будет.

И такое использование BETWEEN для времени не всегда корректно (зависит от СУБД) -- лучшэ писать >= 'сегодня 00:00' AND < 'завтра 00:00'

И то, что я использовал prefix и number в том жэ списке полей, в котором их определил -- это по-моему ANSI SQL standard не очень одобряет и не все базы понимают. Но переписывать как правильно мне лень, да и так вполне идиоматично получилось.

сколько получилось время выполнения?


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

обновил архив с дампом.

потестил еще:


на моем запросе во втором select получилось 11 секунд


убрал третье поле время с 9 секунд сразу поднялось до 36, пока вернул обратно....

это выполнилось мгновенно


добавляю к запросу 



получилось 9 сек

Это сообщение отредактировал(а) alligator - 29.8.2015, 18:15

1) Никогда не указывайте напрямую имя базы данных, если не пишэте софт для управления кластером баз данных. Если нужны какие-то внешние данные, и СУБД можэт их получить -- сделайте локальный (в своей БД) альяс/ссылку/что там СУБД позволяет.

2) А зачем Вы, собственно, сделали две DATABASE? Вам мало проблем?
Люди, которые будут это деплоить, за такое и побить могут.

3) Никогда не используйте + или - или какие-то ещё знаки операцый в именах таблиц. И русских букв не используйте. Вообще, используйте только a-zA-Z0-9_ . И не начинайте имя с цыфры. Людям, которые ковыряют потроха движков и делают какие-то метабазыданных иногда можно использовать какие-нибудь @ или % или $ для служэбных цэлей, прикладному программисту -- нет.

0.35c. А самому запустить тот запрос?



Что выкладывать? Базу с интэгрированной таблицэй cdr? Ну, можно, но мне лень пока. Учитывая, что всё, что нужно -- перенести одну таблицу в основную базу. Да и на скорость влиять не должно -- это общая такая рекомендацыя.



Для времени, я сказал. И BETWEEN строго эквивалентно a >= begin AND a <= end, тут уж это чисто синтаксическая разница. А для времени надо полуоткрытый интэрвал.



Не верю. У меня минут 15 выполнялось -- оно и понятно, там несколько миллионов записей в результсете, поскольку на каждую запись из cdr выдастся всё с совпадающим ABC/DEF префиксом -- это в среднем по полторы тысячи записей.
И это пото ещё отсортировать.

Добавлено через 4 минуты и 44 секунды


Впрочем, да, тут я подумал -- для твоего варианта, с INNER JOIN, оно можэт ускорить... В среднем вдвое, наверное. Но только за счёт того, что этот поиск не будет выполняться нормально.

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


можно по подробней....?


Полностью согласен.


Только добрался до компа, до этого небыло возможности....
Потестировал твой запрос и более детально посмотрел код, спасибо что нашел время!
Работает быстро, но в хранимке проверяется только поле start, а это не очень подходит для биллинга, может неверно считать...
конечно понятно что это только пример обхода бага оптимизатора...
Так вроде, точнее....


Запрос занял 0.3904 сек

убираю из запроса:

Explain на этом поле показывает ALL следовательно единственный для этой таблицы primary key не используется
Запрос занял 0.0129 сек


Да , уже потом заметил что забыл добавить WHERE......etc...

Это сообщение отредактировал(а) alligator - 31.8.2015, 17:55

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

У меня для обхода этого в результате было поле code_is_valid (оно должно было проверяться в приложэнии). Твой вариант -- тожэ вполне вариант. Но в любом случае непопавшые никуда звонки надо как-то обрабатывать, и, для начала, их выбрать (например, таки добавив поле code_is_valid и сделав везде LEFT OUTER JOIN вместо INNER JOIN).




Непохожэ на правду. Скорее, в первом случае он с диска читался. У меня строго одинаковые 0.35с получились в обоих случаях. Да и чему там тормозить: в numbertype две записи, они и безо всякого ключа в столько жэ сравнений фильтруются.

Ну и, кстати, попробовал тожэ самое в postgres (без stored function, просто двумя запросами в коде) -- оно сразу втрое быстрее, чем такой жэ запрос mysql с stored function стало.

Почитал про postgresql, заинтересовало ..... поставил но пока ищу как дамп импортировать

Руками импортировать, разумеется. С отдельно сохранил весь DDL, отдельно DML.

DDL переписывал довольно заметно. int(3) -> smallint, int(7) -> int, int(15) -> bigint. ADD KEY -> create index, дажэ макрос в виме записал, поскольку их много было.

Да, весьма удобно тэстировать в транзакцыи. Т.е. 

После многочисленных экспериментов с INSERT/DELETE полной таблицы жэлательно сделать vacuum full verbose analyze -- поскольку старые версии записей с диска удалятся не так чтобы скоро дажэ в версиях, в которых есть autovacuum.

Ну и да, плюсы постгрэсса всё-таки не в скорости. Со скоростью всё довольно неплохо, в сложных случаях -- так и лучшэ, чем в mysql, но всё-таки дело не в этом.

Добавлено через 11 минут и 28 секунд



Честно говоря, посмотрел на mysql в очередной раз -- действительно у него нет ничего хорошэго чтобы делать ссылки внутри. А т.н. "базы данных" очень похожы на то, что в других СУБД называется "scheme" или "namespace", потому действительно не настолько это всё требуется переименовывать. Точнее, можэт и требуется -- но всё равно нет нормальных способов.

Просто как-то очень непринято для одной задачи выделять две базы данных. Ещё менее принято фиксировать имя базы данных -- поскольку, например, типичный случай -- это на одном сервере один боевой вариант и пачка тэстовых, и отличаются только именами.

Впрочем, конкретно в твоём случае, возможно, что ENGINE=federated было бы во многих смыслах более общим решэнием, и позволило действительно разнести cdr и ведение счёта по разным серверам.

В общем сделал базу на PostgreSQL, изначально конечно тяжеловато..... много незнакомого, но результат впечатлил....
база + primary key + индексы с таким запросом в лоб:

700 ms

если целиком:

10 секунд


Это сообщение отредактировал(а) alligator - 1.9.2015, 17:19

Э-э-э. Ну, надо заметить, что если интересует скорость -- то таки или пытаться сделать cube и GiST-индэкс на abcdef||start/abcdef||end (не знаю точно как, ни разу с ними не работал) или таки делать почти тот жэ самый хак для выборки одного значения по индэксу:



Это, собственно, логично если знать как индэксы работают и практически неизбежно.

Разница с mysql -- в том, что не требуется оборачивать это в функцыю (у mysql без оборачивания планировщик херню делал).



Слушай, ну это надо в JOIN CONDITION записывать. И, если с INNER JOIN это был такой синтаксический кунштюк (ну, физически серверу никакой разницы, где это условие написано), то с LEFT OUTER JOIN -- это обязательно должно быть в JOIN CONDITION.

И да, наличие измеримой разницы между первым и вторым -- говорит, что что-то не то с индэксами. Там по идее всё, что ты добавлял -- это выборка одного значения по ключу, это несопоставимое время должно занимать. Ну, у меня оно отличается всё-таки -- 1.6с и 1.8с, но незначительно.