Варианты MySQL, которые вы могли упустить из виду

задняя часть MySQL

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

Механизм хранения MySQL

Обзор механизма хранения

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

Архитектуру MySQL можно понять в соответствии с трехуровневой моделью.

Механизм хранения также является компонентом MySQL.Это своего рода программное обеспечение.Функции, которые он может выполнять и поддерживать, в основном включают:

  • параллелизм
  • Поддержка транзакций
  • ограничения целостности
  • физическое хранилище
  • поддержка индекса
  • справка по производительности

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

  • MyISAM
  • InnoDB
  • BDB
  • MEMORY
  • MERGE
  • EXAMPLE
  • NDB Cluster
  • ARCHIVE
  • CSV
  • BLACKHOLE
  • FEDERATED

По умолчанию, если вы создаете таблицу без указания механизма хранения, будет использоваться механизм хранения по умолчанию.Если вы хотите изменить механизм хранения по умолчанию, вы можете указать его в файле параметров.default-table-type, чтобы иметь возможность просматривать текущий механизм хранения

show variables like 'table_type';

Странно, почему бы и нет? Проверьте онлайн, этот параметр был отменен в 5.5.3.

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

show engines \g

При создании новой таблицы вы можете добавитьENGINEКлючевое слово устанавливает механизм хранения только что созданной таблицы.

create table cxuan002(id int(10),name varchar(20)) engine = MyISAM;

Выше мы указалиMyISAMнакопительный двигатель.

Что делать, если вы не знаете механизм хранения таблицы? ты можешь пройтиshow create tableсмотреть

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

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

Как видите, механизм хранения по умолчаниюInnoDB.

Если ваш механизм хранения хочет быть заменен, вы можете использовать

alter table cxuan003 engine = myisam;

заменить, после завершения замены он будет отображать0 rows affected, а по факту успешно эксплуатировался

Мы используемshow create tableПосмотрите на sql таблицы, чтобы знать

Функции механизма хранения

Далее будут представлены несколько часто используемых механизмов хранения и их основные характеристики.Это **MyISAM, InnoDB, MEMORY и MERGE**.

MyISAM

До версии 5.1 MyISAM был механизмом хранения MySQL по умолчанию. MyISAM имел плохой параллелизм и использовался в нескольких сценариях. Основные функции:

  • не поддерживается事务эксплуатации характеристики ACID не существуют, эта конструкция предназначена для производительности и эффективности.

  • не поддерживается外键Операция, если принудительно добавить внешний ключ, MySQL не сообщит об ошибке, но внешний ключ не работает.

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

  • MyISAM будет хранить на диске три файла, имя файла и имя таблицы совпадают, расширение.frm(存储表定义),.MYD(MYData,存储数据),MYI(MyIndex,存储索引). Здесь важно отметить, что MyISAM кэширует только索引文件, не кэширует файлы данных.

  • Типы индексов, поддерживаемые MyISAM:全局索引(Full-Text),B-Tree 索引,R-Tree 索引

    Полнотекстовый индекс: Появился для решения проблемы низкой эффективности нечеткого запроса текста.

    Индекс B-дерева: все узлы индекса хранятся в соответствии со структурой данных сбалансированного дерева, и все узлы данных индекса находятся в листовых узлах.

    Индекс R-Tree: его метод хранения отличается от индекса B-Tree. Он в основном предназначен для хранения пространства и многомерных полей данных для индексации. Текущая версия MySQL поддерживает только поля типа геометрии для индексации. По сравнению с BTREE, RTREE преимуществом является поиск диапазона.

  • Если хост, на котором расположена база данных, выходит из строя, файлы данных MyISAM легко повреждаются, и их трудно восстановить.

  • Добавление, удаление, изменение и производительность запросов: SELECT обладает высокой производительностью и подходит для многих запросов.

InnoDB

Начиная с MySQL 5.1, механизм хранения по умолчанию стал механизмом хранения InnoDB.По сравнению с MyISAM, механизм хранения InnoDB претерпел серьезные изменения.Его основные функции:

  • Поддержка операций с транзакциями с функцией изоляции ACID транзакций, уровень изоляции по умолчанию —可重复读(repetable-read),пройти черезMVCC（并发版本控制）быть реализованным. в состоянии решить脏读и不可重复读Проблема.
  • InnoDB поддерживает операции с внешним ключом.
  • Детализация блокировки InnoDB по умолчанию行级锁, производительность параллелизма лучше, и возникнет взаимоблокировка.
  • Как и MyISAM, механизм хранения InnoDB также имеет.frm文件存储表结构Определения, но разница в том, что данные таблицы InnoDB и данные индекса хранятся вместе, и они расположены на B+ конечных узлах, в то время как данные таблицы MyISAM и данные индекса разделены.
  • InnoDB имеет безопасный файл журнала, который используется для восстановления потерянных данных из-за сбоев базы данных или других ситуаций для обеспечения согласованности данных.
  • Типы индексов, поддерживаемые InnoDB и MyISAM, одинаковы, но конкретная реализация сильно отличается из-за разных файловых структур.
  • С точки зрения производительности CRUD, если выполняется большое количество CRUD-операций, рекомендуется использовать механизм хранения InnoDB, который удаляет строки во время операций удаления и не перестраивает таблицы.

MEMORY

Механизм хранения MEMORY использует то, что хранится в памяти, для создания таблиц. Каждая таблица MEMORY фактически соответствует только одному файлу на диске, формат.frm. Доступ к таблице MEMORY осуществляется быстро, поскольку ее данные хранятся в памяти. Использовать по умолчаниюHASH 索引.

MERGE

Механизм хранения MERGE представляет собой комбинацию набора таблиц MyISAM. Сама таблица MERGE не содержит данных. Операции запроса, обновления и удаления таблиц типа MERGE фактически выполняются во внутренних таблицах MyISAM. Таблица MERGE содержит два файла на диске, один.frmопределение таблицы хранения файлов, одно из них.MRGВ файле хранится состав таблицы MERGE и т.д.

Выберите правильный механизм хранения

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

  • MyISAM: если приложение обычно основано на поиске, с небольшим количеством операций вставки, обновления и удаления, а целостность и параллелизм вещей не очень высоки, обычно рекомендуется выбирать механизм хранения MyISAM.
  • InnoDB: если используются внешние ключи, требуется высокая степень параллелизма и требования к согласованности данных высоки, обычно выбирается механизм InnoDB.Как правило, крупные интернет-компании предъявляют высокие требования к параллелизму и целостности данных, поэтому механизм хранения InnoDB обычно используется.
  • ПАМЯТЬ: Механизм хранения ПАМЯТИ хранит все данные в памяти и может обеспечить очень быстрый доступ, когда требуется быстрое позиционирование. ПАМЯТЬ обычно используется для небольших таблиц, которые обновляются реже, для быстрого доступа к результатам.
  • MERGE: MERGE использует таблицу MyISAM для внутреннего использования.Преимущество таблицы MERGE заключается в том, что она может преодолеть ограничение размера одной таблицы MyISAM, а путем распределения разных таблиц на нескольких дисках можно эффективно повысить эффективность доступа к таблице MERGE. .

Выберите подходящий тип данных

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

Выбор CHAR и VARCHAR

char и varchar — это два типа данных, которые мы часто используем для хранения строк. char обычно хранит строки фиксированной длины, которые представляют собой типы символов фиксированной длины, например следующие:

ценность char(5) байты памяти
'' ' ' 5 байт
'cx' 'cx ' 5 байт
'cxuan' 'cxuan' 5 байт
'cxuan007' 'cxuan' 5 байт

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

Обратите внимание: если MySQL использует не-严格模式Если это так, последняя строка таблицы выше может быть сохранена. Если MySQL использует严格模式Если да, то последняя строка таблицы над хранилищем сообщит об ошибке.

Если используется тип символа varchar, давайте посмотрим на пример

ценность varchar(5) байты памяти
'' '' 1 байт
'cx' 'cx ' 3 байта
'cxuan' 'cxuan' 6 байт
'cxuan007' 'cxuan' 6 байт

Как видите, если используется varchar, сохраненные байты будут сохранены в соответствии с фактическим значением. Вы можете удивиться, почему длина varchar равна 5, а хранить нужно 3 байта или 6 байт, это потому, что при использовании типа данных varchar для хранения длина строки будет добавлена ​​в конце по умолчанию, занимая 1 слово Section ( два байта используются, если длина объявления столбца превышает 255). varchar не заполняет пустые строки.

Обычно используйте char для хранения строк фиксированной длины, таких какИдентификационный номер, номер мобильного телефона, электронная почта и т. д.;Используйте varchar для хранения строк переменной длины. Поскольку длина char фиксирована, скорость его обработки намного выше, чем у VARCHAR, но недостатком является то, что он тратит впустую место для хранения, но с непрерывным развитием версии MySQL производительность типа данных varchar также постоянно улучшается и улучшается. , поэтому во многих приложениях чаще используется тип VARCHAR.

В MySQL разные механизмы хранения используют разные принципы для CHAR и VARCHAR.

  • MyISAM: рекомендуется использовать столбцы данных фиксированной длины вместо столбцов данных переменной длины, то есть CHAR.
  • ПАМЯТЬ: используйте фиксированную длину для обработки, и CHAR, и VARCHAR обрабатываются как CHAR.
  • InnoDB: рекомендуется тип VARCHAR

ТЕКСТ и BLOB

Как правило, при сохранении меньшего количества текста мы выбираем CHAR и VARCHAR.При сохранении больших объемов текста мы часто выбираем TEXT и BLOB; основное различие между TEXT и BLOB заключается в том, что BLOB может сохранять二进制数据; а ТЕКСТ может сохранять только字符数据, ТЕКСТ подразделяется, чтобы иметь

  • TEXT
  • MEDIUMTEXT
  • LONGTEXT

BLOB подразделяется на

  • BLOB
  • MEDIUMBLOB
  • LONGBLOB

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

TEXT и BLOB будут иметь некоторые проблемы с производительностью после удаления данных. Чтобы повысить производительность, рекомендуется использоватьOPTIMIZE TABLEФункция дефрагментации таблицы.

Синтетические индексы также можно использовать для повышения производительности запросов к текстовым полям (BLOB и TEXT). Синтетический индекс предназначен для создания хеш-значения на основе содержимого больших текстовых полей (BLOB и TEXT) и сохранения этого значения в соответствующем столбце, чтобы соответствующая строка данных могла быть найдена в соответствии с хеш-значением. Обычно используются такие алгоритмы хэширования, как md5() и SHA1(). Если строки, сгенерированные алгоритмом хеширования, имеют пробелы в конце, не сохраняйте их в CHAR и VARCHAR. Давайте посмотрим на это использование.

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

Вставьте данные в cxuan005, где хеш-значение используется в качестве хеш-значения информации.

Затем вставьте еще два данных

Вставьте часть данных с информацией как cxuan005

Если вы хотите запросить данные, информация которых равна cxuan005, вы можете запросить хеш-столбец для запроса

Это пример синтетического индекса.Если вы хотите выполнять нечеткие запросы к BLOB, вы будете использовать префиксный индекс.

Другие способы оптимизации BLOB и TEXT:

  • Не извлекайте индексы BLOB и TEXT без необходимости
  • Разделите столбцы BLOB или TEXT в отдельные таблицы.

Выбор чисел с плавающей запятой и фиксированной запятой

Числа с плавающей запятой относятся к значениям, которые содержат десятичные дроби.После того, как число с плавающей запятой вставляется в указанный столбец за пределами указанной точности, число с плавающей запятой будет округлено в большую сторону.Число с плавающей запятой в MySQL относится кfloatиdouble, число с фиксированной точкой относится кdecimal, числа с фиксированной точкой могут сохранять и отображать данные более точно. Ниже приведен пример, объясняющий точность чисел с плавающей запятой.

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

Затем вставьте два данных

Затем выполните запрос, вы увидите, что округление двух данных, полученных из запроса, отличается.

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

Сначала измените два поля cxuan006 на одинаковую длину и десятичные разряды.

Затем вставьте два данных

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

Выбор типа даты

В MySQL типы дат представленыДАТА, ВРЕМЯ, ДАТАВРЕМЯ, TIMESTAMP,существует

138 картинок, которые познакомят вас с MySQL

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

  • TIMESTAMP связан с часовым поясом и может лучше отражать текущее время. Если записанная дата должна использоваться людьми в разных часовых поясах, лучше использовать TIMESTAMP.
  • DATE используется для представления года, месяца и дня.Если фактическое значение приложения необходимо сохранить год, месяц и день, можно использовать DATE.
  • ВРЕМЯ используется для представления часов, минут и секунд.Если фактическое значение приложения необходимо сохранить в часах, минутах и ​​секундах, можно использовать ВРЕМЯ.
  • ГОД используется для представления года, ГОД имеет 2 цифры (предпочтительно 4 цифры) и год в 4-значном формате. По умолчанию 4 бита. Если фактическое приложение сохраняет только год, совершенно нормально хранить тип YEAR с 1 байтом. Это может не только сэкономить место для хранения, но и повысить эффективность работы стола.

Набор символов MySQL

Давайте взглянем на набор символов MySQL.Вкратце, набор символов — это набор текстовых символов, правил кодирования и сравнения. В 1960 году американская организация по стандартизации ANSI выпустила первый компьютерный набор символов, известный какASCII(American Standard Code for Information Interchange). Начиная с кодировки ASCII, каждая страна и международная организация изучили собственный набор символов, напримерISO-8859-1,GBKЖдать.

Но каждая страна использует свой набор символов, что создает большие трудности с переносимостью. Итак, чтобы использовать Unicode,国际标准化组织(ISO)Указывает единый стандарт символов — кодировку Unicode, которая поддерживает почти все кодировки символов. Вот некоторые распространенные кодировки символов

набор символов фиксированная длина Кодирование
ASCII да Однобайтовое 7-битное кодирование
ISO-8859-1 да Однобайтовое 8-битное кодирование
GBK да двухбайтовое кодирование
UTF-8 нет 1 - 4 байтовая кодировка
UTF-16 нет 2-байтовая или 4-байтовая кодировка
UTF-32 да 4-байтовая кодировка

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

MySQL поддерживает множество наборов символов, вы можете использоватьshow character set;чтобы увидеть все доступные наборы символов

или использовать

select character_set_name, default_collate_name, description, maxlen from information_schema.character_sets;

Проверять.

использоватьinformation_schema.character_setчтобы увидеть наборы символов и правила сопоставления.

Категории