Схема MySQL

[TOC]

1. Логическая архитектура

• Первый уровень — это аутентификация соединения клиента, и C/S имеет такую ​​структуру.
• Второй уровень — это серверный уровень, содержащий большинство основных служб MySQL.
• Третий слой включает в себя механизм хранения, в связи с сервером через API, API избегает различий в хранилище хранения, разные двигатели хранения не будут взаимодействовать друг с другом без дополнительного механизма хранения для анализа SQL (InnoDB Exception, он анализирует определение внешнего ключа. , потому что сам сервер не реализует эту функцию)

1.1 Управление подключением и безопасность

• Каждый клиент имеет один поток в процессе сервера

• Сервер будет отвечать за кеширование потоков, и нет необходимости создавать или уничтожать потоки для каждого нового соединения (версия 5.5 и выше предоставляет пул потоков, который может использовать небольшое количество потоков для обслуживания большого количества соединений).

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

  ​

1.2 Оптимизация и исполнение

• MySQL разберет запрос, создаст внутреннюю структуру данных (дерево разбора) и проведет различные оптимизации (перепишет запросы, определит порядок чтения таблицы, выберет соответствующий индекс)
• Пользователи могут влиять на процесс принятия решений MySQL с помощью специальных подсказок ключевых слов (hint) оптимизатору. Вы также можете попросить оптимизатор объяснить различные факторы процесса оптимизации, чтобы пользователи могли реконструировать запросы и схемы и изменять соответствующие конфигурации.
• Оптимизатор не заботится о подсистеме хранения, используемой таблицей, но подсистема хранения влияет на оптимизацию запроса. Оптимизатор запросит у механизма хранения информацию о емкости или стоимости конкретной операции, статистику табличных данных и т. д.
• Для операторов SELECT сервер проверяет кэш запросов перед анализом запроса.

2. Параллельный контроль

Параллельный контроль на двух уровнях: уровень сервера и уровень механизма хранения.

2.1 Блокировка чтения-записи

• Общий замок (общий замок), замок для чтения (чтение блокировки): общие, не блокируйте друг друга
• Эксклюзивная блокировка (эксклюзивная блокировка), блокировка записи (блокировка записи): монопольная (будет блокировать другие блокировки чтения-записи)

2.2 Детализация блокировки

• Параллелизм, предоставляющий общие ресурсы: делает блокировку объектов более избирательной. Старайтесь блокировать только часть данных\срезов данных, которые необходимо изменить.Чем меньше объем заблокированных данных, тем выше степень параллелизма.
• Различные операции блокировки (получение блокировки, проверка снятия блокировки, освобождение блокировки) потребляют ресурсы.
• Стратегия блокировки:
  • Блокировка таблицы (блокировка таблицы):
    • Самая простая и наименее затратная стратегия. Реализация уровня сервера
    • Заблокируйте всю таблицу. Блокировки записи блокируют другие блокировки, а блокировки чтения не блокируют друг друга.
    • В некоторых сценариях блокировки таблиц также могут иметь хорошую производительность. Например, блокировка таблицы READ LOCAL поддерживает определенные типы параллельных операций записи. Блокировки записи имеют более высокий приоритет, чем блокировки чтения, и могут быть вставлены в начало очереди блокировок чтения.
    • Механизм хранения может управлять своими собственными блокировками, а уровень сервера по-прежнему будет использовать различные допустимые блокировки таблиц для достижения различных целей. Например, сервер будет использовать блокировки таблиц для таких операторов, как ALTER TABLE, игнорируя механизм блокировки механизма хранения.
  • блокировка строки:
    • Максимальная поддержка параллельной обработки также приводит к максимальным затратам на блокировку.
    • Реализовано только в механизме хранения (InnnoDB, XtraDB и т. д.)

3. Транзакция

• Транзакция — это атомарный набор SQL-запросов или независимая единица работы.
• ACID транзакции:
  • Атомарность: неделимая наименьшая единица работы.
  • Непротиворечивость: база данных всегда переходит из одного согласованного состояния в другое согласованное состояние.
  • Изоляция: изменения, сделанные одной транзакцией, не видны другим транзакциям, пока они не будут окончательно зафиксированы.
  • Долговечность: после фиксации транзакции ее модификации постоянно сохраняются в базе данных. (Нет 100% гарантии долговечности)

3.1 Уровень изоляции линий изоляции

В стандарте SQL предусмотрено четыре уровня изоляции:

• READ UNCOMMITED (незафиксированное чтение):
  • Изменения в транзакции, даже если они не зафиксированы, видны другим транзакциям.
  • Транзакция считывает незафиксированные данные, известные как грязное чтение.
  • Производительность ненамного лучше, чем у других уровней, но у него гораздо меньше преимуществ, и поэтому его использование не рекомендуется.
• Прочитано Commited (Compart Read), не повторяемый читатель (неписанный читаемый):
  • Уровень по умолчанию для большинства баз данных, но не MySQL.
  • Транзакция может только считывать изменения, сделанные зафиксированными транзакциями.Другими словами, любые изменения, сделанные транзакцией с самого начала до ее фиксации, невидимы для других транзакций.
  • Это может привести к виртуальному чтению. Например, транзакция A считывает данные дважды, а транзакция B изменяет данные между ними. Два чтения будут иметь разные результаты, и можно будет прочитать добавления, удаления и изменения других транзакций.
• ПОВТОРЯЕМОЕ ЧТЕНИЕ:
  • Уровень MySQL по умолчанию.
  • Решите проблему грязного чтения и убедитесь, что результаты чтения одной и той же записи несколько раз в одной и той же транзакции непротиворечивы.
  • Это может привести к фантомному чтению (Phantom Read), транзакция A читает и изменяет данные, транзакция B также изменяет данные (вставляет или удаляет) в этом диапазоне, транзакция A снова читает данные из этого диапазона и находит фантомную строку (Phantom Row). ).), вы можете прочитать добавления и удаления других транзакций
• СЕРИАЛИЗУЕМЫЙ (сериализуемый):
  • Самый высокий уровень изоляции, который заставляет транзакции выполняться последовательно.
  • Блокировки помещаются на каждую считанную строку данных, что приводит к большому количеству тайм-аутов и проблемам блокировки блокировок.

уровень изоляции	Возможность грязного чтения	возможность неповторяемого чтения	Возможность призрачного чтения	блокировка чтения
незафиксированное чтение	Yes	Yes	Yes	No
Отправить для чтения	No	Yes	Yes	No
повторяемое чтение	No	No	Yes	No
Сериализуемый	No	No	No	Yes

3.2 Тупик

• Тупик относится к двум или более транзакциям, которые занимают друг друга на одном и том же ресурсе и запрашивают блокировку занятия другой стороны, что приводит к феномену порочного круга.
• причина:
  • настоящий конфликт данных
  • Как реализован механизм хранения
• Решение (частично или полностью откатить одну из транзакций):
  • Обнаружение взаимоблокировки. Метод InnoDB для работы с взаимоблокировками: откат транзакции, которая содержит наименьшую эксклюзивную блокировку на уровне строки (простой алгоритм отката).
  • Механизм тайм-аута взаимоблокировки, отказ от запроса на блокировку после тайм-аута

3.3 Журнал транзакций (Журнал с опережающей записью)

• Используя журнал транзакций, механизм хранения изменяет данные таблицы, просто изменяя ее копию в памяти, а затем записывая это поведение в журнал транзакций, который сохраняется на жестком диске.
• Для большинства реализаций механизма хранения изменение данных требует двойной записи на диск (первый для записей журнала и второй для данных).
• преимущество:
  • Повышение эффективности бизнеса
  • высокоскоростной. В методе append операция записи журнала представляет собой последовательный ввод-вывод в небольшой области диска, а не случайный ввод-вывод в нескольких областях.

3.4 Транзакции в MySQL

Механизм хранения транзакций: MySQL, такой как InnoDB и NDB Cluster, сторонние, такие как XtraDB и PBXT.

• АВТОКОММИТ:

  • Режим MySQL по умолчанию. Если транзакция запускается неявно, каждый запрос рассматривается как транзакция для выполнения операции фиксации.

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

    mysql> SHOW VARIABLES LIKE 'AUTOCOMMIT';
    mysql> SET AUTOCOMMIT = 1;

  • Это не влияет на нетранзакционные таблицы, такие как MyISAM или таблицы в памяти. Поскольку в таблицах этого типа нет концепции COMMIT или ROLLBACK, то есть они всегда находятся в режиме AUTOCOMMIT.

  • В языке определения данных (DDL) такие операции, как ALTER TABLE, LOCK TABLES и т. д., вызывающие большое количество изменений данных, заставят COMMIT зафиксировать текущую транзакцию перед выполнением.

    

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

    mysql> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITED;

  • MySQL может идентифицировать все четыре уровня изоляции ANSI, а двигатель InnoDB поддерживает все уровни изоляции.

• Смешивание механизмов хранения в транзакциях

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

• Неявная и явная блокировка

  • Неявная блокировка:

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

  • Явная блокировка:

    • MySQL можно явно заблокировать с помощью определенных операторов, эти операторы не относятся к спецификации SQL, старайтесь избегать использования

      SELECT ... LOCK IN SHARE MODE
      SELECT ... FOR UPDATE

  • другой замок

    • MySQL поддерживает операторы LOCK TABLES и UNLOCK TABLES, которые реализованы на уровне сервера и не имеют ничего общего с механизмом хранения. Не заменяет обработку транзакций. При использовании в InnoDB это серьезно повлияет на производительность, и эффект не так хорош, как его блокировка на уровне строк.
    • Рекомендация: За исключением отключения AUTOCOMMIT в транзакции, вы можете использовать LOCK TABLES и не выполнять никакое другое использование явно, независимо от того, какой механизм хранения используется.

4. Многоверсионный контроль параллелизма (MVVC)

Большинство механизмов хранения MySQL, Oracle и PostgreSQL реализуют MVVC, но механизмы реализации различаются, как правило, оптимистичный контроль параллелизма и пессимистичный контроль параллелизма.

• Цель:

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

• Принцип реализации:

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

• Упрощенная версия InnoDB MVVC работает:

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

  Работает только при двух уровнях изоляции REPEATABLE READ и READ COMMITED.

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

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

  На уровне изоляции REPEATABLE READ MVCC работает:

  • ВЫБРАТЬ:

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

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

  • INSERT:

    Сохраните текущий номер версии системы как номер версии строки для каждой недавно вставленной строки данных

  • DELETE

    Сохраните номер текущей версии системы в качестве идентификатора удаления строки для каждой удаляемой строки.

  • UPDATE:

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

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

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

-- 表路径：/MySQL_data/MyDB/Mytalbe.frm
mysql> SHOW TABLE STATUS LIKE 'MyTABLE'\G;

5.1 Механизм хранения InnoDB

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

• Цель дизайна: Обращение с большим количеством краткосрочных транзакций (большинство краткосрочных транзакций являются нормальными, редко прокат)
• Обзор:
  • Данные хранятся в табличном пространстве, черном ящике, управляемом InnoDB, состоящем из ряда файлов данных.
  • Используя MVVC для поддержки высокого параллелизма, реализованы четыре стандартных уровня изоляции, по умолчанию используется ПОВТОРЯЕМОЕ ЧТЕНИЕ, а InnoDB блокирует строки, участвующие в запросе, с помощью стратегии блокировки следующего ключа, а также блокирует пробелы в индексе, чтобы предотвратить появление магического чтения. .
  • Основанный на создании кластеризованного индекса, он имеет высокую производительность для запроса первичного ключа. Однако вторичный индекс (вторичный индекс, непервичный индекс ключа) должен содержать столбец первичного ключа.Если индекс первичного ключа слишком велик, все остальные индексы будут большими.
  • Чтение данных с диска с использованием предсказуемого упреждающего чтения, автоматическое создание хэш-индексов в памяти для ускорения операций чтения (адаптивный хэш-индекс) и ускорение операций вставки (вставка буфера).
  • Поддержите настоящую горячую резервную копию через некоторые механизмы и инструменты
  • Рекомендация: Доступно Прочтите официальное руководство «Модель транзакций InnoDB и блокировка» a

5.2 Механизм хранения MyISAM

Механизм хранения по умолчанию для MySQL 5.1 и более ранних версий. Полнотекстовое индексирование, сжатие, пространственные функции (ГИС) поддерживаются, транзакции и блокировка на уровне строк не поддерживаются, безопасное восстановление после сбоя невозможно. Для данных, предназначенных только для чтения, или относительно небольшой таблицы, допускающей ремонт (исправление) операции, можно рассмотреть MyISAM.

• Хранение: Таблицы хранятся в файловой системе с файлами данных .MYD и .MYI и индексными файлами.
• характеристика:
  • Блокировка и параллелизм: блокируйте всю таблицу, а не конкретную строку. Разделяемая блокировка добавляется в таблицу чтения при чтении, а эксклюзивная блокировка добавляется при записи. Поддержка параллельной вставки (CONCURRENT INSERT), при чтении запроса также можно вставлять новые данные.
  • Ремонт: это концепция, отличная от восстановления транзакций и восстановления после сбоя. Медленно и может привести к потере данных. пройти черезCHECK TABLE mytableКонтрольный список ошибок,REPAIR TABLE mytableсделать ремонт.
  • Функции индексирования: поддержка полнотекстового индексирования, т. е. индекса, созданного на основе сегментации слов. Даже длинные поля, такие как BOLB и TEXT, можно индексировать на основе первых 500 символов.
  • Отложенная запись ключа (Delayed Key Write): если указано DELAY_KEY_WRITE, то каждый раз, когда модификация завершается, измененные данные индекса будут записываться не на диск, а в буфер ключа в памяти.Они записываются на диск только тогда, когда ключ буфер очищается или таблица закрывается. Может значительно повысить производительность записи, но может привести к повреждению индекса для выполнения операций восстановления в случае сбоя базы данных или хоста.
• Сжатые таблицы. Сжатые таблицы можно использовать для операций только для чтения, что значительно сокращает дисковое пространство и количество операций ввода-вывода, тем самым повышая производительность запросов.
• Производительность. Простой дизайн, данные хранятся в компактном формате, в некоторых сценариях производительность хорошая. Наиболее типичная проблема с производительностью — это блокировки таблиц.

5.3 Другие механизмы хранения, встроенные в MySQL

• Механизм архивирования: нетранзакционный тип сжатия указывает механизм вставки и оптимизации. Поддержка INSERT, SELECT и индексов, каждый SELECT требует полного сканирования таблицы и предотвращает другие SELECT, реализованные для обеспечения согласованности чтения; поддерживает блокировку на уровне строк и специальные буферы для достижения высокой параллельной вставки. Подходящие классы приложений для регистрации и сбора данных.
• Двигатель Blackke: он не реализует какой-либо механизм хранения, поскольку он отбрасывает все вставленные данные и не сохраняет его, но сервер будет записывать журнал таблицы BlackHole, поэтому его можно использовать для копирования данных в базу данных в режиме ожидания или просто Журнал в журнал. Подходит для специальных архитектур репликации и аудита журнала, но не рекомендуется.
• Механизм CSV: обрабатывает файлы CSV как таблицы MySQL, но не поддерживает индексы. Подходит как механизм обмена данными.
• Federated Engine: прокси-сервер для доступа к другим серверам MySQL, создания удаленного клиентского соединения MySQL, передачи запросов на удаленный сервер для выполнения, а затем извлечения или отправки необходимых данных.
• Механизм памяти:
  • Все данные хранятся в памяти, дисковый ввод-вывод не требуется, и это на порядок быстрее, чем MyISAM.
  • Хэш-индекс поддерживается, но это блокировка на уровне таблицы, поэтому производительность одновременной записи низкая.
  • TEXT BOLB не поддерживает столбцы и длину каждой строки.
  • Он подходит для быстрого доступа к данным, и данные не будут изменены, и не беда, если они будут потеряны после перезапуска (данные потеряются, а структура таблицы останется).
  • Сценарии применения:
    • Для таблиц поиска или сопоставления
    • Используется для кэширования результатов периодически агрегируемых данных.
    • Используется для сохранения промежуточных данных, сгенерированных при анализе данных.
  • MySQL необходимо использовать временную таблицу для хранения промежуточных результатов при выполнении запроса.Используется таблица памяти.Если данные слишком велики или содержат поля BLOB или TEXT, используется таблица MyISAM.
  • Память не таблицаCREATE TEMPORARY mytableСозданные таблицы, которые могут использовать любой механизм хранения.
• Механизм слияния: устарел
• NDB Cluster Engine: участие в кластере MySQL

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

• Если вам не нужно использовать некоторые функции, которых нет в InnoDB, и нет другого способа заменить их, вам следует предпочесть движок InnoDB.
• Не смешивайте механизмы хранения.
• Соображения:
  • дела
  • резервный
  • аварийное восстановление
  • уникальные черты
• Сценарии применения:
  • Приложение для ведения журнала:
    • Используйте MyISAM или механизм хранения архива
    • Очевидное включение информации о дате для таблицы регистрации

5.5 Движок таблицы преобразования

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

• ALTER TABLE

  mysql> ALTER TABLE mytable ENGINE = InnoDB;

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

• экспорт и импорт

  Используя mysqldump, измените в нем оператор CREATE TABLE.

• Создание и запрос (CREATE и SELECT)

  mysql> CREATE TABLE innodb_table LIKE myisam_table;
  mysql> ALTER TABLE innodb_table ENGINE=InnoDB;
  mysql> INSERT INTO innodb_table SELECT * FROM myisam_table;
  -- 如数据量过大，可分批处理。
  mysql> START TRANSACTION;
  mysql> INSERT INTO innodb_table SELECT * FROM myisam_table where id BETWEEN x AND y;
  mysql> COMMIT;
  -- 如需要可对原表加锁，保证数据一致性。

  ​