Принцип индекса оптимизации MySql и оптимизация SQL

MySQL SQL

1. Причины низкой производительности Sql

  • Слишком много данных запроса (можно ли их разделить и как можно меньше фильтровать условия)
  • Связано слишком много таблиц, слишком много объединений (принцип объединения. Просмотрите все данные в таблице B с каждым фрагментом данных в таблице A. Поэтому попробуйте сначала отфильтровать.)
  • индекс не используется
  • Настройка сервера и настройка различных параметров (буферизация, количество потоков и т.д.)

2. Порядок записи и выполнения Sql

  • Порядок написания
select distinct  '字段'   
from '表'   
join '表' on '条件'   
where '条件'   
group by '字段'   
having '条件'   
order by '字段'   
limit '条件'
  • исполнительный лист
from '表'   
on '条件'
'join类型' join '表'
where '条件'
group by '字段'
having '条件'
select distinct '字段'
order by '字段'
limit '条件'
  • Диаграмма разбора SQL

3. Указатель

3.1 Что такое индекс

показатель(Index) — это структура данных, которая помогает MySQL эффективно извлекать данные. В дополнение к данным система базы данных также поддерживает структуры данных, которые удовлетворяют определенным алгоритмам поиска.Эти структуры данных указывают на данные определенным образом, так что эффективные алгоритмы поиска могут быть реализованы на этих структурах данных.Эта структура данных является индексом.
Вообще говоря, сам индекс тоже очень большой, и хранить его весь в памяти невозможно, поэтому он часто хранится на диске в виде индексного файла, на который мы обычно ссылаемся, если не указано иное, относится кBTreeИндекс (сбалансированное дерево многостороннего поиска). Среди них по умолчанию используются кластерный индекс, вторичный индекс, составной индекс покрытия, префиксный индекс и уникальный индекс.BTreeиндекс, совместно именуемый индексом.BTreeПосле индекса идет еще хэш-индекс

3.2 Принцип индексации

3.2.1 Описание

  • показатель(Index) — это структура данных, которая помогает MySQL эффективно извлекать данные. Разные движки используют разные структуры данных.
  • MyISAM занимаетBTree, InnoDB берет на себяB+TREE
    База данных в нашей работе обычно использует InnoDB, поэтому мы сосредоточимся на объясненииB+TREEИндекс дерева .B+ — это реализация дерева B+ в базе данных, и это наиболее распространенный и часто используемый индекс в базе данных. Буква B в дереве B+ означает баланс, а не бинарность, потому что дерево B+ развилось из самого раннего сбалансированного бинарного дерева. Прежде чем говорить о дереве B+, вы должны сначала понять бинарное дерево поиска, сбалансированное бинарное дерево (AVLTree) и сбалансированное многоходовое дерево поиска (B-Tree), дерево B+ постепенно оптимизируется из этих деревьев.

3.2.2 Двоичное дерево поиска

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

Найдите узлы бинарного дерева и найдите, что количество поисков узла с глубиной 1 равно 1, количество поисков узла с глубиной 2 равно 2, а количество поисков узла с глубина n равна n, поэтому среднее количество поисков равно (1 + 2 + 2 + 3 + 3 + 3) / 6 = 2,3 раза

Двоичное дерево поиска можно построить произвольно.Те же шесть чисел, что и 2, 3, 5, 6, 7 и 8, можно построить и следующим образом:

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

3.2.3 Сбалансированное двоичное дерево (AVL-дерево)

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

Если вы вставляете или удаляете узлы в дереве AVL, это может привести к тому, что дерево AVL потеряет равновесие.Это несбалансированное двоичное дерево можно обобщить в четыре отношения: LL (лево-лево), RR (право-право), LR (лево -правый), РЛ (правый левый). Их принципиальные схемы следующие:

Каждая из этих четырех несбалансированных позиций имеет свое собственное определение:

LL: LeftLeft, также известный как «Left Left». После вставки или удаления узла левый дочерний элемент (Left Child) корневого узла (Left Child) по-прежнему имеет непустые узлы, в результате чего высота левого поддерева корневого узла на 2 больше, чем высота правого поддерево, и дерево AVL не сбалансировано.
РР: Право-Право, также известное как «Право-Право». После вставки или удаления узла правый дочерний элемент (Right Child) корневого узла имеет непустой узел, из-за чего высота правого поддерева корневого узла на 2 больше, чем высота левого поддерева, и дерево AVL не сбалансировано.
LR: LeftRight, также известный как «левый и правый». После вставки или удаления узла левый дочерний элемент корневого узла (Left Child) и правый дочерний элемент (Right Child) также имеют непустые узлы, в результате чего высота левого поддерева корневого узла на 2 больше, чем высота левого поддерева корневого узла. высота правого поддерева, а дерево AVL не сбалансировано.
РЛ: Право-лево, также известное как «Право-лево». После вставки или удаления узла левый дочерний элемент (Left Child) правого дочернего элемента корневого узла (Right Child) также имеет непустые узлы, в результате чего высота правого поддерева корневого узла на 2 больше, чем высота левое поддерево, а дерево AVL не сбалансировано.

После того, как дерево AVL выйдет из равновесия, его можно восстановить, повернув его. Ниже описаны соответствующие методы вращения в случае четырех видов потери равновесия. Ротация ЛЛ. Когда LL выходит из равновесия, дерево AVL можно перебалансировать за один оборот. Действуйте следующим образом:

  1. Сделайте левого потомка корневого узла новым корневым узлом.
  2. Сделайте правый дочерний элемент нового корневого узла левым дочерним элементом исходного корневого узла.
  3. Сделайте исходный корневой узел правым потомком нового корневого узла.

Принципиальная схема вращения ЛЛ выглядит следующим образом:

Вращение RR: когда RR выходит из равновесия, метод вращения осесимметричен с LL.Этапы следующие:

  1. Сделайте правого потомка корневого узла новым корневым узлом.
  2. Сделайте левого дочернего элемента нового корневого узла правым дочерним элементом исходного корневого узла.
  3. Сделайте исходный корневой узел левым дочерним элементом нового корневого узла.

Принципиальная схема вращения RR выглядит следующим образом:

Вращение LR: когда LR разбалансирован, требуется два вращения Шаги следующие:

  1. Вращение RR вокруг левого дочернего элемента корневого узла.
  2. Вращение LL вокруг корневого узла.

Схема вращения LR выглядит следующим образом:

Вращение RL: Когда RL выходит из равновесия, ему также необходимо выполнить два вращения.Метод вращения осесимметричен с LR.Этапы следующие:

  1. Вращение LL вокруг правого потомка корневого узла.
  2. Вращение RR вокруг корневого узла.

Схема вращения RL выглядит следующим образом:

3.2.4 Сбалансированное многоканальное дерево поиска (B-дерево)

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

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

Механизм хранения InnoDB имеет концепцию страницы, а страница — это наименьшая единица управления диском. Размер по умолчанию каждой страницы в механизме хранения InnoDB составляет 16 КБ, а размер страницы может быть установлен на 4 КБ, 8 КБ и 16 КБ с помощью параметра innodb_page_size.

Однако пространство для хранения дискового блока в системе часто не так велико, поэтому каждый раз, когда InnoDB обращается за дисковым пространством, она будет использовать несколько последовательных дисковых блоков, чтобы достичь размера страницы 16 КБ. InnoDB использует страницы в качестве базовой единицы при чтении данных с диска на диск.Если при запросе данных каждая часть данных на странице может помочь определить местоположение записи данных, это уменьшит количество дисковых операций ввода-вывода.Улучшение запроса эффективность.

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

  1. Каждый узел имеет не более m потомков.
  2. За исключением корневого узла и конечных узлов, каждый другой узел имеет как минимум Ceil(m/2) дочерних элементов.
  3. Если корневой узел не является листовым узлом, он должен иметь как минимум 2 дочерних узла.
  4. Все листовые узлы находятся в одном слое и не содержат другой информации о ключевом слове.
  5. Каждый нетерминальный узел содержит n ключевых слов (P0,P1,…Pn, k1,…kn)
  6. Количество ключевых слов n удовлетворяет: ceil(m/2)-1
  7. ki(i=1,...n) — это ключевое слово, и ключевые слова отсортированы в порядке возрастания.
  8. Pi(i=1,...n) — указатель на корневой узел поддерева. Все ключи узлов поддерева, на которые указывает P(i-1), меньше ki, но все больше k(i-1)

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

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

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

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

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

3.2.5B+Tree

B+Tree — это оптимизация, основанная на B-Tree, что делает его более подходящим для реализации структуры индексов внешнего хранилища.Подсистема хранения InnoDB использует B+Tree для реализации своей структуры индексов.

Из диаграммы структуры B-дерева в предыдущем разделе видно, что каждый узел содержит не только значение ключа данных, но и значение данных. Место для хранения каждой страницы ограничено.Если данные данных велики, количество ключей, которые могут быть сохранены каждым узлом (т. е. страницей), будет очень маленьким.Когда объем хранимых данных велик, он также будет приводят к B- Глубина дерева велика, что увеличивает количество дисковых операций ввода-вывода во время запроса, тем самым влияя на эффективность запроса. В B+Tree все узлы записи данных хранятся на конечных узлах того же слоя в порядке размера значения ключа, а на неконечных узлах хранится только информация о значении ключа, что может значительно увеличить количество значений ключа. хранится в каждом узле. , уменьшите высоту B+Tree.

Между B+Tree и B-Tree есть несколько различий:

  1. Нелистовые узлы хранят только информацию о ключе-значении.
  2. Между всеми листовыми узлами есть цепной указатель.
  3. Записи данных хранятся в листовых узлах.

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

Обычно в B+Tree есть два головных указателя, один указывает на корневой узел, другой указывает на конечный узел с наименьшим ключевым словом, и между всеми конечными узлами (т.е. узлами данных) существует кольцевая структура. Таким образом, в B+Tree могут быть выполнены две операции поиска: одна — поиск по диапазону и поиск по страницам для первичного ключа, а другая — случайный поиск, начиная с корневого узла.

Возможно, в приведенном выше примере всего 22 записи данных, и преимущества B+Tree не видны.Сделаем расчет ниже:

Размер страницы в механизме хранения InnoDB составляет 16 КБ, тип первичного ключа общей таблицы — INT (занимает 4 байта) или BIGINT (занимает 8 байт), а тип указателя также обычно составляет 4 или 8 байт, т. е. сказать, что около 16 КБ / (8B + 8B) = 1K ключевых значений хранятся на странице (узел в дереве B +) (поскольку это оценка, для удобства расчета значение K здесь 〖 10〗^3). Другими словами, индекс B+Tree с глубиной 3 может содержать 10^3 * 10^3 * 10^3 = 1 миллиард записей.

На практике каждый узел может быть заполнен не полностью, поэтому в базе данных высота B+Tree обычно составляет 2–4 слоя. Механизм хранения InnoDB в mysql предназначен для хранения корневого узла в памяти, то есть для поиска записи строки с определенным значением ключа требуется не более 1-3 операций ввода-вывода на диске.

Индекс B+Tree в базе данных можно разделить на кластеризованный индекс и вторичный индекс. Реализация приведенной выше примерной диаграммы B+Tree в базе данных представляет собой кластеризованный индекс, а конечные узлы в B+Tree кластеризованного индекса хранят данные записи строки всей таблицы. Отличие вспомогательного индекса от кластеризованного индекса состоит в том, что листовые узлы вспомогательного индекса содержат не все данные записей строк, а ключ кластеризованного индекса, в котором хранятся соответствующие данные строки, то есть первичный ключ. При запросе данных через вспомогательный индекс механизм хранения InnoDB будет проходить по вспомогательному индексу, чтобы найти первичный ключ, а затем использовать первичный ключ, чтобы найти полные данные записи строки в кластеризованном индексе.

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

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

3.4 Категории индексов

3.4.1 Общий индекс

является самым основным индексом, он не имеет ограничений.

CREATE index 索引名 on 表名(列名)

3.4.2 Уникальный индекс

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

CREATE UNIQUE index 索引名 on 表名(列名)

3.4.3 Индекс первичного ключа

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

3.4.4 Составной индекс

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

CREATE index 索引名 on 表名(列名,列名...)

3.4.5 Полнотекстовое индексирование

В основном используется для поиска ключевых слов в тексте, без прямого сравнения со значением в индексе. Полнотекстовый индекс сильно отличается от других индексов, он больше похож на поисковую систему, а не на простое сопоставление параметров оператора where. Полнотекстовый индекс используется с операцией сопоставления, а не с общим оператором where плюс лайк. Его можно использовать при создании таблицы, изменении таблицы и создании индекса, но в настоящее время только столбцы char, varchar и text могут создавать полнотекстовый индекс. Стоит отметить, что когда объем данных велик, теперь поместите данные в таблицу без глобального индекса, а затем используйте индекс CREATE для создания полнотекстового индекса, вместо того, чтобы создавать полный текст для таблицы и затем записывать данные . намного быстрее

CREATE TABLE `table` ( 
    `id` int(11) NOT NULL AUTO_INCREMENT ,
    `title` char(255) CHARACTER NOT NULL ,
    `content` text CHARACTER NULL ,
    `time` int(10) NULL DEFAULT NULL ,
    PRIMARY KEY (`id`), FULLTEXT (content) 
);

3.5 Базовый синтаксис индексов

  • Создайте
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON 表名(列名)
  • Удалить
DROP INDEX [indexName] ON 表名;
  • Проверять
SHOW INDEX FROM 表名
  • изменить команду
‐‐ 有四种方式来添加数据表的索引： 
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的， 且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL 外，NULL可能会出现多次）。
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。 
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。

3.6 Сценарии использования индекса

3.6.1 Надлежащее использование индексов

  1. Поля, которые часто используются в качестве условий запроса, должны быть проиндексированы.
  2. Поля, связанные с другими таблицами в многотабличных запросах, взаимосвязи внешнего ключа индексируются
  3. Выбор одностолбцового индекса/составного индекса приводит к созданию составного индекса при высокой степени параллелизма.
  4. Поля, которые часто используются для сортировки в запросах
  5. Часто используется в запросах для подсчета или группировки полей.

3.6.2 Ненадлежащее использование индексов

  1. Часто обновляемые поля: каждое обновление влияет на дерево индекса.
  2. Поля, не используемые в условном запросе where
  3. слишком мало записей в таблице
  4. Таблицы, которые часто добавляются, удаляются и изменяются: когда таблица обновляется, индекс также должен обновляться.
  5. Примечание. Если в таблице много повторяющихся записей, нет особого смысла строить для нее индекс.

4. Анализ производительности

4.1Query Optimizer

MySQL Optimizer – это модуль оптимизатора, специально отвечающий за оптимизацию операторов SELECT. Его основная функция – предоставить запрошенному клиентом запросу оптимальный план выполнения путем расчета и анализа различной статистической информации, собранной в системе. Лучший способ извлечения данных.

4.2 Общие узкие места MySQL

  1. Насыщение ЦП: насыщение ЦП происходит, когда данные загружаются в память или считываются с диска.
  2. Узкое место ввода-вывода: узкое место дискового ввода-вывода возникает, когда загруженные данные намного превышают объем памяти.
  3. Узкое место в производительности серверного оборудования

4.3 План выполнения Объяснение

4.3.1Объяснить обзор

Используйте ключевое слово объяснения для имитации выполнения оптимизатором операторов SQL-запросов, чтобы узнать, как MYSQL обрабатывает операторы SQL.Мы можем использовать план выполнения для анализа узких мест в производительности операторов запросов или структур таблиц.

4.3.2 Объяснить функцию

  1. Просмотр порядка чтения таблицы
  2. Просмотр типа операции операции чтения базы данных
  3. Посмотрите, какие индексы, скорее всего, будут использоваться
  4. Посмотрите, какие индексы на самом деле используются
  5. Просмотр ссылок между таблицами
  6. Посмотрите, сколько строк в таблице запрашивается оптимизатором

4.3.3 Синтаксис

  • грамматика
explain sql语句

4.3.4 Объяснение каждого поля

  • Готов к работе
create table t1(
id int primary key, 
name varchar(20), 
col1 varchar(20), 
col2 varchar(20), 
col3 varchar(20) 
);
create table t2( 
id int primary key,
name varchar(20), 
col1 varchar(20), 
col2 varchar(20), 
col3 varchar(20) 
);
create table t3( 
id int primary key,
name varchar(20),
col1 varchar(20),
col2 varchar(20), 
col3 varchar(20) 
);
insert into t1 values(1,'zs1','col1','col2','col3'); 
insert into t2 values(1,'zs2','col2','col2','col3'); 
insert into t3 values(1,'zs3','col3','col2','col3'); 
create index ind_t1_c1 on t1(col1);
create index ind_t2_c1 on t2(col1); 
create index ind_t3_c1 on t3(col1);
create index ind_t1_c12 on t1(col1,col2);
create index ind_t2_c12 on t2(col1,col2); 
create index ind_t3_c12 on t3(col1,col2);
  • После выполнения оператора объяснения sql:

4.3.4.1 id

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

  • три ситуации:

    1. Значения id одинаковые, а порядок выполнения сверху вниз
    explain select t2.* from t1,t2,t3 where t1.id = t2.id and t1.id= t3.id and t1.name = 'zs';
    2. Значение id разное, чем больше значение id, тем выше приоритет и выполняется первым.
    explain select t2.* from t2 where id = (select id from t1 where id = (select t3.id from t3 where t3.name='zs3'));
    3. Бывают одинаковые и разные значения id.Если id одинаковые, то выполняются сверху вниз.Чем больше значение id, тем выше приоритет и раньше выполнение.
    explain select t2.* from (select t3.id from t3 where t3.name='zs3') s1,t2 where s1.id = t2.id;

4.3.4.2select_type

Тип запроса, в основном используемый для различения

  • SIMPLE: простой запрос на выборку, запрос не содержит подзапросов или UNION.
  • ОСНОВНОЙ: если запрос содержит сложные подзапросы, самый внешний запрос помечается как ОСНОВНОЙ.
  • ПОДЗАПРОС: включить подзапросы в список SELECT или WHERE.
  • DERIVED: подзапросы, включенные в список from, помечаются как производные DRIVED, MYSQL будет рекурсивно выполнять эти подзапросы и помещать результаты во временную таблицу.
  • UNION: если второй SELECT появляется после объединения, он помечается как UNION.Если объединение включено в подзапрос предложения from, внешний выбор помечается как: производный
  • РЕЗУЛЬТАТ ОБЪЕДИНЕНИЯ: выберите, чтобы получить результаты из таблицы объединения
explain select col1,col2 from t1 union select col1,col2 from t2;

4.3.4.3table

Показать, к какой таблице относятся данные в этой строке

4.3.4.4type

Тип доступа: все, индекс, диапазон, ссылка, eq_ref, константа, система, ноль
Порядок от лучшего к худшему: system > const > eq_ref>ref >range > index > all , лучше всего оптимизировать до уровня диапазона или уровня ссылки

  • system: в таблице есть только одна строка записей (системная таблица), которая является частным случаем типа const, который в принципе не отображается
  • const: его можно найти одним запросом через индекс. const используется для сравнения первичного ключа или уникального индекса. Поскольку сопоставляется только одна строка данных, это очень быстро. Если первичный ключ помещается в список where, mysql преобразует запрос в константу.
explain select * from (select * from t1 where id=1) s1;
  • eq_ref: сканирование уникального индекса, для каждого ключа индекса ему соответствует только одна запись в таблице, обычное сканирование первичного ключа или уникального индекса.
explain select * from t1,t2 where t1.id = t2.id;
  • ref : сканирование неуникального индекса, которое возвращает все строки, соответствующие одному значению, по сути является доступом к индексу, оно возвращает все подходящие строки, однако может возвращать несколько подходящих строк.
explain select * from t1 where col1='zs1';
  • диапазон : извлекает только строки в заданном диапазоне и использует индекс для выбора строк. Ключевой столбец показывает, какой индекс фактически используется. Как правило, условия диапазона, такие как между, >, 
explain select * from t1 where id between 1 and 10;
  • index : сканировать всю индексную таблицу, разница между index и all в том, что тип index проходит только по дереву индексов, обычно это быстрее, чем all, потому что файл index обычно меньше, чем файл данных, хотя и index, и all read вся таблица, но индекс из индекса, прочитанного с жесткого диска, и все прочитанные данные с жесткого диска
explain select id from t1;
  • all : полное сканирование таблицы полное сканирование таблицы, просмотр всей таблицы для поиска совпадающих строк
explain select * from t1;
  • Примечание: Во время разработки мы должны убедиться, что запрос достигает хотя бы уровня диапазона, желательно ref.Если появляются все миллионы данных, в общем, нам нужно рассмотреть возможность использования оптимизации индекса.

4.3.4.5possible_keys

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

4.3.4.6key

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

explain select t2.* from t1,t2,t3 where t1.col1 = ' ' and t1.id = t2.id and t1.id= t3.id;

explain select col1 from t1;

4.3.4.7key_len

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

explain select * from t1 where col1='c1';

explain select * from t1 where col1='col1' and col2 = 'col2';
‐‐ 注意: 为了演示这个结果,我们删除了c1上面的索引 
alter table t1 drop index ind_t1_c1; 
‐‐ 执行完成之后,再次创建索引 
create index ind_t1_c1 on t1(col1);

4.3.4.8ref

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

explain select * from t1,t2 where t1.col1 = t2.col1 and t1.col2 = 'col2';

4.3.4.9rows

По табличной статистике и выбору индекса оцените количество строк, которые нужно прочитать, чтобы узнать необходимые записи (сколько строк читает оптимизатор), чем меньше, тем лучше

4.3.4.10extra

Содержит другую очень важную дополнительную информацию

  • Использование файловой сортировки: указывает, что mysql будет использовать внешний индекс для сортировки данных, вместо чтения в порядке индекса в таблице операция сортировки, которую нельзя выполнить с помощью индекса в Mysql, называется сортировкой файлов.
explain select col1 from t1 where col1='col1' order by col3;

‐‐ 上面这条SQL语句出现了using filesort,但是我们去执行下面这条SQL语句的时候它,又不会出现using filesort
explain select col1 from t1 where col1='col1' order by col2;

‐‐ 如何优化第一条SQL语句 ? 
create index ind_t1_c13 on t1(col1,col3);
explain select col1 from t1 where col1='col1' order by col3;
  • Использование временных: временная таблица используется для сохранения промежуточных результатов.Mysql использует временную таблицу при сортировке результатов запроса, что является общим для упорядочения по запросу и группировки по запросу.
explain select col1 from t1 where col1>'col1' group by col2;

explain select col1 from t1 where col1 >'col1' group by col1,col2;
  • Using index :
  • Покрывающий индекс используется в операции запроса (столбцы запроса согласуются со столбцами индекса), чтобы избежать доступа к строкам данных таблицы, что является эффективным.
  • Если использование where также присутствует, это указывает, что индекс используется для выполнения поиска значения ключа индекса.
  • Если использование where одновременно отсутствует, это указывает на то, что индекс используется для чтения данных, а не для выполнения действия поиска.
  • Покрывающий индекс: столбец запроса совпадает со столбцом индекса, другими словами, столбец запроса должен быть покрыт индексом ключа, то есть столбец данных в выборке может быть прочитан только из индекса, без чтения исходного строка данных, MySql может использовать индекс для возврата полей в списке выбора без необходимости повторного чтения файла данных на основе индекса
explain select col2 from t1 where col1='col1';

explain select col2 from t1;
  • using where : указывает, где используется условная фильтрация
  • использование буфера соединения : указывает, что используется кеш соединения, и это может произойти, если слишком много объединений.
  • невозможно где : значение в предложении where всегда ложно и не может быть использовано для получения каких-либо данных
explain select * from t1 where col1='zs' and col1='ls';
  • select tables optimized away :
  • При отсутствии предложения group by оптимизация минимальных/максимальных операций на основе индексов или оптимизация операций count(*) для механизма хранения MyISAM не требует ожидания фазы выполнения для выполнения вычислений и фазы генерации плана выполнения запроса. завершает оптимизацию.
  • отличные : оптимизируйте отдельную операцию, прекратите поиск одного и того же значения после нахождения первых совпадающих данных.

5. Оптимизируйте реальный бой

5.1 Оптимизация запросов к одной таблице

Требование: Запросите article_id с наибольшим количеством просмотров, когда category_id равен 1, а количество комментариев больше 1.

  • оператор создания таблицы
CREATE TABLE IF NOT EXISTS `article` (
`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`author_id` INT(10) UNSIGNED NOT NULL, 
`category_id` INT(10) UNSIGNED NOT NULL, 
`views` INT(10) UNSIGNED NOT NULL,
`comments` INT(10) UNSIGNED NOT NULL,
`title` VARBINARY(255) NOT NULL,
`content` TEXT NOT NULL 
);
INSERT INTO `article`(`author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES
(1, 1, 1, 1, '1', '1'),
(2, 2, 2, 2, '2', '2'),
(1, 1, 3, 3, '3', '3');
  • План реализации
EXPLAIN SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
  • нет индекса
Вывод: очевидно, тип ALL, худший случай. Существует также использование сортировки файлов в Extra, что также является худшим случаем. Оптимизация обязательна.
  • Оптимизация первая:
create index idx_article_ccv on article(category_id,comments,views);

Вывод: тип становится диапазоном, что терпимо. Но использование файловой сортировки дополнительно по-прежнему неприемлемо. Но индекс мы уже установили, почему он бесполезен?Это потому что по принципу работы индекса BTree сначала сортируется category_id, если встречается такой же category_id, то сортируются комментарии, а если одинаковые комментарии встречаются, то представления сортируются. Когда поле комментариев находится в средней позиции в объединенном индексе, поскольку условие комментариев > 1 является значением диапазона (так называемый диапазон), MySQL не может использовать индекс для извлечения следующей части представлений, то есть индекса. за полем запроса типа диапазона недопустимо.

  • Оптимизация вторая:
‐‐ 先删除优化一索引 
DROP INDEX idx_article_ccv ON article;
‐‐ 创建索引 
create index idx_article_cv on article(category_id,views);

5.2 Ассоциативная оптимизация запросов

5.2.1 Пример

Требования: класс запроса и книга с использованием левого внешнего соединения

  • оператор создания таблицы
CREATE TABLE IF NOT EXISTS `class` (
`id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`id`) 
);
CREATE TABLE IF NOT EXISTS `book` (
`bookid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, 
`card` INT(10) UNSIGNED NOT NULL,
PRIMARY KEY (`bookid`) 
);
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO class(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO book(card) VALUES(FLOOR(1 + (RAND() * 20)));
  • План реализации
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
  • оптимизация
ALTER TABLE `book` ADD INDEX Y ( `card`);

Видно, что тип второй строки стал ref, и строки тоже стали оптимизированными.
Это определяется функцией левого соединения. Условие LEFT JOIN используется для определения того, как искать строки из правой таблицы, и обе должны быть слева, поэтому правая является нашей ключевой точкой и должна быть проиндексирована.
То есть: внешнее соединение создает индекс на противоположной стороне.

5.2.2 Предложение по оптимизации

  • Убедитесь, что поле соединения управляемой таблицы проиндексировано (таблица, к которой присоединена управляемая таблица, является управляемой таблицей (необходимо запрашивать))
  • При левом соединении выберите маленькую таблицу в качестве ведущей, а большую — в качестве ведомой.
  • Во время внутреннего соединения mysql поможет вам выбрать таблицу небольшого набора результатов в качестве управляющей таблицы.

5.3 Оптимизация подзапросов

  • Старайтесь не помещать подзапросы в управляемую таблицу, и возможно, что индекс не будет использоваться.
select a.name ,bc.name from t_emp a left join 
    (select b.id , c.name from t_dept b
    inner join t_emp c on b.ceo = c.id)bc
    on bc.id = a.deptid 
上段查询中用到了子查询，必然 bc 表没有索引。肯定会进行全表扫描
上段查询 可以直接使用 两个 left join 优化 
select a.name , c.name from t_emp a 
    left outer join t_dept b on a.deptid = b.id 
    left outer join t_emp c on b.ceo=c.id 
所有条件都可以使用到索引 
若必须用到子查询，可将子查询设置为驱动表，因为驱动表的type 肯定是 all，而子查询返回的结果表没有索引，必定 也是all

5.4 порядок по оптимизации

5.4.1 Подготовка среды

  • оператор создания таблицы
CREATE TABLE tblA(
id int primary key not null auto_increment, 
age INT,
birth TIMESTAMP NOT NULL,
name varchar(200) 
);

INSERT INTO tblA(age,birth,name) VALUES(22,NOW(),'abc');
INSERT INTO tblA(age,birth,name) VALUES(23,NOW(),'bcd'); 
INSERT INTO tblA(age,birth,name) VALUES(24,NOW(),'def');

CREATE INDEX idx_A_ageBirth ON tblA(age,birth,name);

5.4.2 Описание

MySQL поддерживает два вида сортировки, FileSort и Index, индекс эффективен Это означает, что MySQL просматривает сам индекс, чтобы завершить сортировку. Метод FileSort менее эффективен.

5.4.3 ORDER BY будет использовать метод Index для сортировки

  • Оператор ORDER BY использует первый крайний левый столбец индекса.
EXPLAIN SELECT * FROM tbla WHERE age > 1 ORDER BY age
  • Используйте комбинацию условного столбца предложения Where и предложения Order BY, чтобы удовлетворить самый левый передний столбец индекса.
EXPLAIN SELECT * FROM tbla WHERE age = 1 ORDER BY birth
  • Если запрос диапазона индекса появляется в предложении where (то есть диапазон появляется в объяснении), порядок по индексу будет недействительным.
  • Сортировка по составному условию, в соответствии с порядком индекса, категории сортировки до и после несовместимы, что приведет к сбою сортировки по индексу.

5.5 ГРУППИРОВКА ПО оптимизации

5.5.1 Описание

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

5.5.2 Примечание

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

5.6 предельная оптимизация

5.6.1 Описание

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

5.6.2 Оптимизация

  • Шаг 1. Используйте индексированный столбец или первичный ключ для операции «Упорядочить по»
  • Шаг 2. Запишите первичный ключ, возвращенный в последний раз, и используйте аннотации для фильтрации в следующем запросе (если первичный ключ не является непрерывным, это строковый тип, вы можете создать запись столбца)

6. Резюме

  • Полная стоимость соответствует моему любимому
  • Правило наилучшего левого префикса (если индексируется несколько столбцов, следуйте правилу самого левого префикса. Это означает, что запрос начинается с самого левого переднего столбца индекса и не пропускает столбцы в индексе)
  • Не выполняйте никаких операций (расчет, функция, (автоматическое или ручное) преобразование типа) в столбце индекса, что приведет к сбою индекса и переходу к полному сканированию таблицы.
  • Механизм хранения не может использовать столбец справа от условия диапазона в индексе.
  • Попробуйте использовать покрывающий индекс (запрос, который обращается только к индексу (столбец индекса согласуется со столбцом запроса)) для уменьшения выбора *
  • MySQL не может использовать индексы при использовании не равно (!= или ), что приведет к полному сканированию таблицы.
  • не является нулевым, не может использовать индекс, но является нулевым, может использовать индекс
  • Например, начинается с подстановочного знака ('%abc...') Ошибка индекса MySQL станет операцией полного сканирования таблицы.
  • Строковый индекс без одинарных кавычек не работает
  • Используйте или меньше, индекс будет недействительным при использовании его для подключения
Категории