Оптимизация производительности MySQL

MySQL
Оптимизация производительности MySQL

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

  • Введение в оптимизацию

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

  • Основной принцип

Основные принципы оптимизации производительности MySQL:

  1. уменьшить узкие места в системе;
  2. сократить использование ресурсов;
  3. Улучшить скорость отклика системы.
  • Общий метод

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

  1. Выявление узких мест в системе и повышение общей производительности базы данных MySQL;
  2. Разумный структурный дизайн и настройка параметров, улучшение операций с базой данных;
  3. Максимально экономьте системные ресурсы, чтобы система могла предоставлять услуги с большей нагрузкой.

Например:

  1. За счет оптимизации файловой системы улучшена уникальная скорость записи дискового ввода-вывода;
  2. Улучшить нагрузочную способность MySQL в условиях высокой нагрузки за счет оптимизации стратегии планирования операционной системы;
  3. Ускорьте ответ на запрос, оптимизировав структуру таблицы, индекс, оператор запроса и т. д.

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

Доступно в MySQLSHOW STATUSоператор для просмотра параметров производительности базы данных MySQL, мы можем понять состояние базы данных MySQL на основе этих параметров производительности и сформулировать разумную стратегию оптимизации.

воплощать в жизньshow status;Вы можете просмотреть все параметры производительности, выполнитьshow status like '参数名称';Вы можете просмотреть параметры производительности с указанным именем параметра.Как правило, некоторые типы параметров имеют одинаковый префикс.

Нижеshow status;Имеется 356 возвращаемых результатов оператора, большинство из которых здесь опущено:

mysql> show status;
+-----------------------------------------------+--------------------------------------------------+
| Variable_name                                 | Value                                            |
+-----------------------------------------------+--------------------------------------------------+
| Aborted_clients                               | 0                                                |
| Aborted_connects                              | 2
| Bytes_received                                | 256                                              |
| Bytes_sent                                    | 185                                              |
| **这里省略了一部分......**                                                                          |
| Threads_running                               | 1                                                |
| Uptime                                        | 1038207                                          |
| Uptime_since_flush_status                     | 1038207                                          |
+-----------------------------------------------+--------------------------------------------------+
356 rows in set (0.00 sec)

Ниже приводится описание некоторых параметров производительности, для подробного описания, пожалуйста, посетитеБлог с открытым исходным кодом на Github : GitHub.com/ijiangtao/no…

название штата

сфера

объяснить подробно

Aborted_clients

Global

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

Aborted_connects

Global

Попытка подключиться к сбою сервера MySQL

Handler_read_prev

Both

Количество запросов на чтение предыдущей строки в ключевом порядке. Этот метод чтения в основном используется для оптимизации ORDER BY... DESC.

Handler_read_rnd

Both

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

Handler_read_rnd_next

Both

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

Handler_rollback

Both

Количество внутренних операторов ROLLBACK.

Handler_savepoint

Both

Количество запросов на размещение точки сохранения в механизме хранения.

Handler_savepoint_rollback

Both

Количество откатов к точке сохранения по запросу механизма хранения.

Handler_update

Both

Количество запросов на обновление строки в таблице.

Handler_write

Both

Количество запросов на вставку строки в таблицу.

Last_query_cost

Session

Общая стоимость последнего скомпилированного запроса, рассчитанная оптимизатором запросов. Стоимость сравнения разных планов запросов для одного и того же запроса. Значение по умолчанию 0 означает, что запрос не был скомпилирован. Значение по умолчанию — 0. Last_query_cost имеет область действия сеанса.

Max_used_connections

Global

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

Open_tables

Both

Количество открытых в данный момент столов.

Qcache_free_memory

Global

Объем свободной памяти, используемой для кэша запросов.

Qcache_hits

Global

Количество обращений к кэшу запросов.

Qcache_inserts

Global

Количество запросов, добавленных в кэш.

Qcache_total_blocks

Global

Запросите общее количество блоков в кеше.

Queries

Both

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

Questions

Both

Количество запросов, отправленных на сервер.

Select_full_join

Both

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

Select_full_range_join

Both

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

Select_range

Both

Количество объединений с использованием диапазонов в первой таблице. Общий случай не критичен, даже если значение довольно велико.

Select_range_check

Both

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

Select_scan

Both

Количество объединений, выполняющих полное сканирование первой таблицы.

Slave_running

Global

Значение ON, если сервер является ведомым, подключенным к ведущему.

Slow_queries

Both

Количество запросов, время выполнения которых превышает long_query_time секунд.

Sort_range

Both

Количество сортировок, выполненных в диапазоне.

Sort_rows

Both

Количество уже отсортированных строк.

Sort_scan

Both

Количество сортировок, сделанных при сканировании таблицы.

Table_locks_immediate

Global

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

Table_locks_waited

Global

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

Threads_cached

Global

Количество потоков в кэше потоков.

Threads_connected

Global

Количество открытых на данный момент подключений.

Threads_created

Global

Количество потоков, созданных для обработки соединений. Если значение Thread_created велико, вы можете увеличить значение thread_cache_size. Метод расчета скорости доступа к кешу Threads_created/Connections.

Threads_running

Global

Количество активных (не спящих) потоков.

Uptime

Global

Время работы сервера (в секундах).

Оптимизация запросов MySQL

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

Проанализируйте оператор запроса

мы можем использоватьexplainилиdescribeКлючевые слова для анализа оператора запроса, синтаксис выглядит следующим образом:

  1. explain [extended] SELECT SelectOptions
  2. describe [extended] SELECT SelectOptions
  3. desc [extended] SELECT SelectOptions
mysql> explain extended SELECT id, name, ip_address, `action`, create_time FROM t_user_action_log where id=11;
+----+-------------+-------------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table             | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_user_action_log | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)

mysql> describe extended SELECT id, name, ip_address, `action`, create_time FROM t_user_action_log where id=11;
+----+-------------+-------------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table             | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_user_action_log | NULL       | const | PRIMARY       | PRIMARY | 8       | const |    1 |   100.00 | NULL  |
+----+-------------+-------------------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)
  1. id : id — идентификатор выбора, порядковый номер запроса.
  2. select_type : select_type указывает тип оператора запроса. НапримерSIMPLEПредставляет простой запрос, который не включает запросы на соединение и подзапросы.
  3. table : таблица представляет собой таблицу для запроса.
  4. тип: указывает тип соединения
  5. возможных_ключей: указывает индексы, которые можно использовать.
  6. rows: указывает количество строк, которые необходимо проверить для запроса.
  7. Дополнительно: указывает детали запроса.

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

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

  1. как запрос с использованием индекса

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

Поэтому для строк в MySQL рекомендуется использовать единый префикс вместо суффикса.

mysql> explain SELECT id, ip_address FROM t_user_action_log where ip_address like '7%';
+----+-------------+-------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+
| id | select_type | table             | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_user_action_log | NULL       | range | ip_address_idx | ip_address_idx | 51      | NULL |    2 |   100.00 | Using where |
+----+-------------+-------------------+------------+-------+----------------+----------------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain SELECT id, ip_address FROM t_user_action_log where ip_address like '%0';
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table             | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_user_action_log | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   41 |    11.11 | Using where |
+----+-------------+-------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
  1. или оператор запроса ключевых запросов

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

  1. Оптимизировать подзапросы

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

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

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

Оптимизация структуры базы данных

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

При проектировании структуры базы данных следует учитывать:

  • избыточность данных
  • Скорость запроса и обновления
  • тип данных поля
  • и т.д……

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

Разделить таблицу с множеством полей на несколько таблиц

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

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

Добавить промежуточную таблицу

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

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

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

Добавьте лишние поля

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

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

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

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

Вставка записей оптимизации скорости

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

На основе этих измерений ниже представлены несколько стратегий оптимизации.

Таблицы для движка MyISAM

  1. Отключить индексацию

Для непустой таблицы движка MyISAM при вставке данных MySQL построит индекс на вставленных данных в соответствии с индексом таблицы.Если вставляется большой объем данных, эффективность вставки будет снижена.

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

  • Отключить индексацию:
alter table t_user_action_log_oneline disable keys;
  • Включить индексацию:
alter table t_user_action_log_oneline enable keys;
  1. Отключить проверку уникальности

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

  • Отключить проверку уникальности:
set unique_checks=0;
  • Включите проверку уникальности:
set unique_checks=1;
  1. Использовать объемную вставку

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

  1. использовать файл данных загрузки

Файл загрузки данных может импортировать данные более эффективно.

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

mysql> select * from t_user_action_log into outfile "/data/mysql/t_user_action_log.sql";
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

В это время найдите каталог, указанный в secure-file-priv для экспорта:

mysql> show global variables like '%secure%';
+--------------------------+------------------------------------------------+
| Variable_name            | Value                                          |
+--------------------------+------------------------------------------------+
| require_secure_transport | OFF                                            |
| secure_auth              | ON                                             |
| secure_file_priv         | C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\ |
+--------------------------+------------------------------------------------+
3 rows in set, 1 warning (0.01 sec)

Укажите, что экспортированные данные используют запятые для разделения результатов:

select * from t_user_action_log into outfile "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/t_user_action_log.sql" fields terminated by ',';

Содержимое файла t_user_action_log.sql следующее:

50,LiSi,9.8.8.2,0,2019-06-30 20:46:21
51,LiSi,7.8.8.2,5,2019-06-30 11:56:43

Используйте данные нагрузки, несущие для вставки данных в партии:

load data infile "C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/t_user_action_log.sql" into table t_user_action_log fields terminated by ',';

Таблицы для движка InnoDB

  1. Отключить проверку уникальности

Это то же самое, что и предыдущая таблица движка MyISAM.

  1. Отключить проверку внешнего ключа

Отключите проверку внешнего ключа перед вставкой:

set foreign_key_checks=0;

Включите внешний ключ Проверка после вставки:

set foreign_key_checks=1;
  1. Отключить автоматическое представление

Отключите автофиксацию перед вставкой:

set autocommit=0;

Чтобы включить автофиксацию после вставки:

set autocommit=1;

Анализируйте, проверяйте и оптимизируйте таблицы

1. Таблица анализа

используется в MySQLANALYZE TABLEоператор для анализа таблицы, основной синтаксис оператора выглядит следующим образом:ANALYZE TABLE 表名1 [,表名2…] ;

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

ANALYZE TABLEОператор может анализировать таблицы типов InnoDB и MyISAM.

mysql> analyze table t_user_action_log;
+--------------------------------------------+---------+----------+----------+
| Table                                      | Op      | Msg_type | Msg_text |
+--------------------------------------------+---------+----------+----------+
| ijiangtao_local_db_mysql.t_user_action_log | analyze | status   | OK       |
+--------------------------------------------+---------+----------+----------+
1 row in set (0.02 sec)
  • Таблица: указывает имя таблицы;
  • Op: указывает операцию, которую необходимо выполнить. анализ означает выполнение операций анализа. check означает проверку поиска. оптимизировать средства для выполнения операций оптимизации;
  • Msg_type: указывает тип информации, а отображаемое значение обычно представляет собой состояние, предупреждение, ошибку и информацию;
  • Msg_text: Показать информацию.

2. контрольный список

MySQL использует оператор CHECK TABLE для проверки таблиц. Оператор CHECK TABLE может проверять таблицы InnoDB и MyISAM на наличие ошибок. И оператор также может проверить представление на наличие ошибок.

Основной синтаксис этого оператора следующий:CHECK TABLE 表名1 [,表名2…] [option] ;

Среди них параметр option имеет 5 параметров, а именно QUICK, FAST, CHANGED, MEDIUM и EXTENDED. Эффективность выполнения этих пяти параметров последовательно снижается.

Параметр option действителен только для таблиц MyISAM, но не для таблиц InnoDB. Оператор CHECK TABLE также добавляет к таблице блокировку только для чтения во время выполнения.

mysql> check table t_user_action_log;
+--------------------------------------------+-------+----------+----------+
| Table                                      | Op    | Msg_type | Msg_text |
+--------------------------------------------+-------+----------+----------+
| ijiangtao_local_db_mysql.t_user_action_log | check | status   | OK       |
+--------------------------------------------+-------+----------+----------+
1 row in set (0.01 sec)

3. оптимизировать таблицу

MySQL использует оператор OPTIMIZE TABLE для оптимизации таблиц. Этот оператор действителен как для таблиц типа InnoDB, так и для таблиц типа MyISAM. Однако оператор OPTILMIZE TABLE может оптимизировать только поля типа VARCHAR, BLOB или TEXT в таблице.

Основной оператор таблицы синтаксиса OPTILMIZE заключается в следующем:OPTIMIZE TABLE 表名1 [,表名2…] ;

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

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

Есть много способов оптимизировать таблицу: оператор OPTIMIZE TABLE, инструмент mysqlcheck (для запуска сервера) или myisamchk (чтобы не запускать сервер и не взаимодействовать с таблицей).

С использованием MySQL таблицы, включающие BLOB- и VARCHAR-байты, станут более громоздкими, так как эти поля имеют разную длину, при вставке, обновлении или удалении записей они будут занимать разный размер места, а записи будут фрагментироваться. космос. Как и фрагментированный диск, он снижает производительность и нуждается в дефрагментации, поэтому оптимизируйте.

mysql> optimize table t_user_action_log;
+--------------------------------------------+----------+----------+-------------------------------------------------------------------+
| Table                                      | Op       | Msg_type | Msg_text                                                          |
+--------------------------------------------+----------+----------+-------------------------------------------------------------------+
| ijiangtao_local_db_mysql.t_user_action_log | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| ijiangtao_local_db_mysql.t_user_action_log | optimize | status   | OK                                                                |
+--------------------------------------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.11 sec)

Оптимизация сервера MySQL

Чтобы оптимизировать производительность сервера MySQL, рассмотрите следующие аспекты:

  1. Настроить достаточно памяти
  2. Настройте систему с большим объемом дисков, чтобы сократить время ожидания чтения с диска.
  3. Разумно распределяйте дисковый ввод-вывод и распределяйте дисковый ввод-вывод на несколько устройств, чтобы уменьшить конкуренцию за ресурсы и улучшить возможности параллельной работы.
  4. Настройте многопроцессорность, MySQL — это многопоточная база данных, многопроцессорность может обрабатывать несколько потоков одновременно.
  5. Оптимизируйте параметры MySQL, такие как настройка размера буфера индекса с помощью параметра key_buffer_size.

Суммировать

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

Статьи по Теме

Wechat-westcall

Категории