ReSQL.ru
     
powered by simpleCommunicator - 2.0.59     © 2025 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Запрос не выполняется за долгое время.
9 сообщений из 9, страница 1 из 1
Запрос не выполняется за долгое время.
    #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, 10:32
| Ответить | Цитировать | Написать  
Запрос не выполняется за долгое время.
    #39735082
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
Показывайте DDL таблицы, запросы и планы обоих случаев - быстрого и медленного.
...
Рейтинг: 0 / 0
19.11.2018, 12:17
| Ответить | Цитировать | Написать  
Запрос не выполняется за долгое время.
    #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, 13:58
| Ответить | Цитировать | Написать  
Запрос не выполняется за долгое время.
    #39735133
miksoft
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
miksoft
Участник
alsudnik,

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

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

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

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

EAV

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

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

Спасибо за все советы, есть над чем подумать.
...
Рейтинг: 0 / 0
20.11.2018, 09:29
| Ответить | Цитировать | Написать  
9 сообщений из 9, страница 1 из 1
Форумы / MySQL [игнор отключен] [закрыт для гостей] / Запрос не выполняется за долгое время.
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
Польз. соглашение
NoSQL.ru       Новости, Чат       Фотоальбом: Участники, Встречи
Закрыть
start [/forum/topic.php?fid=47&gotonew=1&tid=1829468]:
 0ms
get settings:
 10ms
get forum list:
 11ms
check forum access:
 4ms
check topic access:
 4ms
track hit:
 32ms
get topic data:
 9ms
get first new msg:
 7ms
get forum data:
 2ms
get page messages:
 51ms
get tp. blocked users:
 2ms
others: 10ms
total:  142ms
созд. / загр.: 142ms
x
x
Закрыть


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