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

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

CREATE TABLE

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    (create_definition,...)
    [table_options]
    [partition_options]

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    [(create_definition,...)]
    [table_options]
    [partition_options]
    select_statement

Or:

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
    { LIKE old_tbl_name | (LIKE old_tbl_name) }

create_definition

create_definition:
    col_name column_definition
  | [CONSTRAINT [symbol]] PRIMARY KEY [index_type] (index_col_name,...)
      [index_option] ...
  | {INDEX|KEY} [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] UNIQUE [INDEX|KEY]
      [index_name] [index_type] (index_col_name,...)
      [index_option] ...
  | {FULLTEXT|SPATIAL} [INDEX|KEY] [index_name] (index_col_name,...)
      [index_option] ...
  | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name,...) reference_definition
  | CHECK (expr)

column_definition, reference_definition

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string']
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]
      [reference_definition]
 
reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [ON DELETE reference_option]
      [ON UPDATE reference_option]
 
reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

Обновление кортежа в родительском отношении

RESTRICT, NO ACTION: Не разрешать изменение
CASCADE: Изменить каскадно
SET NULL: Установить в NULL
SET DEFAULT: Установить значение по умолчанию

CREATE TABLE (пример)

CREATE TABLE shop (
    article INT(4) UNSIGNED ZEROFILL DEFAULT '0000' NOT NULL,
    dealer  VARCHAR(20) DEFAULT '' NOT NULL,
    price   DECIMAL(16, 2) DEFAULT 0.00 NOT NULL,
    PRIMARY KEY pk_shop (article, dealer)
) ENGINE = InnoDB;

CREATE TABLE test (
    id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    UNIQUE KEY (article)
) ENGINE = InnoDB AS SELECT article, dealer, price FROM shop;

ALTER TABLE

ALTER [IGNORE] TABLE tbl_name
    [alter_specification [, alter_specification] ...]
    [partition_options]

alter_specification:
    table_options
| ADD [COLUMN] (col_name column_definition,...)
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
        [index_type] (index_col_name,...) [index_option] ...
| ADD [CONSTRAINT [symbol]]
        FOREIGN KEY [index_name] (index_col_name,...)
        reference_definition
  | CHANGE [COLUMN] old_col_name new_col_name column_definition
        [FIRST|AFTER col_name]
  | MODIFY [COLUMN] col_name column_definition
        [FIRST | AFTER col_name]
  | DROP [COLUMN] col_name
  | DROP PRIMARY KEY
  | DROP FOREIGN KEY fk_symbol
  | DISABLE KEYS
  | ENABLE KEYS
  | RENAME [TO|AS] new_tbl_name
  | ORDER BY col_name [, col_name] ...

INFORMATION_SCHEMA

CHARACTER_SETS
COLLATIONS
COLUMNS
COLUMN_PRIVILEGES
ENGINES
EVENTS
GLOBAL_STATUS and SESSION_STATUS
GLOBAL_VARIABLES and SESSION_VARIABLES
KEY_COLUMN_USAGE
PARTITIONS
PLUGINS
PROCESSLIST
PROFILING
REFERENTIAL_CONSTRAINTS
ROUTINES
SCHEMATA
SCHEMA_PRIVILEGES
STATISTICS
TABLES
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TRIGGERS
USER_PRIVILEGES
VIEWS

INFORMATION_SCHEMA: TABLES

TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE
ENGINE
VERSION
ROW_FORMAT
TABLE_ROWS
AVG_ROW_LENGTH
DATA_LENGTH
MAX_DATA_LENGTH
INDEX_LENGTH
DATA_FREE
AUTO_INCREMENT
CREATE_TIME
UPDATE_TIME
CHECK_TIME
TABLE_COLLATION
CHECKSUM
CREATE_OPTIONS
TABLE_COMMENT

INFORMATION_SCHEMA: COLUMNS

TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
ORDINAL_POSITION
COLUMN_DEFAULT
IS_NULLABLE
DATA_TYPE
CHARACTER_MAXIMUM_LENGTH
CHARACTER_OCTET_LENGTH
NUMERIC_PRECISION
NUMERIC_SCALE
DATETIME_PRECISION
CHARACTER_SET_NAME
COLLATION_NAME
COLUMN_TYPE
COLUMN_KEY
EXTRA
PRIVILEGES
COLUMN_COMMENT
GENERATION_EXPRESSION

SELECT

SELECT
    [ALL | DISTINCT | DISTINCTROW ]
      [HIGH_PRIORITY]
      [STRAIGHT_JOIN]
      [SQL_CACHE | SQL_NO_CACHE] [SQL_CALC_FOUND_ROWS]
    select_expr [, select_expr ...]
    [FROM table_references
    [WHERE where_condition]
    [GROUP BY {col_name | expr | position}
      [ASC | DESC], ... [WITH ROLLUP]]
    [HAVING where_condition]
    [ORDER BY {col_name | expr | position}
      [ASC | DESC], ...]
    [LIMIT {[offset,] row_count | row_count OFFSET offset}]

Типы таблиц

Постоянные таблицы
Временные таблицы
Виртуальные таблицы (представления)

Фильтрация

=
!=, <>
>, <, >=, <=
BETWEEN
IN
LIKE (_, %)
OR
AND
NOT

JOIN-ы

CASE

CASE case_value
    WHEN when_value
        THEN statement_list
    [WHEN when_value
        THEN statement_list] ...
    [ELSE statement_list]
END CASE

Or:

CASE
    WHEN search_condition THEN statement_list
    [WHEN search_condition THEN statement_list] ...
    [ELSE statement_list]
END CASE
 
IF (EXP1, EXP2, EXP3)

Формирование групп

Группировка по одному столбцу
Группировка по нескольким столбцам
Группировка посредством выражений
WITH ROLLUP

SET sql_mode = 'ONLY_FULL_GROUP_BY';

Формирование групп

SELECT * FROM employee;

id	name	department	grade
1	John	HR	A
2	Mark	HR	A
3	Smit	IT	A
4	Lili	IT	B
5	Alex	PR	B
6	Walton	HR	B
7	Kim	PR	B
8	Marry	HR	A
9	Paul	PR	C
10	Christ	HR	A
11	Jiji	HR	B
12	Bob	PR	B

Формирование групп

SELECT grade, department, COUNT(*)
FROM employee
GROUP BY grade, department WITH ROLLUP;

grade	department	COUNT(*)
A	HR	4
A	IT	1
A		5
B	HR	2
B	IT	1
B	PR	3
B		6
C	PR	1
C		1
		12

Агрегаторы

Name	Description
AVG()	Return the average value of the argument
BIT_AND()	Return bitwise and
BIT_OR()	Return bitwise or
BIT_XOR()	Return bitwise xor
COUNT(DISTINCT)	Return the count of a number of different values
COUNT()	Return a count of the number of rows returned
GROUP_CONCAT()	Return a concatenated string
MAX()	Return the maximum value
MIN()	Return the minimum value
STD()	Return the population standard deviation
STDDEV()	Return the population standard deviation
SUM()	Return the sum
VARIANCE()	Return the population standard variance

HAVING

SELECT
    column_name, aggregate_function(column_name)
FROM
    table_name
WHERE
    column_name operator value
GROUP BY
    column_name
HAVING
    aggregate_function(column_name) operator value

ORDER BY, LIMIT

ORDER BY {col_name | expr | position}
    [ASC | DESC], ...

LIMIT {[offset,] row_count | row_count OFFSET offset}

INSERT

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    {VALUES | VALUE} ({expr | DEFAULT},...),(...),...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

Есть еще специфичный для MySQL вариант:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    SET col_name={expr | DEFAULT}, ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

INSERT

INSERT [LOW_PRIORITY | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name [(col_name,...)]
    SELECT ...
    [ ON DUPLICATE KEY UPDATE
      col_name=expr
        [, col_name=expr] ... ]

INSERT

INSERT INTO tbl_temp2 (fld_id)
    SELECT
        tbl_temp1.fld_order_id
    FROM
        tbl_temp1
    WHERE
        tbl_temp1.fld_order_id > 100;
 
INSERT INTO table (a, b, c) VALUES (1, 2, 3)
    ON DUPLICATE KEY UPDATE c = c + 1;
 
INSERT INTO table (a, b, c) VALUES (1, 2, 3), (4, 5, 6)
    ON DUPLICATE KEY UPDATE c = VALUES(a) + VALUES(b);

UPDATE

UPDATE [LOW_PRIORITY] [IGNORE] table_reference
    SET    col_name1={expr1|DEFAULT}
        [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-table syntax:

UPDATE [LOW_PRIORITY] [IGNORE] table_references
    SET    col_name1={expr1|DEFAULT}
        [, col_name2={expr2|DEFAULT}] ...
    [WHERE where_condition]

UPDATE

UPDATE table1
SET
    col_name1 = col_name2,
    col_name2 = col_name1

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 table1
SET
    col_name1 = (@swap := col_name1),
    col_name1 = col_name2,
    col_name2 = @swap

DELETE

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
    [WHERE where_condition]
    [ORDER BY ...]
    [LIMIT row_count]

Multiple-table syntax (MySQL only):

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    tbl_name[.*] [, tbl_name[.*]] ...
    FROM table_references
    [WHERE where_condition]

Or:

DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
    FROM tbl_name[.*] [, tbl_name[.*]] ...
    USING table_references
    [WHERE where_condition]

DELETE

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

Or:

DELETE t1, t2
FROM
    t1 INNER JOIN t2
    INNER JOIN t3
WHERE
    t1.id = t2.id AND t2.id = t3.id;

Or:

DELETE FROM t1, t2
USING
    t1 INNER JOIN t2
    INNER JOIN t3
WHERE
    t1.id = t2.id AND t2.id = t3.id;

Список смежных вершин (Adjacency List)

Главный недостаток такого подхода — необходимо достоверно знать количество уровней вложенности в вашей иерархии, кроме того, чем больше иерархия, тем больше JOIN'ов — тем ниже производительность.

Тем не менее, данный способ обладает и существенными достоинствами — в дерево легко вносить изменения, менять местами и удалять узлы.

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

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

Однако он плохо применим, когда нужно вычитывать какие-либо иные куски дерева, находить пути, предыдущие и следующие узлы при обходе и вычитывать ветки дерева целиком (на всю глубину).

Вложенное множество (Nested Set)

Nested Set действительно хорош, когда нам необходимо считывать структуру деревьев из БД. При этом он одинаково хорош для деревьев любого объема.

Тем не менее, для иерархических структур, которые подвергаются частому изменению он, очевидно, не будет являться оптимальным выбором.

Материализованный путь (Materialized Path)

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

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

Алгоритм довольно уверенно себя чувствует на достаточно больших объемах данных.

Наиболее неприятной в данном алгоритме будет операция вставки узла в середину уже существующей структуры и перенос одной ветки в другую.

А вот удаление, добавление в конец или изменение узла — это операции довольно простые, и, как правило, не вызывают сложностей в данной модели.

Комбинированный подход

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

Списки смежности + материализованный путь (Adjacency List + Materialized Path)
Списки смежности + вложенные множества (Adjacency List + Nested Set)

Комбинировать же Nested Set и Materialized Path особого смысла не имеет, т.к. существенного выигрыша ни в чтении, ни в записи вы не получите.

Adjacency List + Materialized Path

Для AL при использовании с MP:

Улучшаются операции выборки ветвей и поддеревьев целиком;
Ухудшаются операции переноса ветвей.

Для MP при использовании с AL:

Улучшаются операции выборки наследников заданного узла;
Улучшаются операции выборки родителей заданного узла.

Adjacency List + Nested Set

Для связки AL+NS взаимовыгодность не столь очевидна.

В первую очередь это объясняется тем, что недостатки от проблем изменения узлов дерева в модели NS напрочь убивают в этой сфере все достоинства AL.

Это значит, что такую связку следует рассматривать лишь как качественное улучшение поиска родителей и наследников заданного узла в алгоритме NS, а также как повышение надежности самого алгоритма (ключи можно всегда перестроить в случае порчи — информацию о связях хранит AL).

Но ведь и это качественное улучшение, хотя и не такое очевидное.