Мой SQL

Практичное введение в MySQL

часть 2

Автор - Геогрий Бажуков / @_bugov

Временные таблицы

  • уникальность именования
  • автоматическая чистка
          
  mysql> CREATE TEMPORARY TABLE pets_with_pills
      -> SELECT c.*, COUNT(pi.id) as count FROM (
      ->    (SELECT id, name, birthday, 'cat' as kind_name FROM cats)
      ->    UNION
      ->    (SELECT id, name, birthday, 'dog' as kind_name FROM dogs)
      -> ) as c
      ->   JOIN kind k ON k.name = c.kind_name
      ->   JOIN passport p ON c.id = p.pet_id AND p.kind_id = k.id
      ->   JOIN passport_to_pill pp ON pp.passport_id = p.id
      ->   JOIN pill as pi ON pi.id = pp.pill_id
      -> GROUP BY c.id, k.id
      -> HAVING COUNT(pi.id) > 1;
  Query OK, 3 rows affected (0.06 sec)
  Records: 3  Duplicates: 0  Warnings: 0
          
                    
  mysql> SELECT * FROM pets_with_pills LIMIT 2;
  +----+--------------+------------+-----------+-------+
  | id | name         | birthday   | kind_name | count |
  +----+--------------+------------+-----------+-------+
  |  1 | Перл         | 0000-00-00 | cat       |     2 |
  |  2 | Тайлер       | 1970-01-01 | dog       |     2 |
  +----+--------------+------------+-----------+-------+
  2 rows in set (0.00 sec)
          

Удаление временной таблицы

  • DROP TABLE
  • при закрытии соединения
          
  mysql> exit
  Bye
  b@d:~$ mysql -uuserus -ps3cr3t testum
  mysql> SELECT * FROM pets_with_pills LIMIT 2;
  ERROR 1146 (42S02): Table 'testum.pets_with_pills' doesn't exist
        

Вспомогательные таблицы

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

Представления

Не данные, а запрос. Это динамическое отображение таблицы. Поэтому, при изменении таблицы, будут изменяться данные из представлений.

Примеры представлений

  • Если бы у нас была таблица pets, мы могли получить представления cats, dogs
  • Если бы у нас была только таблица passport, мы могли получить представление pets (STI)
  • Для middle, first, last name могли бы сделать универсальный ФИО
  • Пересечение, группировку, слияние.
                    
  mysql> CREATE VIEW pets AS
      -> (SELECT name, birthday FROM cats)
      ->  UNION
      -> (SELECT name, birthday FROM dogs)
      -> ORDER BY birthday, name;
  Query OK, 0 rows affected (0.08 sec)
          
                    
  mysql> SELECT * FROM pets LIMIT 10;
  +--------------------+------------+
  | name               | birthday   |
  +--------------------+------------+
  | Перл               | 0000-00-00 |
  | Шрёдингер          | 1900-04-01 |
  | Тайлер             | 1970-01-01 |
  | Рекс               | 2001-02-12 |
  | Бобик              | 2013-02-01 |
  | Барсик             | 2014-01-01 |
  | Лютик              | 2201-12-12 |
  +--------------------+------------+
  7 rows in set (0.00 sec)
          

Ограничения представлений

  • нельзя повесить триггер на представление,
  • нельзя сделать представление на основе временных таблиц; нельзя сделать временное представление;
  • в определении представления нельзя использовать подзапрос в части FROM,
  • в определении представления нельзя использовать системные и пользовательские переменные;

Ограничения представлений 2

  • внутри хранимых процедур нельзя в определении представления использовать локальные переменные или параметры процедуры,
  • в определении представления нельзя использовать параметры подготовленных выражений (PREPARE),
  • таблицы и представления, присутствующие в определении представления должны существовать.
  • только представления, удовлетворяющие ряду требований, допускают запросы типа UPDATE, DELETE и INSERT.
        
  mysql> CREATE ALGORITHM=TEMPTABLE VIEW pill_count AS
      ->      SELECT c.*, COUNT(pi.id) as count FROM (
      ->         (SELECT id, name, birthday, 'cat' as kind_name FROM cats)
      ->         UNION
      ->         (SELECT id, name, birthday, 'dog' as kind_name FROM dogs)
      ->      ) as c
      ->        JOIN kind k ON k.name = c.kind_name
      ->        JOIN passport p ON c.id = p.pet_id AND p.kind_id = k.id
      ->        JOIN passport_to_pill pp ON pp.passport_id = p.id
      ->        JOIN pill as pi ON pi.id = pp.pill_id
      ->      GROUP BY c.id, k.id
      ->      HAVING COUNT(pi.id) > 1;
  ERROR 1349 (HY000): View's SELECT contains a subquery in the FROM clause
          

В общем виде

                    
  CREATE [OR REPLACE]
  [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
  VIEW view_name [(column_list)]
  AS select_statement
  [WITH [CASCADED | LOCAL] CHECK OPTION]
          

В общем виде

  • OR REPLACE — старое будет удалено, а новое создано. Имена таблиц и представлений в рамках одной базы данных должны быть уникальны.
  • ALGORITM — определяет алгоритм, используемый при обращении к представлению.
  • column_list — задает имена полей представления.
  • WITH CHECK OPTION — при использовании данной конструкции все добавляемые или изменяемые строки будут проверяться на соответствие определению представления.

ALGORITM

  • MERGE — MySQL при обращении к представлению добавляет в использующийся оператор соответствующие части из определения представления и выполняет получившийся оператор.
  • TEMPTABLE — MySQL заносит содержимое представления во временную таблицу, над которой затем выполняется оператор обращенный к представлению.
  • UNDEFINED — MySQL сам выбирает какой алгоритм использовать при обращении к представлению.

MERGE

Использование алгоритма MERGE требует соответствия 1 к 1 между строками таблицы и основанного на ней представления.

        
  mysql> CREATE ALGORITHM=MERGE VIEW live_pets AS SELECT pets.* FROM pets
      -> WHERE pets.birthday < NOW();
  Query OK, 0 rows affected (0.08 sec)

  mysql> SELECT * FROM live_pets;
  +--------------------+------------+
  | name               | birthday   |
  +--------------------+------------+
  | Перл               | 0000-00-00 |
  | Шрёдингер          | 1900-04-01 |
  | Тайлер             | 1970-01-01 |
  | Рекс               | 2001-02-12 |
  | Бобик              | 2013-02-01 |
  | Барсик             | 2014-01-01 |
  +--------------------+------------+
  6 rows in set (0.00 sec)
        

MERGE

В случае MERGE алгоритма MySQL включает определение представления в использующийся оператор SELECT: заменяет имя представления на имя таблицы, заменяет список полей на определения полей представления и добавляет условие в части WHERE с помощью оператора AND.

TEMPTABLE

Содержимое представления лежит во временной таблице. С MySQL 5.6/MariaDB 5.3 используются клоны индексов.

TEMPTABLE


  mysql> CREATE ALGORITHM=TEMPTABLE VIEW pill_count AS
      ->   SELECT c.name as pet , COUNT(pi.id) as count FROM cats c
      ->      JOIN passport p ON p.pet_id = c.id
      ->      JOIN passport_to_pill pp ON pp.passport_id = p.id
      ->      JOIN pill as pi ON pi.id = pp.pill_id
      ->   GROUP BY c.name;
  Query OK, 0 rows affected (0.07 sec)

TEMPTABLE select


  mysql> select MAX(count) FROM pill_count;
  +------------+
  | MAX(count) |
  +------------+
  |          3 |
  +------------+
  1 row in set (0.00 sec)

TEMPTABLE vs MERGE


  mysql> CREATE OR REPLACE ALGORITHM=MERGE VIEW pill_count AS
      ->    SELECT c.name as pet , MAX(COUNT(pi.id)) as count FROM cats c
      ->       JOIN passport p ON p.pet_id = c.id
      ->       JOIN passport_to_pill pp ON pp.passport_id = p.id
      ->       JOIN pill as pi ON pi.id = pp.pill_id
      ->    GROUP BY c.name;
  ERROR 1111 (HY000): Invalid use of group function

Представления WITH

Все добавляемые или изменяемые строки будут проверяться на соответствие определению представления.


   CREATE TABLE catz (name TEXT, age NUMERIC);
   insert into catz(name, age) values('Perl', -1);
   insert into catz(name, age) values('Erlang', -1);
   insert into catz(name, age) values('PHP', 1);
   CREATE VIEW live_catz AS SELECT * FROM catz WHERE age > 0 WITH CHECK OPTION;
          

  mysql> INSERT INTO live_catz VALUES('Ruby', 1);
  Query OK, 1 row affected (0.10 sec)

  mysql> INSERT INTO live_catz VALUES('Hack', -1);
  ERROR 1369 (HY000): CHECK OPTION failed 'testum.live_catz'
          

Процедуры

Способ сохранять программы на sql.

Возраст из даты


  DELIMITER $$
  DROP PROCEDURE IF EXISTS get_age $$
  CREATE PROCEDURE get_age(IN my_date DATE)
  BEGIN
    SELECT TIMESTAMPDIFF(YEAR, my_date, curdate());
  END $$
          
          
  mysql> CALL get_age('1923-01-01'); $$
  +-----------------------------------------+
  | TIMESTAMPDIFF(YEAR, my_date, curdate()) |
  +-----------------------------------------+
  |                                      91 |
  +-----------------------------------------+
  1 row in set (0.00 sec)
          

Вернём значение

          
  mysql> DELIMITER $$
  mysql> 
  mysql> DROP PROCEDURE IF EXISTS my_sqrt$$
  Query OK, 0 rows affected, 1 warning (0.00 sec)

  mysql> CREATE PROCEDURE my_sqrt(input_number INT, OUT out_number FLOAT)
      -> BEGIN
      ->     SET out_number=SQRT(input_number);
      -> END$$
  Query OK, 0 rows affected (0.00 sec)

  mysql> 
  mysql> DELIMITER ;
  mysql> CALL my_sqrt(4, @out_value);
  Query OK, 0 rows affected (0.00 sec)

  mysql> SELECT @out_value;
  +------------+
  | @out_value |
  +------------+
  |          2 |
  +------------+
  1 row in set (0.00 sec)
          

if / else

          
  DELIMITER $$
    
  CREATE PROCEDURE `proc_IF` (IN param1 INT)  
  BEGIN  
      DECLARE variable1 INT;  
      SET variable1 = param1 + 1;  
    
      IF variable1 = 0 THEN  
          SELECT variable1;  
      END IF;  
    
      IF param1 = 0 THEN  
          SELECT 'Parameter value = 0';  
      ELSE  
          SELECT 'Parameter value <> 0';  
      END IF;  
  END $$  
  DELIMITER ;
          

while

          
  DELIMITER $$
    
  CREATE PROCEDURE `proc_WHILE` (IN param1 INT)  
  BEGIN  
      DECLARE variable1, variable2 INT;  
      SET variable1 = 0;  
    
      WHILE variable1 < param1 DO  
          INSERT INTO table1 VALUES (param1); 
          SELECT COUNT(*) INTO variable2 FROM table1;  
          SET variable1 = variable1 + 1;  
      END WHILE;  
  END $$

  DELIMITER ;
          

Курсоры

          
  DELIMITER $$
    
  CREATE PROCEDURE `proc_CURSOR` (OUT param1 INT)  
  BEGIN  
      DECLARE a, b, c INT;  
      -- Объявление курсора и его заполнение
      DECLARE cur1 CURSOR FOR SELECT col1 FROM table1;
      -- Что делать, когда больше нет записей
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;  
      OPEN cur1;  
    
      SET b = 0;  
      SET c = 0;  
    
      WHILE b = 0 DO  
          FETCH cur1 INTO a;  
          IF b = 0 THEN  
              SET c = c + a;  
          END IF;  
      END WHILE;  
    
      CLOSE cur1;  
      SET param1 = c;
  END $$

  DELIMITER ;
          

Триггеры

Хранимая процедура, которая не вызывается непосредственно, а исполняется при наступлении определенного события (вставка, удаление, обновление строки)

Определение триггеров

          
  CREATE TRIGGER trigger_name trigger_time trigger_event
  ON tbl_name FOR EACH ROW trigger_stmt*
          

Пример триггера

          
  CREATE TABLE `log` (
    `id` INT( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `msg` VARCHAR( 255 ) NOT NULL,
    `row_id` INT( 11 ) NOT NULL,
    PRIMARY KEY (`id`)
  )
          

Пример триггера 2

          
  DELIMITER $$
  CREATE TRIGGER `insert_cats` AFTER INSERT ON `cats`
  FOR EACH ROW BEGIN
     INSERT INTO log Set msg = 'insert', row_id = NEW.id;
  END $$
          

Хранить ли логику в базе?

Как изменять базу?

Масштабирование

  • вертикальное
  • горизонтальное
  • во времени

Реплицирование

Репликация — тиражирование изменений данных с главного сервера БД на одном или нескольких зависимых серверах. Главный сервер — мастер, а зависимые — реплики/slave.

Как работает реплика

  • Бинлоги - представление исполняемых команд (mysqlbinlog)
  • Не данные, а запросы, вызывающие изменения

Зачем реплика

  • производительность и масштабируемость
  • отказоустойчивость
  • резервирование данных
  • отложенные/выделенные вычисления

Конфиги


  server-id = 1
  log-bin = /var/lib/mysql/mysql-bin
  replicate-do-db = dbs
          

  server-id = 2
  relay-log = /var/lib/mysql/mysql-relay-bin
  relay-log-index = /var/lib/mysql/mysql-relay-bin.index
  replicate-do-db = testdb
          

Проверка


  mysql@master> SHOW MASTER STATUS
  File: mysql-bin.000003
  Position: 98
  Binlog_Do_DB:
  Binlog_Ignore_DB:
          

  mysql@replica> SHOW SLAVE STATUS
  Slave_IO_State: Waiting for master to send event
  Slave_IO_Running: Yes
  Slave_SQL_Running: Yes
  Seconds_Behind_Master: 5
          

RO/WO

Пишем на master - читаешь со slave

MMM

Master-Master Management

M-M-M-M-M...

Master-Master Hell

Партиционирование

  • по диапазону
  • по списку значений
  • по хешу
  • по ключу

Пример партиционирования

  CREATE TABLE IF NOT EXISTS `cats` (
    `id` int(11) unsigned NOT NULL auto_increment,
    `name` varchar(50) collate utf8_unicode_ci NOT NULL,
    `birthday` date default NULL,
    PRIMARY KEY  (`id`)
  )
  PARTITION BY RANGE (cat_id) (
      PARTITION p0 VALUES LESS THAN (6),
      PARTITION p1 VALUES LESS THAN (11),
      PARTITION p2 VALUES LESS THAN (16),
      PARTITION p3 VALUES LESS THAN (21)
  );
          

Вставим > 20 и всё плохо

          
  ERROR 1526 (HY000): Table has no partition for value 21
          

MAXVALUE

Шардинг

Шардинг - хранение разбитых по некоторому принципу данных на нескольких серверах.

Варианты шардинга

  • по номеру сообщения
  • по диапазонам
  • по очередности
  • хитрая функция

Пусть имеем 100 шард, 10 БД, 2 физических сервера

            
  // по кругу из 100 таблиц
  int shard_id = key % 100;               
  // чет/нечет на каждый физический сервер          
  int connect_id = shard_id  & 0x0001;              
  // каждый 20-й в одну шарду, из них чет в одну шарду/ нечет в другую
  int db_id = (shard_id % 20) + connect_id          
          

Предгенерация

Выполняем запросы ДО того, как они понадобятся.

Очереди

Задачи должны быть выполнены в ближайшем будущем.

Прогрев

Перед использованием "прогреваем", чтобы кэши обновились, при работе не было скачка нагрузки из-за холодного старта.

Кэш

Как инвалидируем?

Redis

  • Быстрое храниличе ключ-значение
  • Подписка на изменение

MongoDB

  • Нефиксированная схема
  • Кластеризация
  • GridFS

PostgreSQL

  • Свои типы данных
  • bitmap индексы
  • Частичные индексы
  • Рекурсивные запросы
  • Наследование таблиц

PConnect

Если используем, то нужно поддержать на всех уровнях коммуникации.

Handler Socket

Сырой, но быстрый.

Проектирование

  • Бизнес-логика
  • Объёмы данных
  • Допустимая деградация
  • Схема движения данных
  • Проектируем БД
  • Пытаемся сломать

Объёмы данных

  • Старт (полгода, год)
  • Скорость приращения (функция)
  • Критический путь (супремум функции)

Допустимая деградация

  • Что должно жить всегда
  • Что может "упасть"
  • "Упасть" по времени
  • Что делать, если "упало" то, что не должно

Схема движения данных

  • Что и где обрабатывается
  • Куда оно потом идёт
  • Как это может измениться во времени
  • Что будет при масштабировании

Проектируем БД

Из проектирования базы данных вырастает проект. Все вышеперечисленные пункты должны быть учтены и описаны в реализации бд.

Пытаемся сломать

А что если ... ?

THE END???

georgy.bazhukov@gmail.com

@_bugov