Версия для печати темы Нажмите сюда для просмотра этой темы в оригинальном формате

Форум программистов > Составление SQL-запросов > запрос для биллинга

Автор: alligator 26.8.2015, 19:55

Всем доброго времени суток, делаю небольшую биллинговую систему для офисной АТС требуется помощь в составлении запроса на текущий момент есть запрос: Код SELECT b.calldate,b.src, CONCAT(b.prefix, b.number) as dst , b.duration, b.billsec, b.billmin, b.trunk, td.price,  ROUND(CEIL(b.billmin)*td.price, 2) as bill, t.numbertype, i.ispname,sr.subregionname,r.regionname FROM (SELECT h.calldate ,h.src,SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 1, 3 ) AS prefix ,SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 4, 7 ) AS number,h.duration,h.billsec , ROUND((CASE h.billsec WHEN 0 THEN 1 ELSE h.billsec END)/60, 2) as billmin, REGEXP_REPLACE(dstchannel, "(?:SIP|OOH323|LOCAL|SCCP)/(?:FMPR|FMGL)?([a-zA-Z_]+)?.*", '\\1') as trunk FROM  asteriskcdrdb.cdr h  WHERE  calldate BETWEEN  '2015-05-01 00:00:00' AND  '2015-05-31 23:59:59' AND LENGTH( h.src ) <=3 AND LENGTH( h.dst ) >3 AND h.disposition='ANSWERED') as b  inner join astcdr.codes  c on b.prefix=c.abcdef inner join astcdr.zoneinfo zi on zi.uid=c.zoneinfouid inner join astcdr.isp i on i.uid=zi.ispuid inner join astcdr.numtype t on t.uid=c.type inner join astcdr.region r on r.uid=zi.regionuid inner join astcdr.subregion sr on sr.uid=zi.subregionuid inner join astcdr.tariff tr on tr.trunk=b.trunk inner join astcdr.`tariff-data` td on td.tuid=tr.uid and ........ WHERE  b.number between c.start and c.end and b.trunk='Beeline'; запрос выбирает данные с сервера и выдает сформированный ответ кто, куда , стоимость, страна и т.д. столкнулся с проблемой при доработке запроса, надо выбрать нужную тарифную опцию по наиболее большому количеству совпадений с таблицей tariff-data на основании данных r.uid,sr.uid,i.uid , посредством гугла и других источников была сделана примерно такая конструкция: Код SELECT *, (if(FIND_IN_SET('8',isp),1,0)+if(FIND_IN_SET('131',region),1,0)+if(FIND_IN_SET('1844',subregion),1,0)) as matches from `tariff-data` ORDER BY matches DESC LIMIT 1 она работает только с ручной заменой данных внутри FIND_IN_SET ,как можно доработать основной запрос с этим или подобным функционалом?

Автор: tzirechnoy 26.8.2015, 21:18

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

Автор: alligator 27.8.2015, 10:13

tzirechnoy, вы предлагаете использовать zoneinfo, это подходит только для одного направления а у меня их больше 4, когда я только начинал делать данный запрос он как раз использовал zoneinfo. Цитата(tzirechnoy @  26.8.2015,  22:18 ) А если там действительно есть тарифные опцыи, разные для разных пользователей -- то всё равно вписать номер тарифа для направления в zoneinfo, а пользователю прописать номер его тарифной опцыи, и сделать полную таблицу, в которой содержалось бы дэкартово произведение всех тарифов по направлениям (их, дажэ с учётом всех имеющихся стран, всего в пределах двух сотен) и всех тарифных опцый пользователей (ну, их тожэ дажэ в крупных операторах в пределах нескольких сот) -- получится маленькая таблица с десятком тысяч записей о цэнах.  Не получиться сделать то что вы предлагаете, например у меня таблица zoneinfo содержит пример 14к записей * на кол-во направлений =4 и получим 56к записей я решил привязываться дополнительно к направлению звонка о чем говорит строка приводящая строку в необходимый вид: Код REGEXP_REPLACE(dstchannel, "(?:SIP|OOH323|LOCAL|SCCP)/(?:FMPR|FMGL)?([a-zA-Z_]+)?.*", '\\1') as trunk тоесть получаеться когда разбираю звонок у меня получается следующая информация посредством которой надо определить тариф, зону и стоимость звонка в зону.: звонок на 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 небольшой, не могу утверждать как лучше сделать и собственно поэтому сюда и обратился....

Автор: Akina 27.8.2015, 10:45

Цитата(alligator @  27.8.2015,  11:13 )  у меня таблица zoneinfo содержит пример 14к записей * на кол-во направлений =4 и получим 56к записей Это копейки. Кабы речь шла о сотнях миллионов записей - вот тогда можно было бы кивать на их количество. Цитата(alligator @  27.8.2015,  11:13 ) Мне кажется что такое решение не раздувало бы базу, и было бы более оптимальным с точки зрения кол-ва записей. Оптимизировать размер ценой производительности и нормализации, усложняя логику, имхо глупо.

Автор: alligator 27.8.2015, 10:50

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

Автор: Akina 27.8.2015, 11:27

Цитата(alligator @  27.8.2015,  11:50 ) ожет таблице tariff-data сделать вместо 3-х полей isp,region,subregion одно поле zones и вносить туда zoneinfo uid Звучит разумно. Цитата(alligator @  27.8.2015,  11:50 ) а проверять FIND_IN_SET А вот это - нет. Нахрена где-то там собирать этот сет, чтобы потом его же парсить? почему нельзя по-человечески нормализовать данные и не плодить хитровывернутых геморроев?

Автор: alligator 27.8.2015, 11:47

Цитата(Akina @  27.8.2015,  12:27 ) Цитата(alligator @  27.8.2015,  11:50 ) а проверять FIND_IN_SET А вот это - нет. Нахрена где-то там собирать этот сет, чтобы потом его же парсить? почему нельзя по-человечески нормализовать данные и не плодить хитровывернутых геморроев?  Как я понимаю ваше предложение хранить каждый zoneinfo uid в отдельной строке по каждой зоне тарификации, правильно?

Автор: tzirechnoy 27.8.2015, 14:16

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

Автор: Akina 27.8.2015, 14:29

Цитата(alligator @  27.8.2015,  12:47 ) ваше предложение хранить каждый zoneinfo uid в отдельной строке по каждой зоне тарификации, правильно?  Конечно. А ещё точнее - я бы предложил провести анализ как положено и полностью нормализовать всю структуру.

Автор: alligator 27.8.2015, 15:46

Дополнил структуру, в итоге запрос получился таким: Код SELECT b.calldate,b.src, CONCAT(b.prefix, b.number) as dst , b.duration, b.billsec, b.billmin, b.trunk, td.price,  ROUND(CEIL(b.billmin)*td.price, 2) as bill, t.numbertype, i.ispname,s.subregionname,r.regionname FROM (SELECT h.calldate ,h.src,SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 1, 3 ) AS prefix ,SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 4, 7 ) AS number,h.duration,h.billsec , ROUND((CASE h.billsec WHEN 0 THEN 1 ELSE h.billsec END)/60, 2) as billmin, REGEXP_REPLACE(dstchannel, "(?:SIP|OOH323|LOCAL|SCCP)/(?:FMPR|FMGL)?([a-zA-Z_]+)?.*", '\\1') as trunk FROM  asteriskcdrdb.cdr h  WHERE  calldate BETWEEN  '2015-05-01 00:00:00' AND  '2015-05-31 23:59:59' AND LENGTH( h.src ) <=3 AND LENGTH( h.dst ) >3 AND h.disposition='ANSWERED') as b  inner join astcdr.codes  c on b.prefix=c.abcdef inner join astcdr.zoneinfo zi on zi.uid=c.zoneinfouid inner join astcdr.isp i on i.uid=zi.ispuid inner join astcdr.numtype t on t.uid=c.type inner join astcdr.region r on r.uid=zi.regionuid inner join astcdr.subregion s on s.uid=zi.subregionuid inner join astcdr.tariff tr on tr.trunk=b.trunk inner join astcdr.`tariff-data` td on td.tuid=tr.uid inner join astcdr.`tariff-zones` tz on tz.zone=zi.uid and tz.tduid=td.uid WHERE  b.number between c.start and c.end and b.trunk='Beeline' ORDER BY b.calldate DESC; Запрос стал отрабатывать чуток быстрее, но все же недостаточно быстро на текущий момент 43 секунды,  до этого было 52 =( Сервер на базе виртуальной машины 4 ядра Intel® Core™ i7-4820K CPU @ 3.70GHz\ 4gb ram \ HDD 7200 (физика не нагружена другими виртуалками) Как можно оптимизировать запрос? уж совсем медленно работает  ( P.S. когда проверяю некоторые запросы отдельно то работает практически мгновенно, но когда в один делаю долго Explain Во время выполнения запроса, процессор 100%

Автор: alligator 27.8.2015, 16:14

Цитата(tzirechnoy @  27.8.2015,  15:16 ) После этой фразы я дажэ понял, что Вы имели в виду, когда говорили, что не получится записать это в zoneinfo. Но с Вас таки довольно тяжэло понимать, так что лучшэ бы Вы приводили примеры реальных данных, на которых можно определить, что Вы имеете в виду и какие проблемы Вы видите. И да, в zoneinfo я предлагал указывать как раз индэкс тарифа направления. Их, типично, штук 5 по РФ, возможно ещё 1-5 по своей зоне и штук 150 по остальному миру. Если  всё достаточно маньячно -- то будет около 100 по РФ (на каждый субъект) 150 по остальному миру. Если всё совсем маньячно, и надо описывать в каждом регионе несколько локальных зон -- то будет 400 по РФ. Как видите, никаких 15k нет и в помине -- хотя 15k направлений, действительно, можэт быть (можэт быть и большэ). Ну и, 4 тарифных опцыи что на 500 тарифов по направлениям, что на 15k -- это совершэнно копеечная таблица, которая замечательно поместится в память (да и дажэ, вероятно, в кэш процэссора), и поиск в которой займёт максимум 100ns (десяток доступов к произвольной области паамяти, если она ещё не в кэшэ), а скорее и меньшэ. согласен что тяжело понимать, надо видеть всю структуру, выкладываю https://www.dropbox.com/sh/uwv45f1iqw1x9og/AADCbwUBTCwAimTtpNpZRrTIa?dl=0 P.S для работы запроса необходима MariaDB Если есть время глянуть и посоветовать как сделать лучше, мне бы это очень помогло......

Автор: alligator 28.8.2015, 09:51

Провел небольшие тесты Тестировал так - 36 сек Код SELECT b.calldate,b.src, CONCAT(b.prefix, b.number) as dst = 36 сек В таком виде - 19 сек Код SELECT b.calldate,b.src, CONCAT(b.prefix, b.number) as dst  FROM (SELECT h.calldate ,h.src,SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 1, 3 ) AS prefix ,SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 4, 7 ) AS number,h.duration,h.billsec , ROUND((CASE h.billsec WHEN 0 THEN 1 ELSE h.billsec END)/60, 2) as billmin, REGEXP_REPLACE(dstchannel, "(?:SIP|OOH323|LOCAL|SCCP)/(?:FMPR|FMGL)?([a-zA-Z_]+)?.*", '\\1') as trunk FROM  asteriskcdrdb.cdr h  WHERE  calldate BETWEEN  '2015-05-01 00:00:00' AND  '2015-05-31 23:59:59' AND LENGTH( h.src ) <=3 AND LENGTH( h.dst ) >3 AND h.disposition='ANSWERED') as b  inner join astcdr.codes  c on b.prefix=c.abcdef inner join astcdr.zoneinfo zi on zi.uid=c.zoneinfouid inner join astcdr.isp i on i.uid=zi.ispuid inner join astcdr.numtype t on t.uid=c.type inner join astcdr.region r on r.uid=zi.regionuid inner join astcdr.subregion s on s.uid=zi.subregionuid WHERE  b.number between c.start and c.end and b.trunk='Beeline' ORDER BY b.calldate DESC; так - 9 сек Код SELECT b.calldate,b.src, CONCAT(b.prefix, b.number) as dst  FROM (SELECT h.calldate ,h.src,SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 1, 3 ) AS prefix ,SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 4, 7 ) AS number,h.duration,h.billsec , ROUND((CASE h.billsec WHEN 0 THEN 1 ELSE h.billsec END)/60, 2) as billmin, REGEXP_REPLACE(dstchannel, "(?:SIP|OOH323|LOCAL|SCCP)/(?:FMPR|FMGL)?([a-zA-Z_]+)?.*", '\\1') as trunk FROM  asteriskcdrdb.cdr h  WHERE  calldate BETWEEN  '2015-05-01 00:00:00' AND  '2015-05-31 23:59:59' AND LENGTH( h.src ) <=3 AND LENGTH( h.dst ) >3 AND h.disposition='ANSWERED') as b  inner join astcdr.codes  c on b.prefix=c.abcdef inner join astcdr.zoneinfo zi on zi.uid=c.zoneinfouid WHERE  b.number between c.start and c.end and b.trunk='Beeline' ORDER BY b.calldate DESC; Добавил индексы + добавил (FORCE INDEX), убрал 1 join получилось 9 секунд (возможно сделать хотябы до 3 секунд?) Код SELECT SQL_NO_CACHE b.calldate,b.src, CONCAT(b.prefix, b.number) as dst , b.duration, b.billsec, b.billmin, b.trunk, td.price,  ROUND(CEIL(b.billmin)*td.price, 2) as bill, i.ispname,s.subregionname,r.regionname FROM (SELECT h.calldate ,h.src,SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 1, 3 ) AS prefix ,SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 4, 7 ) AS number,h.duration,h.billsec , ROUND((CASE h.billsec WHEN 0 THEN 1 ELSE h.billsec END)/60, 2) as billmin, REGEXP_REPLACE(h.dstchannel, "(?:SIP|OOH323|LOCAL|SCCP)/(?:FMPR|FMGL)?([a-zA-Z_]+)?.*", '\\1') as trunk FROM  asteriskcdrdb.cdr h FORCE INDEX (calldate) WHERE  h.calldate BETWEEN  DATE'2015-05-01' AND  DATE'2015-05-31' AND LENGTH( h.src ) <=3 AND LENGTH( h.dst ) >3 AND h.disposition='ANSWERED') as b  inner join astcdr.codes  c on b.prefix=c.abcdef inner join astcdr.zoneinfo zi on zi.uid=c.zoneinfouid inner join astcdr.isp i on i.uid=zi.ispuid inner join astcdr.region r on r.uid=zi.regionuid inner join astcdr.subregion s on s.uid=zi.subregionuid inner join astcdr.tariff tr on tr.trunk=b.trunk inner join astcdr.`tariff-data` td on td.tuid=tr.uid inner join astcdr.`tariff-zones` tz on tz.zone=zi.uid and tz.tduid=td.uid WHERE  b.number between c.start and c.end and b.trunk='Beeline' ORDER BY b.calldate DESC

Автор: tzirechnoy 28.8.2015, 21:53

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

Автор: tzirechnoy 29.8.2015, 11:17

Ага, нашёл. Кривизну оптимизатора можно нагнуть с помощью хранимки. Создаю функцыю: Код delimiter // CREATE FUNCTION get_cuid(prefix smallint(3), num int(7)) RETURNS int(15) DETERMINISTIC READS SQL DATA BEGIN   DECLARE maxstart int(7);   DECLARE res int(15);   SELECT max(cdir.start)      FROM codes cdir      WHERE cdir.abcdef = prefix AND cdir.start < num   LIMIT 1   INTO maxstart;   SELECT uid     FROM codes     WHERE codes.abcdef = prefix AND codes.start = maxstart   LIMIT 1   INTO res;   RETURN res; END // delimiter ; Потом таким запросом выбираю: Код SELECT b.calldate, b.src, CONCAT(b.prefix, b.number) AS dst, b.duration,        b.billsec, b.billmin, b.dstchannel, t.numbertype, i.ispname,        s.subregionname, r.regionname,        (b.prefix=c.abcdef AND b.number BETWEEN c.start AND c.end)            AS code_is_valid    FROM     (SELECT h.calldate, h.src,             SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 1, 3 )                AS prefix,             SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 4, 7 )                AS number,             h.duration, h.billsec,             ROUND((CASE h.billsec WHEN 0 THEN 1 ELSE h.billsec END)/60, 2)                AS billmin,             dstchannel,             (SELECT get_cuid(prefix,number))                AS codeuid             FROM  cdr h             WHERE  calldate BETWEEN  '2015-05-01 00:00:00'                                      AND  '2015-05-31 23:59:59'                    AND LENGTH( h.src ) <=3                    AND LENGTH( h.dst ) >3                    AND h.disposition='ANSWERED'                    AND h.dstchannel LIKE '%/Beeline%'      ) AS b      INNER JOIN codes c ON b.codeuid=c.uid      INNER JOIN zoneinfo zi ON zi.uid=c.zoneinfouid      INNER JOIN isp i ON i.uid=zi.ispuid      INNER JOIN numtype t ON t.uid=c.type      INNER JOIN region r ON r.uid=zi.regionuid      INNER JOIN subregion s ON s.uid=zi.subregionuid    ORDER BY b.calldate DESC И всё ужэ нормально работает. Ну, как нормально, без хранимой оно было бы в два раза быстрее, но хоть так. Да, поскольку у меня сейчас нет 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 не очень одобряет и не все базы понимают. Но переписывать как правильно мне лень, да и так вполне идиоматично получилось.

Автор: alligator 29.8.2015, 18:03

Цитата(tzirechnoy @  29.8.2015,  12:17 ) И всё ужэ нормально работает. Ну, как нормально, без хранимой оно было бы в два раза быстрее, но хоть так. сколько получилось время выполнения? Цитата(tzirechnoy @  29.8.2015,  12:17 ) А, и привёл всё в одну базу, я ещё запощу по этому поводу когда соберусь с мыслями. ок, выкладывай как будет возможность потестируем. обновил архив с дампом. потестил еще: Цитата(tzirechnoy @  29.8.2015,  12:17 ) И такое использование BETWEEN для времени не всегда корректно (зависит от СУБД) -- лучшэ писать >= 'сегодня 00:00' AND < 'завтра 00:00' Код WHERE  b.number >= c.start and b.number <= c.end and b.trunk='Beeline' ORDER BY b.calldate DESC на моем запросе во втором select получилось 11 секунд Цитата(tzirechnoy @  29.8.2015,  12:17 ) И да, тройной индэкс для abcdef, start, end безсмысленен -- можно оставить только первые два поля. То есть третье вообще ни в каких логичных случаях использоваться не будет. убрал третье поле время с 9 секунд сразу поднялось до 36, пока вернул обратно.... это выполнилось мгновенно Код SELECT h.calldate ,h.src,SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 1, 3 ) AS prefix ,SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 4, 7 ) AS number,h.duration,h.billsec , ROUND((CASE h.billsec WHEN 0 THEN 1 ELSE h.billsec END)/60, 2) as billmin, REGEXP_REPLACE(h.dstchannel, "(?:SIP|OOH323|LOCAL|SCCP)/(?:FMPR|FMGL)?([a-zA-Z_]+)?.*", '\\1') as trunk FROM  asteriskcdrdb.cdr h FORCE INDEX (calldate) WHERE  h.calldate BETWEEN  DATE'2015-05-01' AND  DATE'2015-05-31' AND LENGTH( h.src ) <=3 AND LENGTH( h.dst ) >3 AND h.disposition='ANSWERED' добавляю к запросу  Код inner join astcdr.codes  c on b.prefix=c.abcdef inner join astcdr.zoneinfo zi on zi.uid=c.zoneinfouid Код SELECT SQL_NO_CACHE b.calldate,b.src, CONCAT(b.prefix, b.number) as dst , b.duration, b.billsec, b.billmin, b.trunk FROM (SELECT h.calldate ,h.src,SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 1, 3 ) AS prefix ,SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 4, 7 ) AS number,h.duration,h.billsec , ROUND((CASE h.billsec WHEN 0 THEN 1 ELSE h.billsec END)/60, 2) as billmin, REGEXP_REPLACE(h.dstchannel, "(?:SIP|OOH323|LOCAL|SCCP)/(?:FMPR|FMGL)?([a-zA-Z_]+)?.*", '\\1') as trunk FROM  asteriskcdrdb.cdr h FORCE INDEX (calldate) WHERE  h.calldate BETWEEN  DATE'2015-05-01' AND  DATE'2015-05-31' AND LENGTH( h.src ) <=3 AND LENGTH( h.dst ) >3 AND h.disposition='ANSWERED') as b  inner join astcdr.codes  c on b.prefix=c.abcdef inner join astcdr.zoneinfo zi on zi.uid=c.zoneinfouid получилось 9 сек

Автор: tzirechnoy 29.8.2015, 19:39

1) Никогда не указывайте напрямую имя базы данных, если не пишэте софт для управления кластером баз данных. Если нужны какие-то внешние данные, и СУБД можэт их получить -- сделайте локальный (в своей БД) альяс/ссылку/что там СУБД позволяет. 2) А зачем Вы, собственно, сделали две DATABASE? Вам мало проблем? Люди, которые будут это деплоить, за такое и побить могут. 3) Никогда не используйте + или - или какие-то ещё знаки операцый в именах таблиц. И русских букв не используйте. Вообще, используйте только a-zA-Z0-9_ . И не начинайте имя с цыфры. Людям, которые ковыряют потроха движков и делают какие-то метабазыданных иногда можно использовать какие-нибудь @ или % или $ для служэбных цэлей, прикладному программисту -- нет.

Автор: tzirechnoy 29.8.2015, 20:09

Цитата сколько получилось время выполнения? 0.35c. А самому запустить тот запрос? Цитата ок, выкладывай как будет возможность потестируем. Что выкладывать? Базу с интэгрированной таблицэй cdr? Ну, можно, но мне лень пока. Учитывая, что всё, что нужно -- перенести одну таблицу в основную базу. Да и на скорость влиять не должно -- это общая такая рекомендацыя. Цитата WHERE  b.number Для времени, я сказал. И BETWEEN строго эквивалентно a >= begin AND a <= end, тут уж это чисто синтаксическая разница. А для времени надо полуоткрытый интэрвал. Цитата добавляю к запросу  Код inner join astcdr.codes  c on b.prefix=c.abcdef inner join astcdr.zoneinfo zi on zi.uid=c.zoneinfouid Не верю. У меня минут 15 выполнялось -- оно и понятно, там несколько миллионов записей в результсете, поскольку на каждую запись из cdr выдастся всё с совпадающим ABC/DEF префиксом -- это в среднем по полторы тысячи записей. И это пото ещё отсортировать. Добавлено через 4 минуты и 44 секунды Цитата И да, тройной индэкс для abcdef, start, end безсмысленен -- можно оставить только первые два поля. Впрочем, да, тут я подумал -- для твоего варианта, с INNER JOIN, оно можэт ускорить... В среднем вдвое, наверное. Но только за счёт того, что этот поиск не будет выполняться нормально.

Автор: alligator 31.8.2015, 17:01

Цитата(tzirechnoy @  29.8.2015,  20:39 ) 2) А зачем Вы, собственно, сделали две DATABASE? Вам мало проблем? Люди, которые будут это деплоить, за такое и побить могут. Я бы и сделал в одной базе. сервер телефонии просто пишет в отдельную базу, поэтому пока решил оставить так.... а вообще думал синхронизировать записи в отдельную таблицу, саму биллинг систему держать не на сервере телефонии Цитата(tzirechnoy @  29.8.2015,  20:39 ) сделайте локальный (в своей БД) альяс/ссылку/что там СУБД позволяет. можно по подробней....? Цитата(tzirechnoy @  29.8.2015,  20:39 ) 3) Никогда не используйте + или - или какие-то ещё знаки операцый в именах таблиц. Полностью согласен. Цитата(tzirechnoy @  29.8.2015,  21:09 ) 0.35c. А самому запустить тот запрос? Только добрался до компа, до этого небыло возможности.... Потестировал твой запрос и более детально посмотрел код, спасибо что нашел время! Работает быстро, но в хранимке проверяется только поле start, а это не очень подходит для биллинга, может неверно считать... конечно понятно что это только пример обхода бага оптимизатора... Так вроде, точнее.... Код CREATE DEFINER=`root`@`localhost` FUNCTION `get_cuid`(`prefix` SMALLINT(3), `num` INT(7)) RETURNS int(15)     READS SQL DATA     DETERMINISTIC BEGIN   DECLARE maxstart int(7);   DECLARE res int(15);    SELECT max(cdir.start)      FROM codes cdir      WHERE cdir.abcdef = prefix AND cdir.start <= num   LIMIT 1   INTO maxstart;   SELECT uid     FROM codes     WHERE codes.abcdef = prefix and codes.start=maxstart  AND codes.end >= num   LIMIT 1   INTO res;   RETURN res; END Запрос занял 0.3904 сек убираю из запроса: Код      INNER JOIN numtype t ON t.uid=c.type Explain на этом поле показывает ALL следовательно единственный для этой таблицы primary key не используется Запрос занял 0.0129 сек Цитата(tzirechnoy @  29.8.2015,  21:09 ) Не верю. У меня минут 15 выполнялось Да , уже потом заметил что забыл добавить WHERE......etc...

Автор: tzirechnoy 31.8.2015, 20:20

Цитата Работает быстро, но в хранимке проверяется только поле start, а это не очень подходит для биллинга, может неверно считать... А если поле end в полученном результате неправильное -- то либо у тебя номер не попал ни в какой промежуток, либо у тебя пересекающиеся промежутки. И тот и другой случай во-первых нехорошы для биллинга, а во-вторых требуют ручного вмешательства. У меня для обхода этого в результате было поле code_is_valid (оно должно было проверяться в приложэнии). Твой вариант -- тожэ вполне вариант. Но в любом случае непопавшые никуда звонки надо как-то обрабатывать, и, для начала, их выбрать (например, таки добавив поле code_is_valid и сделав везде LEFT OUTER JOIN вместо INNER JOIN). Цитата Запрос занял 0.3904 сек Цитата Запрос занял 0.0129 сек Непохожэ на правду. Скорее, в первом случае он с диска читался. У меня строго одинаковые 0.35с получились в обоих случаях. Да и чему там тормозить: в numbertype две записи, они и безо всякого ключа в столько жэ сравнений фильтруются. Ну и, кстати, попробовал тожэ самое в postgres (без stored function, просто двумя запросами в коде) -- оно сразу втрое быстрее, чем такой жэ запрос mysql с stored function стало.

Автор: alligator 1.9.2015, 09:37

Цитата(tzirechnoy @  31.8.2015,  21:20 ) Ну и, кстати, попробовал тожэ самое в postgres (без stored function, просто двумя запросами в коде) -- оно сразу втрое быстрее, чем такой жэ запрос mysql с stored function стало. Почитал про postgresql, заинтересовало ..... поставил но пока ищу как дамп импортировать

Автор: tzirechnoy 1.9.2015, 10:35

Руками импортировать, разумеется. С отдельно сохранил весь DDL, отдельно DML. DDL переписывал довольно заметно. int(3) -> smallint, int(7) -> int, int(15) -> bigint. ADD KEY -> create index, дажэ макрос в виме записал, поскольку их много было. Да, весьма удобно тэстировать в транзакцыи. Т.е.  Код begin; \i pg_ddl.sql ^C rollback;begin; После многочисленных экспериментов с INSERT/DELETE полной таблицы жэлательно сделать vacuum full verbose analyze -- поскольку старые версии записей с диска удалятся не так чтобы скоро дажэ в версиях, в которых есть autovacuum. Ну и да, плюсы постгрэсса всё-таки не в скорости. Со скоростью всё довольно неплохо, в сложных случаях -- так и лучшэ, чем в mysql, но всё-таки дело не в этом. Добавлено через 11 минут и 28 секунд Цитата можно по подробней....? Честно говоря, посмотрел на mysql в очередной раз -- действительно у него нет ничего хорошэго чтобы делать ссылки внутри. А т.н. "базы данных" очень похожы на то, что в других СУБД называется "scheme" или "namespace", потому действительно не настолько это всё требуется переименовывать. Точнее, можэт и требуется -- но всё равно нет нормальных способов. Просто как-то очень непринято для одной задачи выделять две базы данных. Ещё менее принято фиксировать имя базы данных -- поскольку, например, типичный случай -- это на одном сервере один боевой вариант и пачка тэстовых, и отличаются только именами. Впрочем, конкретно в твоём случае, возможно, что ENGINE=federated было бы во многих смыслах более общим решэнием, и позволило действительно разнести cdr и ведение счёта по разным серверам.

Автор: alligator 1.9.2015, 12:44

В общем сделал базу на PostgreSQL, изначально конечно тяжеловато..... много незнакомого, но результат впечатлил.... база + primary key + индексы с таким запросом в лоб: Код SELECT b.calldate,b.src, CONCAT(b.prefix, b.number) as dst , b.duration, b.billsec, b.billmin, b.trunk  FROM (SELECT h.calldate ,h.src,SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 1, 3 ) AS prefix ,SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 4, 7 ) AS number,h.duration,h.billsec , ROUND((CASE h.billsec WHEN 0 THEN 1 ELSE h.billsec END)/60, 2) as billmin, REGEXP_REPLACE(h.dstchannel, '(?:SIP|OOH323|LOCAL|SCCP)/(?:FMPR|FMGL)?([a-zA-Z_]+)?.*', '\1') as trunk FROM  cdr h WHERE  h.calldate BETWEEN  DATE'2015-05-01' AND  DATE'2015-05-31' AND LENGTH( h.src ) <=3 AND LENGTH( h.dst ) >3 AND h.disposition='ANSWERED') as b inner join codes  c on CAST(b.prefix AS smallint) = c.abcdef inner join zoneinfo zi on zi.uid=c.zoneinfouid inner join isp i on i.uid=zi.ispuid inner join region r on r.uid=zi.regionuid inner join subregion s on s.uid=zi.subregionuid WHERE  CAST(b.number AS integer) between c.start and c.end and b.trunk='Beeline' ORDER BY b.calldate DESC 700 ms если целиком: Код SELECT b.calldate,b.src, CONCAT(b.prefix, b.number) as dst , b.duration, b.billsec, b.billmin, b.trunk , td.price,  ROUND(CEIL(b.billmin)*td.price, 2) as bill, i.ispname,s.subregionname,r.regionname FROM (SELECT h.calldate ,h.src,SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 1, 3 ) AS prefix ,SUBSTRING( RIGHT( LPAD( h.dst, 12,  '9' ) , 10 ) , 4, 7 ) AS number,h.duration,h.billsec , ROUND((CASE h.billsec WHEN 0 THEN 1 ELSE h.billsec END)/60, 2) as billmin, REGEXP_REPLACE(h.dstchannel, '(?:SIP|OOH323|LOCAL|SCCP)/(?:FMPR|FMGL)?([a-zA-Z_]+)?.*', '\1') as trunk FROM  cdr h WHERE  h.calldate BETWEEN  DATE'2015-05-01' AND  DATE'2015-05-31' AND LENGTH( h.src ) <=3 AND LENGTH( h.dst ) >3 AND h.disposition='ANSWERED') as b     LEFT OUTER JOIN codes  c on CAST(b.prefix AS smallint) = c.abcdef     LEFT OUTER JOIN zoneinfo zi on zi.uid=c.zoneinfouid     LEFT OUTER JOIN isp i ON i.uid=zi.ispuid     LEFT OUTER JOIN region r ON r.uid=zi.regionuid     LEFT OUTER JOIN subregion s ON s.uid=zi.subregionuid     LEFT OUTER JOIN tariff tr on tr.trunk=b.trunk     LEFT OUTER JOIN tariff_data td on td.tuid=tr.uid     LEFT OUTER JOIN tariff_zones tz on tz.zone=zi.uid and tz.tduid=td.uid WHERE  CAST(b.number AS integer) between c.start and c.end and b.trunk='Beeline' ORDER BY b.calldate DESC 10 секунд

Автор: tzirechnoy 1.9.2015, 18:04

Цитата  индексы с таким запросом в лоб: Э-э-э. Ну, надо заметить, что если интересует скорость -- то таки или пытаться сделать cube и GiST-индэкс на abcdef||start/abcdef||end (не знаю точно как, ни разу с ними не работал) или таки делать почти тот жэ самый хак для выборки одного значения по индэксу: Код                   (SELECT c.uid                      FROM codes  c                      WHERE                          SUBSTRING( LPAD( h.dst, 12,  '9' ) , 3, 3)::bigint                               = c.abcdef                          AND SUBSTRING( LPAD( h.dst, 12,  '9' ), 6, 7 )::bigint                               > c.start                         ORDER BY c.abcdef, c.start DESC LIMIT 1)                      AS codeuid Это, собственно, логично если знать как индэксы работают и практически неизбежно. Разница с mysql -- в том, что не требуется оборачивать это в функцыю (у mysql без оборачивания планировщик херню делал). Цитата WHERE  CAST(b.number AS integer) between c.start and c.end  Слушай, ну это надо в JOIN CONDITION записывать. И, если с INNER JOIN это был такой синтаксический кунштюк (ну, физически серверу никакой разницы, где это условие написано), то с LEFT OUTER JOIN -- это обязательно должно быть в JOIN CONDITION. И да, наличие измеримой разницы между первым и вторым -- говорит, что что-то не то с индэксами. Там по идее всё, что ты добавлял -- это выборка одного значения по ключу, это несопоставимое время должно занимать. Ну, у меня оно отличается всё-таки -- 1.6с и 1.8с, но незначительно.

Powered by Invision Power Board (http://www.invisionboard.com)
© Invision Power Services (http://www.invisionpower.com)