Мой SQL

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

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

Hello world!

Почти каждая программа состоит из данных и команд обработки этих данных.

						
  print("Hello world!")
  
&&
						
  fh = fopen("filename.dat", "w")
  fwrite("Hello world", ... , fh)
  

Данные в

  • Регистрах процессора
  • Кэше процессора
  • Памяти
  • Сохраняемой памяти
  • Сети

Реальный мир

Что есть СУБД?

Что такое СУРБД

Loading...

Вспомнить всё

Let's battle begin!

					
  mysql> CREATE DATABASE testum;
  mysql> GRANT ALL PRIVILEGES ON testum.* TO
      -> userus@localhost IDENTIFIED BY 's3cr3t' WITH GRANT OPTION;
					

SHOW DATABASES

					
      mysql> SHOW DATABASES;
      +--------------------+
      | Database           |
      +--------------------+
      | information_schema |
      | testum             |
      | mysql              |
      | performance_schema |
      +--------------------+
      4 rows in set (0.09 sec)
					
  • База данных testum создалась.
  • Помимо наших баз в mysql хранятся ещё и служебные базы.

SHOW DATABASES

К слову, а что хранится в служебных базах? Например, пользователи:


      mysql> SELECT User, Host FROM mysql.user;
      +------------------+-----------+
      | User             | Host      |
      +------------------+-----------+
      | root             | 127.0.0.1 |
      | root             | ::1       |
      | debian-sys-maint | localhost |
      | root             | localhost |
      | userus           | localhost |
      | root             | home      |
      +------------------+-----------+
      6 rows in set (0.01 sec)
					

О котиках

Теперь давайте перейдём в созданную базу(use testum) и создадим тестовую таблицу:


      mysql> CREATE TABLE cats (name TEXT, age NUMERIC);
					

Проверим:


      mysql> SHOW TABLES;
      +------------------+
      | Tables_in_testum |
      +------------------+
      | cats             |
      +------------------+
      1 row in set (0.00 sec)
      
					

ACHTUNG!

Мы работаем из под root!

					
          mysql -uuserus -ps3cr3t testum
					

Больше котиков!!!

Давайте добавим в нашу базу котика!

          
      mysql> insert into cats(name, age) values('Перл', 1.2);
      Query OK, 1 row affected, 1 warning (0.07 sec)
          
В результате
          
      mysql> SELECT * FROM cats LIMIT 100;
      +------+------+
      | name | age  |
      +------+------+
      | ???? |    1 |
      +------+------+
      1 row in set (0.00 sec)
          

Вопросы?

Всё из-за того, что мы не установили, в какой кодировке хранится имя. Давайте это исправим:
          
      mysql> ALTER TABLE cats MODIFY name BLOB;
      Query OK, 1 row affected (0.02 sec)
      Records: 1  Duplicates: 0  Warnings: 0

      mysql> ALTER TABLE cats MODIFY name TEXT CHARACTER SET utf8;
      Query OK, 1 row affected (0.02 sec)
      Records: 1  Duplicates: 0  Warnings: 0
          

Убираем вопросы?

          
      mysql> DELETE FROM cats;
      Query OK, 1 row affected (0.09 sec)

      mysql> insert into cats(name, age) values('Перл', 1.2);
      Query OK, 1 row affected, 1 warning (0.07 sec)

      mysql> SELECT * FROM cats LIMIT 100;
      +----------+------+
      | name     | age  |
      +----------+------+
      | Перл     |    1 |
      +----------+------+
      1 row in set (0.00 sec)
          

Год и два месяца

          
      mysql> select 2/12 + 1;
      +----------+
      | 2/12 + 1 |
      +----------+
      |   1.1667 |
      +----------+
      1 row in set (0.00 sec)
          

Почему 1?

DESCRIBE

          
      mysql> DESCRIBE cats;
      +-------+---------------+------+-----+---------+-------+
      | Field | Type          | Null | Key | Default | Extra |
      +-------+---------------+------+-----+---------+-------+
      | name  | text          | YES  |     | NULL    |       |
      | age   | decimal(10,0) | YES  |     | NULL    |       |
      +-------+---------------+------+-----+---------+-------+
      2 rows in set (0.00 sec)
          

"DECIMAL(10,0)" - храним 10 знаков, из них после запятой - 0

Поправим

          
      mysql> ALTER TABLE cats MODIFY age DECIMAL(10,5);
      Query OK, 1 row affected, 1 warning (0.22 sec)
      Records: 1  Duplicates: 0  Warnings: 1

      mysql> DESCRIBE cats;
      +-------+----------------+------+-----+---------+-------+
      | Field | Type           | Null | Key | Default | Extra |
      +-------+----------------+------+-----+---------+-------+
      | name  | text           | YES  |     | NULL    |       |
      | age   | decimal(10,5)  | YES  |     | NULL    |       |
      +-------+----------------+------+-----+---------+-------+
      2 rows in set (0.00 sec)
      

И саму запись:

          
      mysql> UPDATE cats SET age=1 + 2/12 WHERE name="Перл";
      Query OK, 0 rows affected, 1 warning (0.14 sec)
      Rows matched: 1  Changed: 0  Warnings: 1
      
          

WARNINGS

Нам в очередной раз написали warning. Посмотрим же наконец его!

          
      mysql> SHOW WARNINGS;
      +-------+------+------------------------------------------+
      | Level | Code | Message                                  |
      +-------+------+------------------------------------------+
      | Note  | 1265 | Data truncated for column 'age' at row 1 |
      +-------+------+------------------------------------------+
      1 row in set (0.00 sec)
          

И всё же...

Нам в очередной раз написали warning. Посмотрим же наконец его!

          
      mysql> SELECT * FROM cats LIMIT 100;
      +----------+---------+
      | name     | age     |
      +----------+---------+
      | Перл     | 1.16667 |
      +----------+---------+
      1 row in set (0.00 sec)
          

Loading...

Вглубь!

Сколько лет коту?

					
  mysql> ALTER TABLE cats ADD COLUMN id INT(11) UNSIGNED NOT NULL;
  Query OK, 1 row affected (0.24 sec)
  Records: 1  Duplicates: 0  Warnings: 0

  mysql> ALTER TABLE cats ADD PRIMARY KEY(id);
  Query OK, 0 rows affected (0.19 sec)
  Records: 0  Duplicates: 0  Warnings: 0

  mysql> ALTER TABLE cats MODIFY COLUMN id INT(11)
      -> UNSIGNED NOT NULL AUTO_INCREMENT;
  Query OK, 1 row affected (0.19 sec)
  Records: 0  Duplicates: 0  Warnings: 0

  mysql> ALTER TABLE cats MODIFY COLUMN birthday DATE;
  Query OK, 0 rows affected (0.06 sec)
  Records: 1  Duplicates: 0  Warnings: 0
      
  • Суррогатный ключ
  • AUTO_INCREMENT

Паспорт

Лилу Даллас. Мульти-паспорт!

					
      mysql> CREATE TABLE passport (
          ->   id            INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, -- Паспортный номер
          ->   pet_id        INT(11) UNSIGNED NOT NULL,         -- Связь к таблице животных
          ->   colour_id     INT(11) UNSIGNED DEFAULT NULL,     -- Окрас шерсти
          ->   kind_id       INT(11) UNSIGNED DEFAULT NULL,     -- Вид ('кот', 'собака', ...)
          ->   owner_id      INT(11) UNSIGNED DEFAULT NULL,     -- Владелец
          ->   sex           ENUM('male', 'female'),            -- Пол
          ->   special_marks  VARCHAR(6000) CHARACTER SET utf8,  -- Особые приметы
          ->
          ->   PRIMARY KEY(id),              -- Первичный ключ. Красив и могуч!
          ->   UNIQUE  KEY(pet_id, kind_id), -- Объявляем пару уникальной.
          ->
          ->   FOREIGN KEY (colour_id) REFERENCES colour(id), -- Прописываем связи
          ->   FOREIGN KEY (kind_id)   REFERENCES kind(id),
          ->   FOREIGN KEY (owner_id)  REFERENCES user(id)
          -> ) ENGINE=InnoDB;
      ERROR 1005 (HY000): Can't create table 'testum.passport' (errno: 150)
  Records: 1  Duplicates: 0  Warnings: 0
      

Консистентность c InnoDB

errno: 150 - код ошибки InnoDB. "Создать таблицу не получилось из-за внешних ключей". На самом деле, это очень полезный механизм, реализованный в рамках InnoDB - контроль целостности.

Цвет


      mysql> CREATE TABLE colour (
          ->   id      INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
          ->   name    VARCHAR(255) CHARACTER SET utf8,
          ->   picture VARCHAR(255) CHARACTER SET ascii,
          ->   
          ->   PRIMARY KEY(id),
          ->   UNIQUE  KEY(name)
          -> ) ENGINE=InnoDB;
      Query OK, 0 rows affected (0.17 sec)
      

Вид


      mysql> CREATE TABLE kind (
          ->   id      INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
          ->   name    VARCHAR(255) CHARACTER SET utf8,
          -> 
          ->   PRIMARY KEY(id),
          ->   UNIQUE  KEY(name)
          -> ) ENGINE=InnoDB;
      Query OK, 0 rows affected (0.10 sec)
      

Владелец


      mysql> CREATE TABLE user (
          ->   id          INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
          ->   first_name  VARCHAR(64) CHARACTER SET utf8,
          ->   middle_name VARCHAR(64) CHARACTER SET utf8,
          ->   last_name   VARCHAR(64) CHARACTER SET utf8,
          ->   address     VARCHAR(255) CHARACTER SET utf8,
          ->   -- phone       BIGINT(12) UNSIGNED NOT NULL,
          ->   phone       VARCHAR(12) NOT NULL,
          -> 
          ->   PRIMARY KEY(id),
          ->   UNIQUE  KEY(phone)
          -> ) ENGINE=InnoDB;
      Query OK, 0 rows affected (0.12 sec)
      

Ещё раз!


      CREATE TABLE passport...
      

Неявный o2o

m2o

Прививочки!

					
      mysql> CREATE TABLE pill (
          ->   id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
          ->   name VARCHAR(128) CHARACTER SET utf8,
          ->   description TEXT CHARACTER SET utf8,
          ->   
          ->   PRIMARY KEY(id),
          ->   UNIQUE KEY(name)
          -> );
      Query OK, 0 rows affected (0.13 sec)

      mysql> 
      mysql> CREATE TABLE passport_to_pill (
          ->   passport_id INT(11) UNSIGNED NOT NULL,
          ->   pill_id INT(11) UNSIGNED NOT NULL,
          ->   created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
          ->   
          ->   UNIQUE KEY (passport_id, pill_id)
          -> );
      Query OK, 0 rows affected (0.19 sec)
					

m2m

Заливаем данные

					
    mysql> INSERT INTO cats(name, birthday) VALUES('Барсик', '2014-01-01'),
        ->            ('Шрёдингер', '1900-04-01'), ('Лютик', '2201-12-12');
    Query OK, 3 rows affected (0.06 sec)
    Records: 3  Duplicates: 0  Warnings: 0
					

Все прививки котиков

					
      mysql> SELECT c.name, pi.name 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;
      +--------------------+------------------+
      | name               | name             |
      +--------------------+------------------+
      | Перл               | Улыбин           |
      | Перл               | Узбагоин         |
      | Барсик             | Узбагоин         |
      | Шрёдингер          | Узбагоин         |
      | Шрёдингер          | Плацебо          |
      +--------------------+------------------+
      5 rows in set (0.00 sec)
					

ORDER BY

					
      mysql> SELECT c.name, pi.name 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
          -> ORDER BY c.name, pi.name; 
      +--------------------+------------------+
      | name               | name             |
      +--------------------+------------------+
      | Барсик             | Узбагоин         |
      | Перл               | Узбагоин         |
      | Перл               | Улыбин           |
      | Шрёдингер          | Плацебо          |
      | Шрёдингер          | Узбагоин         |
      +--------------------+------------------+
      5 rows in set (0.05 sec)
					

GROUP BY

					
      mysql> SELECT c.name, COUNT(pi.id) 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;
      +----------+--------------+
      | name     | COUNT(pi.id) |
      +----------+--------------+
      | Перл     |            5 |
      +----------+--------------+
      1 row in set (0.00 sec)

      mysql> SELECT c.name, COUNT(pi.id) 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;
      +--------------------+--------------+
      | name               | COUNT(pi.id) |
      +--------------------+--------------+
      | Барсик             |            1 |
      | Перл               |            2 |
      | Шрёдингер          |            2 |
      +--------------------+--------------+
      3 rows in set (0.05 sec)
					

DISTINCT

					
      mysql> SELECT u.id, CONCAT(u.first_name, ' ', u.middle_name,
          ->  ' ', u.last_name) as ФИО
          -> FROM user u JOIN passport p ON p.owner_id = u.id;
      +----+----------------------------------------+
      | id | ФИО                                    |
      +----+----------------------------------------+
      |  1 | Иван Иванович Иванов                   |
      |  1 | Иван Иванович Иванов                   |
      |  1 | Иван Иванович Иванов                   |
      |  1 | Иван Иванович Иванов                   |
      |  1 | Иван Иванович Иванов                   |
      |  1 | Иван Иванович Иванов                   |
      |  1 | Иван Иванович Иванов                   |
      +----+----------------------------------------+
      7 rows in set (0.00 sec)

      mysql> SELECT DISTINCT(u.id), CONCAT(u.first_name, ' ', u.middle_name,
          ->  ' ', u.last_name) as ФИО
          -> FROM user u JOIN passport p ON p.owner_id = u.id;
      +----+----------------------------------------+
      | id | ФИО                                    |
      +----+----------------------------------------+
      |  1 | Иван Иванович Иванов                   |
      +----+----------------------------------------+
      1 row in set (0.00 sec)

					

Собачка!

					
      mysql> CREATE TABLE dogs (
          ->    id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT,
          ->    name VARCHAR(128) CHARACTER SET utf8,
          ->    birthday DATE NOT NULL,
          ->    
          ->    PRIMARY KEY(id)
          -> );
      Query OK, 0 rows affected (0.16 sec)

      mysql> INSERT INTO dogs(name, birthday) VALUES('Бобик', '2013-02-01'),
          ->    ('Тайлер', '1970-01-01'), ('Рекс', '2001-02-12');
      Query OK, 3 rows affected (0.07 sec)
      Records: 3  Duplicates: 0  Warnings: 0
					

Уши, лапы и хвост!

					
      mysql> SELECT * FROM passport;
      +----+---------+-----------+---------+----------+--------+--------------+
      | id | pet_id  | colour_id | kind_id | owner_id | sex    | spcial_marks |
      +----+---------+-----------+---------+----------+--------+--------------+
      |  1 |       1 |         1 |       1 |        1 | male   | NULL         |
      |  2 |       2 |         1 |       1 |        1 | male   | NULL         |
      |  3 | 9999999 |         1 |       1 |        1 | male   | NULL         |
      |  4 |       4 |         1 |       1 |        1 | male   | NULL         |
      |  5 |       1 |         1 |       2 |        1 | female | NULL         |
      |  6 |       2 |         1 |       2 |        1 | female | NULL         |
      |  7 |       3 |         1 |       2 |        1 | female | NULL         |
      +----+---------+-----------+---------+----------+--------+--------------+
      7 rows in set (0.00 sec)
					

UNION

					
      mysql> (SELECT name, birthday FROM cats)
          ->  UNION
          ->  (SELECT name, birthday FROM dogs)
          ->  ORDER BY birthday, name;
      +--------------------+------------+
      | 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)
					

HAVING

Получим питомцев, у кого более одной прививки:

					
      mysql> 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;
          +----+--------------------+------------+-----------+-------+
          | id | name               | birthday   | kind_name | count |
          +----+--------------------+------------+-----------+-------+
          |  1 | Перл               | 0000-00-00 | cat       |     2 |
          |  2 | Тайлер             | 1970-01-01 | dog       |     2 |
          |  4 | Шрёдингер          | 1900-04-01 | cat       |     2 |
          +----+--------------------+------------+-----------+-------+
          3 rows in set (0.00 sec)
					

Loading...

А почему Вы спрашиваете?

Индекс

Индекс — объект базы данных, создаваемый с целью повышения производительности поиска данных.

Кластерный индекс

Его может не быть более одного. По нему упорядочены данные таблицы. Все прочие индексы - отображение каких-то данных в кластерный индекс.

WTF?

Как устроен индекс?

Сортированный файл

Например, файл упорядочен по возрастанию. Сложность log(n).

Хэш

Одностороннее отображение. Например, одно время в php хэширующей функцией была strlen. Работает за константу (добраться до множества значений) + m (перебор значений в полученном множестве).

Деревья

  • Перебалансировка
  • Fill factor

Обычный составной ключ

					
					CREATE INDEX имя ON таблица (поле1, поле2)
					
					
					CREATE INDEX имя ON таблица (поле1, поле2) USING HASH
					
					
					CREATE INDEX имя ON таблица (поле1(длина) DESC, поле2)
					

Primary Key

UNIQUE INDEX

					
					CREATE UNIQUE INDEX имя ON таблица (поле1, поле2)
					

UNIQUE KEY?

Где спрашивать?

  • where
  • order by
  • ...

EXPLAIN!!!

Loading...

Видишь суслика?

Вот скажи мне, американец, в чем сила?

					
    mysql> EXPLAIN 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;
					
				  
+----+--------------+------------+--------+------------------------+---------
| id | select_type  | table      | type   | possible_keys          | key     
+----+--------------+------------+--------+------------------------+---------
|  1 | PRIMARY      | k          | index  | PRIMARY,name           | name    
|  1 | PRIMARY      | p          | ref    | PRIMARY,pet_id,kind_id | kind_id 
|  1 | PRIMARY      | pp         | ref    | passport_id            | passport
|  1 | PRIMARY      | pi         | eq_ref | PRIMARY                | PRIMARY 
|  1 | PRIMARY      | <derived2> | ALL    | NULL                   | NULL    
|  2 | DERIVED      | cats       | ALL    | NULL                   | NULL    
|  3 | UNION        | dogs       | ALL    | NULL                   | NULL    
| NULL | UNION RESULT | <union2,3> | ALL    | NULL                   | NULL  
+----+--------------+------------+--------+------------------------+---------
					

system

Таблица содержит только одну строку (= системная таблица). Это - частный случай типа связывания const.

const

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

eq_ref

Это наилучший возможный тип связывания среди типов, отличных от const. Данный тип применяется, когда все части индекса используются для связывания, а сам индекс - UNIQUE или PRIMARY KEY.

ref

если для связывания используется только крайний левый префикс ключа, или если ключ не является UNIQUE или PRIMARY KEY

range

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

index

ALL, за исключением того, что просматривается только дерево индексов.

ALL

Всё плохо :(

По-медленнее, я записываю

				
				mysqld --log=log_file_name
				

ИЛИ

				
				log = log_file_name
				

Файл? Фи...

  			
  mysql> SET GLOBAL log_output = 'TABLE';
  Query OK, 0 rows affected (0.00 sec)

  mysql> SET GLOBAL general_log = 'ON';
  Query OK, 0 rows affected (0.05 sec)

  mysql> select event_time, thread_id, server_id, argument
      -> from mysql.general_log;
  +------------+-----------+-----------+---------------------------------+
  | event_time | thread_id | server_id | argument                        |
  +------------+-----------+-----------+---------------------------------+
  | 2014-09-20 |        38 |         0 | SET GLOBAL general_log = 'ON'   |
  | 2014-09-20 |        38 |         0 | select * from mysql.general_log |
  +------------+-----------+-----------+---------------------------------+
  2 rows in set (0.00 sec)
      

So slow

				
				mysqld --log-slow-queries=log_file_name
				

ИЛИ в /etc/my.cnf, [mysql]

				
        log-slow-queries=/tmp/slow_queries.log
        long_query_time=1
        log_queries_not_using_indexes=YES
				
				
				mysql> SET GLOBAL slow_query_log = 'ON';
        Query OK, 0 rows affected (0.00 sec)

        mysql> SET GLOBAL LONG_QUERY_TIME = 1;
        Query OK, 0 rows affected (0.00 sec)
        
        mysql> select * from mysql.slow_log ;
        Empty set (0.00 sec)
				

THE END?

georgy.bazhukov@gmail.com

@_bugov