SQLinfo.ru - Все о MySQL

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

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

Вы не зашли.

#1 27.11.2019 01:18:41

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

Запрос к таблице, где имеются партиции

Доброго дня, Коллеги!

Ситуация следующая:

mysql 5.7.22 (Linux Ubuntu).

Есть табличка t1, в ней 500 записей
В запросе к ней leftjoin'ится другая табличка t2, у которой окрест 100,000,000 записей.
В t2 PRIMARY KEY (`good_id`,`shop_id`)
При этом поле t2.shop_id - по нему партиционировано.
Запрос [SELECT count(distinct shop_id) FROM t2;] возвращает 480,
Количество записей в t2 для каждого shop_id - варьируется от 20,000 до 500,000.

Сам запрос, который препарирую (самая важная его часть):

SELECT count(*) from t1 vol
LEFT JOIN t2 vga ON vga.shop_id = vol.office_id
WHERE vga.good_id = 123456;


Запрос возвращает 436,
разница между
show status like '%Innodb_rows_read%';
примерно тысяча
show status like '%Innodb_rows_read%';

explain запроса в поле партиций перечисляет 300 партиций (при чем подозреваю, что не все он там указал, потому что даже если закомментировать условие WHERE vga.good_id = 123456 - все равно остаётся 300 партиций, а в случае отсутствия WHERE он все же обязан пройтись по всем партициям).

Когда innodb_buffer_pool не разогрет, запрос отрабатывает в 10 раз медленнее, чем если такой же запрос, но вместо t2 участвует табличка
t22, которая отличается лишь тем, что у нее нет партиций по полю shop_id.

Разница между
show status like '%Innodb_rows_read%'; до и после выполнения запроса - такая же - 1000, но запрос выполняется куда шустрее, в разы.

Собственно, вопросы:

1) Правильно ли я понимаю, что:

Поскольку в WHERE нет указан shop_id - мы вынуждены пробегать по всем 480 партициям - и это сильно увеличивает "накладные расходы".
В случае же с таблицей t22 без партиционирования - он работает с одной партицией и соответственно отдаёт результат быстро?

2) Есть ли возможность через профилирование или еще через как-то увидеть то, к скольки партициям были реальные обращения и сколько времени это заняло? explain все же тут не даёт должной инфы, да и то - он порой на сложные запросы такую дичь выдаёт...

3) Для исследований порой очень удобно "остудить" кеш innodb_buffer_pool, дабы видеть реальную скорость, когда ничего не лежит в страницах буфера.
Перерыл русско и англоязычный интернет, большей частью все сводится к тому, что "не надо это вам", даже Пётр Зайцев по сути также пишет.
Усложняется еще несколько тем, что все на докерах в моей конфигурации.
В общем, если кто знает какой-то скрытый хак?..

Прошу знающих поделиться размышлениями smile


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

Неактивен

 

#2 27.11.2019 06:24:55

rgbeast
Администратор
MySQL Authorized Developer and DBA
Откуда: Москва
Зарегистрирован: 21.01.2007
Сообщений: 3845

Re: Запрос к таблице, где имеются партиции

Знаю ответ только на вопрос (1). Да, все верно, идет использование индекса на каждой отдельной партиции. Это линейно по числу партиций, а если бы был единый индекс, то сложность была бы логарифмической.

Неактивен

 

#3 27.11.2019 08:33:39

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6722

Re: Запрос к таблице, где имеются партиции

0. У тебя плохой PK. При партиционировании по shop хороший индекс должен бы по нему начинаться.

Если у тебя запросы в основном по товарам, то более правильное разбиение будет по ним, а не по магазинам.

1. Правильно понимаешь!

2. В случае с партиционированием обычно полезно знать 1 или много, это ты умеешь отличать. 300 или 480 — уже без разницы, это в любом случае плохой запрос. Точное количество, скорее всего, не достанешь. И ещё раз — ты не хочешь его знать smile

3. Тебе это не надо знать. Вот реально. На рабочей системе рабочая скорость как раз прогретая. Если ты хочешь оптимизировать, смотри планы. Если тебе нужно отвечать на вопрос «как оно себя ведёт сразу после перезагрузки сервера», то тогда перезагружай и сразу после — смотри. Все остальные способы не дают ответа на твой вопрос и не добавляют информации. Но, скорее всего, это ты тоже не хочешь smile

Неактивен

 

#4 27.11.2019 16:39:35

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

Re: Запрос к таблице, где имеются партиции

1, 2, 3 - accepted!

На предмет 0:

Есть немалое количество часто используемых запросов, которые идут по магазинам, а не по товарам. Почему партиция и была введена по полю shop_id.

>При партиционировании по shop хороший индекс должен бы по нему начинаться.
А чем непосредственно это будет лучше?
Для, допустим, приведенного в топике запроса?
Что изменится и чем станет лучше если, в t2 в PK поменяем поля местами, то есть сделаем  (`shop_id`,`good_id`)?


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

Неактивен

 

#5 27.11.2019 16:45:49

paulus
Администратор
MySQL Authorized Developer and DBA
Зарегистрирован: 22.01.2007
Сообщений: 6722

Re: Запрос к таблице, где имеются партиции

Это философский вопрос. Партиционирование хорошо только тогда, когда ты всегда (кроме явных агрегатов) бегаешь в одну партицию. А если ты бегаешь только в одну, то и индекс полезен только в правильном порядке (либо надеяться на то, что MySQL сообразит про pruning, и тогда выбросить вообще партиционирование из ключа). А в твоем порядке непонятно, зачем он там — он не может ни для чего использоваться.

Неактивен

 

#6 27.11.2019 18:14:41

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

Re: Запрос к таблице, где имеются партиции

Понятно, только количество случаев, когда MySQL не соображает как раз-таки про pruning (хотя по идее должен) что-то велико, судя по stackoverflow :-(.
Да и такие рассказы не прибавляют оптимизма.
Так что иду другим путем - выясняю, что же заставило ввести партицию и стоило ли это делать smile.

Спасибо всем откликнувшимся!


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

Неактивен

 

Board footer

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