Эта статья участвовала в "Проект «Звезда раскопок»”, чтобы выиграть творческий подарочный пакет и бросить вызов творческим поощрительным деньгам.
Можно сказать, что индекс — это большое сердце базы данных.Если в базе данных нет индекса, то существование самой базы данных не имеет большого значения, и она ничем не отличается от обычных файлов. Таким образом, хороший индекс особенно важен для систем баз данных.Сегодня давайте поговорим об индексе MySQL.С точки зрения деталей и реального бизнеса, давайте рассмотрим преимущества индекса дерева B+ в MySQL и точки знаний, которые нам нужны обратите внимание при использовании index.
Эффективно используйте индексы
На работе самый прямой способ судить о том, нужно ли индексировать поле в таблице данных, заключается в следующем: будет ли это поле часто появляться в нашейwhereсостояние. С макро точки зрения с таким мышлением проблем нет, но с долгосрочной точки зрения иногда может потребоваться более детальное мышление, например, нам нужно не только построить индекс по этому полю? Лучше ли совместный индекс нескольких полей? Возьмем в качестве примера пользовательскую таблицу. Поля в пользовательской таблице могут иметьИмя пользователя,Идентификационный номер пользователя,Домашний адрес пользователяи т.п.
"1. Недостатки обычных индексов"
Теперь необходимо найти имя пользователя на основе идентификационного номера пользователя.В это время очевидно, что первый метод, который приходит на ум, этоid_cardСоздайте индекс сверху, строго говоря, это уникальный индекс, потому что идентификационный номер должен быть уникальным, тогда, когда мы выполняем следующий запрос:
SELECT name FROM user WHERE id_card=xxx
Его поток должен быть таким:
- Сначала выполните поиск в индексном дереве id_card, чтобы найти идентификатор первичного ключа, соответствующий id_card.
- Поиск по индексу первичного ключа по идентификатору, чтобы найти соответствующее имя
С точки зрения эффекта результат не представляет проблемы, но с точки зрения эффективности этот запрос кажется немного затратным, поскольку он извлекает два дерева B+, предполагая, что высота одного дерева равна 3, тогда высота двух деревьев равна 6. Поскольку корневой узел находится в памяти (здесь два корневых узла), конечное количество операций ввода-вывода, которые необходимо выполнить на диске, равно 4, а среднее время, затрачиваемое на один случайный ввод-вывод на диске, равно 10 мс, поэтому в конечном итоге это займет 40 мс. Это число среднее, не быстрое.
«2. Ловушка индекса первичного ключа»
Поскольку проблема заключается в таблице возврата, которая приводит к поиску в обоих деревьях, основная проблема состоит в том, чтобы увидеть, можно ли получить ее только в одном дереве. Здесь вы, возможно, нашли точку входа с точки зрения бизнеса,Идентификационный номер уникален, тогда наш первичный ключ не может использовать идентификатор автоинкремента по умолчанию, мы устанавливаем первичный ключ на наш идентификационный номер, так что для всей таблицы нужен только один индекс, и через идентификационный номер мы можем найти все необходимые данные, включая наш Кажется разумным думать об этом просто, пока вы указываете идентификатор в качестве номера идентификатора каждый раз, когда вставляете данные, но, если хорошенько подумать, возникает проблема.
Здесь, из характеристик дерева B+, данные дерева B+ хранятся на листовых узлах, и данные управляются в страницах.Одна страница 16К.Что это значит? Даже если у нас сейчас есть строка данных, она будет занимать страницу данных размером 16 КБ. Только когда наша страница данных будет заполнена, она будет записана на новую страницу данных. Новая страница данных и старая страница данных физическине обязательно непрерывный, и очень важно, чтобы, несмотря на то, что страницы данных были физически прерывистыми, данныелогически непрерывный.
Может быть, вам будет любопытно, какое это имеет отношение к идентификационному номеру, о котором мы говорим в качестве идентификатора первичного ключа? В этот момент следует обратить вниманиенепрерывныйЭто ключевое слово, идентификационный номер не является последовательным, что это значит? Когда мы вставляем фрагмент прерывистых данных, чтобы сохранить непрерывность, нам нужно переместить данные.Например, исходные данные на странице имеют 1-> 5, а в это время вставляется 3, тогда нам нужно переместите 5 в конец 3. Может быть, вы скажете, что это не так много накладных расходов, но если новые данные 3 заставляют эту страницу A быть заполненной, то это зависит от того, есть ли место на странице B позади нее. , начальные данные страницы B в это время должны быть теми, которые переполняются со страницы A, также необходимо переместить соответствующие данные. Если в это время на странице B недостаточно места, необходимо применить новую страницу C, а затем часть данных будет перемещена на эту новую страницу C, а связь между страницей A и страницей B будет прервана, и вставка между ними будет страницей C на уровне кода, чтобы переключить указатель связанного списка.
Подводя итог, прерывистые идентификационные номера в качестве первичного ключа могут вызвать перемещение данных страницы, случайный ввод-вывод и частое применение новых страниц.. Если мы используем самоинкрементный первичный ключ, то id должен быть последовательным, и не будет проблем с перемещением данных из-за случайного ввода-вывода, а стоимость вставки должна быть относительно небольшой.
На самом деле есть еще одна причина, по которой не рекомендуется использовать ID-номер в качестве первичного ключа: ID-номер слишком велик как число, и его приходится хранить в bigint, обычно школьнику этого достаточно. использовать int. Мы знаем, что одна страница может хранить 16 КБ. Когда пространство, занимаемое самим индексом, больше, это приведет к меньшему количеству данных, которые можно хранить на странице. Поэтому в случае определенного объема данных использование bigint требует больше страниц, чем int, то есть больше страниц памяти.
«3. Копье и щит совместной индексации»
Из вышеизложенного можно сделать два вывода:
- Старайтесь не возвращаться к столу
- Идентификационный номер не подходит для индекса первичного ключа
Поэтому, естественно, я подумал о совместном индексе, создайте совместный индекс [идентификационный номер + имя], обратите внимание на порядок совместного индекса и соблюдайте принцип крайнего левого. Поэтому, когда мы также выполняем следующий sql:
select name from user where id_card=xxx
Мы можем получить нужное нам поле имени без возврата таблицы.Однако проблема, что сам номер ID занимает слишком много места, до сих пор не решена.Это проблема самих бизнес-данных.Если вы хотите ее решить, мы можно использовать некоторые алгоритмы преобразования для преобразования. Исходные большие данные преобразуются в небольшие данные, такие как crc32:
crc32.ChecksumIEEE([]byte("341124199408203232"))
Идентификационный номер, который первоначально требовал 8 байтов дискового пространства, может быть заменен 4-байтовым кодом CRC, поэтому наша база данных должна добавить еще одно поле.crc_id_card, совместный индекс также изменился с [идентификационный номер + имя] на [crc32 (идентификационный номер) + имя], и место, занимаемое совместным индексом, стало меньше. Но это преобразование также обходится дорого:
- Каждый дополнительный CRC, приводящий к увеличению ресурсов процессора
- Дополнительные поля хоть и уменьшают место индекса, но тоже занимают место
- Есть вероятность конфликта в crc, что требует от нас фильтровать данные по id_card после запроса данных.Стоимость фильтрации зависит от количества дубликатов данных.Чем больше повторений, тем медленнее фильтрация.
По поводу оптимизации хранения совместного индекса, вот небольшая деталь.Предположим есть два поля А и Б, занимающие 8 байт и 20 байт соответственно.Когда совместный индекс уже [А,В], мы все равно будем поддерживать отдельный запрос Б, поэтому, естественно, мы также создаем индекс для B, тогда пространство, занимаемое двумя индексами, равно8+20+20=48, теперь можно использовать индекс независимо от того, запрашиваем ли мы через A или B. Если мы сможем установить индексы [B, A] и A в условиях, разрешенных бизнесом, это не только удовлетворит потребности запроса данных через A или B В индексе он также может занимать меньше места:20+8+8=36.
«4. Краткий и лаконичный указатель префиксов»
Иногда поле, которое нам нужно проиндексировать, имеет строковый тип, а строка очень длинная. Мы хотим добавить индекс к этому полю, но мы не хотим, чтобы этот индекс занимал слишком много места. В этом случае мы можем рассмотрите возможность создания индекса префикса. Создайте индекс с первой частью символов в этом поле, чтобы вы могли пользоваться индексом и экономить место. Здесь следует отметить, что в случае большого повторения префикса должен быть пробел между скоростью префиксного индекса и обычного индекса.
alter table xx add index(name(7));#name前7个字符建立索引
select xx from xx where name="JamesBond"
«5. Скорость и медлительность уникального индекса»
Прежде чем говорить об уникальном индексе, давайте сначала разберемся с характеристиками обычного индекса.Мы знаем, что для дерева B+ данные конечных узлов упорядочены.
Предположим, теперь мы хотим запросить2Эти данные, затем, когда 2 найдено через дерево индексов, механизм хранения не прекращает поиск, потому что может быть несколько 2, что означает, что механизм хранения затем будет искать в обратном направлении на листовом узле, и после нахождения второго 2, это остановилось? Ответ — нет, потому что подсистема хранения не знает, есть ли еще двойки позади, поэтому она должна искать в обратном направлении, пока не найдет первые данные, которые не равны 2, то есть 3. После обнаружения 3 остановите поиск, это обычный процесс поиска по индексу.
Уникальный индекс отличается.Из-за уникальности невозможно иметь повторяющиеся данные, поэтому после получения наших целевых данных он вернется напрямую, а не будет идти назад, как обычный индекс.найти один раз, С этой точки зрения уникальный индекс быстрее обычного индекса, но когда данные обычного индекса все на одной странице, он ненамного быстрее. С точки зрения вставки данных уникальный индекс может немного уступать, из-за уникальности каждый раз при вставке нужно судить о том, существуют ли уже вставляемые данные, в то время как обычный индекс в этой логике не нуждается, и он очень важно, чтобы для обеспечения уникальности индекса не использовался буфер изменений (см. ниже).
«6. Не добавляйте индексы вслепую»
На работе вы можете столкнуться с такой ситуацией: Нужно ли добавлять индекс к этому полю? . Для этой проблемы наш общий метод оценки: будет ли запрос использовать это поле, если это поле часто встречается в условиях запроса, мы можем рассмотреть возможность добавления индекса. Но если вы судите только по этому условию, вы можете добавить неправильный индекс. Давайте рассмотрим пример: Предположим, что есть пользовательская таблица с данными около 100 Вт. В пользовательской таблице есть поле пола для указания мужчин и женщин, а доля мужчин и женщин составляет почти половину. Теперь мы хотим подсчитать информация обо всех мальчиках, а затем мы добавляем поле пол.index, и мы написали sql следующим образом:
select * from user where sex="男"
Если ничего другого, InnoDB не будет выбирать гендерный индекс. Если используется гендерный индекс, то таблицу необходимо вернуть.В случае большого объема данных, какие будут последствия возврата таблицы? Я разместил картинку, которая такая же, как выше, и все это знают:
Основная причина — много операций ввода-вывода, а часть данных требуется 4 раза, так что насчет данных 50 Вт? Результаты предсказуемы. Следовательно, в ответ на эту ситуацию оптимизатор MySQL с высокой вероятностью выполнит полное сканирование таблицы и непосредственно просканирует индекс первичного ключа, потому что производительность может быть выше.
«7. Эти вещи о сбое индекса»
В некоторых случаях, из-за нашего неправильного использования, mysql не использует индексы, что обычно легко происходит при преобразовании типов.Может быть, вы скажете, разве mysql уже не поддерживает неявное преобразование? Например, теперь есть целочисленное поле индекса user_id.Поскольку мы не обращали внимания при запросе, мы записали его так:
select xx from user where user_id="1234"
Обратите внимание, что это 1234 для символов, и когда это происходит, MySQL действительно достаточно умен, чтобы преобразовать 1234 для символов в 1234 для чисел и с удовольствием использовать индекс user_id. Но если у нас есть символьное поле индекса user_id, или потому что мы не обращали внимания при запросе, мы написали:
select xx from user where user_id=1234
В настоящее время есть проблема, и индекс не будет использоваться.Может быть, вы спросите, почему MySQL не конвертирует его в это время, просто конвертирует 1234 числа в 1234 символьного типа? Здесь необходимо объяснить правила преобразования.При сравнении строк и чисел помните: MySQL преобразует строки в числа. Может быть, вы спросите еще раз: "Почему нет необходимости в индексе для преобразования символьного поля user_id в число? Речь идет о структуре индекса дерева B+. Мы знаем, что индекс дерева B+ разветвляется и сортируется в соответствии с значение индекса. Да, когда мы преобразуем тип поля индекса, значение изменится. Например, исходное значение равно A. Если выполняется целочисленное преобразование, оно может соответствовать значению B (int(A )=B).В настоящее время дерево индексов нельзя использовать, поскольку дерево индексов построено в соответствии с A, а не B, поэтому индекс не будет использоваться.
Оптимизация индекса
"1.изменить буфер"
Мы знаем, что при обновлении фрагмента данных мы должны сначала определить, находится ли страница этих данных в памяти.Если да, то обновить соответствующую страницу памяти напрямую.Если нет, мы можем только перейти на диск, чтобы прочитать соответствующую страницу данных в память.Давай,а потом обнови,в чем проблема?
- Чтение на диск немного медленное
- Если одновременно обновляется много данных, то может быть много дискретных операций ввода-вывода.
Чтобы решить проблему скорости в этом случае,change bufferПоявился, во-первых, не вводите в заблуждение слово буфер.Помимо того, что он находится в публичном буферном пуле, буфер изменений также будет сохраняться на диске. Когда у нас есть буфер изменений, в процессе обновления, если мы обнаружим, что соответствующей страницы данных нет в памяти, мы не будем читать соответствующую страницу данных с диска, а поместим данные для обновления в буфер изменений. Когда данные буфера изменений будут синхронизированы с диском? Что, если в это время произойдет действие чтения? Во-первых, в фоновом режиме находится поток, который будет периодически синхронизировать данные буфера изменений на диск.Если поток не успел синхронизироваться, но операция чтения происходит снова, событие слияния данных буфера изменений изменение буфера на диск также будет активировано.
Следует отметить, что не все индексы могут использовать буфер смены, например индекс первичного ключа и уникальный индекс, из-за уникальности им необходимо судить о том, существуют ли данные или нет при обновлении, если страница данных не находится в памяти, вы должны перейти на диск для чтения соответствующей страницы данных в память, а обычный индекс не имеет значения, нет необходимости проверять уникальность. Чем больше буфер изменений, тем больше теоретическая выгода, потому что, во-первых, меньше операций дискретного чтения, а во-вторых, когда на странице данных происходит несколько изменений, ее нужно только один раз слить на диск. Конечно, не все сценарии подходят для буфера чейнджера.Если ваш бизнес должен быть прочитан сразу после обновления, буфер чейнджера будет контрпродуктивным, потому что действие слияния должно запускаться постоянно, в результате чего количество случайных операций ввода-вывода не уменьшается, но увеличивается.Снижаются накладные расходы на поддержание буфера чейнджера.
"2. Нажатие указателя вниз"
Ранее мы упоминали индекс соединения, индекс соединения должен удовлетворять самому левому принципу, то есть, когда индекс соединения равен [A, B], мы можем использовать индекс через следующий sql:
select * from table where A="xx"
select * from table where A="xx" AND B="xx"
Фактически, совместный индекс также может использовать принцип самого левого префикса, то есть:
select * from table where A like "赵%" AND B="上海市"
Но здесь следует отметить, что, поскольку часть A используется, до MySQL 5.6, после извлечения всех данных, A которых начинается с «Zhao», приведенный выше sql немедленно возвращается к таблице (выберите * used), а затем противопоставление суждение о том, является ли B «Шанхайским», здесь немного сбивает с толку? Почему суждение В не оценивается непосредственно по объединенному индексу, чтобы количество раз возврата к таблице не было меньше? Причина этой проблемы в использовании самого левого префикса.Хотя индекс может использовать часть A, он вообще не использует B. Это кажется немного «глупым», поэтому после MySQL 5.6 есть индекс под Нажимаем эту оптимизацию (Index Condition Pushdown), с помощью этой функции, хоть и используется крайний левый префикс, также возможен поиск A% и фильтрация не-B данных по объединенному индексу, что сильно снижает отдачу к таблице , частота.
«3. Обновить соседние страницы»
Прежде чем говорить об обновлении соседних страниц, давайте сначала поговорим о грязных страницах.Мы знаем, что при обновлении части данных мы должны сначала определить, находится ли страница, на которой находятся данные, в памяти.Если нет, нам нужно прочитать данные страницу в память сначала.Затем обновите данные в памяти, тогда вы обнаружите, что страница в памяти имеет самые последние данные, но страница на диске все еще старые данные, затем страница в памяти, где эти данные в это время находится грязная страница. Для согласованности ее необходимо сбросить на диск. Итак, вопрос в том, когда чистить? Сколько грязных страниц нужно очищать каждый раз? Если он сбрасывается каждый раз, когда вносятся изменения, производительность будет низкой.Если он сбрасывается в течение длительного времени, будет накапливаться много грязных страниц, в результате чего в пуле памяти будет доступно меньше страниц, что, в свою очередь, повлияет на нормальные функции. . Таким образом, скорость очистки не может быть слишком высокой, но она должна быть своевременной.очистить нитьбудет выполняться регулярно, чтобы гарантировать, что это не будет слишком быстро, когдаслишком много грязных страницилижурнал повторов почти заполнен, он немедленно активирует флэш-диск, обеспечивая своевременность.
В процессе сброса грязных страниц InnoDB имеет здесь оптимизацию: если соседние страницы грязных страниц, подлежащих сбросу, также грязные, то они сбрасываются вместе. Преимущество этого заключается в том, что случайный ввод-вывод может быть уменьшен. В случае механических дисков,оптимизация должна быть довольно большая,но тут могут быть ямы.если соседние грязные страницы текущей грязной страницы слить вместе,то соседние страницы сразу опять грязные из-за изменения данных.есть ощущение лишнего в это время? И вместо этого это пустая трата времени и денег. Что еще хуже, если сосед соседней страницы также является грязной страницей... тогда эта цепная реакция может иметь кратковременные проблемы с производительностью.
"4.МРР"
В реальном бизнесе нам могут предложить максимально использовать покрывающий индекс и не возвращать таблицу, потому что возврат к таблице требует больше операций ввода-вывода и занимает больше времени, но иногда нам приходится возвращаться к таблице, а возврат таблицы вызовет не только слишком много операций ввода-вывода, но и, что более серьезно, слишком много дискретных операций ввода-вывода.
select * from user where grade between 60 and 70
Теперь мы хотим запросить информацию о пользователе с оценкой от 60 до 70, поэтому наш sql написан, как указано выше, конечно, нашgradeПоле проиндексировано.Согласно здравому смыслу, сначала будут найдены данные класса=60 по индексу класса, затем индекс первичного ключа будет искаться по идентификатору, соответствующему данным класса=60, и, наконец, он будет вернуться к индексу оценок снова. , продолжайте повторять то же действие...,
Предположим теперь, что id=1 соответствует классу=60, данные находятся вpage_no_1Выше id=10 соответствует классу=61, данные находятся вpage_no_2Выше id=2 соответствует классу=62, данные находятся вpage_no_1, поэтому реальная ситуация заключается в том, чтобы сначала найти данные на page_no_1, затем переключиться на page_no_2 и, наконец, снова переключиться на page_no_1, но на самом делеid=1а такжеid=2Его можно полностью объединить, просто прочитайте page_no_1 один раз, что не только экономит IO, но и позволяет избежать случайного ввода-вывода.Это MRR. После использования MRR вспомогательный индекс не сразу вернется в таблицу, а поместит полученный идентификатор первичного ключа в буфер, затем отсортирует его, а затем последовательно прочитает индекс первичного ключа, что значительно сокращает дискретный ввод-вывод.
Прошлые основные моменты:
кафка! К счастью, я сохранил свою руку
Все начинается с удаления MySQL
Одна статья, чтобы понять откат и сохраняемость MySQL
Наконец, поиск в WeChat [притворитесь, что понимаете программирование], если у вас есть какие-либо вопросы, пожалуйста, свяжитесь со мной, если есть проблема с моей статьей, вы можете исправить меня, если вам нравится учиться, нравится учиться, вы можете следить меня.