一、为什么要理解数据库的性能

数据库位于应用程序架构的最底层，是承载用户数据的核心组件，几乎所有用户操作都会涉及数据库交互，数据库读写速度直接影响用户体验，快速响应带来良好体验，慢速响应会导致用户不满

数据库的性能测试范围

• SQL语句的性能测试
• 数据库构架设计的合理性测试
• 数据库资源的使用率测试
• 数据库的性能指标关注

二、MySQL高性能数据库架构

1、单点数据库架构

（1）初始形态： 项目初期使用的孤零零的单一数据库实例，类似在CentOS/Ubuntu上搭建的基础MySQL

（2）操作特点：所有读写操作（增删改查）都集中在同一数据库上执行

（3）IO分类：数据库操作本质分为两类 - 写操作（增删改）和读操作（查），对应磁盘的IO操作类型

2、主备数据库架构

（1）组成结构：主数据库（Master）承担读写，备用数据库（Plan B）处于待命状态

（2）故障切换：当主库挂掉时，备库自动升级为主库；原主库恢复后变为备库

（3）存在问题：切换过程中会出现响应延迟，用户体验下降

（4）适用场景：作为用户量增长初期的过渡方案

3、读写分离架构

（1）架构原理：主库专注写操作，从库专注读操作，通过数据同步保持一致性

（2）同步机制：主库写入后立即同步到从库，确保读操作能获取最新数据

（3）扩展性: 主从库均可配置备用节点，形成多层容灾体系

（4）典型场景: 电商系统中，商品浏览（读）远多于下单支付（写）的操作比例

4、一主多从架构

（1）设计动机：应对读操作量远大于写操作量的业务场景（如登录>>注册、浏览>>购买）

（2）数据分发：

• 哈希策略: 按用户ID对从库数量取模（如user_id%3）固定分配
• 轮询策略: 依次分配请求到各从库

（3）同步挑战：跨地域部署可能导致网络抖动，产生数据延迟（如购物车添加后立即查看可能看不到）

（4）扩展能力：从库数量可线性增加（理论上无上限）

5、双机热备架构

（1）核心组件：通过Keepalived服务提供虚拟IP(VIP)，对客户端透明

（2）故障转移: 主库宕机时VIP自动漂移到从库，用户无感知

（3）硬件要求：主库需要较高配置，同时处理读写压力

（4）演变形态

• 双机热备：主+1从的基础配置
• 多机热备：主+多从的扩展配置

（5）解决痛点: 主要改善主从同步延迟问题，但非完美方案

三、海量数据下的分库分表策略

随着数据量增长，单库承载压力过大，更高级的拆分方案

1、拆分的原因

• 数据膨胀：持续写入导致单库/单表数据量过大（如电商系统订单表）
• 硬件限制：CPU核数（16核→32核）和内存（128G→256G）升级存在成本天花板

2、数据库拆分方案

（1）垂直拆分（按业务模块拆分）

将不同业务模块的表拆分到独立数据库，降低单库压力。

适用场景

• 业务模块间耦合度低，如电商系统中的订单库、用户库、商品库。
• 不同业务对数据库性能要求差异大，如高频交易与低频日志分开存储。

（2）水平拆分（按数据分片）

将同一表的数据按规则分散到多个库或表中，分为分库分表和分表不分库两种。

（3）混合拆分策略

结合垂直与水平拆分，例如先按业务垂直分库，再对单库内大表水平分表。

四、慢查询的定义与设置

1、基本概念

（1）本质特征：执行时间超过设定阈值的查询语句，且仅针对SELECT查询

（2）相对性：快慢是相对概念，需通过参数long_query_time明确定义时间阈值（如1秒）

（3）优化目标：专门捕捉执行时间大于阈值的SQL语句进行性能优化

2、设置方法

在配置文件中（如my.cnf）添加以下参数：

slow_query_log = 1  
slow_query_log_file = /var/log/mysql/mysql-slow.log  
long_query_time = 2  # 单位：秒，默认10秒，建议根据业务调整  
log_queries_not_using_indexes = 1  # 记录未使用索引的查询  
 

long_query_time定义慢查询的阈值（秒），log_queries_not_using_indexes记录未使用索引的查询。

3、分析工具

mysqldumpslow MySQL自带的工具，用于汇总慢查询日志中的SQL语句。常用命令：

mysqldumpslow -s t -t 10 /var/log/mysql/mysql-slow.log
 

-s t按总时间排序（降序），-s c按出现次数降序，-t 10显示前10条记录。

五、使用执行计划对SQL语句进行性能分析

1、定义

EXPLAIN（执行计划）是用于分析SQL查询性能的关键词，通过优化索引方案提升查询速度

2、语法

 在SELECT语句前添加EXPLAIN

3、限制

只能用于查询语句（SELECT），不能用于INSERT/UPDATE/DELETE等操作

4、返回结果说明

（1）id：代表着 sql 语句的执行顺序。当嵌套查询等多个 select 的情况会出现不同的值。

• id 这列数字越大越代表着这条 sql 语句是先被执行的。
• 当数字一样大时，那么就从上往下依次执行。
• 当 id 列为 null 的时候，就代表这是一个结果集，不需要使用它来进行查询。

（2）select_type

• SIMPLE：简单查询，不包含子查询或UNION操作
• PRIMARY：包含子查询的最外层查询
• UNION：UNION操作中第二个及以后的SELECT语句
• DEPENDENT UNION：受外部查询影响的UNION查询
• UNION RESULT：UNION操作的结果集，id列为NULL
• SUBQUERY：FROM子句外的子查询
• DEPENDENT SUBQUERY：受外部查询影响的子查询
• DERIVED：FROM子句中的子查询（派生表）

（3）table：显示的查询表名

• 别名显示：查询使用别名时显示别名
• 临时表标识：<derived N>表示临时表，N为执行顺序
• UNION结果：<union M,N>表示UNION查询的临时结果集

（4）type：显示了连接类别，有没有用到索引

• 性能排序：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
• system：表中只有一行数据或空表（仅MyISAM/Memory引擎）
• const：使用主键或唯一索引的等值查询
• eq_ref：多表连接中驱动表返回单行数据且匹配第二表主键
• ref：使用非唯一索引的等值查询
• range：索引范围扫描（>,<,BETWEEN,IN等操作）
• index：全索引扫描
• ALL：全表扫描（性能最差）
• ref_or_null：类似ref但增加了NULL值比较
• index_subquery：IN子查询使用辅助索引去重
• index_merge：使用多个索引取交集/并集

注意

• 除ALL外其他type都可能使用索引
• 除index_merge外其他type只能用一个索引

（5）possible_keys

• 可能使用的索引: 查询时可能使用到的索引都会在这里列出来
• 空值判断: 如果显示为null，则表示没有使用到相关索引
• 实际案例: 在查询分析中可能出现"index2"等具体索引

（6）key

• 实际使用的索引: 显示查询真正使用到的索引
• 特殊情况处理:
  • 当select_type为index_merge时，可能出现两个以上的索引
  • 其他select_type值只会出现一个索引
• 空值情况: 如果没有用到索引，则值为null
• 重要性: 该字段非常重要，直接标识查询是否使用了索引

（7）key_len

• 索引长度计算:
  • 单列索引：计算整个索引长度
  • 多列索引：只计算实际使用到的列的长度
• 优化原则: 在不损失精确性的情况下，长度越短越好
• 空值处理: 如果键是NULL，则长度也为NULL
• 实际案例: 查询中可能出现长度为4或5的索引

（8）ref

• 作用: 显示使用哪个列、常数与key一起从表中选择行
• 不同查询类型:
  • 常数等值查询：显示const
  • 连接查询：显示驱动表的关联字段
  • 使用表达式/函数：可能显示func
• 特殊情况: 当条件列发生内部隐式转换时也会显示func

（9）rows

• 估算行数: 执行计划中估算的扫描行数，不是精确值
• 优化指标: 该数值越小越好，数值大表示查询效率低
• 实际案例: 查询中可能出现13行、36行甚至17977行等不同值

（10）extra

• 常见值及含义:
  • using index: 直接通过索引获取数据，性能好
  • using where: 使用WHERE条件过滤数据
  • using filesort: 排序时无法使用索引，性能较差
  • using temporary: 使用临时表存储中间结果
  • using join buffer: 5.6+版本优化关联查询的特性
  • distinct: 使用distinct关键字去重
• 临时表说明:
  • 可以是内存或磁盘临时表
  • 多列order by等情况会使用临时表
• 连接优化:
  • using intersect: AND连接索引条件时获取交集
  • using union: OR连接索引条件时获取并集