Стратегия оптимизации высокопроизводительного индекса MySQL

MySQL
Стратегия оптимизации высокопроизводительного индекса MySQL

Диаграмма картирования знаний MySQL, смотрите всю статью на одной картинке:

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

1. Объяснение инструмента

Прежде чем подробно обобщить стратегию оптимизации индекса MySQL, позвольте мне представить вам инструмент, который поможет вам устранить большинство проблем во время медленного процесса обработки запросов: Объяснение. Для подробного ознакомления с объяснением вы можете проверить адрес официального веб-сайта:Dev.MySQL.com/doc/Furious/…. Вот еще один метод обучения для всех, то есть вы должны перейти на официальный сайт, чтобы узнать информацию из первых рук.Если вы чувствуете, что чтение на английском языке вызывает затруднения, рекомендуется накапливать и читать статьи на английском языке.Английский язык очень важен для программистов. , Advanced Многие технические и теоретические материалы на английском языке, и официальный сайт также очень всеобъемлющий.Если вы хотите стать техническим мастером, вы должны практиковаться. Бред здесь, позвольте мне кратко описать, как использовать Объяснение. Пример:

  mysql> explain select * from user where name="xiao" and age=9099 and birthday="1980-08-02";
     +----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+
     | id | select_type | table | partitions | type | possible_keys | key        | key_len | ref               | rows | filtered | Extra |
     +----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+
     |  1 | SIMPLE      | user  | NULL       | ref  | unique_key    | unique_key | 249     | const,const,const |    1 |   100.00 | NULL  |
     +----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+

В результате объяснения есть несколько столбцов, кратко расскажем о часто используемых столбцах: select_type, type, key, key_len, ref, rows. По остальным столбцам вы можете обратиться к введению на официальном сайте.

  • select_type означает тип запроса, будь то простой запрос или сложный запрос.Если он не включает подзапросы и UNION, select_type имеет значение SIMPLE. Другие сложные запросы включают SUBQUERY и UNION.
  • тип, очень важный, часто используется при анализе запросов, тип имеет несколько значений ALL, index, range, ref, const(system), NULL. ALL означает полное сканирование таблицы, от начала до конца; индексирование аналогично полному сканированию таблицы, за исключением того, что MySQL сканирует таблицу в соответствии с порядком индекса, а не строк; диапазон, сканирование диапазона, то есть ограниченное сканирование индекса, начинается с индекса. В определенный момент возвращаются строки, соответствующие этому диапазону. ref, индексированный доступ, возвращает все строки, соответствующие одному значению. const, константа, некоторая часть запроса оптимизируется в константу. NULL обычно означает, что нет необходимости обращаться к таблице или индексу при выполнении. Сортировка типа скорости запроса: const > ref > range > index=ALL.
  • ключ, это легко понять, какой индекс используется.
  • key_len. Количество байтов, используемых в индексе.
  • ref, который указывает столбец или константу, используемую для поиска в индексе записи ключевого столбца.

2. Подготовьте стол

CREATE TABLE `user` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `gender` varchar(16) DEFAULT NULL,
 `name` varchar(64) DEFAULT NULL,
 `birthday` varchar(16) NOT NULL,
 `age` int(11) unsigned NOT NULL,
 PRIMARY KEY (`id`),
 KEY `unique_key` (`name`,`age`,`birthday`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

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

3. Сценарии индекса B-Tree и связанные с ними ограничения:

Индекс B-Tree, в соответствии с принципом анализа предыдущей статьи, знает, что данные хранятся в порядке, поэтому он не работает, пока индекс используется в операторе запроса.Давайте рассмотрим конкретные сценарии и ограничения.

  1. полное соответствие стоимости. Сопоставление всех значений относится к сопоставлению со всеми столбцами в индексе, например:
mysql> explain select * from user where name="xiao" and age=9099 and birthday="1980-08-02";
+----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | unique_key    | unique_key | 249     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------------+---------+-------------------+------+----------+-------+

Полное соответствие значений, то есть все столбцы по индексу совпадают точно.Из ref и key_len видно, что в операторе используются три индекса. Теоретически индекс более чувствителен к порядку, но на самом деле вы можете увидеть результат, выполнив следующий оператор:

explain select * from user where age=9099 and birthday="1980-08-02" and name="xiao";

Оказывается, ответ тот же, потому что оптимизатор запросов MySQL автоматически настраивает условный порядок предложения where, чтобы он соответствовал наиболее подходящему индексу.

  1. соответствует крайнему левому префиксу. Если вы хотите найти всех с именем=сяо, просто используйте первый столбец указателя.
mysql> explain select * from user where name="xiao";
+----+-------------+-------+------------+------+---------------+------------+---------+-------+-------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows  | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | user  | NULL       | ref  | unique_key    | unique_key | 195     | const | 15170 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+-------+----------+-------+

Вы можете видеть, что используется индекс имени. Что делать, если крайний левый префикс не совпадает:

mysql> explain select * from user where birthday="1980-08-02";
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 30340 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+-------+----------+-------------+

Видно, что если индекс не запрашивается по имени, он становится полным запросом таблицы.

  1. соответствие префиксу столбца. То есть вы можете сопоставить только начало значения определенного столбца, например, вы хотите сопоставить данные, начинающиеся с name=xiao-1.
   mysql> explain select * from user where name like "xiao-1%";
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | unique_key    | unique_key | 195     | NULL | 1111 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+

Вы можете видеть, что тип — диапазон, и используется объединенный индекс с ключом = уникальным_ключом. Если имя похоже на "%xiao-1%", то индекс использовать нельзя.Причину можно предположить по характеристикам B-Tree.

  1. значение диапазона соответствия. Например, вы хотите найти данные, имя которых находится между [xiao-1, xiao-200].
mysql> explain select * from user where name >  "xiao-1" and name <= "xiao-200";
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | unique_key    | unique_key | 195     | NULL | 1113 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+

Видно, что type=range, используется индекс unique_key.

  1. Совпадение ровно с одним столбцом, а диапазон совпадет с другим столбцом. Например, если вы хотите проверить данные с name="xiao" и возрастом между [1,100].
mysql> explain select * from user where name="xiao" and age > 1 and age < 100;
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | user  | NULL       | range | unique_key    | unique_key | 199     | NULL |   98 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------------+---------+------+------+----------+-----------------------+

На предыдущее совпадение диапазона можно ссылаться по длине key_len.Обнаружено, что длина key_len стала больше.На самом деле используются два индекса имени и возраста.Имя-точное совпадение, а возраст-совпадение диапазона . Подумайте: если оператор sql станет:

select * from user where name="xiao" and age > 1 and age < 100 and birthday="2000-08-02";

Будет ли использоваться индекс дня рождения?

Выше также упоминалось, что индексы B-Tree имеют некоторые ограничения.А теперь подведем итоги:

  • Принцип самого левого префикса, если поиск не начат по крайнему левому столбцу индекса, то индекс использовать нельзя.
  • Столбцы в индексе нельзя пропускать.Например, если индекс (имя, возраст, день рождения), то если указаны только два столбца имя и день рождения, индекс дня рождения нельзя использовать.
  • Если в запросе указан диапазон столбцов, все столбцы справа от него не могут использовать индекс. Последняя мысль из пункта 5 как раз об этом ограничении.
  • Если оператор запроса содержит функции или выражения, нельзя использовать индексы, например, age-1=18 или left(name, 3) = xia.
  • Соответствие префиксу столбца, который является третьим пунктом, упомянутым выше, если подобное выражение равно «%xiao-1%», индекс не может использоваться.

4. Стратегия индексации

Давайте сначала суммируем преимущества индексов:

  • Индексы значительно сокращают объем данных, которые сервер должен сканировать.
  • Индексы помогают серверу избежать сортировки и временных таблиц.
  • Индексы могут превратить случайный ввод-вывод в последовательный ввод-вывод.

После разговора о трех основных преимуществах вы думаете, что если это таблица и столбец, то достаточно добавить индексы ко всем столбцам?

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

Так есть ли способ узнать, когда использовать индекс, а когда нет?

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

  • Селективность индекса. Высокопроизводительная MySQL (третья редакция) определяет избирательность индекса как отношение уникальных значений индекса (также известного как количество элементов) к общему количеству записей (#T) в таблице данных в диапазоне от 1/#T до 1 между . Чем выше избирательность индекса, тем выше эффективность запроса, поскольку высокоселективный индекс позволяет MySQL отфильтровывать больше строк при поиске. Селективность уникального индекса равна 1. В следующем примере показано, как вычислить селективность:

    mysql> select count( distinct name) / count(1) from user;
+----------------------------------+
| count( distinct name) / count(1) |
+----------------------------------+
|                           0.6632 |
+----------------------------------+

mysql> select count( distinct birthday) / count(1) from user;
+--------------------------------------+
| count( distinct birthday) / count(1) |
+--------------------------------------+
|                               0.0002 |
+--------------------------------------+

mysql> select count( distinct id) / count(1) from user;
+--------------------------------+
| count( distinct id) / count(1) |
+--------------------------------+
|                         1.0000 |
+--------------------------------+

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

    Далее подведем итоги общих стратегий индексации:

    • независимый столбец: Независимый столбец означает, что индексированный столбец не может быть частью выражения или параметром функции. Об этом тоже было сказано выше и повторяться не буду.
    • индекс префикса: иногда необходимо проиндексировать столбец с длинными символами, например, имя, которое обычно является длинным символом.Если он используется в качестве индекса, весь файл индекса станет очень большим, а скорость запроса также будет замедлена. Один из способов — индексировать только некоторые символы в начале, что может значительно сэкономить место в индексе и повысить эффективность индексации. Конечно, эта оптимизация также снизит избирательность индекса, например:
    mysql> select count( distinct left(name, 8)) / count(1) from user;
+-------------------------------------------+
| count( distinct left(name, 8)) / count(1) |
+-------------------------------------------+
|                                    0.3648 |
+-------------------------------------------+

mysql> select count( distinct left(name, 9)) / count(1) from user;
+-------------------------------------------+
| count( distinct left(name, 9)) / count(1) |
+-------------------------------------------+
|                                    0.6630 |
+-------------------------------------------+

    Видно, что когда используется имя и префикс 8 символов, селективность все еще относительно низкая. Когда он становится 9 символов, селективность намного выше. Измените индекс на левый (имя, 9) и см. что длина индекса уменьшается. Измените индекс на (имя (9), возраст, день рождения)

    mysql> explain select * from user where name="xiao" and age=9099 and birthday="1980-08-02";
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key         | key_len | ref               | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------------+
|  1 | SIMPLE      | user  | NULL       | ref  | unique_key2   | unique_key2 | 84      | const,const,const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+---------------+-------------+---------+-------------------+------+----------+-------------+

    Сравнивая оператор объяснения при полном сопоставлении, key_len уменьшен с 249 до 84, что в три раза меньше, что значительно уменьшает размер индексного файла и повышает эффективность. Но есть и недостаток: индекс префикса недействителен для операций ORDER BY или GROUP BY.

  • Выберите подходящий порядок столбцов индекса, Из анализа сцены видно, что индекс B-Tree хранит данные по порядку, поэтому выбор наиболее подходящего последовательного столбца индекса очень полезен для запросов, но это не относительно интуитивно понятный метод, обычно учитывающий селективность и бизнес-требования. Например, в приведенном выше примере селективность имя>возраст>день рождения, и обычно есть много сценариев, в которых компания опрашивается по имени пользователя, поэтому порядок индекса (имя, возраст, день рождения). Грубо говоря, индексы создаются только для полей, которые часто используются в качестве условий запроса.

  • Особенности кластерных индексов, Исходя из анализа принципа индекса в предыдущей статье, индекс B-Tree, используемый механизмом InnoDB, является кластеризованным индексом. Каковы характеристики этого типа индекса? Как упоминалось в предыдущей статье, данные InnoDB кластеризуется по первичному ключу. Если таблица не отображается Если первичный ключ определен, InnoDB вместо этого предпочтет уникальный ненулевой индекс. Если такой индекс не найден, первичный ключ будет неявно определен для кластеризации индекса. Поэтому при выборе первичного ключа рекомендуется ориентироваться на следующее:

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

    Некоторые считают, что достаточно использовать уникальное поле в бизнесе в качестве первичного ключа, нет необходимости выбирать в качестве первичного ключа автоинкрементный id, который не имеет никакого отношения к бизнесу. Причины следующие:

    • Данные InnoDB агрегируются и хранятся в порядке первичного ключа, а сами записи данных хранятся на листовых узлах первичного индекса. Это требует, чтобы каждая запись данных в одном и том же листовом узле (размером является одна страница памяти или страница диска) хранилась в порядке первичного ключа, поэтому всякий раз, когда вставляется новая запись, MySQL будет вставлять ее в соответствующий узел в соответствии с его первичный ключ и положение, если страница достигает коэффициента загрузки (InnoDB по умолчанию 15/16), открывается новая страница (узел). Если в таблице используется автоинкрементный первичный ключ, каждый раз, когда вставляется новая запись, запись будет последовательно добавляться на следующую позицию текущего узла индекса.Когда страница заполнена, новая страница будет открываться автоматически. Если используется уникальный первичный ключ предприятия, он может не быть автоматически увеличивающимся первичным ключом (например, номером удостоверения личности или номером студента и т. д.) Поскольку значение первичного ключа, вставляемого каждый раз, приблизительно случайно, каждый новый запись должна быть вставлена ​​в середину существующей страницы индекса.В какой-то момент MySQL должен переместить данные, чтобы вставить новую запись в нужное место, и даже целевая страница может быть записана обратно на диск и очищена от кэш, а затем считывание с диска в это время. Добавляется много накладных расходов, и в то же время частые операции перемещения и подкачки вызывают сильную фрагментацию, что приводит к недостаточно компактной структуре индекса. OPTIMIZE TABLE необходимо использовать для перестроения таблицы и оптимизации заполнения страниц. Видно, что стоимость вставки огромна.

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

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

  • Избыточные и повторяющиеся индексыMySQL позволяет создавать несколько индексов для одного и того же столбца.Иногда я вижу, что строится УНИКАЛЬНЫЙ КЛЮЧ (имя, возраст), а затем строится КЛЮЧ (имя), так что индекс имени дублируется, а отдельный индекс необходимо удалить, если он найден, что может значительно сократить накладные расходы. Чем больше индексов, тем медленнее вставка данных.

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

5. Как удалить ненужные данные из больших таблиц:

Если таблица содержит более миллиона уровней, индекс требует дополнительных затрат на обслуживание, поскольку индексный файл — это отдельный файл, поэтому при добавлении, удалении и изменении данных будут выполняться дополнительные операции над индексным файлом, и эти операции необходимо потребляют дополнительные операции ввода-вывода, снижают эффективность выполнения добавлений, удалений и модификаций. А скорость удаления данных пропорциональна количеству созданных индексов. Есть небольшая хитрость, вы можете обратиться к:

  • Сначала удалите индекс.Если вы удалите данные напрямую, данные дерева индексов будут скорректированы в большом масштабе, и потребление не может быть оценено.
  • Затем удалите бесполезные данные.В это время индекса нет, и скорость удаления бесполезных данных будет намного выше.
  • После удаления данных и последующего перестроения индекса данных в это время будет меньше, а скорость будет относительно выше.

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

Это все, что касается стратегии индексирования.В следующей статье кратко излагается MySQL CRUD и оптимизация запросов к нескольким таблицам.

Для получения более интересных статей, пожалуйста, обратите внимание на публичный аккаунт: «Tiancheng Technology Talk».

Категории