1 Расширенный SQL в MySQL и PostgreSQL. Сравнение возможностей. Даниил Каменский

2 План доклада Расширения SQL пользовательские переменные оконные функции рекурсивные запросы расширения для работы с таблицами

3 План доклада Особенности и отличия базовых функций в MySQL и PostgreSQL типы таблиц, особенности физического размещения работа с индексами оптимизация подзапросов дополнительно

4 Польз. переменные MySQL Поддерживаются с версии Возможность присваивать значения и использовать далее в рамках сессии FROM `shop`; SELECT * FROM `shop` WHERE

5 Пример 1: Пики загрузки Поиск максимального различия между смежными значениями

6 Структура таблицы

7 Без явной сортировки ALTER table `t` ORDER BY `t` ASC; SET FROM `t`;

8 Сортировка индексом CREATE INDEX t_v ON t(t, v); SET FROM `t` FORCE INDEX (t_v);

9 Сортировка в запросе SET FROM (SELECT * FROM `t` ORDER BY t);

10 Аналог в модели РБД SELECT MAX( t1.v - ( SELECT v FROM t t2 WHERE t2.t < t1.t ORDER BY t2.t DESC LIMIT 1) ) FROM t t1;

11 Пример 2 Оценка загрузки каналов связи за определенные временные промежутки

12 Реализация через ПП select t, v from ( select t, if (t % := := v) as v, if( (t + 1) % 5,1,0) as mark from t) tt where mark = 0; select sum(v) from t group by (t-(second(t)%5));

13 Детализация запроса

14 Длительности выполнения

15 Пример 3 Поиск n элементов группы по критерию. CREATE TABLE salary ( dep_id int, --идентификатор отдела emp_id int, --идентификатор работника sal int -- зарплата );

16 Запрос через @p=0; SELECT * FROM (SELECT * FROM `salary` ORDER BY dep_id ASC, sal DESC) t WHERE if or

17 Аналог в РБД SELECT t1.* FROM salary t1 HAVING ( (SELECT count(*) FROM `salary` t2 WHERE t2.dep_id=t1.dep_id AND t2.sal>t1.sal)

18 SWAP колонок, rownum UPDATE t SET field1=field2, field2=field1 Неправильный результат! UPDATE t SET SELECT AS rownum, t.* FROM t;

19 Оконные функции PostgreSQL Выполнение вычисления над списком строк в таблице, которые так или иначе относятся к текущей строке Вычисление агрегатного значения без использования GROUP BY

20 Примеры функций lag() – предыдущее значение в разбиении row_number() – номер строки в разбиении first_value() – первое значение в разбиении

21 Оконные функции SELECT dep_id, emp_id, sal, 100 * sal::float / (sum(sal) OVER (PARTITION BY dep_id)) FROM salary WHERE dep_id = 1;

22 Макс. перепад через ПП В postgresql.conf custom_variable_classes = 'lv' select set_config ('lv.data', 0', true); select set_config ('lv.max_value', '0', true); select current_setting('lv.data')::integer

23 Макс. перепад через ОФ SELECT max(v) FROM (SELECT p.v - lag(t.v) OVER (ORDER BY t) as v FROM t ) tt;

24 Макс. перепад через функцию create function public.temp_func() returns int as $$ DECLAREprev integer; curr integer; max_ integer; BEGINmax_ := 0;prev := 0; FOR curr IN SELECT v FROM ppLOOP if (curr - prev > max_) thenmax_ := curr - prev; end if; prev := curr;END LOOP; return max_; END $$ language plpgsql;

25 Скорости выполнения запросов в PostgreSQL Таблица на 10,000,000 записей Оконные функции: 2,5 cек Пользовательские переменные: 13 сек Функция на plpgsql: 2 сек Запрос с подзапросами: 20 сек

26 Элементы групп через ОФ SELECT dep_id, emp_id, sal FROM ( SELECT row_number() OVER (partition BY dep_id ORDER BY sal desc ) num, dep_id, emp_id, sal FROM salary ) t WHERE num < 3;

27 Рекурсивные запросы with [recursive] [ ( ) ] as ( )

28 Дерево, структура

29 Обход дерева в ширину WITH RECURSIVE recursetree(val, id, level, pathstr) AS ( SELECT val, id, 0, cast('' as text) FROM tree WHERE parent_id = 0 UNION ALL SELECT t.val, t.id, rt.level + 1, rt.pathstr || '=>' || t.val::text FROM tree t JOIN recursetree rt ON rt.id = t.parent_id ) SELECT space(level) || val, id, level, pathstr FROM recursetree ORDER BY level, id;

30 Вывод рекурсивного запроса

31 Intersect SELECT t1.val FROM set_operations_1 t1 INTERSECT SELECT t2.val FROM set_operations_2 t2; SELECT val FROM set_operations_1 WHERE val IN (SELECT val FROM set_operations_2); SELECT t1.val FROM set_operations_1 t1 JOIN set_operations_2 t2 using(val);

32 Except SELECT t1.val FROM set_operations_1 t1 EXCEPT SELECT t2.val FROM set_operations_2 t2; SELECT val FROM set_operations_1 WHERE val NOT IN (SELECT val FROM set_operations_2); SELECT t1.val FROM set_operations_1 t1 LEFT JOIN set_operations_2 t2 USING (val) WHERE t2.val IS NULL

33

34 Работа с таблицами ON DUPLICATE KEY insert into param(param, val) values(project_version,100) on duplicate key update val=100'; REPLACE INTO replace into param(param, val) values(project_version, 100);

35 Работа с таблицами INSERT IGNORE insert ignore into param(param, val) values(project_version,100) insert into param(param, val) select 'project_version', 100' where 1 not in (select 1 from param where param = 'project_version');

36 Реализация upsert в PG CREATE OR REPLACE FUNCTION upsert(id_ integer, val_ integer) RETURNS void AS $$ BEGIN LOOP UPDATE t SET val = val_ WHERE id = id_ IF found THEN RETURN; END IF; BEGIN INSERT INTO t(id, val) VALUES (id_, val_); RETURN; EXCEPTION WHEN unique_violation THEN END; END LOOP; END; $$ LANGUAGE 'plpgsql';

37 Создание правила CREATE RULE "t_on_duplicate_ignore" AS ON INSERT TO "t" WHERE EXISTS(SELECT 1 FROM y WHERE pk_col = NEW.pk_col_1) DO INSTEAD NOTHING;

38 Типы таблиц в MySQL InnoDB MyISAM Memory Merge Archive Blackhole

39 Типы таблиц в MySQL tmp_table_size max_heap_table_size innodb_table_per_file

40 Индексы Основные типы Btree Hash MySQL – только memory-таблицы PostgreSQL – hash не рекомендуется

41 Индексные алгоритмы MySQL: Невозможность использовать составные индексы при нестрогом равенстве в начале SELECT * FROM t WHERE a

42 Алгоритмы JOIN MySQL: nested loop PostgreSQL: nested loop join merge join hash join

43 Функц. и сост. индексы CREATE INDEX i ON billing(is_payed) WHERE is_payed = false; CREATE INDEX i ON users ( substr( , strrevpos( , '.') + 1 ) ); CREATE INDEX i ON y(f1 ASC, f2 DESC);

44 MySQL - подзапросы SELECT * FROM users WHERE id IN (SELECT poster_id FROM posts WHERE poster_ip LIKE %); SELECT * FROM users u JOIN posts p ON u.id=p.poster_id WHERE p.poster_ip LIKE % ;

45 Анонимные блоки кода MySQL: CREATE/DROP IF EXISTS/IF NOT EXISTS PostgreSQL: DO $$ begin if exists (select 1 from pg_tables where tablename = t') then drop table t;end if; end $$;

46 Дополнение MySQL: Фальшивые Foreign Keys у таблиц MyISAM Фальшивые hash-индексы у таблиц не memory При ANSI возможность делать ошибки – например SELECT avg(a), b FROM t; PostgreSQL: Отсутствие возможности менять колонки местами Генерация исключений в хранимых процедурах

47 Спасибо за внимание!

48 Планы исполнения запросов А. Запрос с ПП Синтаксический анализ запроса Создание плана исполнения Определение хранилища базы (engine) Цикл foreach { Обращение к engine } Б. Цикл внутри ХП { Синтаксический анализ запроса (курсор) Определение хранилища базы (engine) Обращение к engine Переход к следующему шагу цикла (интерпретатор языка SQL) }