[TOC]

Индексы MySQL и настройка SQL

В этой статье есть ссылки на другие связанные статьи в Интернете, и в конце этой статьи есть ссылки.

индексы MySQL

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

Официальное определение индекса в MySQL: Индекс — это структура данных, которая помогает MySQL эффективно получать данные. Извлекая основу предложения, можно понять суть индекса: индекс — это структура данных.

Принцип индекса MySQL

цель индекса

Цель индекса - повысить эффективность запроса. Его можно сравнить со словарем. Если мы хотим найти слово «mysql», мы должны найти букву m, затем найти букву y снизу вниз и затем найдите оставшийся sql. Если индекса нет, то вам может понадобиться просмотреть все слова, чтобы найти то, что вы хотите, что, если я хочу найти слова, начинающиеся с m? Или как насчет слов, начинающихся с ze? Вы чувствуете, что без индекса это вообще невозможно?

То же самое верно и для взятия книг в библиотеке.Если вы хотите взять книгу, вы должны сначала найти соответствующую классифицированную тему, а затем найти соответствующий номер.Это живой пример из жизни.Общий указатель может ускорить скорость запроса позиция.

Принцип индексации

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

То же самое верно и для базы данных, но она, очевидно, намного сложнее, потому что она сталкивается не только с эквивалентными запросами, но и с диапазонными запросами (>,

структура индекса

Никакая структура данных не создается из воздуха, у нее должна быть своя предыстория и сценарии использования. Давайте теперь суммируем, что нам нужно, чтобы эта структура данных могла делать. На самом деле это очень просто, то есть: каждый раз, когда мы ищем данные. количество дисковых операций ввода-вывода контролируется в малом порядке, предпочтительно в постоянном порядке. Затем мы думаем, сможет ли хорошо управляемое дерево многоканального поиска удовлетворить потребности? Так появилось дерево b+.

Объяснение структуры индекса дерева b+

Светло-синий блок мы называем дисковым блоком, вы можете видеть, что каждый дисковый блок содержит несколько элементов данных (показаны темно-синим) и указатели (показаны желтым), например, дисковый блок 1 содержит элементы данных 17 и 35, содержит указатели P1 , P2, P3, P1 представляет дисковые блоки меньше 17, P2 представляет дисковые блоки от 17 до 35, а P3 представляет дисковые блоки больше 35. Реальные данные существуют в листовых узлах, а именно 3, 5, 9, 10, 13, 15, 28, 29, 36, 60, 75, 79, 90, 99. Неконечные узлы не хранят реальные данные, а хранят только элементы данных, определяющие направление поиска.Например, 17 и 35 фактически не существуют в таблице данных.

b+ процесс поиска по дереву

Как показано на рисунке, если вы хотите найти элемент данных 29, то сначала с диска в память будет загружен дисковый блок 1. В это время происходит IO, и бинарный поиск используется для определения того, что 29 находится в памяти между 17 и 35, и заблокирован блок диска 1. Указатель P2, время памяти незначительно, потому что оно очень короткое (по сравнению с вводом-выводом диска), а блок диска 3 загружается из диска в память через дисковый адрес указателя P2 дискового блока 1, и происходит второй IO, 29 в 26 и между 30, указатель P2 дискового блока 3 блокируется, и дисковый блок 8 загружается в память через указатель, и происходит третий ввод-вывод. Реальная ситуация такова, что трехслойное дерево b+ может представлять миллионы данных. Если для поиска миллионов данных требуется только три операции ввода-вывода, повышение производительности будет огромным. Если нет индекса, каждый элемент данных будет иметь операцию ввода-вывода. , тогда в общей сложности требуются миллионы операций ввода-вывода, очевидно, что стоимость очень и очень высока.

b+ свойства дерева

1. Благодаря приведенному выше анализу мы знаем, что чем меньше интервал, тем больше количество элементов данных и тем ниже высота дерева. Поэтому каждый элемент данных, то есть поле индекса, должен быть как можно меньше, например, int занимает 4 байта, что вдвое меньше, чем bigint 8 байт. Вот почему дерево b+ требует, чтобы реальные данные размещались в листовых узлах, а не во внутренних узлах.После размещения во внутренних узлах элементы данных дисковых блоков значительно уменьшатся, в результате чего дерево станет выше. Когда элемент данных равен 1, он вырождается в линейную таблицу.

2. Когда элемент данных дерева b+ представляет собой составную структуру данных, например (имя, возраст, пол), число b+ строит дерево поиска в порядке слева направо, например, когда (Чжан Сан, 20 лет, Ж) Когда данные получены, дерево b+ сначала сравнивает имя, чтобы определить следующее направление поиска.Если имя совпадает, то по очереди сравнивает возраст и пол и, наконец, получает извлеченные данные; но когда нет имени такие как (20,F) Когда приходят данные, дерево b+ не знает, какой узел проверять следующим, потому что имя является первым фактором сравнения при построении дерева поиска, и его нужно искать по имени, чтобы узнать куда запросить дальше. Например, когда извлекаются такие данные, как (Чжан Сан, F), дерево b+ может использовать имя для указания направления поиска, но следующее поле age отсутствует, поэтому только данные, имя которых равно Zhang San, могут быть только найдено, а затем сопоставляется пол — это данные F. Это очень важное свойство, то есть крайний левый совпадающий признак индекса.

Реализация индекса MySQL

В MySQL индексы относятся к понятию уровня механизма хранения.Разные механизмы хранения реализуют индексы по-разному.В этой статье в основном обсуждаются методы реализации индексов механизмов хранения MyISAM и InnoDB.

Реализация индекса MyISAM

Механизм MyISAM использует B+Tree в качестве структуры индекса, а поле данных конечного узла хранит адрес записи данных.На следующем рисунке представлена ​​схема индекса MyISAM:

Здесь в таблице три столбца.Предполагая, что мы используем Col1 в качестве первичного ключа, приведенный выше рисунок представляет собой схематическую диаграмму первичного ключа таблицы MyISAM. Видно, что индексный файл MyISAM сохраняет только адрес записи данных. В MyISAM нет разницы в структуре между первичным индексом и вторичным ключом (Secondary key), но первичный индекс требует, чтобы ключ был уникальным, в то время как ключ вторичного индекса может повторяться. Если мы построим вторичный индекс на Col2, структура этого индекса показана на следующем рисунке:

Это также B+Tree, и поле данных сохраняет адрес записи данных. Таким образом, алгоритм поиска индекса в MyISAM заключается в том, чтобы сначала искать индекс в соответствии с алгоритмом поиска B + Tree.Если указанный ключ существует, значение поля данных извлекается, а затем считывается соответствующая запись данных со значением поля данных в качестве адреса.

Метод индекса MyISAM также называется «некластеризованным», что призвано отличать его от кластеризованного индекса InnoDB.

Реализация индекса InnoDB

Хотя InnoDB также использует B+Tree в качестве структуры индекса, конкретная реализация полностью отличается от MyISAM.

Первое существенное отличие заключается в том, что файлы данных InnoDB сами по себе являются индексными файлами. Из вышеизложенного известно, что индексный файл MyISAM и файл данных разделены, а индексный файл сохраняет только адрес записи данных. В InnoDB сам файл данных таблицы представляет собой индексную структуру, организованную B+Tree, а поле данных конечного узла этого дерева сохраняет полные записи данных. Ключ этого индекса является первичным ключом таблицы данных, поэтому сам файл данных таблицы InnoDB является первичным индексом.

Вышеприведенный рисунок представляет собой схематическую диаграмму основного индекса InnoDB (который также является файлом данных).Вы можете видеть, что конечные узлы содержат полные записи данных. Такой индекс называется кластерным индексом. Поскольку файлы данных InnoDB агрегируются по первичному ключу, InnoDB требует, чтобы таблица имела первичный ключ (MyISAM может не иметь его).Если он не указан явно, система MySQL автоматически выберет столбец, который может однозначно идентифицировать запись данных в качестве первичного ключа.Если он не существуетДля этого типа столбца MySQL автоматически генерирует неявное поле в качестве первичного ключа для таблицы InnoDB.Длина этого поля составляет 6 байтов, а тип - длинный.

Второе отличие от индексов MyISAM заключается в том, что поле данных вторичного индекса InnoDB хранит значение первичного ключа соответствующей записи вместо ее адреса. Другими словами, все вторичные индексы в InnoDB относятся к первичному ключу как к полю данных. Например, на следующем рисунке показан вспомогательный индекс, определенный для Col3:

Здесь в качестве критерия сравнения используется код ASCII английских символов. Реализация кластеризованного индекса делает поиск по первичному ключу очень эффективным, но при поиске по вторичному индексу необходимо получить индекс дважды: сначала извлекается вторичный индекс для получения первичного ключа, а затем первичный ключ используется для извлечения. записи в первичном индексе.

Понимание методов реализации индексов различных механизмов хранения очень полезно для правильного использования и оптимизации индексов.Например, зная реализацию индекса InnoDB, легко понять, почему не рекомендуется использовать слишком длинные поля в качестве первичных ключей, поскольку все вторичные индексы ссылаются на первичный ключ Index, длинный первичный индекс сделает вторичный индекс слишком большим. В другом примере не рекомендуется использовать немонотонное поле в качестве первичного ключа в InnoDB, потому что файл данных InnoDB сам по себе является деревом B+, а немонотонный первичный ключ заставит файл данных поддерживать характеристики B+Tree при вставке новых записей Частая корректировка разделения очень неэффективна, и использование поля автоинкремента в качестве первичного ключа является хорошим выбором.

Как построить правильный индекс

Принцип индексации

Одним из наиболее важных принципов является принцип крайнего левого префикса. Прежде чем упомянуть об этом, мы должны поговорить о совместном индексе. Индекс в MySQL может ссылаться на несколько столбцов в определенном порядке. Такой тип индекса называется объединенным индексом. объединенный индекс представляет собой упорядоченный набор , где каждый элемент является столбцом таблицы данных. Кроме того, одностолбцовый индекс можно рассматривать как частный случай, когда количество элементов в объединенном индексе равно 1.

Крайний левый принцип сопоставления индексов конкретно означает, что если столбцы индекса — A, B, C, а порядок — также A, B, C:

- 那么查询的时候，如果查询【A】【A，B】 【A，B，C】，那么可以通过索引查询
- 如果查询的时候，采用【A，C】，那么C这个虽然是索引，但是由于中间缺失了B，因此C这个索引是用不到的，只能用到A索引
- 如果查询的时候，采用【B】 【B，C】 【C】，由于没有用到第一列索引，不是最左前缀，那么后面的索引也是用不到了
- 如果查询的时候，采用范围查询，并且是最左前缀，也就是第一列索引，那么可以用到索引，但是范围后面的列无法用到索引

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

При использовании механизма хранения InnoDB, если нет особой необходимости, всегда используйте поле автоинкремента, не связанное с бизнесом, в качестве первичного ключа. С точки зрения оптимизации индекса базы данных использование механизма InnoDB без автоматического увеличения первичных ключей — определенно плохая идея.

InnoDB использует кластеризованный индекс, а сами записи данных хранятся на листовых узлах основного индекса (B+Tree). Это требует, чтобы каждая запись данных в одном и том же листовом узле (размером является одна страница памяти или страница диска) хранилась в порядке первичного ключа, поэтому всякий раз, когда вставляется новая запись, MySQL будет вставлять ее в соответствующий узел в соответствии с его первичный ключ и положение, если страница достигает коэффициента загрузки (InnoDB по умолчанию 15/16), открывается новая страница (узел). Если в таблице используется автоинкрементный первичный ключ, каждый раз, когда вставляется новая запись, запись будет последовательно добавляться на следующую позицию текущего узла индекса.Когда страница заполнена, новая страница будет открываться автоматически. следующее:

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

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

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

Поэтому по возможности старайтесь использовать автоинкрементные поля в качестве первичных ключей в InnoDB.

Общие советы по индексированию

1. Крайний левый принцип сопоставления префиксов, очень важный принцип, mysql всегда будет сопоставляться справа, пока не встретит запрос диапазона (>, 3 и d = 4 Если установлен индекс порядка (a,b,c,d), d не будет использовать индекс, если установлен индекс (a,b,d,c), его можно использовать, порядок a, b, d Может быть скорректирован произвольно.

2. = и in могут быть не по порядку, например, a = 1 и b = 2 и c = 3, а индекс (a,b,c) может быть построен в любом порядке, и оптимизатор запросов mysql поможет вам его оптимизировать в форму, которую индекс может распознать

3. Постарайтесь выбрать в качестве индекса столбец с высокой степенью дискриминации. Формула для степени дискриминации: количество (уникальный столбец) / количество (*), что указывает на долю неповторяющихся полей. Чем больше доля, чем меньше записей мы сканируем. 1, а некоторые поля статуса и пола могут иметь степень дискриминации 0 перед большими данными, поэтому некоторые люди могут спросить, имеет ли какое-либо значение опыта для этого соотношения? Различные сценарии использования, это значение также трудно определить.Как правило, поля, которые необходимо объединить, должны быть выше 0,1, то есть сканируется в среднем 10 записей.

4. Столбец индекса не может участвовать в расчете, держите столбец «чистым», например, from_unixtime(create_time) = '2014-05-29', индекс нельзя использовать, причина очень проста, дерево b+ хранит все поле значения в таблице данных, но при извлечении нужно применить функцию ко всем элементам для сравнения, что явно слишком дорого. Таким образом, оператор должен быть записан как create_time = unix_timestamp('2014-05-29');

5. Попробуйте расширить индекс, не создавайте новый индекс. Например, в таблице уже есть индекс a, и теперь нужно добавить индекс (a, b), затем нужно изменить только исходный индекс Конечно, исходные данные и онлайн-использование должны быть обдуманный.

Оптимизация MySQL

Оптимизация конфигурации

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

базовая конфигурация

• innodb_buffer_pool_size
  • Это первая опция, которую следует установить после установки InnoDB. Буферный пул — это место, где кэшируются данные и индексы: чем больше, тем лучше, это гарантирует, что вы используете память вместо диска для большинства операций чтения. Типичные значения: 5–6 ГБ (8 ГБ ОЗУ), 20–25 ГБ (32 ГБ ОЗУ), 100–120 ГБ (128 ГБ ОЗУ).
• innodb_log_file_size
  • Это размер журнала повторов. Журнал повторов используется для обеспечения быстрой и надежной записи, а также для восстановления после сбоев. Вплоть до MySQL 5.1 его было трудно настраивать, потому что, с одной стороны, вы хотели, чтобы он был больше, чтобы повысить производительность, а с другой стороны, вы хотели, чтобы он был меньше, чтобы обеспечить более быстрое восстановление после сбоя. К счастью, начиная с MySQL 5.5, производительность аварийного восстановления была значительно улучшена, так что вы можете одновременно иметь высокую производительность записи и аварийного восстановления. До MySQL 5.5 общий размер журнала повторов был ограничен 4 ГБ (по умолчанию может быть 2 файла журнала). Это было улучшено в MySQL 5.6. Если вы знаете, что вашему приложению нужно часто записывать данные, и вы используете MySQL 5.6, вы можете начать с того, чтобы сделать его 4G.
• max_connections
  • Если вы часто видите ошибку «Слишком много подключений», это связано с тем, что значение max_connections слишком низкое. Это очень распространено, потому что приложение не закрывает соединения с базой данных должным образом, и вам нужно большее значение, чем 151 соединение по умолчанию. Основным недостатком после того, как max_connection установлено высокое значение (например, 1000 или выше), является то, что сервер перестает отвечать на запросы при выполнении 1000 или более активных транзакций. Использование пула соединений в вашем приложении или пула процессов в MySQL может помочь решить эту проблему.

Конфигурация InnoDB

• innodb_file_per_table
  • Этот параметр сообщает InnoDB, следует ли хранить все данные таблицы и индексы в общем табличном пространстве (innodb_file_per_table = OFF) или в отдельном файле .ibd для каждой таблицы данных (innodb_file_per_table = ON). Один файл на таблицу позволяет освободить место на диске при удалении, усечении или перестроении таблицы. Это также необходимо для некоторых расширенных функций, таких как сжатие данных. Но прироста производительности это не дает. Основной сценарий, когда вы не хотите иметь один файл на таблицу, — это когда таблиц очень много (скажем, 10 000+). В MySQL 5.6 это свойство по умолчанию включено, поэтому в большинстве случаев вам не нужно ничего делать. В предыдущих версиях перед загрузкой данных необходимо было установить для этого свойства значение ON, поскольку оно влияет только на вновь созданные таблицы.
• innodb_flush_log_at_trx_commit
  • Значение по умолчанию равно 1, что означает, что InnoDB полностью поддерживает функции ACID. Это значение наиболее подходит, когда вашей основной задачей является безопасность данных, например, на главном узле. Но для систем с более низкой скоростью диска (чтения и записи) это может быть огромным накладным расходом из-за дополнительных fsync, требуемых каждый раз, когда изменение сбрасывается в журнал повторов. Установка его значения на 2 приведет к ненадежности (надежности), поскольку зафиксированная транзакция сбрасывается в журнал повторов только один раз в секунду, но это приемлемо для некоторых сценариев, таких как резервный узел основного узла.Это значение приемлемо для . Значение 0 быстрее, но может привести к потере некоторых данных в случае сбоя системы: только для резервных узлов.
• innodb_flush_method
  • Эта конфигурация определяет способ записи данных и журналов на диск. В общем, если у вас есть аппаратный RAID-контроллер с механизмом обратной записи для его независимого кэша и защиты аккумулятора, для него следует установить значение O_DIRECT; в противном случае в большинстве случаев следует установить значение fdatasync (значение по умолчанию). sysbench — хороший инструмент, который поможет вам выбрать этот вариант.
• innodb_log_buffer_size
  • Эта конфигурация определяет кэш, выделяемый для еще не выполненных транзакций. Его значения по умолчанию (1 МБ) обычно достаточно, но если ваша транзакция содержит большие двоичные объекты или большие текстовые поля, этот буфер быстро заполнится и вызовет дополнительные операции ввода-вывода. Посмотрите на переменную состояния Innodb_log_waits, если она не равна 0, увеличьте innodb_log_buffer_size.

другие настройки

• query_cache_size
  • Кэш запросов является хорошо известным узким местом, даже если параллелизм невелик. Лучший вариант — отключить его с самого начала, установить query_cache_size = 0 (теперь значение по умолчанию в MySQL 5.6) и использовать другие методы для ускорения запросов: оптимизировать индексы, увеличить копирование для распределения нагрузки или включить дополнительные кеши (например, memcache или redis). ). Если вы включили кеш запросов для своего приложения и не заметили никаких проблем, кеш запросов может быть вам полезен. Это то, о чем нужно быть осторожным, если вы хотите отключить его.
• log_bin
  • Включение ведения двоичного журнала требуется, если вы хотите, чтобы сервер базы данных действовал как резервный узел для основного узла. Если вы сделаете это, не забудьте установить для server_id уникальное значение. Даже при наличии только одного сервера это полезно, если вы хотите выполнить восстановление данных на определенный момент времени (включить ведение двоичного журнала): восстановить из самой последней резервной копии (полное резервное копирование) и применить изменения в двоичном журнале (добавочное резервное копирование). . После создания двоичного журнала он сохраняется постоянно. Поэтому, если вы не хотите, чтобы на диске заканчивалось место, вы можете использовать PURGE BINARY LOGS для очистки старых файлов или установить expire_logs_days, чтобы указать, через сколько дней журналы будут автоматически очищены. Запись двоичного журнала не обходится без накладных расходов, поэтому рекомендуется отключить этот параметр, если он не нужен на неосновной реплике.
• skip_name_resolve
  • Когда клиент подключается к серверу базы данных, сервер выполняет разрешение имени хоста, и когда DNS работает медленно, соединение также устанавливается медленно. Поэтому рекомендуется отключить параметр skip_name_resolve, не выполняя поиск DNS при запуске сервера. Единственным ограничением является то, что в операторе GRANT можно использовать только IP-адреса, поэтому следует соблюдать осторожность при добавлении этого параметра в существующую систему.

Настройка SQL

Как правило, необходимо выполнить настройку SQL.Для SQL с медленным запросом система или сервер могут включить журнал медленных запросов.Журнал медленных запросов обычно включен, особенно для онлайн-систем.Если есть медленный запрос, его можно отфильтровать по журналу. Но после того, как вы узнали SQL, который необходимо оптимизировать, следующее, что нужно сделать, — это настроить его.

Основные шаги для оптимизации медленных запросов

0. Сначала запустите его, чтобы увидеть, действительно ли он работает медленно, обратите внимание на настройку SQL_NO_CACHE.
1. Условием where является запрос к одной таблице, а минимальная возвращаемая таблица записей заблокирована. Смысл этого предложения состоит в том, чтобы применить оператор запроса where к таблице с наименьшим количеством записей, возвращаемых в таблице, и начать поиск Каждое поле отдельной таблицы запрашивается отдельно, чтобы увидеть, какое поле имеет наивысшую степень дискриминация.
2. объясните, как проверить план выполнения, соответствует ли он ожиданию 1 (начать запрос из таблицы с меньшим количеством заблокированных записей)
3. Оператор sql в форме order by limit позволяет сначала проверить отсортированную таблицу.
4. Понимание сценариев использования на стороне бизнеса
5. При добавлении индексов учитывайте несколько принципов построения индексов.
6. Результаты наблюдения, не соответствующие ожиданиям, продолжают анализировать с 0

Часто используемые методы настройки

план выполненияобъяснить

В нашей повседневной работе мы иногда открываем медленные запросы для записи некоторых операторов SQL, которые выполнялись в течение длительного времени.Нахождение этих операторов SQL не означает, что мы закончили.Мы часто используем команду объяснения для просмотра плана выполнения этих SQL , чтобы проверить, использовал ли оператор SQL индекс и было ли выполнено полное сканирование таблицы, что можно проверить с помощью команды объяснения. Итак, мы углубимся в оптимизатор MySQL на основе затрат, а также получим много подробностей о стратегиях доступа, которые могут быть приняты во внимание оптимизатором, и о том, какая стратегия, как ожидается, будет принята оптимизатором при выполнении оператора SQL.

Чтобы использовать объяснение, вам нужно всего лишь добавить ключевое слово объяснения к исходному выбору, как показано ниже:

mysql> explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | servers | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.03 sec)

Кратко объясните значение каждого поля объяснения

1. id : идентификатор, указывающий порядок выполнения SQL, SQL выполняется от большого к меньшему
2. select_type: указывает тип каждого предложения select в запросе.
3. таблица: показывает, к какой таблице относятся данные в этой строке, иногда не настоящее имя таблицы.
4. тип: указывает, как MySQL находит нужную строку в таблице, также известную как «тип доступа». Обычно используются следующие типы: ALL, index, range, ref, eq_ref, const, system, NULL (слева направо, от плохой до хорошей производительности).
5. возможных_ключей: указывает, какой индекс MySQL может использовать для поиска записей в таблице.Если в поле, участвующем в запросе, есть индекс, индекс будет указан, но не обязательно будет использоваться запросом.
6. Ключ: Столбец ключа показывает ключ (индекс), который MySQL фактически решил использовать.Если индекс не выбран, ключ равен NULL.
7. key_len: указывает количество байтов, используемых в индексе, которое можно использовать для расчета длины индекса, используемого в запросе (значение, отображаемое key_len, представляет собой максимально возможную длину поля индекса, а не фактическую длину, т.е. , key_len вычисляется в соответствии с определением таблицы, а не извлекается из таблицы)
8. ref: Указывает условие соответствия соединения для приведенной выше таблицы, то есть какие столбцы или константы используются для поиска значения в индексном столбце.
9. rows: Указывает, что MySQL оценивает количество строк, которые нужно прочитать, чтобы найти необходимые записи на основе статистики таблицы и выбора индекса.Теоретически, чем меньше строк, тем выше производительность запроса.
10. Дополнительно: этот столбец содержит сведения о запросе, разрешенном MySQL.

Особенности ОБЪЯСНЕНИЯ

• EXPLAIN не сообщает вам о триггерах, хранимых процедурах или о том, как определяемые пользователем функции влияют на запрос.
• EXPLAIN не учитывает различные кэши.
• EXPLAIN не показывает работу по оптимизации, проделанную MySQL при выполнении запроса.
• Некоторые статистические данные являются приблизительными, а не точными значениями.
• EXPALIN может объяснить только операцию SELECT, остальные операции следует переписать как SELECT для просмотра плана выполнения.

Практическая тренировка

Структура таблицы и оператор запроса

Если есть следующая структура таблицы

circlemessage_idx_0 | CREATE TABLE `circlemessage_idx_0` (
  `circle_id` bigint(20) unsigned NOT NULL COMMENT '群组id',
  `from_id` bigint(20) unsigned NOT NULL COMMENT '发送用户id',
  `to_id` bigint(20) unsigned NOT NULL COMMENT '指定接收用户id',
  `msg_id` bigint(20) unsigned NOT NULL COMMENT '消息ID',
  `type` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '消息类型',
  PRIMARY KEY (`msg_id`,`to_id`),
  KEY `idx_from_circle` (`from_id`,`circle_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

Проанализируйте следующий оператор запроса с помощью объяснения плана выполнения.

mysql> explain select msg_id from circlemessage_idx_0 where  to_id = 113487 and circle_id=10019063  and msg_id>=6273803462253938690  and from_id != 113487 order by msg_id asc limit 30;
+----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
| id | select_type | table               | type  | possible_keys           | key     | key_len | ref  | rows   | Extra       |
+----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | circlemessage_idx_0 | range | PRIMARY,idx_from_circle | PRIMARY | 16      | NULL | 349780 | Using where |
+----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> explain select msg_id from circlemessage_idx_0 where  to_id = 113487 and circle_id=10019063   and from_id != 113487 order by msg_id asc limit 30;
+----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
| id | select_type | table               | type  | possible_keys   | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | circlemessage_idx_0 | index | idx_from_circle | PRIMARY | 16      | NULL |   30 | Using where |
+----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

анализ проблемы

С помощью двух приведенных выше планов выполнения можно обнаружить, что при отсутствии условия запроса msg_id >= xxx извлекаемых строк намного меньше, и в запросе используются оба индекса, а используется только индекс первичного ключа. Это показывает, что индекс должен быть необоснованным и не работать в полной мере.

Анализируя этот план выполнения, мы видим, что при включении условия запроса msg_id >= xxx в строках содержится более 34w строк, в данном случае это означает, что слишком много извлечений, либо таблица действительно такая большая, или индекс неразумен и бесполезен Когда дело доходит до индекса, в большинстве случаев использовать индекс бесполезно и разумно. Индекс, используемый в столбце, также является ПЕРВИЧНЫМ, что может быть (msg_id,to_id), обратите внимание, что порядок индекса msg_id находится перед to_id, когда мы создаем таблицу, поэтому запрос MySQL обязательно сначала будет использовать индекс msg_id, после использования индекса msg_id будет получено 34w строк, и из-за индекса msg_id условие запроса больше или равно, поэтому после этого условия запроса индекс to_id нельзя использовать снова.

Затем посмотрите на длину key_len, равную 16, в сочетании с ключом как PRIMARY, после чего можно проанализировать, что используется только один индекс первичного ключа.

Наконец, посмотрите на значение типа, которое является диапазоном, что означает, что запрос является либо запросом диапазона, либо совпадением нескольких значений.

Обратите внимание, что оператор from_id != xxx не может использовать индекс. Можно использовать только from_id = xxx, поэтому на самом деле можно использовать индекс from id, и это необходимо тщательно учитывать при создании индекса.

Как оптимизировать

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

Затем начните следующий шаг анализа. Поскольку msgid задан как первичный ключ, он должен быть уникальным в глобальном масштабе. Всего msgid будет не меньше, чем объемов данных, тогда получение msg_id по сути является получением всей таблицы. Оптимизация, которую нам нужно сделать, состоит в том, чтобы свести к минимуму индекс и уменьшить количество строк в запросе; затем нам нужно подумать о том, какие поля можно запрашивать, чтобы уменьшить количество строк? Например, в таблице данных определенного пользователя будет меньше, чем количество строк для запроса msgid? Запрос определенного пользователя и принадлежность к определенному кругу, будет меньше? и Т. Д. . .

Тогда, согласно анализу фактической ситуации, количество строк, попадающих в to_id в одной таблице, должно быть меньше, чем количество попаданий в msg_id, поэтому необходимо сначала убедиться, что можно использовать индекс to_id. вы можете взаимодействовать с порядком msg_id и to_id при установке первичного ключа; Однако, поскольку это уже онлайн-таблица, данных уже много, и бизнес работает. В этом случае изменение первичного ключа вызовет много проблем (конечно, изменение индекса допустимо), поэтому не рекомендуется изменять первичный ключ напрямую. Затем, чтобы обеспечить эффективное использование индекса to_id, необходимо создать новый совместный индекс, тогда первым полем индекса только что созданного совместный индекс должен быть to_id.Для этого бизнес-сценария лучше всего добавить Circle_id index, который может быстро индексировать; таким образом получается индекс нового объединенного index (to_id, circle_id), а затем, поскольку msg_id должен быть найден, на этой основе добавляется msg_id. Окончательный совместный индекс (to_id, circle_id, msg_id); в этом случае такие операторы запроса могут быть быстро получены: где to_id = xxx и circle_id = xxx и msgId >= xxx

Конечно, создание индекса не означает, что определенному оператору sql нужен индекс, но устанавливается совместный индекс.В этом случае, если индексов слишком много, это повлияет на производительность записи (вставка, удаление, модификация), и тогда соответственно увеличится и место для хранения; кроме того, mysql будет потреблять еще и ресурсы для поддержания индексов в процессе работы, поэтому чем больше индексов, тем лучше, необходимо объединить наиболее часто запрашиваемые и наиболее влияющие на производительность sql установить соответствующие индексы. Следует отметить, что совместный индекс или набор первичных ключей — это b-дерево, а несколько индексов — это несколько b-деревьев.

Суммировать

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

1. Больше индексов не лучше
2. Различие между первичным ключом и индексом
3. Понимание принципа структуры индекса
4. Общие сведения о правилах индексирования запросов

Ссылаться на

Принцип индексации Meituan-MySQL и оптимизация медленных запросов

Структура данных и принцип алгоритма индекса MySQL

Спасибо оригинальному автору справочной статьи

["Добро пожаловать, обратите внимание на мою общедоступную учетную запись WeChat: разработка серверной системы Linux, и позже я буду активно отправлять высококачественные статьи через общедоступную учетную запись WeChat"]