Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Запрос не выполняется за долгое время. / 9 сообщений из 9, страница 1 из 1
19.11.2018, 10:32
    #39734987
alsudnik
alsudnik
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос не выполняется за долгое время.
Всем привет!
Имеется таблица 17 млн записей (3,5 Gb), возникли проблемы с выборкой по этой таблице.
Суть вот в чем, данный запрос отрабатывает приемлемо быстро, около 1 сек.

SELECT count(DISTINCT(card)) FROM `visits_razdel` WHERE
card != 0 AND
cat IN ('cat_1','cat_2','cat_3','cat_4','cat_5','cat_6','cat_7','cat_8','cat_9') AND
`date_short` >= '2018-10-18' AND `date_short` < '2018-11-16'

Но при добавлении любой десятой категории в условие запрос начинает выполнение и дождаться его результата не получается, приходится прерывать выполнение. Складывается впечатление что не хватает буфера для выполнения запроса. К сожалению конфигурировать настройки сервера еще не приходилось и даже поиграться с настройками достаточно сложно т.к. сервер удаленный и все настройки выполняются сторонними администраторами хостинга.

Подскажите в какой области копать. По поисковику упоминается важность параметра innodb buffer pool size, сейчас его значение 2 Гб, что при таблице в 3,5 Гб явно мало.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.11.2018, 12:17
    #39735082
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос не выполняется за долгое время.
Показывайте DDL таблицы, запросы и планы обоих случаев - быстрого и медленного.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.11.2018, 13:58
    #39735132
alsudnik
alsudnik
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос не выполняется за долгое время.
Структура таблицы
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
CREATE TABLE `client_visits_razdel` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `client_id` varchar(255) NOT NULL DEFAULT '',
  `num_card` int(11) NOT NULL,
  `raz` varchar(50) NOT NULL,
  `cat_abc` varchar(255) NOT NULL,
  `brand` varchar(255) NOT NULL,
  `ref_id` int(11) DEFAULT '0',
  `date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `date_short` date DEFAULT NULL,
  `cnt_view` int(11) NOT NULL DEFAULT '1',
  `mobile` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `cat_abc` (`cat_abc`),
  KEY `brand` (`brand`),
  KEY `ref_id` (`ref_id`),
  KEY `client_id` (`client_id`),
  KEY `num_card` (`num_card`),
  KEY `date_short` (`date_short`),
  KEY `test` (`num_card`,`cat_abc`,`date_short`)
) ENGINE=InnoDB AUTO_INCREMENT=17020641 DEFAULT CHARSET=cp1251



Запрос который прерывал спустя 2 минуты выполнения:
Код: sql
1.
2.
3.
4.
EXPLAIN SELECT count(DISTINCT(num_card)) FROM `client_visits_razdel` WHERE 
num_card != 0 AND 
cat_abc IN ('vytyazhki','poverhnosti_gazovye','duhovye_shkafy','kofemashiny','mikrovolnovie_pechi','posudomoechnie_mashiny','stiralnie_mashiny','xolodilniki','poverhnosti_elektricheskie','vodonagrevateli') AND 
`date_short` >= '2018-10-18' AND `date_short` < '2018-11-16'

Код: plaintext
1.
id 	select_type  table 			type 	         possible_keys 					key 	     key_len 	ref rows 	Extra
"1"	"SIMPLE"	"client_visits_razdel"	"range"	"cat_abc,num_card,date_short,test"	"cat_abc"	"257"	\N	"1260"	"Using index condition; Using where"


Запрос который отрабатывает за 1 секунду, в нем указано на одну категорию меньше чем в верхнем запросе:
Код: sql
1.
2.
3.
4.
EXPLAIN SELECT count(DISTINCT(num_card)) FROM `client_visits_razdel` WHERE 
num_card != 0 AND 
cat_abc IN ('vytyazhki','poverhnosti_gazovye','duhovye_shkafy','kofemashiny','mikrovolnovie_pechi','posudomoechnie_mashiny','stiralnie_mashiny','xolodilniki','poverhnosti_elektricheskie') AND 
`date_short` >= '2018-10-18' AND `date_short` < '2018-11-16'

Код: plaintext
1.
id 	select_type   table 			type 	         possible_keys 					key 	     key_len ref rows 	Extra
"1"	"SIMPLE"	"client_visits_razdel"	"range"	"cat_abc,num_card,date_short,test"	"test"	"4"	\N	"519221"	"Using where; Using index"
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.11.2018, 14:00
    #39735133
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос не выполняется за долгое время.
alsudnik,

Форматирование смогу поправить только вечером, но даже так видно, что используется другой индекс.
Попробуйте пересобрать статистику по таблице или явно указать в запросе какой индекс использовать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.11.2018, 14:51
    #39735154
alsudnik
alsudnik
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос не выполняется за долгое время.
miksoft,

Спасибо вам за подсказку, при явном указании индекса test тяжелый запрос тоже выполнился очень быстро. Огорчает лишь то что данный составной индекс создавался для решения конкретно этой задачи. А задач может быть множество, вытянуть данные по разделу, категории, бренду, характеристикам, различные вариации раздел + бренд, категория + бренд, раздел + бренд + характеристика и тд. Как поступают в этом случае? И может подскажете на какие параметры сервера все же стоит обратить внимание для оптимизации, тот же innodb buffer pool size с 2Гб.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.11.2018, 20:30
    #39735314
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос не выполняется за долгое время.
alsudnik
Код: sql
1.
cat_abc IN ('vytyazhki',

Кстати, использовать строковые идентификаторы групп не выглядит хорошим решением.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.11.2018, 20:33
    #39735318
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос не выполняется за долгое время.
alsudnikА задач может быть множество, вытянуть данные по разделу, категории, бренду, характеристикам, различные вариации раздел + бренд, категория + бренд, раздел + бренд + характеристика и тд. Как поступают в этом случае?По-разному. Вообще этот вопрос надо задавать архитектору БД.

Бывают разные варианты:
как ваш, с кучей полей и индексов на них

EAV

фасетные идексы
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
19.11.2018, 20:40
    #39735320
miksoft
miksoft
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос не выполняется за долгое время.
alsudnikИ может подскажете на какие параметры сервера все же стоит обратить внимание для оптимизации, тот же innodb buffer pool size с 2Гб.Буферный пул InnoDB в идеале должен вмещать все таблицы и индексы с запасом в 5-10%. На практике обычно достаточно, если в него влезают все "горячие" (которые часто читаются) фрагменты таблиц и индексов.

Кроме увеличения буферного пула помогает и обратное действие - уменьшение объема данных. В данном случае индентификаторы групп явно имеет смысл заменить на числовые, тогда хватит 2-4 байт.
Да и вообще все varchar-ы вызывают сомнение в этой таблице.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
20.11.2018, 09:29
    #39735426
alsudnik
alsudnik
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Запрос не выполняется за долгое время.
miksoft,

Спасибо за все советы, есть над чем подумать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Запрос не выполняется за долгое время. / 9 сообщений из 9, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
созд. / загр.: 144ms
Закрыть
start [/forum/topic.php?fid=47&tablet=1&tid=1829468]:
 0ms
get settings:
 11ms
get forum list:
 15ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 36ms
get topic data:
 12ms
get forum data:
 2ms
get page messages:
 47ms
get tp. blocked users:
 2ms
others: 11ms
total:  144ms
x
x
Закрыть


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