Это 23-й день моего участия в ноябрьском испытании обновлений. Узнайте подробности события:Вызов последнего обновления 2021 г.
Каковы сходства и различия между обычными индексами и уникальными индексами в различных бизнес-сценариях?
Давайте посмотрим разницу между ними на примере
Например: запрос имени пользователя по идентификационному номеру выберите имя из CUser, где id_card = 'XXXXXX';
- Создайте уникальный индекс для поля id_card
- Создайте нормальный индекс
Влияние операторов запросов на производительность
Предположим, что выполняемый оператор запроса — это select id from T, где k = 5. Сначала начните с корня дерева по дереву B+ - выполните поиск до конечного узла по слою - найдите запись через дихотомию внутри страницы данных.
- Обычный индекс — найти первую запись, удовлетворяющую условию — продолжить поиск следующей записи — пока не встретится запись, не удовлетворяющая условию
- Уникальный индекс — поскольку индекс определяет уникальность — поиск прекращается после первой записи, удовлетворяющей условию.
Разница в производительности — минимальная
InnoDB - данные считываются и записываются в единицах страниц данных Когда запись необходимо прочитать - в страницах, вся считывается в память - InnoDB, размер каждой страницы данных по умолчанию составляет 16 КБ. Поэтому движок читает и записывает страницу за страницей — когда найдена запись с k = 5, страница данных, на которой она находится, вся находится в памяти.
- Обычный индекс — больше операций «найти и оценить следующую запись» — нужен только один поиск по указателю и одно вычисление
Влияние процесса обновления на производительность
При обновлении страницы данных - страница данных находится в памяти - прямое обновление Страницы данных не находятся в памяти — не влияя на согласованность данных — InnoDB будет кэшировать эти операции обновления в буфере изменений. Это избавляет от необходимости читать страницу данных с диска. В следующий раз, когда запросу потребуется обратиться к странице данных — прочитать страницу данных в память — выполнить операции, связанные с этой страницей в буфере изменений — убедиться в правильности логики данных
change buffer
Постоянные данные - имеют копию в памяти, а также записываются на диск Операция применяется к исходной странице данных — процесс получения последнего результата называется слиянием. - Доступ к этой странице данных вызовет слияние - Системные и фоновые потоки будут регулярно объединяться - При нормальном завершении работы БД (shutdown) - слияние Сначала запишите операцию обновления в буфер изменений - уменьшите количество операций чтения с диска - скорость выполнения оператора будет значительно улучшена. Данные считываются в память — необходимо занять буферный пул — этот метод позволяет избежать использования памяти и улучшить ее использование.
Память в пуле буферов используется в буфере изменений, поэтому ее нельзя увеличить по беспроводной сети. Размер буфера изменений - может быть установлен динамически параметром innodb_change_buffer_max_size
- 参数 为 50 - change buffer的大小最多只能占用buffer pool的50%
При каких условиях можно использовать буфер изменений?
Уникальный индекс — все операции обновления — должны сначала определить, нарушает ли операция ограничение уникальности — для определения необходимо прочитать страницу данных в память — нет необходимости использовать буфер изменений ** Обновление уникального индекса не может использовать буфер изменений — обычный индекс может использовать **
Если в эту таблицу вставляется новая запись (4, 400), каков поток обработки InnoDB?
-
Целевая страница для обновляемой записи находится в памяти
- Уникальный индекс — находит позицию между 3 и 5 — определяет отсутствие конфликта — вставляет значение — завершает выполнение инструкции
- нормальный индекс - найти позицию между 3 и 5 - вставить это значение - конец выполнения оператора
Разница между обычным индексом и уникальным индексом в производительности оператора обновления является всего лишь суждением - потребляет небольшое количество процессорного времени.
-
Целевая страница для обновления этой записи отсутствует в памяти
- Уникальный индекс — прочитать страницу данных в память — решить, что конфликта нет — вставить это значение — конец выполнения инструкции
- Обычный индекс - обновить записи в буфере изменений, выполнение оператора заканчивается
Чтение данных с диска в память включает произвольный доступ ввода-вывода, который является одной из самых ресурсоемких операций в базе данных — буфер изменений уменьшает произвольный доступ к диску — улучшение производительности обновления очевидно!
Изменить сценарии использования буфера
Слияние — это момент обновления реальных данных. Основная цель буфера изменений — кэшировать записанные действия по изменению. — Перед слиянием страницы данных. — Чем больше изменений записано в буфере изменений. (чем больше обновлений будет на этой странице) - тем больше выгода
Предприятия, которые больше пишут и меньше читают - Вероятность того, что страница будет открыта сразу после написания, мала - Лучше всего использовать буфер изменений - Системы выставления счетов и регистрации
Запрос будет выполнен сразу после записи — сначала обновление будет записано в буфер изменений — но затем, поскольку вскоре будет осуществлен доступ к странице данных — процесс слияния будет запущен немедленно — так что количество операций ввода-вывода с произвольным доступом не будет уменьшено - стоимость обслуживания буфера изменений будет увеличена - Буфер изменений имеет побочный эффект
Выбор индекса и практика
Возможность запроса - без разницы
Обновление производительности - попробуйте выбрать нормальные индексы
Обычный индекс + буфер изменений — оптимизация обновления для таблиц с большими объемами данных
изменить буфер и журнал повторов
Например: вставьте в t(id, k) значения (id1, k1), (id2, k2)
Предполагая текущее состояние индексного дерева K, после нахождения местоположения страница данных, где находится k1, находится в памяти (буферный пул InnoDB), а страница данных, где находится k2, не находится в памяти.
1. Страница 1 находится в памяти, напрямую обновить память
2. Страницы2 нет в памяти - в области буфера изменений памяти записано сообщение "вставить строку в страницу2"
3. Запишите два вышеуказанных действия в журнал повторов.
Транзакция может быть завершена — стоимость выполнения этого оператора обновления очень низкая — записываются две ячейки памяти — записывается один диск [две операции объединяются для записи одного диска]
читать запросы после этого
1. Оператор чтения находится вскоре после оператора обновления — данные в памяти все еще там —
1. Чтение страницы 1 - возврат напрямую из памяти - не нужно обновлять данные из журнала повторов перед возвратом
1. Прочитать страницу 2 — прочитать страницу 2 с диска в память — применить журнал операций в буфере изменений — сгенерировать правильную версию и вернуть результат
журнал повторов сохраняет потребление операций ввода-вывода при случайной записи на диск [преобразование в последовательную запись]
Буфер изменений экономит потребление операций ввода-вывода при произвольном чтении диска