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

индексная модель

Существует три основные модели индексации: пары ключ-значение, упорядоченные массивы и деревья поиска.

пара ключ-значение

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

Характерной чертой пар ключ-значение является то, что запрос данных выполняется очень быстро, но могут выполняться только запросы с равными значениями, а запросы диапазона не поддерживаются. Метод пары ключ-значение широко используется в NoSQL, например в Redis.

отсортированный массив

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

Данные запроса упорядоченного массива могут напрямую использовать метод деления пополам, поэтому временная сложность составляет O (logN). А отсортированные массивы могут быть очень удобны для запросов диапазона. Проблема с упорядоченными массивами заключается в том, что для сохранения упорядоченности массива очень дорого перемещать все данные за позицию вставки при вставке. Поэтому упорядоченные массивы подходят для индексации статических данных, статические данные создаются один раз, и нет необходимости вставлять их снова.

дерево поиска

Двоичное дерево поиска также упорядочено, левый узел меньше родительского узла, а родительский узел меньше правого узла. Сложность запроса бинарного дерева поиска также равна o(logN), а сложность обновления также равна o(logN).Конечно, чтобы сохранить сложность o(logN), бинарное дерево необходимо сбалансировать.

Но когда количество данных очень велико, высота бинарного дерева поиска будет очень большой, что увеличивает время поиска. Например, для сбалансированного бинарного дерева с миллионом узлов высота дерева равна 20. Предположим, что для случайного чтения части данных с диска требуется 10 мс, тогда простой запрос занимает 200 мс, что явно неприемлемо.

Чтобы уменьшить высоту бинарного дерева, было придумано N-арное дерево поиска, и родительский узел может иметь N дочерних узлов. Для N-арного дерева поиска с миллионом узлов, когда N равно 100, высота дерева уменьшилась до 3.

Индексная модель InnoDB

Кластеризованные и некластеризованные индексы

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

Затем мы сравниваем характеристики хранения кластеризованных и некластеризованных индексов. Прямая ссылка на картинку в книге "High Performance MySQL" по сравнению кластеризованных и некластеризованных индексов. Слева — кластеризованный индекс, индекс первичного ключа и вторичный индекс, а справа — индекс первичного ключа и вторичный индекс некластеризованного индекса.

Особенности кластерных и некластеризованных индексов:

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

Кластерные индексы имеют ряд преимуществ:

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

Кластерные индексы имеют некоторые недостатки:

Скорость вставки сильно зависит от порядка вставки. Вставки могут быть быстрыми, если они выполняются в порядке первичного ключа. Разделение страниц может произойти, если вставки не в порядке первичного ключа. Разделение страниц не только влияет на скорость вставки, но и исходная страница теперь разделена на две страницы для хранения, и две страницы не заполнены, занимая больше места на диске. Вот почему администраторы баз данных всегда рекомендуют использовать автоматически увеличивающийся идентификатор в качестве первичного ключа при использовании механизма InnoDB;
Обновление кластеризованного индекса обходится дорого, поскольку каждая обновленная строка перемещается в новое место;
Во вторичном индексе необходимо выполнить поиск дважды, поскольку значение первичного ключа хранится во вторичном индексе, и требуется еще одна операция возврата таблицы;
Значение первичного ключа хранится во вторичном индексе, из-за чего вторичный индекс может занимать больше места;

Индексная модель InnoDB

InnoDB стал механизмом хранения MySQL по умолчанию, начиная с версии MySQL 5.5 Давайте проанализируем индексную модель InnoDB. InnoDB использует модель индекса дерева B+, а индекс первичного ключа InnoDB представляет собой кластеризованный индекс.

Что такое дерево B+? Каковы преимущества дерева B+ перед деревом B? Как дерево B+ вставляет и удаляет данные?

По указанным выше вопросам обращайтесь к этому блогу, который очень прост и понятен:Подробное объяснение вставки и удаления B-деревьев и B+-деревьев

Подсистема InnDB использует дерево B+ в качестве модели индекса.Для дерева B+ m-порядка временная сложность эквивалентного запроса равна logm(N), а данные дерева B+ находятся на листовых узлах. Листовые узлы дерева B+ имеют указатели на следующие конечные узлы, поэтому запросы диапазона также выполняются очень быстро.

Чтобы поддерживать упорядоченность кластеризованного индекса, вставки InnoDB в порядке первичного ключа не вызовут разделения страниц. При случайной вставке легко вызвать разделение страниц. Поэтому идентификатор автоинкремента обычно выбирается в качестве первичного ключа. Следующие схемы обычно используются в операторах построения таблиц.id BIGINT UNSIGNED NOT NULL PRIMERY KEY AUTO_INCREMENT

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

Особенности индексов MySQL

форма возврата

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

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

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

Предположим, что таблица создана следующим образом:

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `a` varchar(32) DEFAULT NULL,
  `b` varchar(32) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a_b` (`a, b`),
) ENGINE=InnoDB

В дополнение к установлению индекса первичного ключа для id в этой таблице также перечисленыaиbСовместный вторичный индекс устанавливается наa_b. При использовании условия wherewhere a = 'xxx' and b = 'yyy', вы можете использовать индексa_b, когда условие гдеwhere a = 'xxx'Индекс также можно использовать, когда единственное условие wherewhere b = 'yyy'нельзя использовать индекс. Это принцип самого левого префикса.

Еще одно замечание о совместном индексе и принципе крайнего левого префикса:Как нечеткие, так и диапазонные запросы сделают недействительными столбцы после столбца запроса в объединенном индексе..

Например: для совместного индекса a_b в таблице t при наличии запросаselect * from t where a like 'test%' and b = 'luck', приведет к тому, что объединенный индекс будет использовать только столбец a для попадания, а столбец b завершится ошибкой, поскольку в столбце a уже есть нечеткий запрос.

выталкивание индекса

select * from t where a like 'test%' and b = 'luck';

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

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

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

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

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

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

Приоритет отдается тем, у кого наивысшая степень дифференциации. Например, пользовательская таблица глобального населения имеет такие поля, как пол, национальность и возраст. В нормальных условиях степень различия по национальности выше, чем по полу, например, людей, отвечающих требованиям китайцев, меньше, чем тех, которые соответствуют требованиям мужчин. Таким образом, национальность имеет приоритет перед полом при установлении общего индекса.
Перечислимые значения имеют приоритет. Сейчас это все еще таблица пользователей. Предположим, мы установили совместный индекс key_a (национальность, возраст, пол). Если я хочу найти китайских пользователей-мужчин, этот совместный индекс не будет работать. Но если мы установим совместный индекс key_b (пол, национальность, возраст), в данном случае мы хотим найти 18-летних пользователей в Китае, можно ли еще использовать этот индекс? Конечно, мы можем указать IN (мужской, женский) через условие IN при запросе. Таким образом, принцип самого левого префикса может быть удовлетворен.

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

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

Вы хотите использовать уникальный индекс

Для запроса после того, как обычный индекс достигает первой записи, он продолжает поиск, в то время как уникальный индекс может вернуться немедленно. Однако дисковая память хранится в виде подкачки.Наименьшая подкачка 4К.При чтении первой записи велика вероятность, что остальные данные тоже в подкачке 4К, а подкачка 4К загружена в память. . Таким образом, влияние двух запросов на производительность не сильно отличается.

Для записи обычные вставки данных индекса записываются, чтобы сначала изменить buf, чтобы ускорить операции записи. Однако для обеспечения уникальности уникальный индекс не может использовать change buf.Уникальный индекс должен сначала проверить, существуют ли уже такие же данные индекса, и вставить их, если они не существуют.

Если бизнес может гарантировать уникальность, попробуйте использовать общий индекс, если бизнес не может гарантировать уникальность, рассмотрите возможность использования уникального индекса.

Вы хотите использовать UUID в качестве первичного ключа?

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

Вот как я понимаю это понимание:

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

индекс покрытия

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

на пользовательском столе

CREATE TABLE `user` (
  `id` int(11) NOT NULL,
  `id_card` varchar(32) DEFAULT NULL,
  `name` varchar(32) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `ismale` tinyint(1) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `id_card` (`id_card`),
  KEY `name_age` (`name`,`age`)
) ENGINE=InnoDB

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

длина управляющего индекса

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

Существует несколько способов управления длиной индекса:

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

индекс префикса

Если индекс слишком длинный, он раздуется, и префиксный индекс используется для уменьшения нагрузки на индекс.

CREATE TABLE User(
  ID bigint unsigned primary key,
  email varchar(64), 
  ... 
) engine=innodb;

Есть такая пользовательская таблица, использующая почтовый ящик в качестве регистрационного имени. Предположим, есть такая группа пользователей,abcdii@163.com, abcdrr@163.com, abcdoo@163.com, abcdss@163.com. В бизнес-требованиях велика вероятность найти информацию о пользователе по имени пользователя, поэтому необходимо добавить индекс к имени пользователя. Самый простой способ — создать общий индексный ключ (электронная почта) напрямую или использовать префиксный индексный ключ (электронная почта (4)).

Если установлен обычный индекс, длина индекса равна 10, а если установлен префиксный индекс, длина индекса равна 4. Предположим, вы хотите запросить данные abcdoo@163.com для общего индекса, сначала найдите строку abcdoo@163.com, вернитесь к таблице, чтобы получить данные, а затем продолжите поиск по индексу и обнаруживает, что адрес abcdss@163.com больше не соответствует условиям , поэтому поиск завершается. Таблица возвращается только один раз, поэтому система определяет, что найдена только 1 строка.

Для индекса префикса его необходимо искать четыре раза, каждый из которых должен быть возвращен в табличную операцию, чтобы подтвердить, является ли он искомым объектом, потому что условие может быть выполнено, когда длина индекса равна 4. Учтите, что если индекс префикса принимает 5 ключей длины (email(5)), то можно выполнить только один поиск. Это показывает, что длину индекса можно уменьшить при условии, что префиксный индекс может поддерживать хорошее разделение.

Так как же выбрать длину индекса префикса?

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

SELECT count(distinct email) as C FROM User;

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

SELECT count(distinct left(email, 4) as C4 FROM User;
SELECT count(distinct left(email, 5) as C5 FROM User;

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

Как бороться с большим количеством повторений перед струной

Если префиксная часть строки, например идентификационный номер, часто повторяется. Есть два решения,

Один из них - хранить строку вверх ногами, а при запросе переворачивать строку, а затем запрашивать

SELECT * FROM t WHERE id_card = reverse('input_id_card');

Во-вторых, вычисление значения HASH через строку, и индекс строится на значении HASH. Однако разные идентификаторы также могут иметь одинаковое значение хеш-функции, поэтому при запросе необходимо точно определить идентификационный номер.id_card_crcПоле хеш-значения после вычисления crc32 для ID. После вычисления хеша индекс занимает всего 4 байта.

SELECT * FROM t WHERE id_card_crc = crc32('input_id_card') and id_card='input_id_card'

Оба эти метода приведут к сбою запроса диапазона, о чем следует знать.