Как оптимизированы индексы MySQL? Двадцать айронов для вас!

MySQL

предисловие

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

索引优化几大规则

Правила оптимизации индекса

1. Ведущий нечеткий запрос аналогичного утверждения не может использовать индекс

select * from doc where title like '%XX'；   --不能使用索引
select * from doc where title like 'XX%'；   --非前导模糊查询，可以使用索引
  • Поскольку поиск по страницам строго запрещен, левый нечеткий или полный нечеткий, при необходимости вы можете использовать поисковую систему для решения.

2. Союз, в, или может все попасть в индекс, рекомендуется использовать в

  1. unionИндексы могут быть поражены, а MySQL потребляет меньше всего ресурсов процессора.
select * from doc where status=1
union all
select * from doc where status=2;
  1. inСпособен поразить индекс, соотношение ЦП оптимизации запросовunion allМного, но на это можно не обращать внимания, рекомендуется использовать вообщеin.
select * from doc where status in (1, 2);
  1. orНовая версия MySQL может попасть в индекс, а оптимизация запросов потребляет больше ресурсов ЦП, чемinСлишком много, не рекомендуется для частого использованияor.
select * from doc where status = 1 or status = 2
  1. Пополнить: Некоторые места говорят вwhereиспользуется в условияхor, произойдет сбой индекса, что приведет к полному сканированию таблицы, что является недоразумением:

  • ①ТребованияwhereВсе поля, используемые предложением, должны быть проиндексированы;

  • ②Если объем данных слишком мал, mysql считает, что полное сканирование таблицы выполняется быстрее, чем поиск по индексу при формулировании плана выполнения, поэтому индекс не будет использоваться;

  • ③Убедитесь, что версия mysql5.0Или больше, и оптимизатор запросов включенindex_merge_union=on, то есть переменнаяoptimizer_switchсуществуют вindex_merge_unionи дляon.

3. Отрицательный условный запрос не может использовать индекс

  • Отрицательные условия:!=,<>,not in,not exists,not likeЖдать.

  • Например, следующая инструкция SQL:

select * from doc where status != 1 and status != 2;
  • Может быть оптимизирован для запроса:
select * from doc where status in (0,3,4);

4. Крайний левый префиксный принцип совместного индекса

  • если в(a,b,c)Создайте совместный индекс по трем полям, тогда он автоматически создастa| (a,b) | (a,b,c)индекс группы.

  • Чтобы войти в бизнес-требования, оператор SQL выглядит следующим образом:

select uid, login_time from user where login_name=? andpasswd=?
  • может построить(login_name, passwd)совместный индекс. Потому что бизнеса почти нет.passwdтребования одного условного запроса, в то время как существует множествоlogin_nameТребования к единым условным запросам, чтобы вы могли создавать(login_name, passwd)совместный индекс вместо(passwd, login_name).
  1. При создании сводного индекса поле с наибольшей степенью дискриминации находится в крайнем левом углу.
  1. Когда существует смешанное условие оценки незнака равенства и знака равенства, при создании индекса столбец условия знака равенства добавляется перед. какwhere a>? and b=?, то даже еслиaявляется более дискриминационным и должен такжеbСтавим на передний план индекс.
  1. При запросе крайнего левого префикса это не означает, что порядок оператора SQL должен соответствовать индексу соединения..
  • Следующая инструкция SQL также может попасть(login_name, passwd)Этот совместный индекс:
select uid, login_time from user where passwd=? andlogin_name=?
  • Но все же рекомендуюwhereПоследний порядок согласуется с общим индексом, и это хорошая привычка.
  1. еслиindex(a,b,c), where a=3 and b like 'abc%' and c=4,aможет быть использован,bможет быть использован,c____ не работает.

5. Столбец справа от условия диапазона в индексе использовать нельзя (индекс можно использовать для столбца диапазона), а индекс столбца после столбца диапазона недействителен.

  • Условия полигона таковы:<、<=、>、>=、betweenЖдать.
  • До диапазона столбцов для индекса, если запрос имеет два полных диапазона столбцов, вы не можете использовать индекс.
  • Если есть общий индекс(empno、title、fromdate), то в следующем SQLemp_noможно использовать индекс иtitleиfrom_dateИндексы не используются.
select * from employees.titles where emp_no < 10010' and title='Senior Engineer'and from_date between '1986-01-01' and '1986-12-31'

6. Не делайте никаких операций (расчетов, функций) над колонками индекса, иначе индекс выйдет из строя и перейдет к полному сканированию таблицы

  • Например, следующий оператор SQL, даже еслиdateНа таблице создается индекс, а также выполняется полное сканирование таблицы:
select * from doc where YEAR(create_time) <= '2016';
  • Его можно оптимизировать как вычисление значения следующим образом:
select * from doc where create_time <= '2016-01-01';
  • Например, следующая инструкция SQL:
select * from order where date < = CURDATE()；
  • может быть оптимизирован для:
select * from order where date < = '2018-01-2412:00:00';

7. Принуждение выполнит полное сканирование таблицы

  • Строковый тип без одинарных кавычек приведет к сбою индекса, потому что mysql сам выполнит преобразование типа, что эквивалентно работе со столбцом индекса.
  • еслиphoneполеvarcharтип, следующий SQL не может попасть в index.
select * from user where phone=13800001234
  • может быть оптимизирован для:
select * from user where phone='13800001234';

8. Столбцы с частыми обновлениями и низкой дискриминацией данных не должны индексироваться.

  • Обновления изменят B+-дерево, а индексация часто обновляемых полей сильно снизит производительность базы данных.

  • «Пол» — это атрибут, который мало чем отличается. Строить индекс бессмысленно. Он не может эффективно фильтровать данные, а его производительность аналогична полному сканированию таблицы.

  • Дискриминация, как правило, более 80% времени может быть проиндексирована, может быть использована дискриминацияcount(distinct(列名))/count(*)вычислять.

9. Используйте покрывающий индекс для выполнения операций запроса, избегайте возврата к таблице и сокращайте использование select *

  • Покрывающий индекс: количество запрашиваемых столбцов и установленный индекс одинаковы, а поля одинаковы.
  • Для запрашиваемого столбца данные можно получить из индекса, вместо перехода к строке через row-locator, то есть «опрашиваемый столбец должен быть покрыт построенным индексом», что может ускорить запрос.
  • Например, для регистрации бизнес-требований оператор SQL выглядит следующим образом.
Select uid, login_time from user where login_name=? and passwd=?
  • может построить(login_name, passwd, login_time)совместный индекс , так какlogin_timeбыл установлен в индексе, запрошенныйuidиlogin_timeне уходиrowизвлекает данные по нему, тем самым ускоряя запросы.

10. Индекс не будет содержать столбцов со значениями NULL

  • Пока столбец содержит значения NULL, он не будет включен в индекс, если в составном индексе есть один столбец, содержащийNULLзначение, столбец недопустим для этого составного индекса. Поэтому мы стараемся использовать все возможноеnot nullОграничения и значения по умолчанию.

11, является нулевым, не нулевым, не может использовать индекс

12. Если есть сцены сортировки и группировки, обратите внимание на упорядоченность индекса

  1. order byПоследнее поле является частью составного индекса и размещается в конце составного индекса, чтобы избежать сортировки файлов и повлиять на производительность запроса.
  • Например, для заявленияwhere a=? and b=? order by c, вы можете построить совместный индекс(a,b,c).
  1. Если в индексе есть диапазон поиска, то индекс незамечен, например WHERE a>10 ORDER BY b;,показатель(a,b)Невозможно отсортировать.

13. Используйте короткие индексы (префиксные индексы)

  • Столбцы индексируются, и длина префикса должна быть указана, если это возможно. Например, если естьCHAR(255)столбец, если этот столбец стоит первым10или20个字符内，可以做到既使得前缀索引的区分度接近全列索引，那么就不要对整个列进行索引。因为短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作，减少索引文件的维护开销。 можно использоватьcount(distinct leftIndex(列名, 索引长度))/count(*)для вычисления дискриминативности индекса префикса.

  • Но недостаток в том, что его нельзя использовать дляORDER BYиGROUP BYоперации и не может использоваться для покрытия индексов.

  • Однако во многих случаях нет необходимости индексировать все поля, и длину индекса можно определить в соответствии с фактическим распознаванием текста.

14. Используйте отложенную ассоциацию или подзапросы для оптимизации сценариев многостраничного пейджинга.

  • MySQL не пропускаетoffsetхорошо, но возьмиoffset+Nстроку, затем вернуться, чтобы отбросить предыдущую строку смещения, и вернуть N строк, затем, когда смещение особенно велико, эффективность очень низкая, либо контролировать общее количество возвращаемых страниц, либо выполнять перезапись SQL для количества страниц, превышающих определенный порог.
  • Пример выглядит следующим образом: сначала быстро найдите то, что нужно приобрестиidсегмент, а затем связать:
selecta.* from 表1 a,(select id from 表1 where 条件 limit100000,20 ) b where a.id=b.id；

15. Если ясно, что возвращается только один результат, ограничение 1 может повысить эффективность

  • Например, следующий оператор SQL:
select * from user where login_name=?;
  • может быть оптимизирован для:
select * from user where login_name=? limit 1
  • Я четко знаю, что есть только один результат, но база данных этого не знает, поэтому я говорю ей активно останавливать движение курсора.

16. Лучше не присоединяться более чем к трем столам

  • Типы данных полей, которые необходимо объединить, должны быть одинаковыми.При запросе нескольких таблиц убедитесь, что связанные поля должны иметь индексы.

  • Например:left joinОн определяется левой стороной, а данные в левой части должны его иметь, поэтому правая сторона является нашей ключевой точкой,Чтобы построить индекс, вам нужно правильно построить. Конечно, если индекс слева, вы можете использоватьright join.

17. Рекомендуется контролировать индекс одной таблицы в пределах 5

18. Типа в SQL оптимизация производительности объясните: как минимум уровень диапазона, требование это уровень ref, если он может быть константой, то лучше

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

  • ref: использовать обычный индекс（Normal Index）.

  • range: выполнить поиск диапазона по индексу.

  • когдаtype=indexКогда физический файл индекса полностью просканирован, скорость очень низкая.

19. Поля с уникальными характеристиками в бизнесе, даже если это комбинация нескольких полей, должны создавать уникальный индекс

  • Не думайте, что это влияет на уникальные индексыinsertСкорость, эта потеря скорости незначительна, но прирост скорости поиска очевиден. Кроме того, даже если на прикладном уровне выполняется очень полный контроль проверки, пока нет уникального индекса, согласно закону Мерфи должны генерироваться грязные данные.

20. Избегайте следующих заблуждений при создании индексов

  1. Чем больше индексов, тем лучше. Если вы считаете, что вам нужен запрос, создайте индекс.

  2. NingqueWulan, что индекс потребляет пространство, серьезная замедляющая скорость обновлений и дополнений.

  3. Сопротивляйтесь уникальному индексу и думайте, что уникальность бизнеса нужно решать методом «сначала проверить, а затем вставить» на прикладном уровне.

  4. Оптимизация преждевременно, оптимизация без знания системы.

Избирательность индексов и индексация префиксов

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

  • Первый случай заключается в том, что записи таблицы относительно малы, например, таблица с одной или двумя тысячами или даже всего несколькими сотнями записей, нет необходимости создавать индекс, просто позвольте запросу выполнить полное сканирование таблицы. Насчет того, сколько записей считать лишним, у этого человека личное мнение.Мой личный опыт - использовать в качестве разделительной черты 2000. Если количество записей не превышает 2000, то можно считать, что индекс не строить, а если количество записей превышает 2000, индексация может считаться целесообразной.

  • В другом случае не рекомендуется строить индекс, если индекс селективности низкий. Так называемый индекс селективности (Selectivity), который представляет собой уникальное значение индекса (также называемое базовым, Cardinality) и записывает отношение номера таблицы (#T):

Index Selectivity = Cardinality / #T
  • Очевидно, диапазон избирательности(0, 1]``，选择性越高的索引价值越大，这是由B+Tree的性质决定的。例如，employees.titles表，如果Поле title часто запрашивается отдельно, нужно ли его индексировать или нет, давайте посмотрим на его избирательность:
SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
+-------------+
| Selectivity |
+-------------+
|      0.0000 |
+-------------+

  • titleотсутствие избирательности0.0001(Точное значение — 0,00001579), поэтому нет необходимости индексировать его отдельно.

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

  • Предполагая, что таблица сотрудников имеет только один индекс , тогда, если мы хотим найти человека по имени, мы можем выполнить только полное сканирование таблицы:

EXPLAIN SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido';
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
| id | select_type | table     | type | possible_keys | key  | key_len | ref  | rows   | Extra       |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | employees | ALL  | NULL          | NULL | NULL    | NULL | 300024 | Using where |
+----+-------------+-----------+------+---------------+------+---------+------+--------+-------------+
  • Если вы часто ищете сотрудников по имени, это явно неэффективно, поэтому мы можем рассмотреть возможность создания индекса. Есть два варианта, построить<first_name>или<first_name, last_name>, посмотрите на селективность двух индексов:
SELECT count(DISTINCT(first_name))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.0042 |
+-------------+
SELECT count(DISTINCT(concat(first_name, last_name)))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9313 |
+-------------+
  • <first_name>Очевидно, что избирательность слишком низкая, ``选择性很好，但是first_name和last_name加起来长度为30，有没有兼顾长度和选择性的办法？可以考虑用first_name和last_name的前几个字符建立索引，例如`, чтобы увидеть его избирательность: 
SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.7879 |
+-------------+
  • Селективность неплохая, но до 0,9313 все же далековато, поэтому добавим префикс last_name к 4:
SELECT count(DISTINCT(concat(first_name, left(last_name, 4))))/count(*) AS Selectivity FROM employees.employees;
+-------------+
| Selectivity |
+-------------+
|      0.9007 |
+-------------+
  • В это время селективность уже идеальна, а длина этого индекса всего18,Сравнивать<first_name, last_name>Почти в половине случаев мы строили этот префиксный индекс:
ALTER TABLE employees.employees
ADD INDEX `first_name_last_name4` (first_name, last_name(4));
  • На этом этапе снова выполните запрос по имени и сравните и проанализируйте результаты перед индексированием:
SHOW PROFILES;
+----------+------------+---------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                           |
+----------+------------+---------------------------------------------------------------------------------+
|       87 | 0.11941700 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
|       90 | 0.00092400 | SELECT * FROM employees.employees WHERE first_name='Eric' AND last_name='Anido' |
+----------+------------+---------------------------------------------------------------------------------+

  • Прирост производительности значителен: запросы выполняются более чем в 120 раз быстрее.

  • Префиксный индекс учитывает размер индекса и скорость запроса, но его недостаток в том, что его нельзя использовать дляORDER BYиGROUP BYоперации и не может использоваться дляCovering index(то есть, когда сам индекс содержит все данные, необходимые для запроса, доступ к самому файлу данных больше не осуществляется).

Суммировать

  • В этой статье в основном рассказывается о 20 принципах оптимизации индексов, надеюсь, она понравится читателям.
  • эта статья脑图иPDF文档Он готов, и нуждающиеся партнеры могут подписаться на общедоступную учетную запись WeChat [Code Ape Technology Column], чтобы отвечать на ключевые слова.索引优化Получать.

Категории