Как БЫСТРО вывести несколько млн.строк в txt из mariaDB(mySQL)?

Question

[Андрей]

Есть БД , содержащая в себе 10млн записей в одной таблице, и примерно 1,5 млрд в другой. И еще пара мелких(до 200 записей) таблиц. Это только для разработки, потом будет минимум в 20 раз больше.

Сервер - dedicated i7-7700k, 32GB RAM, SSD 2*256GB(потом добавим еще);

Запрос вида

SELECT DISTINCT acc.email, acc.password
FROM accounts acc JOIN accounts_status st ON acc.id_account=st.account_id
WHERE st.service_id in(1,5,8,7,122,147,80,58,77,60,55,15,22,14,16,78,75) AND st.status = 1

в dbForgeStudio выполняется примерно за 0,2 сек(правда, включен постраничный режим). В консоли mysql - ничего путного не выходит, а если добавить LIMIT 1000000 то вывод происходит за минуту. При использовании INTO OUTFILE - ситуация аналогична.

Как я понимаю, абсолютно большую часть времени занимает не сам запрос, а именно вывод. В конечном итоге вся эта система должна выдавать txt файл, несколько миллионов строк - вполне нормальная длина.

Как реализовать быстрое(в адекватное время) извлечение из базы в текстовый файл?

---ОБНОВЛЕНО 1:47 02-09-2017
Похоже, дело таки в скорости работы самого запроса. В dbForgeStudio переключил Результаты в виде таблицы на Результаты в виде текста и получил уже совсем другое время выполнения запроса. Теперь вопрос в том, как можно оптимизировать время выполнения. Думаю, для этого лучше будет создать новый вопрос.

Comments

[Fixid]

Лог профилирования в студию

[Андрей]

Fixid,

/usr/sbin/mysqld, Version: 10.2.8-MariaDB-log (MariaDB Server). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 170901 21:29:30
# User@Host: admin[admin] @ ***мой_IP***
# Thread_id: 10  Schema: spl_db_test  QC_hit: No
# Query_time: 62.581469  Lock_time: 0.000150  Rows_sent: 0  Rows_examined: 2126288
# Rows_affected: 0
use spl_db_test;
SET timestamp=1504294170;
SELECT DISTINCT acc.email, acc.password <------>INTO OUTFILE '/tempS/s2.txt'
    FROM accounts acc JOIN accounts_status st ON acc.id_account=st.account_id
    WHERE st.service_id in(1,5,8,7,122,147,80,58,77,60,55,15,22,14,16,78,75)  AND st.status = 1;
# Time: 170901 21:30:42
# User@Host: admin[admin] @ ***мой_IP***
# Thread_id: 11  Schema: spl_db_test  QC_hit: No
# Query_time: 59.322831  Lock_time: 0.000163  Rows_sent: 1000000  Rows_examined: 3000798
# Rows_affected: 1000000
SET timestamp=1504294242;
SELECT DISTINCT acc.email, acc.password <------>INTO OUTFILE '/tempS/s3.txt'
    FROM accounts acc JOIN accounts_status st ON acc.id_account=st.account_id
    WHERE st.service_id in(1,5,8,7,122,147,80,58,77,60,55,15,22,14,16,78,75)  AND st.status = 1 
LIMIT 1000000;

Вот, пожалуйста. До этого лог был отключен(знаю что так нельзя). Первый запрос - без LIMIT 1000000, я его прервал после 2х минут выполнения, а второй - с LIMIT 1000000 - выполнился примерно за минуту.

Заранее большое спасибо за ответ, если я предоставил не ту информацию - прошу прощения, не так хорошо знаком с отладкой БД.

[Дмитрий Гаджиев]

а как вы делаете запись в txt файл?

Answer 1

[asd111]

Уверены что тормозит запись, а не сам запрос ?
В вашем случае если средняя длина результата 50 байт, то 1 млн. таких записей это примерно 50 MB т.е. это запишется на SSD за 1 сек.
Сделайте

explain SELECT DISTINCT acc.email, acc.password
FROM accounts acc JOIN accounts_status st ON acc.id_account=st.account_id
WHERE st.service_id in(1,5,8,7,122,147,80,58,77,60,55,15,22,14,16,78,75) AND st.status = 1 LIMIT 100000

возможно проблема в индексах.
Посмотрите индексы SHOW INDEXES FROM accounts, SHOW INDEXES FROM accounts_status
Посмотрите чему равно у вас innodb_buffer_pool_size в конфигах mysql, должно быть 22400M примерно для 32 GB.

Comments

[Андрей]

explain ... результат
SHOW INDEXES FROM accounts
SHOW INDEXES FROM accounts_status
innodb_buffer_pool_size стоит 24G, не думаю что если уменьшить его то сильно что то измениться.

Я думаю что проблема не в скорости записи, а именно в "отдаче" от mariaDB/обработке выдачи из таблицы в формат для записи в файл. Криво как то объяснил.

P.S. я конечно несколько новичок, но не до такой степени что бы не знать про эти параметры и что такое индексы. ;)

[asd111]

Попробуйте

SELECT DISTINCT acc.email, acc.password
FROM accounts acc JOIN accounts_status st ON acc.id_account=st.account_id
WHERE st.service_id in(1,5,8,7,122,147,80,58,77,60,55,15,22,14,16,78,75) AND st.status = 1 LIMIT 100000 IGNORE INDEX (st.service_id, st.status)

Если окажется быстрее, то как временное решение можно попробовать убрать индексы service_id и status в таблице accounts_status.
В целом советую объединить данные две таблицы в одну(добавить данные из accounts_status в accounts) и оставить только primary key на id_accounts, а остальные индексы убрать. Ещё надо будет посмотреть explain запросов чтобы все данные помещались в RAM, если не помещаются то надо будет разбить таблицу на несколько таблиц чтобы данные при запросе помещались в RAM.
У вас тормозит из за того что используется join на двух больших таблицах и это приводит к большому количеству random access к индексам. Даже на SSD последовательное чтение быстрее чем random access и поэтому прочесть всю большую таблицу бывает быстрее чем прочесть рандомно такой запрос c индексированным полем как

st.service_id in(1,5,8,7,122,147,80,58,77,60,55,15,22,14,16,78,75)

.

[Андрей]

asd111, спасибо, скоро попробую.
Что скажете про использование Внешних ключей, и про оптимизацию при помощи временных таблиц(https://rauf.ru/article/optimizaciya-join-s-pomosh... нагуглил это вчера, хочу узнать у специалиста, насколько применимо к моей ситуации.

Также, у меня тип таблиц COMPRESSED - это может оказывать негативное влияние?

Делать такую денормализацию не очень хочется, но похоже придется. Разбить на несколько таблиц - это уже проблема - выборка данных должна происходить по всем имеющимся данным.
При объединении двух таблиц в одну, можно рассчитывать на адекватную производительность на больших(250 млн строк) объемах?

Возможно, стоит использовать другую СУБД? Может быть вообще нереляционную, по типу Хранилища колонок(но не совсем понятно как делать выборки с фильтрацией)?

[Андрей]

asd111, да, без индексов работает быстрее. Честно - большая неожиданность для меня! Отвечая на свой вопрос - Внешние ключи, думаю, будут только мешать.
Сейчас буду пробовать с объедением таблиц в одну, но это займет некоторое время.

[neu3y]

Реляционные СУБД давным-давно уже, чуть ли не при рождении своем, научились искать данные без их реального извлечения.
Тормозит не запрос, не поиск, не отбор.
а именно извлечение.

[asd111]

Андрей, Временную таблицу нужно попробовать. Ещё можно попробовать два запроса вместо запроса с join.

SET @account_ids = (SELECT account_id FROM accounts_status WHERE service_id in(1,5,8,7,122,147,80,58,77,60,55,15,22,14,16,78,75) AND status = 1 IGNORE INDEX (st.service_id, st.status) LIMIT 100000); 
SELECT DISTINCT email, password FROM accounts WHERE id_account IN (@account_ids);

Из колоночных таблиц могу посоветовать решение от yandex - ClickHouse. Там тоже sql для запросов https://clickhouse.yandex/ Для аналитики это конечно лучше чем mysql.