Подробно объясните принцип и оптимизацию индекса Mysql.

MySQL

содержание

предисловие

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

В этой статье описаны некоторые знания в процессе изучения индексации MySQL, в основном для понимания и расширения «высокопроизводительного MySQL».

что такое индекс

Индекс — это структура данных, используемая механизмом хранения для быстрого поиска записей.

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

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

id name age
1 huyan 10
2 huiui 18
3 lumingfei 20
4 chuzihang 15
5 nono 21

Если мы хотим найти имя человека в возрасте 15 лет, мы можем пройтись по всем данным только для сравнения по одному без индекса, тогда временная сложность будет O(n).

А если в процессе вставки данных поддерживать дополнительный массив, то поле age сохраняется упорядоченно, получается следующий массив.


[10,15,18,20,21]
 |  |  |  |  |
[x1,x4,x2,x3,x5]

Следующий x - это место хранения смоделированных данных на диске. В настоящее время, если нам нужно найти имя 15-летнего человека, мы можем выполнить двоичный поиск в массиве покрытия. Как мы все знаем, временная сложность бинарного поиска O (logn), после чего вы можете получить реальные данные в соответствии с конкретным местоположением.

PS: В качестве индекса в MySQL используется не массив, а дерево B+ (описанное ниже) Пример массива используется здесь только потому, что его легче понять.

Что нам может дать индексация?

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

Подводя итог преимуществам индексов (изложенным в книге «Высокая производительность»):

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

Каковы недостатки индексов?

Во-первых, индекс — это тоже данные, и его тоже нужно хранить, поэтому он будет занимать дополнительное место для хранения. принесет дополнительные накладные расходы времени.

в заключении:

  • Индекс занимает место на диске или в памяти
  • Замедляет операции вставки и обновления

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

Какие типы индексов существуют?

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

MySQL в основном имеет следующие индексы:

  • Индекс B-дерева/индекс дерева B+
  • хэш-индекс
  • Индекс пространственных данных
  • полный текстовый указатель

В этой статье рассматриваются только индексы B-дерева и индексы B+ дерева.

Индексы B-дерева и индексы B+ дерева

Принцип структуры данных B-дерева и B+ дерева здесь подробно объясняться не будет, заинтересованные партнеры могут обратиться к статье в статье или изучить его самостоятельно через google.

B-дерево

B-дерево — это многостороннее сбалансированное дерево поиска, которое для B-дерева порядка M обладает следующими свойствами:

  1. Корневой узел имеет не менее двух дочерних элементов.
  2. Каждый узел содержит k-1 элементов и k потомков, где m/2
  3. Каждый листовой узел содержит k-1 элементов, где m/2
  4. Все листовые узлы находятся на одном уровне.
  5. Элементы в каждом узле располагаются от меньшего к большему, тогда k-1 элементов — это в точности деление диапазона значений, содержащихся в k дочерних элементах.

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

В+ дерево

B+-дерево является расширенной версией B-дерева, и на основе B-дерева сделаны следующие ограничения:

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

Какие преимущества это может принести?

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

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

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

Префиксные индексы и индексная избирательность

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

  1. Найдите соответствующее значение в индексированном дереве B+, например, найдите название школы как卡塞尔学院Запись, и получить адрес этих данных на диске.
  2. По адресу переходим на диск, чтобы найти все значения данных.

Тогда, если во всех значениях названия школы,卡塞尔может однозначно идентифицировать эти данные, а затем использовать卡塞尔сделать индекс может достигать и卡塞尔学院Имеют ли индексирование такой же эффект?

Ответ да, при использовании卡塞尔Если да, то размер индекса можно уменьшить до 60% от исходного, в этом роль префиксного индекса.

индекс префикса: При индексировании относительно длинной строки вы можете индексировать только часть символов в начале, что может значительно сэкономить место в индексе и повысить эффективность индексации, однако это также снизит избирательность индекса.

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

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

select 
    count(distinct left(school_name,3))/count(*) as sch3, 
    count(distinct left(school_name,4))/count(*) as sch4,
    count(distinct left(school_name,5))/count(*) as sch5,
    count(distinct school_name)/count(*) as original
from 
    user;

найти вoriginalэто оригинальный выбор,sch3,sch4,sch5Это селективность при взятии в качестве индекса первых 3, 4 и 5 символов столбца.Постепенно увеличивайте это значение, когда селективность не сильно отличается от исходной, это более подходящая длина индекса префикса.(вообще это это так, но есть исключения, когда такой префиксный индекс будет плохо работать в конкретном случае, когда данные крайне неравномерны).

Как только вы нашли правильную длину, вы можете создать индекс префикса:alter table user add index sch_pre3(`school(3)`)

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

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

Обычно у нас есть对多个列进行索引Поскольку потребности запросов разнообразны. В настоящее время мы можем установить несколько независимых индексов или создать совместный индекс. В большинстве случаев совместный индекс является более подходящим.

Предположим, мы хотим выполнить этот оператор:select * from user where school_name = '卡塞尔' and age > 20,мы вschoolиageУстановите два независимых индекса на вышеуказанном, тогда мы ожидаем, что этот оператор запроса попадет в два индекса, но использование команды объяснения для проверки обнаружит, что это не обязательно.Это метафизический процесс.Я не изучал его четко.

Теоретически MySQL поддерживает объединенные индексы в версиях после 5.0, то есть использование двух индексов одновременно, но оптимизатор MySQL не обязательно так думает, он может подумать,查询两次B+树的代价高于查询一次索引之后去数据表进行过滤, поэтому я выберу использование только одного индекса (я провел тест, аналогичный этому случаю, на моих собственных 5 таблицах, и результаты показали, что используется только один индекс).

Синтаксис для создания совместного индекса:alter table user add index school_age(`school`,`age`).

При использовании объединенного индекса очень важным фактором является то, что все столбцы индекса могут выполнять сопоставление только самого левого префикса, например, приведенного выше.school_ageСовместный индекс нельзя использовать, когда в качестве условия запроса используется только возраст, т. е.select * from user where age =20Он не может попасть в указанный выше индекс сустава.

Не вдаваясь ни в какие запросы, следует сказать, что столбец с высокой селективностью помещается перед совместным индексом, но на самом деле мы более通过查询来反推索引,以使某个固定的查询可以尽可能的命中索引以提高查询速度В конце концов, цель нашего индекса также состоит в том, чтобы ускорить запрос.

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

Принцип самого левого префиксного индекса

Когда столбец данных упорядочен, mysql может использовать индекс, тогда предположим, что мы установилиschool_ageИндекс, пример данных выглядит следующим образом:

school age
a 12
b 12
b 14
b 15
c 1

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

С точки зрения всей таблицы, поле возраста не упорядочено, поэтому индекс нельзя использовать напрямую. Затем посмотрите на таблицу данных, когда упорядочен возраст? Когда школа выполняет сопоставление фиксированных значений, например, когдаschool=bКогда , для этих трех частей данных возраст упорядочен, поэтому можно использовать возрастной индекс Это принцип крайнего левого префикса.

Кроме того, крайний левый индекс префикса может использоваться только с запросом диапазона, например.select * from user where school > a, select * from user where school = a and age > 12, все могут попасть в индекс, ноselect * from user where school > a and age > 12Среди них только школа может соответствовать индексу, что также можно сделать из вышесказанного.Потому что, когда школа соответствует диапазону, mysql не может подтвердить, строго ли упорядочено поле возраста.Например, совпадение диапазона школы соответствует четырем частям данные b и c, то возраст не упорядочен, последующие индексы использовать нельзя.

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

Кластерный индекс — это не тип индекса, а способ хранения данных Кластерный индекс Innodb сохраняет индекс и данные в одной и той же структуре данных.

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

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

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

Разницу между кластеризованными и некластеризованными индексами можно проиллюстрировать на простом примере:

Когда мы получаем книгу, каталог является первичным ключом, и это кластеризованный индекс, потому что содержимое, которое непрерывно в каталоге, также непрерывно в тексте.迎着阳光盛大逃亡Для глав вам нужно только найти соответствующую страницу в оглавлении, например 459, а затем перейти к соответствующему номеру страницы, чтобы просмотреть текст.

Как насчет некластеризованного указателя, похожего на приложение в конце книги?индекс имен собственныхто же самое (вторичный нормальный индекс), когда вы смотрите вверх邦达列夫, приложение сообщит вам, что термин появляется в迎着阳光盛大逃亡раздел, а затем вам нужно перейти в каталог (индекс первичного ключа), чтобы снова найти соответствующий номер страницы.

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

Когда индекс содержит (или покрывает) значения всех полей, которые необходимо запросить, мы называем его покрывающим индексом.

Рассмотрим следующий оператор запроса:

select 
  school_name,age
from  
  user
where 
  school_name = '金色莺尾花学院'

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

Почему префикс и покрывающие индексы нельзя использовать вместе?

Поскольку цель префиксных индексов состоит в том, чтобы использовать префиксы для представления реальных значений, они имеют небольшую разницу в селективности, но MySQL все еще не может определить, что представляют собой реальные данные, такие как阿里巴巴и阿里妈妈Это то же самое, когда префикс равен 2, но для того, чтобы содержимое Alimama не отображалось при запросе Alibaba, вам нужно вернуться к таблице данных, чтобы получить данные, и снова выполнить точное совпадение, чтобы фильтр.

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

Удалите избыточные и повторяющиеся индексы

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

Например, создание обычного индекса по первичному ключу, несомненно, бесполезно.

Например, если есть совместный индексschool_age, затем создайтеschoolнезависимый индекс из-за принципа соответствия самого левого префикса индекса,school_ageВполне возможно попасть прямоschool, поэтому последний можно удалить.

Как просмотреть актуальную информацию об индексе?

индексная информация

можно использовать в mysqlshow index from table_nameдля просмотра индекса в таблице он будет иметь следующий вывод:

2019-06-02-22-28-04

или использоватьshow create table table_nameчтобы увидеть оператор создания таблицы, который содержит оператор для создания индекса.

размер индекса

В версиях после 5.0 мы можем проверитьinformation_schema.TABLESданные в таблице, чтобы получить более подробные данные.

Значение каждого поля этой таблицы следующее:

поле значение
Table_catalog Справочник регистрации листа данных
Table_schema Имя базы данных, к которой принадлежит таблица данных
Table_name имя таблицы
Table_type тип таблицы [системный вид
Engine Используемый движок базы данных [MyISAM
Version версия, по умолчанию 10
Row_format Формат линии [Компактный
Table_rows Сколько строк данных хранится в таблице
Avg_row_length средняя длина линии
Data_length Длина данных
Max_data_length максимальная длина данных
Index_length длина индекса
Data_free космический мусор
Auto_increment Выполнить автоинкрементное текущее значение первичного ключа автоинкремента
Create_time время создания таблицы
Update_time время обновления таблицы
Check_time Проверить время стола
Table_collation Набор кодировок проверки символов для таблицы
Checksum контрольная сумма
Create_options Создать параметры
Table_comment Примечания, примечания к таблицам

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

// 查看当前MySQL服务器所有索引的大小(以MB为单位,默认是字节)
SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES
// 查看某一个库的所有大小
SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES  WHERE table_schema = 'XXX';
// 查看某一个表的索引大小
SELECT CONCAT(ROUND(SUM(index_length)/(1024*1024), 2), ' MB') AS 'Total Index Size' FROM TABLES  WHERE table_schema = 'yyyy' and table_name = "xxxxx";  
// 汇总查看一个库中的数据大小及索引大小
SELECT CONCAT(table_schema,'.',table_name) AS 'Table Name', CONCAT(ROUND(table_rows/1000000,4),'M') AS 'Number of Rows', CONCAT(ROUND(data_length/(1024*1024*1024),4),'G') AS 'Data Size', CONCAT(ROUND(index_length/(1024*1024*1024),4),'G') AS 'Index Size', CONCAT(ROUND((data_length+index_length)/(1024*1024*1024),4),'G') AS'Total'FROM information_schema.TABLES WHERE table_schema LIKE 'xxxxx';

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

Примечание: Приведенная выше таблица кэширована.После обновления индекса базы данных лучше всего выполнитьanalyze table xxxx, а затем проверьте его снова.MySQL будет обновлять эту таблицу только в случае значительного изменения данных таблицы (изменение размера более чем на 1/16 или вставка 2 миллиардов строк).

фрагментация индекса

В процессе создания и удаления индекса неизбежно произойдет фрагментация индекса и, конечно же, фрагментация данных.optimize table xxxЧтобы переупорядочить индексы и данные, для механизмов хранения, которые не поддерживают эту команду, можно использовать бессмысленный оператор alter для запуска переупорядочения, например: замена механизма хранения таблицы текущим механизмом,alter table xxxx engine=innodb.

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

Книга "Высокопроизводительный MySQL (третье издание)"B-дерево В+ дерево


Заканчивать.



ChangeLog

2019-06-01 Завершено

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

Добро пожаловать на перепечатку, пожалуйста, подпишите и сохраните исходную ссылку.

Контактный адрес электронной почты: huyanshi2580@gmail.com

Дополнительные заметки об обучении см. в личном блоге ------>Хуян тен

Категории