MySQL性能管理及架构设计:SQL查询优化、分库分表

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
日志服务 SLS,月写入数据量 50GB 1个月
简介: SQL查询优化、分库分表

一、SQL查询优化(重要)

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

通过用户反馈获取存在性能问题的SQL;
通过慢查日志获取存在性能问题的SQL;
实时获取存在性能问题的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

常用工具:mysqldumpslow和pt-query-digest

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

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

  SELECT id,user,host,DB,command,time,state,info
FROM information_schema.processlist
WHERE TIME>=60

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

1.2 SQL的解析预处理及生成执行计划(重要)
1.2.1 查询过程描述(重点!!!)

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

发送SQL语句。
查询缓存,如果命中缓存直接返回结果。
SQL解析,预处理,再由优化器生成对应的查询执行计划。
执行查询,调用存储引擎API获取数据。
返回结果。

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依照执行计划和存储引擎进行交互

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



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

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

1、统计信息不准确
2、成本估算与实际的执行计划成本不同
![](http://static.roncoo.com/images/Mb735icPzQRSeEBdGFpJjyiBK4cGYwT3.png)

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

4、MySQL不考虑并发查询

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

1.4 MySQL优化器可优化的SQL类型
查询优化器:对查询进行优化并查询mysql认为的成本最低的执行计划。 为了生成最优的执行计划,查询优化器会对一些查询进行改写
可以优化的sql类型

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

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

3、使用等价变换规则;

4、优化count(),min(),max();
![](http://static.roncoo.com/images/Nn3Ga3yTQBRGBedSX4shadshfbsjJyKC.png)
  5、将一个表达式转换为常数;

6、子查询优化;

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%';



1.6 特定SQL的查询优化
1.6.1 大表的数据修改


1.6.2 大表的结构修改

利用主从复制,先对从服务器进入修改,然后主从切换
(推荐)

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

修改语句这个样子:

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

利用工具修改:

1.6.3 优化not in 和 <> 查询
子查询改写为关联查询:

二、分库分表
2.1 分库分表的几种方式
分担读负载 可通过 一主多从,升级硬件来解决。
2.1.1 把一个实例中的多个数据库拆分到不同实例(集群)

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

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

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

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

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

2.1.3.1 如何选择分区键

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

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

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

文章来源:https://segmentfault.com/a/1190000013781544

相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL,然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助 &nbsp; &nbsp; 相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
目录
相关文章
|
2月前
|
缓存 JSON 关系型数据库
MySQL 查询优化分析 - 常用分析方法
本文介绍了MySQL查询优化分析的常用方法EXPLAIN、Optimizer Trace、Profiling和常用监控指标。
|
2月前
|
存储 SQL 关系型数据库
京东面试:mysql深度分页 严重影响性能?根本原因是什么?如何优化?
京东面试:mysql深度分页 严重影响性能?根本原因是什么?如何优化?
京东面试:mysql深度分页 严重影响性能?根本原因是什么?如何优化?
|
4月前
|
存储 关系型数据库 MySQL
MySQL细节优化:关闭大小写敏感功能的方法。
通过这种方法,你就可以成功关闭 MySQL 的大小写敏感功能,让你的数据库操作更加便捷。
258 19
|
4月前
|
关系型数据库 MySQL 数据库
|
5月前
|
SQL 存储 缓存
MySQL的架构与SQL语句执行过程
MySQL架构分为Server层和存储引擎层,具有高度灵活性和可扩展性。Server层包括连接器、查询缓存(MySQL 8.0已移除)、分析器、优化器和执行器,负责处理SQL语句;存储引擎层负责数据的存储和读取,常见引擎有InnoDB、MyISAM和Memory。SQL执行过程涉及连接、解析、优化、执行和结果返回等步骤,本文详细讲解了一条SQL语句的完整执行过程。
187 3
|
5月前
|
SQL 关系型数据库 MySQL
基于SQL Server / MySQL进行百万条数据过滤优化方案
对百万级别数据进行高效过滤查询,需要综合使用索引、查询优化、表分区、统计信息和视图等技术手段。通过合理的数据库设计和查询优化,可以显著提升查询性能,确保系统的高效稳定运行。
177 9
|
5月前
|
关系型数据库 MySQL
图解MySQL【日志】——磁盘 I/O 次数过高时优化的办法
当 MySQL 磁盘 I/O 次数过高时,可通过调整参数优化。控制刷盘时机以降低频率:组提交参数 `binlog_group_commit_sync_delay` 和 `binlog_group_commit_sync_no_delay_count` 调整等待时间和事务数量;`sync_binlog=N` 设置 write 和 fsync 频率,`innodb_flush_log_at_trx_commit=2` 使提交时只写入 Redo Log 文件,由 OS 择机持久化,但两者在 OS 崩溃时有丢失数据风险。
129 3
|
7月前
|
SQL 存储 关系型数据库
MySQL进阶突击系列(01)一条简单SQL搞懂MySQL架构原理 | 含实用命令参数集
本文从MySQL的架构原理出发,详细介绍其SQL查询的全过程,涵盖客户端发起SQL查询、服务端SQL接口、解析器、优化器、存储引擎及日志数据等内容。同时提供了MySQL常用的管理命令参数集,帮助读者深入了解MySQL的技术细节和优化方法。
|
SQL 存储 关系型数据库
MySQL下使用SQL命令进行表结构与数据复制实践
MySQL下使用SQL命令进行表结构与数据复制实践
201 0
|
SQL 关系型数据库 MySQL
MySQl数据库第八课-------SQL命令查询-------主要命脉2
MySQl数据库第八课-------SQL命令查询-------主要命脉
131 0

推荐镜像

更多