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

Тема в разделе "PHP", создана пользователем Hafner, 4 май 2010.

Статус темы:
Закрыта.
Модераторы: latteo
  1. Hafner

    Hafner Постоялец

    Регистр.:
    9 июл 2008
    Сообщения:
    79
    Симпатии:
    30
    И так, имеем следующее:
    Две базы данных:
    1. имена пользователей со всей личной инфой (пароль, мыло, icq и т.п.)
    2. статистические данные по пользователям за каждый день (коннекты, заработанные поинты, на какой площадке получены и т.п.)
    Таким образом за день по каждому пользователю может собраться несколько записей, так как площадок много и для каждой заносятся свои коннекты и поинты.
    Во вторую таблицу данные поступают из игрового приложения, нам же нужно эти данные обработать и представить пользователю сайта в нужном формате, а именно:
    • необходим расчет рейтинга каждого пользователя и упорядочивание всех по этому рейтингу. Сейчас рейтинг рассчитывается пхп-скриптом путем выборки всех полей из 2-ой таблицы с группировкой по userid, дальнейший расчет рейтинга при каждом проходе цикла, занесение полученного рейтинга в массив с последующей нужной сортировкой. Данная схема очень медленная, а с каждым днем 2-ая, да и первая таблицы наполняются всё больше и больше.
    Прошу помочь с оптимизацией. Буду благодарен за любые идеи! В уме у меня пока крутится только кеширование или перенос расчета рейтинга в само игровое приложение, чтобы в базу оно кидало уже готовый результат для каждого пользователя. А далее обычный селект с сортировкой по рейтингу и выводом результатов. Но такая схема скажется на производительности игрового приложения, поэтому хочется решить это иным способом.
     
  2. saen

    saen

    Регистр.:
    6 авг 2006
    Сообщения:
    756
    Симпатии:
    129
    Надо глянуть сами запросы, а также структуру таблиц БД. Возможно неудачно проставлены индексы полей, по которым производится выборка(если конечно они вообще проставлены:)). Как вариант можно использовать встроенные shmop-функции в php для кеширования. Если данные очень большие, имеет смысл переходить на redis либо на memcached
     
    Hafner нравится это.
  3. Abliganto

    Abliganto Постоялец

    Регистр.:
    30 ноя 2009
    Сообщения:
    111
    Симпатии:
    46
    К слову про кеширование: насколько я понял, проблема не в хранении данных, а в самом алгоритме рассчёта. Да, мемори кеш ускорит доступ к некоторым данным, но это скорей нужно для фронтэнда. Да и нельзя там хранить важных данных, иначе можно всё потерять.
    Обновлять рейтинг нужно только изменившимся пользователям (совершившим действия). Если есть возможность - использовать инкреметное добавление (чтобы не пересчитывать каждый раз все статсы, иначе это вас быстро погубит). Если в ваших 2х таблицах будет несколько миллионов записей и вы будете делать по ним группировки - это быстро погубит ваш сервер. А лишние индексы возможно будут тормозить добавление новых данных.
    Если нужна конкретика, выкладывайте необходимые данные и задавайте конкретные вопросы ;)
     
  4. saen

    saen

    Регистр.:
    6 авг 2006
    Сообщения:
    756
    Симпатии:
    129
    1) Redis к примеру может хранить информацию не только в распределенной памяти, но и в файлах. Так что вероятность потери информацию оттуда не больше чем из БД. А скорость считывания информации в разы больше.
    2) Вывод рейтинга пользователей это по-вашему не фронтенд?
     
  5. Abliganto

    Abliganto Постоялец

    Регистр.:
    30 ноя 2009
    Сообщения:
    111
    Симпатии:
    46
    Вы хотите сказать что рейтинг рассчитывается сейчас в реалтайме? Я и говорю, что фронтэнд пусть хранит в кеше, а бэкэнд рассчитывает рейтинг и сохраняет к примеру в БД в отдельную таблицу рейтинга. Имхо тут всё просто :)

    Насколько я понял, у ТС проблема в самом рассчёте
     
  6. saen

    saen

    Регистр.:
    6 авг 2006
    Сообщения:
    756
    Симпатии:
    129
    Тогда оптимизация в рефакторинге кода и возможно еще поможет установка eaccelerator
     
  7. Hafner

    Hafner Постоялец

    Регистр.:
    9 июл 2008
    Сообщения:
    79
    Симпатии:
    30
    Немного дополню и опишу как сейчас работает:
    Пользователь сайта жмакает на кнопку "Статистика" ---> ему отображается список пользователей отсортированных по рейтингу - именно при каждом обращении юзера к статистике производится расчет рейтинга.
    Тут я привел только часть всего механизма, там помимо этого расчета рейтинга ещё куча вычислений идет, которые тоже используют запросы в бд, поэтому очень медленно страничка обновляется.
    Вот я и хотел услышать варианты оптимизации. Пока для себя уяснил следующие пункты:
    • создать темповую табличку для кеша и её периодически рефрешить;
    • проверить и настроить индексацию таблиц.

    Сам код и запросы сейчас не могу выложить, только вечером доберусь до исходников.
     
  8. saen

    saen

    Регистр.:
    6 авг 2006
    Сообщения:
    756
    Симпатии:
    129
    Вообще для таких вещей как оптимизация надо делать профилирование, во многих php IDE присутсвует. Делаешь профилирование -> находишь узкие места -> делаешь отладку и рефакторинг. Хотя я подозреваю что оснвное время и ресурсы у тебя кушает выборка из БД, поэтому надо делать кеширование
     
  9. shvonder_85

    shvonder_85 Создатель

    Регистр.:
    3 май 2010
    Сообщения:
    12
    Симпатии:
    3
    Что каcается mysql, то нужно проанализировать все выполняющиеся в коде запросы. При вызове EXPLAIN для любого селекта в поле key нужно увидеть имя какого-нибудь индекса. В possible_keys mysql показывает все доступные индексы. Часто бывает, что mysql не использует существующие индексы. Про индексы можно почитать тут:

    http://www.m
     
  10. Hafner

    Hafner Постоялец

    Регистр.:
    9 июл 2008
    Сообщения:
    79
    Симпатии:
    30
    Праздники закончились, продолжаю тему. Вобщем сам скрипт расчета рейтинга stats.php выглядит следующим образом:
    PHP:
    <?
        
    $query_pg mysql_query("SELECT count(*) FROM (SELECT dbid FROM ".$config['stats_players']." WHERE serverip = '$srv' GROUP BY dbid) AS temp") or die(mysql_error());
        
    $r_pg=mysql_fetch_row($query_pg);
        
    $total=$r_pg[0];
        if( isset( 
    $_GET['pg'] ) ) $pg intval$_GET['pg'] );
        if( !
    $pg OR $pg $pg 1;
        
    $pagetotal 50;    
        
    $startselect = ($pg-1)*$pagetotal;
        
    $home_link "?do=stats&srv=$srv&type=players";
        
    $query2 mysql_query("SELECT MAX(streak_kills), MAX(streak_deaths), SUM(kills), SUM(headshotkills), SUM(deaths), SUM(ffkills), SUM(ffdeaths), SUM(ct_team), SUM(t_team), SUM(connections), last_ip, dbid, SUM(".$config['stats_players'].".online) AS online, ".$config['players_table'].".player_nick AS nick, MAX(".$config['stats_players'].".last_time) AS lasttime, SUM(wins) FROM ".$config['stats_players'].", ".$config['players_table']." WHERE serverip = '$srv' AND ".$config['stats_players'].".dbid = ".$config['players_table'].".id GROUP BY nick") or die(mysql_error());
        
    $title_stats "
            <table class='tbl_content'>
                <tr>
                    <td style='border:0px;'>
                        <table cellspacing='1' width='100%'>
                            <tr class='tbl_title'>
                                <td width=\"5%\" align=\"center\">Rank</td>
                                <td width=\"34%\" align=\"center\">Player</td>
                                <td width=\"7%\" align=\"center\">K:D</td>
                                <td width=\"7%\" align=\"center\">HS%</td>
                                <td width=\"13%\" align=\"center\">K.Streak</td>
                                <td width=\"13%\" align=\"center\">D.Streak</td>
                                <td width=\"13%\" align=\"center\">Online</td>
                                <td width=\"13%\" align=\"center\">Skill</td>
                            </tr>
        "
    ;
        if(
    mysql_num_rows($query2) == 0) {
            
    $data .= "
                <tr>
                    <td width=\"100%\" colspan=\"8\" align=\"center\">- данные отсутствуют -</td>
                </tr>
            "
    ;
        } else {
            while (
    $result2 mysql_fetch_assoc($query2)) {
                
    $auth=$result2['nick'];
                
    $userid=$result2['dbid'];
                if(
    $result2['SUM(kills)'] <= 100) { 
                    
    $skill '?';
                } else {
                    
    $now date(U);
                    
    $nAct = ($now $result2['lasttime'])/3600;
                    if (
    $nAct 24) { $activity 100; }
                    elseif (
    $nAct 744) { $activity 0; }
                    else { 
    $activity 2400/$nAct; }
                    
    $skill=((($result2['SUM(kills)']+$result2['SUM(ffkills)'])*60/$result2['online']) + (2*($result2['SUM(kills)']+$result2['SUM(ffkills)'])/($result2['SUM(deaths)']+$result2['SUM(ffdeaths)'])) + ($result2['MAX(streak_kills)']/$result2['MAX(streak_deaths)']) + (5*$result2['SUM(headshotkills)']/($result2['SUM(kills)']+$result2['SUM(ffkills)'])) + (2*$result2['SUM(wins)']/($result2['SUM(ct_team)'] + $result2['SUM(t_team)'])))*$activity;                            
                    
    $skill=round($skill);
                }
                
    $kd kdrate(($result2['SUM(kills)']+$result2['SUM(ffkills)']),($result2['SUM(deaths)']+$result2['SUM(ffdeaths)']));
                
    $hspercent hsper(($result2['SUM(kills)']+$result2['SUM(ffkills)']),$result2['SUM(headshotkills)']);
                
    $res GeoIP_record_by_addr($gi,$result2['last_ip']); 
                if(!
    $res->country_code$res->country_code "err";
                
    $flag $res->country_code.".gif";
                
    $array_players[$auth] = array($userid,$flag,$kd,$hspercent,$result2['MAX(streak_kills)'],$result2['MAX(streak_deaths)'],$result2['online'],$skill);
            }
             }
        
    uasort($array_players"cmp");
        
    $array_players array_reverse($array_players);
        
    $a 1;
        
    $count_pl count($array_players);
        
    $navy3 "<tr><td colspan=\"8\" align=\"right\" style=\"padding:10px;font-size:10px;border:0px;\">".build_navigation($pg,$count_pl,$home_link,$pagetotal)."</td></tr>";
        
    $data $navy3;
        foreach(
    $array_players as $key => $type) {
            if((
    $a <= $pg*$pagetotal) && ($a >= (($pg 1) * $pagetotal 1))) { 
                if(
    $type[7] == '?')
                    
    $n "-";
                else
                    
    $n $a;                        
                
    $data .= "<tr><td width=\"5%\" align=\"center\">".$n."</td><td width=\"34%\" align=\"left\"><img style=\"vertical-align:middle;\" src=\"limewizard/_images/flags/".$type[1]."\" alt=\"\" />&nbsp;<a href=\"limewizard.php?do=stats&srv=$srv&type=players&id=$type[0]\" alt=\"$key\">".htmlspecialchars($key)."</a></td>";
                foreach(
    $type as $v => $array_players) {
                    if(
    $v 1) { 
                        if(
    $v == 6) {
                            
    $data .= "<td align=\"center\">".compacttime($array_players)."</td>";
                        } elseif(
    $v == 2) {
                            
    $data .= "<td align=\"center\">".commify($array_players)."</td>";
                        } elseif(
    $v == 3) {
                            
    $data .= "<td align=\"center\">".commify($array_players)."%</td>";
                        } else {
                            
    $data .= "<td align=\"center\">".$array_players."</td>";
                        }
                    }
                }
                
    $data .= "</tr>";
            }
            
    $a++;
        }
        if(
    $data == '') {
            
    $data .= "
                <tr>
                    <td width=\"100%\" colspan=\"8\" align=\"center\">- ничего не найдено -</td>
                </tr>
            "
    ;
        }
        
    $footer_stats "
            </table></td></tr></table>
        "
    ;
        
    $center_section $title_stats.$data.$navy3.$footer_stats;
    ?>
    Тут как раз из базы вытаскивается всё, рассчитывается рейтинг, загоняется в массив, сортируется и потом уже форичем осуществляется постраничная навигация. Вот тут интересно ваше мнение, может что-то стоит оптимизировать(?), так как, если база разрастется, то постоянно селектить все данные с последующей обработкой в массиве, имхо, не удачно.

    + скрипт, который выводит информацию из stats.php и добавляет свои несколько блоков на страницу, осуществляя дополнительные запросы в бд:
    PHP:
    <?

        
    //####################################################################################################################
        // BLOCK 2
        //####################################################################################################################
        
    $query4=mysql_query("SELECT player_nick, player_ip, online FROM ".$config['players_table']." WHERE 1=1 ORDER BY online DESC LIMIT 5") or die(mysql_error());
        
    $data '';
        
        if(
    mysql_num_rows($query4) == 0) {
            
    $menu2 '';
        } else {
            while(
    $result4=mysql_fetch_assoc($query4)) {
                
    $player $result4[player_nick];
                if(
    strlen($player) > 20) {
                    
    $player substr($player020).'...';
                }
                
    $res GeoIP_record_by_addr($gi,$result4['player_ip']); 
                if(!
    $res->country_code)
                    
    $res->country_code "err";
                
    $flag $res->country_code.".gif";
        
                
    $data2 .= '
                    <tr>
                        <td width="80%"><img style="vertical-align:middle;" src="limewizard/_images/flags/'
    .$flag.'" alt="" />&nbsp;'.$player.'</td><td width="20%" align="right">'.compacttime($result4[online]).'</td>
                    </tr>
                '
    ;
            }
            
    $menu2 '
                <div class="block">
                    <h5 class="blocksubhead smaller">
                        <img alt="" src="limewizard/_templates/'
    .$template.'/images/widget-poll.png" title="Statistic"/>
                        Best Online
                    </h5>
                    <div id="c_blog_comments" class="blockbody">
                        <ul id="latestcomments" class="blockrow">
                            <li class="smallfont">
                                        <table width="100%" border="0">
                                            '
    .$data2.'
                                        </table>
                            </li>
                        </ul>
                    </div>
                </div>
                <div class="underblock"></div>
            '
    ;
        }

        
    //####################################################################################################################
        // BLOCK 3
        //####################################################################################################################
        
    if($config['servers_kz'] != "") {
            
    $srv_num substr_count($config['servers_kz'], ",") + 1;
            if(
    $srv_num >= 2) {
                
    $srv_custom explode(",",$config['servers_kz']);
                
    $sqlcond "";
                foreach(
    $srv_custom as $var) {
                    
    $sqlcond .= " AND ".$config['servers_table'].".id != ".$var;
                }
            }
        }

        
    $query5 mysql_query("SELECT MAX(streak_kills), MAX(streak_deaths), SUM(kills), SUM(headshotkills), SUM(deaths), SUM(ffkills), SUM(ffdeaths), SUM(ct_team), SUM(t_team), SUM(connections), last_ip, SUM(".$config['stats_players'].".online) AS online, ".$config['players_table'].".player_nick AS nick, MAX(".$config['stats_players'].".last_time) AS lasttime, SUM(wins) FROM ".$config['stats_players'].", ".$config['players_table'].", ".$config['servers_table']." WHERE ".$config['stats_players'].".dbid = ".$config['players_table'].".id".$sqlcond." GROUP BY nick") or die(mysql_error());
        
    $total_players=mysql_num_rows($query5);
        if(
    $total_players == 0) {
            
    $menu3 '';
        } else {
            while(
    $result5 mysql_fetch_array($query5)) {
                
    $auth_name=htmlspecialchars($result5['nick'],ENT_QUOTES);
            
                if(
    $result5['SUM(kills)'] <= 100) { 
                    
    $skill '?';
                    
    $strike '?';
                    
    $hs '?';
                    
    $kd '?';
                } else {
                    
    $now date(U);
                    
    $nAct = ($now $result5['lasttime'])/3600;
                    if (
    $nAct 24) { $activity 100; }
                    elseif (
    $nAct 744) { $activity 0; }
                    else { 
    $activity 2400/$nAct; }
                    
    $skill=((($result5['SUM(kills)']+$result5['SUM(ffkills)'])*60/$result5['online']) + (2*($result5['SUM(kills)']+$result5['SUM(ffkills)'])/($result5['SUM(deaths)']+$result5['SUM(ffdeaths)'])) + ($result5['MAX(streak_kills)']/$result5['MAX(streak_deaths)']) + (5*$result5['SUM(headshotkills)']/($result5['SUM(kills)']+$result5['SUM(ffkills)'])) + (2*$result5['SUM(wins)']/($result5['SUM(ct_team)'] + $result5['SUM(t_team)'])))*$activity;                            
                    
    $skill=round($skill);
                    
    $strike round($result5['MAX(streak_kills)']);
                    
    $hs round(100*$result5['SUM(headshotkills)']/($result5['SUM(kills)']+$result5['SUM(ffkills)']),2);
                    
    $kd round(($result5['SUM(kills)']+$result5['SUM(ffkills)'])/($result5['SUM(deaths)']+$result5['SUM(ffdeaths)']),2);
                }
            
                
    $res GeoIP_record_by_addr($gi,$result5['last_ip']); 
                if(!
    $res->country_code)
                    
    $res->country_code "err";
                
    $flag $res->country_code.".gif";
        
                
    $array[$result5['nick']] = array($flag,$skill,$strike,$hs,$kd);
            }
            
    $num 1;
            
    uasort($array"sort_players");
            
    $top_players array_insert($array$num);
        
            
    $num 2;
            
    uasort($array"sort_players");
            
    $top_streak array_insert($array$num);
        
            
    $num 3;
            
    uasort($array"sort_players");
            
    $top_hs array_insert($array$num);
        
            
    $num 4;
            
    uasort($array"sort_players");
            
    $top_kd array_insert($array$num);
        
            
    $menu3 '
                <div class="block">
                    <h5 class="blocksubhead smaller">
                        <img alt="" src="limewizard/_templates/'
    .$template.'/images/widget-poll.png" title="Statistic"/>
                        Best Skill
                    </h5>
                    <div id="c_blog_comments" class="blockbody">
                        <ul id="latestcomments" class="blockrow">
                            <li class="smallfont">
                                <table width="100%" border="0">
                                    '
    .$top_players.'
                                </table>
                            </li>
                        </ul>
                    </div>
                </div>
                <div class="underblock"></div>
            '
    ;
            
    $menu4 '
                <div class="block">
                    <h5 class="blocksubhead smaller">
                        <img alt="" src="limewizard/_templates/'
    .$template.'/images/widget-poll.png" title="Statistic"/>
                        Best Strike
                    </h5>
                    <div id="c_blog_comments" class="blockbody">
                        <ul id="latestcomments" class="blockrow">
                            <li class="smallfont">
                                <table width="100%" border="0">
                                    '
    .$top_streak.'
                                </table>
                            </li>
                        </ul>
                    </div>
                </div>
                <div class="underblock"></div>
            '
    ;
            
    $menu5 '
                <div class="block">
                    <h5 class="blocksubhead smaller">
                        <img alt="" src="limewizard/_templates/'
    .$template.'/images/widget-poll.png" title="Statistic"/>
                        Best HS%
                    </h5>
                    <div id="c_blog_comments" class="blockbody">
                        <ul id="latestcomments" class="blockrow">
                            <li class="smallfont">
                                <table width="100%" border="0">
                                    '
    .$top_hs.'
                                </table>
                            </li>
                        </ul>
                    </div>
                </div>
                <div class="underblock"></div>
            '
    ;
            
    $menu6 '
                <div class="block">
                    <h5 class="blocksubhead smaller">
                        <img alt="" src="limewizard/_templates/'
    .$template.'/images/widget-poll.png" title="Statistic"/>
                        Best K/D
                    </h5>
                    <div id="c_blog_comments" class="blockbody">
                        <ul id="latestcomments" class="blockrow">
                            <li class="smallfont">
                                <table width="100%" border="0">
                                    '
    .$top_kd.'
                                </table>
                            </li>
                        </ul>
                    </div>
                </div>
                <div class="underblock"></div>
            '
    ;
        }


        
    $menu_all '
                <div id="blog_sidebar_generic">
                    '
    .$menu3.$menu4.$menu5.$menu6.$menu2.'    
                </div>
        '
    ;

        include(
    "stats.php");        

        echo 
    $center_section;    

    ?>
    Опять же использую полную выборку, загон в массив и сортировку в нем по определенным элементам. Чем больше база, тем дольше выполняется скрипт...
    По поводу кеширования я понял, но может будут ещё какие-нибудь замечания или поправки по коду?

    Заранее благодарю за оказанное внимание к моему вопросу и рассчитываю на профессиональную помощь.
     
Статус темы:
Закрыта.