Эта статья из публичного аккаунта WeChat: Холлис, первоначальный автор — неназванный фанат.
Я полагаю, что многие люди знакомы с индексами MySQL.Индекс — это структура данных, которая помогает MySQL эффективно получать данные.
Поскольку индексирование является относительно важным знанием в MySQL, я полагаю, что многие люди имеют определенное понимание, особенно в интервью, которые появляются очень часто. Арендодатель считает, что у него много знаний об индексах MySQL, и, поскольку он недавно ищет собеседование, он рассмотрел много знаний только об индексах.
Тем не менее, я все еще был сломлен: только когда меня оскорбил интервьюер Али, я понял, что мои знания об индексации были только на уровне ученика начальной школы.
Ниже приводится краткое изложение вопросов, связанных с индексом, и знаний в интервью Али.
Концепции индексирования, модели индексирования
Как мы говорили об индексации, ведь я упомянул, что объемы нашего бизнеса относительно велики, и каждый день генерируется около миллиона новых данных, поэтому у нас получается следующий диалог:
Интервьюер: Вы каждый день храните такой большой объем данных в реляционной базе данных?
Я: Да, мы используем базу данных MySQL онлайн.
Интервьюер: Есть миллионы данных в день, десятки миллионов в месяц, так что вы сделали некоторую оптимизацию для запроса?
Я: Мы создали некоторые индексы в базе данных (я сейчас очень сожалею о том, что сказал тогда).
Здесь видно, что интервьюер Али не будет задавать вопросы один за другим, как в некоторых компаниях, а начнет, основываясь на том, что сделал интервьюер, и на некотором содержании процесса интервью.
Интервьюер: Тогда вы можете сказать, что такое индекс?
Я: (Для меня этот вопрос определенно не сложен) Индекс на самом деле представляет собой структуру данных, которая может помочь нам быстро получить данные в базе данных.
Интервьюер: Итак, какую структуру данных использует индекс?
Я: (я также повторил этот вопрос) Общий MySQL в основном имеет две структуры: хэш-индекс и B+.
Индекс дерева, мы используем движок InnoDB, по умолчанию дерево B+.
Здесь я сыграл осторожную хитрость и специально сказал, что индекс связан с механизмом хранения. Надеюсь, интервьюер сможет задать мне несколько вопросов о механизмах хранения.
Интервьюер: Поскольку вы упомянули модель индекса B+ Tree, используемую InnoDB, вы знаете, почему используется B+.
дерево? Есть ли плюсы и минусы по сравнению с хэш-индексами?
Я: (внезапно почувствовал, что этот вопрос немного сложноват, но я все же просто ответил на него со своим запасом знаний) Потому что нижний слой Hash-индекса — это хеш-таблица, представляющая собой структуру, которая хранит данные в ключе-значении. Таким образом, множественные данные вообще не имеют последовательной связи в отношении хранения, поэтому для интервального запроса нельзя выполнить запрос напрямую через индекс, и требуется полное сканирование таблицы. Поэтому хэш-индексы подходят только для сценариев запросов с равными значениями. Дерево B+ представляет собой многостороннее сбалансированное дерево запросов, поэтому его узлы естественным образом упорядочены (левый дочерний узел меньше родительского узла, а родительский узел меньше правого дочернего узла), поэтому нет необходимости выполнять полное сканирование таблицы для запросов диапазона.
Интервьюер: В дополнение к приведенному выше запросу о диапазоне, можете ли вы назвать какие-либо другие различия?
Я: (Я плохо ответил на этот вопрос и потом погуглил)
Научно-популярное время: разница между индексом B+ Tree и хэш-индексом. Хэш-индекс подходит для запроса равного значения, но не может выполнять запрос диапазона. очень низкий, потому что есть проблема коллизии хэшей
Кластерный индекс, индекс покрытия
Интервьюер: Мы только что говорили о дереве B+, так что вы знаете, что может храниться в листовых узлах дерева B+?
Я: Дерево B+ InnoDB может хранить всю строку данных или может быть значением первичного ключа.
Интервьюер: Так в чем разница между ними?
Я: (Когда он спросил меня о листовом узле, я на самом деле предположил, что он может спросить меня о кластеризованном индексе и некластеризованном индексе) В InnoDB конечный узел индекса B+ Tree хранит всю строку данных — это Индексы первичного ключа, также известные как кластерные индексы. Листовой узел индекса B+ Tree хранит значение первичного ключа в виде индекса непервичного ключа, также известного как некластеризованный индекс.
Интервьюер: Итак, есть ли разница между кластеризованным индексом и некластеризованным индексом при запросе данных?
Я: Будут ли запросы кластерного индекса выполняться быстрее?
Интервьюер: Почему?
Я: Потому что конечный узел дерева индекса первичного ключа — это непосредственно вся строка данных, которую мы хотим запросить. Листовым узлом индекса непервичного ключа является значение первичного ключа.После того, как значение первичного ключа найдено, необходимо снова выполнить запрос через значение первичного ключа.
Интервьюер: Вы только что упомянули, что запрос индекса первичного ключа будет проверен только один раз, в то время как индекс непервичного ключа необходимо запрашивать несколько раз. (Позже я узнал, что этот процесс называетсяформа возврата) это верно во всех случаях? Будет ли индекс непервичного ключа запрашиваться несколько раз?
Я: (Э-э, я плохо ответил на этот вопрос, и я узнал позже, проверив информацию самостоятельно.индекс покрытияВы также можете запросить только один раз)
Время науки - покрывающий индекс Покрывающий индекс (покрывающий индекс) относится к выполнению оператора запроса, который можно получить только из индекса, без необходимости чтения из таблицы данных. Можно также сказать, что достигнут охват индекса. Когда оператор запроса соответствует условиям покрытия индекса, MySQL должен использовать индекс только для возврата данных, требуемых запросом, что позволяет избежать возврата к таблице после нахождения индекса, уменьшая ввод-вывод и повышая эффективность. Например, в таблицеcovering_index_sample есть общий индекс idx_key1_key2(key1,key2). Когда мы передаем оператор SQL: выберите ключ2 из покрывающего_индекса_выборки, где ключ1 = 'keytest';, мы можем выполнить запрос по покрывающему индексу, не возвращаясь к таблице.
Индекс союза, совпадение крайнего левого префикса
Интервьюер: Неважно, если вы не знаете, я хотел бы спросить, какие факторы вы учитываете при создании индекса?
Я: Обычно у нас высокая вероятность запроса, и мы часто устанавливаем индекс как поле условия where.
Интервьюер: Вы когда-нибудь пользовались совместным указателем? Я: Да, мы создали совместный индекс для некоторых таблиц.
Интервьюер: Когда вы создаете объединенный указатель, как вы выбираете порядок между несколькими полями в объединенном указателе?
Я: Сначала мы ставим самые узнаваемые поля. Интервьюер: Зачем вы это делаете?
Я: (этот вопрос меня немного смутил, немного смутил) Может быть, таким образом процент попаданий был бы выше. . .
Интервьюер: Тогда знаете ли вы, что крайний левый префикс совпадает?
Я: (Я вдруг вспомнил, что интервьюер хотел спросить об этом. Я виню себя за то, что не подумал об этом только сейчас.) О, о, о. Это то, о чем вы только что спросили.При создании многоколоночного индекса, в соответствии с требованиями бизнеса, мы помещаем наиболее часто используемый столбец в предложение where в самый левый, потому что запрос индекса MySQL будет следовать принципу сопоставления самого левого префикса, то есть , наиболее часто используемый столбец в предложении where находится в самом левом углу. Таким образом, когда мы создаем совместный индекс, такой как (ключ1, ключ2, ключ3), это эквивалентно созданию трех индексов (ключ1), (ключ1, ключ2) и (ключ1, ключ2, ключ3), что является самым левым принципом сопоставления.
Хотя сначала я был немного сбит с толку и не подумал о совпадении крайнего левого префикса, интервьюер все же направил меня. Очень дружелюбный.
Выталкивание индекса, оптимизация запросов
Интервьюер: Какую версию MySQL вы используете в Интернете?
я: у нас MySQL 5.7
Интервьюер: Знаете ли вы, какие оптимизации были сделаны для индексов в MySQL 5.6?
Я: Извините, я не знал об этом. (Позже я проверил это, и есть более важная: оптимизация условия индексирования)
Научно-популярное время — Index Condition Pushdown (проталкивание индекса) В MySQL 5.6 представлена оптимизация проталкивания индекса, которая включена по умолчанию и может быть отключена с помощью SET optimizer_switch = 'index_condition_pushdown=off';. Примеры и пояснения, приведенные в официальной документации, следующие: (почтовый индекс, фамилия, имя) в таблице людей представляет собой индекс
SELECT * FROM people WHERE zipcode='95054' AND lastname LIKE '%etrunia%' AND address LIKE '%Main Street%';
Если технология проталкивания индекса не используется, MySQL запросит соответствующие данные из механизма хранения через zipcode='95054' и вернет их на сервер MySQL, а затем сервер MySQL будет использовать фамилию LIKE '%etrunia%' и адрес LIKE '%Main' Street%', чтобы определить, соответствуют ли данные условиям. Если используется технология выталкивания индекса, MYSQL сначала вернет индекс, который соответствует почтовому индексу = '95054', а затем оценит, соответствует ли индекс условиям в соответствии с фамилией LIKE '%etrunia%' и адресом LIKE '%Main Street%' . Если условия соблюдены, соответствующие данные располагаются по индексу, если нет, то напрямую отбрасываются. С оптимизацией индекса pushdown вы можете уменьшить количество возвратов к таблице в случае запроса с похожими условиями.
Интервьюер: Имеет ли какое-либо влияние такое количество созданных вами индексов, или вы когда-нибудь использовали индексы для запросов к вашим операторам SQL и подсчитывали ли вы их?
Я: Это не засчитано, если мы не столкнемся с медленным SQL, мы будем исследовать
Интервьюер: Во время расследования есть ли способ узнать, есть ли индексный запрос?
Я: Вы можете просмотреть план выполнения инструкции sql с помощью объяснения и проанализировать использование индекса с помощью плана выполнения.
Интервьюер: Тогда что происходит, когда индекс явно создан, но индекс не передается во время выполнения?
Я: (смутно помню, что это связано с оптимизатором, но на этот вопрос так и не ответили)
Popular Science Time - Query Optimizer Запрос оператора SQL может иметь разные планы выполнения.Что касается того, какой план выбрать в итоге, вам нужно выбрать план с наименьшей стоимостью выполнения через оптимизатор. Перед фактическим выполнением оператора запроса с одной таблицей оптимизатор запросов MySQL выяснит все возможные решения для выполнения оператора, а затем найдет решение с наименьшей стоимостью после сравнения. Этот вариант с наименьшими затратами является так называемым планом выполнения. Процесс оптимизации примерно выглядит следующим образом: 1. Найти все возможные индексы согласно условиям поиска 2. Рассчитать стоимость полного сканирования таблицы 3. Рассчитать стоимость выполнения запросов с использованием разных индексов 4. Сравнить стоимость различных схем выполнения, чтобы найти самая низкая стоимость один
Интервьюер: О, давайте сейчас спросим много о знаниях об индексировании. Каков уровень изоляции транзакций ваших онлайн-данных?
Я: (позже я не буду подробно рассказывать об уровне изоляции транзакций)
Такое ощущение, что я недостаточно хорошо ответила.Если бы я знала все эти индексные вопросы, он бы задал еще вопросов, и я боюсь, что его оскорбят еще больше.
Резюме и выводы
Выше приведены вопросы о некоторых знаниях об индексации в интервью и ответы, которые я собрал. Я чувствую, что могу ответить примерно на 70% знаний об индексировании в процессе этого интервью, но только около 50% содержания является уверенным и полностью правильным.Похоже, я недостаточно знаю об индексировании.
Благодаря этому интервью я обнаружил, что такая крупная компания, как Ali, по-прежнему придает большое значение базовым знаниям. Раньше я думал, что самое важное, что нужно спрашивать об индексах, — это разница между Hash и B+, но я не ожидал, что я можно было бы спросить у оптимизатора запросов в конце концов.
В конце концов, неважно, смогу я пройти собеседование или нет, я очень благодарна за то, что у меня есть такая возможность позволить себе увидеть собственные недостатки. Я тоже многому научился из этого интервью. давай!
Использованная литература:Буклет Nuggets — «Как работает MySQL: понимание MySQL с истоков»