Общие схемы оптимизации для MySQL

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

Каковы решения по оптимизации для MySQL?

Общие методы оптимизации базы данных MySQL делятся на три уровня:Оптимизация SQL и индексов, оптимизация структуры базы данных, оптимизация системного оборудованияИ так далее, но в каждом крупном направлении есть множество мелких точек оптимизации, рассмотрим их подробнее.

Примечания по оптимизации:

• Основано на данных, а не на догадках
• Избегайте преждевременной оптимизации
• Избегайте чрезмерной оптимизации
• Глубокое понимание бизнеса
• Оптимизация производительности — это постоянная битва
• Выберите правильные метрики, тестовые примеры, тестовую среду

оптимизировать цель

• Сокращение времени ввода-вывода

IO всегда является наиболее вероятным узким местом в базе данных Это определяется ответственностью базы данных Более 90% времени в большинстве операций с базой данных занимают операции ввода-вывода Сокращение количества операций ввода-вывода является первоочередной задачей в оптимизации SQL , конечно же, также является наиболее эффективным методом оптимизации.

• Уменьшить вычисление процессоров

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

Схема оптимизации 1: оптимизация SQL и индекса

• используйте правильный индекс

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

**索引类型**：<font color = "blue">**普通索引、主键索引、唯一索引、组合索引、全文索引**</font>                  

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

• Примечание, когда SQL написан

  • До MySQL 5.0 старайтесь избегать использования или запроса, вместо этого вы можете использовать объединение или подзапрос, потому что использование или запрос в более ранних версиях MySQL может привести к сбою индекса.В версиях после MySQL 5.0 было введено слияние индексов, просто чтобы это то есть это функция слияния пересечения или объединения набора результатов запроса с несколькими условиями, такого как или или и запрос, поэтому это не вызовет проблемы аннулирования индекса. Если другие поля в ограничении не индексируются, используйте или как можно меньше.
  • Избегайте использования операторов != или в условиях запроса where, так как эти операторы заставят обработчик запросов отказаться от индекса и выполнить полное сканирование таблицы.
  • Правильное использование префиксных индексов, MySQL поддерживает префиксные индексы, что означает, что мы можем определить часть строки как индекс. Мы знаем, что чем длиннее индекс, тем больше занимает место на диске и тем меньше значений индекса можно разместить на одной и той же странице данных, а это значит, что время запроса, необходимое для поиска индекса, больше, а эффективность запроса будет уменьшен.. Уменьшен, поэтому мы можем правильно выбрать использование префиксного индекса, чтобы уменьшить занимаемое пространство и повысить эффективность запросов.
  • Старайтесь избегать использования select * и вместо этого запрашивайте обязательные поля, что может повысить скорость и снизить нагрузку на полосу пропускания при передаче по сети.
  • Что касается оптимизации JOIN, попробуйте использовать оператор Join для замены подзапроса, потому что подзапрос является вложенным запросом, а вложенный запрос создаст новую временную таблицу, а создание и удаление временной таблицы займет определенное количество времени. системных ресурсов и стоит определенное количество времени, но оператор Join не создает временную таблицу, поэтому производительность будет выше.
  • Мы должны попытаться использовать маленькую таблицу для управления большой таблицей для запроса, то есть, если данные в таблице B меньше, чем данные в таблице A, порядок выполнения заключается в том, чтобы сначала искать таблицу B, а затем искать таблицу. А.
  • Не выполняйте арифметические операции или другие операции экспрессии на полях столбцов, в противном случае двигатель запроса может неправильно использовать индекс, что влияет на эффективность запроса.
  • Добавление избыточных полей может уменьшить большое количество подключенных таблиц-запросов, поскольку производительность подключенных таблиц-запросов нескольких таблиц очень низкая, все избыточные поля могут быть надлежащим образом добавлены для уменьшения соответствующих запросов нескольких таблиц, которые представляют собой оптимизацию, которая представляет собой пространство За время стратегии.
  • Избегайте преобразования типов Упомянутое здесь «преобразование типов» относится к преобразованию типов, которое происходит, когда тип поля столбца в предложении where не соответствует типу входящего параметра.
  • Попробуйте использовать union all вместо union.Основное различие между union и union all заключается в том, что первому необходимо объединить два (или более) набора результатов, а затем выполнить уникальную операцию фильтрации, которая будет включать сортировку, добавляя множество операций ЦП. , добавляя большое потребление ресурсов и задержку. Поэтому, когда мы можем подтвердить, что повторяющиеся наборы результатов невозможны или нас не волнуют повторяющиеся наборы результатов, попробуйте использовать union all вместо union.
  • Сортируйте как можно меньше, так как операции сортировки будут потреблять больше ресурсов ЦП, поэтому сокращение сортировки может сильно повлиять на время отклика SQL в сценариях с высокой частотой попаданий в кэш и достаточными возможностями ввода-вывода. Если поле сортировки не использует индекс, сортируйте как можно меньше.
  • Значение IN в операторе SQL не должно быть слишком большим, MySQL соответствующим образом оптимизирует IN, то есть сохраняет все константы в IN в массиве, и массив сортируется. Однако, если значение велико, потребление также относительно велико.
  • Когда требуется только одна часть данных, используется ограничение 1, которое должно привести столбец типа в EXPLAIN к константному типу.
  • Различать в и существует, не в и не существует.
  • Используйте разумные методы пейджинга для повышения эффективности пейджинга.
  • сегментированный запрос
  • Избегайте нулевых значений для полей в предложениях where
  • Не рекомендуется использовать префикс % для нечеткого запроса.Если вы используете префикс % для нечеткого запроса, рекомендуется использовать полнотекстовый индекс.
  • Для совместных индексов соблюдается правило крайнего левого префикса. Например: индекс комбинации (a, b, c) три столбца, мы можем использовать (a), (a, b), (a, b, c), (a, c ), (c,b,a),(c,a), но нельзя использовать в сочетании с (c,b),(b,c),(b),(c), иначе индекс будет неверным .
  • Индекс Force можно использовать при необходимости, чтобы заставить индекс запрос.

Все вышеперечисленные схемы оптимизации могут быть проверены объяснением. Подробно описано каждое поле EXPLAIN: Значения поля типа следующие:

Второй план оптимизации: оптимизация структуры базы данных

①Минимальная длина данных

В общем, тем меньше таблица базы данных базы данных, то она запрашивает быстрее, поэтому для повышения эффективности таблицы поля таблицы должно быть установлено как можно меньшее, такое как идентификационный номер, вы можете быть установлены на CHAR (18) не установить варрар (18).

②Используйте самый простой тип данных

Не используйте тип varchar, если вы можете использовать тип int, потому что тип int более эффективен, чем запрос типа varchar.

③Как можно меньше определяйте тип текста

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

④Выберите правильный механизм хранения

       Тип механизма хранения: MyISAM, InnoDB, MEMORY, MERGE, TokuDB, CSV, Archive и т. д..

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

        InnoDB: для приложений обработки транзакций поддерживаются внешние ключи. Если приложение предъявляет относительно высокие требования к целостности транзакций и требует согласованности данных в параллельных условиях, а операции с данными включают множество операций обновления и удаления в дополнение к вставке и запросу, то механизм хранения InnoDB является более подходящим выбором. Механизм хранения InnoDB может не только эффективно уменьшить блокировку, вызванную удалением и обновлением, но также обеспечить полную фиксацию (Commit) и откат (Rollback) транзакции InnoDB является подходящим выбором.

       MEMORY: хранит все данные в оперативной памяти, обеспечивая чрезвычайно быстрый доступ в средах, где требуется быстрое обнаружение записей и других подобных данных. Недостатком MEMORY является то, что существует ограничение на размер таблицы, слишком большую таблицу нельзя кэшировать в памяти, а во-вторых, необходимо обеспечить возможность восстановления данных таблицы, а также данных в памяти. таблица может быть восстановлена ​​после аварийного закрытия базы данных. Таблицы MEMORY обычно используются для небольших таблиц, которые обновляются реже для быстрого доступа к результатам.

       MERGE: используется для логической группировки ряда идентичных таблиц MyISAM и обращения к ним как к объекту. Преимущество таблиц MERGE заключается в том, что они могут преодолевать ограничение размера одной таблицы MyISAM и распределять разные таблицы по нескольким дискам. может эффективно повысить эффективность доступа к таблице MERGE, которая очень подходит для сред VLDB, таких как хранилища данных. Он принадлежит стороннему механизму хранения с высокой производительностью записи и высоким коэффициентом сжатия, поддерживает обработку транзакций механизмов хранения MySQL и MariaDB и поддерживает большинство онлайн-операций DDL.

       TokuDB: использование фрактального древовидного индекса для обеспечения эффективной вставки; отличные функции сжатия, почти в 10 раз выше, чем у InnoDB; функция горячих изменений схемы поддерживает онлайн-создание индекса и операции DDL, такие как добавление и удаление столбцов атрибутов. Используйте Bulk Loader для быстрой загрузки больших объемов данных, предоставьте технологию устранения задержек master-slave, поддержите ACID и MVCC.

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

Схема подтаблицы и подбиблиотеки — это также то, что мы часто называем вертикальным разделением (подтаблица) и горизонтальным разделением (подбиблиотека).

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

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

Третий план оптимизации: оптимизация системного оборудования

Аппаратные требования MySQL в основном отражены в трех аспектах: диск, сеть и память.

①диск

Возможность поиска на диске (дисковый ввод-вывод), на примере современного высокоскоростного жесткого диска SCSI (7200 об/мин), этот жесткий диск теоретически выполняет поиск 7200 раз в секунду, что определяется физическими характеристиками, и нет возможности изменить его; Вы должны попытаться использовать диски с высокой производительностью чтения и записи, такие как твердотельные накопители, которые могут сократить время выполнения операций ввода-вывода и повысить общую эффективность работы MySQL.
Диск также может попытаться использовать несколько маленьких дисков вместо одного большого, потому что скорость диска фиксирована, а наличие нескольких маленьких дисков эквивалентно параллельной работе нескольких дисков.

②Интернет

Обеспечение плавной пропускной способности сети (малая задержка) и достаточной пропускной способности сети являются основными условиями для нормальной работы MySQL.Если условия позволяют, можно установить несколько сетевых карт для повышения эффективности работы сервера MySQL в периоды пиковой нагрузки сети.
Конфигурация DNS Попробуйте использовать skip-name-resolve, чтобы уменьшить ненужные проблемы, вызванные разрешением.
Проверьте скорость потери пинга в сети.
Повысьте производительность, оптимизировав сетевые параметры в файле /etc/sysctl.cnf.

③ОЗУ

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

расширение знаний:

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

Журнал медленных запросов относится к функции записи журнала медленных запросов, которую можно включить в MySQL с помощью конфигурации.SQL, превышающий значение long_query_time, будет записан в журнал. Мы можем включить медленный запрос, установив «slow_query_log=1» Есть два способа включить его:

Чтобы открыть его через режим командной строки MySQL, вам нужно всего лишь выполнить «set global slow_query_log=1», но этот режим конфигурации завершится ошибкой после перезапуска службы MySQL; Другой способ можно открыть, изменив файл конфигурации MySQL, нам нужно настроить «slow_query_log=1» в my.cnf, и можно настроить журнал медленных запросов, установив «slow_query_log_file=/tmp/mysql_slow.log» в каталоге хранения, но после того, как настройка завершена таким образом, необходимо перезапустить сервер MySQL, чтобы изменения вступили в силу.
Следует отметить, что после включения функции медленного ведения журнала это окажет определенное влияние на производительность MySQL, поэтому эту функцию следует использовать с осторожностью в производственной среде.

====================== END =========================