Ставьте лайк и смотрите снова, формируйте привычку, ищите в WeChat【Третий принц Ао Бин] Обратите внимание на этот инструмент человека, который борется за выживание в Интернете.
эта статьяGitHub github.com/JavaFamilyВключено, и есть полные тестовые площадки, материалы и мой цикл статей для интервью с производителями первой линии.
предисловие
Интервьюер: Ао Бинг, в вашем резюме сказано, что вы умеете настраивать базу данных.
Ао Бинг: добавьте файл index.
Интервьюер: Еще?
Ао Бин: Хватит.
Интервьюер: Вы знаете, где дверь нашей компании, идите сами или я вас провожу?
Ха-ха, эта сцена в начале - это сцена интервью, которую я себе представлял, но вы думаете, что это очень реально? Каждый резюме будет писать предложение в конце всякий раз, когда база данных будет написана, и сможет оптимизировать базу данных.
Но возникает вопрос: когда интервьюер спрашивает о настройке базы данных, все говорят о добавлении индексов. Вы знаете что-нибудь, кроме добавления индексов?
Или вы знаете все точки, связанные с индексом? Какова максимальная TPS для кластеризованного индекса, некластеризованного индекса, общего индекса, уникального индекса, буфера изменений, блокировки таблицы, блокировки строки, блокировки промежутка и блокировки строки одновременно? И почему индекс выбран неправильно? Вы все это знаете?
Я думаю, что есть еще много моментов, на которые можно ответить с помощью настройки.После прочтения «MySQL Практический», «Высокопроизводительный MySQL» и «Лекции Ding Qi MySQL 47», я подытожил набор логики для моего интервью и ответа. Лично я думаю, что это относительно хороший набор комбо, этот набор даже набирается, и интервьюер тайком поставит вам большой палец вверх Во всяком случае, это в основном набор, когда я брал интервью.
Содержание представляет собой резюме личного понимания, а также пересказ содержания в книге, что требует определенных знаний базы данных, но я думаю, что все нажали, и они обязательно смогут.
текст
По сути настройка базы данных это вообще наша настройка SQL.Настройка SQL может решить большую часть проблем.Конечно не исключена настройка выполнения SQL.
Я представил соответствующие базовые знания об основах индексов и баз данных ранее, поэтому я не буду здесь вдаваться в подробности, но состав базы данных может быть забыт многими небольшими партнерами, поэтому давайте еще раз посмотрим на структурную диаграмму.
Наша так называемая настройка выполняется на этапах анализатора и оптимизатора до выполнения исполнителя Итак, как мы настраиваем нашу работу по разработке?
Шуай Бинг обычно обращается к локальной среде для запуска SQL при разработке бизнеса, связанного с SQL, и использует объяснение, чтобы проверить план выполнения, чтобы увидеть, соответствуют ли результаты анализа его ожиданиям, использовать соответствующие индексы, если они не используются, а затем перейти к Запустите его, чтобы увидеть время выполнения (здесь только операторы запросов, а операторы модификации не могут быть выполнены онлайн).
В случае с SQL я не решаюсь объяснять, но вот первая яма.
Устранение помех кэша
Потому что до MySQL 8.0 в нашей базе был кеш.Я раньше кастрировал.Из-за кеша я обнаружил, что мой sql выполнялся очень быстро.Конечно, не быстро в первый раз, но я не заметьте это. , так что после выхода в интернет из-за того, что кеш часто становится недействительным, rt (время отклика) высокое, а иногда и низкое.
Позже выяснилось, что это была проблема с кешем.Когда мы выполняем SQL, не забудьте добавить SQL NoCache для запуска SQL, чтобы время выполнения было реальным временем выполнения запроса.
Позвольте мне рассказать о том, почему кеш недействителен, а он часто недействителен.
Если наша текущая версия MySQL поддерживает кеширование и мы включаем кеширование, оператор запроса и результат каждого запроса будут кэшироваться в памяти в виде ключ-значение.Вы также можете увидеть нашу структурную диаграмму, запрос сначала проверит, существует ли кеш , и если он не существует, будет использоваться парсер.
Причина, по которой аннулирование кеша происходит чаще, заключается в том, что пока мы обновляем пару таблиц, все кеши этой таблицы будут очищены.На самом деле, у нас редко есть таблицы, которые не обновляются, особенно в моем предыдущем сценарии электронной коммерции. , может и статические таблицы можно использовать К кешу, но мы все используем оффлайн анализ больших данных, кеш бесполезен.
Если у вас версия выше 8.0, вам не нужно беспокоиться об этой проблеме.Если это версия ниже 8.0, не забудьте устранить помехи кеша.
Explain
В начале я упомянул об использовании плана выполнения для анализа, думаю, объяснить ответит каждый, кто занимается настройкой SQL.
Поскольку это в основном необходимая операция для написания SQL, то я сейчас задам вам вопрос, который мне задали, когда я ходил на Али на собеседование:Объясните, какие поля вы помните и каково их значение?
На тот момент я ответил на три вопроса.По умолчанию у всех есть база БД,поэтому я не буду здесь подробно описывать каждое поле.Боюсь что все забудут меня выложить картинку и вспомнить ее для себя.
Тогда позвольте мне еще раз спросить вас, как вы считаете, правильно ли подсчитывать количество строк в этой статистике? Обязательно ли индекс пойдет к оптимальному индексу?
Конечно, я уже задавал этот вопрос, и вы должны знать результат.Количество строк - это только близкое число, и оно не совсем правильное.Индекс не обязательно лучший, и он может быть неправильным.
Мое общее количество строк составляет около 10 Вт, но когда я использую объяснение для анализа sql, я обнаружу, что получаю только 9,4 Вт. Почему количество строк является недальновидным значением?
Друзья, которые читали основные главы, знают, что единицей данных в MySQL является страница, и MySQL использует метод выборочной статистики.При выборочной статистике InnoDB по умолчанию выберет N страниц данных и подсчитает различные значения на этих страницах. , Возьмите среднее значение и умножьте на количество страниц в этом индексе, чтобы получить мощность этого индекса.
Наши данные постоянно меняются, поэтому статистика индекса тоже будет меняться, и статистика будет пересчитываться в соответствии с порогом.
Также легко понять, что индекс MySQL может пойти не так: если индексу A нужно просмотреть 100 строк, то индексу B нужно только 20 строк, но он может выбрать использование индекса A. Вы можете задаться вопросом, не больна ли MySQL, но это не так.
Как правило, неправильный путь заключается в том, что оптимизатор обнаруживает, что использование индекса A не требует дополнительных затрат. Например, индекс B не может напрямую получить наше значение, и нам нужно вернуться к индексу первичного ключа, чтобы получить его. подробнее return Процесс таблицы, это тоже будет учитываться оптимизатором.
Он обнаружил, что нет необходимости возвращаться к таблице, чтобы использовать индекс А, и нет дополнительных накладных расходов, поэтому он выбрал не тот.
Если приведенная выше статистическая информация неверна, это просто. Мы можем использовать проанализировать таблицу tablename для повторной статистической информации индекса. Поэтому на практике, если вы обнаружите, что оценочное значение строк результата объяснения сильно отличается от фактической ситуации, вы можете использовать этот метод для обработки.
Другой метод - заставить индекс принудительно использовать правильный индекс или оптимизировать SQL. В конце концов, это не работает. Вы можете создать новый индекс или удалить неправильный индекс.
индекс покрытия
Как я упоминал выше, могут потребоваться такие операции, как возврат формы, так как же нам не вернуть форму? Вы можете найти то, что хотите, в своем собственном индексе, не переходите к индексу первичного ключа для проверки.
индекс покрытия
Если в индексе, который мы установили, уже есть нужные нам поля, нет необходимости возвращать таблицу.Это также очень распространено в электронной коммерции.Нам нужно перейти к таблице товаров, чтобы запросить идентификатор товара с помощью различной информации. .Идентификатор обычно является первичным ключом. ,может быть sql такой:
select itemId from itemCenter where size between 1 and 6
Поскольку идентификатор товара itemId обычно является первичным ключом, индекс размера определенно будет иметь наше значение.В настоящее время нет необходимости возвращаться к таблице первичного ключа для запроса информации об идентификаторе.
Поскольку покрывающий индекс может сократить количество операций поиска в дереве и значительно повысить производительность запросов, использование покрывающего индекса является распространенным методом оптимизации производительности.
совместный индекс
Возьмем в качестве примера таблицу продуктов. Нам нужно проверить его инвентарь на основе его имени. Предположим, это очень частый запрос, как бы вы построили индекс?
Вы можете оптимизировать SQL, подумав о потреблении приведенной выше таблицы возврата.
Да, построить совместный индекс имени и инвентаря, чтобы можно было найти имя и увидеть инвентарь.Не нужно искать идентификатор и возвращаться к таблице, чтобы запросить инвентарь.Совместный индекс также распространен в нашем процессе разработки, но это не может быть установлено все время Нам нужно думать о пространстве, занимаемом индексом.
Пример, который я только что привел, на самом деле немного тупой: обычно запросов на запрос инвентаризации через название продукта не так много, но это не значит, что нет, неужели мы идем на полное сканирование таблицы?
Крайний левый принцип соответствия
Когда вы пишете SQL, лучше всего использовать существующий SQL, чтобы максимизировать использование.Как и в приведенном выше сценарии, если вы используете нечеткое имя элемента запроса, например «Ao Bing%», вы все равно можете использовать этот индекс, и если есть с таким совместный указатель, нет необходимости создавать отдельный указатель для товарных наименований.
Во многих случаях наш индекс может быть построен неправильно, тогда, если вы отрегулируете порядок, вы сможете оптимизировать весь SQL.
выталкивание индекса
Вы уже знаете правила префиксного индекса, тогда я расскажу об официальной оптимизации для нас, проталкивании индекса.
select * from itemcenter where name like '敖%' and size=22 and age = 20;
Таким образом, когда этот оператор выполняет поиск в дереве индексов, он может использовать только «Ao», чтобы найти первую запись с идентификатором ID1, которая удовлетворяет условию.Конечно, это неплохо, это лучше, чем полное сканирование таблицы.
Тогда что?
Конечно, это нужно для того, чтобы определить, соблюдаются ли другие условия, например, размер.
До MySQL 5.6 вы могли возвращаться к таблице только один за другим из ID1, находить строку данных в индексе первичного ключа, а затем сравнивать значения полей.
Оптимизация выталкивания условия индекса, представленная в MySQL 5.6, может сначала оценить поля, содержащиеся в индексе, во время процесса обхода индекса и напрямую отфильтровать записи, которые не соответствуют условиям, уменьшая количество возвратов к таблице.
Проблема выбора общего индекса уникального индекса
На самом деле этот вопрос несколько раз задавался в моем видео-интервью.Суть в том, чтобы ответить буферу изменений, так что же такое буфер изменений?
Когда страницу данных необходимо обновить, если страница данных находится в памяти, она будет обновлена напрямую, а если страница данных не находится в памяти, InooDB будет кэшировать эти операции обновления в буфере изменений, не влияя на согласованность данных, так что страницу данных не нужно читать с диска.
Когда следующему запросу требуется доступ к странице данных, страница данных считывается в память, а затем выполняются операции, связанные со страницей в буфере изменений.Таким образом может быть гарантирована правильность логики данных.
Следует отметить, что хотя имя называется буфером изменений, на самом деле это постоянные данные. То есть буфер изменений имеет копию в памяти и также записывается на диск.
Процесс применения операций в буфере изменений к исходной странице данных и получения последнего результата называется слиянием.
В дополнение к запуску слияния при доступе к этой странице данных в системе есть фоновый поток, который периодически сливается. Операция слияния также выполняется во время нормального завершения работы базы данных.
Очевидно, что если операцию обновления можно сначала записать в буфер изменений, чтобы уменьшить количество операций чтения с диска, скорость выполнения оператора будет значительно улучшена. Кроме того, чтение данных в память должно занимать пул буферов, поэтому этот метод также позволяет избежать занимания памяти и улучшить использование памяти.
Так,При каких условиях можно использовать буфер изменений?
Для уникального индекса все операции обновления должны сначала определить, нарушает ли операция ограничение уникальности.
Чтобы судить, существуют ли эти данные в таблице, необходимо прочитать страницу данных в память, чтобы судить.Если она была прочитана в память, будет быстрее обновить память напрямую, и нет необходимости использовать буфер изменений.
Следовательно, обновление уникального индекса не может использовать буфер изменений, фактически можно использовать только обычный индекс.
Буфер изменений использует память в пуле буферов, поэтому его нельзя увеличивать бесконечно. Размер буфера изменений может быть установлен динамически через параметр innodb_change_buffer_max_size. Когда этот параметр установлен на 50, это означает, что размер буфера изменений может занимать не более 50% пула буферов.
Чтение данных с диска в память включает произвольный доступ ввода-вывода, который является одной из самых дорогостоящих операций в базе данных.Поскольку буфер изменений уменьшает произвольный доступ к диску, производительность обновления будет значительно улучшена.
Изменить сценарии использования буфера
Поскольку время слияния — это время, когда данные фактически обновляются, а основная цель буфера изменений — кэшировать записанные изменения, поэтому перед слиянием страницы данных чем больше изменений записано в буфере изменений (т. е. изменения на этой странице нужно фиксировать) Чем больше обновлений), тем больше выгода.
Следовательно, для бизнеса, когда нужно больше писать и меньше читать, вероятность доступа к странице сразу после записи относительно мала. В настоящее время лучше всего использовать буфер изменений. Этот тип бизнес-модели обычно используется в биллинге и лог-системы. .
И наоборот, если бизнес-режим обновления заключается в запросе сразу после записи, то даже если условия соблюдены, обновление будет сначала записано в буфере изменений, но затем немедленно будет запущен процесс слияния, поскольку страница данных будет доступ скоро. Таким образом, количество случайных обращений к IO не уменьшится, но увеличит стоимость обслуживания буфера изменений, поэтому для данной бизнес-модели буфер изменений имеет побочный эффект.
индекс префикса
У нас ситуация когда в качестве имени пользователя используется почтовый ящик.Почтовые ящики у всех разные.Можем ли мы построить индекс на ящике,но ящик такой длинный,как мы можем построить индекс?
MySQL поддерживает префиксные индексы, то есть вы можете определить часть строки как индекс. По умолчанию, если вы создаете индекс без указания длины префикса, индекс будет содержать всю строку.
Можем ли мы создать очень дискриминационный префиксный индекс для оптимизации и экономии места?
Использование индекса префикса и определение длины может сэкономить место без увеличения дополнительных затрат на запрос.
Как было сказано выше, покрывающий индекс не нужно возвращать в таблицу, а префиксный индекс, даже если ваш совместный индекс уже содержит релевантную информацию, он все равно вернет таблицу, потому что он не уверен, есть ли у вас полная информация. Даже если вы проверите полный адрес электронной почты www.aobing@mogu.com, он все равно не знает, заполнены ли вы, поэтому ему нужно вернуться к форме, чтобы судить.
Еще один вопрос, который интервьюер спросил у меня в интервью с Али: это очень длинное поле, как мы можем оптимизировать его, если мы хотим его индексировать?
Поскольку существует проблема занятости диска, чем дольше выбран индекс, тем больше места на диске он занимает, тем меньше значений индекса можно разместить на одной и той же странице данных, и тем ниже будет эффективность поиска.
В то время я ответил на хеш и сохранил хэш поля как другое поле.Хорошо каждый раз проверять хеш, а хэш-индекс не велик.
Мы все знаем, что пока различие слишком велико, мы можем использовать обратный порядок или удалить строку, чтобы установить собственное различие, но всем следует обратить внимание на то, что вызов функции также является накладным, этот момент не выполнялся. t заметить в то время.
Например, изначально это был www.aobing@qq,com. Фактически, предыдущийwww.В принципе, разницы нет.Почтовые ящики у всех начинаются так.Если много искать и помещать их в индекс, это будет тратить память.Вы можете перехватить предыдущие с помощью функции substring(), а затем построить индекс.
Все наши удостоверения личности начинаются с региона, и в одном и том же регионе проживает много людей. Если функция REVERSE() перевернута, степень дискриминации может быть выше.
Все эти операции используют функции, поэтому позвольте мне рассказать о ямах функций.
Поле условия Функция Операция
В процессе ежедневной разработки люди часто выполняют операции над многими полями. не использовать Это правила MySQL.
Выполнение функциональных операций над полями индекса может нарушить порядок значений индекса, поэтому оптимизатор решает отказаться от функции поиска по дереву.
Следует отметить, что оптимизатор не пытается отказаться от использования этого индекса.
В это время можно использовать некоторые хитрые методы, такие как select * from tradelog где id+1=10000 не сможет войти в индекс, и select * from tradelog где id=9999 подойдет.
неявное преобразование типов
select * from t where id = 1
Если id — это символьный тип, а 1 — числовой тип, вы обнаружите, что после полного сканирования таблицы с объяснением индекс вообще не используется, почему?
Поскольку нижний уровень MySQL преобразует ваше сравнение, что эквивалентно добавлению такой функции, как CAST ( id AS signed int ), как упоминалось выше, эта функция приведет к сбою индекса.
Неявное преобразование кодировки символов
Все та же проблема, если наборы символов двух таблиц разные, один - utf8mb4, а другой - utf8, потому что utf8mb4 является надмножеством utf8, поэтому после сравнения двух символов они будут преобразованы в utf8mb4, а затем сравнены .
Процесс преобразования эквивалентен добавлению функции CONVERT (id USING utf8mb4), затем вернемся к вышеуказанной проблеме: при использовании функции индекс не используется.
Кроме того, вы можете столкнуться с внезапным зависанием mysql на некоторое время, что может привести к сбросу MySQL.
flush
Каждый знает журнал повторов, то есть журнал операций нашей базы данных, который находится в памяти.Как только журнал повторов будет написан для каждой операции, результат будет возвращен немедленно, но журнал повторов всегда найдет время, чтобы обновиться до диск.Эта операция выполняется на одном уровне.
Перед обновлением, когда страница данных памяти не соответствует содержимому страницы данных диска, мы называем эту страницу памяти «грязной страницей».
После записи данных памяти на диск содержимое страниц данных в памяти и на диске совпадает, что называется «чистой страницей».
Когда смоется?
-
Журнал повторов InnoDB заполнен.В это время система остановит все операции обновления, переместит контрольную точку вперед и оставит место для журнала повторов для продолжения записи.
-
Недостаточно системной памяти Когда требуется новая страница памяти, а памяти недостаточно, некоторые страницы данных должны быть удалены, а память должна быть освобождена для использования другими страницами данных. Если «грязная страница» устранена, то грязная страница должна быть записана на диск первой.
Вы должны сказать, что в этот раз мы не можем просто удалить память напрямую, и в следующий раз вам нужно будет запросить, прочитать страницу данных с диска, а затем использовать журнал повторов для ее применения?
Это на самом деле из соображений производительности, если грязная страница сбрасывается, она обязательно будет записана на диск, что гарантирует наличие у каждой страницы данных двух состояний:
- Во-первых, он существует в памяти, и он должен быть правильным результатом в памяти и возвращаться напрямую;
- Другой заключается в том, что в памяти нет данных, вы можете быть уверены, что файл данных является правильным результатом, и вернуть его после чтения в память. Это самое эффективное.
-
Когда MySQL считает, что система простаивает, она будет сбрасывать немного «грязных страниц» всякий раз, когда есть шанс.
-
MySQL нормально выключается.В это время MySQL сбрасывает все грязные страницы памяти на диск, чтобы при следующем запуске MySQL мог считывать данные прямо с диска, и скорость запуска будет очень быстрой.
Итак, что мы можем сделать, чтобы понять время флеша?
Стратегия управления очисткой грязных страниц Innodb, каждый из наших компьютерных хостов имеет разные возможности ввода-вывода.Вы должны правильно указать InnoDB возможности ввода-вывода хоста, на котором находится InnoDB, чтобы InnoDB мог знать, как быстро он может сбрасывать грязные страницы, когда это необходимо. это лучшее.
При этом будет использоваться параметр innodb_io_capacity, который сообщит InnoDB емкость вашего диска. Рекомендуется установить это значение равным IOPS диска. IOPS диска можно проверить с помощью инструмента fio.
Правильная установка параметра innodb_io_capacity может эффективно решить эту проблему.
Посередине есть интересный момент.При чистке грязных страниц, если рядом с ними есть грязные страницы, они будут счищаться вместе, а если вокруг есть грязные страницы, эта система совместной ответственности будет продолжать распространяться.Эта ситуация на самом деле в эпоху механических жестких дисков. Лучше один IO решает все проблемы,
Но теперь они все твердотельные накопители Параметр innodb_flush_neighbors=0 не может генерировать совместную систему В MySQL 8.0 значение параметра innodb_flush_neighbors по умолчанию уже равно 0.
Ссылки: «Практика MySQL», «Высокопроизводительный MySQL», «Лекции Ding Qi MySQL 47»
Суммировать
В этой статье я упомянул следующие точки знаний:
Он не должен быть полным, блокировки строк, блокировки таблиц, блокировки промежутков, сценарии синхронизации и т. Д. Много не упоминаются, потому что их сценарии более сложны, каждый может быть написан отдельно, MySQL Дин Ци очень всеобъемлющий. -производительность MySQL, можете глянуть, если лень, можете подождать, пока я подытожу.
Я не объяснял подробно каждый пункт, в основном из-за недостатка места. На самом деле, каждый пункт был представлен в большом количестве книг, связанных с MySQL. Я сделаю резюме. Если вы не понимаете конкретную концепцию, вы можно использовать поисковую систему для запроса. Связанные концепции, но я думаю, что выразился довольно прямо.
Эта статья, Ао Бин, уже больше недели лежит в печени.В основном она для того, чтобы разобраться в пунктах знаний, потому что я ее почти забыл.Оглянулся назад и обобщил ее еще раз, а предыдущие заметки все еще там. У меня все еще есть эта статья. Если вам она не нравится, все думают, что можно нажать на нее и посмотреть, хорошо?
Я Ао Бин, программист, живу в Интернете.
Чем больше вы знаете, тем больше вы не знаете,талантнаш【Три подряд】Это самая большая движущая сила для создания Bing Bing, увидимся в следующем выпуске!
Примечание. Если в этом блоге есть какие-либо ошибки и предложения, оставьте сообщение!
Статья постоянно обновляется, вы можете искать в WeChat "Третий принц Ао Бин"Прочтите это в первый раз, ответьте [материал] Подготовленные мной материалы интервью и шаблоны резюме крупных заводов первой линии, эта статьяGitHub github.com/JavaFamilyОн был включен, и есть полные тестовые сайты для интервью с крупными заводами.Добро пожаловать в Star.