Почему БД драйвер Python MySQL Connector работает на порядок быстрее чем в других языках?

Question

[Spooky 2020]

Тестировал различные технологии для автоматизации вставки данных в БД, при таких условиях:
- система хранения MyISAM
- СУБД MySQL 8/MariaDB 10.4
- Вставка 33 000 строк в 5 полей
- Код на C#/PHP/Node/Python
- запросы как подготовленные, так и обычная прямая вставка статичных значений
- запрос вида

INSERT INTO tablename (fieldA, fieldB, fieldC, fieldD, fieldE) VALUES (a, b, c, d, e)

Таблица

CREATE TABLE `zip_code` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `zip` CHAR(5) NOT NULL,
    `city` VARCHAR(50) NOT NULL,
    `stid` CHAR(2) NOT NULL,
    `state` VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8_general_ci'
ENGINE=MyISAM
;

Результат для C#/PHP/Node - 14-15 (четырнадцать - пятнадцать) секунд.
Результат для Python - 1.5 (полторы) секунды!

Откуда такая разница в скорости вставки?
В пайтоне я использовал cursor.executemany() передавая массив с 1000 строк.
В PHP (PDO) я просмотрел все опции, но ничего для большей оптимизации там не подошло.
C#/Noda - там я использовал как указано в доках, каких то дополнительных опций я не нашел.

Какие такие особые механизмы работают в Python MySQL Connector (от Oracle) что позволяют
уделать все остальное? Что немного обидно - ведь я так верил в PHP и компилируемый C#.

Comments

[Владимир Коротенко]

Зря не верите в C#
у меня где то порядка 30-40 миллионов записей вставляются за 3 часа (15-20 полей).
~ 33 тысячи записей в секунду

Вот примеры
https://github.com/vkorotenko/FiasServer

[Spooky 2020]

Владимир Коротенко, Да после создания мульти-ров вставки все более-менее работает одинаково ~ 0.37 секунд.
Я даже ноду снова выкопал, что бы проверить. Ну только пайтон, как обычно, теперь снова сосет по скорости :)

[Владимир Коротенко]

Spooky 2020, питон и php это все же языки интерпретируемые. Так что выбирайте молоток по гвоздям

[Spooky 2020]

Владимир Коротенко,
Да я эту хрень написал хз когда, что бы заливать данные из CSV в тестовую БД, когда сиквел учил.
Так что скриптовые языки тут как раз по месту. Там кода то примерно 100 строк.

[Владимир Коротенко]

Spooky 2020, для csv есть специальные утилиты, быстрей их не найдёте при всём желании. Что то типа builkinsert

Answer 1

[galaxy]

Навскидку: cursor.executemany() соединяет запросы в один большой INSERT. Остальные драйверы сами этого, видимо, не умеют.

Думаю, если вы руками соберете multi-row INSERT, то на то примерно и выйдет:

INSERT INTO tablename (fieldA, fieldB, fieldC, fieldD, fieldE)
VALUES (a, b, c, d, e),
(a1, b1, c1, d1, e1),
(a2, b2, c2, d2, e2),
...

Comments

[Spooky 2020]

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

Если вы руками собирали подобное - как решили это в случае подготовленного запроса?
Так же сделать не получится:

INSERT INTO tablename (fieldA, fieldB, fieldC, fieldD, fieldE)
VALUES (?, ?, ?, ?, ?),
(?, ?, ?, ?, ?),
(?, ?, ?, ?, ?)

или так:

INSERT INTO tablename (fieldA, fieldB, fieldC, fieldD, fieldE)
VALUES ? ? ?

[galaxy]

Если вы руками собирали подобное - как решили это в случае подготовленного запроса?

А что там решать?

$sql = "INSERT INTO tablename (fieldA, fieldB, fieldC, fieldD, fieldE) VALUES " . implode(",", array_fill(0, $N, "(?,?,?,?,?)"));
$stmt = $db->prepare($sql);
$stmt->execute($values);

Подбираете $N, нарезаете на куски $values.

[Spooky 2020]

galaxy, А подобное правильно сопоставит плейсхолдеры и множество данных (values)?
Как они должны быть представлены - просто как цепочка [a, b, c, d, e, a1, b1, c1, d1, e1, a2, b2, c2, d2, e2]
и они подставятся просто по количеству (главное что бы шли в правильном порядке), или нужно передавать
как [[a, b, c, d, e,], [a1, b1, c1, d1, e1], [a2, b2, c2, d2, e2]]?

[galaxy]

Сплошным списком. execute() же пофиг, что это разные row - он просто сопоставляет очередной вопросительный знак очередному параметру.

Answer 2

[index0h]

1k строк отдельными инсертами специалльно вставляют обычно когда производительность по важности где то в далёком и светлом будущем, либо от недостатка опыта.

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

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

Comments

[Spooky 2020]

Вы не поняли или я неясно выразился - массив с 1000 строк - это как раз пак данных на одну вставку.
Строк всего 33к. А вот про то как это можно оптимизировать с помощью оборачивания в транзакцию
для myisam я бы с удовольствием почитал. Но ответить/возразить я смогу уже только завтра.

[index0h]

Spooky 2020, с myisam действительно проблема. Тут только писать один множественный инсерт.
Либо перейти на нормальный движок)))