Pull to refresh

Подсчёт общего количества строк выборке в mySQL при использовании LIMIT

Reading time 5 min
Views 64K
Один хороший человек хочет попасть на хабр. Для подтверждения своих благих намерений он написал статью, которую я привожу вам.

Наверняка многие знают о существовании в mySQL функции FOUND_ROWS(). Её чаще всего используют для подсчета общего числа строк в таблице, удовлетворяющих заданным условиям. Используют её обычно совместно с директивой SQL_CALC_FOUND_ROWS следующим образом:

Mysql> SELECT SQL_CALC_FOUND_ROWS * FROM table WHERE column > 1 LIMIT 0, 50;
Mysql> SELECT FOUND_ROWS();

Результатом второго запроса будет общее количество строк в таблице «table», удовлетворяющих условию «column > 1» (а не только тех, что были возвращены первым запросом).
Следует учитывать некоторые особенности этой функции, когда используете её без директивы SQL_CALC_FOUND_ROWS, о чём добросовестно предупреждает документация mySQL: в этом случае она вернёт общее количество обработанных строк (не возвращённых!). Например:

Mysql> SELECT * FROM table LIMIT 0, 50;
Mysql> SELECT FOUND_ROWS();

Результатом, как и ожидается, будет «50». Но следующие запросы вернут не слишком ожидаемый результат:

Mysql> SELECT * FROM table WHERE column > 1 LIMIT 50, 50;
Mysql> SELECT FOUND_ROWS();

Несмотря на то, что первый запрос вернёт 50 строк, результатом будет «100», т.к. mySQL пришлось просмотреть именно 100 строк.

Кроме того, при использовании UNION с SQL_CALC_FOUND_ROWS также появляются некоторые специфические требования:
  • директива SQL_CALC_FOUND_ROWS должна появляться единственный раз и только в первом запросе;
  • точные результаты возвращаются только при использовании UNION ALL, т.к. при использовании UNION без ALL некоторые строки будут удалены, что не учитывается функцией FOUND_ROWS();
  • если не используется глобальный LIMIT, то директива SQL_CALC_FOUND_ROWS без проигнорирована.

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

Mysql> SELECT * FROM table WHERE column > 1 LIMIT 50, 50;
Mysql> SELECT COUNT(*) FROM table WHERE column > 1;

Вопрос: если вариант с использованием функции FOUND_ROWS() действительно быстрее, то почему его не используют повсеместно? Я решил сравнить оба подхода. Для этого была создана таблица:
CREATE TABLE `table_1` (                  
      `id` int(10) unsigned NOT NULL auto_increment,      
      `column_1` varchar(32) default NULL,           
      `column_2` varchar(32) default NULL,           
      PRIMARY KEY (`id`),                   
      KEY `column_2` (`column_2`)               
     ) ENGINE=MyISAM AUTO_INCREMENT=1;

* This source code was highlighted with Source Code Highlighter.


Таблица содержит три колонки: числовое поле id с auto_increment и PRIMARY-ключом; текстовую колонку column_1 без индекса и текстовую же колонку с индексом column_2. Таблица была заполнена с помощью следующего PHP-скрипта:

<?php
for($i = 0; $i < 457128; $i ++) {
  $db->insert ( 'table_1', array ('column_1', 'column_2' ), array ( md5(uniqid(rand(), true)), md5(uniqid(rand(), true)) ) );
}
?>

* This source code was highlighted with Source Code Highlighter.


$db – это обёртка над PHP-функциями по работе с mySQL. В ней в частности проводится замер времени исполнения запросов к БД.
Далее производились следующие виды выборок:
  • «Стандартный» запрос c выборкой по PRIMARY-ключу
    SELECT * FROM `table_1` WHERE `id` < $id_limit LIMIT 1000
    SELECT COUNT(*) FROM `table_1` WHERE `id` < $id_limit
  • SQL_CALC_FOUND_ROWS c выборкой по PRIMARY ключу:
    SELECT SQL_CALC_FOUND_ROWS * FROM `table_1` WHERE `id` < $id_limit LIMIT 1000
    SELECT FOUND_ROWS() AS count
  • «Стандартный» запрос c выборкой по колонке БЕЗ КЛЮЧА
    SELECT * FROM `table_1` WHERE `column_1` LIKE '$column_1_limit%' LIMIT 1000
    SELECT COUNT(*) FROM `table_1` WHERE `column_1` LIKE '$column_1_limit%'
  • SQL_CALC_FOUND_ROWS c выборкой по колонке БЕЗ КЛЮЧА
    SELECT SQL_CALC_FOUND_ROWS * FROM `table_1` WHERE `column_1` LIKE '$column_1_limit%' LIMIT 1000
    SELECT FOUND_ROWS() AS count
  • «Стандартный» запрос c выборкой по колонке VARCHAR с КЛЮЧОМ
    SELECT * FROM `table_1` WHERE `column_2` LIKE '$column_2_limit%' LIMIT 1000
    SELECT COUNT(*) FROM `table_1` WHERE `column_2` LIKE '$column_2_limit%'
  • SQL_CALC_FOUND_ROWS c выборкой по колонке БЕЗ КЛЮЧА
    SELECT SQL_CALC_FOUND_ROWS * FROM `table_1` WHERE `column_2` LIKE '$column_2_limit%' LIMIT 1000
    SELECT FOUND_ROWS() AS count
  • «Стандартный» запрос c выборкой по обеим колонкам
    SELECT * FROM `table_1` WHERE `column_1` LIKE '$column_1_limit_2%' AND `column_2` LIKE '$column_2_limit_2%' LIMIT 1000
    SELECT COUNT(*) FROM `table_1` WHERE `column_1` LIKE '$column_1_limit_2%' AND `column_2` LIKE '$column_2_limit_2%'
  • SQL_CALC_FOUND_ROWS c выборкой по обеим колонкам
    SELECT SQL_CALC_FOUND_ROWS * FROM `table_1` WHERE `column_1` LIKE '$column_1_limit_2%' AND `column_2` LIKE '$column_2_limit_2%' LIMIT 1000
    SELECT FOUND_ROWS() AS count
  • «Стандартный» запрос c выборкой по обеим колонкам + PRIMARY
    SELECT * FROM `table_1` WHERE `column_1` LIKE '$column_1_limit_3%' AND `column_2` LIKE '$column_2_limit_3%' AND `id` < $id_limit_2 LIMIT 1000
    SELECT COUNT(*) FROM `table_1` WHERE `column_1` LIKE '$column_1_limit_3%' AND `column_2` LIKE '$column_2_limit_3%' AND `id` < $id_limit_2
  • SQL_CALC_FOUND_ROWS c выборкой по обеим колонкам + PRIMARY
    SELECT SQL_CALC_FOUND_ROWS * FROM `table_1` WHERE `column_1` LIKE '$column_1_limit_3%' AND `column_2` LIKE '$column_2_limit_3%' AND `id` < $id_limit_2 LIMIT 1000
    SELECT FOUND_ROWS() AS count


Каждый из запросов исполнялся 10 раз с разными параметрами, при этом параметры у «Стандартного» и SQL_CALC_FOUND_ROWS запроса одного типа на одной итерации – одинаковые. Т.е.:

for($i = 0; $i < 10; $i ++) {
  $id_limit = rand(15000, 20000);
  $id_limit_2 = rand(15000, 20000);

  $column_1_limit = rand(1, 9);
  $column_2_limit = rand(1, 9);

  $column_1_limit_2 = rand(10, 20);
  $column_2_limit_2 = rand(10, 20);

  $column_1_limit_3 = rand(20, 30);
  $column_2_limit_3 = rand(20, 30);
  // НИЖЕ – ИСПОЛНЕНИЕ ЗАПРОСОВ
}

* This source code was highlighted with Source Code Highlighter.


Результаты тестирования можно увидеть на диаграмме:

image

Получается, что всё не так однозначно, как говориться в документации mySQL. При использовании выборки по колонкам с индексам однозначно быстрее «классическая» схема. При использовании же колонок без индексов, а также смешанных запросов, быстрее становится функция FOUND_ROWS(), однако её выигрыш весьма незначителен.
Таким образом, выбор подхода – дело каждого конкретного случая. Когда производительность не имеет особого значения и важны удобство и скорость написания кода, то вполне можно использовать FOUND_ROWS() + SQL_CALC_FOUND_ROWS. В противном случае, видимо, предпочтительнее использовать классическую схему из двух запросов.
Тестирование проводилось на двух машинах:
  • Windows XP SP3, Intel Pentium Core 2 Duo E8300 @ 2.83 GHz, 2 GB, mySQL 5.0.51a
  • Ubuntu 8.04, AMD Opteron 2344 HE Quad-Core, 4 GB, 5.0.51a-3ubuntu5.4

Диаграмма показывает результаты в Windows XP. Результаты (не время выборок) для обеих систем одинаковые.
Tags:
Hubs:
+43
Comments 79
Comments Comments 79

Articles