Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса / 25 сообщений из 25, страница 1 из 1
03.10.2013, 09:59:02
    #38415060
Hett
Hett
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Привет, есть вот такой запрос:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
SELECT DISTINCT f.id
  FROM fh_file f
LEFT JOIN fh_user_file uf
  ON uf.file_id = f.id
  AND uf.deleted = 0 
  AND NOT uf.abuse_status IN (
    :status_user_file,
    :status_user,
    :status_file
  )
INNER JOIN fh_user_file uf2
  ON uf2.file_id = f.id
  AND uf2.date_updated < now() - INTERVAL 7 DAY
  AND (uf2.abuse_status IN (
    :status_user_file,
    :status_user,
    :status_file
  ) OR uf.deleted = 1)
WHERE
  f.flag IS NULL /* != deleted */
  AND uf.id IS NULL



Можно что-то сделать чтобы выполнялся немножко быстрее?
На данный момент 160 секунд он работает (Таблица fh_file = 2 492 909 записей, fh_user_file = 3 392 876)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2013, 10:03:50
    #38415066
Hett
Hett
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
ошибка в запросе

Код: sql
1.
) OR uf2.deleted = 1)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2013, 10:12:25
    #38415080
Hett
Hett
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
В исправленном варианте

140 секунд
 
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
SELECT COUNT(DISTINCT f.id)
  FROM fh_file f
LEFT JOIN fh_user_file uf
  ON uf.file_id = f.id
  AND uf.deleted = 0 
  AND NOT uf.abuse_status IN (
    :status_user_file,
    :status_user,
    :status_file
  )
INNER JOIN fh_user_file uf2
  ON uf2.file_id = f.id
  AND uf2.date_updated < now() - INTERVAL 7 DAY
  AND (uf2.abuse_status IN (
    :status_user_file,
    :status_user,
    :status_file
  ) OR uf2.deleted = 1)
WHERE
  f.flag IS NULL /* != deleted */
  AND uf.id IS NULL



C EXISTS 53 секунды
 
Код: 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.
25.
26.
27.
28.
29.
#EXPLAIN EXTENDED
SELECT DISTINCT f.id
FROM fh_file f
WHERE
  f.flag IS NULL /* != deleted */
  AND EXISTS (
    SELECT * FROM 
      fh_user_file uf2
    WHERE uf2.file_id = f.id
    AND uf2.date_updated < now() - INTERVAL 7 DAY
    AND (uf2.abuse_status IN (
      :status_user_file,
      :status_user,
      :status_file
    ) OR uf2.deleted = 1)
    LIMIT 1
  )
  AND NOT EXISTS (
    SELECT * FROM 
      fh_user_file uf
    WHERE uf.file_id = f.id
    AND uf.deleted = 0 
    AND NOT uf.abuse_status IN (
      :status_user_file,
      :status_user,
      :status_file
    )
    LIMIT 1
  )

...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2013, 10:15:02
    #38415084
Hett
Hett
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
EXPLAIN с EXISTS
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2013, 10:30:17
    #38415113
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Hett,

Модератор: поправил, заменил запрос.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2013, 10:30:41
    #38415114
Hett
Hett
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
спасибо за рефакторинг темы :)
Есть возможность перенести этот запрос на слейв, но если его можно заставить работать секунд за 5-10, то лучше оставить на мастере.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2013, 17:04:42
    #38415881
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Hett,

Ну для начала....

в варианте с ехистс, скорее всего
не нужнен ДИСТИНКТ и
не нужны ЛИМИТ 1

Да и внутру ИФ ЕХИСТС вместо СЕЛЕКТ *
можно написать СЕЛЕЦТ 1
или даже СЕЛЕКТ НУЛЛ....
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2013, 17:20:41
    #38415907
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
...и еше:

у вас два отдельных селекта для ЕХИСТС и НОТ ЕХИСТС.
Оба хорошо работают по индексу user_file_file.
другие условия, похоже , пока не работают.

Попробуйте схлопнуть обе проверки в одну.

Второй вариант -- ускорить отдельные ЕХИСТС / НОТ ЕХИСТС
за счет более сложных индксов (Филе_ид , какойнибудь_флаг)
и даже разбить OR в отдельнуй ЕХИСТС....

Мне кажется схлопывание (первый варинат) будет быстрее
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2013, 18:07:15
    #38415985
ScareCrow
ScareCrow
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
а ЗАЧЕМ для запроса SELECT DISTINCT f.id

впендюрили LEFT JOIN?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2013, 19:21:30
    #38416053
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
ScareCrowа ЗАЧЕМ для запроса SELECT DISTINCT f.id

впендюрили LEFT JOIN?



подумайте еше, мне не удобно вам подсказывать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2013, 20:48:36
    #38416126
ScareCrow
ScareCrow
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
а вы не стесняйтесь
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2013, 21:24:39
    #38416147
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
ScareCrow,

лефт_жоинт там как анти-жоинт,
с условием WHERE ... AND uf.id IS NULL

Ну а дистинкт стоит против умножения на
втором жоинте с UF2.

Вроде как с ексизстами получается быстрее но и
первый вариант вполне функционально верен.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
03.10.2013, 21:45:32
    #38416160
ScareCrow
ScareCrow
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
авторВроде как с ексизстами получается быстрее но и
первый вариант вполне функционально верен
как гарантированно нагнуть сервер, да
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.10.2013, 08:03:56
    #38416297
Hett
Hett
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
javajdbcв варианте с ехистс, скорее всего
не нужнен ДИСТИНКТ и
не нужны ЛИМИТ 1

Да и внутру ИФ ЕХИСТС вместо СЕЛЕКТ *
можно написать СЕЛЕЦТ 1
или даже СЕЛЕКТ НУЛЛ....

Дистинкт конечно да, лишний, а вот на счет лимита, - я как-то раз в подобный запрос решил попробовать добавить LIMIT 1 и он в пару раз быстрее заработал.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.10.2013, 08:20:25
    #38416303
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Hettjavajdbcв варианте с ехистс, скорее всего
не нужнен ДИСТИНКТ и
не нужны ЛИМИТ 1

Да и внутру ИФ ЕХИСТС вместо СЕЛЕКТ *
можно написать СЕЛЕЦТ 1
или даже СЕЛЕКТ НУЛЛ....

Дистинкт конечно да, лишний, а вот на счет лимита, - я как-то раз в подобный запрос решил попробовать добавить LIMIT 1 и он в пару раз быстрее заработал.

1. У вас есть прекрасная возможность за 2-3 минуты
проверить это заново прямо на этом запросе

2. >> Таблица fh_file = 2 492 909 записей, fh_user_file = 3 392 876
А сколько конкретно вылезет по запросу?
Для проверки сделайте:

select count(id)
from
(
SELECT f.id
FROM fh_file f
WHERE
f.flag IS NULL /* != deleted */
AND EXISTS (
SELECT * FROM
........
поставьте сюда весь болшой селект
)

Так вы поймете сколько времени уходит на передачу результата.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.10.2013, 08:52:37
    #38416321
Hett
Hett
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
javajdbcА сколько конкретно вылезет по запросу?
Сейчас накопилось около 50 тысяч, а вообще в зависимости от частоты сбора этого "мусора", несколько тысяч в день.

Попробовал объединить условия

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
SELECT f.id
FROM fh_file f
WHERE
  (
    f.flag IS NULL /* != deleted */
    #OR f.flag = 'corrupted'
  )
  AND NOT EXISTS (
    SELECT * FROM
      fh_user_file uf
    WHERE 
      uf.file_id = f.id
      AND (
        uf.deleted = 0
        AND NOT uf.abuse_status IN (
          :status_user_file,
          :status_user,
          :status_file
        )
      ) OR uf.date_updated > now() - INTERVAL 7 DAY
  )


Но он вешается так совсем, в экслейне показывает

1PRIMARYfrefIX_file_flagIX_file_flag2const1123777100Using where; Using index2DEPENDENT SUBQUERYufALLFK_user_file_file,IX_user_file_abuse_status(null)(null)(null)3352245100Range checked for each record (index map: 0x82)

Без этого OR (для примера, выполняется 47 секунд)

1PRIMARYfrefIX_file_flagIX_file_flag2const1123777100Using where; Using index2DEPENDENT SUBQUERYufrefFK_user_file_file,IDX_fh_user_file_deleted,IX_user_file_abuse_statusFK_user_file_file5db.f.id1100Using where

Есть индекс INDEX IX_user_file_abuse_status (file_id, deleted, abuse_status, date_updated),
но что-то ему не нравится он
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.10.2013, 09:08:53
    #38416339
Hett
Hett
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
64 секунды, но по крайней мере такой вариант мне кажется более адекватным чем первоначальный

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
SELECT f.id
FROM fh_file f
WHERE
  f.flag IS NULL /* != deleted */
  AND NOT EXISTS (
    SELECT * FROM
      fh_user_file uf
    WHERE 
      uf.file_id = f.id
      AND (
        uf.deleted = 0
        AND NOT uf.abuse_status IN (
          :status_user_file,
          :status_user,
          :status_file
        )
      ) 
      UNION ALL
      SELECT 1 FROM
        fh_user_file uf2
        WHERE uf2.file_id = f.id
        AND uf2.date_updated > now() - INTERVAL 7 DAY
  )
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.10.2013, 09:15:26
    #38416346
Hett
Hett
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Для варианта с OR еще вот такие индексы пробовал, но толку мало

1PRIMARYfrefIX_file_flagIX_file_flag2const1123777100Using where; Using index2DEPENDENT SUBQUERYufindex_mergeFK_user_file_file,IDX_fh_user_file,IX_user_file_abuse_status,IDX_fh_user_file2IDX_fh_user_file_deleted,IX_user_file_date_updated1,4(null)2189248100Using sort_union(IDX_fh_user_file_deleted,IX_user_file_date_updated); Range checked for each record (index map: 0x382)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.10.2013, 09:25:16
    #38416352
Hett
Hett
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
SELECT * FROM или SELECT 1 FROM в эзисте на практике ничего не дают, видимо оптимизатор изначально понимает это как и в случае с COUNT(*)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.10.2013, 09:39:48
    #38416359
Hett
Hett
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
не пойму почему не хочет в подзапросах использовать другие индексы, к примеру (file_id, deleted, abuse_status)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.10.2013, 15:28:27
    #38416967
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Hett,

как вариант -- отрабатывайте подзапрос в ехисте
отдельно, на одном ИД
(вернее на одном, потом на другом, брать
из начала-середины-конца разброса ИД)
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
04.10.2013, 15:33:42
    #38416974
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
будьте внимательны: ....AND.... OR....
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
WHERE 
      uf.file_id = f.id
      AND (
        uf.deleted = 0
        AND NOT uf.abuse_status IN (
          :status_user_file,
          :status_user,
          :status_file
        )
      ) OR uf.date_updated > now() - INTERVAL 7 DAY



на всякий случай надо сделать еше одни кавычки:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
WHERE 
      uf.file_id = f.id
      AND 
     (
        (
        uf.deleted = 0
        AND NOT uf.abuse_status IN (
          :status_user_file,
          :status_user,
          :status_file
          )
        ) 
        OR 
        uf.date_updated > now() - INTERVAL 7 DAY
     )



иначе AND пройдет первый а потом ОР
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.10.2013, 14:30:25
    #38418925
Hett
Hett
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
javajdbcна всякий случай надо сделать еше одни кавычки:

Код: sql
1.
2.
SELECT 0 AND 1 OR 1
>1



Получается даже не на всякий случай, а обязательно, походу поэтому и запрос так тормозил :)
С экзистом и юнион, - чем меньше данных остается, тем быстрее выполняется запрос.
А что вообще за состояние Send data, куда что отправляется?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.10.2013, 15:25:34
    #38419024
javajdbc
javajdbc
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
Hett,

http://stackoverflow.com/questions/3638624/mysql-profiler-sending-data
http://dev.mysql.com/doc/refman/5.0/en/general-thread-states.html

т.е. сендинг дата -- чтение данных с диска, обработака и
отправка данных клиенту -- все вместе!

оберните запрос в каунт и оттрейсите
профайл снова:

select count(id)
form
(
большой запрос
)

таким образом уберете время передачи большого количества
данных --- т.е. поймете конкретно скорость самого запроса
без времени доставки результата.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.10.2013, 15:58:56
    #38419081
Hett
Hett
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Оптимизация запроса
javajdbcHett,

http://stackoverflow.com/questions/3638624/mysql-profiler-sending-data
http://dev.mysql.com/doc/refman/5.0/en/general-thread-states.html

т.е. сендинг дата -- чтение данных с диска, обработака и
отправка данных клиенту -- все вместе!

оберните запрос в каунт и оттрейсите
профайл снова:

select count(id)
form
(
большой запрос
)

таким образом уберете время передачи большого количества
данных --- т.е. поймете конкретно скорость самого запроса
без времени доставки результата.
Да такое же время и показывает :)
Доставка это разве не WRITE TO NET? Что-то я найти не могу в офф. доке
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация запроса / 25 сообщений из 25, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение  
Полит. конфиден.  
созд. / загр.: 365ms
Закрыть
start [/forum/topic.php?fid=47&tablet=1&tid=1835932]:
 0ms
get settings:
 10ms
get forum list:
 21ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 29ms
get topic data:
 13ms
get forum data:
 3ms
get page messages:
 68ms
get tp. blocked users:
 2ms
others: 211ms
total:  365ms
x
x
Закрыть


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