Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / MySQL Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Как ускорить сравнение огромных таблиц  [new]
mordegar
Member

Откуда:
Сообщений: 24
Привет! У меня есть 2 таблицы, одна на 300млн. строк (ТАБ1) и вторая на 20млн. строк (ТАБ2)

Я написал команду по нахождению совпадений:
select * from tab1, tab2
where tab1 = tab2

В обеих таблицах по одной колонке с данными типа "hC98hvW374hoEd7V8d", при создании таблиц в настройках ставил маркеры NN и Binar, формат VARCHAR (100).

Сравнение запустилось, но вот уже прошло больше 1 часа, но процесс не заканчивается, и хз когда закончится. И при чем ресурсы не затрачиваются, ОЗУ свободно ещё 30гб, проц холодный, нагрузка 10-17%, 2 gtx1080ti вообще прохлаждаются на 0% загрузки.
В общем как ускорить процесс, может можно как-то настроить чтобы ресурсов больше затрачивалось? Может есть у кого какие идеи?
Потому что скорость слишком мала, файл на 20млн. строк тестовый, закинул посмотреть найдет ли три заранее спрятанных совпадения. А так на очереди файл под 1 МЛРД. строк, но с такой скоростью пока что это не имеет смысла.
14 сен 18, 10:19    [21674423]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
Melkij
Member

Откуда: Санкт-Петербург
Сообщений: 594
mordegar
2 gtx1080ti вообще прохлаждаются на 0% загрузки

GPU-то тут при чём?

mordegar
проц холодный, нагрузка 10-17%

Суммарно по ядрам? Запрос-то однопоточный.

mordegar
ОЗУ свободно ещё 30гб

А базе в конфиге объяснили, что ей можно этим пользоваться?

Ну и что важнее - индексы-то есть? mysql умеет только nested loops для join, что без индекса вечности подобно, на каждую строку левой таблицы перебирать всю правую таблицу.
14 сен 18, 10:27    [21674430]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
mordegar
Member

Откуда:
Сообщений: 24
Melkij
на каждую строку левой таблицы перебирать всю правую таблицу.


Мне нужно чтобы каждую строку из файла 20млн. строк проверил на совпадения в файле с 300 млн. строк.
Как меня уверяли, что база sql легко с этим справится за пару минут, и ничего там настраивать не надо особо, что такое ключи и прочее я хз, мне сказали поставить флажки и выбрать варчар (100), дальше типа по команде пойдет сравнение с богоподобной скоростью.
14 сен 18, 10:36    [21674442]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
mordegar
Member

Откуда:
Сообщений: 24
не ключи а индексы
14 сен 18, 10:37    [21674443]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
mordegar
Member

Откуда:
Сообщений: 24
как и куда эти индексы ставить в workbenchе?
14 сен 18, 10:47    [21674459]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
полудух
Member

Откуда: планета орков, г.Зверополис
Сообщений: 328
mordegar
как и куда эти индексы ставить в workbenchе?

создали таблицы, залили данные, прописали индексы
именно в таком порядке
заливать в таблицу с индексами в разы дольше
14 сен 18, 10:55    [21674467]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
mordegar
Member

Откуда:
Сообщений: 24
Для обеих таблиц нужно делать индексы?
14 сен 18, 11:00    [21674473]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 18093
mordegar
Я написал команду по нахождению совпадений:
select * from tab1, tab2
  where tab1 = tab2

Должно быть
select * from tab1, tab2
  where tab1.field1 = tab2.field2


mordegar
Для обеих таблиц нужно делать индексы?
Конечно.
14 сен 18, 11:01    [21674476]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
mordegar
Member

Откуда:
Сообщений: 24
Akina,
а индексы уникальные ставить? У меня все значения в строках уникальные, в рамках одной таблицы совпадений нет.
14 сен 18, 11:09    [21674485]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 18093
mordegar
индексы уникальные ставить?
Если модель требует уникальности - да, иначе лучше не надо...
14 сен 18, 12:12    [21674576]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
miksoft
Member

Откуда:
Сообщений: 37076
mordegar
Для обеих таблиц нужно делать индексы?
я бы сделал только у большей таблицы.
И в запросе меньшую таблицу сделал ведущей, а большую ведомой.
14 сен 18, 12:50    [21674619]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
mordegar
Member

Откуда:
Сообщений: 24
miksoft
я бы сделал только у большей таблицы.
И в запросе меньшую таблицу сделал ведущей, а большую ведомой.


А это как сделать, я имею ввиду ведущую или ведомую. Большую таблицу поставил на процесс индексирования, вот уже час идет.
14 сен 18, 12:59    [21674627]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 18093
mordegar, а каково ожидаемое количество записей из этого запроса?
14 сен 18, 13:31    [21674672]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
mordegar
Member

Откуда:
Сообщений: 24
Akina,

ровно 3, специально их туда внедрил для теста.
14 сен 18, 13:58    [21674725]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
mordegar
Member

Откуда:
Сообщений: 24
Пока ещё идет индексация большой таблицы. В принципе мне без разницы каким способом все это делать. Мною на другом ресурсе был поставлен вопрос о том, с помощью каких ресурсов можно сделать перебор на совпадения в текстовых файлах от 30 до 100 Гб веса, мне посоветовали SQL, поэтому данный способ и проверяю. Мне в целом и Excel подошел бы по задаче, но у него лимит 1 млн. строк, это где то 40Мб.
Если с sql не получится, даже хз какие ещё варианты использовать.
14 сен 18, 14:05    [21674739]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
mordegar
Member

Откуда:
Сообщений: 24
Т.е. для ясности, у меня есть файл с 300 млн. значений - базовые, т.е. они в стоке будут всегда. А есть постоянно пополняющийся файл, он может в конечном виде и 1 и 2 млрд. значений иметь, но это не суть, главное своевременно, можно даже по частям, эти значения проверять на совпадения в стоковом файле. И вроде задача то не самая сложная по структуре, но как на практике оказалось, не то что мощностей не хватает, а именно искусственные везде какие-то лимиты, ограничения, и вот так просто не получается.
14 сен 18, 14:11    [21674748]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
miksoft
Member

Откуда:
Сообщений: 37076
mordegar
miksoft
я бы сделал только у большей таблицы.
И в запросе меньшую таблицу сделал ведущей, а большую ведомой.


А это как сделать, я имею ввиду ведущую или ведомую. Большую таблицу поставил на процесс индексирования, вот уже час идет.
Если индекс будет только один, то оптимизатор сам определит таблицу с индексом в ведомые.
Проверить можно в плане запроса.
Если план оптимизатора не устраивает, то порядок соединения таблиц можно указать в запросе явно.
14 сен 18, 14:15    [21674757]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
Akina
Member

Откуда: Зеленоград, Москва, Россия
Сообщений: 18093
mordegar
В обеих таблицах по одной колонке с данными типа "hC98hvW374hoEd7V8d"
Это действительные данные, или некая хитрожопая контрольная сумма?

В теории для такого типа запроса на таких объёмах данных можно создать дополнительное хранимое вычисляемое поле хэша, имеющего целочисленный тип, его проиндексировать, и по нему связывать (вернее, связывать по hash(t1f1)=hash(t2.f2) and t1.f1=t2.f2). Но вот незадача - в MySQL есть либо CRC32, чего маловато на таких объёмах, либо MD5, который, наоборот, великоват. А CRC64, который тут был бы в самый раз - увы... хотя никто не запрещает взять, к примеру, первую (или вторую) половину MD5 и преобразовать его в BIGINT. Вероятность коллизии будет достаточно низкой.
14 сен 18, 14:23    [21674768]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
mordegar
Member

Откуда:
Сообщений: 24
Akina,

Это действительные данные
14 сен 18, 14:30    [21674774]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
NIK.2017
Member

Откуда:
Сообщений: 8
Приветствую.
Если исходная информация - текстовые файлы, и проверку надо сделать один раз, возможно, окажется проще:
cat file1 file2 | sort | uniq -d
14 сен 18, 22:10    [21675163]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
miksoft
Member

Откуда:
Сообщений: 37076
NIK.2017
Приветствую.
Если исходная информация - текстовые файлы, и проверку надо сделать один раз, возможно, окажется проще:
cat file1 file2 | sort | uniq -d
Соединять нужно по одному полю, а не по всей строке, насколько я понял задачу.
14 сен 18, 22:25    [21675169]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
mordegar
Member

Откуда:
Сообщений: 24
miksoft
Соединять нужно по одному полю, а не по всей строке, насколько я понял задачу.


Да, но пока жду что получится с индексом, уже 12 часов пыхтит, вроде недолго осталось но места конечно он мне ппц как занял в ходе данной процедуры. Завтра отпишусь, что получится.
14 сен 18, 23:46    [21675254]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
vkle
Member

Откуда: Самара
Сообщений: 14197
Если правильно понял, каждое поле имеет уникальный индекс (ключевое?), а значит, в принципе, вполне может сработать сравнение файлов, состоящих из одного этого поля.

Другой вопрос - алгоритм. Каждое с каждым сравнивать - реально долго.

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

Тут, скорее, вопрос реализации. Возможно СУБД не самое подходящее средство для такой задачи, хотя, отсортированную выгрузку по каждому полю вполне можно сделать.
14 сен 18, 23:54    [21675264]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
miksoft
Member

Откуда:
Сообщений: 37076
mordegar,

Покажите DDL таблиц и индекса, который пытаетесь создать.
Если поле для соединения уникально, то лучше было бы сначала его сделать первичным ключом, а потом данные в таблицу заливать.
15 сен 18, 00:32    [21675309]     Ответить | Цитировать Сообщить модератору
 Re: Как ускорить сравнение огромных таблиц  [new]
полудух
Member

Откуда: планета орков, г.Зверополис
Сообщений: 328
mordegar
В обеих таблицах по одной колонке с данными типа "hC98hvW374hoEd7V8d", при создании таблиц в настройках ставил маркеры NN и Binar, формат VARCHAR (100).

а они что, разных размеров идут?
и вы конечно же делаете индекс по всей длине?
там 10 чаров хватит
15 сен 18, 02:18    [21675354]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / MySQL Ответить