Иван Пономарёв — Скрытая сложность повседневной задачи: отображение табличных данных

Табличный элемент управления (грид), отображающий данные из реляционной базы — один из самых распространённых компонентов пользовательского интерфейса, и все сталкивались с проблемами его производительности и удобства. Доклад покажет, что за кажущейся простотой грида скрываются интересные и не решённые до конца проблемы, требующие познаний в разных областях математики. Мы попробуем отыскать баланс между скоростью и удобством, параллельно научившись интерполяционному поиску, разобравшись в том, что же такое Collation Rules, и увидев, как можно «помогать» базе быстро извлекать нужные для нас данные. Этот доклад вошёл в 20-ку лучших на конференции JPoint-2017.

Transcript

1. Скрытая сложность повседневной задачи: отображение табличных данных Иван Пономарёв, КУРС/МФТИ @inponomarev 1
2. Чем мы занимаемся в КУРСе • Course Orchestra для создания бизнес- приложений • На Java — платформа, на Jython — код бизнес- логики • Бизнес-решения на платформе (банки, госструктуры, коммерческие организации) • PostgreSQL / MSSQL / Oracle 2
3. Все знают, что такое грид 3
4. Грид – это прокрутка… 4
5. Грид – это позиционирование… 5
6. Хороший грид должен • Работать быстро (не грузить БД!) • Работать удобно (не грузить пользователя!) • Одинаково быстро и удобно на 10 записях и на 1 млн записей. 6
7. 7 — И разве нет готовых решений? — А разве это сложно?
8. и вычитать данные таблицы в массив 8 Подход №1: просто взять
9. Подход №1: вычитать в массив • Прокрутка: доступ к «окну» == доступ к N-ному элементу массива —О(1) • Переход к записи: бинарный поиск в отсортированном массиве —O(log(N)) 9
10. Проблемы: • Первоначальная загрузка данных – O(N) 10
12. Проблемы: • Первоначальная загрузка данных – O(N) • «Тяжёлые» данные могут не «пролезть» в пропускную способность канала • Данные изменяются со временем – надо периодически обновлять массив 12
13. Подход №2: Переложим задачу на СУБД? • Хорошая новость: извлечение по ключу — «лёгкая» операция SELECT … WHERE k >= … LIMIT … Переход к записи по ключу работать будет быстро! 13
14. Подход №2: Переложим задачу на СУБД? Плохие новости: • Позиционирование: SELECT COUNT(*) WHERE k<… для определения диапазона полосы прокрутки и позиционирования. • Прокрутка: выбираем записи с N-ной: SELECT … OFFSET … LIMIT … 14
15. Подход №2: Переложим задачу на СУБД? • Имитация ОFFSET: – Oracle before 12: WITH a AS (YOUR_QUERY_HERE_WITH_ORDER_BY) SELECT * FROM (SELECT a.*, ROWNUM rnum FROM a WHERE rownum <= limit) WHERE rnum >= offset ORDER BY rnum – MS SQL Server before 2012: WITH a AS (SELECT ROW_NUMBER() OVER (ORDER BY %s) AS [limit_row_number], %s FROM %s) SELECT * FROM a WHERE [limit_row_number] >= offset AND [limit_row_number] < offset + limit 15
16. Проблемы: • COUNT и OFFSET — «дорогие» операции 16 “The rows skipped by an OFFSET clause still have to be computed inside the server; therefore a large OFFSET might be inefficient.” — Но чем мы ближе к концу набора, тем больше OFFSET!
17. – А зачем нам вообще грид со всеми записями?! • Пагинация 17
18. – А зачем нам вообще грид со всеми записями?! • Пагинация • Фильтрация и показ только лимитированного количества записей 18
19. – А зачем нам вообще грид со всеми записями?! • Пагинация • Фильтрация и показ только лимитированного количества записей • “Load more” 19
20. Бывает и так: 20 А где полоса прокрутки? (Обсуждение темы: https://habrahabr.ru/post/280157/)
21. — Может, строить «честный грид» и не надо? 21
22. — Надо, Федя! 22
23. Почему нужен «честный» грид? 1. Пользователи любят «простыни» данных 23
24. Почему нужен «честный» грид? 1. Пользователи любят «простыни» данных 2. Нужно видеть соседние записи 24
25. Почему нужен «честный» грид? 1. Пользователи любят «простыни» данных 2. Нужно видеть соседние записи 3. Переход к связанному списку 25
27. Как справляются другие ребята? — С переменным успехом. • PgAdmin3 — виснет • SQL Server Management Studio — по умолчанию ограничивает число строк • инструменты DB2, ERP Microsoft Navision (ок. 2003 г.) — сразу открывают и «крутят» любые таблицы 27
28. Наше решение • Disclaimer: – Не «серебряная пуля» – Не всё просто с сортировкой и фильтрацией – Нужны правильные индексы, LIKE с осторожностью • Но: – Неплохой компромисс между скоростью и удобством 28
29. Временное упрощение задачи (потом ограничения снимем!) 1. Первичный ключ таблицы состоит из одного поля k с типом INT 2. Сортировка по первичному ключу 29 CREATE TABLE test ( k INT NOT NULL PRIMARY KEY, descr VARCHAR(20) );
30. Мысленный эксперимент 1. У 1-й записи k = 0. 2. У 1 000-й записи k = 10 000. 3. Записи отсортированы по k. • Вопрос: какое примерно значение k будет иметь запись с номером 500? 30
31. Идея «Угадаем» взаимосвязь между ключом записи k и её порядковым номером λ при помощи линейной интерполяции? 31
32. Насколько хороша линейная интерполяция? (50 из 200) 32 порядковый номер записи значение ключа
33. Случай 10 из 200 — похуже… 33 значение ключа порядковый номер записи
34. Вероятность наличия ровно λ записей с ключом <k 34
35. Теоретическая оценка границ, мат. ожидания и СКО 35
36. Неравномерное распределение ключей — кусочная интерполяция! 36
37. Снимаем ограничения • Ключ составной — нумеруем возможные комбинации так, чтобы описывались (большим) целым числом • Нужна сортировка — добавляем сортировочное поле к началу списка ключевых • Ключ содержит не только INT-поля — нумеруем значения (большими) целыми! 37
38. интерполяционн ая таблица Компоненты алгоритма 38 нумератор интер- полятор генерато р запросов
39. Пример • Справочник улиц КЛАДР • 1 175 430 записей • ORDER BY name, code • CREATE INDEX ix_street ON kladr.street (name, code) 39
40. Пользователь прокрутил записи… 40 изменилась позиция бегунка вычисление порядкового номера вычисление ключа запрос ближайшей записи БД Нумератор Интерполятор Полоса прокрутки
41. Интерполяцией находим порядковый номер ключа 41 изменилась позиция бегунка вычисление порядкового номера вычисление ключа запрос ближайшей записи БД Нумератор Интерполятор Полоса прокрутки
42. Вычисляем примерные значения полей ключа по примерному номеру 42 изменилась позиция бегунка вычисление порядкового номера вычисление ключа запрос ближайшей записи БД Нумератор Интерполятор Полоса прокрутки
43. Запрашиваем записи, ближайшие к найденным примерным значениям полей 43 изменилась позиция бегунка вычисление порядкового номера вычисление ключа запрос ближайшей записи БД Нумератор Интерполятор Полоса прокрутки name: Мд‘Ukp ®$-'•ДЫ9uj?¦АОZ‡У›:"ZG818*$‰9Ювэ‘ code: я$зec§}0,&-»гФ4%эR SELECT ... FROM ... WHERE ("name", "code") >= (?, ?) LIMIT ...
44. Прокрутка в реальном времени 44
45. Асинхронное уточнение позиции 45 запрос ближайшей записи запрос реального номера записи SELECT COUNT(*) FROM kladr.street WHERE ("name", "code") < (?, ?) 586038 (А хотели 584600!)
46. Позиционирование 46
47. Позиционирование 47 обращение к таблице по ключу вычисление порядкового номера позиция бегунка (обр. интерп.) изменение позиции бегунка БД Нумератор Интерполятор Полоса прокрутки запрос реального номера записи SELECT ... FROM ... WHERE ("name", "code") >= (?, ?) LIMIT ... SELECT COUNT(*) FROM ... WHERE ("name", "code") < (?, ?)
48. Вычисляем порядковый номер ключа 48 обращение к таблице по ключу вычисление порядкового номера позиция бегунка (обр. интерп.) изменение позиции бегунка БД Нумератор Интерполятор Полоса прокрутки name: Чаадаева code: 42000010000057000
49. Интерполяцией находим примерную позицию бегунка 49 обращение к таблице по ключу вычисление порядкового номера позиция бегунка (обр. интерп.) изменение позиции бегунка БД Нумератор Интерполятор Полоса прокрутки
50. Выставляем бегунок на позицию 50 обращение к таблице по ключу вычисление порядкового номера позиция бегунка (обр. интерп.) изменение позиции бегунка БД Нумератор Интерполятор Полоса прокрутки запрос реального номера записи
51. Осталось разобраться в одном вопросе… Как работает нумератор? 51
52. Функция нумератора • 52
53. • С BIT и INT всё просто — они нумеруют сами себя • Даты сводимы к целому типу • Составные ключи? • Строки? 53
54. Составной ключ/сортировка: ORDER BY X, Y • 54
55. Составной ключ Если умеем работать с парами значений — 55
56. Составной ключ Если умеем работать с парами значений — — то можем работать и с произвольными N-ками значений 56
57. Как отыскивать записи от составного ключа? • where (k1, k2) >= (K1, K2) (работает только в PostgreSQL) • where k1 > K1 or (k1 = K1 and (k2 >= K2 )) не использует индекс! • where k1 >= K1 and (k1 > K1 or (k2 >= K2 )) 57
58. Нумерация строк 58
59. Интерполяция строк 59
61. Пустая строка, строка из одной буквы, из двух букв… 61
62. 62 ‘’ — 0, ‘а’ — 1, ‘б’ — ? 2 — неправильный ответ: ‘абак’, ‘араб’, ‘арка’…
63. • 63 • Общая формула: • Постаравшись, можно найти алгоритм обратного вычисления строки
64. Ничего не упустили?.. Пишем тест: ИЖЕВСК 297544850130486 ИРКУТСК 309090873152681 ЙОШКАР-ОЛА 343626219328684
65. Реальность: SELECT name FROM city ORDER BY name (PostgreSQL с настройками по умолчанию) 65
66. Правила сортировки (collation rules) Результат «подгонки» правил к PostgreSQL: …<в,В<г,Г<д,Д<е,Е;ё,Ё<ж,Ж<з,З<и,И;й,Й<к,К<л,Л… java.text.RuleBasedCollator extends java.text.Collator java.text.CollationKe y 66
67. Строка с точки зрения Collation Rule 67 Й о ш к а р - О л а 64 69 73 65 5C 6B 01 69 66 5C 01 00 00 00 00 00 00 00 00 00 01 00 00 00 00 00 00 01 00 00
68. Насколько всё это быстро? JMH-замер: • 10000 ops/sec — для 200 символов • 30000 ops/sec — для 50 символов 68
69. Первоначальное заполнение интерполяционной таблицы 69
70. А также не упомянуты… • Отработка и сортировка NULL-значений • Режим прокрутки на малый шаг • Анализ «качества» интерполяционных точек …и другие мелкие, но важные детали “God is in the detail.” 70
71. Выводы 71 1. Создавать фреймворк — дело трудное и неблагодарное
72. Выводы 72 2. Всегда помните про О-оценку сложности алгоритмов
73. Выводы 73 3. По возможности, избегайте COUNT и OFFSET
74. Выводы 4. SQL не всемогущ. Ему можно и нужно «помогать» 5. Помните, что сортировка строк существенно зависит от Collation. Следите за текущим Collation. 6. «Приблизительное» решение бывает намного быстрее «точного» и практически столь же хорошо 74
75. Ссылки • Демо-пример: https://github.com/inponomarev/lyragrid-demo • Платформа C-Orchestra: http://corchestra.ru «Реализация грида» – https://habrahabr.ru/post/278773/ – https://habrahabr.ru/post/279083/ • «Быстро/неудобно — медленно/удобно» – https://habrahabr.ru/post/280157/ 75
76. Вопросы? ponomarev@corchestra.ru @inponomarev 76