排除 Azure Database for MySQL 灵活服务器故障的最佳做法

Artículo
12/23/2024

使用以下各部分的信息使 Azure Database for MySQL 灵活服务器顺利运行，并将这些信息作为指导原则，确保架构是以最佳方式设计的，并且可为应用程序提供最佳性能。

检查索引数

在繁忙的数据库环境中，你可能会发现 I/O 使用率很高，这可能是数据访问模式不佳的一个指标。未使用的索引可能会对性能产生负面影响，因为它们会消耗磁盘空间和缓存，并减慢写入操作 (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 Database for MySQL 灵活服务器对所有非临时表使用 InnoDB 存储引擎。使用 InnoDB 时，数据是使用 B 树结构存储在聚集索引中的。该表是基于主键值通过物理方式组织的，这意味着行是按主键顺序存储的。

InnoDB 表中的每个辅助键条目都包含一个指向存储数据的主键值的指针。换句话说，辅助索引条目包含了该条目所指向的主键值的副本。因此，主键选择对表中的存储开销量有直接的影响。

如果键是由实际数据（例如用户名、电子邮件、SSN 等）衍生出来的，则称为“自然键”。如果键是人为的，而不是来自数据（例如自动递增整数），则称为“合成键”或“代理键”。

通常建议避免使用自然主键。这些键通常非常宽，包含来自一个或多个列的长值。这又会导致严重的存储开销，因为主键值被复制到每个辅助键条目中。此外，自然键通常不遵循预先确定的顺序，这会显著降低性能，并在插入或更新行时引发页面分段。为了避免这些问题，请使用单调增加的代理键来代替自然键。自动递增（大）整数列是单调增加代理键的一个很好的示例。如果需要列的某个组合是唯一的，请将这些列声明为唯一的辅助键。

在生成应用程序的初始阶段，你可能不会提前想象到表开始接近包含 20 亿行的情形。因此，你可能会选择对 ID（主键）列的数据类型使用带符号的 4 字节整数。请务必检查所有表主键，并切换为使用 8 字节整数 (BIGINT) 列，以便应对可能出现的高容量或高增长。

注意

有关数据类型及其最大值的详细信息，请参阅 MySQL 参考手册中的数据类型。

使用覆盖索引

上一部分介绍了如何将 MySQL 中的索引组织为 B 树，在聚集索引中，叶节点包含基础表的数据页。辅助索引与聚集索引具有相同的 B 树结构，你可以在具有聚集索引或堆的表或视图上定义它们。辅助索引中的每个索引行都包含非聚集键值和行定位符。此定位符指向聚集索引或堆中包含该键值的数据行。因此，涉及辅助索引的任何查找都必须从根节点开始，经过分支节点导航到正确的叶节点，以获取主键值。然后，系统在主键索引上执行随机 IO 读取（再次从根节点通过分支节点导航到正确的叶节点）以获取数据行。

为了避免在主键索引上进行这种额外的随机 IO 读取来获取数据行，可以使用覆盖索引，其中包括查询所需的所有字段。通常，使用此方法对受 I/O 约束的工作负载和缓存工作负载很有用。因此，最佳做法是使用覆盖索引，因为它们适合在内存中使用，而且与扫描所有行相比，它们会更小，读取效率更高。

例如，假如有一个表，你要使用该表来尝试查找在 2000 年 1 月 1 日之后加入公司的所有员工。

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”值会显示在“Extra”字段中，这意味着 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

Compartir a través de

排除 Azure Database for MySQL 灵活服务器故障的最佳做法

检查索引数

列出服务器上最繁忙的索引

查看主键设计

使用覆盖索引

下一步

Recursos adicionales