Share via

Рекомендации по устранению неполадок База данных Azure для MySQL — гибкий сервер

  • Статья

ОБЛАСТЬ ПРИМЕНЕНИЯ: Отдельный сервер Базы данных Azure MySQL Гибкий сервер Базы данных Azure MySQL

Важно!

База данных Azure для MySQL один сервер находится на пути выхода на пенсию. Настоятельно рекомендуется выполнить обновление до База данных Azure для MySQL гибкого сервера. Дополнительные сведения о миграции на гибкий сервер База данных Azure для MySQL см. в статье "Что происходит с одним сервером База данных Azure для MySQL?"

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

Проверка количества индексов

В активной среде базы данных может наблюдаться высокая загрузка операций ввода-вывода, которая может быть индикатором плохого доступа к данным. Неиспользуемые индексы могут негативно повлиять на производительность по мере использования дискового пространства и кэша, а также замедления операций записи (INSERT/ DELETE/ UPDATE). Неиспользуемые индексы не используют больше места в хранилище и увеличивают размер резервного копирования.

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

Примечание.

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

SELECT object_schema, object_name, index_name FROM performance_schema.table_io_waits_summary_by_index_usage WHERE index_name IS NOT NULL AND count_star = 0 ORDER BY object_schema, object_name;

или

use information_schema; select tables.table_name, statistics.index_name, statistics.cardinality, tables.table_rows from tables join statistics on (statistics.table_name = tables.table_name and statistics.table_schema = '<YOUR DATABASE NAME HERE>' and ((tables.table_rows / statistics.cardinality) > 1000));

Перечисление самых загруженных индексов на сервере

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

SELECT 
object_schema AS table_schema, 
object_name AS table_name, 
index_name, count_star AS all_accesses, 
count_read, 
count_write, 
Concat(Truncate(count_read / count_star * 100, 0), ':', 
Truncate(count_write / count_star * 100, 0)) AS read_write_ratio, 
 count_fetch AS rows_selected , 
 count_insert AS rows_inserted, 
 count_update AS rows_updated, 
 count_delete AS rows_deleted, 
 Concat(Round(sum_timer_wait / 1000000000000, 2), ' s') AS total_latency , 
 Concat(Round(sum_timer_fetch / 1000000000000, 2), ' s') AS select_latency, 
 Concat(Round(sum_timer_insert / 1000000000000, 2), ' s') AS insert_latency, 
Concat(Round(sum_timer_update / 1000000000000, 2), ' s') AS update_latency, 
 Concat(Round(sum_timer_delete / 1000000000000, 2), ' s') AS  delete_latency 
FROM performance_schema.table_io_waits_summary_by_index_usage 
WHERE index_name IS NOT NULL AND count_star > 0 
ORDER BY sum_timer_wait DESC

Проверка структуры первичного ключа

База данных Azure для MySQL гибкий сервер использует подсистему хранилища InnoDB для всех нетемпорных таблиц. При использовании InnoDB данные хранятся в кластеризованном индексе с применением структуры сбалансированного дерева. Таблица физически упорядочена на основе значений первичного ключа, то есть строки хранятся в порядке первичных ключей.

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

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

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

На начальных этапах создания приложения вы можете не думать заранее, чтобы представить себе время, когда таблица начинает приближаться с двумя миллиардами строк. В результате вы можете использовать подписанное 4-байтовое целое число для типа данных столбца идентификатора (первичного ключа). Обязательно проверка все первичные ключи таблицы и переключиться на использование 8-байтовых целых столбцов (BIGINT) для размещения потенциального объема или роста.

Примечание.

Дополнительные сведения о типах данных и их максимальных значениях см. в разделе Типы данных справочного руководства по MySQL.

Использование покрывающих индексов

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

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

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

mysql> show create table employee\G
*************************** 1. row ***************************
       Table: employee
Create Table: CREATE TABLE `employee` (
  `empid` int(11) NOT NULL AUTO_INCREMENT,
  `fname` varchar(10) DEFAULT NULL,
  `lname` varchar(10) DEFAULT NULL,
  `joindate` datetime DEFAULT NULL,
  `department` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`empid`)
  ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)`

`mysql> select empid, fname, lname from employee where joindate > '2000-01-01';

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

mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 33.33
        Extra: Using where
1 row in set, 1 warning (0.01 sec)

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

mysql> CREATE INDEX cvg_idx_ex ON employee (joindate, empid, fname, lname);

Теперь, если запустить план EXPLAIN для того же запроса, в поле "Дополнительно" отображается текст "Using Index" (Использование индекса), что означает, что InnoDB выполняет запрос с использованием созданного ранее индекса, что подтверждает, что это покрывающий индекс.

mysql> EXPLAIN select empid, fname, lname from employee where joindate > '2000-01-01'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: employee
   partitions: NULL
         type: range
possible_keys: cvg_idx_ex
          key: cvg_idx_ex
      key_len: 6
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.01 sec)

Примечание.

Важно выбрать правильный порядок столбцов в покрывающем индексе, чтобы правильно обслуживать запрос. Общее правило состоит в том, чтобы сначала выбрать столбцы для фильтрации (предложение WHERE), затем сортировку и группировку (ORDER BY и GROUP BY) и, наконец, проекцию данных (SELECT).

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

Следующие шаги

Чтобы найти ответы на самые важные вопросы либо опубликовать новый вопрос или ответ, посетите Stack Overflow.