SQLinfo.ru - Все о MySQL Webew.ru: теория и практика веб-технологий

Форум пользователей MySQL

Задавайте вопросы, мы ответим

Вы не зашли.

#1 04.12.2016 22:56:59

oee
Участник
Зарегистрирован: 03.12.2016
Сообщений: 14

ускорить запрос к большой таблице innodb (+ group by)

Доброго времени суток.

Имеется таблица ~18 млн строк, тип innodb.
Запрос вида


select `id_group`,DATE_FORMAT(`date`,'%d.%m.%Y') as `date`,sum(`s`) as `s`,sum(`c`) as `c`,sum(`h`) as `h`,round(sum(`r`),2) as `r` from `table` where `r`>0 and `id_user`=1 and `group`='texttext' and `date` between '2016-11-27' and '2016-12-04' group by `id_group`,`date`
 

выполняется 5 сек (205 всего, Query took 5.2868 seconds.)

Возможно ли как-то ускорить его?

Структура таблицы

CREATE TABLE IF NOT EXISTS `table` (
  `type` varchar(20) NOT NULL,
  `group` varchar(20) NOT NULL,
  `date` date NOT NULL,
  `d` int(11) NOT NULL,
  `m` int(11) NOT NULL,
  `y` int(11) NOT NULL,
  `id_user` int(11) NOT NULL,
  `id_group` int(11) NOT NULL,
  `id_item` int(11) NOT NULL,
  `country` varchar(20) NOT NULL,
  `operator` varchar(20) NOT NULL,
  `platform` varchar(20) NOT NULL,
  `browser` varchar(20) NOT NULL,
  `s` int(11) NOT NULL DEFAULT '0',
  `c` int(11) NOT NULL DEFAULT '0',
  `h` int(11) NOT NULL DEFAULT '0',
  `r` double(10,2) NOT NULL DEFAULT '0.00'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

ALTER TABLE `table`
 ADD UNIQUE KEY `key` (`type`,`group`,`date`,`id_user`,`id_group`,`id_item`,`country`,`operator`,`platform`,`browser`), ADD KEY `date` (`date`), ADD KEY `d` (`d`), ADD KEY `m` (`m`), ADD KEY `y` (`y`), ADD KEY `id_user` (`id_user`), ADD KEY `id_group` (`id_group`), ADD KEY `id_item` (`id_item`);
 

Отредактированно oee (04.12.2016 23:00:36)

Неактивен

 

#2 04.12.2016 23:35:33

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 4947

Re: ускорить запрос к большой таблице innodb (+ group by)

попробуйте составной (id_user`,`group`,`date`)

Неактивен

 

#3 05.12.2016 08:20:33

oee
Участник
Зарегистрирован: 03.12.2016
Сообщений: 14

Re: ускорить запрос к большой таблице innodb (+ group by)

не помогло

Неактивен

 

#4 05.12.2016 08:23:32

deadka
Администратор
Зарегистрирован: 14.11.2007
Сообщений: 1963

Re: ускорить запрос к большой таблице innodb (+ group by)

Покажите explain и explain extended запроса (лучше и до добавления индекса и после, чтоб посмотреть, что изменилось и изменилось ли вообще).


Зеленый свет для слабаков, долги отдают только трусы, тру гики работают только в консоли...

Неактивен

 

#5 05.12.2016 18:59:29

oee
Участник
Зарегистрирован: 03.12.2016
Сообщений: 14

Re: ускорить запрос к большой таблице innodb (+ group by)

Сегодня странно, увеличилось время выполнения запроса Query took 37.8595 seconds. (это без индекса)
База данных существенно не изменилась, ~19 млн строк

explain без индекса https://yadi.sk/i/EVzzoTwv32HoHb
explain extended без индекса https://yadi.sk/i/xr4teX6H32Ho3T

Добавил такой индекс

ALTER TABLE `table` ADD KEY `key2` (`id_user`,`date`,`id_group`);


Сейчас вечером чуть получше стало.. Может влияла общая нагрузка сервера.. Хотя он особо не нагружен.

Query took 1.5457 seconds.
explain с индексом https://yadi.sk/i/3ofQbSoB32HkLm
explain extended с индексом https://yadi.sk/i/1-WMVhNf32HksA

Я так понимаю индекс используется в запросе и лишним не будет.
Может еще есть способы уменьшить время запроса? Таблица в будущем вырастет как минимум в 2 раза

Неактивен

 

#6 05.12.2016 19:08:42

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 4947

Re: ускорить запрос к большой таблице innodb (+ group by)

порядок имеет значение
я предлагал (id_user`,`group`,`date`)
а вы добавили (`id_user`,`date`,`id_group`)

попробуйте мой вариант и покажите с ним explain

Неактивен

 

#7 05.12.2016 19:40:40

oee
Участник
Зарегистрирован: 03.12.2016
Сообщений: 14

Re: ускорить запрос к большой таблице innodb (+ group by)

vasya, сорри, недосмотрел. Исправил индекс. Время выполнения 4.9620 seconds.
Explain https://yadi.sk/i/sp_OI8OP32J5Dc

Через пару минут выполнил тот-же запрос, время выполнения 1.5792 seconds.
С select SQL_NO_CACHE тоже самое, полторы секунды.
Изменил дату на 1 день назад (диапазон не изменился) 2.0636 seconds.

В целом неплохо, очень даже.. Но все равно, если есть варианты еще уменьшить время выполнения запроса, напишите, попробую.
И еще вопрос, остальные одиночные индексы сильно нужны? Не могут ли они быть лишними? Не бывает ли от них вреда, скажем так?

Отредактированно oee (05.12.2016 19:47:45)

Неактивен

 

#8 05.12.2016 20:03:02

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 4947

Re: ускорить запрос к большой таблице innodb (+ group by)

лишние индексы плохо: замедляют изменение данных, занимают место.
но нужно смотреть не используются ли они в других запросах.


по этому запросу посмотрите на какие этапы тратится время:
http://webew.ru/articles/2732.webew

и через show status like .. нужно смотреть откуда читается индекс (память или диск), где создается временная таблица(память или диск)

добавьте в конец запроса order by null

Неактивен

 

#9 05.12.2016 22:16:17

oee
Участник
Зарегистрирован: 03.12.2016
Сообщений: 14

Re: ускорить запрос к большой таблице innodb (+ group by)

профилирование сделал через pma https://yadi.sk/i/_RqPe1wb32JgoZ
C order by null ничего не изменилось https://yadi.sk/i/T-Vl9D9P32JiBR

Через show status like что именно посмотреть?

весь SHOW STATUS https://yadi.sk/i/hvpi1Y9032JjTv

Отредактированно oee (05.12.2016 22:27:56)

Неактивен

 

#10 05.12.2016 22:27:15

oee
Участник
Зарегистрирован: 03.12.2016
Сообщений: 14

Re: ускорить запрос к большой таблице innodb (+ group by)

нагуглил что за размер временных таблиц отвечают переменные
tmp_table_size  = 128M
max_heap_table_size = 2048M

Если временная таблица превышает размер tmp_table_size, то она скидывается на диск?
Оперативки на сервере 16 гб, вся она не используется.
Какое оптимальное значение установить? Или как вычислить сколько нужно для (например) данного запроса?

Неактивен

 

#11 05.12.2016 22:45:28

oee
Участник
Зарегистрирован: 03.12.2016
Сообщений: 14

Re: ускорить запрос к большой таблице innodb (+ group by)

vasya написал:

лишние индексы плохо: замедляют изменение данных, занимают место.
но нужно смотреть не используются ли они в других запросах.

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

Наверно тогда не стоит их удалять, пусть будут

Отредактированно oee (05.12.2016 22:47:13)

Неактивен

 

#12 06.12.2016 00:16:36

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 4947

Re: ускорить запрос к большой таблице innodb (+ group by)

Ваш браузер устарел, поэтому Диск не может быть открыт.

показывайте в текстовом варианте или завтра посмотрю.

Created_tmp_disk_tables -- Количество неявных временных таблиц на диске, созданных во время выполнения операторов.
Key_reads -- Количество физических считываний блока ключей с диска.

сравниваете эти параметры до запроса и после

Неактивен

 

#13 06.12.2016 18:43:08

oee
Участник
Зарегистрирован: 03.12.2016
Сообщений: 14

Re: ускорить запрос к большой таблице innodb (+ group by)

Значения переменных не меняются

Created_tmp_disk_tables
до запроса 0
после тоже 0

Key_reads
до 453
после тоже 453

Неактивен

 

#14 07.12.2016 12:14:38

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 4947

Re: ускорить запрос к большой таблице innodb (+ group by)

oee написал:

C order by null ничего не изменилось https://yadi.sk/i/T-Vl9D9P32JiBR

изменилось - ушла сортировка, но это не заметно потому, что у вас почему-то очень большой copying to tmp table.

давайте ещё раз, как выглядит сейчас
show create table `имя таблицы`;

и сколько строк отбирается по условию:
`r`>0 and `id_user`=1 and `group`='texttext' and `date` between '2016-11-27' and '2016-12-04'

и какая версия
select version();

Неактивен

 

#15 07.12.2016 12:49:17

oee
Участник
Зарегистрирован: 03.12.2016
Сообщений: 14

Re: ускорить запрос к большой таблице innodb (+ group by)

Выборка не изменилась - 205 строк
Первый раз запрос выполняется почему-то 5 сек.
Все последующие 1.5 сек даже с изменением промежутка даты.

version()
5.5.37-MariaDB-wsrep

CREATE TABLE `table` (
`type` varchar(20) NOT NULL,
`group` varchar(20) NOT NULL,
`date` date NOT NULL,
`d` int(11) NOT NULL,
`m` int(11) NOT NULL,
`y` int(11) NOT NULL,
`id_user` int(11) NOT NULL,
`id_group` int(11) NOT NULL,
`id_item` int(11) NOT NULL,
`country` varchar(20) NOT NULL,
`operator` varchar(20) NOT NULL,
`platform` varchar(20) NOT NULL,
`browser` varchar(20) NOT NULL,
`s` int(11) NOT NULL DEFAULT '0',
`c` int(11) NOT NULL DEFAULT '0',
`h` int(11) NOT NULL DEFAULT '0',
`r` double(10,2) NOT NULL DEFAULT '0.00',
UNIQUE KEY `key` (`type`,`group`,`date`,`id_user`,`id_group`,`id_item`,`country`,`operator`,`platform`,`browser`),
KEY `date` (`date`),
KEY `d` (`d`),
KEY `m` (`m`),
KEY `y` (`y`),
KEY `id_user` (`id_user`),
KEY `id_group` (`id_group`),
KEY `id_item` (`id_item`),
KEY `key2` (`id_user`,`group`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Неактивен

 

#16 07.12.2016 13:37:09

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 4947

Re: ускорить запрос к большой таблице innodb (+ group by)

205 это после группировки, а интересует

select count(*) from `table` where `r`>0 and `id_user`=1 and `group`='texttext' and `date` between '2016-11-27' and '2016-12-04';


что показывает профайлинг при первом выполнении. когда 5 сек?

Неактивен

 

#17 07.12.2016 14:45:11

oee
Участник
Зарегистрирован: 03.12.2016
Сообщений: 14

Re: ускорить запрос к большой таблице innodb (+ group by)

без группировки
62300 всего, Query took 0.0011 seconds

профайлинг вечером сделаю

Неактивен

 

#18 07.12.2016 18:58:10

oee
Участник
Зарегистрирован: 03.12.2016
Сообщений: 14

Re: ускорить запрос к большой таблице innodb (+ group by)

не могу поймать когда он выполняется 5 сек.. Все время ~1.5 сек.
Попробую завтра утром

Неактивен

 

#19 08.12.2016 09:50:27

oee
Участник
Зарегистрирован: 03.12.2016
Сообщений: 14

Re: ускорить запрос к большой таблице innodb (+ group by)

сделал профайлинг с 5 сек.
Все тоже
Copying To Tmp Table    5.2 s    99.23%    1    5.2 s
просто занимает 5 сек

Неактивен

 

#20 09.12.2016 18:26:53

oee
Участник
Зарегистрирован: 03.12.2016
Сообщений: 14

Re: ускорить запрос к большой таблице innodb (+ group by)

ну и на том спасибо.

Неактивен

 

#21 10.12.2016 10:48:00

vasya
Архат
MySQL Authorized Developer
Откуда: Орел
Зарегистрирован: 07.03.2007
Сообщений: 4947

Re: ускорить запрос к большой таблице innodb (+ group by)

да, я задумался над ответом и не ответил
собственно в чем причина такого поведения мне не понятно, надеюсь другие участники форума подбросят идей.

Неактивен

 

Board footer

Работает на PunBB
© Copyright 2002–2008 Rickard Andersson