Случайны выбор дневника Раскрыть/свернуть полный список возможностей


Найдено 2332 сообщений
Cообщения с меткой

mysql - Самое интересное в блогах

Следующие 30  »
rss_rss_hh_new

[Из песочницы] Реализация Row Level Security на MySQL

Пятница, 23 Сентября 2016 г. 16:54 (ссылка)

Привет Хабр! Мне довелось реализовать бизнес-процесс, который предполагал безопасность на уровне строк (Row Level Security) на mysql и php.



image



Row Level Security или безопасность на уровне строк — механизм разграничения доступа к информации к БД, позволяющий ограничить доступ пользователей к отдельным строкам в таблицах.



Т.к. большую часть времени я программирую на Oracle, то решил, что наиболее оптимально реализовать это в БД.



Имеем MySQL 5.1.73 с триггерами, view, хранимыми функциями и процедурами на обычном виртуальном хостинге.



В приложении таблица auth_users



CREATE TABLE `auth_users`
CREATE TABLE `auth_users` (
`conn_id` bigint(20) NOT NULL,
`user_id` int(11) NOT NULL,
`login_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`conn_id`),
KEY `user_id` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Авторизованные пользователи в текущий момент';


которая заполняется при авторизации в php



REPLACE auth_users SET conn_id = CONNECTION_ID(), user_id = :user_id


и очищается при завершении php-скрипта



	public static function user_logout(){
// Очистим таблицу auth_users
app()->db->query("DELETE FROM auth_users WHERE conn_id = CONNECTION_ID()");
}
...
register_shutdown_function(array('Auth', 'user_logout'));


Пример схемы данных:




  • справочник организаций



    CREATE TABLE `organizations`
    CREATE TABLE `organizations` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(100) NOT NULL,
    `type` varchar(100) DEFAULT NULL,
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Организации';
    INSERT INTO organizations (id, name, type) VALUES (1, 'Склад Москва', 'Склад'), (2, 'Склад Новосибирск', 'Склад');


    SELECT * FROM organizations;
    +----+-----------------------------------+------------+
    | id | name | type |
    +----+-----------------------------------+------------+
    | 1 | Склад Москва | Склад |
    | 2 | Склад Новосибирск | Склад |
    +----+-----------------------------------+------------+
    2 rows in set (0.00 sec)



  • настройка доступа:




    1. Кладовщик №1 user_id = 1, имеет доступ на просмотр документов «Склад Москва», на просмотр и редактирование документов «Склад Новосибирск»

    2. Кладовщик №2 user_id = 2, имеет доступ на просмотр документов «Склад Новосибирск», на просмотр и редактирование документов «Склад Москва»

    3. Директор user_id = 3, имеет доступ на просмотр документов «Склад Новосибирск» и «Склад Москва»

    4. Бухгалтер user_id = 4, имеет доступ на просмотр и редактирование документов «Склад Новосибирск» и «Склад Москва»

    5. Менеджер №1 user_id = 5, имеет доступ на просмотр документов «Склад Москва»

    6. Менеджер №2 user_id = 6, имеет доступ на просмотр документов «Склад Новосибирск»



    CREATE TABLE `user_access`
    CREATE TABLE `user_access` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `user_id` int(11) NOT NULL,
    `warehouse_org_id` int(11) NOT NULL,
    `edit` tinyint(1),
    PRIMARY KEY (`id`),
    CONSTRAINT `user_access_ibfk_1` FOREIGN KEY (`warehouse_org_id`) REFERENCES `organizations` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Доступ пользователей';
    INSERT INTO user_access (user_id, warehouse_org_id, edit) VALUES (1, 1, NULL), (1, 2, 1);
    INSERT INTO user_access (user_id, warehouse_org_id, edit) VALUES (2, 1, 1), (2, 2, NULL);
    INSERT INTO user_access (user_id, warehouse_org_id, edit) VALUES (3, 1, NULL), (3, 2, NULL);
    INSERT INTO user_access (user_id, warehouse_org_id, edit) VALUES (4, 1, 1), (4, 2, 1);
    INSERT INTO user_access (user_id, warehouse_org_id, edit) VALUES (5, 1, NULL);
    INSERT INTO user_access (user_id, warehouse_org_id, edit) VALUES (6, 2, NULL);


    SELECT * FROM user_access;
    +----+---------+------------------+------+
    | id | user_id | warehouse_org_id | edit |
    +----+---------+------------------+------+
    | 1 | 1 | 1 | NULL |
    | 2 | 1 | 2 | 1 |
    | 3 | 2 | 1 | 1 |
    | 4 | 2 | 2 | NULL |
    | 5 | 3 | 1 | NULL |
    | 6 | 3 | 2 | NULL |
    | 7 | 4 | 1 | 1 |
    | 8 | 4 | 2 | 1 |
    | 9 | 5 | 1 | NULL |
    | 10 | 6 | 2 | NULL |
    +----+---------+------------------+------+
    10 rows in set (0.00 sec)



  • таблица документы, содержит поле склад (по которому мы будем разграничивать доступ) и другие атрибуты документа



    CREATE TABLE `docs`
    CREATE TABLE `docs` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `warehouse_org_id` int(11) NOT NULL,
    `sum` int(11),
    PRIMARY KEY (`id`),
    CONSTRAINT `docs_ibfk_1` FOREIGN KEY (`warehouse_org_id`) REFERENCES `organizations` (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Доступ пользователей';
    DELETE FROM docs;
    INSERT INTO docs (id, warehouse_org_id, sum) VALUES (1, 1, 10000), (2, 2, 5000);


    SELECT * FROM docs;
    +----+------------------+-------+
    | id | warehouse_org_id | sum |
    +----+------------------+-------+
    | 1 | 1 | 10000 |
    | 2 | 2 | 5000 |
    +----+------------------+-------+
    2 rows in set (0.00 sec)



Итак, начнём настраивать RLS: для начала переименуем целевую таблицу docs -> t_docs



ALTER TABLE docs RENAME t_docs;


и создадим одноимённый редактируемый VIEW



CREATE OR REPLACE VIEW docs AS
SELECT id, warehouse_org_id, sum
FROM t_docs
WITH CHECK OPTION;


Теперь все запросы из клиентских приложений обращаются не напрямую к таблице, а ко VIEW



Важно! Если в системе есть функции, процедуры, запросы, которым не надо ограничивать доступ к таблице, то там необходимо прописать непосредственно таблицу, т.е. t_docs. Например, это могут быть процедуры расчёта долгов/остатков по всей системе.



Теперь сделаем простую вещь, ограничим просмотр в соответствии с контролем доступа.



CREATE OR REPLACE VIEW docs AS
SELECT id, warehouse_org_id, sum
FROM t_docs d
WHERE EXISTS (
SELECT NULL
FROM auth_users
INNER JOIN user_access ON user_access.user_id = auth_users.user_id
AND auth_users.conn_id = CONNECTION_ID()
WHERE d.warehouse_org_id = user_access.warehouse_org_id
)
WITH CHECK OPTION;


Проверим как это сработало:



SELECT * FROM docs;
Empty set (0.00 sec)


Ничего не вернулось. Действительно, ведь надо авторизоваться. Авторизуемся Менеджер №1 user_id = 5



DELETE FROM auth_users;
REPLACE auth_users SET conn_id = CONNECTION_ID(), user_id = 5;
SELECT * FROM docs;
+----+------------------+-------+
| id | warehouse_org_id | sum |
+----+------------------+-------+
| 1 | 1 | 10000 |
+----+------------------+-------+
1 row in set (0.00 sec)


Видит только документы «Склад Москва». Авторизуемся Директор user_id = 3



DELETE FROM auth_users;
REPLACE auth_users SET conn_id = CONNECTION_ID(), user_id = 3;
SELECT * FROM docs;
+----+------------------+-------+
| id | warehouse_org_id | sum |
+----+------------------+-------+
| 1 | 1 | 10000 |
| 2 | 2 | 5000 |
+----+------------------+-------+
2 rows in set (0.00 sec)


Видит документы «Склад Москва» и «Склад Новосибирск»! Вроде всё работает как надо. Тогда переходим к более сложной задаче — ограничение на редактирование. Попробуем авторизоваться Менеджер №1 user_id = 5 и отредактировать документы:



DELETE FROM auth_users;
REPLACE auth_users SET conn_id = CONNECTION_ID(), user_id = 5;
UPDATE docs SET sum = 20000 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
UPDATE docs SET sum = 15000 WHERE id = 2;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0 Changed: 0 Warnings: 0


Обновились только строки которые видим.



Но как же нам добиться различных прав на просмотр и редактирование? Можно добавить ещё одно VIEW e_docs



CREATE OR REPLACE VIEW e_docs AS
SELECT id, warehouse_org_id, sum
FROM t_docs d
WHERE EXISTS (
SELECT NULL
FROM auth_users
INNER JOIN user_access ON user_access.user_id = auth_users.user_id
AND auth_users.conn_id = CONNECTION_ID()
WHERE d.warehouse_org_id = user_access.warehouse_org_id
AND user_access.edit = 1
)
WITH CHECK OPTION;


и все DML команды пустить через это VIEW, но это потребует переписать в приложении все DML-команды и у нас будет уже 3 объекта



t_docs — исходная таблица

docs — RLS-таблица для просмотра

e_docs — RLS-таблица для редактирования



Попробуем другой вариант, более гибкий.




  1. Создадим функцию get_db_mode для отображения текущего режима — просмотр/редактирование



    DELIMITER $
    CREATE FUNCTION get_db_mode()
    RETURNS VARCHAR(20)
    BEGIN
    IF @db_mode = 'edit' THEN
    RETURN 'edit';
    ELSE
    RETURN 'show';
    END IF;
    END
    $
    DELIMITER ;



  2. Модифицируем VIEW, чтобы выдавались разные строки в режиме просмотра/редактирования



    CREATE OR REPLACE VIEW docs AS
    SELECT id, warehouse_org_id, sum
    FROM t_docs d
    WHERE EXISTS (
    SELECT NULL
    FROM auth_users
    INNER JOIN user_access ON user_access.user_id = auth_users.user_id
    AND auth_users.conn_id = CONNECTION_ID()
    WHERE d.warehouse_org_id = user_access.warehouse_org_id
    AND (get_db_mode() = 'show' OR user_access.edit = 1 AND get_db_mode() = 'edit')
    )
    WITH CHECK OPTION;



  3. Теперь при DML в BEFORE триггере будем устанавливать переменную @db_mode в 'edit', а в AFTER триггере в 'show'



    CREATE TRIGGERS
    DELIMITER $
    CREATE TRIGGER `docs_bef_ins_trg` BEFORE INSERT ON `t_docs` FOR EACH ROW
    BEGIN
    SET @db_mode = 'edit';
    END
    $
    CREATE TRIGGER `docs_bef_upd_trg` BEFORE UPDATE ON `t_docs` FOR EACH ROW
    BEGIN
    SET @db_mode = 'edit';
    END
    $
    CREATE TRIGGER `docs_bef_del_trg` BEFORE DELETE ON `t_docs` FOR EACH ROW
    BEGIN
    SET @db_mode = 'edit';
    END
    $

    CREATE TRIGGER `docs_aft_ins_trg` AFTER INSERT ON `t_docs` FOR EACH ROW
    BEGIN
    SET @db_mode = 'show';
    END
    $
    CREATE TRIGGER `docs_aft_upd_trg` AFTER UPDATE ON `t_docs` FOR EACH ROW
    BEGIN
    SET @db_mode = 'show';
    END
    $
    CREATE TRIGGER `docs_aft_del_trg` AFTER DELETE ON `t_docs` FOR EACH ROW
    BEGIN
    SET @db_mode = 'show';
    END
    $
    DELIMITER ;



Вуаля, проверяем как всё работает:



Авторизуемся Кладовщик №1 user_id = 1



DELETE FROM auth_users;
REPLACE auth_users SET conn_id = CONNECTION_ID(), user_id = 1;
SELECT get_db_mode();
+---------------+
| get_db_mode() |
+---------------+
| show |
+---------------+
1 row in set (0.00 sec)
SELECT * FROM docs;
+----+------------------+-------+
| id | warehouse_org_id | sum |
+----+------------------+-------+
| 1 | 1 | 20000 |
| 2 | 2 | 5000 |
+----+------------------+-------+
2 rows in set (0.00 sec)
UPDATE docs SET sum = 105000 WHERE id = 2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
SELECT get_db_mode();
+---------------+
| get_db_mode() |
+---------------+
| show |
+---------------+
1 row in set (0.00 sec)
SELECT * FROM docs;
+----+------------------+--------+
| id | warehouse_org_id | sum |
+----+------------------+--------+
| 1 | 1 | 20000 |
| 2 | 2 | 105000 |
+----+------------------+--------+
2 rows in set (0.01 sec)
UPDATE docs SET sum = 205000 WHERE id = 1;
ERROR 1369 (HY000): CHECK OPTION failed '3006309-habr.docs'


Отлично, просматривать можем, редактировать не даёт. Но не всё так гладко:



SELECT get_db_mode();
+---------------+
| get_db_mode() |
+---------------+
| edit |
+---------------+
1 row in set (0.00 sec)


После ошибки не отработал AFTER триггер и не снял режим редактирования. Сейчас сделав SELECT мы увидим только те строки которые можем редактировать.



SELECT * FROM docs;
+----+------------------+--------+
| id | warehouse_org_id | sum |
+----+------------------+--------+
| 2 | 2 | 105000 |
+----+------------------+--------+
1 row in set (0.00 sec)


Один из вариантов решения, это try… catch PDO в php и выполнять принудительно SET @db_mode = 'show' при любой ошибке



Скрипты для удаления тестовых объектов
DROP TABLE IF EXISTS auth_users;
DROP TABLE IF EXISTS organizations;
DROP TABLE IF EXISTS user_access;
DROP TABLE IF EXISTS docs;
DROP TABLE IF EXISTS t_docs;
DROP VIEW IF EXISTS docs;
DROP FUNCTION IF EXISTS get_db_mode;


Теперь, всю логику по контролю доступа очень легко прописать в одном VIEW. По этой же схеме легко реализовать различный доступ на операции INSERT/UPDATE/DELETE
Original source: habrahabr.ru.

https://habrahabr.ru/post/310832/?utm_source=habrahabr&utm_medium=rss&utm_campaign=best

Метки:   Комментарии (0)КомментироватьВ цитатник или сообщество
lasahpte

Как восстановить таблицу

Четверг, 15 Сентября 2016 г. 12:58 (ссылка)

Как восстановить таблицу. Сегодня MySQL является одним из наиболее популярных решений среди малых и средних систем управления базами данных. Из достоинств MySQL можно выделить возможность работы с таблицами разных типов. Один из них - MyISAM. Такие таблицы отлично подходят для хранения часто запрашиваемых данных, но при сбоях в процессе модификации могут быть легко повреждены. Поэтому нередки случаи, когда требуется восстановить таблицу типа MyISAM. ЧИТАТЬ ДАЛЬШЕ>>>



Комментарии (0)КомментироватьВ цитатник или сообщество
fongecal

Как выполнить SQL-запрос

Четверг, 15 Сентября 2016 г. 11:37 (ссылка)

Непрофессиональному программисту иметь дело с SQL-запросами чаще всего приходится при работе с интернет-ресурсами. Большинство из них - блоги, форумы, системы управления сайтами и т.д. - использует в работе базы данных СУБД MySQL. Для этой СУБД существует очень популярное приложение, позволяющее управлять как отдельными таблицами, так и целыми базами данных. Создание SQL-запросов в PhpMyAdmin - так называется это приложение - возможно как в формате диалога, так и с помощью ручного ввода операторов. ЧИТАТЬ ДАЛЬШЕ>>>



Метки:   Комментарии (0)КомментироватьВ цитатник или сообщество
fongecal

Как выполнить SQL-запрос

Четверг, 15 Сентября 2016 г. 11:28 (ссылка)

Непрофессиональному программисту иметь дело с SQL-запросами чаще всего приходится при работе с интернет-ресурсами. Большинство из них - блоги, форумы, системы управления сайтами и т.д. - использует в работе базы данных СУБД MySQL. Для этой СУБД существует очень популярное приложение, позволяющее управлять как отдельными таблицами, так и целыми базами данных. Создание SQL-запросов в PhpMyAdmin - так называется это приложение - возможно как в формате диалога, так и с помощью ручного ввода операторов. ЧИТАТЬ ДАЛЬШЕ>>>



Метки:   Комментарии (0)КомментироватьВ цитатник или сообщество
rss_rss_hh_new

Как устроена MySQL-репликация

Среда, 07 Сентября 2016 г. 19:13 (ссылка)

Андрей Аксёнов



Андрей Аксенов (Sphinx), shodan



Мой доклад предназначен для тех людей, которые знают слово «репликация», даже знают, что в MySQL она есть, и, возможно, один раз ее настроили, 15 минут потратили и забыли. Больше про нее они не знают ничего.



Мы немного пройдемся по теории, попытаемся объяснить, как это все работает внутри, а после этого вы с утроенными силами сможете сами нырнуть в документацию.



Что такое репликация, в принципе? Это копирование изменений. У нас есть одна копия БД, мы хотим с какой-то целью еще одну копию.



Репликация бывает разных видов. Разные оси сравнения:




  • степень синхронизации изменений (sync, async, semisync);

  • количество серверов записи (M/S, M/M);

  • формат изменений (statement-based (SBR), row-based (RBR), mixed);

  • теоретически, модель передачи изменений (push, pull).



В докладе не будет:







Все это есть в Интернете, синтаксис разбирать смысла нет.





Забавный факт — если немного задуматься, репликация нам теоретически помогает из принципиальных соображений скейлить только чтение. Вот такой несколько неочевидный вывод. Это потому что, если у нас на одну и ту же копию данных надо налить определенное количество изменений, и эта определенная копия данных обслуживается одним и тем же сервером, то этот сервер способен выдержать определенное количество апдейтов в секунду, и больше туда не залить. Способен сервер обновить 1000 записей в секунду, а 2000 — не способен. Что изменится от того, что ты поставишь к этому серверу реплику, неважно, в режиме мастер-слэйв или мастер-мастер? Сумеешь ты на эту реплику налить вторую тысячу апдейтов? Правильный ответ — нет.



На реплику в режиме мастер-мастер ты, конечно, сумеешь налить дополнительных апдейтов, другое дело, что, когда они не прилетят на первый мастер и попытаются на нем сделать вторую тысячу апдейтов, то емкости уже не хватит. Надо понимать и не смешивать два почти очевидных момента, что репликация, как бы, про одно, а то, что данные надо дробить, и если надо скейлить не чтение, а записи, то придется делать что-то другое, а репликация не очень спасет.



Т.е. репликация — это больше про чтение.



Про синхронизацию.



Синхронизация — гарантия наличия и доступности. Доступности в том смысле, что у нас commit прошел, транзакция зафиксировалась, все хорошо, эти данные видно одной или нескольким нодам в кластере, они могут участвовать в следующих запросах. Наличие — это то, что данные, в принципе, есть более чем на одном сервере, но, возможно, транзакция не проигралась и не доступна.



Здесь нет рефрена «commit закончился успешно, что это значит?». Синхронный commit означает, что у нас локальный и удаленный (хотя бы на одной реплике) закончился, т.е. мы что-то закоммитили на машину, если у нас синхронный режим репликации, то эти изменения успешно закоммитились, они видны для последующих запросов на локальной машине, на удаленной машине (хотя бы на одной) тоже видны. Это означает, что если случилась стандартная внештатная ситуация, т.е. в один и серверов прилетел лом и пробил все насквозь — от процессора до самого винта, то, несмотря на это, данные не только скопированы на некий удаленный сервер, но еще, вдобавок, могут мгновенно, без каких-то дополнительных задержек, участвовать в последующих транзакциях.



Это все общая терминология, никак совершенно не связанная с MySQL. В любой распределенной системе оно будет устроено так.



Асинхронный commit — никаких дополнительных гарантий, как повезет.



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



Про сервера для записи. Какие бывают виды репликации.



Master-slave classic, изменения все льются на один сервер, после этого копируются на массу реплик.



Master-master true — когда изменения льются на кучу мастеров одновременно и каким-то образом с одного на другой, с другого на третий и между ними всеми, что порождает и ряд радостей, и ряд автоматических проблем. Понятно, что когда у тебя есть одна «золотая копия» и с нее несколько реплик, которые должны (в идеале — мгновенно) повторять эту «золотую копию», то все сравнительно просто с точки зрения того, как данные туда-сюда гонять и что делать на каждой конкретной копии. С master-master начинается интересная «головная боль», причем, подчеркиваю, не конкретно в случае MySQL, а сугубо теоретическая. Как же быть, если на двух нодах одновременно попытались прогнать одну и ту же транзакцию, которая меняет одни и те же данные, причем, меняет их, для простоты примера, по-разному. Понятно, что одновременно эти два изменения мы применить не можем. На момент, когда мы на одной ноде начинаем что-то изменять, на второй ноде еще пока ничего нет. Конфликт. Одну из транзакций придется откатывать. Вдобавок начинаются отдельные «пляски» со сверкой часов и т.п.



Любопытный момент — даже вариант, когда у вас в конечном итоге все изменения со всех мастеров должны постепенно распространиться везде, все равно не поможет тому самому write bandwidth. Обидно, но вот так.



Приятный вариант — под названием«Master-slave + routing запросов». Приятен он тем, что внутри программировать просто, у тебя есть одна основная копия, ты ее реплицируешь на кучу машин. Это намного проще, чем в мастер-мастер среде, когда все равноправны и т.д., но с точки зрения приложения все равно выглядит так, будто у тебя точек записи много. Ты приходишь на любую ноду, она знает, куда тебя зароутить, и успешно роутит. Ну, и чтения масштабируются — вот оно счастье репликации. Читать можно со всех точек все и всегда.



Теперь ближе к базам данных, «волшебным» форматам statement-based, row-based и т.д. Про формат изменений.



Что можно делать? Можно передавать сами запросы, а можно передавать только измененные строки. Подчеркиваю — пока мы еще не нырнули в дебри MySQL, этим может заниматься любая СУБД, в которой есть запросы, порождающие большое (или не очень) количество изменений, т.е. обновляющие много данных. Возникает вопрос — а что конкретно будем копировать? Можно сами запросы туда-сюда между нодами гонять, а можно гонять только измененные данные. Интересно, что и так и эдак очень плохо! Можно еще пытаться смешивать.



Еще один пункт про то, какие бывают репликации. Про модель распространения. Наверное, где-то до сих пор еще не полностью вымерла модель Push-based, когда та нода, которая внесла изменения, та и обязана их рассылать по всем остальным нодам. С точки зрения программирования и отслеживания state'ов это та еще морока. Поэтому рулит Pull-based. Забирать апдейты с той или иной ноды — это намного проще запрограммировать, чем на одной ноде следить за хаотичным кластером своих реплик.



Некие общие термины ввели. Переходим к тому, как сделали в MySQL.



MySQL, сам по себе, это некий обман. Есть логический слой под названием MySQL, который занимается всяким общими и изолированными от хранения данных делами — сеть, оптимизатор, кэши и т.д. Конкретный физический слой, который отвечает за хранение данных, лежит на этаж ниже. Есть несколько встроенных, есть ставящиеся плагинами. Но даже встроенные MyISAM, InnoDB и т.д. живут на физическом слое. Плагинная архитектура — это клево, можно подцепить новый движок, но мгновенно возникает некая неоптимальность. В принципе, транзакционные write-ahead log'и (WAL), которые физический слой хранения все равно пишет, было бы хорошо использовать для репликации, и если система знает о том, что есть некий физический уровень, или достаточно хорошо сопряжена с этим физическим уровнем, то можно было бы отдельный лог на логическом уровне не писать, а использовать тот же самый WAL. Но у MySQL это невозможно концептуально, либо, если поменять интерфейс в PSE так, чтобы стало возможно концептуально, то будет очень много работы.



Репликация реализована на уровне самого MySQL. В этом есть и хорошее — помимо одного лога в виде глубоко внутренних данных движка хранения, есть более-менее логический лог, возможно, на уровне statement'ов, который ведется отдельно от этого движка. А это «лишняя» безопасность и т.д. плюс, поскольку никаких ограничений внутри нет, можно делать всякий креатив типа подмены движка «на лету».



В веденных терминах в MySQL 4.1 было реализовано: master-slave, pull-based, строго async и строго SBR. Если вы застряли в древней эпохе 4.х, то, наверное, у вас все плохо. Версиям 5.х уже чуть ли не 10 лет — пора бы и обновиться.



Забавно прослеживать по версиям, как люди наступали на всяческие грабли и, когда сделать уже ничего было нельзя, прикручивали к этим граблям новые грабли, чтобы жизнь была не такая болезненная. Так, в версии 5.1 прикрутили RBR, чтобы компенсировать неизбежные проблемы с SBR, и прикрутили mixed режим. В версии 5.6 прикрутили еще приятных штук: semi-sync, delayed slave, GTID.



Еще один момент. Поскольку MySQL — это некий общий слой, с одной стороны, и куча pluggable движков, с другой стороны, в том числе, встроенных, там есть с определенного момента божественный NDB cluster, про который рассказывают крутое. Там полностью синхронная мастер-мастер репликация, очень доступная in-memory БД… Но есть один нюанс — как только начинаешь искать людей, которые в продакшене используют NDB cluster, то таких людей находится крайне мало.



Чем занимается мастер в тот момент, когда вы решили включить репликацию? На мастере происходит довольно мало дополнительных движений. Как обычно, мы по сети принимаем запросы, парсим их, гоняем транзакции, фиксируем их и т.д. Вдобавок к этому, на логическом уровне MySQL мастер начинает вести binary log — файл, не совсем текстовый, в который сыплются все подряд изменения. Также мастер умеет рассылать эти логи по сети. Все это очень просто и, вроде как, работает.



Чем занимается слэйв? Изменения на слэйв лучше не слать, потому что можно попасть в непонятное. У слэйва чуть больше работы. Помимо того, чтобы вести один дополнительный лог и по запросу его рассылать, еще есть тред, который ходит к удаленному мастеру, возможно, даже не к одному, и качает оттуда binary log'и. Решение «давайте ходить к нескольким удаленным мастерам и с них качать разные логи» неоднозначно. С одной стороны неплохо, а с другой получается мгновенное расхождение. Просто физически копировать файлы по SCP нельзя, уже получается на сервере один лог, в нем свои позиции, локально мы их по сетке тянем, складываем в отдельный лог, еще отдельный тред бегает и пытается проигрывать эти локальные логи. Самое адское, на мой взгляд, заключается в том, что вплоть до версии 5.6 идентификация той или иной транзакции в логе происходила по имени файла и позиции на мастере. Интересное решение.



Вот путь записи, который простенький insert проходит без репликации:







Приложение сконнектилось к серверу, положило в таблицу и отбой.



С репликацией получается несколько дополнительных шагов:







Приложение-писатель точно так же идет к мастеру, но вдобавок эти данные попадают в том или ином виде в binary log, потом качаются по сети в relay log, потом из relay log'а постепенно реплеются (если нам повезло, и слэйв не лагает, реплеются сразу) в таблицу на слэйве, после этого все доступно в читателе.



Что конкретно попадает в binary log, зависит от настроек SBR/RBR/mixed. Откуда это все растет? Представим себя базой данных. Нам прилетел простой запрос «обнови одну конкретную запись» — UPDATE users SET x=123 WHERE id=456



Что записать в binary log? В принципе, все равно, на самом деле. Можем коротенький запрос записать, либо (а он обновил одну запись) можем записать изменение каким-то образом в том или ином формате.



Другая ситуация. Представим, что нам прилетел тот самый запрос, который сам по себе маленький, а данных меняет много — UPDATE users SET bonus=bonus+100



Тут эффективный вариант один — писать сам запрос, потому что запрос — ровно 32 байта, а записей он может обновить произвольное количество — 1000, 100 000, 1 000 000, сколько угодно… Неэффективно писать измененные записи в лог.



А что произойдет, если мы в лог поместим такой нехитрый запрос «давайте отключим всех юзеров, которые не логинились давно» — UPDATE users SET disabled=1 WHERE last_login < UNIX_TIMESTAMP(NOW())-100*86400



Внезапно наступает ужас. Проблема в том, что если среплицировать идеально сам запрос, то, во-первых, время никогда не синхронно между двумя нодами, кроме этого, за счет того, что путь записи такой длинный, в момент реплея этот «NOW» разойдется-таки. Реплика внезапно расходится с мастером, и все последующие изменения, формально говоря, уже небезопасны, могут привести к чему угодно.



Вообще говоря, для таких запросов, вне зависимости от количества измененных данных, в идеале надо бы копировать сами строчки. В данном конкретном случае можно сами строчки не копировать, а зафиксировать константу и в лог написать не «NOW», а конкретный timestamp, который был использован мастером на момент репликации.







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




  • мастер многопоточен, а слэйв — нет. Понятно, что если мастер наливает нагрузку в четыре ядра, слэйв эту нагрузку в одно ядро наливать не успевает. Все довольно плохо;

  • состояние слэйва определяется именем позиции в файле мастера. Вдумайтесь — состояние одной ноды в кластере определяется именем файла и позицией в этом файле на другой ноде кластера, с которой может по любым причинам произойти что угодно!

  • «спасительный» RBR. Оказывается, по умолчанию туда пишутся полные before/after row image, т.е. мы изменили одну колонку в пяти-килобайтной строке, оп! — 10 Кб трафика и байтов 20-40 оверхедов на эту строку, потом оп! — едет такая жирная строка предыдущей версии, оп! — едет после этого версия с новыми значениями. Администраторы воют хором! Тем не менее, это просто офигенно с точки зрения некоторых извращенных приложений, например, внешних читалок, которые пытаются подцепиться к серверу MySQL, с него вытягивать данные и делать с ними что-нибудь, например, совать их в полнотекстовый индекс. Насколько это плохо с точки зрения администрирования базы, в которой одно изменение на три байта порождает 10 Кб трафика на винте, а потом 10 Кб трафика по сети на каждого слэйва, настолько же это хорошо для всяких систем типа полнотекстового поиска, как Sphinx, у которых нет локальной копии данных, а MySQL с нуля имплементировать нет никакого желания. В MySQL 5.6 спохватились и сделали binlog_row_image (но по дефолту full, а не minimal или noblob).



Короче говоря, устроено все не хитро — палка, веревка, один лог, второй лог. И даже в этом логе «детские» болезни довольно забавные:







Для человека, который использует репликацию два дня, все это страшно и тяжело. Но, зная, насколько она нехитро устроена, в принципе, понятно, как с ней жить:




  • прежде всего, не верим дефолтам;

  • внимательно смотрим на настройки, думаем, чего хотим — SBR, RBR и т.д.



И лучше сразу настроить, чтобы потом не разбирать странный фарш.



В ситуации «протух лог, разошлась позиция, неизвестно, что происходит» есть определенный инструментарий — смотрим event'ы, пытаемся понять, какая транзакция уже проскочила, какая — нет, можно ли все это дело спасти или восстановить и т.д. Если GTID«ы заранее сумели включить, то жизнь становится проще.



Другой момент наблюдения за репликацией. Интересно посмотреть, как внутреннее кривое устройство провоцирует не то, что конкуренцию, а создание дополнительных продуктов. „Волшебный“ Tungsten Replicator, говорят, хорошо решает задачу под названием „однопоточный слэйв — это плохо“, а если бы не врожденные сложности, не было бы дополнительного продукта, который позволяет пользоваться этим механизмом, переливать данные в другие системы, с одной стороны, и заодно решать ряд проблем, встроенных в существующую систему, с другой стороны.



Как обычно, советовать невозможно. Кому-то помогает, кто-то будет сильно плеваться. Но, говорят, есть ситуации, в которых с неизбежным однопоточным лагом хорошо справляется Tungsten. Я уверен, есть еще всякие увлекательные фокусы, но внутренний однопоточный слэйв — это тяжело.



Что делать, если вы зачем-то использовали реплики как бэкап? Я считаю, надо биться головой об стену, потому что реплика и бэкап — это две разные штуки. Тем не менее, если вы креативные пацаны и используете достаточно новую версию, delayed replication вас спасает, с одной стороны, но с другой стороны, если вы не делаете полноценных бэкапов, вас все равно ничего не спасет.



Далее еще один элемент креатива. Нетрудно представить ситуацию, когда мастер забил логами весь 10 PB облачный диск или забил рассылкой этих логов всю сеть, при этом 90% этих обновлений нам не нужны, потому что нам интересно реплицировать, например, одну таблицу прицельно или одну базу прицельно, а по умолчанию все валится валом в бинарный лог — все изменения по всем базам, по всем таблицам, по всему. Решение опять поражает своей креативностью. С одной стороны, есть четыре настройки — {binlog|replicate}_{do|ignore}_db, которые позволяют фильтровать на мастере — что запишется в лог, а что проигнорируется. На слэйве, соответственно, позволяет делать то же самое. Т.е. на мастере мы можем отфильтровать то, что попадает в binary log — в эту воронку, которая потом сливается в сеть, а на слэйве, соответственно, мы можем поставить входящий фильтр на то, что прилетает из сети. Или писать на диск только часть данных, а потом на слэйве реплеить, опять же, только часть данных. Внезапно даже в этой нехитрой истории наступает ужас, потому что комбинация — используем одну БД, а апдейтим таблицу в другой БД через интересный синтаксис — она ведет себя как-то… А как конкретно она себя поведет — неизвестно, т.к. разные фильтры срабатывают в разные моменты.



Встроенных приятных штук под названием „перевыборы мастера, если он внезапно сдох“ нет, надо поднимать руками. Отсутствие инструментов для менеджмента кластера — это, по моему мнению, хорошо — порождает конкуренцию, порождает создание дополнительных продуктов. В самом деле, если бы в обычном MySQL идеально работала очень клевая мастер-мастер репликация, или хотя бы автоматическое поднятие после сбоев, то зачем бы была нужна всякая Galera, Рercona/MariaDB Cluster и т.д.?



Еще немного фокусов. Интересна реализация репликации, которая простая как палка и веревка, без всяких проверок, с одной стороны, и без всяких инструментов, чтобы приятнее менеджить кластер реплицирующегося слэйва, с другой стороны. Это плохо. Но зато можно вручную лепить из этого такие интересные конфигурации, что содрогнутся все, кто потом придет и за вами будет это разбирать.



Конфигурация №1. Мастер-мастер «на коленке» в стиле MySQL делается вот так:







Что пугает — сколько в мире идиотов! Погуглите „Мастер-мастер MySQL репликация“ — каждая вторая ссылка вот такая. Ад и холокост.



Фокус №2 — catch-all slave — поприятнее. Никаких ненужных проверок нет — что с кого прилетает, кому попадает, и что с этим делать. За счет этого можно сделать забавные штуки типа слэйва, на который либо прицельно сливается часть данных с кучи серверов, либо прицельно сливаются все данные со всех серверов — сервер со всеми-всеми бэкапами. Но, повторюсь, репликация есть, т.е. есть некий базовый инструмент, который копирует таблицу А вместо В и все.



Ну и, наконец, фокус №3 — подменяем всякое. Вспоминаем, что репликация живет на логическом уровне, никак не связанном с физическим уровнем хранения. За счет этого можно крайне интересно чудить. Можно менять движок «на лету» с непонятными целями — вот true story, что, дескать, репликация из InnoDB баз в MyISAM таблицы просто ради того, чтобы полнотекстовый поиск работал хоть как-то. Есть креативный финт под названием „изменение схемы через репликацию“. В чем жир, понимать отказываюсь, но бывают и такие фокусы. Ну и, есть понятный и интересный режим работы под названием „параноидальный апгрейд версии через репликацию“.



В ходе доклада мы узнали:







Тем не менее, с этим адом можно жить, если хотя бы примерно понимать, как он устроен.



Основной посыл в том, что:







В 2015 году на конференции HighLoad++ Junior Андрей Аксёнов прочитал новую версию своего доклада об устройстве репликации в MySQL. Её мы тоже расшифровали и опубликовали в своём блоге.


Original source: habrahabr.ru.

https://habrahabr.ru/post/309326/?utm_source=habrahabr&utm_medium=rss&utm_campaign=best

Метки:   Комментарии (0)КомментироватьВ цитатник или сообщество
rss_rss_hh_new

[Из песочницы] Не поставил favicon на сайте — получи двойной трафик от Chrome

Среда, 07 Сентября 2016 г. 16:59 (ссылка)

Впервые за пять лет разработки интернет-сайтов я столкнулся с весьма неожиданной проблемой, стоившей мне многих часов поиска, нервов и волос на голове. Внезапно я обнаружил, что на новом сайте, который у меня сейчас в разработке на локалхосте, дублируются INSERT запросы к БД. Отправляю один комментарий через форму, а в базу вставляются два. Если вы не знаете, как связана эта проблема с Chrome, favicon.ico и ModRewrite, то добро пожаловать под кат.



Нет, это не в глазах двоится



Разумеется, первое что пришло в голову — где-то в скрипте «двоится» запрос к базе данных. Например, такая «школьная ошибка»:



$query = "INSERT INTO table VALUES(...)";
$result = mysqli->query($query);
if ($result = mysqli->query($query)) {
...
}


Проверяю сто тысяч раз — нет. С кодом все в порядке. Значит браузер по какой-то причине отправляет данные дважды. Генерирую простенький тестовый скрипт с инкрементом сессионной переменной и — да! Вместо увеличения на единицу браузер упорно показывает увеличение переменной на два. Пробую вместо Хрома Сафари — нет такой проблемы. Далее идут поиски некорректно работающих яваскриптов, расширений для браузера, но все безрезультатно. Поиск по интернету давал схожие советы. Многие программисты в аналогичной ситуации вводили проверку на уникальность и отсекали дублирующиеся посты. Но баг то это решение не устраняет и я решил не останавливаться и найти причину такого поведения.



Наконец, причина была найдена. И так как решение нашел с трудом, на англоязычной девелоперской ветке, хочу поделиться им здесь. Все просто — виновника два: Google Chrome (и производные от него браузеры) и mod_rewrite в Apache.



Суть проблемы



Все просто. Особенность браузеров, построенных на платформе Crome в том, что они ищут файл favicon.ico для каждого сайта. И если его нет, то они все равно будут упорно его искать. При каждом обновлении страницы, отдельным запросом к серверу. А большинство .htaccess файлов в Apache имеют в себе строчки:



RewriteCond %{REQUEST_FILENAME} !-f
RewriteRule . /index.php


Руководствуясь этим правилом, получая запрос от браузера к несуществующему favicon.ico Apache послушно перенаправит его к файлу index.php и скрипт отработает дважды. Конечно, в большинстве случаев полноценные веб-приложения имеют проверку на уникальность и не пропускают повторяющиеся запросы. А сайты в большинстве случаев имеют файл favicon.ico. Но все же раз существует такая проблема, значит можно описать методы решения.



Варианты решения



Решение первое, самое простое: заведите на сайте favicon.ico. Chrome найдет его и успокоится.

Решение второе — немного изменить файл .htaccess на сервере. У меня блок правил mod_rewrite для всех проектов теперь будет выглядеть так:




RewriteEngine On
RewriteBase /
RewriteCond %{REQUEST_FILENAME} !-f
# Next line is to solve the Chrome and favicon.ico file issue.
# Without it browser sends two requests to script.
RewriteCond %{REQUEST_FILENAME} !favicon.ico
# RewriteCond %{REQUEST_FILENAME} !-d
RewriteRule . /index.php [L]



Не поставил favicon? Получи двойной трафик от Chrome



Если вдуматься, то утверждение справедливо, ведь вместо того, чтобы отдать «статику», сервер будет запускать веб-приложение, которое в лучшем случае вернёт в браузер вместо иконки динамически сгенерированную 404 страницу. А в худшем — отработает полностью запуск главной страницы сайта. Получается, что не установив иконку на сайте (например, на одном популярном блоговом движке), разработчик вдвое увеличивает нагрузку на сервер от пользователей Google Chrome.



Вот, собственно и все. Надеюсь эта информация кому-то пригодится и сэкономит время.
Original source: habrahabr.ru (comments, light).

https://habrahabr.ru/post/309436/

Метки:   Комментарии (0)КомментироватьВ цитатник или сообщество
renrucksapan

Как восстановить таблицу

Четверг, 02 Сентября 2016 г. 02:04 (ссылка)

Как восстановить таблицу. Сегодня MySQL является одним из наиболее популярных решений среди малых и средних систем управления базами данных. Из достоинств MySQL можно выделить возможность работы с таблицами разных типов. Один из них - MyISAM. Такие таблицы отлично подходят для хранения часто запрашиваемых данных, но при сбоях в процессе модификации могут быть легко повреждены. Поэтому нередки случаи, когда требуется восстановить таблицу типа MyISAM. ЧИТАТЬ ДАЛЬШЕ>>>



Метки:   Комментарии (0)КомментироватьВ цитатник или сообщество
modistla

Как посчитать количество записей

Четверг, 01 Сентября 2016 г. 09:59 (ссылка)

Чаще всего выяснять количество записей при программировании веб-ресурсов приходится в таблицах базы данных, работающей под управлением СУБД MySQL. Для этой операции в языке SQL есть специальная функция. Запрос с е использованием можно модифицировать добавлением дополнительных условий фильтрации - это позволит получить не только общее количество записей, но и количество, удовлетворяющее дополнительным условиям. ЧИТАТЬ ДАЛЬШЕ>>>



Метки:   Комментарии (0)КомментироватьВ цитатник или сообщество
rss_rss_hh_new

Интеграция PostgreSQL с другими СУБД: делаем запросы в MySQL

Вторник, 30 Августа 2016 г. 15:38 (ссылка)

Нередко бывает так, что в большом проекте в силу тех или иных причин — зачастую исторических, хотя бывает по-всякому — его части могут использовать различные СУБД для хранения и поиска критически важных данных. В числе прочего, этому разнообразию способствует конкуренция и развитие технологий, но, так или иначе, взаимодействие между СУБД описывает стандарт SQL/MED 2003 (Management of External Data), который вводит определение Foreign Data Wrappers (FDW) и Datalink.



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

Вторая часть, Datalink, позволяет управлять удаленным SQL-сервером.



Эти две части были реализованы еще в PostgreSQL 9.1 и называются FDW и dblink соответственно. FDW в PostgreSQL сделан максимально гибко, что позволяет разрабатывать wrapper'ы для большого количества внешних источников. В настоящее время мне известны такие FDW, как PostgreSQL, Oracle, SQL Server, MySQL, Cassandra, Redis, RethinkDB, Ldap, а также FDW к файлам типа CSV, JSON, XML и т.п.



В нашей статье мы поговорим о том, как настроить подключение PostgreSQL к MySQL и эффективно выполнять получающиеся запросы.





Для начала собираем и устанавливаем mysql_fdw:



git clone https://github.com/EnterpriseDB/mysql_fdw.git
cd mysql_fdw
# во всех rhel-like дистрибутивов pg_config не попадает в PATH, он лежит в /usr/pgsql-9.5/bin:
PATH=$PATH:/usr/pgsql-9.5/bin USE_PGXS=1 make install


Устаналиваем extension на базу, чтобы загрузились необходимые библиотеки:



CREATE EXTENSION mysql_fdw ;


Создаем сервер:



CREATE SERVER mysql_server_data FOREIGN DATA WRAPPER mysql_fdw
OPTIONS (host '127.0.0.1', port '3306');


И mapping текущего пользователя в PostgreSQL в пользователя MySQL:



CREATE USER MAPPING FOR user SERVER mysql_server_data
OPTIONS (username 'data', password 'datapass');


После этого мы имеем возможность подключить таблицу MySQL в PostgreSQL:



CREATE FOREIGN TABLE
orders_2014 (
id int,
customer_id int,
order_date timestamp)
SERVER mysql_server_data
OPTIONS (dbname 'data', table_name 'orders');


Допустим, мы храним справочник customers в PostgreSQL:



CREATE TABLE customers (id serial, name text);


Попробуем выбрать 5 самых активных покупателей в январе 2014 года:



explain (analyze,verbose)
select
count(o2014.id),
c.name
from orders_2014 o2014
inner join customers c on c.id = o2014.customer_id
where
extract('month' from o2014.order_date) = 1 and
extract('year' from o2014.order_date) = 2014
group by 2 order by 1 desc limit 5;


план PostgreSQL
 Limit  (cost=1285.32..1285.34 rows=5 width=36) (actual time=0.276..0.276 rows=5 loops=1)
Output: (count(o2014.id)), c.name
-> Sort (cost=1285.32..1285.82 rows=200 width=36) (actual time=0.275..0.275 rows=5 loops=1)
Output: (count(o2014.id)), c.name
Sort Key: (count(o2014.id)) DESC
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=1280.00..1282.00 rows=200 width=36) (actual time=0.270..0.271 rows=5 loops=1)
Output: count(o2014.id), c.name
Group Key: c.name
-> Merge Join (cost=1148.00..1248.25 rows=6350 width=36) (actual time=0.255..0.264 rows=8 loops=1)
Output: o2014.id, c.name
Merge Cond: (o2014.customer_id = c.id)
-> Sort (cost=1059.83..1062.33 rows=1000 width=8) (actual time=0.240..0.241 rows=8 loops=1)
Output: o2014.id, o2014.customer_id
Sort Key: o2014.customer_id
Sort Method: quicksort Memory: 25kB
-> Foreign Scan on public.orders_2014 o2014 (cost=10.00..1010.00 rows=1000 width=8) (actual time=0.065..0.233 rows=8 loops=1)
Output: o2014.id, o2014.customer_id
Filter: ((date_part('month'::text, o2014.order_date) = '1'::double precision) AND (date_part('year'::text, o2014.order_date) = '2014'::double precision))
Rows Removed by Filter: 58
Local server startup cost: 10
Remote query: SELECT `id`, `customer_id`, `order_date` FROM `data`.`orders`
-> Sort (cost=88.17..91.35 rows=1270 width=36) (actual time=0.011..0.011 rows=9 loops=1)
Output: c.name, c.id
Sort Key: c.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.customers c (cost=0.00..22.70 rows=1270 width=36) (actual time=0.004..0.005 rows=12 loops=1)
Output: c.name, c.id


Как мы видим, запрос неэффективный, так как со стороны MySQL было получено содержимой всей таблицы: SELECT id, customer_id, order_date FROM data.orders. Сервер, в силу естественных ограничений драйвера MySQL, не в состоянии трансформировать запрос таким образом, чтобы для получения корректного результата этот запрос было бы возможно выполнить на стороне MySQL, и поэтому сначала получает таблицу целиком, а потом уже осуществляет фильтрацию. Однако при изменении запроса можно добиться того, чтобы фильтрация по дате осуществлялась на стороне MySQL:



explain (analyze,verbose)
select
count(o2014.id),
c.name
from orders_2014 o2014
inner join customers c on c.id = o2014.customer_id
where
o2014.order_date between ('2014-01-01') and ('2014-02-01'::timestamptz - '1 sec'::interval)
group by 2 order by 1 desc limit 5;


Сравнение order_date с ('2014-02-01'::timestamp - '1 sec'::interval) неправильно, так как timestamptz хранится с большей точность, чем секунда, но это значение выбрано не случайно, посмотрите:



план PostgreSQL
 Limit  (cost=1285.32..1285.34 rows=5 width=36) (actual time=0.130..0.130 rows=0 loops=1)
Output: (count(o2014.id)), c.name
-> Sort (cost=1285.32..1285.82 rows=200 width=36) (actual time=0.129..0.129 rows=0 loops=1)
Output: (count(o2014.id)), c.name
Sort Key: (count(o2014.id)) DESC
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=1280.00..1282.00 rows=200 width=36) (actual time=0.114..0.114 rows=0 loops=1)
Output: count(o2014.id), c.name
Group Key: c.name
-> Merge Join (cost=1148.00..1248.25 rows=6350 width=36) (actual time=0.111..0.111 rows=0 loops=1)
Output: o2014.id, c.name
Merge Cond: (o2014.customer_id = c.id)
-> Sort (cost=1059.83..1062.33 rows=1000 width=8) (actual time=0.110..0.110 rows=0 loops=1)
Output: o2014.id, o2014.customer_id
Sort Key: o2014.customer_id
Sort Method: quicksort Memory: 25kB
-> Foreign Scan on public.orders_2014 o2014 (cost=10.00..1010.00 rows=1000 width=8) (actual time=0.093..0.093 rows=0 loops=1)
Output: o2014.id, o2014.customer_id
Local server startup cost: 10
Remote query: SELECT `id`, `customer_id` FROM `data`.`orders` WHERE ((`order_date` >= '2014-01-01 00:00:00+00')) AND ((`order_date` <= ('2014-01-02 00:00:00+00' - '00:00:01')))
-> Sort (cost=88.17..91.35 rows=1270 width=36) (never executed)
Output: c.name, c.id
Sort Key: c.id
-> Seq Scan on public.customers c (cost=0.00..22.70 rows=1270 width=36) (never executed)
Output: c.name, c.id


Тут нас поджидает проблема, из-за которой стоит использовать mysql_fdw с большой осторожностью:



SELECT `id`, `customer_id` FROM `data`.`orders` WHERE ((`order_date` >= '2014-01-01 00:00:00+00')) AND ((`order_date` <= ('2014-01-02 00:00:00+00' - '00:00:01')))


Как мы видим, between, представляя из себя синтаксический сахар, был развернут в два условия, одно из которых не вычислено на стороне PostgreSQL: ('2014-02-01'::timestamp - '1 sec'::interval) и преобразовано в разницу двух строк (а не даты и интервала):



mysql> select '2014-01-02 00:00:00+00' - '00:00:01';
+---------------------------------------+
| '2014-01-02 00:00:00+00' - '00:00:01' |
+---------------------------------------+
| 2014 |
+---------------------------------------+
1 row in set, 2 warnings (0.00 sec)


В итоге запрос возвращает неправильный результат.



С подобной проблемой столкнулся один из наших клиентов. Проблема была исправлена в форке PostgresPro, https://github.com/postgrespro/mysql_fdw и создан pull-реквест в основной репозиторий EnterpriseDB. Устанавливаем исправленную версию:



git clone https://github.com/postgrespro/mysql_fdw.git mysql_fdw_pgpro
cd mysql_fdw_pgpro
PATH=$PATH:/usr/pgsql-9.5/bin USE_PGXS=1 make install


Теперь план запроса выглядит так:



план PostgreSQL
 Limit  (cost=1285.32..1285.34 rows=5 width=36) (actual time=0.219..0.219 rows=5 loops=1)
Output: (count(o2014.id)), c.name
-> Sort (cost=1285.32..1285.82 rows=200 width=36) (actual time=0.218..0.218 rows=5 loops=1)
Output: (count(o2014.id)), c.name
Sort Key: (count(o2014.id)) DESC
Sort Method: quicksort Memory: 25kB
-> HashAggregate (cost=1280.00..1282.00 rows=200 width=36) (actual time=0.199..0.201 rows=5 loops=1)
Output: count(o2014.id), c.name
Group Key: c.name
-> Merge Join (cost=1148.00..1248.25 rows=6350 width=36) (actual time=0.183..0.185 rows=8 loops=1)
Output: o2014.id, c.name
Merge Cond: (o2014.customer_id = c.id)
-> Sort (cost=1059.83..1062.33 rows=1000 width=8) (actual time=0.151..0.151 rows=8 loops=1)
Output: o2014.id, o2014.customer_id
Sort Key: o2014.customer_id
Sort Method: quicksort Memory: 25kB
-> Foreign Scan on public.orders_2014 o2014 (cost=10.00..1010.00 rows=1000 width=8) (actual time=0.116..0.120 rows=8 loops=1)
Output: o2014.id, o2014.customer_id
Local server startup cost: 10
Remote query: SELECT `id`, `customer_id` FROM `data`.`orders` WHERE ((`order_date` >= '2014-01-01 00:00:00+00')) AND ((`order_date` <= ('2014-01-02 00:00:00+00' - INTERVAL 1 SECOND)))
-> Sort (cost=88.17..91.35 rows=1270 width=36) (actual time=0.030..0.030 rows=9 loops=1)
Output: c.name, c.id
Sort Key: c.id
Sort Method: quicksort Memory: 25kB
-> Seq Scan on public.customers c (cost=0.00..22.70 rows=1270 width=36) (actual time=0.018..0.020 rows=12 loops=1)
Output: c.name, c.id


Запрос стал быстрее по сравнению с первым, так как с MySQL мы возвращаем значение более точечного запроса:



SELECT `id`, `customer_id` FROM `data`.`orders` WHERE ((`order_date` >= '2014-01-01 00:00:00+00')) AND ((`order_date` <= ('2014-01-02 00:00:00+00' - INTERVAL 1 SECOND)))


Операция фильтрации выполняется теперь на стороне MySQL. При определенных условиях будет использоваться индекс по order_date, если он создан.



Таким образом мы ускорили выполнение запроса. На простом примере мы почуствовали силу Open Source и мощь PostgreSQL по расширяемости в частности.



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



Подробнее про SQL-MED можно прочитать тут: http://cs.unibo.it/~montesi/CBD/Articoli/SQL-MED.pdf

Скачать исправленную версию mysql_fdw можно отсюда: https://github.com/PostgreSQLpro/mysql_fdw


Original source: habrahabr.ru (comments, light).

https://habrahabr.ru/post/308690/

Метки:   Комментарии (0)КомментироватьВ цитатник или сообщество
rss_rss_hh_new

[Перевод] Информатика за индексами в Постгресе

Понедельник, 29 Августа 2016 г. 15:06 (ссылка)

Друзья, PG Day'16 Russia успешно завершилась, мы перевели дух и уже думаем над тем, как сделать грядущие мероприятия еще более интересными и полезными для вас. Продолжаем публиковать интересные, на наш взгляд, материалы о Постгресе и общаться с вами в комментариях. Сегодня представляем перевод статьи Pat Shaughnessy о том, что из себя представляют индексы в PostgreSQL.



Все мы знаем, что индексы — одна из самых мощных и важных функций серверов реляционных баз данных. Как быстро найти значение? Создать индекс. Что нужно не забыть сделать при объединении двух таблиц? Создать индекс. Как ускорить SQL запрос, который начал медленно работать? Создать индекс.







Но что такое эти индексы? И как они ускоряют поиск по базе данных? Чтобы выяснить это, я решил прочитать исходный код сервера базы данных PostgreSQL на C и проследить за тем, как он ищет индекс для простого текстового значения. Я ожидал найти сложные алгоритмы и эффективные структуры данных. И я их нашёл. Сегодня я покажу вам, как выглядят индексы внутри Постгреса, и объясню, как они работают.



Что я не ожидал найти — что я впервые обнаружил, читая исходный код Постгреса — так это теорию информатики в основе того, что он делает. Чтение исходного кода Постгреса превратилось в возвращение в школу и изучение того предмета, на который у меня никогда не хватало времени в молодости. Комментарии на C внутри Постгреса объясняют не только, что он делает, но и почему.



Последовательные сканирования: бездумный поиск



Когда мы покинули команду Наутилуса, они были измучены и почти падали в обморок: алгоритм последовательного сканирования Постгреса бездумно петлял по всем записям в таблице пользователей! Вспомните мой предыдущий пост, в котором мы выполнили этот простой SQL запрос, чтобы найти Капитана Немо:





Постгрес обработал, проанализировал и спланировал запрос. Затем ExecSeqScan, функция C внутри Постгреса, которая выполняет узел плана Последовательное сканирование (SEQSCAN), быстро нашла Капитана Немо:







Но потом Постгрес необъяснимым образом продолжил выполнять цикл по всей таблице пользователей, сравнивая каждое имя с “Captain Nemo”, хотя мы уже нашли то, что искали!







Представьте, что в нашей таблице были бы миллионы записей, — процесс занял бы очень много времени. Конечно, мы могли бы избежать этого, удалив sort и переписав наш запрос так, чтобы принималось только первое найденное имя, но более глубокая проблема заключается в неэффективности способа, которым Постгрес ищет нашу целевую строку. Использовать последовательное сканирование для сравнения каждого значения в таблице пользователей с “Captain Nemo” — это медленно, неэффективно и зависит от случайного порядка, в котором имена появляются в таблице. Что мы делаем не так? Должен быть лучший способ!



Ответ прост: мы забыли создать индекс. Давайте сделаем это сейчас.



Создание индекса



Создать индекс очень просто – нужно всего лишь запустить эту команду:







Как разработчики Ruby, мы, конечно же, использовали бы вместо этого миграцию ActiveRecord add_index, которая выполнит ту же команду CREATE INDEX “под капотом”. Когда мы снова запустим наш запрос select, Постгрес, как обычно, создаст дерево плана, но на этот раз оно будет немного другим:







Обратите внимание, что внизу Постгрес теперь использует INDEXSCAN вместо SEQSCAN. В отличие от SEQSCAN, INDEXSCAN не будет проводить сканирование по всей таблице пользователей. Вместо этого оно использует тот индекс, который мы только что создали, чтобы найти и вернуть записи про Капитана Немо быстро и эффективно.



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




  • Что именно представляет из себя индекс в Постгресе?

  • Если бы я мог залезть в базу данных Постгреса и получше рассмотреть индекс, на что бы он был похож?

  • Каким образом индекс ускоряет поиск?



Давайте попробуем ответить на эти вопросы, изучая исходный код Постгреса.



Что же такое индекс в Постгресе?



Мы можем начать с изучения документации для команды CREATE INDEX.







Здесь вы видите все опции, которые мы можем использовать для создания индекса, например, UNIQUE и CONCURRENTLY. Обратите внимание, что есть такая опция, как метод USING. Он сообщает Постгресу, какой именно индекс нам нужен. Ниже на той же странице есть информация о method — аргументе к ключевому слову USING:







Оказывается, Постгрес имплементирует четыре разных типа индексов [прим. пер.: теперь уже больше, статья была написана раньше, чем появился BRIN и другие новые варианты индексов]. Вы можете использовать их для разных типов данных и в разных ситуациях. Поскольку мы никак не уточняли USING, наш индекс index_users_on_name является “btree” (или B-Дерево) индексом, типом по умолчанию.



Это наша первая подсказка: индекс Постгреса — это B-Дерево. Но что такое B-Дерево? Где мы можем его найти? Внутри Постгреса, конечно же! Давайте поищем в исходном коде Постгреса на C файлы, содержащие “btree:”







Ключевой результат выделен жирным шрифтом: “./backend/access/nbtree.” Внутри этой директории есть файл README. Давайте его прочитаем:





Удивительным образом этот README файл оказался подробным документом на 12 страниц! Исходный код Постгреса содержит не только полезные и интересные комментарии к коду C, но и документацию о теории и реализации сервера БД. Прочитать и понять код в проектах с открытым исходным кодом часто бывает трудно и страшно, но не в PostgreSQL. Разработчики, стоящие за Постгресом, приложили огромные усилия, чтобы мы с вами могли понять их работу.



Название документа README — “Btree Indexing” — подтверждает, что директория содержит код C, который реализует B-Tree индексы в Постгресе. Но первое предложение представляет ещё больший интерес: это отсылка к научной работе, которая объясняет, что такое B-Дерево, и как работают индексы в Постгресе: Efficient Locking for Concurrent Operations on B-Trees, за авторством Лемана (Lehman) и Яо (Yao).



Мы постараемся разобраться с тем, что такое B-Tree с помощью этой научной работы.



Как выглядит B-Tree индекс?



Работа Лемана и Яо объясняет инновационные изменения, которые они внесли в алгоритм B-Tree в 1981 году. Поговорим об этом чуть позже. Но они начинают с простого введения в структуру данных B-Tree, которая была изобретена на 9 лет раньше — в 1972 году. Одна из их диаграмм показывает пример простого B-Tree:







Термин B-Tree является сокращением от английского “balanced tree” — «сбалансированное дерево». Алгоритм делает поиск простым и быстрым. Например, если бы мы хотели найти значение 53 в этом примере, мы бы начали с корневого узла, содержащего значение 40:







Мы сравниваем наше искомое значение 53 со значением, которое мы нашли в узле дерева. 53 — это больше или меньше, чем 40? Поскольку 53 больше 40, мы следуем за указателем вниз и направо. Если бы мы искали 29, мы бы пошли вниз налево. Указатели справа ведут к большим значениям, а слева — к меньшим.



Следуя вниз за указателем к следующему дочернему узлу дерева, мы встречаем узел, содержащий 2 значения:







На этот раз мы сравниваем 53 сразу с 47 и 62 и обнаруживаем, что 47 < 53 < 62. Обратите внимание, что значения в узле дерева отсортированы, поэтому сделать это будет просто. Теперь мы следуем вниз по центральному указателю.



Здесь у нас ещё один узел дерева, уже с тремя значениями:







Просмотрев отсортированный список чисел, мы находим 51 < 53 < 56 и следуем вниз по второму из четырех указателей.



Наконец, мы приходим к узлу-листику дерева:







И вот оно, наше искомое значение 53!



Алгоритм B-Tree ускоряет поиск, потому что:


  • он сортирует значения (называемые ключами) внутри каждого узла;

  • он сбалансирован: ключи равномерно распределяются между узлами, минимизируя количество переходов от одного узла к другому. Каждый указатель ведет к дочернему узлу, который содержит примерно такое же количество ключей, как и каждый последующий дочерний узел.



Как выглядит индекс Постгреса?



Леман и Яо нарисовали эту диаграмму более 30 лет назад. Какое отношение она имеет к тому, как Постгрес работает сегодня? Поразительно, но индекс index_users_on_name, который мы создали ранее, очень похож на эту самую диаграмму из научной работы: мы создали в 2014 году индекс, который выглядит точно так же, как диаграмма из 1981-го!



Когда мы выполнили команду CREATE INDEX, Постгрес сохранил все имена из нашей таблицы пользователей в B-Tree. Они стали ключами дерева. Вот как выглядит узел внутри B-Tree индекса в Постгресе:







Каждая запись в индексе состоит из структуры на языке C под названием IndexTupleData, за которой следует битовая карта (bitmap) и значение. Постгрес использует битовую карту, чтобы записывать, принимают ли какие-либо атрибуты индекса в ключе значение NULL, для экономии места. Фактические значения находятся в индексе после битовой карты.



Давайте подробнее рассмотрим структуру IndexTupleData:







На рисунке выше видно, что каждая структура IndexTupleData содержит:


  • t_tid: это указатель либо на другой index tuple, либо на запись в базе данных. Заметьте, что это не указатель на физическую память на языке С. Вместо этого он содержит числа, которые Постгрес может использовать, чтобы найти искомое значение на страницах памяти.

  • t_info: тут содержится информация об элементах индекса, например, сколько в нём значений и равны ли они null.



Чтобы лучше это понять, давайте покажем несколько записей из нашего индекса index_users_on_name:







Я заменил value какими-то именами из моей таблицы пользователей. Верхний узел дерева содержит ключи “Dr. Edna Kunde” и “Julius Powlowski”, а нижний — “Julius Powlowski” и “Juston Quitzon”. Обратите внимание, что в отличие от диаграммы Лемана и Яо, Постгрес повторяет родительский ключ в каждом дочернем узле. Здесь “Julius Powlowski” является ключом в верхнем и дочернем узлах. Указатель t_tid в верхнем узле отсылает к тому же имени Julius в нижнем узле.



Чтобы узнать больше о том, как именно Постгрес сохраняет ключевые значения в узел B-Tree, обратитесь к заголовочному файлу itup.h:






image

IndexTupleData

view on postgresql.org










Поиск узла B-Tree, содержащего Капитана Немо



Давайте вернемся к нашему изначальному запросу SELECT:







Как именно Постгрес ищет в нашем индексе index_users_on_name значение «Captain Nemo»? Почему использовать индекс быстрее, чем последовательное сканирование, которое мы рассматривали в предыдущем посте? Чтобы это выяснить, давайте немного уменьшим масштаб и посмотрим на некоторые имена пользователей в нашем индексе:







Это корневой узел B-Tree index_users_on_name. Я положил дерево на бок, чтобы имена влезли. Вы можете увидеть 4 имени и одно значение NULL. Постгрес создал этот корневой узел, когда я создал index_users_on_name. Заметьте, что помимо первого значения NULL, которое обозначает начало индекса, остальные 4 значения более-менее равномерно распределены в алфавитном порядке.



Напомню, что B-Tree — это сбалансированное дерево. В этом примере в B-Tree есть 5 дочерних узлов:


  • имена, расположенные по алфавиту до Dr. Edna Kunde;

  • имена, расположенные между Dr. Edna Kunde и Julius Powlowski;

  • имена, расположенные между Julius Powlowski и Monte Nicolas;

  • и т.д.



Поскольку мы ищем имя Captain Nemo, Постгрес переходит по первой верхней стрелке направо. Это обусловлено тем, что Captain Nemo по алфавиту идёт перед Dr. Edna Kunde:







Как видите, справа Постгрес нашел узел B-Tree, в котором содержится Captain Nemo. Для своего теста я добавил в таблицу пользователей 1000 имен. Этот дочерний узел B-Tree включает около 200 имен (240, если быть точным). Так что алгоритм B-Tree существенно сузил нам поиск в Постгресе.



Чтобы узнать больше о конкретном алгоритме, используемом Постгресом для поиска целевого узла B-Tree среди всех его узлов, почитайте функцию _bt_search.






image

_bt_search

view on postgresql.org










Поиск Капитана Немо внутри конкретного узла B-Tree



Теперь, когда Постгрес сузил пространство для поиска до узла B-Tree, содержащего около 200 имен, ему всё ещё нужно найти среди них Капитана Немо. Как же он это сделает? Применит ли он последовательное сканирование к этому укороченному списку?







Нет. Для поиска ключевого значения внутри узла дерева Постгрес переключается на использование бинарного алгоритма поиска. Он начинает сравнивать ключ, расположенный на 50% позиции в узле дерева с “Captain Nemo”:







Поскольку Captain Nemo по алфавиту идёт после Breana Witting, Постгрес перескакивает к позиции 75% и проводит еще одно сравнение:







На этот раз Captain Nemo идёт до Curtis Wolf, так что Постгрес возвращается немного назад. Сделав ещё несколько итераций (Постгресу потребовалось 8 сравнений, чтобы найти Капитана Немо в моём примере), Постгрес наконец находит то, что мы искали.







Чтобы узнать больше о том, как Постгрес ищет значение в конкретном узле B-Tree, почитайте функцию _bt_binsrch:






image

_bt_binsrch

view on postgresql.org










Ещё многое предстоит узнать



Мне не хватит места в этом посте, чтобы рассказать обо всех захватывающих деталях, касающихся B-Tree, индексов баз данных или внутренностях Постгреса… возможно, мне стоит написать книгу Постгрес под микроскопом :) А пока вот вам несколько интересных фактов из теории, которые вы можете прочитать в Efficient Locking for Concurrent Operations on B-Trees или в другой научной работе, на которую она ссылается.




  • Вставки в B-Tree: самая прекрасная часть алгоритма B-Tree — добавление новых ключей в дерево. Они добавляются в отсортированном порядке в подходящий узел дерева, но что происходит, когда места для новых ключей не остаётся? В этом случае, Постгрес делит узел на два, вставляет в один из них новый ключ и добавляет ключ из разделенного узла в родительский узел вместе с указателем на новый дочерний узел. Конечно, есть вероятность, что родительский узел тоже придется разделить, чтобы добавить новый ключ, что приведет к сложной рекурсивной операции.

  • Удаление из B-Tree: обратная операция также интересна. Когда ключ удаляется из узла, Постгрес объединяет одноуровневые узлы, если это возможно, удаляя ключ из их родительского узла. Эта операция также может быть рекурсивной.

  • B-Link- Tree: Работа Лемана и Яо рассказывает об инновации, которую они исследовали, в отношении параллелизма и блокировки, когда несколько потоков используют одно и то же B-Дерево. Как вы помните, код и алгоритмы Постгреса должны быть мультипоточными, потому что много клиентов могут одновременно искать и модифицировать один и тот же индекс. Добавляя еще один указатель из каждого узла B-Tree в следующий дочерний узел – так называемая «правая стрелка» — один поток может искать в дереве, даже если второй поток делит узлы, не блокируя при этом весь индекс:







Не бойтесь исследовать невидимую часть айсберга



Профессор Ароннакс рисковал своей жизнью и карьерой, чтобы найти неуловимый Наутилус и присоединиться к Капитану Немо в длинной череде потрясающих подводных приключений. Нам стоит сделать так же: не бояться нырнуть под воду — вглубь инструментов, языков и технологий, которые вы используете каждый день. Вы можете многое знать о Постгресе, но знаете ли вы, как он работает изнутри? Загляните внутрь, и не успеете оглянуться, как сами окажетесь в подводном приключении.



Изучение на работе информатики, стоящей за нашими приложениями, — это не просто развлечение, а важная составляющая процесса развития разработчика. Инструменты для разработки программного обеспечения совершенствуются с каждым годом, написание веб-сайтов и мобильных приложений упрощается, но мы не должны упускать из виду фундаментальную информатику, от которой мы зависим. Мы все стоим на плечах гигантов – таких людей, как Леман и Яо, а также разработчиков открытого исходного кода, использовавших их теории для создания Постгреса. Не воспринимайте инструменты, которые вы используете каждый день, как должное, — изучайте их устройство. Вы станете мудрее как разработчик и найдёте идеи и знания, о которых даже не подозревали.
Original source: habrahabr.ru.

https://habrahabr.ru/post/308752/?utm_source=habrahabr&utm_medium=rss&utm_campaign=best

Метки:   Комментарии (0)КомментироватьВ цитатник или сообщество

Следующие 30  »

<mysql - Самое интересное в блогах

Страницы: [1] 2 3 ..
.. 10

LiveInternet.Ru Ссылки: на главную|почта|знакомства|одноклассники|фото|открытки|тесты|чат
О проекте: помощь|контакты|разместить рекламу|версия для pda