За последние 24 часа нас посетил 5331 программист и 446 роботов. Сейчас ищут 97 программистов ...

Поиск в двух таблицах

Тема в разделе "MySQL", создана пользователем ivanisoff, 12 фев 2018.

  1. ivanisoff

    ivanisoff Новичок

    С нами с:
    12 фев 2018
    Сообщения:
    47
    Симпатии:
    0
    Добрый день. Можете помочь оптимизировать как то запрос
    Код (Text):
    1. SELECT apache_logs.*
    2. FROM apache_logs
    3. WHERE (apache_logs.user_agent In (SELECT bad_user_agents.name FROM bad_user_agents))
    4. GROUP BY ip


    Логика такая. Есть две таблицы.
    Первая два поля ид и user_agent
    Вторая таблица ид, user_agent и другие данные
    Надо проверить каждую строку из второй таблицы(столбец user_agent) на совпадение любому из значений списка столбца "user_agent" в таблице один

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

    Поидее там быстро должно все работать, в первой таблице список примерно из 150 user_agent всего. И каждую строку с столбцом "user_agent" из таблицы два, а их всего 25000, надо проверить на совпадение любому из этого списка таблицы один. Запрос выполняется секунд 80. Долго слишком. Можете помочь?

    Связать по имени таблицы я не могу, так как список user_agent в таблице 2 намного больше чем в таблице один, и он постоянно увеличивается там, имею ввиду количество уникальных.
     
  2. rewuxiin

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

    С нами с:
    17 апр 2012
    Сообщения:
    452
    Симпатии:
    53
  3. ADSoft

    ADSoft Старожил

    С нами с:
    12 мар 2007
    Сообщения:
    1.071
    Симпатии:
    230
    Адрес:
    Татарстан
    если б не могли - у вас бы и запроса не было....

    наверное так?
    Код (Text):
    1. SELECT logs.*
    2. FROM apache_logs AS logs
    3. LEFT JOIN bad_user_agents AS bad ON bad.name=logs.user_agent
    4. GROUP BY logs.ip
    Ну и для скорости соответствующие поля таблиц сделать индексами
     
  4. ivanisoff

    ivanisoff Новичок

    С нами с:
    12 фев 2018
    Сообщения:
    47
    Симпатии:
    0
    Такой подход я тоже делал, прироста скорости не получил увы..


    Как я могу сделать соответствующие поля индексами? Я понимаю что так будет быстрее, но у меня нет таблицы "справочника" В таблице 1 один список, в таблице 2 другой список и он непостоянен, он увеличивается постоянно, нет же фиксированного списка user_agent, где бы просто индексы ставил что в первой, что во второй таблице, или я чего то не так понимаю?
     
  5. ivanisoff

    ivanisoff Новичок

    С нами с:
    12 фев 2018
    Сообщения:
    47
    Симпатии:
    0
    Хотя запрос я делал так
    Код (Text):
    1. SELECT apache_logs.*
    2. FROM bad_user_agents
    3. LEFT JOIN apache_logs ON (bad_user_agents.name = apache_logs.user_agent)
    4. WHERE apache_logs.user_agent IS NOT null
    5. GROUP by ip
    И по времени он идентичен запросу
    Код (Text):
    1. SELECT apache_logs.*
    2. FROM apache_logs
    3. WHERE (apache_logs.user_agent In (SELECT bad_user_agents.name FROM bad_user_agents))
    4. GROUP BY ip
    Единственное быстрее работает если уберу из первого запроса условие WHERE apache_logs.user_agent IS NOT null, то есть получается такой запрос
    Код (Text):
    1. SELECT apache_logs.*
    2. FROM bad_user_agents
    3. LEFT JOIN apache_logs ON (bad_user_agents.name = apache_logs.user_agent)
    4. GROUP by ip
    Он работает в 2-2,5 раза быстрее примерно, это уже хорошо, но первая строка null идет, не пойму почему, есть предположения почему это может быть?
     
  6. ADSoft

    ADSoft Старожил

    С нами с:
    12 мар 2007
    Сообщения:
    1.071
    Симпатии:
    230
    Адрес:
    Татарстан
    как уж нет, bad_user_agents в вашем случае
    сделайте указанные поля индексами и все .... при добавлении новых значений в табл - индекс будет перестроен (правдо это немного увеличивает время на добавление.... но на вашем кол-ве это вообще незаметно будет)

    потому что она есть ))) почему бы ей не выдаться в результатах ))))
    добавьте тогда к запросу свое условие
    Код (Text):
    1. WHERE logs.user_agent IS NOT null
     
  7. ivanisoff

    ivanisoff Новичок

    С нами с:
    12 фев 2018
    Сообщения:
    47
    Симпатии:
    0
    bad_user_agents это не справочник, здесь лишь часть записей лежит, тут например 150 записей так таблица 1, а в таблице 2 уник записей может вообще быть 1000, и как быть тогда, где есть из таблицы один в таблице два я поставлю индекс хорошо, а в другие поля как быть? поэтому и проблема с индексом.

    Про WHERE logs.user_agent IS NOT null писал что из-за этой проверки тормозится время сильно.

    А вот по null строчке, как бы вычислить где это зарыто, ибо пересмотрел уже по всем параметрам, нигде нет с null не одного поля
     
  8. ADSoft

    ADSoft Старожил

    С нами с:
    12 мар 2007
    Сообщения:
    1.071
    Симпатии:
    230
    Адрес:
    Татарстан
    ну хотите советов - не делайте,
    "справочник" - имелось ввиду в некотором роде
    про индексы похоже у вас вообще понятия нет, что это и для чего, просто поверьте - и добавьте индексы как я указал
    а null - потому что у вас группировка по ip, а надо бы конечно по logs.user_agent
    то есть справедливо - что для некоторых ip нет каких-то юзерагентов вот и null
    а вообще в любом случае 25000 и 1500 не такие цифры чтоб запросам виснуть... нужно смотреть структуру таблиц, типы полей, и индексы. индексы и еще раз индексы
     
  9. ivanisoff

    ivanisoff Новичок

    С нами с:
    12 фев 2018
    Сообщения:
    47
    Симпатии:
    0
    prnt.sc/ie27zo

    Нарисовал мое понимание индексов, разве я не верно понимаю работу?

    Давно бы добавил но как я это сделаю? на картинке второй вариант, когда у меня нет всех возможных вариантов
     
  10. ivanisoff

    ivanisoff Новичок

    С нами с:
    12 фев 2018
    Сообщения:
    47
    Симпатии:
    0
    А вот скрин с null https://prnt.sc/ie2i5j
    Странно как то появлятся она на разных данных всегда вот так на первом месте
     
  11. ADSoft

    ADSoft Старожил

    С нами с:
    12 мар 2007
    Сообщения:
    1.071
    Симпатии:
    230
    Адрес:
    Татарстан
    по моему вы индексы путаете с внешними ключами (FK)
    попробуйте почитать

    в phpmyadmin например - на поле name одной таблицы добавьnе в индекс во второй по user_agents то же самое и все!
    предыдущем посте сказал же - что группировка неверна - вот и null
     
  12. ivanisoff

    ivanisoff Новичок

    С нами с:
    12 фев 2018
    Сообщения:
    47
    Симпатии:
    0
    https://prnt.sc/ie3j46 так??, верно теперь?

    И про нулл, мне нужно найти ip где есть хотя бы раз в запросе с данного ip плохой user agent, поэтому и группирую по ip
     
  13. ADSoft

    ADSoft Старожил

    С нами с:
    12 мар 2007
    Сообщения:
    1.071
    Симпатии:
    230
    Адрес:
    Татарстан
    ну раз сделали - вы должны были почувствовать разницу в скорости... а так - да. Ну еще желательно конечно чтоб поля по размерности совпадали varchar 255 - varchar 255
     
  14. ivanisoff

    ivanisoff Новичок

    С нами с:
    12 фев 2018
    Сообщения:
    47
    Симпатии:
    0
    Есть плюсы, есть минусы, конкретно этот запрос, я оставил с самого первого поста который, стал работать моментально. Вопросов нет. Но другой запрос, массовый insert стал работать в два раза медленнее.
     
  15. ADSoft

    ADSoft Старожил

    С нами с:
    12 мар 2007
    Сообщения:
    1.071
    Симпатии:
    230
    Адрес:
    Татарстан
    ну я говорил что добавление замедлится...
    но не в разы... может добавляете как то не так? )))
     
  16. ivanisoff

    ivanisoff Новичок

    С нами с:
    12 фев 2018
    Сообщения:
    47
    Симпатии:
    0
    https://prnt.sc/ie8v7n вот индексы добавил.
    Как бы есть и плюсы и минусы как раньше говорил, походу выбирать придется что то одно, куда жертвовать время.
    Есть добавление и удаление данных и есть сбор статистики. Дак вот, обработать файл 100мб 350000строк примерно на расчеты(статистику) тратилось 90 секунд у меня, прочитав статью про индексы что вы дали, стало 6-7 секунд - это плюс.

    Но те же 100мб 350000строк например загрузить в таблицу или удалить из нее стало раза в 4 медленнее. Раньше распарсить и загрузить уходило секунд 20 (время везде меряю функцией microtime(1)), сейчас 80. Индексы которые добавил приложил на скрине.

    Есть ли возможность сохранить время на расчет результатов, которое сейчас удалось добиться, но при этом и время вернуть которое раньше было на добавление записей в бд таблицу?
    --- Добавлено ---
    а добавляю записи как в дампе mysql идет, массовый insert, количество подключений к бд минимально, просто запрос длинный идет и все
     
  17. ADSoft

    ADSoft Старожил

    С нами с:
    12 мар 2007
    Сообщения:
    1.071
    Симпатии:
    230
    Адрес:
    Татарстан
    как то вы странно читаете .... или не читаете что-ли?
    я же говорю - нужно еще смотреть как вы файлы обрабатываете и грузите в мускуль

    кстати зачем вы еще там полей в индекс то добавили? Они вроде нигде у вас в запросах и не используются
    --- Добавлено ---
    1. для быстрой загрузки средствами самого MySQL есть http://www.mysql.ru/docs/man/LOAD_DATA.html
    2. ЗА один INSERT можно (и нужно) добавлять сразу группу значений..... например я в некоторых проектах своих использовал группы по 100 записей - оченно увеличивает скорость
    3. Теоретически перед загрузкой можно индексы удалять - загружать - индексы добавлять ..... но не уверен что удаление и создание индексов в сумме с загрузкой будет меньше
     
  18. ivanisoff

    ivanisoff Новичок

    С нами с:
    12 фев 2018
    Сообщения:
    47
    Симпатии:
    0
    1. Прочту завтра статью, спасибо.
    2. Я при тестах обнаружил что вся оптимизация идет в трубу, если количество mysqli_query не минимизировано, то есть если добавлять по одной записи или по несколько, вообще по идее можно запрос делать длины такой select @@max_allowed_packet и его субд должна обработать. Вообщем в принципе делаю так же как и вы написали, может только количество записей за раз больше добавляется.
    3. Это надо опробовать опять же на тестах и проверить, завтра сделаю это.

    А по поводу индексов и запросов - запрос, указанный в первом посте, это лишь один из запросов, проводимых для статистики, и то он лишь составной другого запроса. А индексы все связаны с другими запросами, и там реально прирост скорости налицо стал.

    Добавляю описал в пункте 2 как , а удаляю обычным не мудреным запросом delete from табличка where 1 и все, ничего хитрого.
     
  19. ivanisoff

    ivanisoff Новичок

    С нами с:
    12 фев 2018
    Сообщения:
    47
    Симпатии:
    0
    По скорости, что массовый инсерт
    Код (Text):
    1. INSERT INTO apache_logs (ip,log_time,method_request,page,version,code,size_bytes,url_referer,user_agent) VALUES (''),(''),('');
    что запрос загрузкой файла
    Код (Text):
    1. LOAD DATA INFILE "./test.txt"
    2. INTO TABLE apache_logs
    3. FIELDS TERMINATED BY '||'
    4. (ip,log_time,method_request,page,version,code,size_bytes,url_referer,user_agent);
    Дает одинаковый результат по скорости, что так, что так уходит на 350 000 распарсить каждую на 10 составляющих и добавить в бд уходит 20+- секунд. Важный момент БЕЗ ИНДЕКСОВ.

    С индексами, указанном на скрине https://prnt.sc/ie8v7n на туже самую работу что в первом, что во втором запросе уходит примерно одинаково +-80секунд. Статью вашу прочел, разницу никак не могу получить
     
  20. ivanisoff

    ivanisoff Новичок

    С нами с:
    12 фев 2018
    Сообщения:
    47
    Симпатии:
    0
    В данный момент вижу только такой вариант, использовать конструкцию
    Код (Text):
    1. ALTER TABLE apache_logs DROP INDEX user_agent, DROP INDEX ip_log_time, DROP INDEX ip_code;
    2. DELETE FROM apache_logs WHERE 1;
    3. ALTER TABLE apache_logs ADD INDEX user_agent(user_agent),ADD INDEX ip_log_time(ip,log_time),ADD INDEX ip_code(ip,code);
    Для удаления и добавления данных, как вы писали в 3 пункте своего сообщения, всеравно побыстрее это дело работает. С таким подходом 30 секунд уходит, я все тесты провожу на на одном файле 350 000который. Но блин, 30 секунд это на грани, надо уменьшить как то.
     
  21. artoodetoo

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

    С нами с:
    11 июн 2010
    Сообщения:
    8.673
    Симпатии:
    573
    Адрес:
    из России с любовью
    Ты каждый раз чтоли заново объявляешь идексы? Зачем?!

    Очистить все данные в таблице можно командой TRUNCATE
    --- Добавлено ---
    Познавательно про быструю загрузку данных
    https://dbahire.com/testing-the-fas...and-some-interesting-5-7-performance-results/
    --- Добавлено ---
    https://stackoverflow.com/a/2504211/272885
    --- Добавлено ---
    https://blog.gabriela.io/2016/05/17/fast-data-import-trick/
     
  22. ivanisoff

    ivanisoff Новичок

    С нами с:
    12 фев 2018
    Сообщения:
    47
    Симпатии:
    0
    Вопрос такой, какие бы правильно все таки было создать индексы, вся работа идет по одной таблице, кроме одного подзароса

    Код (Text):
    1. SELECT Y.ip, COUNT(Y.count_all), Z.count_black, Z.count_black_time, Y.count_code, Y.user_agent
    2.     FROM
    3.         (SELECT apache_logs.ip AS ip, COUNT(apache_logs.ip) AS count_all, SUM(CASE WHEN apache_logs.code = '404' THEN 1 ELSE 0 END) AS count_code, X.count_UA AS user_agent
    4.             FROM
    5.                  (SELECT ip, COUNT(user_agent) AS count_UA
    6.                     FROM apache_logs
    7.                     WHERE (apache_logs.user_agent In (SELECT bad_user_agents.name FROM bad_user_agents))
    8.                     GROUP BY ip) AS X
    9.             RIGHT JOIN apache_logs ON (apache_logs.ip = X.ip)
    10.             GROUP BY ip) AS Y
    11.        
    12.     LEFT JOIN
    13.  
    14.         (SELECT B.ip, COUNT(B.count_sequence) AS count_black, SUM(B.count_sequence) AS count_black_time
    15.             FROM
    16.                  (SELECT A.ip, A.count_sequence
    17.                     FROM
    18.                          (SELECT ip, log_time, COUNT(ip) AS count_sequence
    19.                               FROM apache_logs
    20.                               GROUP BY ip, log_time)  AS A
    21.                     WHERE A.count_sequence > 2)  AS B
    22.             GROUP BY B.ip) AS Z
    23.  
    24.     ON (Y.ip = Z.ip)
    25.     GROUP BY Y.ip

    Я создал такие индексы
    Код (Text):
    1. CREATE INDEX user_agent ON apache_logs(user_agent);
    2. CREATE INDEX ip_log_time ON apache_logs(ip,log_time);
    3. CREATE INDEX ip_code ON apache_logs(ip,code);
    Может как то надо по другому? все поля таблицы неуникальные. Запутался я чета уже.

    Вот explain запроса
    https://prnt.sc/if0zy4