Оптимизация SELECT со статистикой за месяц

mat.twg · Сообщение **mat.twg** » 2016.07.06, 11:29

IIIKET писал(а):
mat.twg писал(а):Одназначно!
Если можно аргументов или статистики, пожалуйста.
А вдруг все таки innoDB рассчитано на таблицы до 30 миллионов записей и спокойно с ними работает...
Тасков критических много, это желательно отложить до лучших времен (которые на врятли наступят).

У вас 10 миллиардов, я даже в суть не вникал особо.. или вы из РОС-СТАТА?

demimurych · Сообщение **demimurych** » 2016.07.06, 13:18

IIIKET писал(а):Извините за бардак, проект в ужасном состоянии) Переписать бы полностью да времени не дают...
Таблица InnoDB.

1. В текущий момент число записей свыше 10 000 000 000, на какое количество записей расчитана innoDB? Стоит ли задумываться о использовании архивной таблицы ARCHIVE, в которую переносить данные, которым свыше 4 месяцов?

2. По поводу комбинированных индексов, то они работают, проблема была в кэше.

Сейчас запросы на выборку более миллиона записей длится не более 5 сек. с выборкой сопутствующей статистики с иных таблиц.
Это более менее удовлетворяющие время.

P.S. Завтра залью свежий бэк ап. Отпишусь EXPLAIN-ы которые Вы просили.

Вопрос по обьему данных в таблице не однозначный:
Формально у InnoDb ограничение на обьем данных в таблице нет. НО, на InnoDb накладывает ограничение файловая система, а именно какой максимальный размер файла поддерживает сама система. По умолчанию Mysql все данные InnoDb хранит в одном файле. Однако, (если я ничего не забыл с 5 версии) в mysql появилась настройка позволяющая разбивать InnoDB на несколько файлов.
innodb_file_per_table

Вторая проблема, это ограничение на размер ключа. Посмотреть текущее состояние можно запросом
SHOW TABLE STATUS FROM yourdatabasename

Как самый простой выход:
Вам вероятнее всего нужно не заводить таблицу archive а внимательно ознакомиться с тем что такое партицирование в mysql.
с 5 версии появилась возможность разбивать одну таблицу на несколько по заданному критерию. Например данные за один год лежат в одной таблице, за следующий во второй. При этом, запрос к данным будет ровно таким же, как если бы данные лежали в одной таблице. В гугле наберите mysql партицирование найдете достаточно разжеванного материала по этой теме.

И как резюме.
Раз время запроса УЖЕ сократили до 5 секунд, значит есть куда расти дальше. Я более чем уверен что запрос можно выполнить менее чем за секунду с правильной оптимизацией для случаев order и group by

IIIKET · Сообщение **IIIKET** » 2016.07.06, 17:17

demimurych писал(а):...
На локальной копии базы, сделать следущие запросы с explaine и профайлингом
Оригинальный запрос
Запрос с добавленным составным индексом
Запрос с добавленным составным индексом НО убрать из запроса SORT BY
Запрос с добавленным составным индексом НО убрать из запроса SORT BY и GROUP BY

На текущий момент 12 ккк записей, этот лог чистился до 2015 года. Архивирование не применяется на продакшене, все в локальных дампах.
Таблица:

Код: Выделить всё

CREATE TABLE `om_log` (
  `code` int(5) NOT NULL,
  `count` int(1) NOT NULL,
  `ip` varchar(250) NOT NULL,
  `date` int(10) DEFAULT NULL,
  `lang` varchar(2) NOT NULL,
  KEY `code` (`code`),
  KEY `date` (`date`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1251

Обычный запрос:

Код: Выделить всё

SELECT SQL_NO_CACHE COUNT(1) AS 'visits', `code`
FROM `om_log`
WHERE `code` <> '0'
    AND `date` >= UNIX_TIMESTAMP('2016-06-25')
    AND `date` <= UNIX_TIMESTAMP('2016-08-01')
GROUP BY `code`
ORDER BY `code`
LIMIT 99999999;

/* time: 6.703 */
id   | select_type  | table    | type   | possible_keys | key   | key_len  | ref    | rows    | Extra
1    | SIMPLE       | om_log   | range  | "code,date"   | date  | 5        | NULL   | 552090  | "Using where; Using temporary; Using filesort"

После добавления комбинированного ключа по `data` и `code`:

Код: Выделить всё

ALTER TABLE `om_log` ADD INDEX `report` (`date` ASC, `code` ASC) ;

/* SQL - тот же самый */
/* time: 1.969 */
id   | select_type  | table    | type   | possible_keys        | key     | key_len  | ref    | rows    | Extra
2    | SIMPLE       | om_log   | range  | "code,date,report"   | report  | 5        | NULL   | 535642  | "Using where; Using index; Using temporary; Using filesort"

/* SQL - без order by */
/* time: 1.954 */
id   | select_type  | table    | type   | possible_keys        | key     | key_len  | ref    | rows    | Extra
2    | SIMPLE       | om_log   | range  | "code,date,report"   | report  | 5        | NULL   | 535642  | "Using where; Using index; Using temporary; Using filesort"
/* Да EXPALIN точно такой же */


/* SQL - без order by и group by*/
/* time: 0.750 */
id   | select_type  | table    | type   | possible_keys        | key     | key_len  | ref    | rows    | Extra
3    | SIMPLE       | om_log   | range  | "code,date,report"   | report  | 5        | NULL   | 535642  | "Using where; Using index;"
/* EXPALIN без temporary и filesort */

IIIKET · Сообщение **IIIKET** » 2016.07.06, 17:28

mat.twg писал(а):У вас 10 миллиардов, я даже в суть не вникал особо.. или вы из РОС-СТАТА?

10 миллионов! Порядок очень важен)
Ахха, шутка про РОС-СТАТА заехала будем считать)

Nerf · Сообщение **Nerf** » 2016.07.07, 05:26

Я чего-то не понимаю. Судя по key_len, запрос не использует составной индекс полностью(только часть по дате), но время исполнения запроса упало в 3... Как так?

IIIKET · Сообщение **IIIKET** » 2016.07.07, 09:08

Nerf писал(а):Я чего-то не понимаю. Судя по key_len, запрос не использует составной индекс полностью(только часть по дате), но время исполнения запроса упало в 3... Как так?

Можете объяснить свой вывод, или дать ссылку где толково расписан `key_len`?
Я думал что `type` должен быть 'Eq_ref', и это будет говорить о полном использовании индекса...

Nerf · Сообщение **Nerf** » 2016.07.07, 14:55

Толково не знаю, можно тут
http://dev.mysql.com/doc/refman/5.7/en/ ... utput.html

Но я посмотрел, говорят, что key_len врет иногда. 'Using index' намекает все же, что все ок.
Просто глядя на key_len = 5, я сделал скоропостижный вывод, что используется только дата(4 байта + 1 null), так-то по логике ожидалось увидеть 9.

ПС: С типом все ок http://dev.mysql.com/doc/refman/5.7/en/ ... type_range

mat.twg · Сообщение **mat.twg** » 2016.07.08, 08:49

Попробуйте не использовать внутренние функции типа UNIX_TIMESTAMP('2016-08-01'), а уже готовый int сравнивать.

IIIKET · Сообщение **IIIKET** » 2016.07.08, 09:01

mat.twg писал(а):Попробуйте не использовать внутренние функции типа UNIX_TIMESTAMP('2016-08-01'), а уже готовый int сравнивать.

На самом деле с int и сравниваю, это когда тестил для примера.

mat.twg · Сообщение **mat.twg** » 2016.07.08, 09:04

IIIKET писал(а):
mat.twg писал(а):Попробуйте не использовать внутренние функции типа UNIX_TIMESTAMP('2016-08-01'), а уже готовый int сравнивать.
На самом деле с int и сравниваю, это когда тестил для примера.

Ещё установите лимиты (разбейте на страницы) ускорит, при прочих равных вы достигли оптимальных результатов, в зависимости от выборки результатов.

пс: хотя можно ещё сократить размер индексов с помощью UNSIGNED

IIIKET · Сообщение **IIIKET** » 2016.07.08, 12:20

mat.twg писал(а):
IIIKET писал(а):
mat.twg писал(а):Попробуйте не использовать внутренние функции типа UNIX_TIMESTAMP('2016-08-01'), а уже готовый int сравнивать.
На самом деле с int и сравниваю, это когда тестил для примера.
Ещё установите лимиты (разбейте на страницы) ускорит, при прочих равных вы достигли оптимальных результатов, в зависимости от выборки результатов.

пс: хотя можно ещё сократить размер индексов с помощью UNSIGNED

К сожалению разбивка на страницы не приемлема.
Уж больно много страниц прийдется перелопатить что бы найти что нужно.
+ Это все генерится в xls файл.

mat.twg · Сообщение **mat.twg** » 2016.07.08, 14:45

IIIKET писал(а):
mat.twg писал(а):
IIIKET писал(а): На самом деле с int и сравниваю, это когда тестил для примера.
Ещё установите лимиты (разбейте на страницы) ускорит, при прочих равных вы достигли оптимальных результатов, в зависимости от выборки результатов.

пс: хотя можно ещё сократить размер индексов с помощью UNSIGNED
К сожалению разбивка на страницы не приемлема.
Уж больно много страниц прийдется перелопатить что бы найти что нужно.
+ Это все генерится в xls файл.

Ну так если это не вывод пользователю, то и нечего париться.... уверен в 1с подобное происходит дясятки минут =)

IIIKET · Сообщение **IIIKET** » 2016.07.11, 09:01

mat.twg писал(а):Ну так если это не вывод пользователю, то и нечего париться.... уверен в 1с подобное происходит дясятки минут =)

Не понимаю что Вас так смешит)))
Но при этом ложится сервак (сайт виснет).
Можно купить и сервак по мощнее, но если тонкое место в MySQL лишнее железо не поможет...

Nerf · Сообщение **Nerf** » 2016.07.11, 17:46

mat.twg писал(а):
IIIKET писал(а):
mat.twg писал(а):Попробуйте не использовать внутренние функции типа UNIX_TIMESTAMP('2016-08-01'), а уже готовый int сравнивать.
На самом деле с int и сравниваю, это когда тестил для примера.
Ещё установите лимиты (разбейте на страницы) ускорит, при прочих равных вы достигли оптимальных результатов, в зависимости от выборки результатов.

пс: хотя можно ещё сократить размер индексов с помощью UNSIGNED

1) Как лимиты могут повлиять на скорость выполнения запроса с группировкой в данном случае(кроме limit 0).
2) Как UNSIGNED влияет на размер?

IIIKET писал(а): Но при этом ложится сервак (сайт виснет).
Можно купить и сервак по мощнее, но если тонкое место в MySQL лишнее железо не поможет...

А генерировать ночью раз в месяц, а потом отдавать результат не вариант?

Ale}{ · Сообщение **Ale}{** » 2016.07.11, 18:00

вижу 2 решения:
1. Ну в 1С это называется оборотный регистр накопления. Суть его в том , что выбирается период агрегации данных(ресурсов) по ключам аналитики(измерения), а при вставке данных (удалению) - ресурсы пересчитываются сразу.
У вас в принципе стандартная задача, тем более если вы говорите что, отчеты берутся в большинстве случаем именно помесячно.
Дайте плиз пояснения по полям CODE, COUNT. Правильно ли я предполагаю что COUNT всегда равен 1? CODE - поле, ссылающиеся на справочную таблицу и имеет ограниченное количество вариантов?

2. Впринципе все тоже самое -но агрегацию (подготовку данных) проводить не в момент записи строки в базу, а по расписанию. (OLAP кубы)

Если заинтересует использование регистров - могу помочь с кодом запросов.

IIIKET · Сообщение **IIIKET** » 2016.07.12, 09:10

Ale}{ писал(а):вижу 2 решения:
1. Ну в 1С это называется оборотный регистр накопления. Суть его в том , что выбирается период агрегации данных(ресурсов) по ключам аналитики(измерения), а при вставке данных (удалению) - ресурсы пересчитываются сразу.
У вас в принципе стандартная задача, тем более если вы говорите что, отчеты берутся в большинстве случаем именно помесячно.
Дайте плиз пояснения по полям CODE, COUNT. Правильно ли я предполагаю что COUNT всегда равен 1? CODE - поле, ссылающиеся на справочную таблицу и имеет ограниченное количество вариантов?

2. Впринципе все тоже самое -но агрегацию (подготовку данных) проводить не в момент записи строки в базу, а по расписанию. (OLAP кубы)

Если заинтересует использование регистров - могу помочь с кодом запросов.

Да, вы правильно все поняли, только CODE имеет ограниченное количество вариантов в конкретный момент времени, эти коды пополняются с разным интервалом.
Агрегация решает проблемы, но очень как по мне дорогой ценой. Агрегировать нужно будет по двум параметрам, по `date` и `CODE`. Так как кто угодно может запросить выборку по определенным `CODE`. А количество `CODE` достаточно велико > 30 000, и со временем оно не уменьшается а только растет.

Я привел только один запрос он оптимизирован. Для каждого `CODE` берется под запрос. Из за этого в совокупности и было время большое. Под запросы я сам расковырял.

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

Ale}{ · Сообщение **Ale}{** » 2016.07.13, 01:29

Тогда пробуй агрегацию данных при вставке строки в основную таблицу. В какой момент у тебя заноситься данные?
Посмотрел у себя (период накопления инфы 1 год)- в основной таблице 13к строк (по 2-ум измерениям), а в таблице оборотов (агрегированной) помесячно - всего 980 строк.
Вот и смотри разницу.

IIIKET · Сообщение **IIIKET** » 2016.07.13, 08:54

Ale}{ писал(а):Тогда пробуй агрегацию данных при вставке строки в основную таблицу. В какой момент у тебя заноситься данные?
Посмотрел у себя (период накопления инфы 1 год)- в основной таблице 13к строк (по 2-ум измерениям), а в таблице оборотов (агрегированной) помесячно - всего 980 строк.
Вот и смотри разницу.

Я рад что Вы агригируете данные и выгода очевидна но есть ТЗ.
Не донемайте тем о чем в теме написано "неприменимо".
Данные заносятся в момента захода пользователя на сайт.

mat.twg · Сообщение **mat.twg** » 2016.07.13, 13:07

Nerf писал(а): 1) Как лимиты могут повлиять на скорость выполнения запроса с группировкой в данном случае(кроме limit 0).
2) Как UNSIGNED влияет на размер?

1. В данном контексте, прямо пропорционально размеру выборки результатов.
2. Ну да... 4 байта, размер maximum value, конечно...

Yii Framework

Оптимизация SELECT со статистикой за месяц

Re: Оптимизация SELECT со статистикой за месяц

Re: Оптимизация SELECT со статистикой за месяц

Re: Оптимизация SELECT со статистикой за месяц

Re: Оптимизация SELECT со статистикой за месяц

Re: Оптимизация SELECT со статистикой за месяц

Re: Оптимизация SELECT со статистикой за месяц

Re: Оптимизация SELECT со статистикой за месяц

Re: Оптимизация SELECT со статистикой за месяц

Re: Оптимизация SELECT со статистикой за месяц

Re: Оптимизация SELECT со статистикой за месяц

Re: Оптимизация SELECT со статистикой за месяц

Re: Оптимизация SELECT со статистикой за месяц

Re: Оптимизация SELECT со статистикой за месяц

Re: Оптимизация SELECT со статистикой за месяц

Re: Оптимизация SELECT со статистикой за месяц

Re: Оптимизация SELECT со статистикой за месяц

Re: Оптимизация SELECT со статистикой за месяц

Re: Оптимизация SELECT со статистикой за месяц

Re: Оптимизация SELECT со статистикой за месяц