MySQL数据库性能分析

一、为什么要理解数据库的性能数据库位于应用程序架构的最底层是承载用户数据的核心组件几乎所有用户操作都会涉及数据库交互数据库读写速度直接影响用户体验快速响应带来良好体验慢速响应会导致用户不满数据库的性能测试范围SQL语句的性能测试数据库构架设计的合理性测试数据库资源的使用率测试数据库的性能指标关注二、MySQL高性能数据库架构1、单点数据库架构1初始形态 项目初期使用的孤零零的单一数据库实例类似在CentOS/Ubuntu上搭建的基础MySQL2操作特点所有读写操作增删改查都集中在同一数据库上执行3IO分类数据库操作本质分为两类 - 写操作增删改和读操作查对应磁盘的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、分析工具mysqldumpslowMySQL自带的工具用于汇总慢查询日志中的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语句前添加EXPLAIN3、限制只能用于查询语句SELECT不能用于INSERT/UPDATE/DELETE等操作4、返回结果说明1id代表着 sql 语句的执行顺序。当嵌套查询等多个 select 的情况会出现不同的值。id 这列数字越大越代表着这条 sql 语句是先被执行的。当数字一样大时那么就从上往下依次执行。当 id 列为 null 的时候就代表这是一个结果集不需要使用它来进行查询。2select_typeSIMPLE简单查询不包含子查询或UNION操作PRIMARY包含子查询的最外层查询UNIONUNION操作中第二个及以后的SELECT语句DEPENDENT UNION受外部查询影响的UNION查询UNION RESULTUNION操作的结果集id列为NULLSUBQUERYFROM子句外的子查询DEPENDENT SUBQUERY受外部查询影响的子查询DERIVEDFROM子句中的子查询派生表3table显示的查询表名别名显示查询使用别名时显示别名临时表标识derived N表示临时表N为执行顺序UNION结果union M,N表示UNION查询的临时结果集4type显示了连接类别有没有用到索引性能排序system const eq_ref ref fulltext ref_or_null index_merge unique_subquery index_subquery range index ALLsystem表中只有一行数据或空表仅MyISAM/Memory引擎const使用主键或唯一索引的等值查询eq_ref多表连接中驱动表返回单行数据且匹配第二表主键ref使用非唯一索引的等值查询range索引范围扫描,,BETWEEN,IN等操作index全索引扫描ALL全表扫描性能最差ref_or_null类似ref但增加了NULL值比较index_subqueryIN子查询使用辅助索引去重index_merge使用多个索引取交集/并集注意除ALL外其他type都可能使用索引除index_merge外其他type只能用一个索引5possible_keys可能使用的索引: 查询时可能使用到的索引都会在这里列出来空值判断: 如果显示为null则表示没有使用到相关索引实际案例: 在查询分析中可能出现index2等具体索引6key实际使用的索引: 显示查询真正使用到的索引特殊情况处理:当select_type为index_merge时可能出现两个以上的索引其他select_type值只会出现一个索引空值情况: 如果没有用到索引则值为null重要性: 该字段非常重要直接标识查询是否使用了索引7key_len索引长度计算:单列索引计算整个索引长度多列索引只计算实际使用到的列的长度优化原则: 在不损失精确性的情况下长度越短越好空值处理: 如果键是NULL则长度也为NULL实际案例: 查询中可能出现长度为4或5的索引8ref作用: 显示使用哪个列、常数与key一起从表中选择行不同查询类型:常数等值查询显示const连接查询显示驱动表的关联字段使用表达式/函数可能显示func特殊情况: 当条件列发生内部隐式转换时也会显示func9rows估算行数: 执行计划中估算的扫描行数不是精确值优化指标: 该数值越小越好数值大表示查询效率低实际案例: 查询中可能出现13行、36行甚至17977行等不同值10extra常见值及含义:using index: 直接通过索引获取数据性能好using where: 使用WHERE条件过滤数据using filesort: 排序时无法使用索引性能较差using temporary: 使用临时表存储中间结果using join buffer: 5.6版本优化关联查询的特性distinct: 使用distinct关键字去重临时表说明:可以是内存或磁盘临时表多列order by等情况会使用临时表连接优化:using intersect: AND连接索引条件时获取交集using union: OR连接索引条件时获取并集