SQL_CALC_FOUND_ROWS или не SQL_CALC_FOUND_ROWS?

Занимаясь оптимизацией клиентских запросов, я довольно часто вижу запросы, в которых используется SQL_CALC_FOUND_ROWS. Многие думают, что данная конструкция намного быстрее, чем выполнение двух запросов: получение данных SELECT и подсчет количества записей при помощи COUNT. Попробуем разобраться что к чему.

Создадим следующую таблицу:

CREATE TABLE `count_test` (
`a` int(10) NOT NULL AUTO_INCREMENT,
`b` int(10) NOT NULL,
`c` int(10) NOT NULL,
`d` varchar(32) NOT NULL,
PRIMARY KEY (`a`),
KEY `bc` (`b`,`c`)
) ENGINE=MyISAM

Заполняем её случайными данными:

mysql_connect("127.0.0.1", "root");
mysql_select_db("test");
$num = 10000000;
for ($i = 0; $i < $num; $i++) {
    $b = $i % 1000;
    $sql = "INSERT INTO `count_test` 
            SET `b` = {$b}, 
                `c` = ROUND(RAND() * 10), 
                `d` = MD5({$i})
    ";
    mysql_query($sql);
}

Сначала попытаемся выполнить запрос, используя проиндексированную колонку b в выражении WHERE:

SELECT SQL_NO_CACHE SQL_CALC_FOUND_ROWS *
FROM `count_test`
WHERE `b` = 555
ORDER BY `c` LIMIT 5;

В итоге, для каждого значения b запрос выполнялся 20-100 секунд в первый раз и 2-5 секунд — после кэширования. Такая разность объясняется издержками ввода-вывода, которые требуются MySQL для обработки 10000 строк без конструкции LIMIT.

А что будет, если запрос разбить на два отдельных:

SELECT SQL_NO_CACHE *
FROM `count_test`
WHERE `b` = 666
ORDER BY c LIMIT 5;

Первый раз запрос выполняется 0.01-0.11 сек, 0.00-0.02 сек — в последующие. Теперь посмотрим, сколько будет работать COUNT:

SELECT SQL_NO_CACHE COUNT(*)
FROM `count_test`
WHERE `b` = 666;

Результат ошеломляющий — 0.00-0.04 сек. Получается, что общее время выполнения запросов SELECT и COUNT лежит в промежутке от 0.00 сек до 0.15 сек, что намного меньше времени выполнения исходного запроса. Что на это скажет EXPLAIN:

EXPLAIN SELECT SQL_CALC_FOUND_ROWS *
FROM `count_test`
WHERE `b` = 999
ORDER BY `c` LIMIT 5;

Результат:

SQL_CALC_FOUND_ROWS_1

Запрос с использованием COUNT:

EXPLAIN SELECT SQL_NO_CACHE COUNT(*)
FROM `count_test`
WHERE `b` = 666;

Результат:

SQL_CALC_FOUND_ROWS_2

По результатам запросов видно, что SQL_CALC_FOUND_ROWS заставляет MySQL обрабатывать все данные в таблице, даже если они не нужны в результате (мы запросили всего пять LIMIT 5), а при использовании COUNT применяется индекс, из-за чего такой запрос выполняется гораздо быстрее.

Проведем те же тесты на таблице с индексом на колонке b:

Тест Без индекса
Full-scan, сек
С индексом на колонке b
Filesort, сек
SQL_CALC_FOUND_ROWS 7.0 7.0 + 7.0
SELECT + COUNT 1.8 1.8 + 0.05

Вывод

Если у нас есть подходящие индексы для WHERE и ORDER, то использование двух отдельных запросов вполне может быть намного быстрее чем один с SQL_CALC_FOUND_ROWS.

Ссылки

Источник: https://www.kobzarev.com/programming/to-sql_calc_found_rows-or-not-to-sql_calc_found_rows/

Михаил Кобзарёв

Суровый русский тимлид. Жил в Магадане, в офисе московских веб студий и в Тульской деревне. Виртуозно знает WordPress, PHP, ООП, Vue.js и вот это вот все. Делает крутые высоконагруженные сайты, поэтому уже почти захватил весь рынок WordPress разработки в России. Не дает никому делать сайты без спроса. Ведет блог о разработке, дайджест в телеграмме и в ВК. Любит сиськи, баню и радиоэлектронику. 100% патриот (но это не точно). Тролль 542 уровня. Ездит в отпуск раз в 5 лет.

Добавить комментарий

%d такие блоггеры, как: