www.Litecode.ru – Сборник рецептов для создания сайтов

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

ГлавнаяMySQL → Оптимизация запросов MySQL

MySQL располагает большим набором функций для различных сортировок (ORDER BY), группировок (GROUP BY), объединений (LEFT JOIN или RIGHT JOIN) и так далее. Все они безусловно удобны, но в условиях одноразовых запросов. К примеру, если лично Вам требуется что-то откопать в базе используя кучу таблиц и связок, то кроме вышеперечисленных функций можно и даже нужно применять условный операторы IF. Главная ошибка начинающих программистов это стремление применить такие запросы в рабочем коде сайта. В данном случае сложный запрос безусловно красив, но вреден. Все дело в том, что любые операторы сортировок, группировок, объединений или вложенных запросов, не могут выполняться в оперативной памяти, и используют жесткий диск для создания временных таблиц. А хард, как известно — самое узкое место сервера.

Правила оптимизации mysql запросов

1. Избегайте вложенных запросов

Это самая серьезная ошибка. Родительский процесс всегда будет ждать завершения дочернего и в это время держать коннект к базе, использовать диск и нагружать iowait. Два параллельных запроса в базу и выполнения нужных фильтраций в серверном интерпретаторе (Perl, PHP и т. д.), выполнятся на порядок быстрее чем вложенный.

Примеры на perl, как делать не следует:

    my $sth = $dbh->prepare("SELECT elementID,elementNAME,groupID
                        FROM tbl
                        WHERE groupID IN(2,3,7)");
                        
    $sth->execute();
    
    while ( my @row = $sth->fetchrow_array() ) {
        
        my $groupNAME = $dbh->selectrow_array("SELECT groupNAME
                                            FROM groups
                                            WHERE groupID = $row[2]");
                                            
        ### Допустим нужно собрать названия групп
        ### и добавить их в конец массива с данными
        
        push @row => $groupNAME;
        
        ### Делаем еще что-нибудь ...
        
    }

или не в коем случае вот так:

    my $sth = $dbh->prepare("SELECT elementID,elementNAME,groupID
                        FROM tbl
                        WHERE groupID IN(SELECT groupID
                                        FROM groups
                                        WHERE groupNAME = 'Первая'
                                        OR groupNAME = 'Вторая'
                                        OR groupNAME = 'Седьмая')");
                        

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

Пример на perl, как делаю обычно я:

    my %groups;
    
    my $sth = $dbh->prepare("SELECT groupID,groupNAME
                        FROM groups
                        WHERE groupID IN(2,3,7)");
                        
    $sth->execute();
    
    while ( my @row = $sth->fetchrow_array() ) {
    
        $groups{$row[0]} = $row[1];
    
    }
    
    ### А теперь выполням основную выборку без вложенного запроса
    
    my $sth2 = $dbh->prepare("SELECT elementID,elementNAME,groupID
                        FROM tbl
                        WHERE groupID IN(2,3,7)");
                        
    $sth2->execute();
    
    while ( my @row = $sth2->fetchrow_array() ) {
        
        push @row => $groups{$row[2]};
        
        ### Делаем еще что-нибудь ...
        
    }

2. Не сортируйте, не группируйте и не фильтруйте в базе

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

Примеры на perl, как сортировать не следует:

    my $sth = $dbh->prepare("SELECT elementID,elementNAME
                        FROM tbl
                        WHERE groupID IN(2,3,7)
                        ORDER BY elementNAME");
                        
    $sth->execute();
    
    while ( my @row = $sth->fetchrow_array() ) {
    
        print qq{$row[0] => $row[1]};
    
    }

Пример на perl, как сортирую обычно я:

    my $list = $dbh->selectall_arrayref("SELECT elementID,elementNAME
                        FROM tbl
                        WHERE groupID IN(2,3,7)");
    
    foreach ( sort { $a->[1] cmp $b->[1] } @$list ){
    
        print qq{$_->[0] => $_->[1]};
    
    }
    

Так намного быстрее. Особенно заметна разница если данных много. В случае, если нужно отсортировать в perl по нескольким полям, можно применить сортировку Шварца. Если требуется произвольная сортировка ORDER BY RAND() — используйте сортировку random в perl.

3. Используйте индексы

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

Таким запросом:

ALTER TABLE `any_db`.`any_tbl` ADD INDEX `text_index`(`text_fld`(255));

Где 255 - длина ключа. Для некоторых типов данных он не требуется. Подробности в документации к MySQL.