Оптимизация производительности MySQL: как добавить индекс к строке?

Управляемое чтение

• Большинство современных систем входа в систему поддерживают методы входа по электронной почте и номеру мобильного телефона, так как же создать индекс для строки электронной почты или номера мобильного телефона, чтобы обеспечить наилучшую производительность?
• Сегодня в этой статье мы обсудим, как добавить индекс к строке в Mysql для достижения наилучшей производительности.

• Эта статья была впервые опубликована в публичном аккаунте автора WeChat [Колонка технологий Code Ape], Оригинал не так просто, как читатели обращают внимание, спасибо! ! !

• Чен расскажет о **Что такое префиксный индекс**, **Сравнение префиксного индекса и обычного индекса**, **Как построить префиксный индекс с наилучшей производительностью**, **Влияние префиксного индекса по индексу охвата* *В этих нескольких абзацах.

индекс префикса

• Как следует из названия, для столбцов с длинными значениями, таких как `BLOB`, `TEXT`, `VARCHAR`, вы "должны" использовать **индекс префикса**, то есть первую часть значения в качестве индекса. . Поскольку для хранения индекса также требуется место, также трудно поддерживать индекс, если он слишком длинный.
• Например, мы добавляем индекс префикса к почтовому ящику в таблице «Пользователь» следующим образом:

  alter table user add index index1(email(7));

• Приведенный выше оператор использует первые 7 символов электронной почты в качестве индекса.

Сравнение индекса префикса и нормального индекса

• Давайте используем весь адрес электронной почты в качестве индекса и первые 7 символов в качестве индекса, чтобы увидеть разницу в производительности. Оператор для создания индекса выглядит следующим образом:

  alter table user add index index1(email);

  alter table user add index index2(email(7));

• Предположим, что в таблице `user` есть несколько фрагментов данных (id, name, email): `(1,"Chen","chenmou1993@xxx")`,`(2,"Zhang","chenmou1994@xxx" )`, `(3,"Ли","chenmou1995@xxx")`, `(4,"Ван","chenmou1996@xxx")`.
• Деревья индексов, соответствующие index1 и index2, следующие:

• Если выполняется следующий оператор запроса, как Mysql использует индекс для запроса?

  select * from user where email="chenmou1995@xxx";

[1] Процесс выполнения обычного индекса

1. Найдите запись со значением индекса chenmou1995@xxx из дерева индексов index1 и получите значение id=2;
2. Перейдите к первичному ключу, чтобы найти строку, значение первичного ключа которой равно `id=2`, оцените правильность значения электронной почты и добавьте эту строку в набор результатов;
3. Возьмите следующую запись в позицию, только что найденную в дереве индексов index1, и обнаружите, что условие `email=chenmou1995@xxx` больше не выполняется, и цикл завершается.

В этом процессе вам нужно только один раз вернуться к индексу первичного ключа, чтобы получить данные, поэтому система считает, чтоотсканирована только одна строка.

[2] Процесс выполнения индекса префикса

1. Найдите запись, значение индекса которой равно chenmou из дерева индексов index2, и первая найденная запись имеет id=1;
2. Перейти к первичному ключу, чтобы найти строку, значение первичного ключа которой равно id=1, и решить, что значение электронной почты не равно `chenmou1995@xxx`, эта строка записей отбрасывается;
3. Возьмите следующую запись в позиции, только что найденной в index2, и обнаружите, что это все еще `chenmou`, уберите id=2, а затем перейдите к индексу ID, чтобы взять всю строку и решить, что на этот раз значение правильное, добавить эту строку записей в набор результатов;
4. Повторяйте предыдущий шаг до тех пор, пока значение, полученное на idxe2, не будет «chenmou», и цикл завершится.

В этом процессе индекс первичного ключа необходимо получить 4 раза.То есть сканируется 4 строки.

• Сравнивая приведенные выше запросы, легко обнаружить, что после использования префиксного индекса число считываний данных оператором запроса может увеличиться. **
• Но что, если для этого запроса длина установленного индекса префикса равна 13? Тогда есть только одна запись, которая удовлетворяет chenmou1995, поэтому вы можете напрямую найти id = 2. В это время не только уменьшается пространство, но и количество сканируемых строк.
• Итак, напрашивается вывод: ** Использование префиксного индекса, если длина определена, может сэкономить место без слишком больших дополнительных затрат на запросы. **
• Так как же установить правильный индекс префикса для достижения наилучшей производительности? Тогда смотри вниз......

Как построить индекс префикса для оптимальной производительности

• Путем приведенного выше сравнения можно сделать вывод, что чем выше степень дискриминации индекса префикса, тем лучше, а значит, меньше повторяющихся значений ключа.
• Итак, как посчитать степень дискриминации на самом деле очень просто, нужно просто судить о количестве повторений в базе данных. sql выглядит следующим образом:

  Выбрать count(отличное левое(email,4)) как L4, count(отличное левое(email,5)) как L5, count(отличное левое(email,6)) как L6, count(отличное левое(email,7)) как L7, от пользователя;

• Но если использование префиксов не очень хорошо, например, идентификационный номер нашей страны имеет всего 18 цифр, из которых первые 6 цифр являются кодом адреса, поэтому первые 6 цифр идентификационного номера людей в том же графство, как правило, будет идентичным. В это время, если для идентификационного номера используется индекс префикса длиной 6, различение индекса очень низкое.
• В соответствии с методом, который мы упоминали ранее, вам может потребоваться создать префиксный индекс длиной 12 или более, чтобы удовлетворить требование дискриминации.
• Однако чем длиннее выбран индекс, тем больше место на диске, тем меньше значений индекса можно разместить на одной странице данных и тем ниже будет эффективность поиска.
• Тогда, если мы сможем определить, что в бизнес-требованиях нужен только эквивалентный запрос по удостоверению личности, есть ли другой способ справиться с этим? Этот метод может не только занимать меньше места, но и обеспечивать такую ​​же эффективность запросов. Теперь кратко представим способ решения этой проблемы, конечно, должно быть больше одного способа, а именно:

[1] Сохранение в обратном порядке

Если вы храните идентификационный номер в перевернутом виде, каждый раз, когда вы запрашиваете, вы можете написать:

 select field_list from t where id_card = reverse('输入的身份证号');

Из-за идентификационного номерапоследние 6 цифрНет повторяющейся логики, такой как адресные коды, поэтому последние 6 бит, вероятно, обеспечат достаточную дискриминацию. Конечно, на практике вы не забываете использоватьcount(distinct)способ проверить.

Влияние индекса префикса на индекс покрытия

• Индекс префикса приведет к сбою покрывающего индекса Оператор запроса выглядит следующим образом:

  select id,name from user where email="chenmou1995@xxx";

• Поскольку используется префиксный индекс, его необходимо **вернуть**, чтобы убедиться, что запрос корректен, и использование покрывающего индекса здесь также недопустимо.
• Другими словами, использование префиксного индекса не будет использовать покрывающий индекс для оптимизации производительности запросов, что также является фактором, который необходимо учитывать при выборе использования префиксного индекса.

Суммировать

• Как добавить индекс к строке - это проблема, которую необходимо рассмотреть. Здесь Чен дает следующие предложения:

1. Если длина строки очень короткая, рекомендуется использовать all в качестве индекса напрямую.
2. Используйте индекс префикса для анализа степени дискриминации, и чем выше степень дискриминации, тем лучше.
3. При использовании префиксных индексов необходимо учитывать проблему покрытия аннулирования индекса.