ReSQL.ru
     
powered by simpleCommunicator - 2.0.61     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация индекса
25 сообщений из 36, страница 1 из 2
  1  все
Оптимизация индекса
    #38934524
Фотография Hett
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hett
Участник
Привет,
можно ли что-то сделать в данном случае:

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
INSERT INTO tmp_analytics
SELECT
  analytics.user_file_id,
  analytics.is_deleted,
  IFNULL(SUM(analytics.paids_sum), 0) AS paids_sum,
  IFNULL(SUM(analytics.paids_count), 0) AS paids_count,
  IFNULL(SUM(analytics.requests), 0) AS requests,
  IFNULL(SUM(analytics.downloads), 0) AS downloads
FROM fh_analytics_file analytics FORCE INDEX (IDX_fh_analytics_file3)
WHERE analytics.user_id = '42'
AND analytics.date >= '2014-03-14'
AND analytics.date < '2015-04-13' + INTERVAL 1 DAY
GROUP BY analytics.user_file_id



Пробовал индексы

Код: sql
1.
2.
3.
4.
5.
6.
  INDEX IDX_fh_analytics_file__user_id__user_file_id__date (user_id, user_file_id, date),
  INDEX IDX_fh_analytics_file3 (user_file_id, user_id, date),
  INDEX IDX_user_file_id (user_file_id (6)),
  INDEX my_table__idx (date, user_id),
  INDEX UK_fh_analytics_file1 (user_id, date, user_file_id),
  INDEX UK_fh_analytics_file2 (date, user_id, user_file_id)



Самый оптимальный IDX_fh_analytics_file__user_id__user_file_id__date
отрабатывает за 10 секунд, остальные за 40+
смущает using where в эксплейне

Код: sql
1.
2.
3.
4.
SELECT COUNT(*) FROM fh_analytics_file analytics
  WHERE analytics.user_id = '42'
AND analytics.date >= '2014-03-14'
AND analytics.date < '2015-04-13' + INTERVAL 1 DAY;



447681

SELECT COUNT(*) FROm tmp_analytics ta;
119011
...
Рейтинг: 0 / 0
13.04.2015, 10:24:30
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934531
Фотография Hett
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hett
Участник
Вот так

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
INSERT INTO tmp_analytics
SELECT
  analytics.user_file_id,
  analytics.is_deleted,
  IFNULL(SUM(analytics.paids_sum), 0) AS paids_sum,
  IFNULL(SUM(analytics.paids_count), 0) AS paids_count,
  IFNULL(SUM(analytics.requests), 0) AS requests,
  IFNULL(SUM(analytics.downloads), 0) AS downloads
FROM fh_analytics_file analytics FORCE INDEX (IDX_fh_analytics_file__user_id__user_file_id__date )
WHERE analytics.user_id = '42'
#AND analytics.date >= '2014-03-14'
#AND analytics.date < '2015-04-13' + INTERVAL 1 DAY
GROUP BY analytics.user_file_id;



запрос отрабатывает за 2 секунды, что вполне удовлетворяет, но фильтр по дате тоже необходим
...
Рейтинг: 0 / 0
13.04.2015, 10:31:14
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934535
Фотография Hett
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hett
Участник
Хотя нет, вру, что с датой, что без даты, одинаково работает. Не на той базе запустил просто...
...
Рейтинг: 0 / 0
13.04.2015, 10:35:20
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934537
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
Hettсмущает using where в эксплейнеавторUsing where

A WHERE clause is used to restrict which rows to match against the next table or send to the client. Unless you specifically intend to fetch or examine all rows from the table, you may have something wrong in your query if the Extra value is not Using where and the table join type is ALL or index. Even if you are using an index for all parts of a WHERE clause, you may see Using where if the column can be NULL.
...
Рейтинг: 0 / 0
13.04.2015, 10:36:41
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934538
Фотография Hett
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hett
Участник
user_file_id, date, user_id - NOT NULL
как понимаю он индекс не может этот использовать для фильтрации по всем критериям, к тому же там 2 отсечения (или как их назвать)
...
Рейтинг: 0 / 0
13.04.2015, 10:40:02
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934543
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
Hett,

а там только using where? using index дальше нет?
...
Рейтинг: 0 / 0
13.04.2015, 10:45:19
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934545
Фотография Hett
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hett
Участник
неа, на картинке в первом посте эксплейн по этому индексу
...
Рейтинг: 0 / 0
13.04.2015, 10:51:38
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934585
Фотография ScareCrow
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
ScareCrow
Участник
а ничего что не все поля участвуют в группировке?
...
Рейтинг: 0 / 0
13.04.2015, 11:29:48
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934670
Фотография Hett
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hett
Участник
все и не надо...
...
Рейтинг: 0 / 0
13.04.2015, 12:16:46
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934693
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
Hett,

эти индексы должны были бы помогать (любой из них):

INDEX my_table__idx (date, user_id),
INDEX UK_fh_analytics_file1 (user_id, date, user_file_id),
INDEX UK_fh_analytics_file2 (date, user_id, user_file_id)

НО

с 447681 строками для агрегирования всё равно будет тяжко, это много.

Т.е. индексы всё равно нужны (один из трёх выше )
чтобы из 9 млн отбирать поллимона, но это всё равно много записей.

ЕЩё:
сколько уникальных analytics.user_file_id в выборке ? если много -- вообще труба.

У тебя нет группировки по analytics.is_deleted , а надо бы...
...
Рейтинг: 0 / 0
13.04.2015, 12:29:19
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934702
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
Hettuser_file_id, date, user_id - NOT NULL
как понимаю он индекс не может этот использовать для фильтрации по всем критериям, к тому же там 2 отсечения (или как их назвать)

Может.
Но видимо просто по поводу того, что 1/20 примерно от таблицы выбирается, сервер не хочет использовать индекс.
Причём это может быть как хорошо, так и плохо -- надо смотреть реальные косты выполнения.
Если индекс таки выгоден -- форсить его хинтами.
...
Рейтинг: 0 / 0
13.04.2015, 12:32:12
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934719
Фотография Hett
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hett
Участник
1. уникальных user_file_id 6458354 в этой базе (на деве все хуже)
2. c is_deleted там какой-то и правда логический косяк, но это уже не мое. Хотя возможно конечный результат верный, если берется последнее значение, группировка по этому полю разве что-то даст?

Да, я чет самое главное не написал, простите.
Раньше был индекс user_id,date и все более-менее работало, пока не сделали партицирование
PARTITION BY RANGE (MONTH(date))
12 партиций. После этого и начались тормоза, причем не важно сколько данных попадают под выборку, даже если для юзера записей нет, запрос все равно работал 40 секунд.

Индекс IDX_fh_analytics_file__user_id__user_file_id__date по крайней мере не обладает такой проблемой (у него прямая зависимость времени работы запроса от количества данных)
...
Рейтинг: 0 / 0
13.04.2015, 12:42:06
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934721
Фотография Hett
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hett
Участник
MasterZivфорсить его хинтами.

Это как?
...
Рейтинг: 0 / 0
13.04.2015, 12:43:04
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934723
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
Hett1. уникальных user_file_id 6458354 в этой базе (на деве все хуже)


ВАЖНО: именно С ЭТИМ УСЛОВИЕМ, а не вообще!
...
Рейтинг: 0 / 0
13.04.2015, 12:43:37
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934724
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
HettMasterZivфорсить его хинтами.

Это как?

FORCE INDEX (...), ты же сам писал...
...
Рейтинг: 0 / 0
13.04.2015, 12:44:12
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934726
Фотография Hett
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hett
Участник
В девелоперской таблице, кстать, 32 млн записей
...
Рейтинг: 0 / 0
13.04.2015, 12:44:48
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934729
Фотография Hett
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hett
Участник
MasterZivHett1. уникальных user_file_id 6458354 в этой базе (на деве все хуже)


ВАЖНО: именно С ЭТИМ УСЛОВИЕМ, а не вообще!

ну тогда их число равно количеству данных после группировки
119011 ?
...
Рейтинг: 0 / 0
13.04.2015, 12:45:56
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934730
Фотография Hett
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hett
Участник
MasterZivHettпропущено...


Это как?

FORCE INDEX (...), ты же сам писал...

слово "хинты" смутило
...
Рейтинг: 0 / 0
13.04.2015, 12:46:15
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934736
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
HettPARTITION BY RANGE (MONTH(date))
12 партиций. После этого и начались тормоза, причем не важно сколько данных попадают под выборку, даже если для юзера записей нет, запрос все равно работал 40 секунд.


Это мало партиций. Ну ладно, допустим -- покатит...

Тебе надо чтобы partition pruning заработал, для этого надо в WHERE писать условие, чтобы содержало MONTH(date), чтобы
сразу отсекались ненужные партиции.

Т.е. к AND analytics.date >= '2014-03-14'
AND analytics.date < '2015-04-13' + INTERVAL 1 DAY

надо ещё отельно условия на MONTH(date) прописывать, если это возможно по логике запроса.

Если нет -- ты УВЕЛИЧИЛ таким образом время выполнения на порядок ( в 12 раз, если быть точным).
Так что ж ты хочешь ?
...
Рейтинг: 0 / 0
13.04.2015, 12:47:50
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934738
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
HettMasterZivпропущено...


ВАЖНО: именно С ЭТИМ УСЛОВИЕМ, а не вообще!

ну тогда их число равно количеству данных после группировки
119011 ?

Да, логично, эт я что-то тормознул...
...
Рейтинг: 0 / 0
13.04.2015, 12:48:25
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934744
Фотография Hett
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hett
Участник
MasterZivЕсли нет -- ты УВЕЛИЧИЛ таким образом время выполнения на порядок ( в 12 раз, если быть точным).
Так что ж ты хочешь ?

Ну... партиции то стали меньше в 12 раз, по сравнению с исходной таблицей :)
попробую в условие добавить месяцы
...
Рейтинг: 0 / 0
13.04.2015, 12:50:00
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934758
Фотография Hett
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Hett
Участник
Хотя если верить документации https://dev.mysql.com/doc/refman/5.1/en/partitioning-pruning.html
то partition pruning уже должен заработать, в условии же есть поле по которому осуществляется партицирование
...
Рейтинг: 0 / 0
13.04.2015, 13:00:18
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934759
tanglir
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
tanglir
Участник
HettХотя возможно конечный результат верный, если берется последнее значение, группировка по этому полю разве что-то даст?Берётся не последнее, а случайное.
Hettпока не сделали партицированиеС этого надо ыло начинать :)
HettНу... партиции то стали меньше в 12 раз, по сравнению с исходной таблицей :)Скорость работы индекса (с) - примерно логарифм от количества проиндексированных записей. При партицировании у каждой партиции образуется свой собственный индекс. А теперь сравните log(N) и 12*log(N/12).
...
Рейтинг: 0 / 0
13.04.2015, 13:00:48
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934764
Фотография MasterZiv
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
MasterZiv
Участник
HettНу... партиции то стали меньше в 12 раз, по сравнению с исходной таблицей :)


А вот, парадокс... То, что партиции в 10 раз стали меньше --- не очень и важно, потому что там логарифмическая зависимость,
и эти 10 раз уберутся "на раз".
А что теперь тебе придётся обрабатывать 12 таких таблиц и сливать потом результаты -- факт, и это увеличивает объём работы в 12+ раз, если ты не используешь в запросе отсечение ненужных партиций.

Про отсечение читай тут:
https://dev.mysql.com/doc/refman/5.7/en/partitioning-pruning.html
...
Рейтинг: 0 / 0
13.04.2015, 13:02:52
| Ответить | Цитировать | Написать  
Оптимизация индекса
    #38934767
Фотография Akina
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Akina
Участник
Hettвсе и не надо...
Надо! Сервер не настолько умён, чтобы догадаться, что все записи группы содержать одно и то же значение в негруппируемом поле. Как итог - при построении суммарной комбинации полей группировки и отбора получается "разрыв", и индекс используется неэффективно - вместо простого выбора по индексу начинается сканирование значений таблицы.
...
Рейтинг: 0 / 0
13.04.2015, 13:03:38
| Ответить | Цитировать | Написать  
25 сообщений из 36, страница 1 из 2
  1  все
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Оптимизация индекса
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение   Полит. конфиден.  
NoSQL.ru       Кролег: Проекты, Новости, Чат       РЕД ФОРУМ       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&fpage=139&tid=1833301]:
 0ms
get settings:
 5ms
get forum list:
 14ms
check forum access:
 3ms
check topic access:
 3ms
track hit:
 28ms
get topic data:
 12ms
get forum data:
 2ms
get page messages:
 56ms
get tp. blocked users:
 4ms
others: 203ms
total:  330ms
созд. / загр.: 330ms
x
x
Закрыть


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