За последние 24 часа нас посетили 20448 программистов и 1092 робота. Сейчас ищет 891 программист ...

Как правильно связать таблицы?

Тема в разделе "MySQL", создана пользователем Dmitriy A. Arteshuk, 14 окт 2014.

  1. Dmitriy A. Arteshuk

    Dmitriy A. Arteshuk Активный пользователь

    С нами с:
    19 янв 2012
    Сообщения:
    2.445
    Симпатии:
    66
    Адрес:
    Зеленоград
    есть 3 таблицы innodb

    1. items => item_id(AI).... остальные поля
    2. cats => id(AI).... остальные поля
    3. i2cat => cat_id(id из второй таблицы), item_id (item_id из первой) - таблица связей

    потом идет выборка

    Код (Text):
    1.  
    2. SELECT items.* FROM i2cat AS i2c LEFT JOIN items AS items ON items.id_item=i2c.item_id WHERE i2c.cat_id=100
    работает оно долго (((

    связать индексом 1 и 3 нельзя, ибо в 1 item_id - AI, во втором item_id нет , т.к. один item_id может принадлежать нескольким id из таблицы 2

    какой может быть выход из ситуации?
     
  2. Ganzal

    Ganzal Суперстар
    Команда форума Модератор

    С нами с:
    15 мар 2007
    Сообщения:
    9.902
    Симпатии:
    969
    таблицы проиндексированы? это же одно из базовых решений связи n-n двух таблиц. насколько долго работает и для какого объема данных?
     
  3. Dmitriy A. Arteshuk

    Dmitriy A. Arteshuk Активный пользователь

    С нами с:
    19 янв 2012
    Сообщения:
    2.445
    Симпатии:
    66
    Адрес:
    Зеленоград
    таблица 1 - индекс по item_id так как он AI (ну плюс еще пару критичных полей)
    таблица 2 - то же самое
    таблица 3 - оба поля индекс

    тут еще вот какая петрушка, таблиц таких (1 2 3) их много, и запрос формируется путем UNION, т.е.
    Код (Text):
    1.  
    2. SELECT items.* FROM i2cat_1 AS i2c LEFT JOIN items_1 AS items ON items.id_item=i2c.item_id WHERE i2c.cat_id=100 UNION
    3. SELECT items.* FROM i2cat_2 AS i2c LEFT JOIN items_2 AS items ON items.id_item=i2c.item_id WHERE i2c.cat_id=101 UNION
    4. SELECT items.* FROM i2cat_3 AS i2c LEFT JOIN items_3 AS items ON items.id_item=i2c.item_id WHERE i2c.cat_id=102
    выбока 20 тысяч позиций занимает порядка 7-9 секунд на сервере 512 метров оперативы, гиг проца

    если все items и все cats свалить в одну таблицу....быстрее будет?

    у меня просто паническая боязнь таблиц в несколько лямов строк (
     
  4. Ganzal

    Ganzal Суперстар
    Команда форума Модератор

    С нами с:
    15 мар 2007
    Сообщения:
    9.902
    Симпатии:
    969
    ну 20тыс позиций тоже разные бывают. можно кортежи по 8 байт тянуть а можно и блюрей-изошники...

    1. explain нам что говорит? ок всё?
    2. посмотреть статус сервера. регулярное обращение к разному набору таких триплетных таблиц (если я правильно понял что есть таблицы 1-2-3, есть 4-5-6, 7-8-9 итд просто из них юнионом тянется общий формат ответа) может на таком слабом сервере просто не попадать в кэш и постоянно приходится читать таблицы с диска. будут расти счетчики соответствующие. ну и explain тоже будет выдавать чтение с диска.
    в принципе если данные не очень большие, то можно и поиграться с тюнингом my.cnf. опять же если это дедик БД. отдадим все 512+свап и пусть крутится. если же нет (данные больше, это не дедик) то железо лучше поменять
     
  5. Dmitriy A. Arteshuk

    Dmitriy A. Arteshuk Активный пользователь

    С нами с:
    19 янв 2012
    Сообщения:
    2.445
    Симпатии:
    66
    Адрес:
    Зеленоград
    Мне почему то кажется, что если 1 и 3 связать внешним ключом, то все будет шоколадно, нет?

    Добавлено спустя 4 минуты 1 секунду:
    бляха...про кеш то я и забыл (((

    Код (Text):
    1. [mysqld]
    2. port        = 3306
    3. socket      = /var/lib/mysql/mysql.sock
    4. skip-locking
    5. key_buffer_size = 16K
    6. max_allowed_packet = 64M
    7. table_open_cache = 4
    8. sort_buffer_size = 64K
    9. read_buffer_size = 256K
    10. read_rnd_buffer_size = 256K
    11. net_buffer_length = 2K
    12. thread_stack = 128K
    ты все правильно понял
    беда в том, что каждый запрос тянет позиции из 1-2-3.....
    второй из 5-6-7...в общем они все время разные

    и повторятся врятли будут, поэтому кеш мне кажется тут не выход (
     
  6. igordata

    igordata Суперстар
    Команда форума Модератор

    С нами с:
    18 мар 2010
    Сообщения:
    32.410
    Симпатии:
    1.768
    дай дамп бд
     
  7. Dmitriy A. Arteshuk

    Dmitriy A. Arteshuk Активный пользователь

    С нами с:
    19 янв 2012
    Сообщения:
    2.445
    Симпатии:
    66
    Адрес:
    Зеленоград
    он пару гигов (((
     
  8. Ganzal

    Ganzal Суперстар
    Команда форума Модератор

    С нами с:
    15 мар 2007
    Сообщения:
    9.902
    Симпатии:
    969
    а какое объединение-то?
    таблица А допустим записи в бложике
    таблица Б допустим метки
    тогда прямая связь А-Б будет давать только 1-1. или если мы будем юзать больше одной метки то нам придется создать нужно количество меток с одинаковым именем чтоб было 1-n
    поэтому создается таблица В в которой и происходит связь n-n. АВ как 1н, ВБ как н1 и так мы и выбрали все метки записи поста.

    а если зафигачить по внешнему ключу то читай вариант АБ то есть много повторных строк в каждой из таблиц. либо же они реально 1-1 относятся и тогда зачем промежуточная таблица?
     
  9. Dmitriy A. Arteshuk

    Dmitriy A. Arteshuk Активный пользователь

    С нами с:
    19 янв 2012
    Сообщения:
    2.445
    Симпатии:
    66
    Адрес:
    Зеленоград
    т.е. вариантов связать их по внешнему ключу нет никаких? (
     
  10. Ganzal

    Ganzal Суперстар
    Команда форума Модератор

    С нами с:
    15 мар 2007
    Сообщения:
    9.902
    Симпатии:
    969
    1. если связи 1-1 то не надо юзать связывающую таблицу.
    2. внешний ключ не добавляет производительности. это инструмент контроля целостности данный. в некоторых случаях он может даже к замедлению привести.
     
  11. Chushkin

    Chushkin Активный пользователь

    С нами с:
    17 дек 2010
    Сообщения:
    1.062
    Симпатии:
    91
    Адрес:
    Мещёра, Центр, Болото N3
    1) Если таблицы вместе с индексами не в буфере, то выборка будет со скоростью чтения данных с диска - от 1-2 до 20 МБ/с в зависимости от.
    2) 20 тысяч - это число записей в таблице связей? Или что?
    3) UNION как правило использует временные таблицы. Если выборка большая, то вр.таблица будет создана на диске, а не в памяти.
    В общем, возможно 512М для движка маловато будет - нужно смотреть отчёты (в SQLyog закладка "Profiler", например).
     
  12. Dmitriy A. Arteshuk

    Dmitriy A. Arteshuk Активный пользователь

    С нами с:
    19 янв 2012
    Сообщения:
    2.445
    Симпатии:
    66
    Адрес:
    Зеленоград
    в том то и дело, что связи 1 к N

    ладно, поставим вопрос по другому: увеличение мощности железки может решить вопрос? допустим что выборка 20 тыс записей - это потолок

    Добавлено спустя 2 минуты 47 секунд:
    это число записей в таблицах 1_N но выбираются они исходя из связей в таблице 3
     
  13. Ganzal

    Ganzal Суперстар
    Команда форума Модератор

    С нами с:
    15 мар 2007
    Сообщения:
    9.902
    Симпатии:
    969
    скорее всего да. узким местом может остаться архитектура базы
     
  14. Chushkin

    Chushkin Активный пользователь

    С нами с:
    17 дек 2010
    Сообщения:
    1.062
    Симпатии:
    91
    Адрес:
    Мещёра, Центр, Болото N3
    Кстати, для INNODB нужно другие параметры настраивать.
     
  15. Dmitriy A. Arteshuk

    Dmitriy A. Arteshuk Активный пользователь

    С нами с:
    19 янв 2012
    Сообщения:
    2.445
    Симпатии:
    66
    Адрес:
    Зеленоград
    вот за это я и боюсь (((

    ладно, завтра перенесу проект на локальную машину, посмотрим что "скажет", дальше будем думать, спасибо всем

    да, что бы почитат на ночь про архитектуру базы, так чтоб не до 5 утра )))

    Добавлено спустя 31 секунду:
    подскажите какие, буду очень признателен
     
  16. Chushkin

    Chushkin Активный пользователь

    С нами с:
    17 дек 2010
    Сообщения:
    1.062
    Симпатии:
    91
    Адрес:
    Мещёра, Центр, Болото N3
    1) Сколько записей в i2cat?
    2) Каковы размеры (в Кбайтах) таблиц? (всех в запросе, вместе с индексами)
    3) Сколько всего UNION в запросе?
     
  17. Dmitriy A. Arteshuk

    Dmitriy A. Arteshuk Активный пользователь

    С нами с:
    19 янв 2012
    Сообщения:
    2.445
    Симпатии:
    66
    Адрес:
    Зеленоград
    1. от 1000 и до 100000
    2. от 1 мб и до 100
    3. от 1 до 1000
     
  18. Chushkin

    Chushkin Активный пользователь

    С нами с:
    17 дек 2010
    Сообщения:
    1.062
    Симпатии:
    91
    Адрес:
    Мещёра, Центр, Болото N3
    innodb_buffer_pool_size
    Остальное оставьте по дефолту для начала, - это уже тонкие настройки, особенно для версии 5.6 и старше.

    Добавлено спустя 1 минуту 40 секунд:
    Ну Вы шутник ;)
    Мы ведь говорим о том запросе, который выполняется 7-9 сек? Или о сферическом коне в вакууме?
     
  19. Ganzal

    Ganzal Суперстар
    Команда форума Модератор

    С нами с:
    15 мар 2007
    Сообщения:
    9.902
    Симпатии:
    969
    1000 юнионов в запросе? что серьезно?
     
  20. Dmitriy A. Arteshuk

    Dmitriy A. Arteshuk Активный пользователь

    С нами с:
    19 янв 2012
    Сообщения:
    2.445
    Симпатии:
    66
    Адрес:
    Зеленоград
    а, конкретно, ща, 5 сек

    1. 72480
    2. ~ 39 Mб
    3. 703

    Добавлено спустя 31 секунду:
    ДА!

    я и говорю что что то не так в королевстве )

    Добавлено спустя 1 минуту 45 секунд:
    я просто не уверен (но и не проверял) что такая выборка из одной таблицы в несколько лямов записей будет быстрее
     
  21. Chushkin

    Chushkin Активный пользователь

    С нами с:
    17 дек 2010
    Сообщения:
    1.062
    Симпатии:
    91
    Адрес:
    Мещёра, Центр, Болото N3
    Кстати 2...
    table_open_cache = 4 это очень мало. Должно быть больше. (см. про Opened_tables)
     
  22. igordata

    igordata Суперстар
    Команда форума Модератор

    С нами с:
    18 мар 2010
    Сообщения:
    32.410
    Симпатии:
    1.768
    иди спат

    Базу дай. пара гигов не деньги в XXI веке же ж.
    откуда взял? =) в одну пихай всё.
     
  23. Chushkin

    Chushkin Активный пользователь

    С нами с:
    17 дек 2010
    Сообщения:
    1.062
    Симпатии:
    91
    Адрес:
    Мещёра, Центр, Болото N3
    703 UNION?! Офигеть!
    Так чего ж Вы хотите? Идёт обработка 700 таблиц каждая по 72 тыс записей, т.е. это эквивалент одной таблицы в 50 млн.записей, плюс ещё связанные таблицы. И общий размер, как я понимаю, таблиц порядка трёх гигов.
    И Вы хотите всё это быстро обрабатывать на 512М оперативке?! Флаг Вам в руки... ;)

    В общем, хотите скоростей, то:
    - 2Г RAM (лучше 4)
    - 3Ггц проца
    - объединить 700 таблиц в одну
    Скорость выборки заметно увеличится.

    Добавлено спустя 3 минуты 57 секунд:
    Поддерживаю. Да и 7-Zip ужмакает раз в 10, если что.
     
  24. igordata

    igordata Суперстар
    Команда форума Модератор

    С нами с:
    18 мар 2010
    Сообщения:
    32.410
    Симпатии:
    1.768
    не, после того как он скзаал, что у него там тыща таблиц, то уже просьба о базе не актуальна. =)

    а про 512 оперативки я не заметил. это просто садизм. у меня на виртуалке рабочей больше. ну так... чтоб не свопила.
     
  25. Chushkin

    Chushkin Активный пользователь

    С нами с:
    17 дек 2010
    Сообщения:
    1.062
    Симпатии:
    91
    Адрес:
    Мещёра, Центр, Болото N3
    Да просто интересно попытать - никогда такую хрень не делал, в 1000 однотипных таблиц.
    Самому генерить такую базу лень конечно, а если бы кто дал, то потестить можно было бы малость.

    Да, кстати, и запрос в 700 UNION тоже нужен, не писать же самому такую портянку :)