Во время собеседования у мелких партнеров возникает особенно распространенная проблема, а именно форма возврата базы данных. Что такое форма возврата? Зачем нужна форма возврата?
Сегодня брат Сонг хочет поговорить с вами на эту тему.
1. Структура индекса
Чтобы понять эту проблему, вам нужно сначала понять структуру данных хранения индексов в MySQL. На самом деле, многие друзья, возможно, слышали об этом, B+Tree!
Что такое дерево B+? Тогда вы должны сначала понять, что такое B-Tree, посмотрите на следующую картинку:
Передний — B-Tree, второй — B+Tree, разница между ними в следующем:
- В B-дереве все узлы будут иметь указатели на определенные записи; в B+Tree только конечные узлы будут иметь указатели на определенные записи.
- Разные листья в B-дереве не связаны друг с другом, все листовые узлы в B+Tree соединены указателями.
- В B-Tree указатель на конкретную запись может быть получен в нелистовом узле, и эффективность поиска нестабильна, в B+Tree указатель на конкретную запись должен быть получен в листовом узле, а поиск эффективность стабильна.
Опираясь на два вышеприведенных пункта анализа, мы можем сделать следующие выводы:
- В B+Tree, поскольку неконечные узлы не имеют указателей на конкретные записи, в неконечных узлах может храниться больше элементов индекса, что может эффективно уменьшить высоту дерева и повысить эффективность поиска.
- В B+Tree листовые узлы соединены указателями, поэтому, если есть необходимость сканирования диапазона, это будет очень легко реализовать, в то время как для B-Tree сканирование диапазона должно сохранять листовые узлы и неконечные узлы. перемещаться между точками.
Во-первых, сколько фрагментов данных может хранить B+Tree? Взяв в качестве примера B+Tree, проиндексированное по первичному ключу (принцип расчета объема данных, хранимых во вторичном индексе, аналогичен, но форматы данных, хранящихся на листовых узлах и нелистовых узлах, немного отличаются), мы можем сделать простой расчет.
Когда компьютер хранит данные, минимальной единицей хранения является сектор, а размер сектора составляет 512 байт, а минимальной единицей файловой системы (такой как XFS/EXT4) является блок, а размер блока 4 КБ. Когда движок InnoDB хранит данные, они представлены в единицах страниц.Размер каждой страницы данных по умолчанию составляет 16 КБ, то есть четыре блока.
Основываясь на этом запасе знаний, мы можем приблизительно рассчитать, сколько данных может хранить B+Tree.
Если предположить, что запись в базе данных составляет 1 КБ, то страница может хранить 16 фрагментов данных (конечные узлы); для нелистовых узлов хранится значение первичного ключа + указатель.В InnoDB размер указателя составляет 6 слов. Раздел, предполагая, что наш первичный ключ — bigint, тогда первичный ключ занимает 8 байт,Конечно, есть и другие заголовки, которые также занимают байты, которые мы здесь рассматривать не будем., давайте сделаем грубый расчет, маленькие друзья могут знать, что они имеют в виду:
16*1024/(8+6)=1170
То есть неконечный узел может указывать на 1170 страниц, тогда объем данных, который может хранить трехуровневое B+Tree, составляет:
1170*1170*16=21902400
Можно хранить 21 миллион единиц данных.
В механизме хранения InnoDB высота B + Tree обычно составляет 2-4 уровня, что может соответствовать хранению десятков миллионов данных.При поиске данных поиск по странице представляет собой один ввод-вывод, затем мы запрашиваем первичный ключ На самом деле требуется максимум 2-4 операции ввода-вывода.
Давайте сначала разберемся с этим деревом B+.
2. Два типа индексов
Как мы все знаем, индексы в MySQL имеют множество различных методов классификации, которые можно разделить в зависимости от структуры данных, логической точки зрения или физического хранения Среди них, в зависимости от метода физического хранения, его можно разделить на кластеризованный индекс и не -кластерный индекс Кластерный индекс.
Индекс первичного ключа, который мы обычно называем, на самом деле является кластеризованным индексом; кроме индекса первичного ключа, другие индексы называются индексами непервичного ключа, а индексы непервичного ключа также называются вторичными индексами (вторичный индекс) или называются индексами. вспомогательный указатель.
Для индекса первичного ключа и индекса непервичного ключа используется структура данных B+Tree, единственное отличие состоит в том, что содержимое, хранящееся в листовом узле, отличается:
- Листовой узел индекса первичного ключа хранит полную строку данных.
- Листовые узлы индекса, не являющегося первичным ключом, хранят значение первичного ключа.
Это самая большая разница между ними.
Итак, когда нам нужно запросить:
- Если данные запрашиваются через индекс первичного ключа, например
select * from user where id=100, тогда вам нужно только выполнить поиск B+Tree индекса первичного ключа, чтобы найти данные. - Если данные запрашиваются через индекс, не являющийся первичным ключом, например
select * from user where username='javaboy', то вам нужно сначала выполнить поиск в B+Tree, проиндексированном по столбцу имени пользователя, получить значение первичного ключа после завершения поиска, а затем выполнить поиск в B+Tree, проиндексированном по первичному ключу, чтобы получить полную строку данных.
Для второго метода запроса выполняется поиск всего двух B+деревьев,Поиск B+Tree в первый раз, чтобы получить значение первичного ключа, а затем поиск B+Tree, индексированный по первичному ключу.Этот процесс представляет собой так называемую таблицу возврата.
Из вышеприведенного анализа мы также можем видеть, что запрос через индекс, не являющийся первичным ключом, должен сканировать два B+дерева, в то время как запрос через индекс первичного ключа должен сканировать только одно B+дерево, поэтому, если позволяют условия, он рекомендуется выбирать сначала в запросе Поиск по индексу первичного ключа.
3. Вы обязательно вернете бланк?
Итак, вам нужно вернуться к таблице без индекса первичного ключа?
неуверенно!
Если сам столбец запроса существует в индексе, то даже при использовании вторичного индекса нет необходимости возвращаться к таблице.
Например, у меня есть следующая таблица:
Поля uname и address образуют составной индекс.В настоящее время, хотя это и вторичный индекс, конечные узлы дерева индекса сохраняют не только значение первичного ключа, но и значение адреса.
Давайте посмотрим на следующий анализ:
Видно, что в это время используется индекс uname, но значение последнего Extra равноUsing index, что означает, что используется сканирование покрытия индекса (покрывающий индекс).В это время ненужные записи напрямую отфильтровываются из индекса и возвращаются результаты попадания.Этот шаг выполняется на уровне сервера MySQL и его не нужно возвращать к столу.
4. Расширение
Основываясь на анализе в первом и втором разделах, давайте посмотрим, почему в базе данных рекомендуется использовать автоинкрементный первичный ключ.
- Самоувеличивающийся первичный ключ обычно занимает небольшое пространство, int — 4 байта, а bigint — 8 байтов. Поскольку листовые узлы вторичного индекса хранят первичный ключ, если первичный ключ занимает мало места, это означает, что листовые узлы вторичного индекса в будущем будут занимать меньше места (косвенно уменьшая высоту B+Tree и повысить эффективность поиска).
- Самоувеличивающийся первичный ключ вставляется быстрее, и его можно вставлять напрямую, не вызывая разбиения конечных узлов и других проблем (нет необходимости перемещать другие записи); быть вставлены в два существующих.В середине данных это может вызвать проблемы, такие как разделение листовых узлов, и эффективность вставки низкая (необходимо переместить другие записи).
Конечно, это обсуждение на техническом уровне, если автоинкрементный первичный ключ не может быть использован в бизнесе или есть другие требования, которые делают невозможным использование автоинкрементного первичного ключа, то нет возможности повторно выбрать передовую практику, отвечающую новым требованиям.
Хорошо, сегодняшняя темаформа возврата, теперь все понимают что такоеформа возвратаправильно?