Индекс базы данных был очень важной концепцией, поэтому понимание знаний, связанных с индексами, необходима для развития в задней части кольца. Эта статья представляет собой краткое изложение моего обучения, имеет знание индекса с момента начала выполнения занесенного развития, многие понятия из первоначальной расплывчатой и не поняли до сих пор, как правило, не имеют более четкого понимания, попробуйте поставить некоторые очки На индекс и почему мне нужно сделать, чтобы объяснить, чтобы понять, включая использование индекса концепций, связанных с индексными концепциями MySQL InnoDB, и концепция того, как проектировать и использование индекса, индекс для InnoDB и Samsung, пойдете к объяснению То, что я узнал из необходимости в таких, если у вас неправильные места, пожалуйста, укажите.
B+ Tree
В InnoDB структуры данных, используемые индексами,B+ Tree,здесьB да Balanceзначение. Очень отличительной особенностью дерева В-типа является то, что количество слоев дерева относительно невелико, и каждый слой имеет множество узлов, а расстояние от каждого листового узла дерева до корневого узла одинаково ( вот почему это называетсяBalance TreeПричина), кроме того, каждый узел дерева является страницей данных, так что каждому узлу требуется только один IO, чтобы прочитать все это. Эта структура гарантирует, что дисковый ввод-вывод может выполняться как можно реже при запросе данных, обеспечивая при этом стабильность ввода-вывода.
B+ Tree и B Treeразные,B+ Tree, данные могут храниться только в листовых узлах, внутренние узлы будут содержать только указатели иB TreeДанные могут храниться во внутренних листовых узлах. следовательноB+ TreeКлючевым преимуществом является то, что промежуточные узлы не содержат данных, поэтомуB+ Treeнамного меньше, чемB Tree, и может хранить больше данных в памяти. Кроме того,B+ TreeКаждый листовой узел содержит ссылки на соседние узлы, что позволяет быстро перемещаться по диапазону.
Первичные и вторичные индексы
В механизме хранения InnoDB каждый индекс соответствуетB+ Tree, индексы InnoDB в основном делятся на первичные индексы и вторичные индексы:
- основной индекс: файлы, содержащие записи в соответствии сkeyЗаказ отсортирован, этоkeyЭто первичный индекс, то есть первичный ключ, также известный как кластеризованный индекс. Поскольку невозможно хранить строки в двух разных местах одновременно, таблица может иметь только один кластеризованный индекс. В InnoDB конечные узлы первичного индекса хранят всю строку данных, что также означает, что таблица в InnoDB должна иметь первичный индекс;
- Вспомогательный индекс: аkeyУказанный порядок отличается от физического порядка записей файла, этоkeyвспомогательный показатель. Вторичные индексы в InnoDB не хранят фактические данные в листовых узлах, а содержат только значения первичного индекса. Это означает, что если вы используете вспомогательный индекс для поиска данных, вы можете найти только основной индекс, а затем снова просмотреть следующие деревья основных индексов в соответствии с этим основным индексом и выполнить операцию возврата таблицы;
Как упоминалось выше, в таблице InnoDB должен быть первичный ключ, поэтому некоторые люди могут использовать уникальный идентификатор ID-номера в качестве первичного индекса, что является большой ошибкой. Я только что упомянул, что первичный ключ также называется кластерным индексом, он должен быть отсортирован по порядку и требует кластеризации. Если номер удостоверения личности используется в качестве первичного ключа, нельзя гарантировать, что данные, вставляемые каждый раз, располагаются в порядке номера удостоверения личности, что делает каждую вставку первичного ключа полностью случайной, что может привести к каждой вставке фрагмента данных Проблема разделения страниц (эта тема будет рассмотрена позже). Поэтому, когда структура таблицы определена, следует использовать агрегированную.keyВ качестве первичного ключа, если его действительно нет, вы можете использоватьAUTO INCREMENTСуррогатный ключ действует как первичный индекс, который обеспечивает последовательную запись строк данных. Если вы действительно вообще не определяете первичный ключ, InnoDB вместо этого выберет уникальный ненулевой индекс, но если такого индекса нет, InnoDB неявно определит первичный ключ как кластеризованный индекс.
Из-за такой структуры индексов InnoDB возникают некоторые ограничения:
- Индекс нельзя использовать, если поиск не начинается с крайнего левого столбца индекса;
- Некоторые столбцы в совместном указателе нельзя пропускать;
- Если в запросе есть диапазон столбцов, все столбцы справа не могут быть использованы;
Вышеупомянутые точки в основном представляют собой «крайний левый префикс», который часто можно услышать. Давайте объясним эту проблему на нескольких примерах. Могут быть некоторые неуместные примеры, но я надеюсь, что они могут объяснить проблему, о которой я хочу сказать. Предположим, у нас есть таблица сотрудников со следующей структурой:
| Column | Type | Usage | Index |
|---|---|---|---|
| id | bigint | Primary Key | primary_index |
| employee_id | varchar(10) | идентификатор сотрудника | employee_id_index |
| name | varchar(20) | Имя | name_age_gender_index |
| age | int | возраст | name_age_gender_index |
| gender | int | Пол | name_age_gender_index |
В этой таблице у нас есть совместный индекс (имя, возраст, пол), структура этого индекса примерно такая, как показано на следующем рисунке:
Предположим, что в приведенном выше конечном узле есть несколько сотрудников с именами BX и iCell, и их возраст не совпадает.Они сортируются сначала по имени, а затем по возрасту.
Запрос 1:
SELECT * FROM employees WHERE name='BX' AND age=19 AND gender=0;
Приведенный выше запрос на основе дерева индексов (имя, возраст, пол) находит данные индекса с id 2, которые удовлетворяют условиям, а затем продолжает поиск по ссылкам соседних узлов, находит, что следующие данные не соответствуют условиям , и, наконец, попадает в индекс. Данные - это данные с идентификатором 2, потому что они должны найти все данные строки, поэтому в соответствии с идентификатором 2 перейдите к дереву индекса первичного ключа и продолжайте возвращаться к таблице, чтобы найти данные результата.
Запрос 2:
SELECT * FROM employees WHERE name='iCell';
Выполните поиск по дереву индекса (имя, возраст, пол), найдите, что данные индекса с id 3 соответствуют условиям, а затем продолжите поиск по ссылкам соседних узлов, и найдите, что следующие данные также соответствуют условиям, продолжайте поиск по ссылкам узла, пока не будут найдены данные больше не удовлетворяют условиям, поэтому индексом попадания являются данные с id 3, 4 и 5, а затем продолжить выполнять операцию таблицы возврата в соответствии с этими значениями id до получить данные о результате.
Запрос 3:
SELECT * FROM employees WHERE age=17;
В соответствии с принципом «крайний левый префикс» нет индекса с префиксом возраста, поэтому этот запрос не может использовать индексное дерево (имя, возраст, пол) для поиска данных и должен перейти к основному индексу для выполнения полного сканирования таблицы. , что является не чем иным, как очень медленным. Поэтому, если вы хотите, чтобы этот запрос попадал в индекс, вам нужно добавить индекс только для возраста или добавить совместный индекс с префиксом возраста. Или есть другой метод для такого рода ситуаций, который заключается в использовании метода запроса IN для пропущенного столбца индекса, чтобы иметь совпадение «крайний левый префикс», но здесь поле имени не подходит для метода запроса IN.
Запрос 4:
SELECT * FROM employees WHERE name like 'B%' AND age=17;
SELECT * FROM employees WHERE name='iCell' AND age > 18 AND gender=1;
Из-за ограничения дерева B+, когда в запросе есть запрос диапазона определенного столбца, столбцы, стоящие за запросом диапазона, не могут использовать индекс. В приведенном выше запросеlike B%иage > 18Все запросы диапазона, поэтому последующие запросы не могут быть найдены непосредственно в дереве индекса.
В этом случае версия MySQL 5.6 добавляет Index Condition Pushdown Технология, если оператор where в запросе может использовать существующие поля в индексе (например, имя, возраст, пол здесь), при обходе индекса делать выводы об этих полях и напрямую отфильтровывать значения, которые делают не соответствуют условиям, уменьшая таблицу доступа уровня механизма и количество раз, когда уровень MySQL Server обращается к механизму хранения. Однако этот прием не конфликтует с «крайним левым префиксом», а лишь оптимизирует фильтрацию данных.
Запрос 5:
SELECT * FROM employees WHERE employee_id=11;
Обратите внимание на предыдущее определение таблицы данных, employee_id имеет тип varchar, но этот оператор запроса сравнивает его с числовым типом.В это время будет запущено неявное преобразование типа MySQL, и строка будет преобразована в число для сравнения, то есть приведенное выше утверждение эквивалентно:
SELECT * FROM employees WHERE CAST(employee_id AS int)=11;
То есть в этом запросе над полем индекса выполняется функциональная операция, которая разрушит порядок значений индекса, поэтому индекс не будет затронут, а вместо этого будет выполнено полное сканирование таблицы.
Запрос 6:
SELECT age, gender FROM employees WHERE name='iCell';
Эта ситуация похожа на пример, приведенный в Запросе 2, но результат этого запроса требует, чтобы были возвращены только возраст и пол, а значения возраста и пола включены в индекс, чтобы вместо этого можно было вернуть его напрямую. запроса таблицы. Если индекс содержит значения всех полей, которые необходимо запросить, он является покрывающим индексом.Использование покрывающего индекса не требует операции с таблицей возврата, что может повысить эффективность запроса данных.
Как ORDER BY использует индексы
сказатьORDER BYКак пользоваться индексом для сортировки, надо сначала разобратьсяORDER BYКак это сортируется. В MySQL каждому потоку выделяется место в памятиbufferДля сортировки также есть параметр, который называетсяmax_length_for_sort_data, функция этого параметра — указать длину поля строки, возвращаемой сортировкой.Значение по умолчанию — 1024, а минимальное значение — 4. Если длина поля строки, возвращаемой сортировкой, не превышает значение этого параметра , будет использоваться одна сортировка доступа, в противном случае будет использоваться вторая сортировка доступа.
Теперь мы по-прежнему используем приведенную выше таблицу сотрудников, чтобы проиллюстрировать проблему, со следующим утверждением:
SELECT name, age, employee_id FROM employees WHERE name='iCell' ORDER BY employee_id;
Теперь возвращаемые поля той сортировки, которую я хочу проверить, включают только имя, возраст и employee_id, который по умолчанию точно не будет превышать 1024, поэтому будет использоваться одноразовая сортировка доступа Процесс выглядит следующим образом:
- инициализацияbuffer
- Ударьте значение, имя которого 'iCell' в соответствии с самым левым принципом сопоставления, и узнайте идентификатор первичного ключа в соответствии с вспомогательным индексом;
- Извлеките значение всей строки в соответствии с идентификатором первичного ключа, а затем сохраните значения трех возвращенных столбцов имени, возраста и employee_id вbufferсередина;
- Повторяйте шаги 2 и 3 выше, пока условия запроса больше не будут выполняться;
- Отсортировать данные в буфере по employee_id;
- Вернуть отсортированный результат;
Итак, предположим, что мой текущийmax_length_for_sort_dataЕсли значение очень мало, а длина возвращаемого запрашиваемого подраздела превышает это значение, то будет использоваться вторичная сортировка доступа Процесс выглядит следующим образом:
- инициализацияbuffer
- Ударьте значение, имя которого 'iCell' в соответствии с самым левым принципом сопоставления, и узнайте идентификатор первичного ключа в соответствии с вспомогательным индексом;
- Извлеките значение всей строки в соответствии с идентификатором первичного ключа, а затем сохраните отсортированную строку employee_id и значение первичного ключа в буфере;
- Повторяйте шаги 2 и 3 выше, пока условия запроса больше не будут выполняться;
- Отсортировать данные в буфере по employee_id;
- По первичному ключу в результате сортировки будет возвращена табличная операция, и будет возвращен окончательный результат;
Вышеуказанные два вида не что иное, как то, что MySQL считает, что памяти недостаточно.Если памяти достаточно, он будет использовать больше памяти, чтобы избежать чрезмерных операций возврата таблицы и увеличить доступ к диску. Что делать, если пространство памяти, запрашиваемое сортировкой, не используется? параметрsort_buffer_sizeОн предназначен для управления размером памяти сортировки.Если памяти недостаточно, временный файл на диске будет использоваться для внешней сортировки слиянием.
Зная приведенную выше операцию сортировки в сочетании с предыдущим индексом покрытия иB+ TreeЛогика индекса, есть ли способ его оптимизировать?ORDER BYпроцесс. Прежде всего, будь то первичная сортировка доступа или вторичная сортировка доступа, она должна быть вbufferсортировать данные иB+ TreeСами листовые узлы расположены по порядку, поэтому, пока отсортированные строки могут быть сопоставлены с индексом в соответствии с принципом самого левого сопоставления, можно избежать шагов сортировки памяти. Кроме того, описанный выше шаг сортировки также должен выполнять операцию возврата таблицы, поэтому, если оператор запроса может попасть в покрывающий индекс, можно ли избежать операции возврата таблицы? Кроме того, было бы неплохо использовать один и тот же индекс как для сортировки, так и для поиска строк.
Индекс Samsung
В книге "Высокопроизводительный MySQL" есть книга "Высокопроизводительный MySQL".Relational Database index design and the optimizers", в книге есть понятие "Samsung Index", оно определяется так:
- Встречайте первую звезду: вывезти
WHEREДля соответствующих столбцов после оператора используйте эти столбцы в качестве первых столбцов индекса, чтобы индекс можно было использовать для максимально возможной фильтрации ненужных данных и уменьшения масштаба обработки данных; - Встречайте вторую звезду: будет
ORDER BYВ индекс добавляются столбцы, порядок этих столбцов не меняется, а столбцы, где уже появилась первая звездочка, не учитываются, а индекс используется для сортировки; - Удовлетворить третью звездочку: добавьте оставшиеся столбцы в операторе запроса к индексу, чтобы добиться эффекта покрытия индекса.
Тем не менее, индекс с тремя звездами часто является идеальной ситуацией.В действительности часто существуют требования для запроса диапазона и сортировки одновременно, поэтому трудно удовлетворить первую звезду и вторую звезду одновременно, например следующее утверждение:
SELECT name, age FROM employees WHERE age BETWEEN 15 AND 30 AND gender=1 ORDER BY name;
В соответствии с приведенным выше SQL для построения индекса могут возникнуть две ситуации:
Индекс для первого случая (возраст, пол, имя):
- Удовлетворить первой звездочке: указать возраст и пол в индексе, чтобы был столбец индекса и столбец фильтра после выполнения WHERE;
- Не удается удовлетворить вторую звезду: возраст - это запрос диапазона, пол в настоящее время не упорядочен;
- Удовлетворить третью звезду: поместить имя столбца запроса в индекс;
Индекс для второго случая (пол, имя, возраст):
- Не соответствует первой звездочке: может быть сопоставлен только столбец индекса пола;
- Удовлетворить вторую звезду: при условии равноправия полов имена расположены по порядку;
- Удовлетворить третью звезду: поместить возраст столбца запроса в индекс;
Что касается понятия индекса Samsung, то здесь просто пример, а далее "Relational Database index design and the optimizersНекоторые примеры оптимизации индексов, упомянутые в книге, содержат больше пояснений.
разделение страницы
Как упоминалось ранее, данные в InnoDB хранятся на странице данных, а данные вставляются на страницу данных в порядке индекса, поэтому данные компактно сортируются, но если данные вставляются случайным образом, это может привести к страница данных Проблема деления.
Допустим, страница данных может хранить только 3 части данных, а уже есть 3 части данных (100, 200, 300), если вы хотите вставить часть данных 150, вы подадите заявку на новую страницу данных, 100 , 150 шт. Данные хранятся на исходной странице данных, а 200 и 300 сохраняются на новой странице данных, что может вызвать проблему низкого использования страницы данных.
Вышеупомянутая проблема возникает не только при вставке данных, но и при удалении данных. Здесь следует отметить, что если определенный фрагмент данных на странице данных будет удален, то позиция, оставленная данными, не будет уменьшена, а будет повторно использована.Если данные всей страницы удалены, то эта страница также будет удалена. повторного использования. Если использование двух соседних страниц данных очень мало, система объединит данные этих двух страниц в одну из страниц, а другая страница будет в состоянии повторного использования. Таким образом, удаление данных с помощью удаления не вернет табличное пространство.
Чтобы решить проблему неиспользования табличного пространства, вызванного частым удалением данных, вы можете решить эту проблему, перестроив таблицу, например, с помощью следующей команды:
alter table table_name engin=InnoDB;
Поток этой команды в основном:
- Создайте новую временную таблицу, результат будет таким же, как и в исходной таблице;
- по первичному ключуidСчитайте данные из исходной таблицы и вставьте их в новую таблицу в порядке возрастания;
- Заменить старую таблицу новой таблицей, удалить старую таблицу;
Поэтому мы используемAUTO INCREMENTРежим вставки данных первичного ключа точно соответствует сценарию инкрементной вставки. Каждый раз, когда вставляется новая запись, это операция добавления, которая не требует перемещения других записей и не вызывает разделения листовых узлов.
Заканчивать
Вышеизложенное представляет собой краткое изложение того, что я узнал об индексации, могут быть некоторые упущения или ошибки. Тем не менее, индекс всегда изучался, и кажется, что это очень широкий круг знаний.Эту статью можно рассматривать только как учебную заметку.Я буду продолжать дополнять ее, когда столкнусь с чем-то, что стоит записать в последующем процессе практики.