MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定

栏目: 数据库 · 发布时间: 5年前

内容简介:查询当前服务器执行超过60s的SQL,可以通过脚本周期性的来执行这条SQL,就能查出有问题的SQL。1.发送SQL语句。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(推荐)

MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定
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性能管理及架构(查询优化、分库分表)一遍文章搞定
通过上图可以清晰的了解到 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依照执行计划和存储引擎进行交互

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

MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定
MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定
MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定

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

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

1、统计信息不准确

2、成本估算与实际的执行计划成本不同

MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定

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

MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定

4、MySQL不考虑并发查询

5、会基于固定规则生成执行计划

6、MySQL不考虑不受其控制的成本,如存储过程,用户自定义函数

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

查询优化器:对查询进行优化并查询mysql认为的成本最低的执行计划。

为了生成最优的执行计划,查询优化器会对一些查询进行改写
复制代码

可以优化的sql类型

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

MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定

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

3、使用等价变换规则;

MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定

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

MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定

5、将一个表达式转换为常数;

6、子查询优化;

MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定

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%';
复制代码
MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定
MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定

1.6 特定SQL的查询优化

1.6.1 大表的数据修改

MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定
MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定

1.6.2 大表的结构修改

MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定

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

2.(推荐)

添加一个新表(修改后的结构),老表数据导入新表,老表建立触发器,修改数据同步到新表,
老表加一个排它锁(重命名), 新表重命名, 删除老表。
复制代码
MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定

修改语句这个样子:

alter table sbtest4 modify c varchar(150) not null default ''
复制代码

利用 工具 修改:

MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定

1.6.3 优化not in 和 <> 查询

子查询改写为关联查询:

MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定

二、分库分表

2.1 分库分表的几种方式

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

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

MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定
拆分简单,不允许跨库。但并不能减少写负载
复制代码

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

MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定

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

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

2.1.3 数据库分片

对一个库中的相关表进行水平拆分到不同实例的数据库中
复制代码
MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定

2.1.3.1 如何选择分区键

1.分区键要能尽可能避免跨分区查询的发生

2.分区键要尽可能使各个分区中的数据平均

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

MySQL性能管理及架构(查询优化、分库分表)一遍文章搞定

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

随着业务的发展,数据库成为了整个系统性能的一个瓶颈,这时候就需要对数据库进行优化,但是单单是优化只能提高有限的一点性能,这时候要想解决问题需要的是从数据库架构层面去思考问题。数据库的架构是一个很大的课题,里面最实用的有两个,一个是数据库拆分,一个是读写分离。今天就来谈谈数据库的两种拆分方式。

一、垂直拆分

垂直拆分很简单,就是根据不同的业务来划分不同的数据库。比如一个电商系统根据业务可以分成商品表、会员表、订单表。原先,这些表都是放在同一个数据库服务器上,现在需要垂直拆分数据库,就是将商品表单独放在一个数据库中,会员表单独放在一个数据库中,订单表单独放在一个数据库中,这样就解决了表与表之间的io竞争。

二、水平拆分

垂直拆分比较简单,水平拆分就比较复杂了,要考虑很多东西。垂直拆分根据业务来拆分,或者说的直白点就是根据表名来拆分,而水平拆分是根据表里面的字段来拆分(记住是根据字段来拆分,而不是拆分字段,拆分后的每一张表的表结构都是一样)。比如要拆分用户表,可以根据用户的注册时间这一字段来拆分整个表,2016年注册的用户放在用户表1中,2017年注册的用户放在用户表2中,2018年注册的用户放在用户表3中。这就是水平拆分,看似很简单,实际上要考虑的东西是很多的。就比如上述的例子,我们用时间来拆分,就会有局限性。一个好产品上线后,在开始的时候用户数量都是很少的,都需要一定时间的沉淀,才会有一个用户数量的爆发期。如果用时间来拆分,就会出现一种情况,就是用户表1的规模很小,而用户表2的规模却很大,是用户表1的好几倍,而用户表三可能是用户表1的好几十倍。这样的话,拆分水平拆分的意义就不大了。一般用户表都是用户id来拆分的,具体还要结合实际业务去分析。所以,水平拆分是一件很复杂的事情,大家在进行水平拆分的时候一定要考虑到方方面面,这样才能设计出优秀的数据库架构方案。


以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Eloquent JavaScript

Eloquent JavaScript

Marijn Haverbeke / No Starch Press / 2011-2-3 / USD 29.95

Eloquent JavaScript is a guide to JavaScript that focuses on good programming techniques rather than offering a mish-mash of cut-and-paste effects. The author teaches you how to leverage JavaScript's......一起来看看 《Eloquent JavaScript》 这本书的介绍吧!

在线进制转换器
在线进制转换器

各进制数互转换器

随机密码生成器
随机密码生成器

多种字符组合密码

XML、JSON 在线转换
XML、JSON 在线转换

在线XML、JSON转换工具