Принцип транзакций и индексов InnoDB

задняя часть MySQL Эксплуатация и техническое обслуживание

Нажмите на синее слово выше, чтобы подписаться

Донг Мингбин, старший инженер отдела исследований и разработок бизнес-платформы, присоединился к Lianjia.com (теперь Shell House) в марте 2016 года, сосредоточившись на исследованиях и разработках трафика, а также хорошо разбирается в бэкэнд-разработке.

1. Введение

MySQL требует много знаний и глубоких знаний.Здесь мы в основном сосредоточимся на двух самых основных и наиболее часто используемых пунктах back-end RD: принципы транзакций и индексирования InnoDB, частичная теория, вероятность того, что вас спросят на собеседовании, очень высока. Для того, чтобы лучше объяснить принцип, я разместил много фотографий, большинство из которых из Интернета, которые захвачены и удалены.

2. Механизм хранения InnoDB

2.1 Многоуровневая архитектура MySQL

Многоуровневая архитектура

  • Уровень доступа: в основном отвечает за обработку соединений, авторизацию и аутентификацию, безопасность и другие вопросы.

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

  • Уровень механизма хранения: отвечает за хранение и извлечение данных в MySQL. Уровень службы взаимодействует с механизмом хранения через API. Механизм хранения содержит десятки базовых API-интерфейсов функций, и каждый механизм предоставляет набор конкретных реализаций.

  • Уровень системных файлов: отвечает за чтение и запись базовой файловой системы. 

Эта многоуровневая архитектура может четко разделить обязанности каждого уровня и облегчить расширение.

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

InnoDB относится к слою механизма хранения и является механизмом хранения по умолчанию в MySQL (версия 5.1 и выше). Основными особенностями InnoDB по сравнению с другими механизмами хранения являются: поддержка транзакций, поддержка высокой параллелизма, автоматическое восстановление после сбоев и организация табличных данных на основе кластерных индексов. Основное внимание мы уделяем следующим вопросам:Как InnoDB гарантирует транзакции? Как поддерживать высокий параллелизм? Как хранятся данные?

3. Принцип сделки

Транзакции имеют четыре основные характеристики, а именно: Атомарность, Непротиворечивость, Изоляция, Длительность, ACID для краткости, что является стандартной спецификацией SQL, и InnoDB реализует ее по-своему.

3.1 Особенности КИСЛОТЫ

  • Атомарность: наименьшая единица работы, либо все выполняются успешно, либо все терпят неудачу.

  • Непротиворечивость: целостность базы данных не нарушается после начала и завершения транзакции.

  • Изоляция. Транзакции не влияют друг на друга, и существует четыре уровня изоляции: RU (чтение незафиксированных), RC (чтение зафиксировано), RR (повторяемое чтение) и SERIALIZABLE (сериализация).

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

    кислотные свойства

    Основное внимание уделяется изоляции. Уровень изоляции InnoDB по умолчанию — RR. На этом уровне InnoDB избегает фантомного чтения с помощью механизма MVCC — «неблокирующее чтение моментального снимка и текущее чтение с блокировкой (блокировка строки + блокировка промежутка)». Так что же такое фантомное чтение?Так называемое фантомное чтение относится к текущему чтению одного и того же оператора в той же транзакции, возвращаемые записи точно такие же (количество записей такое же, и сами записи тоже одинаковые), а последующее текущее чтение read не вернется больше, чем в первый раз.запись (фантом).

3.2 Журнал транзакций

Использование InnoDBundo、 redo logДля обеспечения атомарности, согласованности и долговечности транзакций при использованиивести журнал впередЭтот метод превращает случайную запись в последовательную запись с добавлением для повышения производительности транзакций.

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

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

  • checkpoint: Через некоторое время,redo logЭто будет грандиозно. Если восстановление каждый раз начинать с первой записи, процесс восстановления будет очень медленным. С целью сокращения времени восстановления, введениеcheckpointмеханизм. регулярноdatabufferсбрасывает содержимое на дискdatafileвнутри, а затем очиститьcheckpointпредыдущийredo log.

  • Автоматическое восстановление: InnoDB путем загрузки последнего моментального снимка, а затем воспроизведения самого последнегоcheckpointвсе после точкиredo logТранзакции (в том числе незафиксированные и откатные), затем пройденныеundo logОткатите эти незафиксированные транзакции, чтобы завершить восстановление данных. Следует отметить, что,undo logПо сути, это тоже данные строки, и операция записи в нее тоже будет записана.redo logвнутри, т.е.undo logтакже черезredo logдля обеспечения настойчивости.

    бизнес-процесс

На картинке выше показан общий процесс выполнения операции записи транзакции, во всем процессе есть только одна операция сброса, т.е.redo logзаписывающий диск. На самом деле, запись на диск не обязательно сразу сохраняется на диск, в зависимости от конфигурации базы данных по умолчанию.Innodb_flush_log_at_trx_commit=1, то есть один разredo logОперация записи на диск будет немедленно записана на диск, что является самым безопасным решением.

журнал повторов записи на диск

Несколько транзакций в InnoDB разделяют однуredo log buffer, при записи диска текущийbufferСохранение нескольких журналов транзакций в транзакции независимо от того, была ли транзакцияcommit, а не только транзакцииcommitвызоветredo logзапись на диск, другие операции, такие какredo log bufferНедостаточно места, триггерcheckpoint, примерshutdownиbinlogСрабатывает при переключенииredo logоперация записи на диск.

3.3 MVCC

InnoDB использует механизм MVCC для улучшения параллелизма уровня изоляции RR.MVCC (Multi-Version Concurrency Control)Многоверсионный протокол управления параллелизмом, который делит операции чтения на две категории:Снимок прочитанитекущее чтение.Читается последняя версия записи, и к возвращенной записи добавляется блокировка, чтобы гарантировать, что другие транзакции не смогут одновременно изменить ее..

  • Чтение моментального снимка: простая операция запроса, которая относится к чтению моментального снимка и не блокируется. как:

1 select * from table where ?;скопировать код

  • Текущее чтение: специальные операции чтения и операции вставки/обновления/удаления относятся к текущему чтению и должны быть заблокированы. Ниже приведены текущие показания:

1  select * from table where ? lock in share mode;2  select * from table where ? for update;3  insert into table values (…);4  update table set ? where ?;5  delete from table where ?;скопировать код

Чтение моментальных снимков выполняется черезundo logдля достижения множественного контроля версий. Как показано ниже, каждая строка данных:row_idидентификатор строки,trx_idпредставляет идентификатор самой последней измененной транзакции,db_roll_ptrуказать наundo segmentсерединаundo logуказатель. Когда снимок будет прочитан, сравните идентификатор текущей транзакции сtrx_idотношения, еслиtrx_idЕсли он меньше id транзакции, то данные будут видны текущей транзакции, иначе не будут видны, если не видны, то пройдутdb_roll_ptrНайдите запись версии истории, вытащите самую последнюю видимую запись истории.undo logСсылка будет не очень глубокой, предысторияpurgeПоток периодически очищает бесполезные исторические версии (когда нет активных зависимостей транзакций,undo logможно удалить).

undo log

3.4 Анализ блокировок: краткое изложение в «Анализе обработки блокировок InnoDB» Хэ Дэнчэна

Текущее чтение будет заблокировано, как добавить? Это зависит от конкретной ситуации - уровня изоляции и индексации. При уровне изоляции RR InnoDB для того же оператора SQL:

DELETE FROM  T1  WHERE ID=10;

  • Когда ID указан как первичный ключКогда: заблокировать индекс первичного ключаid=10запись о. 

  • Когда столбец ID является уникальным индексом: Первая блокировка уникального индексаid=10строку, а затем заблокировать индекс первичного ключаname=dлиния. 

  • Когда идентификатор указан как вторичный индекс: по вторичному индексу дастid=10Блокировки X добавляются ко всем строкам заблокированной строки, а блокировки GAP будут добавлены к переднему и заднему диапазонам заблокированных строк; в индексе первичного ключа блокировки X будут добавлены к соответствующим записям.

  • Когда столбец ID не индексируется: в таком случаеОчень серьезные последствия! Все строки в индексе первичного ключа блокируются X, а все пробелы блокируются GAP! Данные всей таблицы заблокированы, параллелизма нет вообще, поэтому обязательно проверьте, индексируется ли текущий оператор условия read where.

Значение блокировки GAP: текущая транзакция занимает диапазон пробела, чтобы предотвратить вставку данных в этот диапазон другими транзакциями, вызывая фантомные чтения, которые происходят только на уровне изоляции RR. Если столбец идентификатора является уникальным индексом (или индексом первичного ключа), а идентификатор в текущем операторе условия чтения не существует, InnoDB также добавит блокировку GAP к диапазону.

4. Структура индекса

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

4.1 Дерево Б+

Причина, по которой InnoDB использует деревья B+ для организации индексов, определяется ее плоской структурой. Нелистовые узлы записывают ключевые значения столбцов индекса, а реальные данные хранятся только в листовых узлах.Преимущество этого в том, что нелистовые узлы очень подходят для кэширования (большой узел около 16k и может хранить более 1200 ключевых значений). Дерево В+ в реальной БД очень плоское, при высоте 3 емкость может достигать 22 Гб, при высоте 4 — 26 Тб, кроме того, большие узлы связаны между собой двусвязным списком, что удобно для последовательного сканирования.

В+ дерево
большой узел

4.2 Кластерный индекс и вторичный индекс

  • Кластеризованный индекс: в соответствии с первичным ключом каждой таблицы строится дерево B+, а конечные узлы хранят данные записи строки таблицы (конечные узлы кластеризованного индекса также называются страницами данных). Кластерный индекс — это метод хранения данных. Установка для идентификатора первичного ключа автоматического увеличения может привести к тому, что случайные вставки будут заменены последовательными добавлениями без разделения и фрагментации страниц, что повысит производительность записи.

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

4.3 Стратегия высокопроизводительного индексирования

Правильное создание и использование индексов — основа высокопроизводительных запросов.

  • Независимый столбец: означает, что столбец индекса не может быть частью выражения или параметром функции. Мы должны выработать привычку упрощать условие WHERE, всегда оставляя только столбец индекса с одной стороны от символа сравнения.

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

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

  • Индекс покрытия:Если индекс содержит (или покрывает) значения всех полей, которые необходимо запросить, он называется «покрывающим индексом».. Это очень полезный инструмент, который может значительно повысить производительность, нужно только сканировать вторичный индекс, не возвращаясь к таблице.

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

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

5. Резюме

Back-end RD часто сталкивается с проблемами взаимоблокировки MySQL и медленными запросами в повседневной работе.С этими проблемами мы можем быстро понять принципы транзакций и индексации InnoDB; напротив, после понимания принципов мы рассмотрим предыдущие встречи.Сцена также может внезапно стать ясно. Благодаря этой статье, я надеюсь, вы сможете понятьКак InnoDB гарантирует транзакции? Как поддерживать высокий параллелизм? Как хранятся данные?

Ссылаться на

  • Анализ обработки блокировки InnoDB

  • «Высокопроизводительный MySQL»

  • Принцип реализации механизма хранения InnoDB MVCC

  • Подробное объяснение принципа индекса MySQL InnoDB

  • MySQL Engine поддерживает роуминг журнала повторов InnoDB

Автор: Донг Минбинь

Руководитель: Ченг Тяньлян

Монтажер: Чжун Янь

Сайт: tech.lianjia.com.

Четное

многие

Изысканный

цвет

Пожалуйста, нажмите QR-код справа

Обратите внимание на наш паблик аккаунт

Технология продукта на первом месте

Категории