ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
25 сообщений из 32, страница 1 из 2
  1  все
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38386298
ruff3d
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ruff3d
Участник
Доброго дня!
Стоит задача выбрать общие значения в 2-х таблицах. С SQL запросом вроде-бы все понятно. Но вот время выборки выглядит пугающе... Я понимаю что 60 млн записей не обрабатываются за 5 минут, но я не знаю как ускорить выборку. Использовал индексирование ключевых полей, но безрезультатно.

запрос такого типа
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
SELECT
  DISTINCT
    kod,
    fio,
    dr,
    town 
FROM people p
   JOIN adr a ON a.kod=p.kod ;



таблица people - 60 000 000 записей, adr - 20 000


индексировал kod, fio, dr (people) и adr.kod

Подскажите, что может быть решением данной проблемы.
Тип таблиц - InnoDB
Сервер Xeon (4 ядра) RAM 4GB (настройки mysql оптимизировал для InnoDB)

З.Ы. выборка проводилась 2 дня, на 3-й я психанул))
...
Рейтинг: 0 / 0
04.09.2013, 11:22:17
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38386309
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Опишите задачу более полно.
Из каких таблиц выбираются поля? Желательно это явно указать в запросе.
Зачем в запросе DISTINCT?
Покажите DDL таблиц и имеющихся индексов и план запроса.
...
Рейтинг: 0 / 0
04.09.2013, 11:27:12
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38386362
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
ruff3d З.Ы. выборка проводилась 2 дня, на 3-й я психанул))
не психуй. Давай таблицы. и индексы.
А для начала выкинь дистинкт - уже на порядок шустрее будет.
...
Рейтинг: 0 / 0
04.09.2013, 11:56:38
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38386382
ruff3d
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ruff3d
Участник
Чувствую погрешность в длине полей (255)))) Но не думаю что проблема только в этом.
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
CREATE TABLE people (
  kod varchar(255) DEFAULT NULL,
  fio(255) DEFAULT NULL,
  dr date DEFAULT NULL,
  town varchar(255) DEFAULT NULL,
  INDEX IDX_people (kod (10), fio (20), dr)
)
ENGINE = INNODB
AVG_ROW_LENGTH = 350
CHARACTER SET utf8
COLLATE utf8_general_ci;


CREATE TABLE adr ( 
  kod varchar(255) DEFAULT NULL,
  ul varchar(255) DEFAULT NULL,
  d varchar(255) DEFAULT NULL,
  kv varchar(255) DEFAULT NULL,
  INDEX IDX_adr (kod (10), ul (20), d (3), kv(3))
)
ENGINE = INNODB
AVG_ROW_LENGTH = 197
CHARACTER SET utf8
COLLATE utf8_general_ci;
...
Рейтинг: 0 / 0
04.09.2013, 12:04:23
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38386465
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
ruff3dзапрос такого типа
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
SELECT
  DISTINCT
    kod,
    fio,
    dr,
    town 
FROM people p
   JOIN adr a ON a.kod=p.kod ;

И что, мускль не плюнул в вас ошибкой 'ambiguous column name "kod"'?
Ну и explain запроса неплохо было бы увидеть.
...
Рейтинг: 0 / 0
04.09.2013, 12:36:17
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38386467
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
ruff3d
Код: sql
1.
2.
3.
  ul varchar(255) DEFAULT NULL,
  d varchar(255) DEFAULT NULL,
  kv varchar(255) DEFAULT NULL,

а вот это (равно как и town varchar(255)) вообще адъ (и израиль)
курить "нормальная форма", срочно!
...
Рейтинг: 0 / 0
04.09.2013, 12:37:42
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38386494
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
Ну и ещё - поскольку у вас индексируются только первые 10 символов, то выполнению запроса этот индекс, наверное, слабо поможет - мусклю всё равно придётся (каждый раз, когда код хотя бы с одной стороны длиннее 10 символов, а может, и вообще всегда) лезть в базу, чтобы сравнить полные значения полей.
...
Рейтинг: 0 / 0
04.09.2013, 12:52:12
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38386712
ruff3d
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ruff3d
Участник
базу просто переконвертировал с другой бд, поэтому ничего не менял.... вот с длиной поля ток погарячился.
индексы как только не ставил и на 20 символов (индексировало 2 дня).
есть еще какие-нибудь замечания?
...
Рейтинг: 0 / 0
04.09.2013, 14:47:08
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38386726
Фотография ScareCrow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ScareCrow
Участник
привести размеры полей в к длине данных.
перейти на CHAR(N) вместо VARCHAR(N)
...
Рейтинг: 0 / 0
04.09.2013, 14:53:38
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38386745
ruff3d
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ruff3d
Участник
ScareCrow,
думаете ускорит?
...
Рейтинг: 0 / 0
04.09.2013, 15:08:22
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38386747
ruff3d
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ruff3d
Участник
ScareCrow,
думаете ускорит?
...
Рейтинг: 0 / 0
04.09.2013, 15:09:55
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38386783
ruff3d
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ruff3d
Участник
и что по поводу дефолтных значений NULL ??? это нормально или без них все-таки быстрее?))
...
Рейтинг: 0 / 0
04.09.2013, 15:25:13
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38386809
transpose
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
transpose
Участник
еще можно varchar(255) c помощью hash преобразовать в числа. Так как различных значений у вас не больше 60 миллионов 10-значные числа это прекрасно покроют. тогда и индексирование будет иметь смысл.
...
Рейтинг: 0 / 0
04.09.2013, 15:38:20
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38387115
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
ruff3dДоброго дня!
Стоит задача выбрать общие значения в 2-х таблицах. С SQL запросом вроде-бы все понятно. Но вот время выборки выглядит пугающе... Я понимаю что 60 млн записей не обрабатываются за 5 минут, но я не знаю как ускорить выборку. Использовал индексирование ключевых полей, но безрезультатно.

запрос такого типа
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
SELECT
  DISTINCT
    kod,
    fio,
    dr,
    town 
FROM people p
   JOIN adr a ON a.kod=p.kod ;



таблица people - 60 000 000 записей, adr - 20 000


индексировал kod, fio, dr (people) и adr.kod

Подскажите, что может быть решением данной проблемы.
Тип таблиц - InnoDB
Сервер Xeon (4 ядра) RAM 4GB (настройки mysql оптимизировал для InnoDB)

З.Ы. выборка проводилась 2 дня, на 3-й я психанул))

1. как и зачем вы будете использовать результат?

2. приводите точный запрос, а не ту подделку которыю вы показали.

3. Вас просили показать EXPLAIN.
...
Рейтинг: 0 / 0
04.09.2013, 18:29:08
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38387137
ruff3d
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ruff3d
Участник
Explain

id | select_type | table | possible_keys | key | key_len | ref | rows | Extra
1 SIMPLE adr ALL IDX_kulik (null) (null) (null) 518
1 SIMPLE people ref IDX_people IDX_people 33 adr.kod 1 Using where
...
Рейтинг: 0 / 0
04.09.2013, 18:51:38
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38387157
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
ruff3dExplain

id | select_type | table | possible_keys | key | key_len | ref | rows | Extra
1 SIMPLE adr ALL IDX_kulik (null) (null) (null) 518
1 SIMPLE people ref IDX_people IDX_people 33 adr.kod 1 Using where

ну експлейн -- вроде нормальный, ничего военного.
60М...20М записей -- ну наверно просто много и долго.
Когда промежуточные результаты не поменшаются в памят'
они вываливают на диск и все становится в 100 раз скучнее.

Самое простое -- нарежте задачу на мелкие кусочки,
скажем в процедуре по 10К людей или по 10К адресов
(лучше по основному ключу от...до......от...до...от...до).

Опятьже, как вы будете использовать эти данные?
может их в промежуточную таблицу загнать или
сразу агрегировать для отчетов...или что?
...
Рейтинг: 0 / 0
04.09.2013, 19:09:13
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38387215
ruff3d
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ruff3d
Участник
javajdbcruff3dExplain

id | select_type | table | possible_keys | key | key_len | ref | rows | Extra
1 SIMPLE adr ALL IDX_adr (null) (null) (null) 518
1 SIMPLE people ref IDX_people IDX_people 33 adr.kod 1 Using where

ну експлейн -- вроде нормальный, ничего военного.
60М...20М записей -- ну наверно просто много и долго.
Когда промежуточные результаты не поменшаются в памят'
они вываливают на диск и все становится в 100 раз скучнее.

Самое простое -- нарежте задачу на мелкие кусочки,
скажем в процедуре по 10К людей или по 10К адресов
(лучше по основному ключу от...до......от...до...от...до).

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

Дело в том что там и так должно получится не более 20K строк.
Выбранные данные я экспортирую в xls файл.
может есть еще какие-нить размышления?
...
Рейтинг: 0 / 0
04.09.2013, 20:39:26
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38387223
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
ruff3dДело в том что там и так должно получится не более 20K строк.т.е. к каждому адресу привязано не более одного человека?
...
Рейтинг: 0 / 0
04.09.2013, 21:05:49
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38387261
Фотография javajdbc
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
javajdbc
Участник
ruff3d,

я бы сделал так:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
SELECT
    kod,
    fio,
    dr,
    town 
FROM
(select * from address limit 1 ) a
   STRAIGHT JOIN people  p ON a.kod=p.kod ;



потом 100, потом 5К... просто посмотреть скорости.
кроме того, как вы грузите в ексел?
сразу из какгото ГУЯ?
может проще будет создать промежуточную таблицу?
а потом выгружать?
...
Рейтинг: 0 / 0
04.09.2013, 22:45:34
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38387425
ruff3d
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ruff3d
Участник
tanglirruff3dДело в том что там и так должно получится не более 20K строк.т.е. к каждому адресу привязано не более одного человека?

там не нормальная форма, просто нашел таблицу с адресами.
...
Рейтинг: 0 / 0
05.09.2013, 09:20:41
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38387430
ruff3d
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ruff3d
Участник
javajdbcruff3d,

я бы сделал так:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
SELECT
    kod,
    fio,
    dr,
    town 
FROM
(select * from address limit 1 ) a
   STRAIGHT JOIN people  p ON a.kod=p.kod ;



потом 100, потом 5К... просто посмотреть скорости.
кроме того, как вы грузите в ексел?
сразу из какгото ГУЯ?
может проще будет создать промежуточную таблицу?
а потом выгружать?

при поиске статического значения типа "p.kod = '1234567890'" ищет секунд 10.
пользуюсь dbForge Studio for MySQL, создает промежуточную таблицу при выборке.
...
Рейтинг: 0 / 0
05.09.2013, 09:27:10
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38387461
ruff3d
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ruff3d
Участник
ruff3djavajdbcruff3d,

я бы сделал так:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
SELECT
    kod,
    fio,
    dr,
    town 
FROM
(select * from address limit 1 ) a
   STRAIGHT JOIN people  p ON a.kod=p.kod ;



потом 100, потом 5К... просто посмотреть скорости.
кроме того, как вы грузите в ексел?
сразу из какгото ГУЯ?
может проще будет создать промежуточную таблицу?
а потом выгружать?

при поиске статического значения типа "p.kod = '1234567890'" ищет секунд 10.
пользуюсь dbForge Studio for MySQL, создает промежуточную таблицу при выборке.

хотя нет, это не представление VIEW, а просто вывод в таблицу))
...
Рейтинг: 0 / 0
05.09.2013, 09:56:44
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38387504
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
ruff3dпри поиске статического значения типа "p.kod = '1234567890'" ищет секунд 10.у вас индексы в память не лезут, других объяснений я не вижу...
PS. задачка: умножить 10 секунд на 60 миллионов записей и прикинуть время выполнения запроса :)
...
Рейтинг: 0 / 0
05.09.2013, 10:25:42
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38387521
Ivan Durak
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Ivan Durak
Участник
ruff3dпри поиске статического значения типа "p.kod = '1234567890'" ищет секунд 10.
пользуюсь dbForge Studio for MySQL, создает промежуточную таблицу при выборке.
код преобразуй в integer
...
Рейтинг: 0 / 0
05.09.2013, 10:38:29
| Ответить | Цитировать | Написать  
Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
    #38387542
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
tanglirruff3dпри поиске статического значения типа "p.kod = '1234567890'" ищет секунд 10.у вас индексы в память не лезут, других объяснений я не вижу...Ну почему же? Может '1234567890' настолько неуникальное значение, что за остатком строки очень много раз приходится лазить в таблицу.
...
Рейтинг: 0 / 0
05.09.2013, 10:49:40
| Ответить | Цитировать | Написать  
25 сообщений из 32, страница 1 из 2
  1  все
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Выборка пересечения между 2-х таблиц (в одной 60 млн записей, во второй 20 тыс. записей)
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&msg=38386712&tid=1836108]:
 0ms
get settings:
 12ms
get forum list:
 17ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 38ms
get topic data:
 11ms
get forum data:
 3ms
get page messages:
 72ms
get tp. blocked users:
 1ms
others: 200ms
total:  360ms
созд. / загр.: 360ms
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]