Вот примеры настройки и использования **журнала медленных запросов** (slow query log) в MariaDB на русском языке, чтобы выявить и оптимизировать медленно выполняющиеся SQL-запросы, аналогично функции `log_min_duration_statement` в PostgreSQL.

1. Включение журнала медленных запросов

Журнал можно включить динамически через SQL или через файл конфигурации (my.cnf).

Пример 1: Включение журнала динамически

Чтобы включить журнал без перезапуска MariaDB:

SET GLOBAL slow_query_log = 'ON';  -- Включить журнал
SET GLOBAL long_query_time = 1;   -- Логировать запросы, выполняющиеся дольше 1 секунды
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';  -- Путь к файлу журнала
SET GLOBAL log_queries_not_using_indexes = 'ON';  -- Логировать запросы без индексов

• Проверка настроек:

  SHOW VARIABLES LIKE 'slow_query_log%';
  SHOW VARIABLES LIKE 'long_query_time';

Ожидаемый результат:

  +------------------------+------------------------------+
  | Variable_name          | Value                        |
  +------------------------+------------------------------+
  | slow_query_log         | ON                           |
  | slow_query_log_file    | /var/log/mysql/slow-query.log |
  +------------------------+------------------------------+
  +-----------------+-------+
  | Variable_name   | Value |
  +-----------------+-------+
  | long_query_time | 1.000 |
  +-----------------+-------+

Пример 2: Настройка через файл конфигурации

Редактируем файл конфигурации MariaDB (например, /etc/my.cnf или /etc/mysql/mariadb.conf.d/50-server.cnf):

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 1
log_queries_not_using_indexes = 1
log_output = FILE  -- Логировать в файл (или TABLE для таблицы mysql.slow_log)

• Применение изменений:

Перезапустите MariaDB:

  sudo systemctl restart mariadb

• Права доступа к файлу журнала:

  sudo touch /var/log/mysql/slow-query.log
  sudo chown mysql:mysql /var/log/mysql/slow-query.log
  sudo chmod 640 /var/log/mysql/slow-query.log

2. Пример записи в журнале медленных запросов

После включения журнала запросы, превышающие long_query_time, записываются в файл (например, /var/log/mysql/slow-query.log). Пример записи:

/usr/sbin/mysqld, Version: 10.5.15-MariaDB (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 2025-05-20T14:30:45.123456Z
# User@Host: app_user[app_user] @ localhost []
# Query_time: 1.234567  Lock_time: 0.000123 Rows_sent: 1000  Rows_examined: 100000
SET timestamp=1742581845;
SELECT * FROM large_table WHERE name = 'example';

• Объяснение:
• Query_time: 1.234567 секунд (запрос выполняется ~1.23 сек).
• Lock_time: Время ожидания блокировок (0.000123 сек).
• Rows_sent: 1000 строк возвращено клиенту.
• Rows_examined: 100,000 строк просканировано (возможна неэффективность).
• Запрос: SELECT * FROM large_table WHERE name = 'example'.

3. Анализ с помощью mysqldumpslow

Инструмент mysqldumpslow помогает анализировать и суммировать данные журнала.

Пример 3: Использование mysqldumpslow

Запустите команду для анализа:

mysqldumpslow /var/log/mysql/slow-query.log

• Пример вывода:

  Количество: 10  Время=1.25с (12.5с)  Блокировка=0.00с (0.00с)  Строк=1000.0 (10000), app_user[app_user]@localhost
  SELECT * FROM large_table WHERE name = 'example'

• Количество: Запрос встретился 10 раз.
• Время: Среднее время выполнения (1.25с на запрос, 12.5с всего).
• Строк: Среднее количество возвращённых строк (1000 на запрос).
• Фильтрация:

Для вывода 5 самых медленных запросов:

  mysqldumpslow -t 5 -s t /var/log/mysql/slow-query.log

• -t 5: Показать 5 запросов.
• -s t: Сортировать по общему времени.

4. Логирование в таблицу

Можно записывать медленные запросы в таблицу mysql.slow_log для анализа через SQL.

Пример 4: Включение логирования в таблицу

SET GLOBAL log_output = 'TABLE';
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;

• Анализ таблицы:

  SELECT start_time, user_host, query_time, lock_time, rows_sent, rows_examined, sql_text
  FROM mysql.slow_log
  WHERE query_time > 1
  ORDER BY query_time DESC
  LIMIT 5;

• Пример вывода:

    +---------------------+-----------+-------------+-----------+-----------+---------------+----------------------------------+
    | start_time          | user_host | query_time  | lock_time | rows_sent | rows_examined | sql_text                         |
    +---------------------+-----------+-------------+-----------+-----------+---------------+----------------------------------+
    | 2025-05-20 14:30:45 | app_user  | 00:00:01.23 | 00:00:00.00 | 1000      | 100000        | SELECT * FROM large_table WHERE name = 'example' |
    +---------------------+-----------+-------------+-----------+-----------+---------------+----------------------------------+

5. Оптимизация медленного запроса

Предположим, медленный запрос:

SELECT * FROM large_table WHERE name = 'example';

• Шаг 1: Анализ с EXPLAIN:

  EXPLAIN SELECT * FROM large_table WHERE name = 'example';

• Вывод:

    +----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
    | id | select_type | table      | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
    +----+-------------+------------+------+---------------+------+---------+------+--------+-------------+
    |  1 | SIMPLE      | large_table| ALL  | NULL          | NULL | NULL    | NULL | 100000 | Using where |
    +----+-------------+------------+------+---------------+------+---------+------+--------+-------------+

• Проблема: type: ALL — полное сканирование таблицы, индекс не используется (key: NULL).

• Шаг 2: Создание индекса:

  CREATE INDEX idx_name ON large_table(name);

• Шаг 3: Повторный анализ:

  EXPLAIN SELECT * FROM large_table WHERE name = 'example';

• Улучшенный вывод:

    +----+-------------+------------+------+---------------+----------+---------+-------+------+-------------+
    | id | select_type | table      | type | possible_keys | key      | key_len | ref   | rows | Extra       |
    +----+-------------+------------+------+---------------+----------+---------+-------+------+-------------+
    |  1 | SIMPLE      | large_table| ref  | idx_name      | idx_name | 256     | const | 1    | Using where |
    +----+-------------+------------+------+---------------+----------+---------+-------+------+-------------+

• Улучшение: type: ref, rows: 1 — индекс используется, сканируется меньше строк.

• Шаг 4: Проверка:

Выполните запрос и проверьте журнал. Если время выполнения стало меньше long_query_time, запрос не попадёт в журнал.

6. Логирование плана выполнения

Чтобы включить планы выполнения в журнал (аналог auto_explain в PostgreSQL):

SET GLOBAL log_slow_verbosity = 'query_plan,explain';

• Пример записи в журнале:

  # Time: 2025-05-20T14:32:00.123456Z
  # User@Host: app_user[app_user] @ localhost []
  # Query_time: 1.500000  Lock_time: 0.000100 Rows_sent: 1000  Rows_examined: 100000
  # EXPLAIN: id select_type table      type possible_keys key  key_len ref  rows   Extra
  # EXPLAIN: 1  SIMPLE      large_table ALL  NULL          NULL NULL    NULL 100000 Using where
  SET timestamp=1742581920;
  SELECT * FROM large_table WHERE name = 'example';

7. Автоматизация анализа

Пример 5: Скрипт для анализа логов

#!/bin/bash
LOG_FILE="/var/log/mysql/slow-query.log"
mysqldumpslow -t 10 -s t "$LOG_FILE" > /tmp/slow_query_summary.txt
cat /tmp/slow_query_summary.txt

• Сохраните как analyze_slow_queries.sh, сделайте исполняемым (chmod +x) и запустите:

  ./analyze_slow_queries.sh

Пример 6: SQL-запрос для анализа таблицы

Если используется log_output = TABLE:

SELECT sql_text, COUNT(*) as количество, AVG(query_time) as среднее_время, SUM(rows_examined) as всего_строк
FROM mysql.slow_log
GROUP BY sql_text
ORDER BY среднее_время DESC
LIMIT 5;

Примечания

• Ротация логов: Используйте logrotate для управления размером файла:

  /var/log/mysql/slow-query.log {
      weekly
      rotate 4
      compress
      missingok
      notifempty
  }

• Влияние на производительность: Параметры вроде log_queries_not_using_indexes или log_slow_verbosity = 'query_plan' могут увеличить нагрузку. Тестируйте в нерабочей среде.
• Инструменты: Используйте pt-query-digest из Percona Toolkit для глубокого анализа:

  pt-query-digest /var/log/mysql/slow-query.log

Подробности в [документации MariaDB](https://mariadb.com/kb/en/slow-query-log-overview/).

[file-name 000143_2025-05-20_14-58-14.txt]