MySQL 5.5: Что из нижеперечисленного является лучшим хранилищем для поля text/varchar в innodb?

Question

Требование:

Страница № 1 -> Показать пользователей и 1-2-строчный просмотр их последних 10 сообщений в блоге

Страница № 2 -> Отображение одного блога с полным текстом.

Способ 1:

MySQL table ->   userid -> varchar 50
                 post_id -> integer
                 post_title -> varchar 100
                 post_description -> varchar 10000

для страницы № 1 выберите user_id, post_title, post_description из blog_table . и подстрока post_description используется для отображения предварительного просмотра в листинге.

для страницы № 2 выберите user_id, post_title, post_description, где post_id = N

Способ 2:

 MySQL table ->   userid -> varchar 50
                  post_id -> integer
                  post_title -> varchar 100
                  post_brief -> varchar 250
                  post_description -> text

для страницы № 1 выберите user_id, post_title, post_brief из blog_table .

для страницы № 2 выберите user_id, post_title, post_description, где post_id = N

Означает ли сохранение производительности два столбца, один краткий как varchar, а другой полный как текст (поскольку он обращается к файловой системе и должен запрашиваться только при необходимости)?

Так как, метод 2, будет хранить только указатель на текст в строке, тогда как метод 1 будет хранить полную строку varchar 10K в строке. Влияет ли это на объем табличных данных, которые могут находиться в оперативной памяти, и, следовательно, на производительность чтения запросов?

Answer 1

Производительность SQL-запросов в основном зависит от JOIN, предложений WHERE, GROUP BY и ORDER BY, а не от извлеченных столбцов. Столбцы оказывают заметное влияние на скорость запроса только в том случае, если извлекается значительно больше данных, которые могут быть переданы по сети для обработки вашим языком программирования. Это не тот случай, здесь.

Короткий ответ: Разница в производительности между двумя предлагаемыми настройками, вероятно, будет очень небольшой.

Для хорошей скорости, ваш post_id столбец должен иметь (уникальный) индекс. Вы не выбираете, не сортируете и не группируете по любому другому столбцу, поэтому данные могут поступать прямо из таблицы, что является очень быстрым процессом.

Вы говорите о "страницах" здесь, так что я предполагаю, что они будут представлены пользователям - кажется маловероятным, что вы захотите показать таблицу тысяч сообщений в блоге на той же странице человеку, для этого вы, вероятно, делаете на самом деле в ваших утверждениях есть пункты ORDER BY и / или LIMIT, которые вы не включили в свой вопрос.

Но давайте посмотрим немного глубже во все это. Допустим, мы на самом деле читаем тонны столбцов TEXT непосредственно с жесткого диска, разве мы не достигнем максимальной скорости чтения диска? Не будет ли получение только VARCHAR(250) быстрее, тем более что это избавит вас от лишнего вызова LEFT()?

Мы можем получить вызов LEFT() со стола очень быстро. Строковые функции действительно быстрые - в конце концов, именно процессор просто обрезает некоторые данные, что является действительно быстрым процессом. Единственный раз, когда они создают заметную задержку, это когда они используются в предложениях WHERE, JOIN и т. Д., Но это не потому, что эти функции работают медленно, а потому, что их приходится запускать много раз (возможно, миллионы), чтобы генерировать хотя бы одну строку результатов, и тем более, потому что эти виды использования часто не позволяют базе данных правильно использовать свои индексы.

В итоге все сводится к тому, насколько быстро MySQL может читать содержимое таблицы из базы данных. А это, в свою очередь, зависит от используемого вами механизма хранения и его настроек. MySQL может использовать несколько механизмов хранения, включая (но не ограничиваясь ими) InnoDB и MyISAM. Оба этих движка предлагают разные макеты файлов для больших объектов, таких как столбцы TEXT или BLOB (но, как ни странно, также VARCHAR). Если столбец TEXT хранится на странице, отличной от остальной строки, механизм хранения должен извлечь две страницы для каждой строки. Если он хранится вместе с остальными, это будет всего одна страница. Для последовательной обработки это может быть серьезным изменением производительности.

Вот небольшая справочная информация по этому вопросу:

• Хранение BLOB-объектов в InnoDB
• Динамическое расположение файлов MyISAM и сжатых данных

Длинный ответ: это зависит:)

Вам нужно будет сделать несколько тестов производительности на своем собственном оборудовании, чтобы действительно определить, какая схема на самом деле быстрее. Учитывая, что вторая установка вводит избыточность с дополнительным столбцом, в большинстве сценариев она может работать хуже. Он будет работать лучше, если - и только если - структура таблицы позволит более короткому столбцу VARCHAR вписаться в ту же страницу на диске, в то время как длинный столбец TEXT окажется на другой странице.

Изменить: Подробнее о столбцах ТЕКСТ и производительности

Кажется, существует распространенное заблуждение о BLOB и обработке в памяти. Довольно много страниц (включая некоторые ответы здесь на Stackru - я постараюсь найти их и дать дополнительный комментарий) заявляют, что столбцы TEXT (и все другие большие двоичные объекты) не могут быть обработаны в памяти MySQL, и как таковые всегда боров производительности. Это неправда. Что действительно происходит, так это:

Если вы выполняете запрос, включающий столбец TEXT, и для этого запроса требуется временная таблица, то MySQL должен будет создать эту временную таблицу на диске, а не в памяти, потому что MySQL MEMORY механизм хранения не может обрабатывать столбцы TEXT. Смотрите этот связанный вопрос.

Документация MySQL утверждает это (параграф одинаков для всех версий от 3.2 до 5.6):

Экземпляры столбцов BLOB или TEXT в результате запроса, который обрабатывается с использованием временной таблицы, заставляют сервер использовать таблицу на диске, а не в памяти, поскольку механизм хранения MEMORY не поддерживает эти типы данных (см. Раздел 8.4.3.3, "Как MySQL использует внутренние временные таблицы"). Использование диска влечет за собой снижение производительности, поэтому включайте столбцы BLOB или TEXT в результат запроса, только если они действительно необходимы. Например, избегайте использования SELECT *, который выбирает все столбцы.

Это последнее предложение, которое смущает людей - потому что это просто плохой пример. Просто SELECT * не будет затронута этой проблемой производительности, потому что она не будет использовать временную таблицу. Если такой же выбор был, например, упорядочен по неиндексированному столбцу, ему пришлось бы использовать временную таблицу и эта проблема была бы затронута. Использовать EXPLAIN Команда в MySQL, чтобы узнать, понадобится ли запросу временная таблица или нет.

Кстати: ничего из этого не влияет на кеширование. Столбцы TEXT можно кэшировать, как и все остальное. Даже если для запроса нужна временная таблица и она должна быть сохранена на диске, результат все равно можно будет кэшировать, если у системы есть ресурсы для этого, а кэш не аннулирован. В этом отношении столбец TEXT такой же, как и все остальное.

Редактировать 2: Подробнее о столбцах TEXT и требованиях к памяти...

MySQL использует механизм хранения для извлечения записей с диска. Затем он буферизует результаты и последовательно передает их клиенту. Следующее предполагает, что этот буфер заканчивается в памяти, а не на диске (см. Выше, почему)

Для столбцов TEXT (и других BLOB) MySQL будет буферизовать указатель на фактический BLOB. Такой указатель использует только несколько байтов памяти, но требует, чтобы фактическое содержимое TEXT было извлечено с диска при передаче строки клиенту. Для столбцов VARCHAR (и всего остального, кроме BLOB) MySQL будет буферизовать фактические данные. Обычно это будет занимать больше памяти, потому что большинство ваших текстов будет больше, чем просто несколько байтов. Для вычисляемых столбцов MySQL также буферизует фактические данные, как с VARCHAR.

Пара замечаний по этому поводу: Технически, BLOB-объекты также будут буферизованы, когда они будут переданы клиенту, но только по одному за раз - и для больших BLOB-объектов, возможно, не полностью. Так как этот буфер освобождается после каждой строки, это не имеет большого эффекта. Кроме того, если BLOB фактически хранится на той же странице, что и остальная часть строки, он может в конечном итоге рассматриваться как VARCHAR. Честно говоря, у меня никогда не было требования возвращать множество BLOB-объектов в одном запросе, поэтому я никогда не пытался.

Теперь давайте на самом деле ответим на вопрос (теперь отредактированный):

Страница 1. Обзор пользователей и короткие фрагменты постов в блоге.

Ваши варианты в значительной степени эти запросы

SELECT userid, post_title, LEFT(post_description, 250) FROM `table_method_1`  <-- calculated based on a VARCHAR column
SELECT userid, post_title, LEFT(post_description, 250) FROM `table_method_2`  <-- calculated based on the TEXT column
SELECT userid, post_title, post_brief FROM `table_method_2`                   <-- precalculated VARCHAR column
SELECT userid, post_title, post_description FROM `table_method_2`             <-- return the full text, let the client produce the snippet

Требования к памяти первых трех идентичны. Четвертый запрос потребует меньше памяти (столбец TEXT будет буферизован как указатель), но больше трафика клиенту. Поскольку трафик обычно передается по сети (дорого с точки зрения производительности), он обычно медленнее, чем другие запросы, но ваш пробег может отличаться. Функция LEFT() в столбце TEXT может быть ускорена, если подсказать механизму хранения использовать встроенный макет таблицы, но это будет зависеть от средней длины сохраняемого текста.

Страница 2. Один пост в блоге

SELECT userid, post_title, post_description FROM `table_method_1` WHERE post_id=... <-- returns a VARCHAR
SELECT userid, post_title, post_description FROM `table_method_2` WHERE post_id=... <-- returns a TEXT

Начнем с того, что требования к памяти низкие, поскольку буферизуется только одна строка. По причинам, указанным выше, второй потребуется чуть меньше памяти для буферизации строки, но немного дополнительной памяти для буферизации одного большого двоичного объекта.

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

Резюме

Если у вас есть текст произвольной длины (или что-то, что требует больше, чем несколько килобайт), вы должны использовать столбцы TEXT. Вот для чего они здесь. То, как MySQL обрабатывает эти столбцы, полезно в большинстве случаев.

Для повседневного использования нужно помнить только две вещи:

• Старайтесь не выбирать столбцы TEXT, BLOB-столбцы и все другие столбцы, в которых может быть много данных (и да, включая VARCHAR(10000)), если они вам на самом деле не нужны. Привычка "ВЫБРАТЬ * ИЗ КАКИХ-ЛИБО", когда все, что вам нужно, это пара значений, создаст излишнюю нагрузку на базу данных.
• Когда вы выбираете столбцы TEXT или другие BLOB-объекты, убедитесь, что в выборке не используется временная таблица. Использовать EXPLAIN синтаксис, когда сомневаешься.

Когда вы будете придерживаться этих правил, вы получите довольно приличную производительность от MySQL. Если вам нужна дополнительная оценка, вам придется рассмотреть более мелкие детали. Это будет включать механизмы хранения и соответствующие схемы таблиц, статистическую информацию о фактических данных и знания об используемом оборудовании. Исходя из своего опыта, я обычно мог избавиться от свиней производительности без необходимости копать так глубоко.

Answer 2

Метод 2 выглядит лучше, но если вы храните HTML, post_brief также может быть столбцом TEXT, если это чистый текст, вы можете хранить все в одном столбце и использовать

SELECT user_id, post_title, LEFT(post_description,255) AS post_brief FROM blog_table.

Рассмотрим MySQL 5.6, он намного быстрее, и вы можете использовать FULLTEXT Index в InnoDB, поэтому в случае поиска постов это очень поможет.

Answer 3

Вариант 2 выглядит хорошо для меня тоже. Поскольку пост будет огромным, применение функции к этим столбцам также должно занять время.

И если вы спросите меня, тип данных post_description должен быть blob / text. Даже если столбцы BLOB-объектов не поддерживают поиск, это будет лучшим вариантом.

Единственный недостаток наличия двух столбцов состоит в том, что вы должны убедиться, что как desc, так и бриф синхронизированы (возможно, вы также можете сделать это как функцию)