Можно ли как-нибудь узнать, какие запросы к какой таблице производились на протяжении работы веб-приложения? И сколько времени они занимали?

Question

[sorry_i_noob]

Здравствуйте! Читаю статью на хабре Оптимизация работы с MySQL. Там сказано:

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

Неужели нужно в коде всего приложения искать все запросы к определенной таблице и анализировать их? Может, можно как-нибудь в MySQL это вывести? Какие запросы были к какой таблице и сколько времени они занимали? Сравнить эти запросы - какие чаще, какие реже. Какие занимали много мс, какие мало. Можно так?

Answer 1

[Rsa97]

Обычно анализ начинают с включения журнала медленных запросов (slow query log). Туда записываются все запросы, анализирующие больше заданного количества строк и занимающие больше заданного времени.
Если этого не хватает, то можно включить общий журнал запросов (general query log). Туда уже попадают все запросы и информация о подключении/отключении клиентов.

Comments

[sorry_i_noob]

А если БД пока что не особо заполнена? Никак не проанализировать? Нужно ждать, когда она заполнится и только потом расставлять индексы?

[Rsa97]

sorry_i_noob, Преждевременная оптимизация - одна из ошибок разработчика. Оптимизировать надо когда уже понятны узкие места.

[sorry_i_noob]

Rsa97, то есть, пока БД не заполнена, об индексах вообще думать не надо?

[Rsa97]

sorry_i_noob, Ну почему же, первичные, уникальные и индексы внешних ключей составить всё равно придётся. А вот в остальном, IMHO, лучше сначала проводить анализ. Например, если в таблицу данные добавляются каждую секунду, а запрашиваются раз в сутки, то лишние индексы будут только мешать.

[ThunderCat]

sorry_i_noob,

то есть, пока БД не заполнена, об индексах вообще думать не надо?

Не надо путать необходимые очевидные шаги с преждевременной оптимизацией. Все поля явно указанные в условиях выборки как сравниваемые должны быть индексированы. Другое дело что на разном количестве записей бд будет выбирать разные методы выборки/объединения таблиц, тогда и понадобится(возможно!) оптимизация запросов, а пока нет смысла думать об этой проблеме.

[sorry_i_noob]

Rsa97, понял, спасибо. Есть вопрос по поводу "индексы внешних ключей"...
Допустим, есть таблицы: "Теги", "Публикации" и "Публикации_Теги" (это отношение многие-ко-многим для первых двух таблиц). У последней таблицы есть столбики "ид_тега", "ид_публикации". На них нужно ставить индексы? Так как это внешние ключи, да? Тип индекса - обычный INDEX. Правильно я понимаю?

[Rsa97]

sorry_i_noob, Первичным индексом такой таблицы должен быть составной ("ид_тега", "ид_публикации"). Тогда для внешнего ключа к таблице тэгов индекс уже будет (начало составного индекса), а к таблице публикаций индекс создастся автоматически при создании внешнего ключа.

[sorry_i_noob]

Rsa97, тогда получается, что в таблице "Публикации_Теги" не должно быть столбика "ид", уникально идентифицирующего строку? Вместо него есть два столбика - "ид_тега", "ид_публикации"?
Это всегда так надо делать в таблицах отношений? "один-ко-многим", "многие-ко-многим", "один-к-одному"? Не должно быть столбика "ид", который идентифицирует строку. Вместо него два столбика - ид строк таблиц, для которых создано это отношение?

[Rsa97]

sorry_i_noob, Такой способ имеет смысл именно в таблице отношений многие-ко-многим, когда пары должны быть уникальными. В большинстве остальных случаев используется автоинкрементный id или, например, генерируемый guid. Бывают варианты, когда стоит использовать натуральный ключ, например двухбуквенный код страны.

[sorry_i_noob]

Rsa97, большое спасибо, теперь все понятно. А не подскажете еще. Я читал, что там, где в WHERE написаны два столбца, нужно делать один составной индекс, состоящий, из двух столбцов. Порядок столбцов в индексе такой же, как в WHERE.

А вот если у меня два WHERE - для двух столбцов. Мне два индекса создавать (по одному на каждый столбец) или один? И если один, то какой порядок столбцов у него будет? Чем чаще запрос, где используется WHERE к определенному столбцу, тем первее этот столбец в индексе?
То есть, нужно еще как-то определить, какие запросы чаще, а какие реже.

[Rsa97]

MySQL пытается подобрать индекс, начинающийся и идущий в том же порядке, что и условия во WHERE. При этом, если столбец находится внутри функции, то индекс работать не будет.
WHERE `dist` > 100 - может использоваться индекс по `dist`.
WHERE sqrt(`dist`) > 10 - индекс использоваться не будет.
WHERE `dist` > 100 AND `type` = 'shop' - будет использоваться индекс (`dist`, `type`) или (`dist`), индексы (`type`) и (`type`, `dist`) использоваться не будут.
В зависимости от количества анализируемых запросом строк MySQL пытается оптимизировать использование индексов, поэтому точный вариани нужно смотреть в EXPLAIN.

[sorry_i_noob]

Rsa97, спасибо. А можно еще вопрос...
Если я в таблице "Теги" для столбика "ИмяТега" поставил уникальный индекс. То обычный индекс уже не нужен? Уникальный включает его?
Запрос:
WHERE ИмяТега = какая-то_строка

[Rsa97]

sorry_i_noob, Да.
Уникальный индекс - это обычный индекс, но запрещающий два одинаковых значения или набора значений. Если разрешены NULL, то все они считаются уникальными, так как NULL != NULL.
Первичный индекс - это уникальный индекс по первичному полю, в котором запрещены NULL. Первичный индекс может быть только один на таблицу.

[sorry_i_noob]

Rsa97,

Первичным индексом такой таблицы должен быть составной ("ид_тега", "ид_публикации"). Тогда для внешнего ключа к таблице тэгов индекс уже будет (начало составного индекса), а к таблице публикаций индекс создастся автоматически при создании внешнего ключа.

А имеет ли значение порядок?
"ид_тега", "ид_публикации" или "ид_публикации", "ид_тега" - есть ли разница? И если да, то на что она влияет?

[Rsa97]

sorry_i_noob, Имеет.
Любой индекс используется только с первого поля и по порядку.
То есть, индекс (`ид_тега`, `ид_публикации`) будет использоваться в выражениях

WHERE `tag_id` = :tag AND `pub_id` = :publication
WHERE `tag_id` = :tag

Но не будет в выражении
WHERE `pub_id` = :publication

Насчёт выражения
WHERE `pub_id` = :publication AND `tag_id` = :tagя не уверен, надо смотреть EXPLAIN.

Answer 2

[landergate]

1. В my.cnf:
   performance_schema=1
   
2. Перезапустить MySQL.
   
3. Импортировать sys_xx.sql из проекта mysql-sys: https://github.com/mysql/mysql-sys
   
4. Подождать немного времени, пока наберётся статистика с последнего старта БД.
   
5. Анализировать запросы в таблице `sys`.`statement_analysis`.
   

Answer 3

[Николай Конюхов]

Над прослойкой драйвера к вашей бд вы можете написать обертку и вместо вызова, к примеру, PDO::query вызывать PDOExtend::query с наполнением вида:

class PDOExtend extends PDO
{
    /** @var \Psr\Log\LoggerInterface */
    protected $logger;

    public function __construct(string $dsn, string $username, string $passwd, array $options, \Psr\Log\LoggerInterface $logger)
    {
        parent::__construct($dsn, $username, $passwd, $options);
        $this->logger = $logger;
    }

    public function query(string $preparedQuery): PDOStatement
    {
        $queryStart = microtime(true);
        $result = parent::query($preparedQuery);
        $queryStop = microtime(true);

        $this->logger->debug(sprintf('%s query executed %s', static::class, $preparedQuery), [
            'query_start_time'      => $queryStart,
            'query_finish_time'     => $queryStop,
            'elapsed'               => $queryStop - $queryStart
        ]);
        
        return $result;
    }
}

Answer 4

[Дядька Серёжа]

Гарда БД