Высокопроизводительный MySQL (1): оптимизация схемы и типов данных

база данных

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

В этой главе много концептуального материала, и она может быть немного скучной. Но есть много основополагающих принципов, и если вы продолжите читать, вы все равно получите некоторые выгоды.

100Участвовало несколько опытных разработчиков, которые получили почти1000индивидуальныйstarПолный стек всеплатформенный проект с открытым исходным кодом хотел бы знать это?
адрес проекта:GitHub.com/cache Cats/ из…

1. Выберите оптимизированный тип данных

MySQL поддерживает большое количество типов данных, и выбор правильного типа данных имеет решающее значение. Вот несколько простых принципов, которые помогут сделать правильный выбор.

  • меньше обычно лучше

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

    Но убедитесь, что вы не недооцениваете диапазон значений, которые необходимо сохранить, поскольку увеличение диапазона типов данных в нескольких местах схемы — очень трудоемкая и болезненная операция. Если вы не можете решить, какой тип данных является лучшим, выберите наименьший тип, который, по вашему мнению, не выйдет за допустимые пределы.

  • Просто хорошо

    Операции с простыми типами данных обычно требуют меньше циклов процессора. Например, операции с целыми числами менее затратны, чем операции с символами, поскольку наборы символов и правила сортировки (сопоставления) делают сравнение символов более сложным, чем сравнение целых чисел. Вот два примера: для хранения дат и времени следует использовать встроенные типы MySQL (2) вместо строк, а для хранения IP-адресов следует использовать целые числа. Мы обсудим эту тему чуть позже.

  • Старайтесь избегать NULL

    Обычно лучше указать NOT NULL для столбца, если вам действительно не нужно хранить значения NULL.

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

1.1 Целочисленные типы

Есть два типа чисел: целые числа и действительные числа. Если вы храните целые числа, вы можете использовать следующие целочисленные типы: TINYINT, SMALLINT, MEDIUMINT, INT, BIGINT. Используйте 8, 16, 24, 32, 64-битное дисковое пространство соответственно.

Целочисленные типы имеют необязательное свойство UNSIGNED, означающее, что отрицательные значения не допускаются, что примерно удваивает верхний предел положительных чисел. Например, TINYINT UNSIGNED может храниться в диапазоне от 0 до 255, а TINYINT — в диапазоне от −128 до 127.

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

MySQL может указывать ширину для целочисленных типов, таких как INT(11), что для большинства приложений не имеет смысла: он не ограничивает допустимый диапазон значений, а только указывает, что некоторые интерактивные инструменты MySQL (такие как командная строка MySQL client) используйте для отображения количества символов. INT(1) и INT(20) одинаковы для хранения и вычислений.

1.2 Реальный тип

Действительные числа — это числа с дробной частью. Однако они предназначены не только для хранения дробных частей; DECIMAL также может использоваться для хранения целых чисел, больших, чем BIGINT. MySQL поддерживает как точные, так и неточные типы.

Типы FLOAT и DOUBLE поддерживают приближенные вычисления с использованием стандартной арифметики с плавающей запятой. Тип DECIMAL используется для хранения точных десятичных знаков.

Процессор не поддерживает прямое вычисление DECIMAL.В версии 5.0 и выше сервер MySQL сам реализует высокоточное вычисление DECIMAL. Условно говоря, ЦП напрямую поддерживает собственные вычисления с плавающей запятой, поэтомуОперации с плавающей запятой выполняются значительно быстрее.

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

Из-за дополнительного места и вычислительных затрат следует пытаться использовать DECIMAL только тогда, когда вы выполняете точные вычисления с десятичными дробями, например, для хранения финансовых данных. Однако, когда объем данных относительно велик, вы можете рассмотреть возможность использования BIGINT вместо DECIMAL и умножить единицу денежной единицы, которую нужно сохранить, на соответствующее кратное значение в соответствии с количеством знаков после запятой.

1.3 Тип строки

VARCHAR

Тип VARCHAR используется для хранения строк переменной длины и является наиболее распространенным строковым типом данных. Он более эффективен с точки зрения использования пространства, чем тип с фиксированной длиной, поскольку использует только необходимое пространство.

VARCHAR необходимо использовать 1 или 2 дополнительных байта для записи длины строки: если максимальная длина столбца меньше или равна 255 байтам, для его представления используется только 1 байт, в противном случае используются 2 байта.

VARCHAR экономит место на диске, поэтому он также повышает производительность. Однако, поскольку строки становятся длиннее, UPDATE может сделать строки длиннее, чем раньше, что приводит к дополнительной работе.

Использование VARCHAR уместно в следующих ситуациях:

  • Максимальная длина строкового столбца намного больше средней длины;
  • Обновления столбцов происходят нечасто, поэтому фрагментация не является проблемой;
  • Используется сложный набор символов, такой как UTF-8, где каждый символ хранится с использованием разного количества байтов.

Лучшая стратегия — выделять только то пространство, которое вам действительно нужно, и не быть слишком щедрым, так как более длинные столбцы будут потреблять больше памяти.

CHAR

Тип CHAR имеет фиксированную длину: MySQL всегда выделяет достаточно места в соответствии с определенной длиной строки. При сохранении значений CHAR MySQL удаляет все конечные пробелы.

CHAR подходит для хранения очень коротких строк или всех значений, близких к одинаковой длине. Например, CHAR отлично подходит для хранения значения MD5 пароля, поскольку это значение фиксированной длины.

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

Типы BLOB и TEXT

BLOB и TEXT — это строковые типы данных, предназначенные для хранения очень больших данных, которые хранятся в двоичном и символьном виде соответственно.

На самом деле они принадлежат к двум разным семействам типов данных: символьные типы — TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, LONGTEXT; соответствующие двоичные типы — TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, LONGBLOB. BLOB — это синоним SMALLBLOB, а TEXT — это синоним SMALLTEXT.

MySQL сортирует столбцы BLOB и TEXT иначе, чем другие типы: он сортирует только первые байты max_sort_length каждого столбца, а не всю строку. Если вам нужно отсортировать только первые несколько символов, вы можете уменьшить конфигурацию max_sort_length или использовать ORDER BY SUSSTRING(column, length).

Используйте перечисления (ENUM) вместо строковых типов

Иногда можно использовать столбцы enum вместо обычных строковых типов. Столбец перечисления может хранить некоторые неповторяющиеся строки в виде предопределенного набора. MySQL очень компактен при хранении перечислений, сжимаясь в один или два байта в зависимости от количества значений списка. Внутри MySQL хранит положение каждого значения в списке как целое число и сохраняет «справочную таблицу» сопоставлений «число-строка» в файле таблицы .frm.

Хуже всего в перечислениях то, что список строк фиксирован, и вы должны использовать ALTER TABLE для добавления или удаления строк. Использование перечисления не является хорошей идеей, если только допустимо добавлять элементы только в конец списка.

1.4 Типы даты и времени

MySQL может использовать множество типов для хранения значений даты и времени, таких как YEAR и DATE. Минимальная степень детализации времени, которую может хранить MySQL, составляет секунды (MariaDB поддерживает типы времени на уровне микросекунд). Но MySQL также может выполнять специальные операции с точностью до микросекунд, и мы покажем, как обойти это ограничение памяти.

MySQL предоставляет два похожих типа даты: DATETIME и TIMESTAMP. Для многих приложений они оба работают, но в некоторых сценариях один работает лучше, чем другой.

DATETIME

Этот тип может содержать широкий диапазон значений от 1001 до 9999 с точностью до секунд. Он инкапсулирует дату и время в целые числа в формате ГГГГММДДЧЧММСС независимо от часового пояса. Используйте 8 байт дискового пространства. По умолчанию MySQL отображает значения DATETIME в сортируемом однозначном формате, таком как «2008-01-16 22:37:08». Это способ представления даты и времени в соответствии со стандартом ANSI.

TIMESTAMP

Как и его название, тип TIMETAMP содержит количество секунд, прошедших с полуночи (GMT) 1 января 1970 года, что совпадает с отметкой времени UNIX. TIMESTAMP использует только 4 байта памяти, поэтому он имеет гораздо меньший диапазон, чем DATETIME: он может представлять только годы с 1970 по 2038 год.

Значение, отображаемое TIMESTAMP, также зависит от часового пояса. Сервер MySQL, операционная система и клиентские соединения имеют настройки часового пояса.

Стоит подчеркнуть это различие: TIMESTAMP и DATETIME будут вести себя совершенно по-разному, если данные хранятся или доступны в нескольких часовых поясах. Первый предоставляет значения, зависящие от часового пояса, а второй сохраняет текстовое представление даты и времени.

Специальные свойства TIMESTAMP:

Если при вставке данных не указано значение, оно будет автоматически заполнено текущим временем.

TIMESTAMP по умолчанию имеет значение NOT NULL.

Как правило, вы должны пытаться использовать TIMESTAMP, так как он более эффективен, чем DATETIME.

Если вам нужно хранить значения даты и времени с меньшей степенью детализации, чем секунды, вы можете использовать тип BIGINT для хранения усечений времени на уровне микросекунд или использовать DOUBLE для хранения дробной части после секунды. В любом случае это нормально, или вы можете использовать MariaDB вместо MySQL.

1.5 Выбор идентификатора

Важно выбрать подходящий тип данных для столбца идентификатора.

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

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

  • Целочисленный тип

    Целые числа обычно являются лучшим выбором для столбцов идентификаторов, потому что они быстрые и могут использовать AUTO_INCREMENT.

  • Типы ENUM и SET

    Типы ENUM и SET обычно являются плохим выбором для столбцов идентификаторов, и их следует по возможности избегать.

  • Тип строки

    Строковые типы занимают много места и, как правило, медленнее, чем числовые типы, поэтому также следует избегать использования строк в качестве столбцов идентификаторов.

    Вы также должны быть осторожны с полностью «случайными» строками, такими как строки, сгенерированные MD5(), SHA1() или UUID(). Новые значения, генерируемые этими функциями, произвольно распределяются по большому пространству, что может привести к замедлению работы INSERT и некоторых операторов SELECT.

    При сохранении значения UUID следует удалить знак "-" или, что еще лучше, использовать функцию UNHEX(), чтобы преобразовать значение UUID в 16-байтовое число и сохранить его в столбце BINARY(16). При извлечении его можно отформатировать в шестнадцатеричном формате с помощью функции HEX().

1.6 Специальные типы данных

Некоторые типы данных напрямую не соответствуют встроенным типам. Вот два примера:

  1. Отметка времени с точностью до секунды

    Как упоминалось ранее, для хранения меток времени рекомендуется использовать тип BIGINT.

  2. IPv4-адрес

    Люди часто используют столбцы VARCHAR(15) для хранения IP-адресов. Однако на самом деле они представляют собой 32-битные целые числа без знака, а не строки. Обозначение деления адреса на четыре сегмента с десятичной запятой предназначено только для того, чтобы людям было легче его читать. Таким образом, IP-адрес должен храниться как целое число без знака. MySQL предоставляет функции INET_ATON() и INET_NTOA() для преобразования между этими двумя представлениями.

Во-вторых, подводные камни в дизайне схемы MySQL.

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

слишком много столбцов

Когда работает API механизма хранения MySQL, ему необходимо копировать данные между уровнем сервера и уровнем механизма хранения в формате строкового буфера, а затем декодировать содержимое буфера в столбцы на уровне сервера. Преобразование закодированных столбцов из буферов строк в структуры данных строк является дорогостоящей операцией. Структура строк фиксированной длины MyISAM фактически точно соответствует структуре строк уровня сервера, поэтому преобразование не требуется. Однако структура строк переменной длины MyISAM и структура строк InnoDB всегда требуют преобразования. Стоимость конвертации зависит от количества колонок. Когда мы рассмотрели случай с очень высокой загрузкой ЦП и обнаружили, что клиент использует очень широкие таблицы (тысячи полей), но фактически используется лишь небольшая часть столбцов, преобразование оказалось очень дорогим. Если вы планируете использовать тысячи полей, вы должны знать, что будут некоторые различия в характеристиках производительности сервера.

слишком много ассоциаций

Так называемый шаблон проектирования "entity-attribute-value" (EAV) является распространенным плохим шаблоном проектирования, особенно тот, который не работает надежно в MySQL. MySQL ограничивает каждую операцию ассоциации максимум 61 таблицей, но базы данных EAV требуют много самоассоциаций. Мы видели довольно много баз данных EAV, которые в конечном итоге превышают этот предел. На самом деле, во многих случаях, когда связано менее 61 таблицы, стоимость синтаксического анализа и оптимизации запроса также может стать проблемой для MySQL. Грубо говоря, если вы хотите, чтобы запрос выполнялся быстро и с хорошим параллелизмом, лучше всего связать один запрос с менее чем 12 таблицами.

всемогущее перечисление

Позаботьтесь о том, чтобы не допустить чрезмерного использования перечислений (ENUM). Вот пример, который мы видели:

CREATE TABLE ... (       
	country enum('','0','1','2',...,'31')

Дизайн схемы для этого режима очень беспорядочный. Такое использование типов значений перечисления может быть проблематичным в любой базе данных, поддерживающей типы перечисления, где целые числа должны использоваться в качестве внешних ключей для связи со словарными таблицами или таблицами поиска для поиска конкретных значений. Но в MySQL, когда вам нужно добавить новую страну в список перечисления, вы должны выполнить операцию ALTER TABLE. ALTER TABLE является блокирующей операцией в MySQL 5.0 и более ранних версиях, даже в 5.1 и более поздних версиях ALTER TABLE требуется, если значение не увеличивается в конце списка.

замаскированное перечисление

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

CREATE TABLE ... (       
	is_default set ('Y','N') NOT NULL default 'N'

Если и true, и false здесь одновременно нет, то, несомненно, следует использовать столбцы enum вместо столбцов-коллекций.

NULL для не изобретать здесь

Ранее мы писали о преимуществах отказа от NULL и рекомендуем по возможности рассматривать альтернативы. Даже если вам нужно сохранить де-факто «нулевое значение» в таблице, вам не обязательно использовать NULL. Вместо этого можно использовать 0, какое-то специальное значение или пустую строку.

Но не впадайте в крайности, следуя этому принципу. Также не бойтесь использовать NULL, когда вам действительно нужно представить неизвестное значение. В некоторых сценариях использование NULL может быть лучше, чем какая-то магическая константа. Выбор невозможного значения из диапазона определенного типа, например, представление неизвестного целого числа с -1, может привести к гораздо более сложному коду, склонен к появлению ошибок и может привести к путанице. Работать с NULL действительно непросто, но иногда это лучше, чем его альтернативы.

3. Парадигма и антипарадигма

Обычно для любых заданных данных существует множество представлений, от полной нормализации до полной денормализации, а также компромисс между ними. В нормализованной базе данных каждый факт появится только один раз. Напротив, в денормализованной базе данных информация избыточна и может храниться в нескольких местах.

3.1 Преимущества и недостатки парадигм

При обращении за помощью в решении проблем с производительностью часто рекомендуется нормализовать структуру схемы, особенно для сценариев с интенсивным выполнением операций записи. Обычно это хороший совет. Нормализация часто полезна по следующим причинам:

  • Нормализованные операции обновления обычно выполняются быстрее, чем денормализованные.

  • Когда данные хорошо нормализованы, дублирующихся данных практически нет, поэтому требуется изменять меньше данных.

  • Нормализованные таблицы обычно меньше по размеру и лучше помещаются в памяти, поэтому операции выполняются быстрее.

  • Небольшое количество дополнительных данных означает меньшую потребность в получении списка данных оператора DISTINCT или GROUP BY.

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

3.2 Преимущества и недостатки антипарадигмы

Денормализованные схемы позволяют избежать ассоциаций, поскольку все данные находятся в одной таблице. Если связанные таблицы не требуются, наихудшим случаем для большинства запросов, даже если таблица не использует индексы, является полное сканирование таблицы. Это может быть намного быстрее, чем ассоциативное, когда данные больше, чем память, поскольку избегается случайный ввод-вывод.

Отдельные таблицы также могут использовать более эффективные стратегии индексации.

3.3 Смешивание нормализации и денормализации

Нормализованные и денормализованные схемы имеют свои преимущества и недостатки Как выбрать лучший дизайн?

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

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

Ну вот и закончилась эта глава, увидимся в следующем выпуске~

Полный стек полноплатформенный проект с открытым исходным кодом CodeRiver

CodeRiver – это бесплатная платформа для совместной работы над проектами. Цель – открыть передовые и последующие этапы развития ИТ-индустрии.Являетесь ли вы менеджером по продукту, дизайнером, программистом, тестировщиком или другим отраслевым персоналом, если у вас есть хорошие идеи и идеи. , вы можете бесплатно зайти на CodeRiver. Публикуйте проекты и собирайте единомышленников, чтобы воплотить мечты в реальность!

Сам CodeRiver также является крупномасштабным проектом с открытым исходным кодом, посвященным созданию полнофункционального, полноплатформенного проекта с открытым исходным кодом корпоративного уровня. Охватывает практически все основные технологические стеки, такие как React, Vue, Angular, Mini Programs, ReactNative, Android, Flutter, Java, Node и т. д., уделяя особое внимание качеству кода.

Было почти100Участвовал ряд выдающихся разработчиков, и githubstarОколо1000Кусок. У каждого стека технологий есть несколько опытных боссов, а два архитектора определяют структуру проекта. Независимо от того, какой язык и уровень навыков вы хотите изучать, вы можете чему-то научиться здесь.

пройти через高质量源码 + 博客 + 视频, чтобы помочь каждому разработчику быстро расти.

адрес проекта:GitHub.com/cache Cats/ из…

Ваша поддержка - самая большая движущая сила для нас, чтобы двигаться вперед, добро пожаловать, чтобы поставить лайк, добро пожаловать, чтобы отправить маленькие звездочки ✨ ~

Категории