Вопрос по выборке данных из БД

Тема в разделе "Базы данных", создана пользователем verfaa, 16 май 2013.

Модераторы: latteo
  1. verfaa

    verfaa

    Регистр.:
    29 янв 2007
    Сообщения:
    371
    Симпатии:
    41
    В поле marks может хранится просто число, например 3 или 5, а могут быть перечислены числа - например 2,4,5,8
    Из формы приходит число, например 5 и нужно сравнить это число с числом(или числами) в поле marks и занести в выборку все строки если числа совпали или число из формы присутствует в числах в поле marks.

    Если в поле marks оказалось одно число, то понятно можно просто написать
    Код:
    .... WHERE marks='".$num_from_form."' AND ...
     
     
     
    
    А если в поле marks оказался набор чисел? Как построить запрос в этом случае?
    И главное что в этом случае запрос был универсальным, т.к. , повторюсь, поле marks может содержать как просто одно число, так и числа перечисленные через запятую. т.е. примерно так
    3
    5,8,9
    2
    1,4,8,9
    4
     
  2. Горбушка

    Горбушка Ищу её...

    Регистр.:
    2 май 2008
    Сообщения:
    3.036
    Симпатии:
    2.037
    Код:
    $cat_list = "1|7";
    Дальше в SQL пихаем такую штуку:
    Код:
    WHERE `marks` regexp '[[:<:]](" . $cat_list . ")[[:>:]]' 
    В базе храним 3 строчки: 1,4,8,9 и 2,4,7 и 8,9
    В $cat_list заносим через | цифры: 1|7
    На выходе получаем: 1,4,8,9 и 2,4,7
     
    verfaa нравится это.
  3. verfaa

    verfaa

    Регистр.:
    29 янв 2007
    Сообщения:
    371
    Симпатии:
    41
    Спасибо, только тут и дали рабочее решение.
    Ещё хотел бы спросить, а насколько "тяжелый" такой запрос для БД?

    И вообще насколько тежелы regexp запросы и насколько оптимально их использовать?

    Таблица, в которой содержится поле marks довольно объемна, сейчас в ней более 28к строк, в будущем планируется сотни тысяч.
     
    Горбушка нравится это.
  4. Горбушка

    Горбушка Ищу её...

    Регистр.:
    2 май 2008
    Сообщения:
    3.036
    Симпатии:
    2.037
    Запрос тяжелее, чем обычные WHERE id = 1, т.к. идёт проверка по регулярному выражению, но MySQL изначально был на это расчитан. regexp - это встроенная функция в MySQL, которая используется в очень большом количестве CMS в нагрузочных местах.

    К примеру, в DLE эта конструкция используется для выборки статей из категории + подкатегорий, а это каждая вторая страница сайта... Так что о нагрузке можно не переживать, сервер выдержит.

    Естественно, нужно задавать LIMIT на выборку, ибо выводить 3к строк - плохая идея... Туалетную бумагу разве что генерировать =)
     
    verfaa нравится это.
  5. verfaa

    verfaa

    Регистр.:
    29 янв 2007
    Сообщения:
    371
    Симпатии:
    41
    Спасибо, буду при необходимости всегда пользоваться регулярками в sql-запросах.
    А на других ресурсах меня очень критиковали за подобное перечисление элементов в поле, мол это совсем неправильно.
    Писали, что нужно
    Я не эксперт в БД, но искренне не понимаю зачем заводить лишнюю таблицу с кучей лишних полей, когда можно так изящно обработать их регуляркой и хранить записи в очень компактном и наглядном виде.
    К тому же заведя лишнюю таблицу скорее всего придется пользоваться LEFT JOIN, что далеко не лучшим образом скажется на производительности, тем более что тип таблиц у меня MyISAM
     
  6. Горбушка

    Горбушка Ищу её...

    Регистр.:
    2 май 2008
    Сообщения:
    3.036
    Симпатии:
    2.037
    На самом деле, они правы... INNER JOIN создаст меньшую нагрузку, чем regexp. И по хорошему, надо бы разделять на 2 таблицы и т.д.

    А теперь смотрим логически:
    Среднестатистический MySQL-сервер тянет 50 000 запросов в секунду (простых). Даже если regexp уменьшит эту цифру в двое-трое, нас это ни каким боком не трогает. Даже при нагрузке в 10 раз больше, это 5000 запросов в секунду, а это 300+ обновлений страницы в секунду при 15 запросах на страницу.

    Да, это не 3000 обновлений, а только 300... Да хоть 100... Это огромнейшие цифры... 18000 обновлений в минуту или 9000 пользователей онлайн (при обновлении страниц раз в 2 секунды)... Если брать хостинг на 1000 сайтов, то это 9 юзеров онлайн на каждом.

    Вот такие циферки... Если у Вас не отказоустойчивый сайт на мего-тонны юзеров онлайн - не заморачивайтесь...

    Для тех, кто в танке: MySQL - самая оптимизированная штука на веб-хостинге. Любой Apache/Nginx умрут куда раньше, чем MySQL... Следом умрёт PHP, а лишь потом сдохнет мускул... Единственное, что выживет - почта, ибо на ней нагрузка не сказывается...
     
    verfaa нравится это.
  7. verfaa

    verfaa

    Регистр.:
    29 янв 2007
    Сообщения:
    371
    Симпатии:
    41
    Спасибо, очень интересные цифры.
    Только вот что понимается под среднестатистическим MySQL-сервером? Это сервер какой конфигурации?
    Вот например хорошо зарекомендовавшие себя серверы серии Intel Xeon E3, тот же Intel Xeon E3-1220 с установленным и грамотно настроенным только MySQL (т.е. без Apache/Nginx) сколько простых запросов в секунду сможет потянуть? Можно как-то спрогнозировать? Там наверное производительность вообще огромная должна быть?
     
    Горбушка нравится это.
  8. Горбушка

    Горбушка Ищу её...

    Регистр.:
    2 май 2008
    Сообщения:
    3.036
    Симпатии:
    2.037
    Спрогнозировать очень сложно. Дело в том, что MySQL по-умолчанию быстрее любого другого ПО. Цифра в 50 000 условная, не помню где была найдена - на одном из форумов, связанных с MySQL. Единственный способ тестирования - в несколько потоков.

    Ещё один нюанс: сложность запросов разная, подключение/отключение сессий тоже отнимает время + есть встроенные операции, которые MySQL иногда выполняет. Есть ещё 100500 нюансов, которые могут повлиять на скорость. Скажем так, я могу написать SQL-запрос, который на 64-ядерном сервере под AMD будет выполняться больше секунды...

    Но могу сказать одно: сервер Intel Core2Quad обслуживает весь хостинг nx0.ru (более 20 тысяч реальных сайтов) и это единственная часть кластера, которая ни разу не имела нагрузку в 100% и ни разу не давала сбой (не путать с mysql-проксёй, на неё жалобы были). И это при учёте, что MySQL никто специально не настраивал - просто установка и интеграция в кластер.

    Ещё для размышления: данные с реально работающего сайта. Без кэша, на показ блога уходит 3 SQL-запроса, которые занимают 0,00032 секунды. Это примерно 9375 запросов в секунду. Сервер не оптимизирован, запросы относительно тяжёлые...
     
    verfaa нравится это.
  9. MegaNuke

    MegaNuke Создатель

    Регистр.:
    26 июл 2007
    Сообщения:
    24
    Симпатии:
    7
    Это подсчет в попугаях. Многое зависит от формата таблицы, например MyISAM (дефолтный engine) не умеет блокировок на уровне строки, а значит пока у вас идет insert/update у вас будет тормозить и select.
    Нюансов на самом деле масса.
     
    Горбушка и verfaa нравится это.
  10. Горбушка

    Горбушка Ищу её...

    Регистр.:
    2 май 2008
    Сообщения:
    3.036
    Симпатии:
    2.037
    MegaNuke, я ж об этом и написал во втором абзаце... Там помимо формата таблицы, влияет содержание таблицы, индексы, есть ли вторичные индексы, по которым идёт сортировка и т.д. Я лишь привёл примерные цифры для среднестатистических и простых случаев, а так же намекнул, что есть запросы не на 1 час работы MySQL-сервера (мой рекорд 8 часов на 1 SQL-запрос - сервер был перенастроен, чтобы не закрывал сессию больше суток).

    А приводил я это всё только с 1 целью: показать наглядно, что не нужно заниматься оптимизацией базы на малонагруженных платформах. Если сайт аля Вконтакте/Яндекс/Твиттер - тогда да, а так - смысл не вижу, всё равно PHP и Apache умрут раньше...

    Если MySQL через работу из дисктопной программы работает на скорости 30к запросов, то PHP уже 9к и виновник не MySQL...
     
    verfaa нравится это.