|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
В таблице data_sgrc есть проиндексированное поле period типа date (всегда 1-е число месяца). Запрос №1 выполняется ~0.5 сек для 1128204 записей: Код: sql 1. 2. 3. 4. 5.
Explain: idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEdnullrefi_per|fk_contri_per4const79974100.00Using where1SIMPLEcnulleq_refPRIMARYPRIMARY4direct_contracts.d.id_contragent1100.00null Но стоит только сделать выборку не за 1 месяц, а, к примеру, за 3: Код: sql 1. 2. 3. 4. 5.
Explain: idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEdnullALLi_per|fk_contrnullnullnull110837723.04Using where; Using filesort1SIMPLEcnulleq_refPRIMARYPRIMARY4direct_contracts.d.id_contragent1100.00null как индекс перестает использоваться и запрос №2 выполняется уже ~5 сек. Что тут можно/нужно сделать? MySQL 8.0.27. Может CTE тут выручит? Или как-то изменив запрос/индексы? Ведь даже выполняя запрос №1 трижды, каждый раз для следующего месяца, займет всего ~1.5 сек вместо 5 сек для запроса №2. ... |
|||
:
Нравится:
Не нравится:
|
|||
10.02.2022, 10:45 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
LiYing каждый раз для следующего месяца, займет всего ~1.5 сек Это предположение или Вы проверяли? ... |
|||
:
Нравится:
Не нравится:
|
|||
10.02.2022, 10:54 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
SergiiW LiYing каждый раз для следующего месяца, займет всего ~1.5 сек Это предположение или Вы проверяли? Проверял, пожалуйста, вот лог: Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25.
... |
|||
:
Нравится:
Не нравится:
|
|||
10.02.2022, 11:10 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
LiYing, Сколько записей выдает каждый из запросов? попробуйте сделать OPTIMIZE TABLE для обеих таблиц. ... |
|||
:
Нравится:
Не нравится:
|
|||
10.02.2022, 11:43 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
miksoft LiYing, Сколько записей выдает каждый из запросов? попробуйте сделать OPTIMIZE TABLE для обеих таблиц. Делал - "Table does not support optimize, doing recreate + analyze instead", время не изменилось. Результаты запросов: №1pc1c22021-10-0150741837№2pc1c22021-10-01507418372021-11-01510422112021-12-0151442516 ... |
|||
:
Нравится:
Не нравится:
|
|||
10.02.2022, 11:56 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
LiYing, маленько офтопа Вот интересно, учитывая, что "ON d.id_contragent=c.id", как изменится время и план выполнения, если заменить COUNT(c.id) на COUNT(d.id_contragent)? Кстати, эти поля проиндексированы? в обеих таблицах? ... |
|||
:
Нравится:
Не нравится:
|
|||
10.02.2022, 13:15 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
paver, №1 Код: sql 1. 2. 3. 4. 5.
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEdnullrefi_per|fk_contri_per4const85562100.00Using where1SIMPLEcnulleq_refPRIMARYPRIMARY4direct_contracts.d.id_contragent1100.00null №2 Код: sql 1. 2. 3. 4. 5.
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEdnullALLi_per|fk_contrnullnullnull110843623.04Using where; Using filesort1SIMPLEcnulleq_refPRIMARYPRIMARY4direct_contracts.d.id_contragent1100.00null c.id (из DDL) Код: sql 1.
d.id_contragent (из DDL) Код: sql 1. 2.
Время выполнения запросов после замены примерно то же самое. ... |
|||
:
Нравится:
Не нравится:
|
|||
10.02.2022, 13:32 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
В первом случае селективность индекса 79974 / 1128204 ~ 7%. В принципе и это много, где-то на границе оптимальности. Но тем не менее использование индекса всё ещё возможно. Во втором же случае селективность , вероятно, на уровне 20%, т.е. использование индекса заведомо неэффективно. Что и видно из плана - сервер предпочитает сканирование таблицы. PS. И вообще, где CREATE TABLE обеих таблиц? о статистике данных даже не говорю... ... |
|||
:
Нравится:
Не нравится:
|
|||
10.02.2022, 17:29 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
Akina PS. И вообще, где CREATE TABLE обеих таблиц? о статистике данных даже не говорю... Про статистику не понял (что надо? всё покажу :)), а CREATE вот: Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28. 29. 30. 31. 32. 33. 34. 35. 36. 37. 38. 39. 40. 41. 42. 43. 44. 45. 46. 47. 48. 49. 50. 51. 52. 53. 54. 55. 56. 57. 58. 59. 60. 61. 62. 63. 64. 65. 66. 67.
... |
|||
:
Нравится:
Не нравится:
|
|||
10.02.2022, 20:13 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
Индексы i_per и fk_contr - ниачём. Создай вместо них один по (id_contragent, period) . ... |
|||
:
Нравится:
Не нравится:
|
|||
10.02.2022, 20:39 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
Akina Индексы i_per и fk_contr - ниачём. Создай вместо них один по (id_contragent, period) . Сделано: Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28. 29. 30. 31. 32.
Хм... план изменился (и стал одинаков и для предположения paver "если заменить COUNT(c.id) на COUNT(d.id_contragent)"): Код: sql 1. 2. 3. 4. 5. 6.
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEcnullindexPRIMARYmk_idmkd9null48270100.00Using index1SIMPLEdnullrefid_contr_perid_contr_per9direct_contracts.c.id|const1100.00Using index Код: sql 1. 2. 3. 4. 5. 6.
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEcnullindexPRIMARYmk_idmkd9null48270100.00Using index; Using temporary; Using filesort1SIMPLEdnullrefid_contr_perid_contr_per5direct_contracts.c.id2611.11Using where; Using index >№1 Query Time: 0,435s >№2 Query Time: 2,562s Неожиданно, но уже лучше для диапазонного запроса №2 в два раза, а для №1 время осталось примерно тем же. Еще какие-нибудь предположения, пожалуйста! ... |
|||
:
Нравится:
Не нравится:
|
|||
11.02.2022, 08:26 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
Akina, а конструкция "INDEX `mk_idmkd` (`id_mkd`, `id`) USING BTREE" вообще осмысленна? ... |
|||
:
Нравится:
Не нравится:
|
|||
11.02.2022, 10:09 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
А что с ней не так, с этой конструкцией? композитный индекс из двух полей, может, где-то для какого-то запроса ну очень нужный... ... |
|||
:
Нравится:
Не нравится:
|
|||
11.02.2022, 11:42 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
Akina А что с ней не так, с этой конструкцией? композитный индекс из двух полей, может, где-то для какого-то запроса ну очень нужный... Мне казалось включение первичного ключа в составной индекс ненужным излишеством. Наверное в башке попутались понятия первичный ключ и внутренний идентификатор ... |
|||
:
Нравится:
Не нравится:
|
|||
11.02.2022, 13:09 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
Akina А что с ней не так, с этой конструкцией? композитный индекс из двух полей, может, где-то для какого-то запроса ну очень нужный... Верно, очень частый запрос, например: Код: sql 1. 2. 3. 4.
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEcnullrefPRIMARY|mk_idmkdmk_idmkd5const15100.00null1SIMPLEdnullrefid_contr_perid_contr_per9direct_contracts.c.id|const1100.00null Этот индекс как раз для такого. А по сути вопроса темы, можете еще что-то подсказать? Что изменить, добавить может? ... |
|||
:
Нравится:
Не нравится:
|
|||
11.02.2022, 13:15 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
Даже такой простой запрос не использует индекс: Код: sql 1. 2. 3. 4. 5.
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEdata_sgrcnullALLid_contr_pernullnullnull110843611.11Using where; Using temporary; Using filesort > Query Time: 6,1s !!! :( А данных-то в data_sgrc прибавляется каждый месяц по несколько десятков тысяч. И дальше время выборки будет только расти, как я понимаю. ... |
|||
:
Нравится:
Не нравится:
|
|||
11.02.2022, 13:33 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
LiYing А по сути вопроса темы, можете еще что-то подсказать? Что изменить, добавить может? Я (как любитель) еще пообследовал бы ситуацию. - в какой момент происходит скачек прироста времени исполнения. т.е. посмотреть результаты для периода в 2 месяца. - как быстро растет время в зависимости от периода. т.е. замерить время для 3, 6, 9 и 12 месяцев, например (вдруг не критично). - как ведет запрос, если каунты считать отдельно LiYing Даже такой простой запрос не использует индекс: Для однотабличных запросов можно попробовать покрывающий индекс. И сделать принудительным использование индексов ... |
|||
:
Нравится:
Не нравится:
|
|||
11.02.2022, 13:57 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
paver LiYing А по сути вопроса темы, можете еще что-то подсказать? Что изменить, добавить может? Я (как любитель) еще пообследовал бы ситуацию. - в какой момент происходит скачек прироста времени исполнения. т.е. посмотреть результаты для периода в 2 месяца. - как быстро растет время в зависимости от периода. т.е. замерить время для 3, 6, 9 и 12 месяцев, например (вдруг не критично). - как ведет запрос, если каунты считать отдельно Свел замеры в табличку: периодов в запросеQuery Time для SELECT p c1Query Time для SELECT p c1 c210.527s0.589s22.482s2.499s32.679s2.688s42.888s3.042s53.185s3.121s63.394s3.487s73.574s3.583s83.736s3.802s94.006s4.056s103.874s3.609s114.485s4.208s124.713s4.669s Выбивающееся из общей закономерности роста времени для 9 месяцев обусловлено, видимо, общей нагрузкой на сервер в период замера. ... |
|||
:
Нравится:
Не нравится:
|
|||
11.02.2022, 14:33 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
LiYing Akina Индексы i_per и fk_contr - ниачём. Создай вместо них один по (id_contragent, period) . Сделано: Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22. 23. 24. 25. 26. 27. 28. 29. 30. 31. 32.
Хм... план изменился (и стал одинаков и для предположения paver "если заменить COUNT(c.id) на COUNT(d.id_contragent)"): Код: sql 1. 2. 3. 4. 5. 6.
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEcnullindexPRIMARYmk_idmkd9null48270100.00Using index1SIMPLEdnullrefid_contr_perid_contr_per9direct_contracts.c.id|const1100.00Using index Код: sql 1. 2. 3. 4. 5. 6.
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEcnullindexPRIMARYmk_idmkd9null48270100.00Using index; Using temporary; Using filesort1SIMPLEdnullrefid_contr_perid_contr_per5direct_contracts.c.id2611.11Using where; Using index >№1 Query Time: 0,435s >№2 Query Time: 2,562s Неожиданно, но уже лучше для диапазонного запроса №2 в два раза, а для №1 время осталось примерно тем же. Еще какие-нибудь предположения, пожалуйста! Как-то странно, количество результирующих записей разное, но измеряем же время ... |
|||
:
Нравится:
Не нравится:
|
|||
11.02.2022, 15:28 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
LiYing пропущено... Выбивающееся из общей закономерности роста времени для 9 месяцев обусловлено, видимо, общей нагрузкой на сервер в период замера. Ну, т.е основную нагрузку дает COUNT(DISTINCT c.id_mkd) Увеличение периода влияет некритично (с 2 до 12 - прирост менее чем в 2 раза) Идея по оптимизации: в запросе используются индексы mk_idmkd и id_contr_per, у которых идентификатор контрагента (а это поле связи таблиц) на разных местах. Попробуй поменять порядок в mk_idmkd (id_mkd, id). Ну и последнее: если запрос регулярный и критический, добавить поле id_mkd во вторую таблицу и считать каунты только по ней ... |
|||
:
Нравится:
Не нравится:
|
|||
14.02.2022, 07:01 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
paver Попробуй поменять порядок в mk_idmkd (id_mkd, id) Поменял и оптимизировал таблицу, теперь план для: Код: sql 1. 2. 3. 4. 5.
такой idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEcnullindexPRIMARYmk_idmkd9null45586100.00Using index; Using temporary; Using filesort1SIMPLEdnullrefid_contr_perid_contr_per5direct_contracts.c.id2711.11Using where; Using index Отличие от инвертированного индекса только в чуть меньшем количестве rows (45586 < 48270). Но время выполнения запроса не поменялось, к сожалению. Видимо, и правда остается только "добавить поле id_mkd во вторую таблицу и считать каунты только по ней", если не будет других предложений. ... |
|||
:
Нравится:
Не нравится:
|
|||
14.02.2022, 08:35 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
LiYing если не будет других предложений. Еще мысли чайника. Все необходимые для запроса данные содержатся в индексах, СУБД нет никакой необходимости читать сами таблицы. Для одного месяца так и происходит, при увеличении периода подключается Using filesort. Может следует поиграться с параметрами СУБД, добавить памяти для кешей? Какие именно и сколько - не подскажу, увы. ... |
|||
:
Нравится:
Не нравится:
|
|||
14.02.2022, 10:51 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
Я бы скорее думал о партиционировании по дате. Это позволит резко уменьшить количество читаемых данных (partition pruning). ... |
|||
:
Нравится:
Не нравится:
|
|||
14.02.2022, 10:52 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
Akina Я бы скорее думал о партиционировании по дате. Это позволит резко уменьшить количество читаемых данных (partition pruning). Не сталкивался еще с партиционированием, буду изучать - спасибо! paver, тоже спасибо за участие. Про параметры почитаю. ... |
|||
:
Нравится:
Не нравится:
|
|||
14.02.2022, 11:56 |
|
Как оптимизировать выборку по диапазону?
|
|||
---|---|---|---|
#18+
Akina, Partition pruning при беглом ознакомлении показалось многообещающим, но обнаружилась печалька: Foreign keys not supported for partitioned InnoDB tables. - https://dev.mysql.com/doc/refman/8.0/en/partitioning-limitations.html а у меня в табличке: Код: sql 1. 2. 3. 4. 5. 6. 7. 8. 9. 10.
... |
|||
:
Нравится:
Не нравится:
|
|||
14.02.2022, 13:42 |
|
|
Start [/forum/topic.php?fid=47&msg=40133920&tid=1827770]: |
0ms |
get settings: |
20ms |
get forum list: |
7ms |
check forum access: |
1ms |
check topic access: |
1ms |
track hit: |
59ms |
get topic data: |
5ms |
get forum data: |
1ms |
get page messages: |
563ms |
get tp. blocked users: |
0ms |
others: | 10ms |
total: | 667ms |
0 / 0 |