SQL Fiddle – еще один онлайн инструмент, который можно добавить в закладки. Неплохая песочница для SQL.

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

Я решил написать этот пост, так как осознал: люди с большим опытом работы с MySQL порой не знают, что  база имеет встроенную возможность профилирования запросов. Появилась она еще с MySQL 5.0.37.

В MySQL есть 2 sql–команды для работы с профайлером:

SHOW PROFILE

SHOW PROFILES

Они показывают профили запросов сделанных за время текущей сессии.

По умолчанию профайлер в базе выключен и, чтобы его включить, необходимо выполнить команду:

mysql> set profiling=1;

Включили? Теперь выполните несколько произвольных запросов … и посмотрим что нам скажет профайлер:

mysql> show profiles;

Мы увидим нумерованный список запросов. По умолчанию профайлер хранит последние 15 запросов(можно настроить set profiling_history_size = 50). Чтобы посмотреть подробную информацию о каком либо из них:

mysql> show profile for query 5; // 5 - номер в списке

И мы должны увидеть табличку с двумя колонками:

+--------------------------------+----------+
| Status                         | Duration |
+--------------------------------+----------+
| starting                       | 0.000024 |
| checking query cache for query | 0.000054 |
| Opening tables                 | 0.000025 |
| System lock                    | 0.000004 |
| Table lock                     | 0.000036 |
| init                           | 0.000014 |
| optimizing                     | 0.000007 |
| statistics                     | 0.000015 |
| preparing                      | 0.000011 |
| executing                      | 0.000005 |
| Sending data                   | 0.000117 |
| end                            | 0.000008 |
| query end                      | 0.000003 |
| freeing items                  | 0.000031 |
| storing result in query cache  | 0.000006 |
| logging slow query             | 0.000002 |
| cleaning up                    | 0.000003 |
+--------------------------------+----------+

Duration – это длительность выполнения операции(как подсказывает кэп), а Status – сама операция. Полный список возможных операций можно посмотреть тут.

Информацию из профайлера можно получить не только командами SHOW PROFILE, но и обычным SELECT запросом к таблице PROFILING из INFORMATION_SCHEMA, т.е.:

mysql> select STATE, FORMAT(DURATION, 6) as DURATION 
              from INFORMATION_SCHEMA.PROFILING where QUERY_ID = 5;

Успешного вам анализа и оптимизации!

Недавно обнаружил для себя интересную альтернативу Replace в MySQL.

Суть: если у нас после добавления новой записи возникает дублирование( по первичному или уникальному ключу) – то мы обновляем старую запись, причем правило для обновления задаем сами. Появилось свойство еще в MySQL 4.1.0, но раньше как-то не обращал внимания.

Чтобы не уходить далеко от практики, разберем пример. Создадим таблицу:

CREATE TABLE `test` (
`a` INT,
`b` INT,
`c` INT,
PRIMARY KEY ( `a` )
)

Добавим одну запись в таблицу:

INSERT INTO `test` (`a` ,`b` ,`c`) VALUES (1, 1, 1);

Попробуем добавить еще одну запись в таблицу с дублированием ключа:

INSERT INTO `test` (`a` ,`b` ,`c`) VALUES (1, 2, 3);

Получим ошибку:

#1062 – Duplicate entry ‘1’ for key ‘PRIMARY’

и запись не будет добавлена.

А теперь мы хотим построить такой запрос, который при совпадении индекса обновит информацию в старой записи:

INSERT INTO test (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE c=c+1;

В итоге получим таблицу:

Что для нас может быть интересно? что во второй части, а именно в UPDATE мы можем прописать не обязательно инкремент значения поля, а любые действия, которые захотим – как одно, так и несколько (через запятую).

INSERT INTO test (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE a=300, b=300, c = a+b;

PHP:

Необходимо задать так $a, чтобы следующее выражение

($a[0] === 1 && $a[0] === 2)

вернуло TRUE.

Подсказка: вопрос на понимание использования SPL.

JavaScript:

Необходимо так задать sum, чтобы выражение:

sum(3)(7)

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

+sum(3)(7)...(5)

т.е. параметров может быть неограниченное количество.

Подсказка: В данной задаче поднимаются темы: создания объекта, замыкание, псевдостатические переменные, магические методы.

SQL:

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

Успешной подготовки!

1. Движок(engine) обеих связываемых таблиц должен быть InnoDB.

2. Связываемые поля должны быть проиндексированы и иметь один тип ( размер и знак должен быть один )

Из основного это все.

Теперь синтаксис взятый из официального мануала:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION

Где:

symbol - имя ключа
index_name - имя поля в таблице, которое мы хотим сделать ключом tbl_name - таблица, с которой осуществляем связывание index_col_name - имя поля, с которым связываем наш ключ reference_option - какое действие осуществляем в случае чего

Для пользователей phpmyadmin все это можно осуществить кнопочкой Relation view в структуре таблицы, в которой хотим задать внешний ключ.

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