СУБД. Лекция 9

Организационные моменты

• Не забывайте отмечаться на лекциях!
• Оставляйте обратную связь по завершению лекции!

Рейтинг СУБД

https://db-engines.com/en/ranking

Релизный цикл MySQL

Сравнение с MySQL

• Архитектура MySQL:
  • Модульность и подключаемые хранилища;
  • Обзор основных хранилищ (MyISAM, InnoDB, Memory);
  • Особенности получения результата SELECT-а;
  • Особенности репликации и лога запросов.
• Особенности планировщика запросов;
• Функционал, отсутствующий в PostgreSQL:
  • Memory-таблицы;
  • Кластерные индексы;
  • COLLATION.
• Базовые параметры при конфигурировании MySQL.

Архитектура MySQL

Архитектура MySQL

Архитектура MySQL

В MySQL различные хранилища объектов реализованы в виде подключаемых модулей.

Это порождает конфликт интересов:

• Подключаемые хранилища требуют четкого и простого API для взаимодействия с ядром MySQL. Это требует, чтобы ядро знало о хранилище как можно меньше;
• Для эффективного выполнения запросов нужно знать о хранении данных как можно больше.

Данная особенность архитектуры MySQL проходит красной нитью через весь функционал.

Подсистемы хранения: MyISAM

• Блокировка и конкуренция
• Автоматическое исправление
• Ручное исправление
  CHECK TABLE mytable и REPAIR TABLE mytable
• Индексы кэшируются отдельно
• Полнотекстовый индекс
• Отложенная запись ключей
  DELAY_KEY_WRITE

Подсистемы хранения: InnoDB

• Транзакции
• Блокировки на уровне строк
• Кластерные индексы
• Внешние ключи
• Возможны Deadlocks
• Начиная с версии MySQL 5.6.4, доступен полнотекстовый поиск

Подсистемы хранения: Memory

• Данные хранятся в памяти и теряются при перезапуске
• Поддерживают индексы типа HASH
• Не поддерживают TEXT, BLOB, VARCHAR => CHAR
• Используется для
  промежуточных таблиц

Подсистемы хранения: критерии выбора

• Транзакции
• Конкурентный доступ
• Резервное копирование
• Восстановление после сбоя
• Специальные возможности

Подсистемы хранения: надежность

Подсистемы хранения: практические примеры

• Протоколирование
  • MyISAM, Archive
• Чтение, или в основном чтение
  • MyISAM
• Обработка заказов
  • InnoDB
• Распространение на дисках
  • MyISAM, Compressed MyISAM

Подсистемы хранения: за бортом

• Оптимизатор;
• Репликация;
• Схема базы;
• Триггеры;
• SEQUENCE;
• Синтаксический парсер.

Не транзакционный DDL

Метаданные дублируются как в ядре MySQL, так и в его хранилище. Возникает проблема с транзакционным обновлении метаданных.

Из-за этого DDL-запросы в MySQL не транзакционны.

То есть, любой DDL-запрос автоматически подтверждает старую транзакцию и начинает новую. Этот факт может сильно усложнять написание скриптов миграции.

Дорогой ALTER TABLE

По той же причине, в MySQL почти любой ALTER TABLE влечет за собой полное пересоздание таблицы (по крайней мере в InnoDB). В том числе это касается таких операций как удаление CONSTRAINT-ов, удаление столбцов и создание NULL-столбцов.

В большинстве СУБД подобные операции требуют только изменения метаданных и выполняются мгновенно.

С MySQ 5.7.4 появился флаг ALGORITHM=INPLACE для модификации таблицы без копирования её содержимого.

Особенности выборки данных

По умолчанию резьтаты полностью извлекаются и сохраняются в памяти. В большинстве случаев это наиболее эффективный способ работы и, благодаря архитектуре MySQL, это проще в реализации. Если вы работаете с наборами результатов, которые имеют большое количество строк и не могут выделить пространство в heap вашей JVM, Можно указать драйверу передавать результаты обратно по одной строке за раз.

Для включения этого функционала нужно создать экземпляр Statement следующим образом:

stmt = conn.createStatement(
    java.sql.ResultSet.TYPE_FORWARD_ONLY,
    java.sql.ResultSet.CONCUR_READ_ONLY);
stmt.setFetchSize(Integer.MIN_VALUE);

Архитектура MySQL

В MySQL хранилище и лог транзакций существуют за пределами "движка" СУБД.

Как следствие, в MySQL вместо журнала транзакций для репликации используется отдельный журнал.

Репликация в MySQL

Режимы репликации:

STATEMENT

Сохраняются непосредственно sql запросы, которые несут информацию об изменениях записей.

ROW

Сохраняется информация о конкретных изменениях определенных записей.

В случае DDL-выражений сохраняются сами запросы.

MIXED

Промежуточный формат, который старается использовать statement, когда возможно, а когда нет — row.

Репликация в MySQL

• Репликация провоцирует больше записи на диск;
• Реплика воспроизводит транзакции по очереди (под нагрузкой реплика может сильно отставать);
• Двухфазная фиксации транзакции (между журналом транзакций и журналом репликации);
• Репликация хрупкая, так как некоторые запросы гарантировано ведут к порче данных.

ALTER TABLE foo
  ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  ADD PRIMARY KEY (id);
    

Особенности планировщика запросов

Особенности планировщика запросов

• Планировщик очень мало знает о том, как лежат данные;
• Соединение только через Nested Loop;
• Триггеры игнорируют каскадное изменение данных.

Гарантированно плохие запросы

SELECT title, description FROM foo
UNION ALL
SELECT title, description FROM bar
ORDER BY title;

SELECT * FROM (
    SELECT title, MIN(description) as desc
    FROM film
    GROUP BY title
) f
WHERE f.title LIKE 'al%';

SELECT ... IN ...

SELECT * FROM sakila.film
WHERE film_id
IN (1,23,25,106,277);

SELECT * FROM sakila.film
WHERE film_id = 1
   OR film_id = 23
   OR film_id = 25
   OR film_id = 106
   OR film_id = 277;
    

DELETE

Стандартный синтаксис

DELETE FROM t1
WHERE
    t1.id IN (SELECT t2.id FROM t2);

Специфичный синтаксис

DELETE t1
FROM
    t1 JOIN t2 ON (t1.id = t2.id);

Что еще плохо?

WARNING vs ERROR

CREATE TABLE data (
  id  INT NOT NULL,
  foo INT NULL,
  bar NUMERIC(4, 2)
);
INSERT INTO data VALUES (1, NULL, 1234.5678);
Query OK, 1 row affected, 1 warning (0,00 sec)

SELECT * FROM data;
+----+------+-------+
| id | foo  | bar   |
+----+------+-------+
|  1 | NULL | 99.99 |
+----+------+-------+
1 rows in set (0,00 sec)

WARNING vs ERROR

sql_mode = 'STRICT_TRANS_TABLES'

Данная опция включена по-умолчанию с MySQL 5.7.5.

Первый General Availability релиз с данным изменением 5.7.9 вышел 21 октября 2015 года.

WARNING vs ERROR

ALTER TABLE data
  MODIFY COLUMN foo INT NOT NULL;
Query OK, 1 row affected, 1 warning (0,00 sec)

SELECT * FROM data;
+----+-----+-------+
| id | foo | bar   |
+----+-----+-------+
|  1 |   0 | 99.99 |
+----+-----+-------+
1 rows in set (0,00 sec)

AUTO_INCREMENT

• Нет возможность получить значение AUTO_INCREMENT до вставки записи;
• https://dev.mysql.com/doc/refman/8.0/en/innodb-auto-increment-handling.html#innodb-auto-increment-initialization
• До версии 5.7 включительно, счетчик хранился в оперативной памяти, а не на диске. При рестарте сервера счетчик сбрасывался. А при следующей вставке InnoDB инициализировался инициализировал счетчик максимальным значением AUTO_INCREMENT колонки в таблице: SELECT MAX(ai_col) FROM table_name FOR UPDATE;
• С версии 8.0 AUTO_INCREMENT пишется в redo log.

TIMESTAMP vs DATETIME

TIMESTAMP:

• Внутри хранит кол-во секунд с момента EPOC;
• На клиенте учитывает временную зону;
• Имеет крайне забавное значение, которое не понятно как обрабатывать: 0000-00-00 00:00:00;
• Диапазон значений: от '1970-01-01 00:00:01' UTC до '2038-01-19 03:14:07' UTC;
• Внезапно, по-умолчанию поле этого типа изменяет своё значение при каждом обновлении записи на текущее время.

DATETIME:

• Внутри хранит дату и время;
• Временная зона не влияет на значение поля;
• Диапазон значений: от 0000-01-01 00:00:00 до 9999-12-31 23:59:59.

TIMESTAMP

CREATE TABLE k (
  A INT NULL,
  B NUMERIC (4, 2),
  C TIMESTAMP
);


INSERT INTO k (A, B) VALUES (42, 22.7);


SELECT * FROM k;
+----+-------+---------------------+
| A  | B     |                     |
+----+-------+---------------------+
| 42 | 22.70 | 2017-04-27 20:27:17 |
+----+-------+---------------------+
1 rows in set (0,00 sec)

TIMESTAMP

SELECT * FROM k;
+----+-------+---------------------+
| A  | B     |                     |
+----+-------+---------------------+
| 42 | 22.70 | 2017-04-27 20:27:17 |
+----+-------+---------------------+
1 rows in set (0,00 sec)


UPDATE k SET B = B + 1;


SELECT * FROM k;
+----+-------+---------------------+
| A  | B     |                     |
+----+-------+---------------------+
| 42 | 23.70 | 2017-04-27 20:27:47 |
+----+-------+---------------------+
1 rows in set (0,00 sec)

TIMESTAMP

CREATE TABLE k (
  A INT NULL,
  B NUMERIC (4, 2),
  C TIMESTAMP,
  D TIMESTAMP
);
Error Code: 1067. Invalid default value for 'D'

TIMESTAMP

CREATE TABLE k (
  A INT NULL,
  B NUMERIC (4, 2),
  C TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
  D TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
0 rows updated (0,00 sec)

GROUP BY

SELECT A, B, SUM(C)
FROM K
GROUP BY A;

UPDATE

В MySQL операция UPDATE не соответствует стандарту SQL92.

13.9 <update statement: positioned>
6) The <value expression>s are effectively evaluated before updating the object row. If a <value expression> contains a reference to a column of T, then the reference is to the value of that column in the object row before any value of the object row is updated.

Иллюстрируется запросом:

UPDATE foo SET
    a = b,
    b = a
WHERE id = 42;

lower_case_table_names

В MySQL есть параметр lower_case_table_names, который приводит имена всех таблиц в нижний регистр и по-умолчанию включён под Windows.

В результате, если взять базу, у которой имена таблиц не в нижнем регистре, и развернуть её под Windows, то перенести эту базу обратно под Linux уже не получится.

REPEATABLE READ (PostgreSQL)

# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

# SELECT balance FROM accounts WHERE name = 'Alice';
balance
---------
400

# UPDATE accounts SET balance = balance + 100
$ WHERE name = 'Alice';
UPDATE 1

# COMMIT;

# SELECT balance FROM accounts WHERE name = 'Alice';
balance
---------
500
            

# BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;

# SELECT balance FROM accounts WHERE name = 'Alice';
balance
---------
400







# UPDATE accounts SET balance = balance + 100
$ WHERE name = 'Alice';
ERROR:  could not serialize access
        due to concurrent update
            

REPEATABLE READ (MySQL)

# SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
# BEGIN;
# SELECT balance FROM accounts WHERE name = 'Alice';
balance
---------
400

# UPDATE accounts SET balance = balance + 100
$ WHERE name = 'Alice';
Query OK, 1 row affected (0.00 sec)

# COMMIT;

# SELECT balance FROM accounts WHERE name = 'Alice';
balance
---------
500
            

# SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
# BEGIN;
# SELECT balance FROM accounts WHERE name = 'Alice';
balance
---------
400







# UPDATE accounts SET balance = balance + 100
$ WHERE name = 'Alice';
Query OK, 1 row affected (0.00 sec)

# COMMIT;

# SELECT balance  FROM accounts WHERE name = 'Alice';
balance
---------
600
            

REPEATABLE READ (MySQL)

# SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
# BEGIN;
# SELECT balance FROM accounts WHERE name = 'Alice';
balance
---------
400

# UPDATE accounts SET balance = balance + 100
$ WHERE name = 'Alice';
Query OK, 1 row affected (0.00 sec)









# COMMIT;

# SELECT balance FROM accounts WHERE name = 'Alice';
balance
---------
500
            

# SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
# BEGIN;









# SELECT balance FROM accounts WHERE name = 'Alice';
balance
---------
400

# UPDATE accounts SET balance = balance + 100
$ WHERE name = 'Alice';


Query OK, 1 row affected (0.00 sec)

# COMMIT;

# SELECT balance FROM accounts WHERE name = 'Alice';
balance
---------
600
            

Что хорошо?

Секционирование

CREATE TABLE orders_range (
    customer_surname VARCHAR(30),
    store_id INT,
    salesperson_id INT,
    order_date DATE,
    note VARCHAR(500)
) ENGINE = InnoDB
PARTITION BY RANGE (YEAR (order_date)) (
    PARTITION p_old VALUES LESS THAN(2008),
    PARTITION p_2008 VALUES LESS THAN(2009),
    PARTITION p_2009 VALUES LESS THAN(MAXVALUE)
);

Секционирование

RANGE

По диапазону значений

PARTITION BY RANGE (store_id) (
    PARTITION p0 VALUES LESS THAN (10),
    PARTITION p1 VALUES LESS THAN (20),
    PARTITION p3 VALUES LESS THAN (30)
);

Секционирование

LIST

По точному списку значений

PARTITION BY LIST(store_id) (
    PARTITION pNorth VALUES IN (3,5,6,9,17),
    PARTITION pEast VALUES IN (1,2,10,11,19,20)
);

Секционирование

HASH

По хэшу от какой-либо функции

PARTITION BY HASH(YEAR(hired))
PARTITIONS 10;

KEY

Почти то же самое что и HASH, но по ключу

PARTITION BY KEY(s1)
PARTITIONS 10;

Секционирование

• Все секции должны управляться одной и той же подсистемой хранения. Например, нельзя сжать только часть секций, хотя для составляющих объединенной таблицы это допустимо.
• Любой уникальный индекс над секционированной таблицей должен содержать столбцы, на которые ссылается функция секционирования.
• Хотя сервер MySQL может обойтись без доступа к каждой секции при обработке запроса к секционированной таблице, он тем менее ставит блокировки на все секции.
• Существует ряд ограничений на функции и выражения, которые можно использовать в механизме секционирования.
• Некоторые подсистемы хранения вообще не поддерживают секционирование.
• Внешние ключи не работают.

COLLATIONS

MySQL для регистронезависимого сравнения использует COLLATIONS (например: utf8_general_ci):
http://collation-charts.org/mysql60/mysql604.utf8_general_ci.european.html

Проблемы PostgreSQL:

• Нет регистронезависимого сравнения;
• Проблемы с не-iconv COLLATION:
• Разная сортировка на разных ОС;
• Обновление libc может поломать индексы.

libslave

Библиотека на C++, которая может быть использована в вашем приложении для получения обновлений из MySQL. Libslave не связана на уровне кодов с MySQL-сервером; она собирается и линкуется только с клиентом — libmysqlclient.

Базовые параметры

Кэш MyISAM

key_buffer_size

25 – 50 % от общего объема памяти, зарезервированного для MyISAM кэшей.

key_buffer_1.key_buffer_size = 1G
key_buffer_2.key_buffer_size = 1G

CACHE INDEX t1, t2 IN key_buffer_1;
LOAD INDEX INTO CACHE t1, t2;

Эту SQL-команду можно поместить в файл, выполняемый MySQL на этапе запуска. Имя файла задается с помощью параметра init_file.

В нем может быть несколько SQL-команд, каждая в отдельной строке.

Кэш InnoDB

innodb_buffer_pool_size

В отличие от кэша ключей MyISAM, в пуле буферов InnoDB кэшируются не только индексы, там также хранятся сами данные, буфер вставок, блокировки и другие внутренние структуры. В InnoDB пул буферов используется также для реализации отложенных операций записи и позволяет объединить несколько таких процедур, чтобы затем выполнить их последовательно.
Рекомендуется выставить до 80% физической памяти.

innodb_max_dirty_pages_pct

Говорит InnoDB о допустимом количестве «грязных» (модифицированных) страниц в пуле буферов.

Кэш InnoDB

В MySQL есть возможность сохранить состояние кэша, чтобы избежать проблемы с прогревом базы.

SET GLOBAL innodb_buffer_pool_dump_at_shutdown = ON;
SET GLOBAL innodb_buffer_pool_dump_now = ON;
SET GLOBAL innodb_buffer_pool_load_now = ON;

SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';
SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

Ввод / вывод в MyISAM

delay_key_write

Определяет, когда будут сбрасываться данные на диск.

OFF

MyISAM сбрасывает измененные блоки из буфера ключей после каждой записи, если только таблица не блокирована командой LOCK TABLES.

ON

Включен режим отложенной записи ключей, но только для таблиц, созданных с параметром DELAY_KEY_WRITE.

ALL

Для всех таблиц типа MyISAM используется отложенная запись ключей.

ALTER TABLE sometable DELAY_KEY_WRITE = 1;

Ввод / вывод в MyISAM

• Если сервер аварийно завершает работу, а блоки не были сброшены на диск, то индекс будет испорчен.
• Если было отложено много операций записи, то MySQL потратит больше времени на закрытие таблицы, поскольку вынуждена ждать завершения записи буферов на диск.
• По тем же причинам команда FLUSH TABLES может занимать много времени.
• Не сброшенные «грязные» блоки в буфере ключей могут не оставить места для новых блоков, считываемых с диска. В таком случае выполнение запроса будет приостановлено на время, пока MyISAM не освободит достаточно места в буфере ключей.

Ввод / вывод в InnoDB

Ввод / вывод в InnoDB

innodb_log_file_size

Общий максимальный размер файла логов транзакций.

innodb_log_files_in_group

Количество файлов в группе.

innodb_log_buffer_size

Размер буфера лога транзакций.

Ввод / вывод в InnoDB

mysql> pager grep sequence
PAGER SET TO 'grep sequence'

mysql> SHOW engine innodb STATUS\G SELECT sleep(60); SHOW engine innodb STATUS\G
Log sequence number 84 3836410803
 1 row IN SET (0.06 sec)
 1 row IN SET (1 min 0.00 sec)
Log sequence number 84 3838334638
 1 row IN SET (0.05 sec)

mysql> SELECT (3838334638 - 3836410803) / 1024 / 1024 AS MB_per_min;

+------------+
| MB_per_min |
+------------+
| 1.83471203 |
+------------+

Размер лога выбираем примерно на 10-60 минут работы сервера.

Ввод / вывод в InnoDB

innodb_flush_log_at_trx_commit

0

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

1

Писать буфер в файл журнала и сбрасывать его на устройство постоянного хранения при каждой фиксации транзакции.

2

Писать буфер в файл журнала при каждой фиксации, но не сбрасывать его на устройство постоянного хранения.