Что такое «призрак» индекса Mysql

предисловие

Существует много видов индексов, хэш-индекс, индекс B-дерева, индекс дерева B+, полнотекстовый индекс и т. д. Mysql поддерживает несколько механизмов хранения, а несколько механизмов хранения по-разному поддерживают индексы. В этой статье рассматривается, почему Mysql использует дерево B+ в качестве структуры данных индекса, принцип индекса и оптимизация индекса в Sql.
Официальное определение индекса в Mysql состоит в том, что индекс — это структура данных, которая помогает Mysql эффективно получать данные. Извлечение основы предложения — это просто: индекс — это структура данных.

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

Цель индекса

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

Почему индекс Mysql представляет собой дерево B+?

Почему бы не использовать обычное бинарное дерево?Здесь нет необходимости говорить больше, потому что для большого количества данных высота бинарного дерева слишком велика, а эффективность индексации низкая. Здесь мы в основном объясняем, почему B-деревья не используются (B-дерево - это B-дерево), но используйте дерево B+.

B-дерево (B-дерево)

Все мы знаем, что временная сложность запроса бинарного дерева составляет O(logN), а эффективность запроса достаточно высока, но почему существуют B-деревья и B+ деревья? Ответ: дисковый ввод-вывод. Все мы знаем, что эффективность операций ввода-вывода очень низкая, когда в хранилище большое количество данных, при запросе мы не можем загрузить все данные в память, мы можем только загружать страницы диска по одной, каждую страницу диска соответствует Узлы дерева вызывают большое количество операций дискового ввода-вывода (в худшем случае количество операций дискового ввода-вывода равно высоте дерева) Сбалансированное двоичное дерево приводит к слишком частому чтению и записи дискового ввода-вывода из-за к большой высоте дерева, что приводит к низкой эффективности.Появилось дерево поиска - B-дерево/B+дерево.
Ниже приведено B-дерево третьего порядка (на самом деле элементов узлов много).

B-деревья обладают следующими характеристиками:

Данные и указатели хранятся в узле и отделены друг от друга
В пределах одного узла ключи расположены в порядке возрастания
Если крайний левый указатель узла не равен нулю, ключ слева и справа от узла, который он указывает, меньше, чем крайний левый ключ. Ключ узла, на который указывает средний указатель, расположен посередине двух соседних ключей.
Количество ключей и указателей, хранящихся в разных узлах в B-дереве, может быть несовместимым, но домен и верхний предел каждого узла являются последовательными, поэтому в реализации B-дерева часто применяет одинаковый размер пространства для каждого узла
Каждый нелистовой узел состоит из n-1 ключей и n указателей, где d

В+ дерево

Дерево B+ имеет следующие характеристики:

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

Почему дерево B+ больше подходит для индекса Mysql, чем дерево B

Стоимость чтения с диска B+-дерева низкая: поскольку неконечные узлы B+-дерева не хранят данные, если все ключевые слова одного и того же внутреннего узла хранятся в одном и том же блоке диска, количество ключевых слов, которые диск блок может удерживать также увеличится.много. Чем больше ключевых слов нужно искать в памяти одноразового чтения. Условно говоря, количество операций чтения и записи ввода-вывода также уменьшается.
Эффективность запросов B+-дерева более стабильна: поскольку ветвью B+-дерева является не тот узел, который окончательно указывает на содержимое файла, а индекс конечного узла, поиск любого ключевого слова должен идти от корневого узла. к узлу филиала, а путь запроса такой же. Однако узлы ветвления B-дерева хранят данные, поэтому путь запроса может быть другим.
С деревом B+ легко выполнить операцию сканирования базы данных: поскольку данные дерева B+ хранятся на листовых узлах, а все узлы ветвей являются индексами, удобно сканировать базу данных, достаточно один раз просмотреть листья. Однако B-дерево хранит данные об узлах ветвления.Чтобы найти определенные последовательные данные, вам нужно выполнить обход в порядке их поиска.

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

Мы знаем, что в Mysql есть два широко используемых механизма хранения, MyISAM и InnoDB.Эти два механизма хранения реализуют индексы по-разному.

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

MyISAM использует дерево B+ в качестве структуры индекса, а поле данных конечного узла хранит адрес записи данных. На приведенном выше рисунке представлена схема первичного индекса MyISAM с Col1 в качестве первичного ключа. Видно, что поле данных листового узла на нижнем уровне группы хранит адрес записи данных. Если мы построим вспомогательный индекс по полю Col2, то структура индекса будет следующей:

Алгоритм поиска индекса MyISAM подобен следующему: сначала запрашивается индекс в соответствии с алгоритмом поиска дерева B+.Если указанный ключ существует, извлекается значение поля данных, а затем запрашивается запись данных с адресом данных. поле. Метод индексирования MyISAM также называется «некластеризованным», что отличается от «кластеризованного индекса» InnoDB, поскольку записи данных и индексы не связаны друг с другом.

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

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

Во-вторых, в поле данных вторичного индекса InnoDB вместо адреса хранится значение первичного ключа соответствующей записи. Как показано на рисунке, следующий рисунок представляет собой схематическое представление вспомогательного индекса, определенного в Col3. Листовой узел хранит значение col3 и значение соответствующего первичного ключа col1.

Оптимизация индекса

Самый левый принцип префикса

Совместный индекс: MySQL может использовать несколько столбцов в качестве индекса по порядку.Такой тип индекса называется объединенным индексом.
Самый левый принцип сопоставления индекса: если столбцы индекса — A, B, C, а порядок — также A, B, C, то:

При запросе, если вы запрашиваете [A], [A, B], [A, B, C], вы можете использовать индексный запрос.
Если вы запрашиваете [A, C] при запросе, потому что B отсутствует в середине, то индекс C нельзя использовать, и можно использовать только индекс A.
Если вы запросите [B], [B, C] или [C], поскольку крайний левый префикс A отсутствует, этот совместный индекс не будет использоваться, если нет других индексов.
Если при запросе используется запрос диапазона, и это крайний левый префикс, то можно использовать индекс, но поля за диапазоном не могут использовать индекс.

Этот принцип можно понять в сочетании с принципом индексации: индекс Mysql представляет собой составную структуру дерева B+.Когда индекс является совместным индексом, таким как [имя, возраст, пол], дерево B+ строит дерево индекса в порядок слева направо. . Когда будут получены такие данные, как (Чжан Сан, 20, М), дерево B+ сначала определит следующее направление поиска в соответствии с именем.Если имя совпадает, то сравните имя и пол и, наконец, получите извлеченные данные. . Однако, когда приходят такие данные, как (20, M), mysql не знает, какой узел искать, потому что при создании индекса имя является первым фактором сравнения, и вы должны сначала определить, где искать дальше, исходя из имени. Когда приходят такие данные, как (Чжан Сан, М), вы можете определить следующий поиск на основе имени «Чжан Сан», а затем сопоставить данные, чей пол «М», поэтому можно использовать только имя в совместном индексе. этот индекс.

Другие принципы

1. Попробуйте выбрать в качестве индекса столбец с высокой степенью дискриминации.Формула степени дискриминации: count(различный столбец)/count(*), которая указывает долю неповторяющихся полей. Чем больше доля, тем мы сканируем меньше записей, а столбец уникальности имеет степень дискриминации 1. Вот почему не рекомендуется создавать индексы для столбцов, которые являются очень дискриминационными, такими как статус и пол.
2. Столбец индекса не может участвовать в операции в операторе SQL, иначе индекс будет недействительным. Например, from_unixtime(create_time) = '2014-05-29' индекс использовать нельзя. Причина очень проста. Все значения полей в таблице данных хранятся в дереве b+, но при извлечении вы необходимо применить функцию ко всем элементам.По сравнению с этим стоимость явно слишком высока. Следует изменить на create_time = unix_timestamp('2014-05-29');
3. Совместный индекс более экономичен, чем единый индекс. Например, установление общего индекса [A, B, C] эквивалентно установлению трех индексов [A], [A, B], [A, B, C]. Это требует от нас максимально возможного расширения индекса вместо создания нового индекса, и конкретная ситуация должна быть детально проанализирована.
4. Поля, которые часто запрашиваются, должны быть заново проиндексированы, поля, связанные с другими таблицами, можно рассматривать как создание новых индексов, а поля, отсортированные в запросе, можно рассматривать как создание индекса для повышения эффективности сортировки (например, , много раз требуется создать записи запроса в соответствии с Время возвращается в обратном порядке, обычно кто-то делает заказ по описанию create_time, но если create_time не является индексом, и эта таблица имеет автоматически увеличивающийся идентификатор первичного ключа, тогда order by id desc возвращает тот же результат, но эффективность будет выше).

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

Причины медленного выполнения sql

1. Проблемы с оборудованием: например, низкая скорость сети, нехватка памяти, низкая пропускная способность ввода-вывода, полное дисковое пространство и т. д.
2. Индекс не используется или недействителен.
3. Слишком много данных (подбаза данных и подтаблица).
4. Неверные настройки сервера или параметров.

Анализировать и решать медленные методы SQL

1. Сначала понаблюдайте, откройте журнал медленных запросов, установите соответствующий порог (например, если он превышает 3 секунды, это медленный SQL), а затем запустите производственную среду на день, чтобы увидеть, какой SQL медленный.
2. Объясните и замедлите анализ SQL, например плохо написанные операторы SQL, отсутствие использования индексов или недопустимые индексы, или слишком сложные операторы SQL, слишком много связанных запросов и вложенных подзапросов и т. д.
3. Показать профиль на шаг ближе к деталям выполнения, чем объяснить.Вы можете запросить, что произошло с каждым выполнением SQL и сколько секунд заняло каждое из этих действий.
4. Найдите DBA или операции и техническое обслуживание для настройки параметров сервера Mysql.

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

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

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

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

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

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

Подготовить данные

CREATE TABLE `user_info` (

  `id`   BIGINT(20)  NOT NULL AUTO_INCREMENT,

  `name` VARCHAR(50) NOT NULL DEFAULT '',

  `age`  INT(11)              DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `name_index` (`name`)

)ENGINE = InnoDB DEFAULT CHARSET = utf8;

 

INSERT INTO user_info (name, age) VALUES ('xys', 20);

INSERT INTO user_info (name, age) VALUES ('a', 21);

INSERT INTO user_info (name, age) VALUES ('b', 23);

INSERT INTO user_info (name, age) VALUES ('c', 50);

INSERT INTO user_info (name, age) VALUES ('d', 15);

INSERT INTO user_info (name, age) VALUES ('e', 20);

INSERT INTO user_info (name, age) VALUES ('f', 21);

INSERT INTO user_info (name, age) VALUES ('g', 23);

INSERT INTO user_info (name, age) VALUES ('h', 50);

INSERT INTO user_info (name, age) VALUES ('i', 15);

 

CREATE TABLE `order_info` (

  `id`           BIGINT(20)  NOT NULL AUTO_INCREMENT,

  `user_id`      BIGINT(20)           DEFAULT NULL,

  `product_name` VARCHAR(50) NOT NULL DEFAULT '',

  `productor`    VARCHAR(30)          DEFAULT NULL,

  PRIMARY KEY (`id`),

  KEY `user_product_detail_index` (`user_id`, `product_name`, `productor`)

)ENGINE = InnoDB DEFAULT CHARSET = utf8;

 

INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'WHH');

INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p2', 'WL');

INSERT INTO order_info (user_id, product_name, productor) VALUES (1, 'p1', 'DX');

INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p1', 'WHH');

INSERT INTO order_info (user_id, product_name, productor) VALUES (2, 'p5', 'WL');

INSERT INTO order_info (user_id, product_name, productor) VALUES (3, 'p3', 'MA');

INSERT INTO order_info (user_id, product_name, productor) VALUES (4, 'p1', 'WHH');

INSERT INTO order_info (user_id, product_name, productor) VALUES (6, 'p1', 'WHH');

INSERT INTO order_info (user_id, product_name, productor) VALUES (9, 'p8', 'TE');

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

Анализ объяснить

ID тот же, порядок выполнения сверху вниз

Идентификатор разный, чем больше значение, тем быстрее выполнение

select_type

Всего существует следующие типы select_type:
1. SIMPLE: указывает, что запрос не использует UNION или подзапросы.
2. PRIMARY: указывает, что этот запрос является самым внешним запросом.
3. ПОДЗАПРОС: указывает, что этот запрос является первым запросом
4. UNION: указывает, что этот запрос является вторым или последующим запросом UNION. 5. ЗАВИСИМОЕ ОБЪЕДИНЕНИЕ: второй или последующий оператор запроса в ОБЪЕДИНЕНИИ зависит от внешнего запроса. 6. ОБЪЕДИНЕНИЕ РЕЗУЛЬТАТА: Результат ОБЪЕДИНЕНИЯ 7. ЗАВИСИМЫЙ ПОДЗАПРОС: первый SELECT в подзапросе зависит от внешнего запроса, то есть подзапрос зависит от результата внешнего запроса 8. ПРОИЗВОДНАЯ: Производная, указывающая ВЫБОР производной таблицы.

table

table представляет таблицу или производную таблицу, задействованную в запросе

Таблица производных2 с id=1 означает, что она получена из u и o с id=2

type

Поле типа более важно, и это важная основа для оценки эффективности запроса.
1, система: в таблице есть только одни данные, этот тип является специальным константным типом.
2. const: сканируйте условие равенства знаков первичного ключа или уникального индекса и возвращайте не более одного фрагмента данных, а скорость запроса чрезвычайно высока, потому что его нужно прочитать только один раз.
3. eq_ref: этот тип обычно появляется в соединениях с несколькими таблицами, что означает, что для каждого результата в предыдущей таблице может быть сопоставлена только одна строка результатов в последней таблице, а операция сравнения запроса обычно = и эффективность запроса высока.
4. ref: этот тип обычно представляет собой объединение нескольких таблиц для неуникальных индексов, или индексов, не использующих первичный ключ, или индексов, использующих правило крайнего левого префикса.
5. диапазон: указывает, что используется запрос диапазона индекса, и некоторые записи данных в таблице получены через диапазон поля индекса.Этот тип обычно появляется в =, , >, >=, , МЕЖДУ, В() операции.
6. index: указывает на полное сканирование индекса, аналогичное типу ALL, за исключением того, что тип All представляет собой полное сканирование таблицы, а индекс сканирует все индексы без сканирования данных. Тип индекса обычно указывается в: запрашиваемые данные могут быть получены непосредственно в дереве индекса без сканирования данных. В этом случае в поле Дополнительно отображается Использование индекса.
7. ALL: указывает на полное сканирование таблицы с наихудшей производительностью. Когда объем данных велик, это будет огромной катастрофой для базы данных.

possible_keys

Он представляет индекс, который mysql может использовать при запросе. Примечание: Даже если какие-то индексы появляются в возможных_ключах, это не означает, что они действительно используются.Какие индексы использует mysql при запросе, определяется полем ключа.

key

Это индекс, который mysql фактически использует при выполнении запросов.

key_len

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

ref

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

rows

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

extra

Много дополнительной информации в объяснении будет отображаться в дополнительном поле, общие из них следующие:
1. использование файловой сортировки: указывает, что mysql требует дополнительных операций сортировки, и эффект сортировки не может быть достигнут с помощью индексного порядка. Вообще использование файловой сортировки рекомендуется оптимизировать и убрать, так как такой запрос потребляет много ресурсов процессора.
2. Использование индекса: сканирование покрывающего индекса, что означает, что запрос может найти необходимые данные в дереве индекса без сканирования файла данных таблицы, что часто означает хорошую производительность.
3. использование временной: запрос использует временную таблицу, что обычно происходит в случае сортировки, группировки и объединения нескольких таблиц.Эффективность запроса невысока, рекомендуется оптимизация.
4. использование где: указывает, где используется фильтрация.

Суммировать

Общие принципы индексации:

Выберите уникальный индекс: значение уникального индекса уникально, и запись может быть определена быстрее с помощью этого индекса.
Поля индекса, которые часто требуют операций сортировки, группировки и объединения.
Создавайте индексы для полей, которые часто используются в качестве условий запроса.
Ограничьте количество индексов: чем больше индексов, тем лучше, слишком большое количество индексов замедляет вставки и обновления из-за необходимости поддерживать дерево индексов.
Попробуйте использовать индекс с небольшим объемом данных.Если значение индекса очень длинное, это повлияет на скорость запроса.
Принцип соответствия крайнего левого префикса — очень важный принцип.
Попробуйте выбрать в качестве индекса столбец с высокой степенью дискриминации.Формула степени дискриминации - это доля полей, которые не повторяются.
Индексированные столбцы не могут участвовать в вычислениях, держите столбцы «чистыми»: запросы с функциями не участвуют в индексации.
Удалите индексы, которые больше не используются или используются редко.
Расширьте индекс, насколько это возможно, вместо того, чтобы создавать новый индекс.Если вы можете использовать составной индекс, не создавайте индекс.