MySQL: детали индекса

исходный адрес

Принцип индекса MySQL и медленная оптимизация запросов:Специальности.Meituan.com/2014/06/30/…
Объясните подробно:blog.CSDN.net/QQ_38975553…
Подробное объяснение BTree и B+Tree:blog.CSDN.net/WeChat_4194…

Обзор индекса

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

Классификация индексов

1) Отделить от структуры хранения

Индекс B-дерева (B+дерево, B-дерево)
хэш-индекс
полнотекстовый индекс полнотекстовый индекс

2) Отделить от прикладного уровня

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

3) Разделяется в зависимости от того, соответствует ли порядок расположения записей таблицы порядку расположения индексов.

Кластеризованный индекс: порядок записей таблицы такой же, как порядок индекса.
Некластеризованный индекс: порядок записей в таблице не соответствует порядку индекса.

Индексировать базовую структуру данных

Дисковый ввод-вывод и чтение вперед

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

Данные чтения диска зависят от механического движения.Время, необходимое для считывания данных каждый раз, можно разделить на три части: время поиска, задержка вращения и время передачи.Время поиска относится к времени, которое требуется магнитному рычагу для перемещения в указанную дорожку., основной диск обычно составляет менее 5 мс; задержка вращения - это скорость диска, которую мы часто слышим, например, диск 7200 об / мин, что означает, что он может вращаться 7200 раз в минуту, то есть он может вращаться 120 раз за 1 секунду, а задержка вращения составляет 1/120/2 = 4,17 мс Время передачи относится ко времени чтения или записи данных с диска, как правило, в десятых долях миллисекунды, что незначительно по сравнению с первые два раза. Тогда время доступа к диску, то есть время одного дискового ввода-вывода, составляет около 5+4.17 = 9 мс, что звучит неплохо, но вы должны знать, что машина с 500-MIPS может выполнять 500 миллионов инструкций в секунду, Поскольку инструкции зависят от природы электричества, другими словами, 400 000 инструкций могут быть выполнены за одно время ввода-вывода, а база данных может легко содержать 100 000 000 000 000 или даже 10 000 000 данных уровня, а каждые 9 миллисекунд — это, очевидно, катастрофа.

На следующем рисунке для справки приведена сравнительная таблица задержки аппаратного обеспечения компьютера:

various-system-software-hardware-latencies

Учитывая, что дисковый ввод-вывод является очень затратной операцией, операционная система компьютера сделала некоторые оптимизации, при выполнении ввода-вывода в буфер памяти считываются не только данные текущего адреса диска, но и соседние данные, т.к. Принцип упреждающего чтения говорит нам, что когда компьютер обращается к данным по адресу, соседние данные также будут доступны быстро. Данные, считываемые каждым IO, называются страницей. Количество данных на странице зависит от операционной системы, обычно 4 КБ или 8 КБ, то есть при чтении данных на странице фактически происходит только один ввод-вывод Эта теория очень полезна для разработки структуры данных индекса.

B-дерево и B+дерево

B-tree

B-Tree — это сбалансированное дерево поиска, предназначенное для внешних устройств хранения, таких как диски.

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

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

Каждый узел имеет не болееmдочерние узлы
Каждый нелистовой узел (кроме корневого узла) имеет не менее ⌈m/2⌉ дочерние узлы
Если корневой узел не является листовым узлом, то он имеет как минимум двух дочерних элементов.
имеютkнелистовые узлы дочерних узлов имеютk− 1 ключ
Все листовые узлы находятся на одном уровне

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

Каждый узел занимает дисковое пространство одного дискового блока.Два ключевых слова в порядке возрастания на узле и три указателя на корневой узел поддерева.Указатель хранит адрес дискового блока, где находится дочерний узел. Три поля области действия, разделенные двумя ключевыми словами, соответствуют полям области действия данных поддерева, на которые указывают три указателя. Взяв в качестве примера корневой узел, ключевые слова равны 17 и 35, диапазон данных поддерева, на который указывает указатель P1, меньше 17, диапазон данных поддерева, на который указывает указатель P2, составляет 17~35, и диапазон данных поддерева, на которое указывает указатель P3, больше 35.

Смоделируйте процесс поиска ключевого слова 29:

Найдите блок диска 1 в соответствии с корневым узлом и прочитайте его в память. [Дисковый ввод-вывод в первый раз]

Сравните ключ 29 в интервале (17, 35) и найдите указатель P2 блока 1 диска.
Найдите дисковый блок 3 по указателю P2 и прочитайте его в память. [Дисковый ввод-вывод 2-й раз]

Сравните ключ 29 в интервале (26, 30) и найдите указатель P2 дискового блока 3.
Найдите дисковый блок 8 по указателю P2 и прочитайте его в память. [3-й дисковый ввод-вывод]

Ключевое слово 29 находится в списке ключевых слов в блоке 8 диска.

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

B+Tree

B+Tree — это оптимизация, основанная на дереве B. Механизм хранения InnoDB использует B+Tree для реализации своей структуры индекса.

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

Поскольку неконечные узлы B+Tree хранят только информацию о ключе-значении, предполагая, что каждый блок диска может хранить 4 значения ключа и информацию об указателе, структура B+Tree показана на следующем рисунке:

Несколько принципов построения индекса

Самый левый принцип сопоставления префиксов, очень важный принцип, mysql всегда будет сопоставляться справа, пока не встретит запрос диапазона (>, 3 и d = 4 Если установлен индекс порядка (a,b,c,d), d не будет использовать индекс, если установлен индекс (a,b,d,c), его можно использовать, порядок a, b, d Может быть скорректирован произвольно.
= и in могут стоять не по порядку, например, a = 1 и b = 2 и c = 3. Индекс (a,b,c) можно строить в любом порядке, оптимизатор запросов mysql поможет вам его оптимизировать в форму, которую индекс может распознать.
Постарайтесь выбрать в качестве индекса столбец с высокой степенью дискриминации. Формула для степени дискриминации: количество (уникальный столбец) / количество (*), что указывает на долю неповторяющихся полей. Чем больше доля, чем меньше записей мы сканируем. 1, а некоторые поля статуса и пола могут иметь степень дискриминации 0 перед большими данными, поэтому некоторые люди могут спросить, имеет ли какое-либо значение опыта для этого соотношения? В зависимости от сценария использования это значение трудно определить, как правило, поля, которые необходимо объединить, должны быть выше 0,1, то есть в среднем 10 записей за сканирование.
Столбец индекса не может участвовать в расчете, держите столбец «чистым», например, from_unixtime(create_time) = '2014-05-29', индекс нельзя использовать, причина очень проста, дерево b+ хранит все поле значения в таблице данных, но при извлечении нужно применить функцию ко всем элементам для сравнения, что явно слишком дорого. Таким образом, оператор должен быть записан как create_time = unix_timestamp('2014-05-29').
Попробуйте расширить индекс, не создавайте новый индекс. Например, в таблице уже есть индекс a, и теперь вам нужно добавить индекс (a, b), тогда вам нужно только изменить исходный индекс.

Основные шаги для оптимизации медленных запросов

Сначала запустите его, чтобы увидеть, действительно ли он работает медленно, обратите внимание на настройку SQL_NO_CACHE.
Условием where является запрос к одной таблице, а минимальная возвращаемая таблица записей заблокирована. Смысл этого предложения состоит в том, чтобы применить оператор запроса where к таблице с наименьшим количеством записей, возвращаемых в таблице, и начать поиск Каждое поле отдельной таблицы запрашивается отдельно, чтобы увидеть, какое поле имеет наивысшую степень дискриминация.
объясните проверить план выполнения, соответствует ли он 2 ожиданиям (начать запрос из таблицы с меньшим количеством заблокированных записей)
Оператор sql в форме order by limit позволяет сначала проверить отсортированную таблицу.
Понимание сценариев использования на стороне бизнеса
При добавлении индексов учитывайте несколько принципов построения индексов.
Результаты наблюдения, не соответствующие ожиданиям, продолжают анализировать с 0

объяснить подробно

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

объяснить вывод

Column	JSON Name	Meaning
id	select_id	The SELECT identifier
select_type	None	The SELECT type
table	table_name	The table for the output row
partitions	partitions	The matching partitions
type	access_type	The join type
possible_keys	possible_keys	The possible indexes to choose
key	key	The index actually chosen
key_len	key_length	The length of the chosen key
ref	ref	The columns compared to the index
rows	rows	Estimate of rows to be examined
filtered	filtered	Percentage of rows filtered by table condition
Extra	None	Additional information

id

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

MySQL делит выборочные запросы на простые запросы (SIMPLE) и сложные запросы (PRIMARY). Сложные запросы делятся на три категории: простые подзапросы, производные таблицы (подзапросы в операторе from) и запросы на объединение.

Чем больше столбец идентификатора, тем выше приоритет выполнения. Если идентификатор тот же, он будет выполняться сверху вниз. Если идентификатор равен NULL, он будет выполнен последним..

select_type

select_type указывает, является ли соответствующая строка простым или сложным запросом.

table

Этот столбец указывает, к какой таблице обращается строка объяснения.

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

При наличии объединения значение столбца таблицы UNION RESULT равно , 1 и 2 представляют идентификаторы выбранных строк, участвующих в объединении.

partitions

type

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

В порядке от лучшего к худшему: system > const > eq_ref > ref > range > index > ALL

NULL:MySQL может разложить оператор запроса на этапе оптимизации, и ему больше не нужно обращаться к таблице или индексу на этапе выполнения. Например: чтобы выбрать минимальное значение в столбце индекса, это можно сделать, просматривая только индекс, и нет необходимости обращаться к таблице во время выполнения..
const, system: MySQL может оптимизировать часть запроса и преобразовать его в константу (см. результаты показа предупреждений). Когда все столбцы, используемые для первичного ключа или уникального ключа, сравниваются с константами, поэтому в таблице есть не более одной совпадающей строки, которая читается один раз, что быстрее. system является частным случаем const. Когда в таблице есть только один совпадающий кортеж, это system
eq_ref:Объединение использует все части первичного ключа или индекса уникального ключа, возвращая не более одной подходящей записи. Это, вероятно, лучший тип соединения из const, и простые запросы на выборку не имеют этого типа.
ref:По сравнению с eq_ref он не использует уникальный индекс, а использует общий индекс или частичный префикс уникального индекса.Индекс необходимо сравнить с определенным значением, и может быть найдено несколько подходящих строк.
range:Сканирование диапазона обычно происходит в таких операциях, как in(), between , > , = . Используйте индекс для получения заданного диапазона строк.
index:Сканировать полный индекс таблицы, что обычно быстрее, чем ALL. (индекс читается из индекса, и все читается с жесткого диска)
ALL:То есть полное сканирование таблицы, а значит, mysql нужно найти нужную строку от начала до конца. Обычно это требует увеличения индекса для оптимизации

possible_keys

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

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

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

key

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

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

key_len

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

ref

Этот столбец показывает столбец или константу, используемую в таблице поиска значения в индексе записи ключевого столбца, общие из них: const (константа), имя поля (например: фильм. id)

rows

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

filtered

Extra

Using index:Столбцы запроса покрываются индексом, а условием фильтрации является начальный столбец индекса (крайний левый индекс), что обеспечивает высокую производительность. Как правило, используется покрывающий индекс (индекс содержит все запрашиваемые поля). Для innodb, если это вспомогательный индекс, производительность значительно улучшится..
Using where:Столбец запроса не покрывается индексом, а условие фильтра where не является ведущим столбцом индекса..
Using where Using index:Столбец запроса покрывается индексом, а условие фильтра where является одним из столбцов индекса, но не ведущим столбцом индекса, что означает, что квалифицированные данные не могут быть запрошены непосредственно через поиск по индексу.Использование индекса означает, что выбор использует индекс покрытия.
NULL:Столбцы запроса не покрываются индексом, а условие фильтра where является ведущим столбцом индекса, что означает, что индекс используется, но некоторые поля не покрываются индексом, что должно быть реализовано путем «возврата таблица", не только с использованием индекса, ни полностью без индекса.
Using index condition:Как и при использовании where, столбцы запроса не полностью покрываются индексом, а условие where представляет собой диапазон ведущих столбцов;
Использование временных: MySQL необходимо создать временную таблицу для обработки запроса. Когда это происходит, как правило, необходимо оптимизировать.Во-первых, нужно подумать об использовании индексов для оптимизации.
Использование файловой сортировки: вместо того, чтобы читать строки из таблицы в порядке индекса, mysql сортирует результаты, используя внешний индекс. На этом этапе mysql просматривает все подходящие записи в соответствии с типом соединения, сохраняет ключ сортировки и указатель строки, а затем сортирует ключ и извлекает информацию строки по порядку. В этом случае вообще необходимо рассмотреть возможность использования индексов для оптимизации.

Анализ случая

1. Написание сложных предложений

Во многих случаях мы пишем SQL только для реализации функции. Это только первый шаг. Различные методы написания операторов часто имеют существенные различия в эффективности. Это требует от нас очень четкого понимания плана выполнения MySQL и принципов индексирования. См. Следующее утверждение:

select
    distinct cert.emp_id
from
    cm_log cl
    inner join (
        select
            emp.id as emp_id,
            emp_cert.id as cert_id
        from
            employee emp
            left join emp_certificate emp_cert on emp.id = emp_cert.emp_id
        where
            emp.is_deleted = 0
    ) cert on (
        cl.ref_table = 'Employee'
        and cl.ref_oid = cert.emp_id
    )
    or (
        cl.ref_table = 'EmpCertificate'
        and cl.ref_oid = cert.cert_id
    )
where
    cl.last_upd_date >= '2013-11-07 15:03:00'
    and cl.last_upd_date <= '2013-11-08 16:00:00';

Сначала запустите его, 53 записи занимают 1,87 секунды, и оператор агрегации не используется, что относительно медленно.

   53 rows in set (1.87 sec)

explain

Кратко опишите план выполнения.Сначала mysql сканирует таблицу cm_log по индексу idx_last_upd_date, чтобы получить 379 записей, затем таблица сканирует 63727 записей, которые разбиты на две части.derived представляет таблицу построения, то есть несуществующую таблица, которую можно просто понять как результирующий набор, сформированный оператором, за которым следует число, указывающее идентификатор оператора. производный2 означает, что запрос с ID = 2 построил виртуальную таблицу и вернул 63727 записей. Давайте посмотрим, что сделал оператор с ID = 2, чтобы вернуть такой большой объем данных.Сначала полная таблица сканирует 13317 записей в таблице сотрудников, а затем связывает таблицу emp_certificate по индексу emp_certificate_empid. 1 означает, что каждая ассоциация заблокирована только Запись записывается, и эффективность относительно высока. После получения связывается с 379 записями cm_log по правилам. Из процесса выполнения видно, что возвращается слишком много данных, и большая часть возвращаемых данных не используется cm_log, поскольку cm_log блокирует только 379 записей.

Как его оптимизировать? Видно, что нам все еще нужно присоединиться к cm_log после запуска, поэтому можем ли мы присоединиться к cm_log раньше? Тщательный анализ оператора найти несложно.Основная идея заключается в том, что если ref_table cm_log имеет значение EmpCertificate, то оно связано с таблицей emp_certificate, а если ref_table является значением Employee, оно связано с таблицей сотрудников.Мы можем полностью разделите его на две части и соедините их с помощью union.Обратите внимание, что здесь используется union, а Union all не используется, потому что исходный оператор имеет «различный» для получения единственной записи, а union просто выполняет эту функцию. Если в исходном операторе нет отдельного и его не нужно дедуплицировать, мы можем использовать объединение всех напрямую, потому что действие дедупликации при использовании объединения повлияет на производительность SQL.

Оптимизированное выражение выглядит следующим образом:

   select
       emp.id
   from
       cm_log cl
       inner join employee emp on cl.ref_table = 'Employee'
       and cl.ref_oid = emp.id
   where
       cl.last_upd_date >= '2013-11-07 15:03:00'
       and cl.last_upd_date <= '2013-11-08 16:00:00'
       and emp.is_deleted = 0
   union
   select
       emp.id
   from
       cm_log cl
       inner join emp_certificate ec on cl.ref_table = 'EmpCertificate'
       and cl.ref_oid = ec.id
       inner join employee emp on emp.id = ec.emp_id
   where
       cl.last_upd_date >= '2013-11-07 15:03:00'
       and cl.last_upd_date <= '2013-11-08 16:00:00'
       and emp.is_deleted = 0

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

2. Уточнить сценарии применения

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

select
  *
from
  stage_poi sp
where
  sp.accurate_result = 1
  and (
      sp.sync_status = 0
      or sp.sync_status = 2
      or sp.sync_status = 4
  );

Давайте посмотрим, сколько времени требуется для запуска, 951 фрагмент данных составляет 6,22 секунды, что очень медленно.

951 rows in set (6.22 sec)

Сначала объясните, количество строк достигло 3,61 миллиона, а тип = ALL указывает на полное сканирование таблицы.

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

Взгляните на количество записей с accept_result = 1:

select count(*),accurate_result from stage_poi  group by accurate_result;
+----------+-----------------+
| count(*) | accurate_result |
+----------+-----------------+
|     1023 |              -1 |
|  2114655 |               0 |
|   972815 |               1 |
+----------+-----------------+

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

Взгляните еще раз на поле sync_status:

select count(*),sync_status from stage_poi  group by sync_status;
+----------+-------------+
| count(*) | sync_status |
+----------+-------------+
|     3080 |           0 |
|  3085413 |           3 |
+----------+-------------+

Та же степень дискриминации тоже очень низкая, и по теории не годится для индексации.

Из анализа проблемы, кажется, пришел к выводу, что таблица не может быть оптимизирована.Различие между двумя столбцами очень низкое.Даже если добавить индекс, он может только адаптироваться к этой ситуации.Трудно сделать общую оптимизацию, например, когда sync_status 0, 3 Распределение очень даже, то записей блокировки также миллионы.

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

В соответствии с правилами индексирования используйте следующую инструкцию для создания индекса

alter table stage_poi add index idx_acc_status(accurate_result,sync_status);

Наблюдая ожидаемый результат, это занимает всего 200 мс, что более чем в 30 раз быстрее.

952 rows in set (0.20 sec)

Рассмотрим процесс анализа проблемы.Однотабличные запросы относительно легко оптимизируются.В большинстве случаев достаточно добавить индексы к полям в условии where по правилам.Если это просто "безмозгло" оптимизация, очевидно, некоторые столбцы с очень низкой степенью дискриминации и столбцы, которые не должны быть проиндексированы, также будут проиндексированы, что серьезно повлияет на производительность вставки и обновления, а также может повлиять на другие операторы запроса. Поэтому очень важен сценарий использования нашего корректирующего SQL шага 4. Только зная этот бизнес-сценарий, мы можем лучше помочь нам лучше анализировать и оптимизировать оператор запроса.

3. Заявления, которые нельзя оптимизировать

select
   c.id,
   c.name,
   c.position,
   c.sex,
   c.phone,
   c.office_phone,
   c.feature_info,
   c.birthday,
   c.creator_id,
   c.is_keyperson,
   c.giveup_reason,
   c.status,
   c.data_source,
   from_unixtime(c.created_time) as created_time,
   from_unixtime(c.last_modified) as last_modified,
   c.last_modified_user_id
from
   contact c
   inner join contact_branch cb on c.id = cb.contact_id
   inner join branch_user bu on cb.branch_id = bu.branch_id
   and bu.status in (1, 2)
   inner join org_emp_info oei on oei.data_id = bu.user_id
   and oei.node_left >= 2875
   and oei.node_right <= 10802
   and oei.org_category = - 1
order by
   c.created_time desc
limit
   0, 10;

Еще несколько шагов.

Давайте сначала посмотрим, как долго выполняется оператор: на 10 записей ушло 13 секунд, что невыносимо.

10 rows in set (13.06 sec)

explain

Из плана выполнения mysql сначала проверяет таблицу org_emp_info для сканирования 8849 записей, затем использует индекс idx_userid_status для связывания таблицы branch_user, затем использует индекс idx_branch_id для связывания таблицы contact_branch и, наконец, связывает таблицу контактов с первичным ключом.

Возвращаемых строк очень мало, и исключений не видно. Смотрим выписку и обнаруживаем, что сзади стоит комбинация order by + limit.Может быть сумма сортировки слишком велика? Итак, мы упрощаем SQL, удаляем порядок и ограничение и смотрим, сколько записей используется для сортировки.

select
 count(*)
from
  contact c  
inner join
  contact_branch cb 
     on  c.id = cb.contact_id  
inner join
  branch_user bu 
     on  cb.branch_id = bu.branch_id 
     and bu.status in (
        1,
     2)  
  inner join
     org_emp_info oei 
        on  oei.data_id = bu.user_id 
        and oei.node_left >= 2875 
        and oei.node_right <= 10802 
        and oei.org_category = - 1  

+----------+
| count(*) |
+----------+
|   778878 |
+----------+
1 row in set (5.19 sec)

Выяснилось, что 778 878 записей были заблокированы перед сортировкой. Если сортировать результирующий набор из 700 000, это было бы катастрофой. Неудивительно, что это так медленно. Можем ли мы изменить свое мышление? Сначала отсортировать по созданному_времени контактов, а затем присоединиться будет быстрее Шерстяная ткань?

Таким образом, он преобразуется в следующий оператор, который также можно оптимизировать с помощью Straight_Join:

select
   c.id,
   c.name,
   c.position,
   c.sex,
   c.phone,
   c.office_phone,
   c.feature_info,
   c.birthday,
   c.creator_id,
   c.is_keyperson,
   c.giveup_reason,
   c.status,
   c.data_source,
   from_unixtime(c.created_time) as created_time,
   from_unixtime(c.last_modified) as last_modified,
   c.last_modified_user_id
from
   contact c
where
   exists (
       select
           1
       from
           contact_branch cb
           inner join branch_user bu on cb.branch_id = bu.branch_id
           and bu.status in (1, 2)
           inner join org_emp_info oei on oei.data_id = bu.user_id
           and oei.node_left >= 2875
           and oei.node_right <= 10802
           and oei.org_category = - 1
       where
           c.id = cb.contact_id
   )
order by
   c.created_time desc
limit
   0, 10;

Проверьте эффект. Ожидается, что он увеличится более чем в 13 000 раз в течение 1 мс!

10 rows in set (0.00 sec)

Я думал, что работа уже сделана, но мы упустили одну деталь в предыдущем анализе. Теоретически, сначала сортировка, а затем объединение — это то же самое, что сначала объединение, а затем сортировка. Причина такого увеличения заключается в том, что есть предел! Общий процесс выполнения выглядит следующим образом: mysql сначала сортирует первые 10 записей по индексу, а затем присоединяется к фильтру. много данных отфильтровано внутренним соединением. Иногда это будет катастрофа. В крайних случаях ни одного фрагмента данных не может быть найдено во внутреннем слое. MySQL также глупо берет 10 фрагментов за раз, почти обходя эти данные Таблица!

Поэкспериментируйте с SQL с разными параметрами:

select
  sql_no_cache c.id,
  c.name,
  c.position,
  c.sex,
  c.phone,
  c.office_phone,
  c.feature_info,
  c.birthday,
  c.creator_id,
  c.is_keyperson,
  c.giveup_reason,
  c.status,
  c.data_source,
  from_unixtime(c.created_time) as created_time,
  from_unixtime(c.last_modified) as last_modified,
  c.last_modified_user_id
from
  contact c
where
  exists (
      select
          1
      from
          contact_branch cb
          inner join branch_user bu on cb.branch_id = bu.branch_id
          and bu.status in (1, 2)
          inner join org_emp_info oei on oei.data_id = bu.user_id
          and oei.node_left >= 2875
          and oei.node_right <= 2875
          and oei.org_category = - 1
      where
          c.id = cb.contact_id
  )
order by
  c.created_time desc
limit
  0, 10;

Empty set (2 min 18.99 sec)

2 мин 18. 99 сек! Гораздо хуже, чем раньше. Из-за механизма вложенных циклов mysql оптимизация в этой ситуации практически невозможна. Этот оператор может быть передан прикладной системе только для оптимизации ее собственной логики.

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

Здесь анализируется случай медленного запроса, и выше приведены лишь некоторые типичные случаи. В процессе оптимизации мы столкнулись с более чем 1000 строк, включая "мусорный SQL" из 16 объединений таблиц. Мы также столкнулись с различиями между онлайн- и офлайн-базами данных, из-за которых медленные запросы напрямую приводили к смерти приложения. Мы также столкнулся с сравнениями, эквивалентными varchar.Я не писал одинарные кавычки, и я также столкнулся с декартовыми запросами произведения, которые непосредственно убили подчиненную библиотеку. Сколько бы кейсов ни было, это лишь накопление некоторого опыта.Если мы знакомы с внутренними принципами оптимизатора запросов и индекса, то разбор этих кейсов становится особенно простым.