Привет, ребята, это программист cxuan. Добро пожаловать в мою последнюю статью. Эта статья представляет собой краткую версию настройки MySQL. Я добавил немного опыта настройки в ежедневный процесс разработки. Помогло. Начните текст ниже.
Мой github включил эту статью, адрес находится по адресуОптимизация MySQL
Как правило, традиционные интернет-компании редко сталкиваются с проблемами оптимизации 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: просмотр количества открытых в данный момент подключений.
Следующий блог обобщает почти все параметры состояния шоу и может использоваться в качестве справочного руководства.
Найдите 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 очень важен. Об этом столбце много информации. Читатели могут обратиться к этой статье.
Это включает в себя такие параметры, как состояние потока и необходимость блокировки таблицы.Вы можете просматривать выполнение 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 и text могут создавать полнотекстовые индексы.Те, кто создает таблицы подходят для добавления полнотекстовых индексов.
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, из ключа и 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.
Общие оптимизации SQL
Ранее мы представили использование индексов для оптимизации MySQL, так как же нам оптимизировать различный синтаксис и синтаксис SQL? Далее я расскажу о волне оптимизации SQL с точки зрения команд SQL.
Импортированные оптимизации
Для таблиц типа MyISAM большой объем данных можно импортировать следующим образом
ALTER TABLE tblname DISABLE KEYS;
loading the data
ALTER TABLE tblname ENABLE KEYS;
Эти две команды используются для включения или выключения обновления неуникальных индексов в таблицах MyISAM. При импорте большого объема данных в непустую таблицу MyISAM, установив эти две команды, можно повысить эффективность импорта. Для импорта большого количества данных в пустую таблицу MyISAM по умолчанию сначала импортируются данные, а затем создается индекс, поэтому его не нужно устанавливать.
Однако для таблицы поисковой системы InnoDB это не может повысить эффективность импорта.У нас есть следующие способы повысить эффективность импорта:
- Поскольку таблицы типа InnoDB хранятся в порядке первичных ключей, упорядочивание импортируемых данных в порядке первичных ключей может эффективно повысить эффективность импорта данных. Если таблица InnoDB не имеет первичного ключа, система по умолчанию создаст внутренний столбец в качестве первичного ключа, поэтому, если вы можете создать первичный ключ для таблицы, вы можете использовать это преимущество для повышения эффективности импорта данных.
- Выполните SET UNIQUE_CHECKS = 0 перед импортом данных, чтобы отключить проверку уникальности, и выполните SETUNIQUE_CHECKS = 1 после импорта, чтобы восстановить проверку уникальности, что может повысить эффективность импорта.
- Если приложение использует метод автоматической фиксации, рекомендуется выполнить SET AUTOCOMMIT = 0 перед импортом, отключить автоматическую фиксацию и выполнить SET AUTOCOMMIT = 1 после импорта, а также включить автоматическую фиксацию, что также может повысить эффективность. импорта.
Вставить оптимизацию
При вставке операторов вы можете рассмотреть следующие способы оптимизации
- Если вы вставляете несколько фрагментов данных в одну и ту же таблицу, лучше вставлять их одновременно, что может сократить время подключения к базе данных -> отключение, как показано ниже.
insert into test values(1,2),(1,3),(1,4)
- Если вы вставляете несколько фрагментов данных в разные таблицы, вы можете использоватьinsert delayedОператоры повышают эффективность выполнения. Смысл отложенного заключается в том, чтобы оператор вставки выполнялся немедленно, иначе данные будут помещены в очередь памяти, а не записаны на диск.
- Для таблиц MyISAM можно увеличить значение bulk_insert_buffer_size, чтобы повысить эффективность вставки.
- Лучше всего хранить файлы индекса и данных на разных дисках.
Оптимизация группы по
В сценарии использования группировки и сортировки, если вы сначала выполняете Группировать по, а затем Упорядочить по, вы можете указатьorder by nullСортировка запрещена, так как можно избежать упорядочения по нулюfilesort, сортировка файлов занимает много времени. Следующим образом
explain select id,sum(moneys) from sales2 group by id order by null;
Оптимизация заказа по
В плане выполнения часто видно, чтоExtraСортировка по файлам отображается в столбце. Сортировка по файлам — это разновидность сортировки файлов. Этот метод сортировки относительно медленный. Мы считаем, что это неправильная сортировка и ее необходимо оптимизировать.
Способ оптимизации заключается в использовании индексов.
Создаем индекс на cxuan005.
create index idx on cxuan005(id);
Затем мы запрашиваем, используя поля запроса, и сортируем в том же порядке.
explain select id from cxuan005 where id > '111' order by id;
Как видите, в этом запросеUsing index. Это показывает, что мы используем index.
Если порядок создания индекса и порядок по несовместимы, будет использоваться использование файловой сортировки.
explain select id from cxuan005 where id > '111' order by info;
MySQL поддерживает два способа сортировки: файловую и индексную.Использование индекса означает, что MySQL просматривает сам индекс, чтобы завершить сортировку. Индекс эффективен, файловая сортировка неэффективна.
order by будет использовать индекс только при соблюдении следующих условий
- Оператор order by использует самый верхний левый столбец индекса.
- Используйте предложение where и комбинацию условного столбца предложения order by, чтобы удовлетворить самый левый передний столбец индекса.
Оптимизация вложенных запросов
Вложенный запрос — это метод запроса, который мы часто используем.Этот метод запроса может использовать оператор SELECT для создания одного результата запроса, а затем использовать этот результат в качестве области запроса вложенного оператора в другом операторе запроса. При использовании подзапросы могут разделить сложный запрос на независимые части, которые легче понять логически, а также поддерживать и повторно использовать код.
Однако в некоторых случаях эффективность подзапросов невысока, обычно используютjoinдля замены подзапросов.
Анализ объяснения с использованием оператора SQL вложенного запроса выглядит следующим образом.
explain select c05.id from cxuan005 c05 where id not in (select id from cxuan003);
Из результатов объяснения видно, что запрос основной таблицы — index, а подзапрос — index_subquery, оба из которых не очень эффективны. Мы используем объединение для оптимизации плана анализа следующим образом.
explain select c05.id from cxuan005 c05 left join cxuan003 c03 on c05.id = c03.id;
Из результатов анализа объяснения мы видим, что основной запрос таблицы и подзапрос являются индексом и ссылкой соответственно, а эффективность выполнения ссылки относительно высока.Как правило, эффективность типа от высокого к низкому составляет System-->const-- >eq_ref-->ref --> fulltext-->ref_or_null-->index_merge-->unique_subquery-->index_subquery-->range-->index-->all .
Оптимизация подсчета
Мы все слишком часто используем count.Как правило, он используется для подсчета количества строк в результирующем наборе определенного столбца.Когда MySQL подтверждает, что выражение в скобках не может быть пустым, он на самом деле подсчитывает количество строк.
На самом деле у count есть еще один слой статистики:Подсчитать количество значений в столбце, при подсчете количества значений столбца по умолчанию не будет учитыватьсяNULLстоимость.
Распространенная ошибка, которую мы совершаем, заключается в том, что мы указываем столбец в круглых скобках, но хотим подсчитать количество строк в результирующем наборе. Если вы хотите узнать количество строк в результирующем наборе, лучше всего использовать count(*).
Оптимизация ограничения пейджинга
Обычно наша система выполняет пейджинг, в общем случае мы будем использовать ограничение плюс смещение для достижения. В то же время порядок по оператору будет добавлен к сортировке. Если индекс используется, эффективность обычно не является проблемой.Если индекс не используется, MySQL может выполнять множество операций сортировки файлов.
Обычно мы можем столкнуться с такой ситуацией, как ограничение 1000, 50, отбрасывание 1000 и взятие только 50, эта стоимость очень высока, если все страницы обращаются с одинаковой частотой, то такой запрос должен обращаться к половине таблицы по средним данным .
Чтобы оптимизировать такой запрос, либоОграничить количество страниц, илиОптимизация производительности для больших смещений.
IN не должен содержать слишком много значений в SQL
В MySQL соответственно оптимизирован IN.MySQL будет хранить все константы в массиве.Если значений много, потребление тоже увеличится.Например,
select name from dual where num in(4,5,6)
Для операторов SQL, подобных этому, не используйте in, если можно использовать between.
Когда требуется только одна часть данных
Если требуется только одна часть данных, рекомендуется использоватьlimit 1, в результате чего тип в плане выполнения станетconst.
Свести к минимуму сортировку, если индексы не используются
Попробуйте использовать union all вместо union
Основное различие между объединением и объединением всех заключается в том, что первому необходимо объединить набор результатов, а затем выполнить уникальную операцию фильтрации, которая будет включать сортировку, увеличение количества операций ЦП, увеличение потребления ресурсов и задержки. Конечно, предварительным условием для объединения всех является то, что два набора результатов не содержат повторяющихся данных.
где оптимизация условий
-
Избегайте тестов NULL для полей в предложениях WHERE.
-
Избегайте использования операторов != или в WHERE.
-
Не рекомендуется использовать нечеткие запросы с префиксом %, такие как LIKE "%name" или LIKE "%name%", что приведет к сбою индекса и выполнению полного сканирования таблицы. Но можно использовать LIKE "name%".
-
Избегайте выполнения операций выражения над полями, где, например, **выберите user_id, user_project из table_name, где age*2=36 ** — операция выражения, рекомендуется заменить ее на **select user_id, user_project из table_name, где age=36 /2 **
-
Рекомендуется определить тип столбца в предложении where, чтобы избежать преобразования типов, когда тип поля столбца не соответствует типу входящего параметра.
При запросе попробуйте указать имя поля запроса
Когда мы ежедневно используем запрос на выборку, мы пытаемся использовать метод выбора имени поля, чтобы избежать прямого **выбора * **, который увеличивает ненужное потребление (процессор, ввод-вывод, память, пропускная способность сети); и запрос эффективность относительно низкая.
Кроме того,У меня было шесть PDF-файлов, и вся сеть распространилась более чем на 10w+. После поиска «Programmer cxuan» в WeChat и подписки на официальный аккаунт я ответил cxuan в фоновом режиме и получил все PDF-файлы. следует