ФорумПрограммирование → Заметки про SQL
2017-11-21 02:00:52
В этой теме я буду оставлять заметки по SQL (точнее, по тому диалекту, который используется в MySQL).
2017-11-21 04:12:15
Оператор LIMIT.



Оператор LIMIT в SQL используется, когда нужно сделать обработку некоторого определённого количества или диапазона записей. Типичный запрос выглядит следующим образом:



SELECT *
FROM table
WHERE condition
LIMIT number;



В данном случае number определяет количество записей, которое нужно обработать. Причём, счёт будет идти с начала таблицы. Похожим образом можно определить диапазон, при этом задаются два значения через запятую: первое определяет начало диапазона (счёт с нуля) или, иными словами, смещение – сколько нужно пропустить значений перед нужным диапазоном, второе значение – задаёт количество записей. Предыдущий код таким образом можно переписать так:



SELECT *
FROM table
WHERE condition
LIMIT 0, number;



Смещение, также, можно задать с помощью ключевого слова OFFSET.
Ниже приведены два аналогичных фрагмента кода:



LIMIT skip, number;



LIMIT number OFFSET skip;



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



SELECT *
FROM posts
WHERE topicid = current
LIMIT pagesize
OFFSET pageoffset;



Здесь pagesize задаёт количество записей (постов на странице), а pageoffset = pagenumber * pagesize, где pagenumber – номер страницы (при нумерации с нуля).
Всё хорошо работает до того момента, когда смещение станет достаточно большим и при выборке нужно будет пропустить тысячи записей перед требуемым диапазоном. Тогда начнутся тормоза и придёт время оптимизации. Благо это сделать не сильно сложно. Запрос следует переписать следующим образом:



SELECT *
FROM posts
JOIN
    (SELECT id 
    FROM posts 
    ORDER BY id LIMIT pagesize OFFSET pageoffset) as subrange
ON subrange.id = posts.id
WHERE topicid = current;
2017-11-22 09:10:28
Как определить номер записи в запросе.

В самом простейшем варианте, в любой таблице есть уникальное автоинкрементируемое поле id, потому достаточно получить его значение. Но в реальных задачах зачастую простейшие решения не работают. Сегодня я сам столкнулся очередной раз с подобной задачей, потому решил сразу же написать заметку про то, как с ней справляться. Задача появилась при реализации хлебных крошек в трекере. Трекер работает очень просто – он берёт последние 30 записей из таблицы posts в обратном порядке элементарным запросом:
SELECT `id`, `topicid`, `userid`, `content`, `created` FROM `posts` ORDER BY `id` DESC LIMIT 0, 30;

Все эти данные вы можете потом наблюдать на странице трекера /tracker/
В хлебных крошках каждого поста в трекере выводятся такие ссылки: на сам форум, на секцию/раздел форума, на тему, на страницу и номер поста. Проблема в том, что в вышеприведённой выборке нет абсолютно никакой информации о том, под каким номером находится пост в своей теме. id в данном случае совсем ни о чём не говорит, так как таблица posts содержит все посты форума в порядке их создания.

Решение напрашивается само собой. Нужно сделать выборку всех постов из темы, в которой находится текущий пост. В этом поможет значение topicid. При создании выборки пронумеровать все посты с помощью переменной, а потом узнать номер поста в этой выборке с нужным нам id.

Первым делом создадим переменную:
SET @cnt := 0;

Вторым делом напишем внутренний запрос:
SELECT `id`, (@cnt := @cnt + 1) as `cnt` FROM `posts` WHERE `topicid`=:topicid

Здесь как раз делается выборка всех постов из той темы, в которой находится интересующий нас пост, а также каждой записи присваивается порядковое значение с помощью @cnt.
А теперь из этой выборки нужно взять порядковый номер с нужным id:
SELECT t.`cnt` FROM
(
	...
) as t
WHERE t.`id`=:id;

Обозвать как-нибудь вложенный запрос (в данном случае – t) нужно исключительно из-за заморочек MySQL. В результате такого запроса, мы получим единственную запись с единственным полем, которое будет содержать порядковый номер поста в теме. А теперь весь SQL-код целиком:
SET @cnt := 0;
SELECT t.`cnt` FROM
(
	SELECT `id`, (@cnt := @cnt + 1) as `cnt` FROM `posts` WHERE `topicid`=:topicid
) as t
WHERE t.`id`=:id;

2017-11-22 18:15:24
Но зачем? Не проще ли сохранять номер поста темы в саму запись поста? При удалении записи из темы - вести пересчет всех записей темы, где номер поста > удаляемого.

Использование заранее расчитанных значений позволит значительно снизить нагрузку на треккер.
2017-11-22 20:20:47
> Но зачем?
Попрактиковаться в работе с SQL.

> Не проще ли сохранять номер поста темы в саму запись поста?
Проще.

> При удалении записи из темы - вести пересчет всех записей темы, где номер поста > удаляемого.
Вот это уже не проще, а примерно так же.

> Использование заранее расчитанных значений позволит значительно снизить нагрузку на треккер.
На данный момент подобный запрос занимает считанные миллисекунды, которые по пальцам можно пересчитать. Но, ты, безусловно, прав, твой вариант будет работать быстрее.
2017-11-22 20:33:47
Drazd
Добавил подсчёт времени генерации страниц. Можешь открыть html-код любой страницы и в конце посмотреть, сколько времени занимает её генерация на данный момент вместе со всеми sql-запросами и прочей чепухой – это капля в море.
2017-11-29 18:34:12

SQL JOINS



изображение
2017-12-05 01:24:15

Основы языка SQL. Оператор SELECT.