C, PHP, VB, .NET

Когато информацията на някоя страница е много, понякога решаваме да направим т.нар. страниране. Това означава да покажем първите няколко записа и след тях да поставим връзки към следваща страница, на която ще се покажат следващите няколко записа. Тоест даваме възможност на потребителят да прелиства напред-назад като в книга, вместо да му изсипваме цялото съдържание накуп. Как се постига това в MySQL/MariaDB?

Първо решение: LIMIT и OFFSET (бавно; за свободно навигиране; с възможни пропуски при манипулиране на данните по време на извличане на информацията)

Основният начин, по който може да се постигне това, е чрез комбинация от LIMIT и OFFSET върху еднозначно сортирана с ORDER BY поредица от записи. Чрез LIMIT <брой продукти на страница> OFFSET <номер на страница умножен по броя продукти на страница, започвайки от 0> бихме могли да извършим въпросната навигация. Нека дадем пример със следната таблица:

CREATE TABLE products(
   num INT PRIMARY KEY,
   name VARCHAR(255) NOT NULL,
   price DECIMAL(5,2) NOT NULL
);

INSERT INTO products(num, name, price) VALUES 
(59, "A", 25.50), (12, "B", 43.70), (110, "C", 41.30),
(15, "D", 110.12), (85, "E", 200.00), (5, "F", 21.40),
(1, "G", 134.99), (66, "H", 15.84), (300, "I", 71.00);

Да речем, че искаме да показваме по два записа на страница, като всичко ще е подредено по азбучен ред. Заявката за първа страница би била следна:

SELECT name, price FROM products
ORDER BY name, num
LIMIT 2 OFFSET 0;

За втора страница би била:

SELECT name, price FROM products
ORDER BY name, num
LIMIT 2 OFFSET 2;

За трета страница OFFSET ще е 4, и т.н. (номер на страницата, започвайки от 0, умножен по броя на записите в LIMIT). Забележете, че освен по име, сортираме за всеки случай и по първичния ключ. Това се прави, за да направи сортирането детерминистично (еднозначно) и винаги да се гарантира една и съща подредба. Ако не сортираме по num, при повтарящо се име на продукт бихме могли да получим потенциално различна подредба от една и съща заявка.

Този подход е много удобен, защото спокойно бихме могли да навигираме директно на която си поискаме страница. При него обаче има един недостатък: изтриването на информация променя страниците. Представете си, че потребител е на уеб сайта Ви и разглежда първата страница с продукти. Той би виждал продукти A и B, а на втора страница биха го очаквали продукти C и D. Ако в този момент изтрием продукт A и след това потребителят отиде на втора страница, той ще види продукти D и E, вместо C и D. Това е така, защото продукт C вече е дефакто преместен на първа страница.

Второ решение: Чрез пазене на състоянието (бързо; за последователно навигиране; без пропуски при манипулиране на информацията по време на навигиране)

Алтернативно решение на този проблем е да въведем концепция за съхранение на позицията, на която потребителят се намира текущо, която да е независима от състоянието на базата от данни. Ето как може да стане това:

• не използваме OFFSET, а само LIMIT;
• взимаме последният продукт от страницата, на която текущо се намира потребителя;
• с условие WHERE лимитираме резултатната таблица така, че да показва само записите, които са СЛЕД последния видян от потребителя продукт.

Ето пример за реализация на тази концепция с примерната таблица. Първата страница се извиква по стандартния начин:

SELECT name, price, num FROM products
ORDER BY name, num
LIMIT 2;
+------+-------+-----+
| name | price | num |
+------+-------+-----+
| A    | 25.50 |  59 |
| B    | 43.70 |  11 |
+------+-------+-----+

След като вече знаем, че последния продукт е бил с име „B“ и num=11, то следващият в списъка би могъл да е или с име „B“ и по-голям номер, или с име, което е лексикографски след „B“. Така втора страница следва да бъде заредена със следната заявка:

SELECT name, price, num FROM products
WHERE (name = "B" AND num>11)
         OR
      (name > "B")
ORDER BY name, num
LIMIT 2;

При това решение винаги продуктите от втора страница ще останат такива, каквито са били, включително ако всички предишни продукти от първа страница бъдат изтрити. Разбира се детерминистичното сортиране тук отново е от ключово значение. Показаният подход обаче има недостатък – при него не можем да се прехвърлим на произволна страница. Различните страници трябва да се зареждат една сред друга, защото всяка следваща зависи от данните, записани в последния елемент на предишната. Това би било подходящо ако например правите уеб страница, която зарежда постепенно нова и нова информация при „скролване“ надолу, но е неизползваемо при свободна навигация.

Трето решение: Оптимизиран LIMIT и OFFSET (бърз, за свободно навигиране; с възможни пропуски при манипулиране на данните по време на извличане на информацията)

Първото решение с OFFSET има и още един недостатък – то е, че при много големи таблици, работи бавно и заема много памет. Второто решение няма проблем с бързината, но вече споменахме неговият основен проблем – не може да се отиде на произволна страница. Дали не може първият вариант да се подобри?

Една често препоръчвана техника, която се справя с този проблем е извършване на отложено свързване (deferred join). Идеята е да се създаде допълнителен индекс по колоните, по които ще се сортира:

ALTER TABLE products ADD INDEX(name, num);

след което заявката с LIMIT и OFFSET да се изпълни по следния начин, използвайки SELF JOIN (примерът е за втора страница):

SELECT p1.name, p1.price FROM products AS p1
JOIN (SELECT num FROM products ORDER BY name, num LIMIT 2 OFFSET 2) AS p2
ON p1.num = p2.num;

Идеята тук идва от следното: вложената в JOIN клаузата заявка (таблица p2) работи само и единствено със създадения индекс – така тя реално НЕ прави дискова операция. Ето пример между оригиналната заявка, в която в SELECT се извличаха name и price:

EXPLAIN SELECT name, price FROM products ORDER BY name, num LIMIT 2 OFFSET 2;
+------+-------------+----------+------+---------------+------+---------+------+------+----------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+------+-------------+----------+------+---------------+------+---------+------+------+----------------+
|    1 | SIMPLE      | products | ALL  | NULL          | NULL | NULL    | NULL | 9    | Using filesort |
+------+-------------+----------+------+---------------+------+---------+------+------+----------------+

а ето и новата вложена заявка p2, при които price не се извлича, т.е. всичко, което заявката трябва да върне като резултат (num), се съдържа в създадения от нас индекс:

MariaDB [pagination]> EXPLAIN SELECT num FROM products ORDER BY name, num LIMIT 2 OFFSET 2;
+------+-------------+----------+-------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+-------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | products | index | NULL          | name | 1026    | NULL | 4    | Using index |
+------+-------------+----------+-------+---------------+------+---------+------+------+-------------+

От удебеления текст ще видите, че първата заявка използва достъп до диска, докато втората работи само с индекса, което разбира се е много по-бързо. Тоест при предложеното решение самото страниране (заявката p2, с която се премахва излишната информация) ще се случва по-бързо, а после добавянето на нужната за потребителя информация (name, price) се извлича от диска само за намерените редове (което е забавяне, но по правило е по-малко, отколкото техниката с LIMIT и OFFSET с filesort).