MySQL - Лучший метод для обработки этих иерархических данных?
Это - продолжение:
MySQL - действительно ли возможно получить все подэлементы в иерархии?
У меня есть таблица модели списка смежности произвольной глубины (я в точке, что я могу преобразовать ее во вложенную модель набора.
Я считал данные MySQL о том, как использовать вложенную модель набора, хотя это, казалось, стало все больше сложным и очень сложным, чтобы сделать основные функции, такие как вставка, обновление и удаление.
Другой блог, показывающий, как использовать триггерную систему со смежностью, перечисляет модель для хранения таблицы предков, которая связывает каждый объект с его предками.
Прямо сейчас я должен смочь возвратить список всех детей данного узла, изменить или удалить их. Эта иерархическая структура не будет изменяться все время когда-то созданный, но будет массовая сумма иерархических структур.
Эти три метода, которые я вижу:
Созданный Хранимая процедура, которая сделала бы рекурсивный запрос, который возвращает всех детей.
Преобразуйте во Вложенную Модель Набора, которая потребовала бы, чтобы войти в сложности и возможно создать хранимую процедуру, чтобы добавить, отредактировать и удалить в этом.
Составьте Таблицу Предка, описанную выше на, вставляют/удаляют триггеры для обработки всех данных.
Если существуют другие методы, я не исследую, сообщите мне, и я обновлю этот список.
5 ответов
Quassnoi провел несколько тестов производительности на модели вложенных множеств и модели списка смежности и задокументировал результаты и рекомендации в своем сообщении в блоге Список смежности против вложенных наборы: MySQL . Краткое резюме:
- Вложенные наборы быстрее извлекают все дочерние узлы или все родительские узлы.
- Вложенные наборы - плохая идея, если вам часто нужно обновлять таблицу.
Вот вывод из его статьи:
В MySQL предпочтительнее использовать модель вложенных множеств, если обновления иерархической структуры происходят нечасто и есть возможность заблокировать таблицу на время обновления (что может займите минуты на длинном столе).
Это подразумевает создание таблицы с использованием механизма хранения MyISAM, создание ограничивающего прямоугольника типа GEOMETRY, как описано выше, его индексацию с помощью индекса SPATIAL и сохранение уровня в таблице.
Если обновления таблицы происходят часто или невозможно заблокировать таблицу на длительный период времени, подразумеваемый обновлением, то для хранения иерархических данных следует использовать модель списка смежности.
Это требует создания функции для запроса таблицы.
В оставшейся части статьи показано, как определить таблицу, реализовать запросы и дать измерения производительности. Использование пространственного индекса - отличная идея для повышения производительности модели вложенных множеств, которая может быть для вас новой.
Если вы также рассматриваете подходы без MySQL, возможно, вам стоит взглянуть на PostgreSQL , еще одну бесплатную базу данных с открытым исходным кодом. PostgreSQL поддерживает рекурсивные запросы в форме рекурсивных общих табличных выражений , которые делают запросы иерархических данных проще, чем в MySQL, а также обеспечивают лучшую производительность. Куассной также написал статью Список смежности и вложенные наборы: PostgreSQL , в которой показаны подробности.
Пока мы говорим о рассмотрении других подходов, стоит упомянуть также базу данных Oracle. У Oracle также есть собственное расширение CONNECT BY , которое упрощает и ускоряет запросы к иерархическим данным. Статья Quassnoi Список смежности по сравнению с вложенными наборами: Oracle снова рассматривает детали производительности. Запрос, который вам нужен для получения всех дочерних элементов, в этом случае чрезвычайно прост:
SELECT *
FROM yourtable
START WITH id = 42
CONNECT BY parent = PRIOR id
Возможно, вам стоит подумать об использовании документно-ориентированной базы данных, такой как MongoDB . Это могло бы сделать вашу жизнь намного проще.
Я бы всегда выбрал вложенный набор для простоты сдвига и удобства. Я всегда предлагаю эту статью . Он отлично показывает запросы, которые нужны для работы с такими иерархическими данными. Единственный недостаток, который я вижу здесь, заключается в том, что он может работать медленнее при вставке / обновлении новых записей, когда иерархия достигает определенного уровня сложности, но чтение происходит быстрее, чем многие другие решения, которые я видел.
Приведу пример из статьи выше:
SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM category AS t1
LEFT JOIN category AS t2 ON t2.parent = t1.category_id
LEFT JOIN category AS t3 ON t3.parent = t2.category_id
LEFT JOIN category AS t4 ON t4.parent = t3.category_id
WHERE t1.name = 'ELECTRONICS';
+-------------+----------------------+--------------+-------+
| lev1 | lev2 | lev3 | lev4 |
+-------------+----------------------+--------------+-------+
| ELECTRONICS | TELEVISIONS | TUBE | NULL |
| ELECTRONICS | TELEVISIONS | LCD | NULL |
| ELECTRONICS | TELEVISIONS | PLASMA | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | MP3 PLAYERS | FLASH |
| ELECTRONICS | PORTABLE ELECTRONICS | CD PLAYERS | NULL |
| ELECTRONICS | PORTABLE ELECTRONICS | 2 WAY RADIOS | NULL |
+-------------+----------------------+--------------+-------+
6 rows in set (0.00 sec)
Что касается SQL, я не думаю, что он может стать проще и красивее;)
Я понятия не имею о хранимой процедуре ] способ. Но поскольку он включает рекурсию (в вашем случае), я не знаю, будет ли он быстрым при многих уровнях иерархии. Я полагаю, вы можете попробовать.
Однажды мне пришлось хранить сложную иерархическую систему ведомости материалов произвольной глубины в SQL-подобном диспетчере баз данных, который на самом деле не подходил для этой задачи, и в итоге это привело к беспорядку. и сложные индикаторы, определения данных, запросы и т. д. После перезапуска с нуля использование диспетчера баз данных для предоставления только API для чтения и записи записей на простых индексированных ключах и выполнение всего фактического ввода / манипуляции / отчетности во внешнем коде, конечный результат был быстрее реализован, проще для понимания и проще в обслуживании и улучшении. Наиболее сложным из необходимых запросов было, по сути, SELECT A FROM B.
Итак, вместо того, чтобы встраивать логику и операции в ограничения MySQL, подумайте о том, чтобы вытащить код для выполнения того, что вы хотите, и полагаться на MySQL только для получения нижнего уровня. / ставит.
Я считаю, что при работе с иерархическими наборами данных лучше всего подходить к этому с учетом кеширования. Одно из основных преимуществ такого способа решения этой проблемы заключается в том, что он не требует денормализации вашей базы данных во что-то, что может быть труднее изменить.
Поскольку поиск в кучах памяти (memcache, redis и т. Д.) Выполняется намного быстрее, чем SQL для простых разрешений id -> data , я бы использовал их для кеширования списка идентификаторов прямых потомков для каждого узел. Таким образом, вы можете получить достойную производительность с помощью рекурсивного алгоритма для создания полного списка для любого узла.
Чтобы добавить / удалить новый узел, вам нужно только сделать недействительным его прямой родительский кеш O (1) .
Если этого недостаточно, вы можете добавить еще один уровень кеша в список всех дочерних узлов узла на каждом узле. Чтобы это работало с прилично изменяемым набором данных, вы должны записать производительность кеша (соотношение свежих / кэшированных попаданий) каждого узла и установить уровень допуска для того, когда сохранять кеш. Это также может быть сохранено в куче памяти, поскольку это неважные данные.
Если вы используете эту более продвинутую модель кэширования, вам нужно будет отметить, что эти полные списки дочерних узлов должны быть признаны недействительными при изменении любого из его дочерних узлов O (log n) .
Когда у вас есть список идентификаторов детей, вы можете использовать синтаксис SQL WHERE id IN (id1, id2, ....) для запроса того, что вы хотите.