Переведите, как называется TMD. ОБЪЯСНИТЕ

Вы переводите для переводчика, что за сюрприз, что за хрень сюрприз!

- "Пусть пули летят"

Всем привет, я Йи Йи, программист, который мечтает бросить код и писать!

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

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

Тревожный до смерти.

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

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

EXPLAIN

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

Оптимизированный план выполнения называетсяПлан реализации.

EXPLAINФункция заключается в просмотре плана выполнения, который очень прост в использовании, будь тоselect insert update delete, все только нужно добавить впередиexplain.

-- items : 商品主数据表
explain select * from items;

Результаты после выполнения следующие (для удобства просмотра используется древовидная структура для отображения):

Слева находится название столбца плана выполнения.Ключом к нашему обучению является знание значения каждого столбца.

Справа соответствующее значение.В реальной разработке проблема оператора sql диагностируется путем анализа значения.

Понять план выполнения

id

selectКак понять порядок выполнения? См. следующий sql:

  explain
  select *
  from items_img
  where item_id =
        (select id from items where item_name like '蛋糕');

Есть два запроса, какой из них выполняется первым, можно судить по id:

• Чем больше id, тем выше приоритет и выполнение первым.
• В случае одного и того же идентификатора порядок выполнения сверху вниз.

Подтвердите это:

можно увидетьid=2,соответствоватьitemsтаблица, выполнить в первую очередь.

select_type

Посмотрите на выходные результаты и обнаружите, что подзапросselect_typeЗначения разные, что они означают?

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

PRIMARYозначает, что запрос содержит подзапросы, и запрос находится на самом внешнем уровне, иSUBQUERYЭто переводит на подзапросы. вышеSIMPLEЭто самый распространенный и простой запрос.

Есть и другие значения:

• DERIVED: указывает вfromсодержит подзапросы
• UNION: для больших запросов, содержащих UNION или UNION ALL, все запросы, кроме самого внешнего, будут помечены какPRIMARY, остальные будут помечены какUNION.
• UNION RESULT: представляет временную таблицу в запросе UNION.
• MATERIALIZED:INилиEXISTSпоследующий запрос.

Дополнительные инструкции:

MATERIALIZEDПреобразование означает материализацию, то есть процесс сохранения записей в результирующем наборе подзапроса во временную таблицу.

Временные таблицы называются материализованными таблицами. Именно потому, что записи в материализованной таблице индексируются (материализованная таблица в памяти имеет хэш-индекс, а дисковая — индекс дерева B+), которая выполняется через индекс.INОператор очень быстро определяет, отсутствует ли операнд в результирующем наборе подзапроса, тем самым повышая производительность оператора подзапроса.

table

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

partitions

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

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

Тип раздела:

• RANGEРаздел: Раздел в соответствии с заданным непрерывным интервалом. Особенно полезно при удалении старых данных.
• LISTРаздел: Раздел в соответствии с конкретными значениями. Предположим, что определенный товар продается в трех театрах Восточного Китая, Центрального Китая и Северного Китая, согласно театральному подразделению, вwhereВам нужно только указать раздел при запросе.
• HASHРазбиение: Разбиение выполняется по модулю фиксированного целого числа, что требует более равномерного распределения данных.HashПерегородки также существуют с традиционнымиHashТа же проблема с подтаблицами, плохая масштабируемость.MySQLтакже обеспечивает консистенцию, подобнуюHashМетод разделения - ЛинейныйHashРаздел, нужно только добавить при определении разделаLINEARключевые слова.
• KEYПерегородка: сHashРазделы похожи, толькоHashфункция другая.

См. пример создания секционированной таблицы:

 -- 创建user表
 create table user_partitions (
   id int auto_increment, 
   name varchar(12),
   primary key(id)
 )
 -- 按照id分区，id<100 p0分区，其他p1分区
 partition by range(id)(
     partition p0 values less than(100),
     partition p1 values less than maxvalue
 );

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

type

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

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery> range > index>all

Просто запомните упрощенную версию:

system > const > eq_ref > ref > range > index>all

Производственная среда обычно должна обеспечиватьrefилиrangeуровень. Представляем вам по очереди:

system: В таблице есть только одна строка записей (равная системной таблице), которая обычно не отображается.

const: нашел по индексу один раз.

Пример:

explain
    select * from items where id = 'bingan-1001';

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

ref: сканирование неуникального индекса, которое возвращает все строки, соответствующие одному значению.

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

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

explain
    select * from items where sell_counts = 3308;

range: Получить только указанный диапазон строк.

Или столбец продаж, sql выглядит следующим образом:

explain
    select * from items where sell_counts between 3000 and 10000;

index: Когда результатами запроса являются все проиндексированные столбцы.

explain
    select id,sell_counts from items;

all: полное сканирование таблицы

explain
    select * from items where sell_counts;

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

explain select min(id) from items;

tableОн также пуст, что указывает на то, что проверяется только индекс.

possible_keys

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

Если вы хотите принудительно использовать или не использовать индекс, вы можете использовать в запросеforce index,ignore index.

key

Фактический используемый индекс.

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

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

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

key_len

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

Посмотрите на случай:

Создать новый совместный индекс

Выполните следующий sql

explain
    select * from items where sell_counts=300;

взгляните на результаты

Очевидно, используется совместный индекс, но какой столбец используется конкретно, выясняется, чтоken_len4, ровно одинintДлина типа также используется толькоsell_countsэтот столбец.

изменить sql

explain
    select * from items where sell_counts=300 and item_name='好吃蛋糕甜点蒸蛋糕';

Результаты

Индекс не меняется,ken_lenстать134, как это пришло?

надо сначала посмотретьitem_nameДлина32.

Также нужно знать, что такое кодировка символов?show variables like 'character%';

utf8mb4Что это?Проще говоря, это настоящий MySQLutf8, а в MySQLutf8является «собственной кодировкой», которая может кодироватьUnicodeНе много персонажей.

На самом деле это ошибка в MySQL.utf8mb4находится в ремонте.

Ближе к началу используйте длину поля * закодированные байты = общее количество байтов. который32*4=128. (latin1 занимает один байт,gbk занимает два байта,utf-8занимает три байта).

Но это еще не конец, потому чтоvarcharимеет переменную длину и также требует два бита для хранения реальной длины. Добавь этоintчетыре байта, всего134, отсюда следует, чтоsell_contsиitem_nameдве колонки (128+2+4=134).

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

ref

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

explain
    select * from items i where id = 'cake-1001';

это константа

explain
    select * from items i left join category c
    on c.id = i.cat_id;

это столбец

rows

Примерное количество строк для чтения.

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

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

explain
    select * from items i where sell_counts >  100;

filtered

Процентная оценка количества строк, отфильтрованных по условию таблицы.

Extra

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

• Using index: Столбец запроса покрывается индексом, то есть используется покрывающий индекс, что будет очень быстро.
• Using where: указывает, где используется фильтрация.
• Using where Using index: запрошенный столбец охватывается индексом, но не является ведущим столбцом (первым столбцом) индекса.
• NULL: Столбец запроса не охватывается индексом, а условием фильтрации является начальный столбец индекса. То есть индекс используется, но его недостаточно, нужно вернуться к таблице (сначала получить id, а через id еще раз проверить)
• Using index condition: Столбцы запроса не полностью покрываются индексом, а условие where представляет собой диапазон ведущих столбцов.
• Using temporary: Используются временные таблицы, такие как дедупликация и группировка.
• Using filesort: столбец сортировки не индексируется.
• Using join buffer (Block Nested Loop): Во время связанного запроса, когда управляемая таблица не имеет индекса, MySQL обычно выделяет блок с именемjoin bufferБлок памяти для увеличения скорости запросов, который мы называем блочным алгоритмом вложенного цикла.

explain
    select * from items i left join category c
    on c.name = i.item_name;

Также будетNo tables used(без предложения from) и так далее.

Суммировать

хорошо, все столбцы EXPLAIN были обсуждены, так что давайте подытожим:

имя столбца	значение
id	исполнительный лист
select_type	тип запроса
table	таблица используется
partitions	используемый раздел
type	тип доступа
possible_keys	возможные индексы
key	фактический индекс
key_len	количество байтов, используемых для индексации
ref	значение, соответствующее индексированному столбцу
rows	Расчетное количество просканированных строк
filtered	Коэффициент экранирования
Extra	Дополнительная дополнительная информация

Наконец

На данный момент уже не проблема стать лидером рек и озер.Если вы хотите стать известным доктором SQL-оптимизации, вам нужно увидеть метод перемещения цветов и деревьев в следующем разделе. Быть в курсе!

Нажмите, чтобы увидеть! Нажмите, чтобы увидеть! Также TMD смотрит!