Oc-windows.ru

IT Новости из мира ПК
0 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Php sql limit

Оптимизируем LIMIT offset

Везде, где используется LIMIT offset для больших таблиц, рано или поздно начинаются тормоза. Запросы вида

могут выполнятся очень долго. Например, в моем случае, на одном из сайтов кол-во комментариев перевалило за 200к и постраничная навигация по комментариям начала ощутимо тормозить, а в mysql-slow.log все чаще стали попадать запросы с временем выполнения 3-5сек.

Проблема заключается в том, что используя LIMIT 100000, 30 — mysql вначале пройдется по первым 100000 записям и только потом выберет нужные 30. Избежать этого достаточно просто, достаточно использовать подзапрос вида, который в общем случае выглядит так:

Давайте рассмотрим конкретный пример. В моем случае используется движок DLE и в нем запрос выглядит следующим образом:

Исправленный запрос выглядит так:

На графике можно увидеть результат такой замены:

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

PS. Фикс для DLE для комментариев (аналогичным образом можно сделать для всех навигаций). В файле comments.class.php
найти

заменить эту строчку на:

Редакторский дайджест

Присылаем лучшие статьи раз в месяц

Скоро на этот адрес придет письмо. Подтвердите подписку, если всё в силе.

  • Скопировать ссылку
  • Facebook
  • Twitter
  • ВКонтакте
  • Telegram
  • Pocket

Похожие публикации

  • 2 декабря 2008 в 19:02

Три первых шага к оптимизации LAMP

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

Оптимизация MySQL / семинар Петра Зайцева

Заказы

AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Комментарии 30

возможно, есть смысл менять сортировку(при этом, конечно, необходимы некоторые манипуляции с offset)… Или привязаться к другим полям, предварительно их проиндексировав, если есть такая возможность. (SELECT id FROM table WHERE date ’01-03-2014′)

(если кто-то подумал что автор предложил 100% решение проблемы — это не совсем так, попробуйте запустить SELECT id FROM table LIMIT 10000000,40 на базе с 20 000 000 записей)

Oracle rownum считает до сортировки, т.е. там нужно писать так:

Mysql в этом плане гораздо удобней.
Насчет оптимизации не знаю, нет возможности сейчас проверить план.

(WHERE только во внешнем запросе: ничего не выдаёт)

Если where будети во внутреннем запросе — бенчмарки будут другие, думаю. К тому-же вместо where может быть JOIN (не LEFT JOIN)

мм, если комментов очень много, наверное, стоит создать таблицу вида

[pk] theme_id
[pk] page_id
[pk+uq] comment_id

по запросу в нее получать список ID всем комментов, которые нужны…
так как таки читают комменты обычно намного больше, чем удаляют. да и построение пагинатора не потребует каунтов и беспокойства самой таблицы постов

Проблема заключается в том, что используя LIMIT 100000, 30 — mysql вначале пройдется по первым 100000 записям и только потом выберет нужные 30.

Не совсем так. В общем случае, будут выбраны все записи, попадающие под условие where (тут его нет), отсортированы, затем будут отброшены первые 100 тыс записей и выданы следующие 30. Скорее всего, в данном случае сортировки не будет, так как сортировка по индексному полю, но все равно в пустую будут прочитаны 100тыс записей и для них будут выполнены соединения, отсюда и линейный рост времени выполнения.

Избежать этого достаточно просто

>а как быть в случае удаления коммента?

Пересчёт номеров страниц одноразовая операция и выполняется относительно быстро. Удаление — событие нечастое, можно подождать и с пересчётом.

У меня на форуме бывает под 15 тыс. ответов в теме. До 600 страниц на топик. И это при том, что я стараюсь жёстко разделять тему на новые темы в случае её роста, так бы и многие тысячи страниц были у некоторых (а ля iXBT-style, когда найти что-то потом нереально). При реальном использовании основная масса народа постоянно пасётся именно на последних страницах, т.е. львиная масса запросов именно в духе… LIMIT 10000, 25. Когда сидит хотя бы человек 200 в онлайне, нагрузка получается очень большая. Особенно, учитывая то, что сортировка не по одному ID, а по дате сообщения, приоритету сортировки, с учётом пометки «удалено» (физическое удаление в моём случае не практикуется) и ещё что-то. Индексы получаются сложные и большие. Пришлось вводит административные ограничения «200 страниц темы — создавайте новую тему» и т.п. Параметр «страницы темы» не вводил, так как тоже опасался проблемы пересчёта. Потом составил не особенно сложный запрос пересчёта номеров страниц и ввёл соответствующую сущность. Выборки стали выполняться мгновенно.

Читать еще:  Php html encode

Запрос SQL на выборку определённого числа записей

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

Давайте сразу рассмотрим запрос SQL на выборку определённого числа записей:

SELECT * FROM users WHERE id > 5 LIMIT 10

Данным запросом мы получим 10 первых записей. Все остальные отпадут. Изменение от обычного SQL-запроса на выборку данных состоит только в параметре «LIMIT«. Число, которое идёт за ним, сообщает, какое количество записей мы хотим получить, и в нашем случае — это 10.

Также существует возможность задавать после «LIMIT» два числа:

SELECT * FROM users WHERE id > 5 LIMIT 10, 20

Данный SQL-запрос вернёт записи, начиная с 10-го номера включительно в количестве 20-ти штук. То есть первое число означает, с какой записи надо формировать результат выборки, а второе число означает, какое количество записей всего должно быть.

Собственно, это всё, что необходимо для выборки определённого числа записей. Используется это очень часто, например, при выводе последних 10-ти зарегистрированных пользователях. Или при выводе 5-ти свежих статей (как на главной странице моего сайта), или в других аналогичных ситуациях.

Копирование материалов разрешается только с указанием автора (Михаил Русаков) и индексируемой прямой ссылкой на сайт (http://myrusakov.ru)!

Добавляйтесь ко мне в друзья ВКонтакте: http://vk.com/myrusakov.
Если Вы хотите дать оценку мне и моей работе, то напишите её в моей группе: http://vk.com/rusakovmy.

Если Вы не хотите пропустить новые материалы на сайте,
то Вы можете подписаться на обновления: Подписаться на обновления

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

Порекомендуйте эту статью друзьям:

Если Вам понравился сайт, то разместите ссылку на него (у себя на сайте, на форуме, в контакте):

Она выглядит вот так:

  • BB-код ссылки для форумов (например, можете поставить её в подписи):
  • Комментарии ( 6 ):

    Михаил! Добрый день. Вопрос-использование LIMIT каким то образом оптимизирует запрос, если изначально известно, что нужно выбрать одну строку? Каким образом действует этот «ограничитель»? При выявлении нужной строки в таблице выборка прекращается или она дальше происходит и просто выводится одна строка?

    Нет, прекращается дальнейший поиск. В этом и преимущество, получается выигрыш в скорости, и это будет намного быстрее, чем потом, например, в PHP убирать всё лишнее.

    Это же касается не только SELECT, но и других операторов?

    LIMIT везде ускоряет процесс.

    Здравствуйте! Я хочу сделать страницу пользователя. Например, он напишет в свою страницу: «привет», и оно должно отобразиться. У меня вопрос: Как хранить эти сообщения и отделять друг от друга при выборке?

    Для добавления комментариев надо войти в систему.
    Если Вы ещё не зарегистрированы на сайте, то сначала зарегистрируйтесь.

    Copyright © 2010-2020 Русаков Михаил Юрьевич. Все права защищены.

    Как разбить вывод из mysql постранично

    Как сделать постраничный вывод из mysql «как в яндексе»?
    по 10 записей на страницу, внизу — ссылки на остальные страницы?

    Сначала научимся получать из базы нужные записи.
    Их получение в mysql обеспечивается оператором LIMIT, который вызывается с двумя параметрами — с какой записи начинать, и сколько выводить (внимание! не по какую, а сколько!)
    SELECT * FROM table LIMIT 0,10
    этот запрос вернет записи с первой по 10, поскольку нумерация начинается с 0
    соответственно, запрос для третьей страницы будет выглядеть, как
    SELECT * FROM table LIMIT 20,10
    получается, что нам всего лишь надо передать в скрипт число, которое потом подставить в запрос.
    Этим будет заниматься код, который выводит ссылки на страницы.
    Естественно, в цикле.
    Для цикла нам понадобится количество записей, которое возвращает запрос без лимита.
    Это число можно получить двумя путями. Либо отдельным запросом, в котором отсутствует оператор LIMIT, а вместо перечисления полей после оператора SELECT запрашивается только count(*):
    $q=»SELECT count(*) FROM table»;
    $res=mysql_query($q);
    $row=mysql_fetch_row($res);
    $total_rows=$row[0];

    Читать еще:  Php 64 bit

    Либо, если версия mysql больше 4.0, то общее количество строк можно запросить в том же запросе. См. документацию mysql по функции FOUND_ROWS()
    Однако, первый сособ представляется более удобным, хотя и немного более медленным.

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

    Для начала определим, сколько всего получится страниц. Для этого надо поделить общее число записей на количество оных на одной странице и округлить результат в большую сторону. Таким округлением занимается в пхп функция ceil()
    $num_pages=ceil($total_rows/$per_page);
    В этом выражении участвует переменная $per_page , в которую мы положим количество выводимых на странице записей.
    Ведь, если это количество изменится, мы же не хотим ползать по всему коду и исправлять цифры? проще сделать это один раз в начале скрипта при объявлении переменной. В запрос, вторым параметром LIMIT, подставлять нужно, конечно же, тоже ее.

    Ну, а дальше, собственно, вывод ссылок.
    for($i=1;$i
    в цикле от 1 до $num_pages выводим ссылку с параметром num, равным числу, которое надо передать в LIMIT, а в тексте ссылки пишем номер страницы, поскольку людям понятнее видеть номер страницы, а не записи. На код это не влияет, а людям приятно.

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

    Старая версия, классический говнокод
    что у нас в результате получилось?

    // количество записей, выводимых на странице
    $per_page=10;
    // получаем номер страницы
    if (isset($_GET[‘page’])) $page=($_GET[‘page’]-1); else $page=0;
    // вычисляем первый оператор для LIMIT
    $start=abs($page*$per_page);
    // составляем запрос и выводим записи
    // переменную $start используем, как нумератор записей.
    $q=»SELECT * FROM `table` ORDER BY field LIMIT $start,$per_page»;
    $res=mysql_query($q);
    while($row=mysql_fetch_array($res)) <
    echo ++$start.». «.$row[‘field’].»
    n»;
    >

    // дальше выводим ссылки на страницы:
    $q=»SELECT count(*) FROM `table`»;
    $res=mysql_query($q);
    $row=mysql_fetch_row($res);
    $total_rows=$row[0];

    Разумеется, вышеприведённый код подходит только как учебное пособие. С его помощью становится понятным принцип, но в реальных условиях мы сразу же столкнемся, как минимум, с двумя проблемами:
    Во-первых, кроме переменной $page нашему крипту явно будут переданы и другие переменные, да и адрес может совсем не совпадать с именем скрипта. А мы это при формировании ссылок не учитываем.
    Во-вторых, нормальный современный сайт немыслим без шаблонов. И такая ужасная лапша из SQL запросов, PHP кода и HTML тегов никуда не годится.

    Плюс надо избавляться от устаревшего расширения mysql и организовывать работу с БД более интеллектуально.

    Займемся решением этих проблем.
    Первая решается очень просто при использовании функции http_build_query()

    Вторая — тоже несложно. Шаблонизаторов много, но мы воспользуемся самым универсальным — PHP.

    Что же у нас получилось? А получился у нас — рефакторинг! Переделка старого кода в соответствии с требованиями современности, плюс мелкое причесывание:

    Читать еще:  Php menuid guardant register

    include ‘safemysql.class.php’ ;
    $db = new safeMysql ();

    //получаем номер страницы и значение для лимита
    $cur_page = 1 ;
    if (isset( $_GET [ ‘page’ ]) && $_GET [ ‘page’ ] > 0 )
    <
    $cur_page = $_GET [ ‘page’ ];
    >
    $start = ( $cur_page — 1 ) * $per_page ;

    //выполняем запрос и получаем данные для вывода
    $sql = «SELECT SQL_CALC_FOUND_ROWS * FROM Board LIMIT ?i, ?i» ;
    $data = $db -> getAll ( $sql , $start , $per_page );
    $rows = $db -> getOne ( «SELECT FOUND_ROWS()» );

    //узнаем общее количество страниц и заполняем массив со ссылками
    $num_pages = ceil ( $rows / $per_page );

    // зададим переменную, которую будем использовать для вывода номеров страниц
    $page = 0 ;

    //а дальше выводим в шаблоне днные и навигацию:
    ?>
    Найдено сообщений:

    PHP | MySQL LIMIT Clause

    В MySQL предложение LIMIT используется с оператором SELECT, чтобы ограничить количество строк в наборе результатов. Предложение Limit принимает один или два аргумента, которые являются offset и count. Значением обоих параметров могут быть ноль или положительные целые числа.

    Смещение: используется для указания смещения первой строки, которая будет возвращена.
    Количество : используется для указания максимального количества строк, которые будут возвращены.

    Предложение Limit принимает один или два параметра, когда указаны два параметра, первый — это смещение, а второй — счетчик, тогда как всякий раз, когда указан только один параметр, он обозначает количество строк, которые должны быть возвращены с начала набора результатов. ,

    Синтаксис:

    Подробнее об предложении LIMIT вы можете узнать в статье MySQL | ПРЕДЕЛ ОГРАНИЧЕНИЯ .

    Рассмотрим следующую таблицу «Данные» с тремя столбцами «Имя», «Фамилия» и «Возраст».

    Чтобы извлечь первые три строки из таблицы «Данные», мы будем использовать следующий запрос:

    Чтобы извлечь строки 2-3 (включительно) из таблицы «Данные», мы будем использовать следующий запрос:

    Ниже приведена реализация запроса PHP для отображения первых двух строк таблицы «Данные» с использованием предложения LIMIT как в процедурных, так и в объектно-ориентированных расширениях:

      Предельная оговорка с использованием процедурного метода

    $link = mysqli_connect( «localhost» , «root» , «» , «Mydb» );

    if ( $link == = false) <

    die ( «ERROR: Could not connect. » .mysqli_connect_error());

    $sql = «SELECT * FROM Data LIMIT 2» ;

    if ( $res = mysqli_query( $link , $sql )) <

    if (mysqli_num_rows( $res ) > 0) <

    while ( $row = mysqli_fetch_array( $res )) <

    echo »

    » . $row [ ‘Firstname’ ]. «

    » ;

    echo »

    » . $row [ ‘Lastname’ ]. «

    » ;

    echo »

    » . $row [ ‘Age’ ]. «

    » ;

    echo «No matching records are found.» ;

    echo «ERROR: Could not able to execute $sql. » .mysqli_error( $link );

    Выход :

    Объяснение:

    1. Переменная «res» хранит данные, возвращаемые функцией mysql_query ().
    2. Каждый раз, когда mysqli_fetch_array () вызывается, он возвращает следующую строку из набора res ().
    3. Цикл while используется для цикла по всем строкам таблицы «data».
  • Предельное предложение с использованием объектно-ориентированного метода

    $mysqli = new mysqli( «localhost» , «root» , «» , «Mydb» );

    if ( $mysqli == = false) <

    die ( «ERROR: Could not connect. » . $mysqli ->connect_error);

    $sql = «SELECT * FROM Data LIMIT 2» ;

    if ( $res = $mysqli ->query( $sql )) <

    if ( $res ->num_rows > 0) <

    while ( $row = $res ->fetch_array()) <

    echo »

    » . $row [ ‘Firstname’ ]. «

    » ;

    echo »

    » . $row [ ‘Lastname’ ]. «

    » ;

    echo »

    » . $row [ ‘Age’ ]. «

    » ;

    echo «No matching records are found.» ;

    echo «ERROR: Could not able to execute $sql. » . $mysqli ->error;

    Выход :

    Предельная оговорка с использованием метода PDO

    $pdo = new PDO( «mysql:host=localhost;dbname=Mydb» , «root» , «» );

    $pdo ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    catch (PDOException $e ) <

    die ( «ERROR: Could not connect. » . $e ->getMessage());

    $sql = «SELECT * FROM Data LIMIT 2» ;

    $res = $pdo ->query( $sql );

    if ( $res ->rowCount() > 0) <

    while ( $row = $res ->fetch()) <

    echo »

    » . $row [ ‘Firstname’ ]. «

    » ;

    echo »

    » . $row [ ‘Lastname’ ]. «

    » ;

    echo »

    » . $row [ ‘Age’ ]. «

    » ;

    echo «No matching records are found.» ;

    catch (PDOException $e ) <

    die ( «ERROR: Could not able to execute $sql. » . $e ->getMessage());

    Выход :

  • Ссылка на основную публикацию
    Adblock
    detector