快速成长期应用架构实践 （16）：数据库诊断

欢迎访问网易云社区，了解更多网易技术产品运营经验。

4.5 数据库诊断

数据库作为一个大型的并发存储系统，其内部设计极其复杂，开发者在使用数据库时， 面临着可用性、可靠性、性能、安全、扩展性等多重挑战，这使得数据库的使用具有较高 的技术门槛。一般大型团队都会雇佣专职的 DBA 来维护数据库的日常运行，指导开发者建 立正确的使用姿势，但是对于中小型团队而言，受限于人力成本，这种模式难以实现。随 着 DevOps 理念的流行，开发者开始更多地参与和承担数据库的运维工作，其中就包括对 数据库的定期健康检查。

对数据库定期进行健康检查是数据库日常维护的重要环节，通过检查数据库的各项运 行指标，评估系统的运行风险，提前将风险消灭在摇篮中，能够有效提高数据库服务的质 量。数据库的健康检查涉及索引设计、容量规划、服务安全、参数配置、用户访问、集群 复制 6 个方面。

索引设计 

合理的索引设计能够有效加速数据库的访问，提高查询的执行效率，减少用户查询对 服务端的资源消耗。但是不合理、低效、冗余甚至无效的索引不仅无法起到加速查询的效 果，反而会影响数据库的插入、更新性能，甚至是数据库的高可用方案能否生效。

主键索引缺失：由于 MySQL 默认存储引擎 InnoDB（MySQL 5.6 版本以上）使用 的是聚簇索引表设计，这就要求所有的表必须包含一个主键，所有的数据记录按 照主键次序构建 B+树。如果用户在创建表时显式指定主键，数据库就会使用用户 指定的主键构建 B+树，但是如果用户没有显式指定主键，同时也没有创建任何唯 一键索引，InnoDB 为了确保每张表至少包含一个主键，会默认为用户生成一个“隐 含主键”，该主键对用户不可见，甚至对于 MySQL Server 层的 binlog 也不可见。 binlog 是连接 MySQL 主从复制节点的纽带，所有主节点的更新都是通过 binlog 传递给从节点的，一旦 binlog 中没有更新记录的主键 ID，就会导致基于 Row 格式的 binlog 在从节点执行时，无法确定一条唯一记录，只能通过全表扫描来进行 匹配，大幅降低了从机的执行效率，造成复制延迟，如图 4-37 所示。如果是高可 用故障切换的从节点，会导致切换的时间大幅增加，甚至会导致高可用机制失 效。如果是实现读写分离的只读从节点，则会导致应用读到的数据可能是很久 以前的旧数据。所以使用 InnoDB 存储引擎的 MySQL 用户在创建表时，最好显 式指定主键。 

主键索引与业务相关：如果用户在创建表时指定的主键与业务相关，就会被频 繁更新，从而引起 MySQL 数据库的 InnoDB 存储引擎进行频繁的节点合并和 分裂，造成大量额外的系统 I/O 开销，影响数据库的插入和更新性能。我们推 荐开发者在创建表时指定与业务无关的自增字段作为主键，这样不仅能提高按 时间序插入的性能（顺序写入硬盘），同时也可以提高按插入时间范围检索的 查询效率。

冗余索引：如果一个索引涉及的字段属性包含另外一个索引涉及的字段属性，同 时两个索引字段顺序一致，且两个索引的首字段属性相同，则可以认为涉及字段 少的索引为冗余索引。在 MySQL 5.7 版本推出 sys 库之前，我们可以通过 percona 的工具 pt-duplicate-key-checker 来完成对冗余索引的检查，在 MySQL 5.7 版本中， 我们可以通过 sys 库 schema_redundant_indexes 表来完成，如图 4-38 所示。

低效索引：索引的作用在于通过索引，查询能够扫描更少的记录。数据库中的记 录在索引字段区分度越高，扫描的记录数就越少，从而执行的效率就越高。如果 数据库表中的记录在索引字段区分度不大，索引对记录的筛选结果就不明显，索 引就无法起到加速查询的作用。通过数据库记录在索引字段的区分度，我们可以 衡量索引的执行效率。

MySQL 系统库下，innodb_index_stats 表的 stat_value 字段，记录了某张表在某个索引 的不同取值的记录个数，innodb_table_stats 表的 n_rows 字段记录了某张表的总记录数，二 者相除，即可得到数据库记录在某个索引的区分度，越接近 1，表示区分度越高，低于 0.1， 则说明区分度较差，开发者应该重新评估 SQL 语句涉及的字段，选择区分度高的多个字段 创建索引，通过运行下面的 SQL 语句，就可以计算每张表的索引区分度。

无效索引：如果一个索引始终无法被查询使用，它的存在就只能增加数据库的维 护开销，开发者应该及时删除这些索引。通过 MySQL 5.7 版本 sys 库 schema_unused_indexes 视图，可以查看当前实例有哪些索引从未被使用。

容量规划 

数据库的运行依赖计算、存储、网络等多种资源，通过对各种资源的使用情况分析， 对资源进行合理的规划配置，是数据库稳定运行的必要条件。 

CPU：通常使用 CPU 利用率衡量 CPU 的繁忙程度，通过 top 命令，开发者可以查 看 CPU 利用率实时变化。CPU 利用率持续超过 80%，预示计算资源已经接近饱 和，如果开发者已经做过 SQL 优化，就需要使用更高配置的 CPU。通过查看 7 天内 CPU 利用率超过 80%的时间占整体时间的百分比，以及单次持续时间超过一 定阈值，则可视为 CPU 扩容的触发条件。

I/O：大部分数据库应用都是的 I/O Bound 类型，I/O 处理能力直接决定了数据库 的性能。I/O 利用率统计了一秒内 I/O 请求队列非空的时间比例，I/O 利用率越高 就表示硬盘越繁忙。但是 I/O 利用率 100%并不表示系统已经无法处理更多的 I/O 请求。I/OPS 和每秒 I/O 字节数可以从存储设备的层次更准确地描述 I/O 负载。每 一个存储设备都有 I/OPS 和每秒 I/O 字节数的上限，任意一个达到上限，就会成 为 I/O 处理能力的瓶颈，在传统机械硬盘中，随机 I/O 主要受到 IOPS 的限制，顺 序 I/O 主要受带宽限制。除此之外，我们还可以从应用的角度，使用一次 I/O 请 求的响应时间来描述 I/O 负载，一次 I/O 请求的响应时间包括其在队列中的等待 时间和实际 I/O 处理时间之和。开发者可以通过 iostats 很方便地收集这些数据。 如果这些指标在一段时间内持续接近设定上限，就可以认为 I/O 过载，通过扩大 内存，让更多的读写请求命中缓存以缓解硬盘 I/O。另外，使用更高配置的存储设 备，例如固态硬盘，也可以大幅提高系统的 I/O 处理能力。

存储空间：存储空间不足会导致严重的系统故障，数据库可能会宕机，更为严重 的是数据库进程存活，但是无法响应服务，从而造成基于进程的宕机监控失效。 根据 7 天内数据库中存储数据的变化，我们可以按照一定的拟合算法，估算出未 来 3 天内数据的增长情况，来判断实例是否存在存储空间不足的风险。

内存：使用 InnoDB 存储引擎的 MySQL 数据库在实例启动时，就会预分配一块固 定大小的内存空间，所有读写请求都会在该空间中完成，如果内存中缓存了用户 读写的数据，则直接读取内存，如果内存中没有用户读写的数据，则需要将数据 先从硬盘中装载进内存中，由于内存的读写速度远远快于硬盘，这就使得读写请 求是否命中内存决定了读写请求的处理速度。内存空间越大，缓存数据越多，命中的概率也就越大。所以我们可以使用缓存命中率来衡量内存空间大小是否满足 应用的需求。在 MySQL 中，show engine innodb status 命令的 Buffer pool hit rate 可以度量近一段时间范围内 Buffer pool 的命中情况，如图 4-39 所示。 

网络：网络带宽在数据库返回记录较多的情况下，也可能会成为系统的瓶颈。一 般我们使用每秒网络流入和流出字节数来衡量网络流量是否达到带宽限制。在云 环境下，每台虚拟机或者容器都有一定的网络带宽配额，私有网络的配额相对比 较大，公网配额与用户付费相关；使用 iftop 可以查看当前系统的网络流量。
服务安全

弱密码：MySQL 的登录认证使用 IP 和账户密码的方式，很多开发者为了方便记 忆，习惯将数据库密码设置为弱密码，这非常危险。数据库中的数据很多涉及敏 感业务，弱密码容易被破解，对数据库中的数据来说是一个严重安全隐患。MySQL 系统库下 user 表的 password 字段保存了所有用户的密码，MySQL 使用 2 次 sha-1 的不可逆加密算法，所以我们无法通过 password 字段获取用户的密码内容，但是 我们可以通过将常见弱密码制成彩虹表，模拟 MySQL 的加密算法，匹配 password 字段，即可发现数据库中的弱密码账号。

网络安全：在一般的业务架构中，数据库都不会直接服务于终端用户，而是服务 于运行业务逻辑的应用程序。所以数据库和业务程序之间出于安全的考虑，会选择使用私有网络。即便如此，为了避免数据库连错，也需要在设置数据库账号时， 增加 IP 来源限制。在一些特定的场景下，如果数据访问必须借助公网来实现，就 会将数据库暴露在公网上。使用公网数据库实例，必须要配置防火墙，否则存在 被攻击的隐患。通过 iptables 我们可以控制访问数据库的来源 IP。

权限检查：MySQL 提供了多种权限配置，为了方便管理及避免误操作，一般会将 管理权限和访问权限配置成两个不同的账号，禁止使用管理权限作为业务程序访 问数据库的账号。通过系统库 MySQL 库的 user 表可以确认各个账号拥有的权限， 尽量避免业务账号拥有 super 权限。

参数配置

内存相关参数：MySQL 数据库的内存使用包括共享内存与连接独占内存两个部 分。每一个用户新建连接，数据库都要分配一块固定大小的内存空间保存用户的 临时数据，这些空间为单个连接独占。在 MySQL 实例启动时，系统同时也会预 先分配一些实例级别的共享内存空间，例如 Innodb_buffer_pool、Innodb_log_ buffer_pool 等，供所有连接共享。独占内存空间乘以最大连接数加上共享内存空 间，我们可以计算出 MySQL 最大可使用的内存空间，如果超过实际物理内存大 小，就存在 MySQL 进程被 Linux 操作系统强行 oom kill 风险，导致实例宕机。 MySQL 的这些内存空间都可以通过配置参数指定大小，如果超过实际内存空间， 应该调整相应参数配置，最常见的是调整 Innodb_buffer_pool 和最大连接数。

 Innodb 日志相关参数：innodb_log_file_size 定义了 Innodb 重做日志的大小，如果 参数设置过小，会导致数据库写操作频繁卡顿，如果设置过大，会导致数据库实 例重启或者故障恢复花费大量的时间。对于使用固态硬盘等高配置存储设备的数 据库，可以将重做日志设置大一些，对于使用机械硬盘的数据库，应该设置小一 些，一般在 512M 到 4G 之间。innodb_flush_log_at_trx_commit 定义了重做日志的 刷新节奏，如果该参数非 1，会导致数据库宕机重启后丢失部分更新数据，对于数据可靠性要求较高的应用造成严重影响。

二进制日志相关参数：binlog 主要用于 MySQL 集群复制及故障恢复担任协调者 的作用。binlog_format 定义了 binlog 的格式，主要包括 ROW、STATEMENT 和 MIXED 3 种格式，ROW 格式是最安全的一种日志格式，会保证主从数据的严格 一致，建议开发者选用 ROW 格式。但是 ROW 格式的 binlog 会占用更多的存储 空间，通过 expire_logs_days 可以控制保存 binlog 的天数，如果 binlog 占用的存 储空间比例超过 50%，就要考虑适当减少 binlog 的保存天数。sync_binlog 参数 定义了 binlog 刷新硬盘的节奏，如果非 1，就会导致宕机重启后最近的更新数据 丢失。

连接数相关参数：MySQL 有最大连接数限制 max_connections，如果应用连接超 过 max_connetions 限制，就会得到 out of max connections 异常，无法建立连接。 show processlist 可以查看当前的连接数，如果接近最大限制，就存在无法新建连 接的风险。通过在应用端使用连接池可以控制数据库的连接数。
用户访问

慢连接：慢查询数量是最直观的反映数据库处理能力是否满足业务需求的指标。 通过设置 slow_query_log 可以开启慢查询日志，MySQL 数据库会将执行时间超 过 long_query_time 的查询记入慢查询日志，如果某个时间段内，慢查询数量急 剧增加，开发者就必须要关注数据库的性能问题，首先需要进行 SQL 优化，其 次考虑资源是否需要扩容，最后可能需要数据库水平扩展方案，包括创建只读 从节点。

死锁数量：两个事务涉及的数据库记录有重叠，如果 SQL 语句的加锁顺序不一致， 就会导致事务之间的死锁。虽然 MySQL 数据库会自动检测死锁并强制回滚系统 认为代价较小的事务，但是死锁的检测与事务回滚都有较大的代价，会严重拖慢 数据库的性能，所以当系统中出现大量死锁时，开发者必须重视，要分析发生死 锁的事务 SQL 语句的加锁规则、调整 SQL 语句。通过 show engin innodb status 可 以查看死锁的相关信息及系统的处理过程，如图 4-40 所示。 

集群复制

数据安全：复制是 MySQL 多个节点之间实现数据同步的重要机制，主要用于搭 建高可用实例主从节点及提供多个只读从节点提高读扩展能力。节点之间的数 据是否最终一致对于高可用方案是否生效、只读实例读取的数据是否正确有着 严重影响。从机执行 show slave status 可以获取从机的复制状态，Slave_ IO_Running 和 Slave_SQL_Running 分别表示 I/O 和 SQL 线程是否正常运行，如 果不正常，就要及时处理。参数 relay_log_recovery 和 relay_log_info_repository 影响从节点宕机重启后，与主机的复制位置是否正确，位置错误可能导致数据 错误。

复制性能：复制延迟经常用来评估复制性能是否满足业务需求。Show slave status 的 Seconds behind master 字段标识了从机落后主机的延迟时间。如果延迟较长， 就会影响高可用实例主从切换的时间及只读从节点是否能够及时读到最新数据。 通过使用并行复制技术可以提高从节点的复制性能。MySQL 5.6 版本提供了基于

Database 级别的并行复制，通过 slave_parallel_workers 设置并行线程数；MySQL 5.7 版本提供了基于 LOGICAL_CLOCK 的并行复制，主机上同一个 Group 提交的 binlog 中包含事务在从机并行执行，相比 database，具备更高的并发性，除了设置 slave_parallel_workers，还需要将 slave-parallel-type 设置为 LOGICAL_CLOCK。 slave_preserve_commit_order=1 可以确保从机并行执行的事务按序提交。同时从机 的 log_bin 和 log_slave_updates 参数必须同时开启。

智能数据库健康诊断系统 

使用网易云基础服务的开发者，可以使用平台提供的智能健康诊断系统对数据库服务 中的关系型数据库实例进行自动健康检查。检查内容覆盖 6 个大类 22 个子项，检查结束后 根据检查结果，自动生成健康指数。开发者根据健康指数，可以快速判断系统存在的风险 严重程度，同时平台提供了该分数在所有实例的健康检查中的排名，如图 4-41 所示。 

有风险的项目平台会给出风险提示标识，开发者点击风险项目，会看到系统对该风险 的详细描述及相应的修复建议，如图 4-42 所示。针对部分检查内容，系统提供了一键自动 修复功能。 

图 4-42 风险描述及修复建议 

文章节选自《云原生应用架构实践》 网易云基础服务架构团队 著 

网易云计算基础服务深度整合了 IaaS、PaaS 及容器技术，提供弹性计算、DevOps 工具链及微服务基础设施等服务，帮助企业解决 IT、架构及运维等问题，使企业更聚焦于业务，是新一代的云计算平台。点击可免费试用。