MySQL *

Добавили новые версии баз данных 🐣

Всем приготовиться, целых 3 новых версии баз данных уже в панели. Погнали с обзором:

MySQL 8.4. Тут и повышение производительности, и фиксы багов. А еще улучшения в механизмах репликации.

А для фанатов ClickHouse — х2, сразу два релиза:

➖ 24.8.14 LTS: обновленный тип JSON, механизм обработки таблиц для временных рядов данных, однократная обработка сообщений Kafka и, конечно же, улучшения оператора JOIN. И все в одном флаконе релизе.

➖ 25.1: улучшенные алгоритмы параллельного хэш-соединения, индексы MinMax на уровне таблицы, прокачанные таблицы слияния и функция автоматического увеличения.

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

А меня даже и уговаривать не надо, уже ставлю →

MySQL и MariaDB развелись достаточно давно, и сейчас их следует уже воспринимать как две разные, хоть и высоко-совместимые СУБД. Если этого не учитывать, можно наткнуться на проблему. Потому что даже во вроде бы простой ситуации с очевидным решением можно наткнуться на различие. Одно такое различие попалось мне сегодня.

На заре становления MySQL старался быть максимально user-friendly, вплоть до lamer-friendly, и позволял то, что строгие диалекты отвергали. В частности, MySQL не был против неполной группировки, когда поле выходного набора не входило в выражение группировки, но и не было аргументом агрегатной функции. Разрешение такой фичи регулировалось флагом ONLY_FULL_GROUP_BY в системной переменной Server SQL Mode - @@sql_mode. Только в 8-й версии этот флаг стал включаться в значение переменной по умолчанию.

Также в 8-й версии были реализованы и оконные функции. Особенностью их является то, что это не агрегатные, а скалярные функции, и выполняются они уже после группировки на полях выходного набора, а не на исходных данных. Соответственно если использовать в выходном наборе оконную функцию от аргумента, не входящего в выражение группировки, или при неявной группировке, при ONLY_FULL_GROUP_BY получим ошибку.

CREATE TABLE test (id INT, x INT);
INSERT INTO test VALUES
(1,1), (1,2), (2,3), (2,4);

Соответственно запрос

SELECT id,
       SUM(x) sum_x,
       SUM(x) OVER (PARTITION BY id) wsum_x
FROM test
GROUP BY id;

будет выполнен при сброшенном флаге и приведёт к ошибке 1055 при установленном.

А вот в MariaDB, по крайней мере для версий 10.3 и старше, как выяснилось, в случае такого комбинирования ошибка - не возникает! Более того, наличие в Server SQL Mode флага ONLY_FULL_GROUP_BY - просто игнорируется!

То ли бага, то ли фича, но логического объяснения такой "особенности" я не нахожу. Придётся просто помнить.

В Фреймворк DBGridChart добавлено OLAP-приложение "Экспресс_обработка_таблицы.exe ", позволяющее при разработке программ на C++, Java, Visual Basic, F# и на других языках программирования (имеющих средства выполнения из программы готовых приложений из командной строки с параметрами) автоматическое формирование табличных и ленточных форм, форм с диаграммами и графиками с готовым унифицированным многофункциональным интерфейсом пользователя для корректировки баз данных, OLAP-анализу и прогнозированию данных в табличной и в графической формах.

Таким образом, программист при разработке приложения, даже на языке, отличным от C#, может использовать все средства и возможности Фреймворка DFGridChart путем обращения к приложению "Экспресс_обработка_таблицы.exe " с указанием через параметры значения свойствам, например: текст строки подключения к базе данных, текст команды Select, текст строки подстановки. Порядок формирование таких таблиц определяется интерфейсом (меню, кнопки), разрабатываемого программистом для приложения.

Гиперссылка для скачивания Фреймворка "DBGridChart", OLAP-приложения "Экспресс_обработка.exe" и "Экспресс_обработка_таблицы.exe " с исходными текстами, DLL-библиотеками, документацией и с демонстрационными примерами: https://disk.yandex.ru/d/sI1VhBiSPzDp-A

Пример обращения к приложению "Экспресс_обработка_таблицы.exe " при разработке программы на C++

#include <string>

...

// Символ # должен быть указан перед наименованием свойства!// Внутри строковых констант перед символами \ и " нужно указывать символ \
std::string
program = "C:\\Экспресс_обработка_таблицы\\bin\\Debug\\Экспресс_обработка_таблицы .exe", // Местоположение приложения
p1 = "#ConnString=Provider = Microsoft.Jet.OLEDB.4.0;Data Source="C:\\Демонстрационный пример Экспресс обработка таблицы для C++\\Выпуск изделий.mdb", // Строка подключения к базе данных
p2 = "#DBMS = Access", // Тип СУБД, если не указан (p2 не указано), то определяется по строке подключения
p3 = "#Select = SELECT Подразделения.[Код подразделения]@#,Подразделения.[Наименование подразделения]*, Подразделения.[Численность на 2022], Подразделения.[Численность на 2023], Подразделения.[Численность], Подразделения.[Дата формирования], Подразделения.[Код типа подразделения]*, [Типы подразделений].[Наименование типа подразделения], Подразделения.[Действующее] FROM [Типы подразделений] RIGHT JOIN Подразделения~ ON [Типы подразделений].[Код типа подразделения] = Подразделения.[Код типа подразделения] ORDER BY Подразделения.[Наименование подразделения]", // Запрос с возможностью корректировки таблицы Подразделения. Специальные символы при режиме корректировки после имен полей или псевдонимов: ключевое (@), обязательное (*), не корректируемое (#) поле и после имени корректируемой таблицы указывается символ ~, если таблиц несколько во фразе From.
p4 = "#Podstavit_v_kolonki = ктп #-; SELECT [Наименование типа подразделения], [Код типа подразделения] FROM [Типы подразделений] ORDER BY [Наименование типа подразделения]", //Подстановка кода типа подразделения в колонку с именем ктп по наименованию путем выбора мышкой из списка (сформированного указанной командой Select) наименований типов подразделений. Режимы подстановки: только из списка (-), список открывается в текущей ячейке (#).
p5 = "#Flag_Create_SQL_Select = True", // Разрешить (True)/не разрешить (False) использовать конструктор команд SQL (по умолчанию - True и p5 можно не указывать)
p6 = "#Table_№ = 2367", // Уникальный номер (обычно, случайное число) таблицы или запроса для сохранения макета настройки индивидуально для таблицы
parameters = p1 + p2 + p3 + p4 + p5 + p6, // параметры приложения
command = program + " " + parameters; // командная строка
system(command.c_str()); // выполнение приложения, которое формирует табличную форму с многофункциональным унифицированным интерфейсом Фреймворка DBGridChart

Добавили на сайт быстрый подбор сервисов с помощью ИИ

Теперь не нужно бегать по интернету с запросами по типу «параметры сервера под 1с битрикс» и искать нужные тарифы в облаках. Достаточно зайти на одну страницу и простыми словами рассказать о проекте.

ИИ моментально порекомендует оптимальное решение и подберет инфраструктуру на базе нашего облака — с точной стоимостью каждого сервиса и возможностью заказа прямо на сайте.

Например, у вас высоконагруженный мобильный мессенджер с голосовыми и видеозвонками, трафик более 100 000 активных пользователей в день, база данных MySQL, для сообщений и медиафайлов нужно 2 ТБ.

ИИ предложит два сервера с 8 ядрами, 12 и 16 ГБ оперативки для обработки большого объема данных. Конфигурацию с 4 ядрами и 8 ГБ оперативки для базы данных MySQL и балансировщик с 2 репликами для устойчивого распределения трафика. И, наконец, хранилище на 2 ТБ.

Пока ИИ работает не со всеми сервисами и тарифами, но постепенно мы его дообучаем.

Подобрать сервисы с помощью ИИ →

Как я снизил нагрузку на базу данных в 53 раза с помощью ChatGPT

Недавно запустил Telegram-бота для мониторинга сайтов. Он раз в 10 минут проверяет сайты, добавленные пользователями. Постепенно их проверка стала занимать существенное время.

Сначала я думал, что проблема в медленных сайтах и долгих ответах whois. Потом замерил разные участки кода и понял, что примерно 3 секунды для каждого из сайтов отрабатывает запрос к моей же базе данных (MySQL). С помощью него я получаю результаты предыдущих проверок: последней и последней успешной. Это нужно, чтобы сравнить с текущим результатом и уведомить пользователя об изменениях.

Запрос выглядел примерно так:

SELECT *
FROM 
    checks c
LEFT JOIN (
    SELECT *
    FROM checks c2
    WHERE c2.id = (
            SELECT MAX(c3.id)
            FROM checks c3
            WHERE c3.db_message_id = c2.db_message_id 
              AND c3.response_received = true
        )
) AS sub 
ON  c.db_message_id = sub.db_message_id
WHERE
    c.db_message_id = :db_message_id
ORDER BY c.id DESC 
LIMIT 1;

Я попросил ChatGPT o1-preview оптимизировать код, его предложение:

SELECT *
FROM
    (SELECT * 
     FROM checks 
     WHERE db_message_id = :db_message_id 
     ORDER BY id DESC 
     LIMIT 1) c
LEFT JOIN
    (SELECT *
     FROM checks
     WHERE db_message_id = :db_message_id AND response_received = true
     ORDER BY id DESC
     LIMIT 1) c2
ON 1=1;

Время обработки сайтов снизилось примерно в 10 раз, нагрузка на базу — в 53.

Кажется, подписка окупилась.

Очередной баг в MySQL.

Нашёл ещё один баг в MySQL. Поскольку Оракл все российские регистрации позакрывал - напишу хоть сюда, что ли...

Баг найден в запросе LOAD XML INFILE. Суть бага - если выполняется загрузка многоуровневого (nested) XML, то после обработки атрибутов нижнего уровня расположенные ниже по тексту атрибуты верхнего уровня не обрабатываются.

Например, имеется такой XML:

<data>
<item col1="1">
  <col2>2</col2>
  <nested col3="3">x</nested>
  <col4>4</col4>
</item>
<item col1="11">
  <col2>22</col2>
  <col4>44</col4>
  <nested col3="33">xx</nested>
</item>
</data>

Выполняем его импорт в таблицу

CREATE TABLE data (
    col1 INT,
    col2 INT,
    col3 INT,
    col4 INT,
	nested VARCHAR(4)
    );

запросом

LOAD XML INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/test.xml' 
INTO TABLE data
ROWS IDENTIFIED BY '<nested>';

и видим, что для первой записи, где атрибут col4 расположен после вложенного атрибута nested, импорт col4 не выполняется. А во второй записи всё нормально.

mysql> SELECT * FROM data;
+------+------+------+------+--------+
| col1 | col2 | col3 | col4 | nested |
+------+------+------+------+--------+
|    1 |    2 |    3 | NULL | x      |
|   11 |   22 |   33 |   44 | xx     |
+------+------+------+------+--------+
2 rows in set (0.00 sec)

В общем, если работаете с импортом XML - будьте осторожны. И лучше импортируйте каждый уровень в отдельную таблицу.

Сегодня получилось узнать "что-нибудь новенького"©. А именно - узнал, когда именно проверяется ссылочная целостность при ссылке на себя.

Создадим таблицу:

CREATE TABLE test (
  id SERIAL PRIMARY KEY,
  FOREIGN KEY (id) REFERENCES test (id)
);

Таблица ссылается сама на себя. Более того, поле ссылается само на себя. Практического смысла - ноль, но это позволяет узнать тонкости. Тип данных SERIAL выбран потому, что он создаёт дополнительный вторичный индекс, необходимый для создания внешнего ключа. Для других типов данных индекс надо создавать явно, причём до создания внешнего ключа.

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

Пробуем.

INSERT INTO test VALUES (1);
SELECT * FROM test;

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

Всё это можно увидеть, например, на этом fiddle.

Зачем это может понадобиться? не знаю...

Как мы объединили два разных екома в одну CRM

Оба интернет-магазина — назовем их А и Б — годами работали самостоятельно, накопили много контактов и клиентов, а потом объединились в одну компанию. Чтобы выстроить продажи, им нужна была общая база данных. Но стек у магазинов отличался, и понадобилась наша помощь.

Помимо стека, были и другие ограничения:

Срок MVP: на всё про всё — полгода.

Бюджет: лепить огромного отказоустойчивого мастодонта мы не могли.

Удобство: нужен был сервис одного окна с понятным интерфейсом.

Поэтому мы остановились на Bitrix24. Первым делом определили, что должно быть в общей CRM и какие данные нам нужны. Потом на этапе ППО выбрали механизм реализации — процесс ETL (Extract. Transform. Load). Он состоит из трех этапов:

• извлечение данных из имеющихся баз,

• преобразование их под новую бизнес-модель,

• загрузка в новую CRM.

>> Подробно про каждый этап рассказываем в отдельной статье.

В итоге пришли вот к такой архитектуре: 

Как видим, у нас было три экстрактора: общий для магазина А и два отдельных для магазина Б (один для Kafka, другой для Json). Два трансформера — для каждого магазина свой, они выдавали одинаковые DTO и передавали их в лоадер. Дальше лоадер закидывал всё в B2B CRM.

В результате нам удалось выгрузить свыше 170 000 активных компаний и более 264 000 контактов из обоих интернет-магазинов.

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

Опечатка в MySQL 8.1 Reference Manual

Читая документацию по процессу апгрейда до версии 8.1, нашёл опечатку.

На странице 2.10.5 Preparing Your Installation for Upgrade имеется пункт 5 с описанием проверки на то, что отсутствуют констрейнты с именами более 64 символов. Приведён текст запроса, который это проверяет. Однако в нём по ошибке вместо таблицы INNODB_FOREIGN написана несуществующая таблица INNODB_SYS_FOREIGN.

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

SELECT TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME IN
  (SELECT LEFT(SUBSTR(ID,INSTR(ID,'/')+1),
               INSTR(SUBSTR(ID,INSTR(ID,'/')+1),'_ibfk_')-1)
   FROM INFORMATION_SCHEMA.INNODB_FOREIGN
   WHERE LENGTH(SUBSTR(ID,INSTR(ID,'/')+1))>64);

PS. Багтрак недоступен (учётные записи россиян заблокированы, а от анонимусов репорты не принимаются), напишу хоть здесь, чтобы не потерялось (и так уже с десяток багов протерялся/забылся).

MySQL тоже может выдать невменяемое сообщение об ошибке

Вообще MySQL славен тем, что если в SQL-коде встретилась синтаксическая ошибка, он возвращает сообщение об ошибке, которое точно описывает суть проблемы и точно указывает место в коде, где обнаружена ошибка. Но и на старуху бывает проруха..

Неожиданно в достаточно простом коде сообщение об ошибке совершенно не соответствует проблеме:

CREATE TABLE t1 (id INT);
CREATE TABLE t2 (id INT);
SELECT * FROM t1 FULL JOIN t2 ON t1.id = t2.id;

Вопреки ожидаемому сообщению о проблеме с FULL JOIN (MySQL в принципе не поддерживает этот тип связывания) сервер неожиданно выдаёт

ERROR 1054 (42S22): Unknown column 't1.id' in 'on clause'

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

Понятно, что эта проблема связана с парсером. FULL является в MySQL ключевым (но не зарезервированным) словом, и используется в некоторых административных запросах (например, в некоторых SHOW).

Увы, исправить это нельзя. Можно только запомнить.

Невидимые столбцы в MySQL 8

Продолжая писать о малоизвестных возможностях MySQL хочу рассказать о портированном со старшего брата Oracle DB. Так начиная с версии 8.0.23 любой столбец таблицы можно объявить невидимым.

Это можно сделать как при создании таблицы, так и при добавлении новой колонки.

CREATE TABLE test_table (
  a INT,
  b DATE INVISIBLE
) ENGINE = InnoDB;
ALTER TABLE test_table ADD COLUMN c INT INVISIBLE;

Видимость колонки может быть изменена в любое время:

ALTER TABLE test_table CHANGE COLUMN b b DATE VISIBLE;
ALTER TABLE test_table MODIFY COLUMN b DATE INVISIBLE;
ALTER TABLE test_table ALTER COLUMN c SET VISIBLE;

Следующие команды покажут скрытые колонки с флагом INVISIBLE:

SHOW COLUMNS FROM test_table;
SHOW CREATE TABLE test_table;

При выборке всех столбцов (SELECT *) скрытые столбцы не видны, только при явном указании:

TABLE test_table; SELECT * FROM test_table; -- столбец с скрыт

SELECT a, b, c FROM test_table; -- столбец с показан

То же самое при вставке, без явного указания не работает:

INSERT INTO test_table VALUES (1, now(), 33); == ошибка
INSERT INTO test_table () VALUES (1, now(), 33); == тоже ошибка

INSERT INTO test_table VALUES (1, 22); -- вставлен NULL
INSERT INTO test_table (a, b, c) VALUES (1, now(), 33); -- все значения

UPDATE для скрытых столбцов работает как обычно.

Зачем нужно? Безопасность данных (скрыть чувствительные данные). Знаете еще применения - пишите в комментах

Официальная документация

Сайт для тестирования SQL

На днях натолкнулся на дикий баг в мускуле

Оказывается что при использовании короткого синтаксиса внешний ключ не создается

create table test (
 id int primary key,
 first_name varchar(20),
 last_name varchar(30)
);

create table ref1 (
 id int primary key,
 test_id int references test(id)
);

В этом примере внешний ключ для поля `test_id` не создаётся

Подробное описание здесь: https://tproger.ru/articles/staryj-bag-v-mysql-istoriya-detektiva/

MySQL 8: short syntax for select all

Начиная с MySQL 8, вы можете использовать довольно короткий запрос для выбора всех данных из таблицы.
Просто используйте table a; вместо SELECT * FROM a; и получить тот же результат:

mysql> table a;
+===+===+
| m | n |
+===+===+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
+---+---+

mysql> table b;
+===+===+
| m | n |
+===+===+
| 1 | 2 |
| 1 | 3 |
| 3 | 4 |
+---+---+

mysql> table c;
+===+===+
| m | n |
+===+===+
| 1 | 3 |
| 1 | 3 |
| 3 | 4 |
+---+---+

Этот синтаксис также можно использовать с оператором UNION и INTERSECT

mysql> table a union all table b union all table c;
+===+===+
| m | n |
+===+===+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 1 | 2 |
| 1 | 3 |
| 3 | 4 |
| 1 | 3 |
| 1 | 3 |
| 3 | 4 |
+---+---+

mysql> table a intersect table b;
+===+===+
| m | n |
+===+===+
| 1 | 2 |
| 3 | 4 |
+---+---+

Вы можете проверить этот код на SQLize.online