3 записи с тегом "db"

Случайность — маска неузнанной закономерности
Александр Волков

Как-то я писал о том как быстро можно сделать выборку в MySQL по большой таблице в случайном порядке не используя ORDER BY RAND(), теперь этот же вопрос всплыл на Postgre. Если вы не знаете, то ORDER BY RAND достаточно медленная операция из-за того, что сортируется фактически вся таблица. Некоторые советовали всё-таки обычную сортировку или чуть-изменённую с использованием Primary Key, но я на своём опыте убедился что конструкция IN всегда медленней чем EXISTS.

Электроны и дырки​

Вообще задача подразумевает выборку и нескольких случайных рядов, но она сводится к выборке одного ряда, а точней ID. Самый простой способ - взять минимум (MIN или 1), максимум (MAX или значение Sequence) и случайным образом тыкнуть в промежутке. Задача чем-то напоминает процесс хэширования. И всё хорошо покуда всё идёт по порядку и ряды не удаляются. Как только в базе возникают пустоты (а они возникают вероятностью пропорционально размеру проекта), то получается что ряд не находится.

Использовать наиболее близкий к этой дырке ID'шник мы не можем из-за неравномерного распределения вероятностей и не гарантированного присутсвия вообще каких-то данных если мы в конце. Хотя можно плюнуть..

SELECT * FROM mytable WHERE id >= CEIL(RANDOM()*(SELECT MAX() FROM mytable)) ORDER BY id LIMIT 1;

Лотерея​

Делать цикл с простукиванием каждой дыры тоже рискованно - вставит кто-нибудь гипер прыжок ID с 435 на 43600 и сложность алгоритма прыгнет основательно. 

Можно оторваться от ID'шек и внести случайны float-параметр. Такой лотерее дыры в ID уже неважны. Но  неравномерность вероятности всё-таки существует и в таком случае. Правда чем больше рядов, тем она незначительней, но тем больше места занимают эти лишние данные.

ALTER TABLE mytable ADD myrand DOUBLE PRECISION; --добавляем колонку UPDATE mytable SET myrand = RANDOM(); --очень медленная процедура ALTER TABLE mytable ALTER myrand SET NOT NULL DEFAULT RANDOM(); --ставим случайное значение для новых рядов по умолчанию SELECT * FROM mytable WHERE myrand >= (SELECT RANDOM() OFFSET 0) ORDER BY myrand ASC LIMIT 1;

Горст и грабен​

Ещё простое решение - использовать случайный OFFSET. Сразу минус - LIMIT конструкция не позволяет динамически задавать значения, но образно идея такая:

SELECT CEIL(RANDOM()*(SELECT COUNT(*) AS cnt FROM mytable)); --получим из базы offset SELECT * FROM quotes LIMIT 1 OFFSET 43600; --и передадим в явном виде в запрос

LIMIT сразу перейдёт к нужному ряду - нет нужды в ID, а значит больше свободы. Единственный вопрос в скорости. Ведь используемая внутри COUNT функция - аггрегирующая, а в Postgre она отличается от обычного счётчика в MySQL. Ко всему прочему, как говорит документация - OFFSET может быть неэффективным в больших базах из-за того, что обработка предыдущих рядов всё-равно происходит.

Встала задача экспортирования данных из таблицы Firma в качестве строки. Если бы дело было в одноразовом копировании то проблемы особой и не-было бы - всегда есть клиентские программы, которые экспортируют хоть в Excel, хоть в текстовый файл какие угодно колонки. Но мне необходимо было это сделать при помощи SQL, потому что прямого доступа к live-базе данных у меня нет, а размер таблицы мне неизвестен.

Вход - таблица Firma

1=>Google;2=>Microsoft

Агрегирующие функции занимаются именно такой задачей - на входе у них массив данных, а на выходе - одно значение. Типичным примером являются функции COUNT, MAX, SUM. Postgre пошла по пути универсальности и позволяет пользователю самому создавать такие функции. Создаём CONCAT функцию:

CREATE AGGREGATE concat (  
BASETYPE = text,  
SFUNC = textcat,  
STYPE = text,  
INITCOND = ''  
);

Теперь можно сразу получить строку с данными, но мне нужна сортировка по названию фирмы, но никакие вложенные запросы и ORDER BY этому не помогают:

SELECT CONCAT(name||';') FROM Firma;

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

CREATE VIEW FirmaSorted AS SELECT * FROM Firma ORDER BY name;

Остаётся сделать выборку, в которую я добавлю ещё одно поле, уберу последний символ-разделитель и сделаю UPDATE полученного значения в другую таблицу:

UPDATE tblLists SET strValue=(  
SELECT TRIM(';' FROM (SELECT CONCAT(id||'=>'||name||';') FROM FirmaSorted))  
) WHERE strName='companies';

PostgreSQL - крутая СУБД, потому что..

1. Она не принадлежит какой-либо компании, как например Oracle или mysql и распространяется по BSD-лицензии

2. Имеет поддержку продвинутых фишек - PL/pgSQL, триггеров. Теоретически это очень полезно при создании очень масштабной системы.

3. Транзакции по принципам ACID придают скорости - несколько запросов в одной транзакции выполняются как одна атомарная операция. А мультиверсионность помогает избежать блокировки таблиц.

4. Объектность. Таблица выступает фактически в виде класса, а ряды - в виде объектов, так же как классы могут наследоваться, так и таблицы могут наследоваться.

5. Система привилегий операций с таблицами, схожа с системой по принципу с Oracle.

6. Репликации, т.е. использование нескольких серверов даёт масштабируемость.

Переход на PostgreSQL как правило связан с обработкой больших объемов данных - когда число рядов переваливает миллион, то mysql начинает испытывать трудности, а Natural Join нескольких таких таблиц может привести вообще к падению сервера. Да и тесты на параллельность поддерживают Postgre. Энтузиастам может быть интересна же не столь масштабируемость, сколько объектность, позволяющая большую свободу, или если хотите - сложность, по сравнению с mysql.

При переходе из mysql, следует отметить различия, которые естественно следует предусмотреть:

• Различные типы данных. Нет datetime, зато есть возможность создать тучу своих типов.
• Отсутсвие autoincrement. Такая функция заменена наличием sequence, и в упрощённом виде может использоваться так:
  CREATE TABLE mynewtable ( id SERIAL }
• pgAdmin III очень удобная консольная программа, а в качестве замены phpMyAdmin есть phpPgAdmin
• Аналогом DATE_FORMAT является to_char
• Заглавные буквы в названиях таблиц и полей по умолчанию переводятся в нижний регистр, это можно обойти используя двойные кавычки
• Форматом для LIMIT конструкции стал также поддерживаемый с mysql 4LIMIT # OFFSET #
• Просмотр запущенных процессов реализуется аналогом
  --show processlisе``SELECT * from pg_stat_activity ;

Читайте также:

• Субъективная разница Postgre и MySQL
  
• Замена SQL_CALC_FOUND_ROWS из MySQL в Postgre