MySQL数据库性能分析

敷衍一下X

433人浏览 · 2026-03-12 17:12:41

敷衍一下X · 2026-03-12 17:12:41 发布

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

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

数据库的性能测试范围

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连接索引条件时获取并集