MySQL性能管理及架构设计(三):SQL查询优化、分库分表 - 完结篇

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
云数据库 RDS PostgreSQL,高可用系列 2核4GB
简介:

一、SQL查询优化(重要

1.1 获取有性能问题SQL的三种方式

  1. 通过用户反馈获取存在性能问题的SQL;

  2. 通过慢查日志获取存在性能问题的SQL;

  3. 实时获取存在性能问题的SQL;

1.1.2 慢查日志分析工具

相关配置参数:


slow_query_log # 启动停止记录慢查日志,慢查询日志默认是没有开启的可以在配置文件中开启(on)slow_query_log_file # 指定慢查日志的存储路径及文件,日志存储和数据从存储应该分开存储long_query_time # 指定记录慢查询日志SQL执行时间的阀值默认值为10秒通常,对于一个繁忙的系统来说,改为0.001秒(1毫秒)比较合适log_queries_not_using_indexes #是否记录未使用索引的SQL

常用工具:mysqldumpslowpt-query-digest


pt-query-digest --explain h=127.0.0.1,u=root,p=p@ssWord slow-mysql.log

1.1.3 实时获取有性能问题的SQL(推荐

559b925ff63530fabf879a1b51a932b35f19ac93
SELECT id,user,host,DB,command,time,state,infoFROM information_schema.processlistWHERE TIME>=60

查询当前服务器执行超过60sSQL,可以通过脚本周期性的来执行这条SQL,就能查出有问题的SQL

1.2 SQL的解析预处理及生成执行计划(重要

1.2.1 查询过程描述(重点!!!


6834c773ec7f48f14755d20b7f65f03933411327

通过上图可以清晰的了解到MySql查询执行的大致过程:

  1. 发送SQL语句。

  2. 查询缓存,如果命中缓存直接返回结果。

  3. SQL解析,预处理,再由优化器生成对应的查询执行计划。

  4. 执行查询,调用存储引擎API获取数据。

  5. 返回结果。

1.2.2 查询缓存对性能的影响(建议关闭缓存

第一阶段:
相关配置参数:


query_cache_type # 设置查询缓存是否可用query_cache_size # 设置查询缓存的内存大小query_cache_limit # 设置查询缓存可用的存储最大值(加上sql_no_cache可以提高效率)query_cache_wlock_invalidate # 设置数据表被锁后是否返回缓存中的数据query_cache_min_res_unit # 设置查询缓存分配的内存块的最小单
缓存查找是利用对大小写敏感的哈希查找来实现的,Hash查找只能进行全值查找(sql完全一致),如果缓存命中,检查用户权限,如果权限允许,直接返回,查询不被解析,也不会生成查询计划。

在一个读写比较频繁的系统中,建议关闭缓存,因为缓存更新会加锁。将query_cache_type设置为off,query_cache_size设置为0

1.2.3 第二阶段:MySQL依照执行计划和存储引擎进行交互

这个阶段包括了多个子过程:


d12c547b434b9fdfe47ecd0872ffe9679c558ac8

一条查询可以有多种查询方式,查询优化器会对每一种查询方式的(存储引擎)统计信息进行比较,找到成本最低的查询方式,这也就是索引不能太多的原因

1.3 会造成MySQL生成错误的执行计划的原因

1、统计信息不准确
2、成本估算与实际的执行计划成本不同

a7c307c09905286a78beda8cbac92e428025e625

3、给出的最优执行计划与估计的不同

5c70589fbf2547616ac66a285c3a6666c13da8d0

4、MySQL不考虑并发查询
5、会基于固定规则生成执行计划
6、MySQL不考虑不受其控制的成本,如存储过程,用户自定义函数

1.4 MySQL优化器可优化的SQL类型

查询优化器:对查询进行优化并查询mysql认为的成本最低的执行计划。 为了生成最优的执行计划,查询优化器会对一些查询进行改写

可以优化的sql类型

1、重新定义表的关联顺序;

9086a814f6ffb2aff6786c5c36e4a06aa629d269

2、将外连接转换为内连接;

3、使用等价变换规则;

be42c85cf05bc4f5dd9a5f5d5c391a6d27aa6c2f

4、优化count(),min(),max();

bf5f8ba5a825f7e84875565b398b89a62bc19559

5、将一个表达式转换为常数;
6、子查询优化;
0da9d9bc5a91496f722cb603ccc379b8133e01bd

7、提前终止查询,如发现一个不成立条件(如where id = -1),立即返回一个空结果;

8、对in()条件进行优化;

1.5 查询处理各个阶段所需要的时间

1.5.1 使用profile(目前已经不推荐使用了)

set profiling = 1; #启动profile,这是一个session级的配制执行查询show profiles; # 查询每一个查询所消耗的总时间的信息show profiles for query N; # 查询的每个阶段所消耗的时间

1.5.2 performance_schema是5.5引入的一个性能分析引擎(5.5版本时期开销比较大)


启动监控和历史记录表:use performance_schema


update setup_instruments set enabled='YES',TIME = 'YES' WHERE NAME LIKE 'stage%';update set_consumbers set enabled='YES',TIME = 'YES' WHERE NAME LIKE 'event%';

c083a1a4e20aeebe0266a606810da2b026021a1a

1.6 特定SQL的查询优化

1.6.1 大表的数据修改

3ae447660cbaf33c2b849e1cb24253b52ec5ac2a

1.6.2 大表的结构修改

50a0de3e8d4dfa6dba45d38b7c38d26dae40cdd2
  1. 利用主从复制,先对从服务器进入修改,然后主从切换

  2. (推荐)

添加一个新表(修改后的结构),老表数据导入新表,老表建立触发器,修改数据同步到新表, 老表加一个排它锁(重命名), 新表重命名, 删除老表。
b9e43625bb84de720ba2870c32ca6c79128a5b82

修改语句这个样子:


alter table sbtest4 modify c varchar(150) not null default ''

利用工具修改:

f371846c5e50c84bb9ba5b947df0ee96df86a5cc

1.6.3 优化not in 和 <> 查询

子查询改写为关联查询:

74637baace7c94807d624b161fe9be6fd3f7b6ef

二、分库分表

2.1 分库分表的几种方式

分担读负载 可通过 一主多从,升级硬件来解决。

2.1.1 把一个实例中的多个数据库拆分到不同实例(集群)


7c37174f9df50e01f6ffa531e1e28e89aa84126b

拆分简单,不允许跨库。但并不能减少写负载。

2.1.2 把一个库中的表分离到不同的数据库中


ecd6d2916fb3a2ebb878ba89700e0fd2c666716e

该方式只能在一定时间内减少写压力。

以上两种方式只能暂时解决读写性能问题。

2.1.3 数据库分片

对一个库中的相关表进行水平拆分到不同实例的数据库中
a1285f738d1c3b05f6eafd3cab065c6250be0b7e

2.1.3.1 如何选择分区键

  1. 分区键要能尽可能避免跨分区查询的发生
  2. 分区键要尽可能使各个分区中的数据平均

2.1.3.2 分片中如何生成全局唯一ID

6d32b717b1094888115e049fb48fb0d02951154e

扩展:表的垂直拆分和水平拆分




原文发布时间为:2018-09-19

本文作者:唐成勇

本文来自云栖社区合作伙伴“云时代架构”,了解相关信息可以关注“云时代架构”。

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
2月前
|
负载均衡 算法 关系型数据库
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
本文聚焦 MySQL 集群架构中的负载均衡算法,阐述其重要性。详细介绍轮询、加权轮询、最少连接、加权最少连接、随机、源地址哈希等常用算法,分析各自优缺点及适用场景。并提供 Java 语言代码实现示例,助力直观理解。文章结构清晰,语言通俗易懂,对理解和应用负载均衡算法具有实用价值和参考价值。
大数据大厂之MySQL数据库课程设计:揭秘MySQL集群架构负载均衡核心算法:从理论到Java代码实战,让你的数据库性能飙升!
|
3月前
|
机器学习/深度学习 人工智能 文件存储
Llama Nemotron:英伟达开源基于Llama架构优化的推理模型,253B参数持平DeepSeek R1!
NVIDIA推出的Llama Nemotron系列推理模型,基于Llama架构优化,包含Nano/Super/Ultra三款,在数学推理、编程和工具调用等任务中展现卓越性能。
113 5
Llama Nemotron:英伟达开源基于Llama架构优化的推理模型,253B参数持平DeepSeek R1!
|
3月前
|
数据采集 运维 Serverless
云函数采集架构:Serverless模式下的动态IP与冷启动优化
本文探讨了在Serverless架构中使用云函数进行网页数据采集的挑战与解决方案。针对动态IP、冷启动及目标网站反爬策略等问题,提出了动态代理IP、请求头优化、云函数预热及容错设计等方法。通过网易云音乐歌曲信息采集案例,展示了如何结合Python代码实现高效的数据抓取,包括搜索、歌词与评论的获取。此方案不仅解决了传统采集方式在Serverless环境下的局限,还提升了系统的稳定性和性能。
|
26天前
|
关系型数据库 MySQL 分布式数据库
Super MySQL|揭秘PolarDB全异步执行架构,高并发场景性能利器
阿里云瑶池旗下的云原生数据库PolarDB MySQL版设计了基于协程的全异步执行架构,实现鉴权、事务提交、锁等待等核心逻辑的异步化执行,这是业界首个真正意义上实现全异步执行架构的MySQL数据库产品,显著提升了PolarDB MySQL的高并发处理能力,其中通用写入性能提升超过70%,长尾延迟降低60%以上。
|
2月前
|
SQL 数据采集 关系型数据库
实现MySQL与SQL Server之间数据迁移的有效方法
总的来说,从MySQL到SQL Server的数据迁移是一个涉及到很多步骤的过程,可能会遇到各种问题和挑战。但只要精心规划、仔细执行,这个任务是完全可以完成的。
134 18
|
2月前
|
SQL 存储 自然语言处理
SQL的解析和优化的原理:一条sql 执行过程是什么?
SQL的解析和优化的原理:一条sql 执行过程是什么?
SQL的解析和优化的原理:一条sql 执行过程是什么?
|
3月前
|
缓存 监控 安全
301重定向进阶指南:从基础配置到企业级架构优化
本文深入探讨网站重定向的高级技巧与企业级实现,涵盖正则表达式重定向、权重无损迁移、分布式系统适配等核心内容。通过解析301/302状态码区别及应用场景,结合Nginx、Apache配置示例,帮助开发者优化大规模网站重定向逻辑。同时,文章介绍CDN边缘重定向、微服务架构下的规则管理以及容灾设计,确保高性能与安全性。最后提供全链路监控方案和经典案例分析,助你规避流量损失风险,提升SEO表现。
128 38
|
3月前
|
SQL 关系型数据库 MySQL
【MySQL】SQL分析的几种方法
以上就是SQL分析的几种方法。需要注意的是,这些方法并不是孤立的,而是相互关联的。在实际的SQL分析中,我们通常需要结合使用这些方法,才能找出最佳的优化策略。同时,SQL分析也需要对数据库管理系统,数据,业务需求有深入的理解,这需要时间和经验的积累。
108 12
|
3月前
|
负载均衡 算法 关系型数据库
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL集群架构负载均衡故障排除与解决方案
本文深入探讨 MySQL 集群架构负载均衡的常见故障及排除方法。涵盖请求分配不均、节点无法响应、负载均衡器故障等现象,介绍多种负载均衡算法及故障排除步骤,包括检查负载均衡器状态、调整算法、诊断修复节点故障等。还阐述了预防措施与确保系统稳定性的方法,如定期监控维护、备份恢复策略、团队协作与知识管理等。为确保 MySQL 数据库系统高可用性提供全面指导。
|
3月前
|
SQL 关系型数据库 MySQL
大数据新视界--大数据大厂之MySQL数据库课程设计:MySQL 数据库 SQL 语句调优方法详解(2-1)
本文深入介绍 MySQL 数据库 SQL 语句调优方法。涵盖分析查询执行计划,如使用 EXPLAIN 命令及理解关键指标;优化查询语句结构,包括避免子查询、减少函数使用、合理用索引列及避免 “OR”。还介绍了索引类型知识,如 B 树索引、哈希索引等。结合与 MySQL 数据库课程设计相关文章,强调 SQL 语句调优重要性。为提升数据库性能提供实用方法,适合数据库管理员和开发人员。

推荐镜像

更多