предисловие
Я уже написал статью длиной в две главы.Для третьей статьи я думал о том, что написать (на самом деле, я не стал).По карте мозга мы должны говорить о знании индексации в Эта статья Оптимизация производительности MySQL является очень важной точкой знаний, не пропустите ее, но я рассмотрю ее относительно подробно здесь, и я полагаю, что вы немного почерпнете после ее прочтения.
Сначала отправьте два авиабилета 🛬 Партнеры, которые не читали предыдущую статью, могут перейти к ее прочтению первыми, от мелкого к более глубокому:
Поскольку в указателе много знаний, на официальном сайте также много контента. Если вы хотите узнать об этом больше, вы можете перейти на официальный сайт. Если вы хотите сначала прочитать его, вы можете сначала прочтите мое краткое изложение предметного указателя.Эта глава разделена на три части:
- Логическую структуру хранения innodb нужно понимать как побочную историю.
Связано с MySQL (дополнительная статья) - логическая структура хранения innodb;
- Структура данных индекса также используется в качестве еще одной главы посредством вычислительного анализа модели данных алгоритма запроса.
Связано с MySQL (3) - вывод модели данных индекса и подробное введение в B + Tree;
- Использование индексов и правил оптимизации также будет рассматриваться как отдельные главы.
Связано с MySQL (четыре) - индекс ключевых точек оптимизации производительности
Упомянутая выше карта мозга выглядит следующим образом.
текст
Что такое индекс?
Определение индекса
- Давайте сначала посмотрим на определение индекса в Википедии:
Индекс базы данных — это отсортированная структура данных в системе управления базами данных (СУБД), помогающая быстро запрашивать и обновлять данные в таблицах базы данных.
- Итак, как мы понимаем это определение?
Во-первых, данные хранятся на диске в виде файла, и каждая строка данных имеет свой дисковый адрес. Если индекса нет, чтобы получить часть данных из 5 миллионов строк данных, вы можете только просмотреть все данные в этой таблице по очереди, пока не найдете эту часть данных. Но после того, как у нас есть индекс, нам нужно только получить эти данные в индексе, потому что это специальная структура данных для быстрого поиска.После того, как мы найдем адрес диска, где хранятся данные, мы можем получить данные. Точно так же, как мы ищем содержание того или иного раздела в 500-страничной книге, точно невозможно начать с первой страницы. Затем эта книга имеет специальное оглавление. В ней может быть только несколько страниц содержания. Она организована по номерам страниц и может быть найдена в соответствии с пиньинь или радикалами. Пока вы определяете номер страницы, соответствующий содержанию, вы можем быстро найти нужный нам контент.
Виды и методы индексации
Тип индекса: обычный, уникальный, полнотекстовый
как создать индекс
В InnoDB существует три типа индексов: общий индекс, уникальный индекс (индекс первичного ключа — это специальный уникальный индекс) и полнотекстовый индекс.
Обычный (Normal): Также называемый неуникальным индексом, это самый распространенный индекс без каких-либо ограничений.
Уникальный (Unique): Уникальный индекс требует, чтобы значения ключей не могли повторяться. Кроме того, следует отметить, что индекс первичного ключа является специальным уникальным индексом и имеет дополнительное ограничение, требующее, чтобы значение ключа не могло быть пустым. Индексы первичного ключа создаются с помощью первичного ключа.
Fulltext: Для относительно больших данных, например, мы храним содержимое сообщений, а данных несколько КБ, если вы хотите решить проблему низкой эффективности подобного запроса, вы можете создать полнотекстовый индекс. Полнотекстовые индексы можно создавать только для полей текстового типа, таких как char, varchar и text.
Создайте индекс с помощью командной строки следующим образом:
create table m3 (
name varchar(50),
fulltext index(name)
);
Использование полнотекстового индексирования
SELECT
*
FROM
fulltext_test
WHERE
MATCH(content) against('6曦轩' IN NATURAL LANGUAGE MODE);
MyISAM и InnoDB поддерживают полнотекстовое индексирование. Существует три типа указателей: общие, уникальные и полнотекстовые.
Мы говорим, что индекс — это структура данных, так какую же структуру данных следует выбрать для эффективной индексации данных? Давайте продолжим чтение.
В этой главе мы используем некоторые структуры данных, чтобы пошагово вычислить, почему MySQL использует B + дерево в качестве структуры данных индекса, и подробное введение в B + дерево, Длина длинная, и мы будем использовать другую главу для описания:
Авиабилет 🛬:Вывод модели данных индекса MySQL
Что такое кластеризованный индекс (clustered index)?
Кластеризованный индекс означает, что логический порядок значений ключа индекса согласуется с физическим порядком хранения строк данных таблицы. (Например, каталог словаря отсортирован по пиньинь, и содержимое также отсортировано по пиньину. Такой каталог, отсортированный по пиньинь, называется кластерным индексом).
В InnoDB способ организации данных называется (кластеризованной) таблицей организации индекса, поэтому индекс первичного ключа является кластеризованным индексом, а непервичный ключ — некластеризованным индексом.
Если первичный ключ в InnoDB хранится таким образом, как индекс, отличный от первичного ключа, такой как обычный индекс, который мы строим на поле имени, хранит и извлекает данные?
Вторичные индексы хранят значения вторичного индекса и первичного ключа. Если вы используете запрос вторичного индекса, индекс первичного ключа будет запрошен в соответствии со значением первичного ключа, и данные будут окончательно получены.
Например, мы используем индекс имени для запроса name='Jack', он найдет значение первичного ключа в конечном узле, то есть id=1, а затем перейдет к конечному узлу индекса первичного ключа, чтобы получить данные.
- Почему значение первичного ключа хранится во вторичном индексе, а не в дисковом адресе первичного ключа? Если тип данных первичного ключа относительно велик, занимает ли он больше места, чем адрес хранилища? Как мы упоминали ранее, как B Tree реализует узел для хранения нескольких ключевых слов и поддержания баланса?
Это связано с тем, что есть операции форка и слияния, адрес значения ключа в это время изменится, поэтому адрес не может быть сохранен во вспомогательном индексе.
- Другой вопрос, а что если у таблицы нет первичного ключа?
- Если мы определим первичный ключ (PRIMARY KEY), то InnoDB выберет первичный ключ в качестве кластеризованного индекса.
- Если первичный ключ явно не определен, InnoDB выбирает первый уникальный индекс, который не содержит значений NULL, в качестве индекса первичного ключа.
- Если такого уникального индекса также нет, InnoDB выбирает встроенный 6-байтовый длинный ROWID в качестве скрытого кластеризованного индекса, который увеличивает первичный ключ по мере записи строки.
select _rowid name from t2;
Принципы использования индекса
Мы склонны неправильно понимать, то есть строить индексы по часто используемым условиям запроса. Чем больше индексов, тем лучше. Это правда?
Дискретная (sàn) степень столбца
Первый из них называется степенью дисперсности столбца.Давайте сначала посмотрим на следующую формулу степени дисперсии: count(distinct(column_name)) : count(*), соотношение всех различных значений столбца и всех строк данных.
Когда количество строк данных одинаково, чем больше числитель, тем выше дисперсия столбцов.
Проще говоря, если в столбце больше повторяющихся значений, дисперсия будет ниже, а чем меньше повторяющихся значений, тем выше дисперсия.
Разобравшись с концепцией дисперсии, давайте подумаем над другим вопросом, в чем разница между индексацией по имени и индексацией по полу.
Когда мы используем индекс, построенный на основе пола, для извлечения данных из-за слишком большого количества повторяющихся значений нам нужно сканировать больше строк. Например, давайте теперь создадим индекс для столбца «гендер» и посмотрим на план выполнения.
ALTER TABLE user_innodb DROP INDEX idx_user_gender;
ALTER TABLE user_innodb ADD INDEX idx_user_gender (gender); -- 耗时比较久
EXPLAIN SELECT * FROM `user_innodb` WHERE gender = 0;
show indexes from user_innodb;
И степень дисперсии имени выше, например, имя «Валет», нужно сканировать только одну строку.
ALTER TABLE user_innodb DROP INDEX idx_user_name;
ALTER TABLE user_innodb ADD INDEX idx_user_name (name);
EXPLAIN SELECT * FROM `user_innodb` WHERE name = 'Jack';
Глядя на индекс в таблице, количество элементов [kɑ:dɪ'nælɪtɪ] представляет собой количество элементов, которое представляет собой предполагаемое уникальное значение. количество. Чем ближе кардинальность индекса к общему количеству строк в таблице, тем выше дисперсия столбцов.
show indexes from user_innodb;
Если в B+Tree слишком много повторяющихся значений, оптимизатор MySQL обнаруживает, что использование индекса мало чем отличается от использования полного сканирования таблицы, даже если индекс построен, индекс может не использоваться.
Ууууу, в это время Джейд А. Квота из США/~Карри Лакса/виз…
- Что это вдохновляет нас?
Для построения индекса используйте поле с более высокой степенью дисперсии (избирательности).
Крайнее левое совпадение индекса союза
Ранее мы говорили об индексах, созданных для одного столбца, но иногда, когда мы запрашиваем несколько условий, также будет установлен совместный индекс. Индекс с одним столбцом можно рассматривать как специальный совместный индекс.
Например, мы установили совместный индекс для имени и телефона в пользовательской таблице.
ALTER TABLE user_innodb DROP INDEX comidx_name_phone;
ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);
Совместный индекс представляет собой составную структуру данных в B+Tree, которая строит дерево поиска в порядке слева направо (имя слева, телефон справа).
Как видно из этой картинки, имя упорядочено, а телефон не упорядочен. Телефоны заказываются только при совпадении имен.
В настоящее время, когда мы используем where name= 'Qingshan' и phone = '136xx' для запроса данных, B+Tree сначала сравнивает имя, чтобы определить направление, которое следует искать на следующем шаге, влево или вправо. Если названия совпадают, то сравните телефон. Но если условие запроса не имеет имени, вы не знаете, какой узел проверять на первом шаге, потому что имя является первым фактором сравнения при построении дерева поиска, поэтому индекс не используется.
Когда использовать федеративный индекс
Поэтому, когда мы создаем совместный индекс, мы должны поместить наиболее часто используемые столбцы в крайнее левое положение.
Например, могут ли следующие три утверждения использовать объединенный индекс?
1) Используя два поля, можно использовать совместный индекс:
EXPLAIN SELECT * FROM user_innodb WHERE name= '权亮' AND phone = '15204661800';
EXPLAIN SELECT * FROM user_innodb WHERE name= '权亮'
EXPLAIN SELECT * FROM user_innodb WHERE phone = '15204661800'
Как создать федеративный индекс
Однажды наш администратор базы данных пришел ко мне и сказал, что у нас есть два медленных запроса в нашем проекте.
SELECT * FROM user_innodb WHERE name= ? AND phone = ?; SELECT * FROM user_innodb WHERE name= ?;
Согласно нашей идее, один запрос создает один индекс, поэтому мы создаем два индекса для этих двух SQL-запросов, как вы считаете, это правильно?
CREATE INDEX idx_name on user_innodb(name);
CREATE INDEX idx_name_phone on user_innodb(name,phone);
Когда мы создаем совместный индекс, в соответствии с принципом самого левого сопоставления, используйте имя поля слева для запроса , можно использовать и индекс, поэтому первый индекс совершенно не нужен.
Равнозначно установлению двух совместных индексов (имя), (имя, телефон).
Если мы создадим индекс из трех полей index(a,b,c), это эквивалентно созданию трех индексов:
index(a) index(a,b) index(a,b,c)
Используя where b=?, и where b=?, и c=?, и where a=?, и c=?, нельзя использовать индексы. Не могу использовать первое поле, не может сломаться.
Это самый левый принцип сопоставления объединенного индекса MySQL.
индекс покрытия
Форма возврата:
Для индекса без первичного ключа мы сначала находим значение ключа индекса первичного ключа через индекс, а затем узнаем данные, которых нет в индексе через значение первичного ключа.Он сканирует на одно дерево индекса больше, чем на основе запроса по индексу первичного ключа.Этот процесс называется таблицей возврата.
Например: выберите * из user_innodb, где имя = 'Джек';
Во вспомогательном индексе, будь то индекс с одним столбцом или совместный индекс, если столбец данных выбора может быть получен только из индекса, без чтения из области данных, индекс, используемый в это время, называется покрытием index, который позволяет избежать возврата поверхности.
Давайте сначала создадим совместный индекс:
--创建联合索引
ALTER TABLE user_innodb DROP INDEX comixd_name_phone;
ALTER TABLE user_innodb add INDEX `comixd_name_phone` (`name`,`phone`);
Все эти три оператора запроса используют покрывающий индекс:
EXPLAIN SELECT name,phone FROM user_innodb WHERE name= '青山' AND phone = ' 13666666666';
EXPLAIN SELECT name FROM user_innodb WHERE name= '青山' AND phone = ' 13666666666';
EXPLAIN SELECT phone FROM user_innodb WHERE name= '青山' AND phone = ' 13666666666';
Значение «Использование индекса» в «Дополнительно» указывает на то, что используется покрывающий индекс.
Очевидно, поскольку покрывающий индекс уменьшает количество операций ввода-вывода и объем доступа к данным, эффективность запросов может быть значительно повышена.
Проталкивание условия индекса (ICP)
Давайте еще раз посмотрим на такую таблицу и создадим совместный индекс для last_name и first_name.
drop table employees;
CREATE TABLE `employees`(
`emp_no` INT(11) NOT NULL ,
`birth_date` date NULL ,
`first_name` VARCHAR(14) NOT NULL ,
`last_name` VARCHAR(16) NOT NULL ,
`gender` ENUM('M' , 'F') NOT NULL ,
`hire_date` date NULL ,
PRIMARY KEY(`emp_no`)
) ENGINE = INNODB DEFAULT CHARSET = latin1;
ALTER TABLE employees ADD INDEX idx_lastname_firstname(last_name , first_name);
INSERT INTO `employees`(
`emp_no` ,
`birth_date` ,
`first_name` ,
`last_name` ,
`gender` ,
`hire_date`
)
VALUES
(1 , NULL , '698' , 'liu' , 'F' , NULL);
INSERT INTO `employees`(
`emp_no` ,
`birth_date` ,
`first_name` ,
`last_name` ,
`gender` ,
`hire_date`
)
VALUES
(2 , NULL , 'd99' , 'zheng' , 'F' , NULL);
INSERT INTO `employees`(
`emp_no` ,
`birth_date` ,
`first_name` ,
`last_name` ,
`gender` ,
`hire_date`
)
VALUES
(3 , NULL , 'e08' , 'huang' , 'F' , NULL);
INSERT INTO `employees`(
`emp_no` ,
`birth_date` ,
`first_name` ,
`last_name` ,
`gender` ,
`hire_date`
)
VALUES
(4 , NULL , '59d' , 'lu' , 'F' , NULL);
INSERT INTO `employees`(
`emp_no` ,
`birth_date` ,
`first_name` ,
`last_name` ,
`gender` ,
`hire_date`
)
VALUES
(5 , NULL , '0dc' , 'yu' , 'F' , NULL);
INSERT INTO `employees`(
`emp_no` ,
`birth_date` ,
`first_name` ,
`last_name` ,
`gender` ,
`hire_date`
)
VALUES
(6 , NULL , '989' , 'wang' , 'F' , NULL);
INSERT INTO `employees`(
`emp_no` ,
`birth_date` ,
`first_name` ,
`last_name` ,
`gender` ,
`hire_date`
)
VALUES
(7 , NULL , 'e38' , 'wang' , 'F' , NULL);
INSERT INTO `employees`(
`emp_no` ,
`birth_date` ,
`first_name` ,
`last_name` ,
`gender` ,
`hire_date`
)
VALUES
(8 , NULL , '0zi' , 'wang' , 'F' , NULL);
INSERT INTO `employees`(
`emp_no` ,
`birth_date` ,
`first_name` ,
`last_name` ,
`gender` ,
`hire_date`
)
VALUES
(9 , NULL , 'dc9' , 'xie' , 'F' , NULL);
INSERT INTO `employees`(
`emp_no` ,
`birth_date` ,
`first_name` ,
`last_name` ,
`gender` ,
`hire_date`
)
VALUES
(10 , NULL , '5ba' , 'zhou' , 'F' , NULL);
Отключить ИКП:
set optimizer_switch='index_condition_pushdown=off';
Параметры просмотра:
show variables like 'optimizer_switch';
Теперь мы хотим запросить всех сотрудников с фамилией Ван, а последний символ имени — зи, например, Ван Фатзи и Ван Шоузи. Запрос SQL:
select * from employees where last_name='wang' and first_name LIKE '%zi' ;
- Этот SQL может быть выполнен двумя способами:
- В соответствии с совместным индексом найдите все данные вторичного индекса фамилии Ван, а затем вернитесь к таблице, чтобы запросить все квалифицированные данные (3 фрагмента данных) в индексе первичного ключа. Затем вернитесь на уровень сервера и отфильтруйте сотрудников, чьи имена заканчиваются на zi на уровне сервера.
- Узнайте все данные вторичного индекса (3 индекса) фамилии ванг по объединенному индексу, затем отфильтруйте индекс, чье first_name заканчивается на zi (1 индекс) из вторичного индекса, а затем вернитесь к таблице, чтобы запросить все Квалифицированные данные индекса первичного ключа (1 часть данных) возвращаются на уровень сервера.
Очевидно, что второй способ запроса данных по индексу первичного ключа меньше. Обратите внимание, что сравнение индексов выполняется в механизме хранения, а сравнение записей данных — на уровне сервера. Когда условие first_name нельзя использовать для фильтрации индекса, уровень сервера не передаст условие first_name механизму хранения, поэтому считываются две ненужные записи. В настоящее время, если есть 100 000 записей, удовлетворяющих условиям last_name='wang', будет 99 999 записей, которые не нужно читать.
Выполните следующий SQL, используя где:
explain select * from employees where last_name='wang' and first_name LIKE '%zi' ;
Использование Where означает, что не все данные, полученные из механизма хранения, соответствуют условиям и должны быть отфильтрованы на уровне сервера. Сначала используйте условие last_name, чтобы просмотреть диапазон индекса, прочитать записи таблицы данных, а затем сравнить, чтобы проверить, выполняется ли условие first_name LIKE '%zi'. На данный момент подходит только 1 из 3 предметов.
Включить ПИК:
set optimizer_switch='index_condition_pushdown=on';
План выполнения на данный момент, используя условие индекса:
Функция Index Condition Pushdown — это функция, улучшенная после версии 5.6. Применяется только к вторичным индексам. Цель ICP — уменьшить количество операций чтения, которые обращаются к полной строке таблицы, и, таким образом, уменьшить количество операций ввода-вывода.
Суммировать
Поскольку индексы могут иметь огромное значение для повышения производительности запросов, наша цель — максимально использовать индексы.
- На что следует обратить внимание при разумном создании индексов
- Создайте индекс в поле (on), используемом для сортировки и объединения в порядке суждения.
- Не используйте слишком много индексов. - Пустая трата места и медленные обновления.
- Поля с низкой дискриминацией, такие как пол, не должны индексироваться. —— Дисперсия слишком мала, что приводит к слишком большому количеству строк сканирования.
- Часто обновляемые значения не следует использовать в качестве первичных ключей или индексов. - разделение страницы
- Составной индекс ставит на первое место наиболее хэшируемые (хорошо различимые) значения.
- Создайте составной индекс вместо изменения индекса с одним столбцом.
- Как создать индекс для слишком длинного поля?
- Почему не рекомендуется использовать неупорядоченные значения (например, идентификаторы, UUID) в качестве индексов?
- Когда индекс не используется?
- Используйте функции (replace\SUBSTR\CONCAT\sum count avg), выражения, вычисления (+ - */) для индексированных столбцов:
explain SELECT * FROM `t2` where id+1 = 4;- Строка без кавычек, происходит неявное преобразование
ALTER TABLE user_innodb DROP INDEX comidx_name_phone; ALTER TABLE user_innodb add INDEX comidx_name_phone (name,phone);explain SELECT * FROM `user_innodb` where name = 136; explain SELECT * >FROM `user_innodb` where name = '136';
- Подобным условиям предшествует %
- В условии где, например, abc%, например, %2673% и %888 не используются индексы? Почему?
explain select *from user_innodb where name like 'wang%'; explain select *from user_innodb where name like '%wang';Фильтрация слишком дорога для использования индекса. В настоящее время можно использовать полнотекстовое индексирование. 4. Отрицательный поиск
- НЕ НРАВИТСЯ не может:
explain select *from employees where last_name not like 'wang'- != () и NOT IN в некоторых случаях могут:
explain select *from employees where emp_no not in (1) explain select *from employees where emp_no <> 1
Обратите внимание, что использование индекса в операторе SQL зависит от версии базы данных, объема данных и избирательности данных. На самом деле, использовать индекс или нет, в конечном счете, зависит от оптимизатора.
- На чем основан оптимизатор?
Он не основан на правилах (Оптимизатор на основе правил) и не основан на семантике. Как получить, как тратить как можно меньше.docs.Oracle.com/Чэнду/B10501_0… Dev.MySQL.com/doc/Furious/…
By the way
Что-то не так? Вы можете оставить мне сообщение или приватный чат Есть урожай? Тогда поставь мне палец вверх~
Конечно, вы также можете зайти на «6 Xixuan» под моим официальным аккаунтом,
Ответьте «Узнать», чтобы получить копию[Видеоруководство для Java-инженеров и продвинутых архитекторов]~
Ответ на "интервью" вы можете получить:[Вопросы для интервью по Java, с которыми я тщательно разобрался]
Ответ на "карту мозга MySQL", вы можете получить[Очки знаний MySQL, прочесывающие карту мозга в высоком разрешении]
Поскольку я программист с профессиональным образованием, я делал это на php, Android и железе, но в итоге решил сосредоточиться на Java, поэтому, если у вас есть какие-либо вопросы, вы можете задать их и обсудить в публичном аккаунте (вы можно поговорить о технических эмоциях хахаха) см. Если вы приедете, я отвечу как можно скорее. Надеюсь учиться и прогрессировать вместе с вами. Статьи о серверной архитектуре, анализе основных знаний Java, карьере, резюме интервью и т. д. будет продолжать время от времени нажимать вывод.Приглашаем всех обратить внимание~~~