Интервьюер задал вопрос о проблеме индекса MySQL, эта статья решит все за вас!

MySQL

Исходная ссылка: blog.ouyangsihai.cn >>Концепция, использование, оптимизация и сценарии использования индекса B-дерева MySQL.

0 Предисловие

В этой статье не будут объясняться базовые знания об индексах, в основном о соответствующих принципах индексов B-деревьев в базах данных MySQL.Некоторые сведения в ней относятся к книге Inside MySQL Technology, которая также является кратким изложением этих знаний. За знаниями, связанными с B-tree и B-tree, вы можете обратиться к этому моему блогу:Интервьюер спрашивает вас про B-деревья и B-деревья, киньте ему эту статью

1 Управление индексами

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

1.1 Как создается индекс

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

  • Создать индекс напрямую
CREATE [UNIQUE|FULLLTEXT] INDEX index_name ON table_name(column_name(length))

[UNIQUE|FULLLTEXT]: указывает необязательный тип индекса, уникальный индекс, полнотекстовый индекс или обычный индекс без каких-либо дополнительных слов.table_name: имя таблицы, которое означает, какая таблица добавляется для добавления индекса.column_name(length): имя_столбца — это имя столбца таблицы, а длина означает добавление индекса к первой строке длины этого столбца.

  • Добавьте индекс, изменив структуру таблицы
ALTER TABLE table_name ADD [UNIQUE|FULLLTEXT] INDEX index_name (column(length))
  • Создавать индекс при создании таблицы
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    PRIMARY KEY (`id`),
    [UNIQUE|FULLLTEXT] INDEX index_name (title(length))
)

1.2 способ и комбинации первичный ключевой индекс созданный индекс

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

Как создать составной индекс

  • Создавать индекс при создании таблицы
CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    PRIMARY KEY (`id`),
    INDEX index_name(id,title)
)
  • Добавьте индекс, изменив структуру таблицы
ALTER TABLE table_name ADD INDEX name_city_age (name,city,age);

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

CREATE TABLE `table` (
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    PRIMARY KEY (`id`)
)

1.3 удалить индекс

Удалить доступный индексALTER TABLEилиDROP INDEX语句来删除索引。 похожий наCREATE INDEXутверждение,DROP INDEXдопустимыйALTER TABLEВнутренне обрабатывается как инструкция, имеет следующий синтаксис.

(1)DROP INDEX index_name ON talbe_name (2)ALTER TABLE table_name DROP INDEX index_name (3)ALTER TABLE table_name DROP PRIMARY KEY

3-й оператор только после удаленияPRIMARY KEYИспользуется при индексировании, так как таблица может иметь только одинPRIMARY KEYindex, поэтому нет необходимости указывать имя индекса.

1.4 Примеры указателей

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

  • шаг 1: создать таблицу
 create table table_index(
    id int(11) not null auto_increment,
    title char(255) not null,
    primary key(id)
);
  • шаг 2: добавить индекс

Во-первых, мы добавляем обычный индекс к тому, как вы добавляете индекс.

CREATE INDEX idx_a ON table_index(title);

Далее мы добавляем индексы при изменении структуры таблицы.

ALTER TABLE table_index ADD UNIQUE INDEX idx_b (title(100));

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

 ALTER TABLE table_index ADD INDEX idx_id_title (id,title);

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

  • Шаг 3: используйтеSHOW INDEXКоманда для просмотра индексной информации

Если вы хотите просмотреть информацию индекса в таблице, вы можете использовать командуSHOW INDEX, в следующем примере мы смотрим на таблицуtable_indexиндексная информация.

 SHOW INDEX FROM table_index\G;

Получите вышеуказанную информацию, приведенная выше информация, что это значит? Мы представили один за другим!

поле объяснять
Table таблица, в которой находится индекс
Non_unique Неуникальный индекс, если он равен 0, значит он уникален, то есть если индекс столбца не включает повторяющиеся значения, он равен 0, иначе он равен 1
Key_name Имя индекса, если затем сравнивается первичный ключ PRIMARY
Seq_in_index Расположение этой колонны в индексе, начиная с 1, если он является комбинированным индексом, а затем организовать порядок, в котором установлено поле
Collation Как столбец хранится в индексе. Может быть A или NULL, индекс B-дерева всегда A, отсортированный,
Sub_part Индексируется ли часть столбца, если индексируются только первые 100 строк, отображать 100, если это весь столбец, отображать NULL
Packed Сжато ли ключевое слово, если нет, то NULL
Index_type Тип индекса.Для InnoDB поддерживаются только индексы B-tree, поэтому отображается BTREE.
  • шаг 4: удалить индекс

Напрямую удалить метод индекса

DROP INDEX idx_a ON table_index;

Удаление индексов при изменении структуры таблицы

ALTER TABLE table_index DROP INDEX idx_b;

1.5 Анализ ключевых слов кардинальности

В описанном выше значении, что несколько ключевых слов, ноCardinalityЭто ключевое слово очень важно, и оптимизатор определит, использовать ли этот индекс на основе этого значения. В индексе B-дерева имеют значение только очень избирательные поля.высокая селективностьТо есть диапазон значений этого поля очень широк, например, поле имени, имен будет много, и избирательность будет высокой.

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

Когда вам нужен вопрос, это ключевое слово не обновляется во времени. Если вам нужно обновить, вам нужно его использовать.ANALYZE TABLE,Например.

analyze table table_index;

Поскольку в настоящее время данных нет, вы обнаружите, что это значение всегда было равно 0 и не изменилось.

Стратегия механизма хранения InnoDB Cardinality

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

  • Таблица1/16данные изменены
  • Счетчики для механизма хранения InnoDBstat_modified_conter>2000000000

Механизм хранения InnoDB по умолчанию производит выборку конечных узлов 8. Процесс выборки выглядит следующим образом:

  • Количество листовых узлов в индексе B-дерева, обозначаемое какA
  • случайныйПолучить индекс B-дерева8листовой узел. Подсчитайте количество разных записей на каждой странице, p1-p8
  • Оценочное значение Cardinality получено по данным выборки:(p1 p2 p3 ... p8)*A/8

Из-за случайной выборки каждый раз, когда значение Cardinality отличается, только одна ситуация будет одинаковой, то есть конечные узлы в таблице.меньше или равно 8, На этот раз, как случайным образом выбрать эти 8, так что это то же самое.

1.6 Fast Index Creation

До MySQL 5.5,Для добавления или удаления индекса необходимо каждый раз создавать временную таблицу, затем импортировать данные во временную таблицу, а затем удалять исходную таблицу, если такую ​​операцию производить на большой таблице, то она будет очень трудоёмкой, что является большим недостатком.

Механизм хранения InnoDB добавил метод создания индекса Fast Index Creation, начиная с версии 1.0.x.

Стратегия таким образом:Каждый раз, когда в таблицу, где создается индекс, добавляется блокировка S (общая блокировка), нет необходимости перестраивать таблицу при создании индекса.Чтобы удалить вспомогательный индекс, необходимо обновить только внутреннее представление, а вспомогательное индексное пространство помечается как доступное., поэтому эффективность значительно повышается.

1.7 определение онлайн-данных

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

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

ALTER TABLE table_name ADD [UNIQUE|FULLLTEXT] INDEX index_name (column(length))
[ALGORITHM = {DEFAULT|INPLACE|COPY}]
[LOCK = {DEFAULT|NONE|SHARED|EXLUSIVE}]

ALGORITHMУказывает алгоритм создания или удаления индекса.

  • КОПИРОВАТЬ: Как создать временную таблицу
  • INPLACE: нет необходимости создавать временную таблицу
  • ПО УМОЛЧАНИЮ: В соответствии с параметромold_alter_tableоценка параметра, если даOFF,использоватьINPLACEПуть

LOCK указывает, что к таблице добавлена ​​​​блокировка

  • NONE: не добавлять никаких замков
  • SHARE: добавьте блокировку S, можно выполнять одновременное чтение, а операции записи должны ждать
  • ЭКСКЛЮЗИВ: добавьте блокировку X, чтение и запись не могут выполняться одновременно
  • ПО УМОЛЧАНИЮ: сначала определите, можно ли его использоватьNONE, если нет, определить, можно ли его использоватьSHARE, если нет, то судить можно ли его использоватьEXCLUSIVEмодель.

2 Использование индексов B-дерева

2.1 Совместный индекс

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

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

create table t_index(
a char(2) not null default '',
b char(2) not null default '',
c char(2) not null default '',
d char(2) not null default ''
)engine myisam charset utf8;

Создать совместный индекс

alter table t_index add index abcd(a,b,c,d);

Вставьте несколько фрагментов тестовых данных

insert into t_index values('a','b','c','d'),
('a2','b2','c2','d2'),
('a3','b3','c3','d3'),
('a4','b4','c4','d4'),
('a5','b5','c5','d5'),
('a6','b6','c6','d6');

На данный момент мы в основном подготовили необходимые данные и можем продолжить обсуждение совместной индексации.

Когда нам нужно создать федеративный индекс?

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

Когда в игру вступает федеративный индекс?

Иногда мы будем использовать совместный индекс, однако мы не знаем его принципов, мы не знаем, когда он будет играть роль в совместном индексе, когда они потерпят неудачу?

С этим вопросом давайте взглянем на совместный индексКрайний левый принцип соответствия.

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

Ниже мы рассмотрим этот принцип на нескольких примерах.

EXPLAIN SELECT * FROM t_index WHERE a = 'a' \G;

Мы посмотрим на это утверждение результатов, прежде всего, мы видим использование индекса, потому чтоУсловие запроса имеет крайний левый столбец a, то сколько индексов используется? Это нам нужно увидетьkey_lenЭто поле, мы знаем, что кодирование персонажа UTF8 три байта, а тип данных, который мы используем, этоchar(2), занимая два байта, индекс 2*3 равен 6 байтам, поэтому роль играет только один индекс.

EXPLAIN SELECT * FROM t_index WHERE b = 'b2' \G;

Из этого оператора видно, что он не использует индекс, потому чтоpossible_keysпусто, а количество строк из запросаrowsВидно, что это 6 (всего у нас 6 тестовых данных), что указывает на то, что было выполнено полное сканирование, что указывает на то, что эта ситуация не соответствуетКрайний левый принцип соответствия, поэтому индексный запрос использоваться не будет.

EXPLAIN SELECT * FROM t_index WHERE a = 'a2' AND b = 'b2' ORDER BY d \G;

Эта ситуация немного отличается, мы используем сортировку, мы видим, что используется индекс, поkey_lenКак 12 могут привыкнуть 2 индексаa、bКроме того, вы можете видеть в дополнительных параметрахUsing filesort, то есть файловая сортировка.Причина использования файловой сортировки здесь следующая: в приведенном выше запросе используются индексы a и b, но когда мы используем поле d для сортировки, (a, d) или (b, d) эти два Индекс не отсортирован,Использование объединенного указателя имеет то преимущество, что следующее поле указателя будет автоматически отсортировано., в этом случае поле c отсортировано, а d — нет, если мы будем использовать c для сортировки, то получим разные результаты.

EXPLAIN SELECT * FROM t_index WHERE a = 'a2' AND b = 'b2' ORDER BY c \G;

Видите ли вы, что когда мы используем c для сортировки, поскольку используются индексы a и b, c автоматически сортируется, поэтому нет необходимости в файловой сортировке.

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

Q1: Почему бы не создать индекс для каждого столбца в таблице?

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

Q2: Почему вам нужно использовать совместный индекс

уменьшить накладные расходы.建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

индекс покрытия. Для общего индекса (col1,col2,col3), если есть следующий sql: выберите col1,col2,col3 из теста, где col1=1 и col2=2. Затем MySQL может напрямую получать данные, обходя индекс, не возвращаясь к таблице, что сокращает количество случайных операций ввода-вывода. Сокращение операций ввода-вывода, специальный случайный ввод-вывод на самом деле является основной стратегией оптимизации dba. Поэтому в реальных практических приложениях покрывающий индекс является одним из основных методов оптимизации для повышения производительности.

эффективный. Чем больше столбцов индекса, тем меньше данных отфильтровывается индексом. Таблица с фрагментами данных 1000 Вт имеет следующий sql: выберите из таблицы, где col1=1 и col2=2 и col3=3, предполагая, что каждое условие может отфильтровать 10% данных, если есть только однозначный индекс , затем передать индекс Он может отфильтровать 1000W10%=100w фрагментов данных, а затем вернуться к таблице, чтобы найти данные, которые соответствуют col2=2 и col3=3 из 100w фрагментов данных, затем отсортировать и разбить на страницы; если это является совместным индексом, отфильтруйте 1000w10% 10 через индекс % *10%=1w, повышение эффективности можно себе представить!

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

explain select count(*) from t_index \G;

Если это движок Myisam, дополнительный столбец выведетSelect tables optimized awayЗаявление, MyIsam Engine сохранил общее количество записей напрямую возвращает результат, вам не нужно оптимизировать индекс покрытия.

Если движок InnoDB, вывод дополнительного столбцаUsing indexоператор, указывающий, что оптимизатор механизма InnoDB использует операцию покрывающего индекса.

2.2 Подсказки указателя

База данных MySQL поддерживает функцию подсказки индекса. Функция подсказки индекса сообщает оптимизатору, какой индекс использовать. Как правило, функция подсказки индекса (INDEX HINT) может использоваться в следующих двух ситуациях:

  • Оптимизатор базы данных MySQL неправильно выбирает индекс, из-за чего SQL работает очень медленно.
  • Существует множество индексов, которые можно выбрать для оператора SQL, и в этом случае затраты оптимизатора на выбор времени плана выполнения могут быть больше, чем затраты на сам оператор SQL.

Здесь мы продолжаем приведенный выше пример, чтобы объяснить, прежде всего, мы сначала для вышесказанногоt_indexДобавить несколько индексов к таблице;

alter table t_index add index a (a);
alter table t_index add index b (b);
alter table t_index add index c (c);

Далее мы выполняем следующий оператор;

EXPLAIN SELECT * FROM t_index WHERE a = 'a' AND b = 'b' AND c = 'c' \G;

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

EXPLAIN SELECT * FROM t_index USE INDEX(a) WHERE a = 'a' AND b = 'b' AND c = 'c' \G;

Это показывает индекс использования A, если этот метод иногда оптимизатор все еще не выбрал нужный индекс, то мы можем другой способFORCE INDEX.

EXPLAIN SELECT * FROM t_index FORCE INDEX(a) WHERE a = 'a' AND b = 'b' AND c = 'c' \G;

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

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

Многодиапазонная оптимизация чтения

Начал поддерживать MySQL 5.6, цель данной оптимизации уменьшить произвольный доступ к диску, и преобразовать случайный доступ в более последовательный доступ к данным, данная оптимизация подходит для запросов типа range, ref, eq_ref.

Преимущества оптимизации многодиапазонного считывания:

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

Мы можем использовать параметрыoptimizer_switchФлаг, определяющий, следует ли включать оптимизацию многодиапазонного чтения. Следующие методы будут включены всегда:

SET @@optimizer_switch='mrr=on,mrr_cost_based=off';
Индекс Оптимизация условия Plushdown (ICP)

Этот метод оптимизации также поддерживается начиная с MySQL 5.6.До того, как этот метод не поддерживается, при выполнении индексного запроса мы сначала ищем записи по индексу, а затем фильтруем записи по условию where. Однако, если оптимизация ICP поддерживается, база данных MySQL будет определять, может ли выполняться фильтрация по условию «где» при извлечении индекса, т. SQL верхнего уровня.

ICP поддерживает типы запросов range, ref, eq_ref, ref_or_null, в настоящее время поддерживает механизмы хранения MyISAM и InnoDB.

Мы можем включить ICP с помощью следующего оператора:

set @@optimizer_switch = "index_condition_pushdown=on"

или выключено:

set @@optimizer_switch = "index_condition_pushdown=off"

Когда ICP включен, вы можете увидеть это в плане выполнения ExtraUsing index conditionнамекать.

3 Индексные характеристики, преимущества, недостатки и применимые сцены

Функции указателя

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

Преимущества индексации

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

Недостатки индексации

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

Применимые сценарии для индексов

  • соответствует полной стоимости

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

  • Запрос диапазона для сопоставления значений

Поиск диапазона может быть выполнен по значению индекса.

  • соответствует крайнему левому префиксу

Используя только левый столбец в индексовом запросе, таких как индекс совместного набора на полях COL1 COL2 COL3, может быть включен COL1 (COL1 COL2), (COL1 COL2 COL3), эквивалентный с использованием запроса, но не может быть Col2 (COL2 , COL3) эквивалентно использовать запрос. Лежи в принципе сопоставления можно рассматривать как первые принципы MySQL в использовании B-Tree Index.

  • запрашивать только индекс

Когда все столбцы запроса находятся в проиндексированных полях, эффективность запроса выше, поэтому вам следует по возможности избегать использования select * и проверять только те поля, которые необходимы.

  • соответствие префиксу столбца

Поиск выполняется с использованием только первого столбца индекса и только первой части первого столбца индекса.

  • Это обеспечивает точное сопоставление индекса, часть которого соответствует диапазону, а другая часть
  • Если имя столбца является индексом, индекс будет использоваться с использованием значения Column_name NULL, например:
explain select * from t_index where a is null \G
  • Поля, которые часто появляются после ключевых слов, упорядочивают, группируют, различают
  • Поля результирующего набора операций над наборами, такие как объединение
  • Поля, часто используемые для объединения таблиц
  • Рассмотрите возможность использования индексного покрытия, данные редко обновляются. Если пользователи часто запрашивают несколько ваших полей, вы можете рассмотреть возможность создания индекса для этих полей, тем самым превратив сканирование таблицы в сканирование индекса.

сбой индекса

  • Подобный запрос, начинающийся с %, не может использовать индекс B-дерева.Значение ключа в плане выполнения равно нулю, что указывает на то, что индекс не используется.
  • Индекс не используется, когда тип данных имеет неявное преобразование, например,where 'age' 10=30
  • Выполнять функциональные операции над индексированными столбцами по той же причине, что и выше.
  • Regex не использует индекс
  • При сравнении строк и данных индексы не используются.
  • В случае составного индекса, если условие запроса не включает крайнюю левую часть столбца индекса, то есть принцип крайнего левого не выполняется, составной индекс не будет использоваться.
  • Не используйте индекс, если MySQL считает, что использование индекса медленнее, чем полное сканирование таблицы.
  • Для условий, разделенных или, если столбец в условии перед или имеет индекс, но в следующем столбце индекса нет, то задействованный индекс не будет использоваться.
  • Используйте отрицательные запросы (not , not in, not like , ,!= ,!> ,!

Справочная статья

Если в статье есть что-то неуместное, поправьте меня, если вам нравится читать в WeChat, вы также можете подписаться на меня.Публичный аккаунт WeChat:好好学java, доступ к высококачественным учебным ресурсам.

Категории