35 фотографий для настройки MySQL

задняя часть MySQL

Это четвертая статья из серии «Основы MySQL», ссылки на предыдущие три статьи приведены ниже.

138 картинок, которые познакомят вас с MySQL

47 фотографий сделают вас продвинутым в MySQL! ! !

Взрыв! Фотографии MySQL 82 отправят вас в полет!

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

Шаги оптимизации SQL

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

Узнайте количество выполнений SQL с помощью команды show status

Во-первых, мы можем использоватьshow statusКоманда для просмотра информации о состоянии сервера. Команда show status отображает имя и значение каждой серверной переменной variable_name.Переменные состояния доступны только для чтения. При использовании команд SQL вы можете использовать условия like или where, чтобы ограничить результаты. like может выполнять стандартное сопоставление с образцом для имен переменных.

Скриншот не делал, внизу много переменных, читатели могут сами попробовать. Также доступно в ОСmysqladmin extended-statusкоманда для получения этих сообщений.

Но после того, как я выполняю расширенный статус mysqladmin, я получаю эту ошибку.

Это должно быть причиной того, что я не ввел пароль, используйтеmysqladmin -P3306 -uroot -p -h127.0.0.1 -r -i 1 extended-statusПосле этого проблема решена.

Здесь нужно обратить внимание на уровень статистики, который можно добавить в команду show status, в этом уровне есть два уровня.

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

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

Для статистических результатов, запрашиваемых по статусу показа, есть два типа параметров, на которые необходимо обратить внимание.Com_Параметры начинаются с , класс начинается сInnodb_параметр в начале.

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

Com_xxx представляет количество выполнений каждого оператора xxx.Обычно мы заботимся о количестве выполнения операторов select , insert , update и delete , то есть

  • Com_select: количество раз для выполнения операций выбора, запрос приведет к +1.
  • Com_insert: количество раз выполнения операций INSERT.Для операций пакетной вставки INSERT накапливается только один раз.
  • Com_update: сколько раз выполнялась операция UPDATE.
  • Com_delete: сколько раз выполнялась операция DELETE.

Параметры, начинающиеся с Innodb_, в основном

  • Innodb_rows_read: количество строк, возвращенных при выполнении запроса на выборку.
  • Innodb_rows_inserted: количество строк, вставленных операцией INSERT.
  • Innodb_rows_updated: количество строк, обновленных операцией UPDATE.
  • Innodb_rows_deleted: количество строк, удаленных операцией DELETE.

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

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

  • Соединения: количество соединений для запроса к базе данных MySQL.Это число подсчитывается независимо от того, было ли соединение успешным или нет.
  • Аптайм: Время работы сервера.
  • Slow_queries: количество полных запросов.
  • Threads_connected: просмотр количества открытых в данный момент подключений.

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

blog.CSDN.net/Aiyaya_870621…

Найдите SQL, который выполняется менее эффективно

Как правило, есть два способа найти операторы SQL с низкой эффективностью выполнения.

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

MySQL предоставляет функцию ведения журнала медленных запросов, которая может записывать в журнал медленных запросов время запроса SQL, превышающее количество секунд.При ежедневном обслуживании записанная информация журнала медленных запросов может использоваться для быстрого и точного определения проблемы. При запуске с параметром --log-slow-queries mysqld записывает файл журнала, содержащий все операторы SQL, которые выполнялись дольше, чем long_query_time секунд, и находит неэффективный SQL, просматривая этот файл журнала.

Например, мы можем добавить следующий код в my.cnf, затем выйти и перезапустить MySQL.

log-slow-queries = /tmp/mysql-slow.log
long_query_time = 2

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

Также его можно включить командой:

Давайте сначала проверим, включен ли журнал медленных запросов MySQL.

show variables like "%slow%";

Включить журнал медленных запросов

set global slow_query_log='ON';

Затем снова проверьте, включен ли медленный запрос

Как показано на рисунке, мы включили журнал медленных запросов.

Журнал медленных запросов будет записан после завершения запроса, поэтому, когда возникает проблема с эффективностью выполнения ответа приложения, журнал медленных запросов не может обнаружить проблему, и вы должны использовать его в это время.show processlistКоманда для просмотра потоков, которые в настоящее время выполняются в MySQL. В том числе статус потока, блокировать ли таблицу и т. д., вы можете просматривать выполнение SQL в режиме реального времени. Аналогичным образом используйтеmysqladmin processlistОператоры также могут получить эту информацию.

Объясним понятия, соответствующие каждому полю.

  • Id: Id — это идентификатор, который полезен, когда мы используем команду kill для уничтожения процесса, например номер процесса уничтожения.
  • Пользователь: Отобразить текущего пользователя, если это не root, эта команда будет отображать только операторы SQL в пределах ваших полномочий.
  • Хост: отображать IP-адрес для отслеживания проблем
  • Db: показывает, к какой базе данных в данный момент подключен этот процесс. Если значение равно null, выбранной базы данных нет.
  • Команда: Отображает команду, выполняемую текущей блокировкой соединения.Обычно существует три типа: запрос запроса, сон, сон и соединение, соединение.
  • Время: продолжительность этого состояния в секундах.
  • Состояние: отображает состояние текущего оператора SQL, что очень важно и будет подробно объяснено ниже.
  • Информация: Показать этот оператор SQL.

Столбец State очень важен. Об этом столбце много информации. Читатели могут обратиться к этой статье.

blog.CSDN.net/WeChat_3435…

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

Проанализируйте план выполнения SQL с помощью команды EXPLAIN.

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

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

explain select * from test1;

Содержание приведенной выше таблицы следующее

  • select_type: указывает распространенные типы SELECT, такие как SIMPLE. SIMPLE представляет простую инструкцию SQL, за исключением операций UNION или подзапроса. Например, следующий абзац относится к типу SIMPLE.

PRIMARY , самый внешний SELECT в запросе (например, UNION между двумя таблицами или PRIMARY для внешней таблицы с подзапросом, UNION для внутренней таблицы), например следующий подзапрос.

UNION, в операции UNION внутренний SELECT в запросе (когда внутренний оператор SELECT не имеет зависимости от внешнего оператора SELECT).

ПОДЗАПРОС: первый SELECT в подзапросе (если имеется несколько подзапросов), например, в нашем запросе выше, первый подзапрос — это таблица sr (sys_role), поэтому ее тип select_type — SUBQUERY.

  • table , этот параметр указывает таблицу выходного набора результатов.

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

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

    system : когда в таблице есть только одна часть данных, запрос таблицы подобен запросу таблицы констант.

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

    eq-ref : указывает, что первичный ключ таблицы или уникальный индекс таблицы используется при соединении нескольких таблиц, например

    select A.text, B.text where A.ID = B.ID

    В этом операторе запроса для каждой строки идентификатора в таблице A может совпадать только уникальный идентификатор B.Id в таблице B.

    ref : этот тип не так быстр, как описанный выше eq-ref, потому что это означает, что для каждой просканированной строки в таблице A существует несколько возможных строк в таблице C, а C.ID не уникален.

    ref_or_null : аналогично ref, за исключением того, что этот параметр включает запрос для NULL.

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

    unique_subquery : этот параметр часто используется после ключевого слова in, а подзапрос имеет подзапрос с ключевым словом where, которое выражается в sql.

    value IN (SELECT primary_key FROM single_table WHERE some_expr)

    диапазон :запрос диапазона индекса, обычно используемый в запросах с такими операторами, как =, , >, >=, , BETWEEN, IN() и т.п.

    index : Полное сканирование таблицы индекса, сканирование индекса от начала до конца.

    all : это то, с чем мы сталкивались чаще всего, то есть запрос полной таблицы, select * from xxx , с наихудшей производительностью.

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

  • возможных_ключей: указывает индексы, которые могут использоваться при запросе.
  • key : указывает фактический используемый индекс.
  • key_len : длина поля индекса.
  • rows : количество отсканированных строк.
  • filtered : Доля от общего количества строк, занятых количеством запросов SQL, запрошенных условиями запроса.
  • extra : Описание выполнения.

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

показатель

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

Введение индекса

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

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

Давайте сначала разберемся с категориями индексов.

  • 全局索引(FULLTEXT): Глобальный индекс, в настоящее время только механизм MyISAM поддерживает глобальный индекс, он, кажется, решает проблему низкой эффективности нечеткого запроса для текста и ограничен столбцами CHAR, VARCHAR и TEXT.
  • 哈希索引(HASH): Хэш-индекс — это структура данных уникальных пар ключ-значение, используемая в MySQL, которая очень подходит в качестве индекса. HASH-индекс имеет преимущество одноразового позиционирования, ему не нужно искать узел за узлом, как дерево, но такой вид поиска подходит для случая поиска одиночного ключа, для поиска по диапазону, производительности HASH-индекса будет очень низким. По умолчанию механизм хранения MEMORY использует индексы HASH, но индексы BTREE также поддерживаются.
  • B-Tree 索引: B означает Balance, BTree — это сбалансированное дерево, у него много вариантов, самый распространенный — B+ Tree, который широко используется MySQL.
  • R-Tree 索引: R-Tree редко используется в MySQL и поддерживает только тип данных геометрии. Единственными механизмами хранения, которые поддерживают этот тип, являются MyISAM, BDb, InnoDb, NDb и Archive. По сравнению с B-Tree, R-Tree имеет преимущество в области действия. , Найдите.

Логически MySQL подразделяется на следующие категории:

  • Обычный индекс: Обычный индекс — это самый простой тип индекса, он не имеет никаких ограничений. Создано следующим образом

    create index normal_index on cxuan003(id);

метод удаления

drop index normal_index on cxuan003;

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

    create unique index normal_index on cxuan003(id);

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

    CREATE TABLE `table` (
         `id` int(11) NOT NULL AUTO_INCREMENT ,
         `title` char(255) NOT NULL ,
         PRIMARY KEY (`id`)
)

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

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

    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)
);

    Конечно, вы также можете создать глобальный индекс.

    CREATE FULLTEXT INDEX index_content ON article(content)

использование индекса

Индекс может быть создан при создании таблицы, а может быть создан отдельно.Создадим его отдельно.Создаем префиксный индекс на cxuan004

Мы используемexplainДля анализа можно посмотреть, как cxuan004 использует индекс

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

УСЛОВИЯ

Мы создаем составной индекс на основе идентификатора и хэша для cxuan005 следующим образом.

create index id_hash_index on cxuan005(id,hash);

Затем проанализируйте план выполнения в соответствии с идентификатором

explain select * from cxuan005 where id = '333';

Можно обнаружить, что даже если составной индекс (Id, хеш) не используется в условии where, индекс все равно можно использовать, что является префиксной функцией индекса. Но если вы запрашиваете только по хэшу, индекс не будет использоваться.

explain select * from cxuan005 where hash='8fd1f12575f6b39ee7c6d704eb54b353';

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

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

explain select * from cxuan005 where id like '%1';

Мы видим, что если первый символ %, то индекс не используется.

explain select * from cxuan005 where id like '1%';

Если вы используете число%, индекс срабатывает.

Если имя столбца является индексом, то запрос NULL для имени столбца вызовет индекс.

explain select * from cxuan005 where id is null;

Также бывают случаи, когда индекс существует, но MySQL его не использует.

  • В самом простом случае, если использование индекса менее эффективно, чем его отсутствие, то MySQL не будет использовать индекс.

  • Если в SQL используется условие ИЛИ, условный столбец перед ИЛИ имеет индекс, но следующий столбец не имеет индекса, то задействованный индекс не будет использоваться.Например, в таблице cxuan005 только идентификатор и хэш поля имеют индексы, а информационное поле индекса нет, тогда мы используем или для запроса.

    explain select * from cxuan005 where id = 111 and info = 'cxuan';

    Мы можем видеть из выполнения объяснения, хотя индекс ID_hash_index по-прежнему присутствует в опции Possible_Keys, но из Key Key_len можно узнать, что этот оператор SQL не использует индекс.

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

    explain select * from cxuan005 where hash = '8fd1f12575f6b39ee7c6d704eb54b353';

  • Если в расчете участвует столбец условия where, то индекс использоваться не будет

    explain select * from cxuan005 where id + '111' = '666';

  • Столбец индекса использует функцию, то же самое не будет использовать индекс

    explain select * from cxuan005 where concat(id,'111') = '666';

  • индексированный столбец использует как и%у первого символа индекс использоваться не будет.

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

  • Когда тип данных преобразуется неявно, например, varchar может быть преобразован в int без одинарных кавычек, индекс будет недействительным и будет запущено полное сканирование таблицы. Например, следующие два примера могут ясно проиллюстрировать это положение.

  • Использование операции IS NOT NULL для индексированных столбцов

  • Используйте , != в полях индекса. Оператор not-equal никогда не использует индекс, поэтому его обработка приводит только к полному сканированию таблицы.

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

Просмотр использования индекса

При использовании индексов MySQL существуетHandler_read_keyзначение, которое представляетСколько раз строка была прочитана значения индекса. Если значение Handler_read_key относительно низкое, это указывает на то, что улучшение производительности, полученное за счет увеличения индекса, не очень удовлетворительно, и частота использования индекса может быть невысокой.

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

Таблицы анализа MySQL, таблицы проверки и таблицы оптимизации

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

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

Таблица анализа MySQL

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

analyze table cxuan005;

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

Таблица: указывает имя таблицы;

Op: указывает операцию, которую необходимо выполнить, анализ указывает операцию анализа, проверка указывает проверку поиска, а оптимизация указывает операцию оптимизации;

Msg_type: указывает тип информации, его значение отображения обычно является одним из статус, предупреждение и сообщение об ошибке на четыре;

Msg_text: отображение информации.

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

Контрольный список MySQL

Базы данных часто могут сталкиваться с ошибками, такими как ошибки при записи данных на диск, или индексы не обновляются синхронно, или база данных останавливается без закрытия MySQL. В этих случаях могут возникнуть ошибки данных:Incorrect key file for table: ' '. Try to repair itНа этом этапе мы можем использовать оператор Check Table для проверки таблицы и ее соответствующего индекса.

check table cxuan005;

Основная цель контрольного списка — проверить одну или несколько таблиц на наличие ошибок. Check Table работает с таблицами MyISAM и InnoDB. Check Table также может проверить представление на наличие ошибок.

Оптимизированные таблицы MySQL

Таблицы, оптимизированные для MySQL, подходят для удаления большого количества табличных данных или внесения множества изменений в команды VARCHAR, BLOB или TEXT. Таблицы, оптимизированные для MySQL, могут объединять большое количество фрагментов пространства, чтобы устранить потери пространства, вызванные удалением или обновлением. Его команда выглядит следующим образом

optimize table cxuan005;

Мой механизм хранения — это механизм InnoDB, но, как видно из рисунка, InnoDB не поддерживает использование оптимизации, и для оптимизации рекомендуется использовать повторное создание + анализ. Команда оптимизации работает только с таблицами MyISAM и BDB.

У меня было шесть PDF-файлов, и вся сеть распространилась более чем на 10w+. После поиска «Programmer cxuan» в WeChat и подписки на официальный аккаунт я ответил cxuan в фоновом режиме и получил все PDF-файлы. следует

Шесть ссылок в формате PDF

Категории