内容简介:查询当前服务器执行超过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 复制代码
- 常用工具:
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 查询执行的大致过程:
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依照执行计划和存储引擎进行交互
这个阶段包括了多个子过程:
一条查询可以有多种查询方式,查询优化器会对每一种查询方式的(存储引擎)统计信息进行比较,找到成本最低的查询方式,这也就是索引不能太多的原因。
1.3 会造成MySQL生成错误的执行计划的原因
1、统计信息不准确
2、成本估算与实际的执行计划成本不同
3、给出的最优执行计划与估计的不同
4、MySQL不考虑并发查询
5、会基于固定规则生成执行计划
6、MySQL不考虑不受其控制的成本,如存储过程,用户自定义函数
1.4 MySQL优化器可优化的SQL类型
查询优化器:对查询进行优化并查询mysql认为的成本最低的执行计划。 为了生成最优的执行计划,查询优化器会对一些查询进行改写 复制代码
可以优化的sql类型
1、重新定义表的关联顺序;
2、将外连接转换为内连接;
3、使用等价变换规则;
4、优化count(),min(),max();
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 大表的结构修改
1.利用主从复制,先对从服务器进入修改,然后主从切换
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 如何选择分区键
1.分区键要能尽可能避免跨分区查询的发生
2.分区键要尽可能使各个分区中的数据平均
2.1.3.2 分片中如何生成全局唯一ID
扩展:表的垂直拆分和水平拆分
随着业务的发展,数据库成为了整个系统性能的一个瓶颈,这时候就需要对数据库进行优化,但是单单是优化只能提高有限的一点性能,这时候要想解决问题需要的是从数据库架构层面去思考问题。数据库的架构是一个很大的课题,里面最实用的有两个,一个是数据库拆分,一个是读写分离。今天就来谈谈数据库的两种拆分方式。
一、垂直拆分
垂直拆分很简单,就是根据不同的业务来划分不同的数据库。比如一个电商系统根据业务可以分成商品表、会员表、订单表。原先,这些表都是放在同一个数据库服务器上,现在需要垂直拆分数据库,就是将商品表单独放在一个数据库中,会员表单独放在一个数据库中,订单表单独放在一个数据库中,这样就解决了表与表之间的io竞争。
二、水平拆分
垂直拆分比较简单,水平拆分就比较复杂了,要考虑很多东西。垂直拆分根据业务来拆分,或者说的直白点就是根据表名来拆分,而水平拆分是根据表里面的字段来拆分(记住是根据字段来拆分,而不是拆分字段,拆分后的每一张表的表结构都是一样)。比如要拆分用户表,可以根据用户的注册时间这一字段来拆分整个表,2016年注册的用户放在用户表1中,2017年注册的用户放在用户表2中,2018年注册的用户放在用户表3中。这就是水平拆分,看似很简单,实际上要考虑的东西是很多的。就比如上述的例子,我们用时间来拆分,就会有局限性。一个好产品上线后,在开始的时候用户数量都是很少的,都需要一定时间的沉淀,才会有一个用户数量的爆发期。如果用时间来拆分,就会出现一种情况,就是用户表1的规模很小,而用户表2的规模却很大,是用户表1的好几倍,而用户表三可能是用户表1的好几十倍。这样的话,拆分水平拆分的意义就不大了。一般用户表都是用户id来拆分的,具体还要结合实际业务去分析。所以,水平拆分是一件很复杂的事情,大家在进行水平拆分的时候一定要考虑到方方面面,这样才能设计出优秀的数据库架构方案。
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网
猜你喜欢:- 数据库考点:为什么要分库分表?用过哪些分库分表中间件?不同的分库分表中间件都有什么优点和缺点?如何对数据库如何进行拆分?
- MySQL分库分表
- Mycat 和分库分表
- 浅谈分库分表那些事儿
- 分库分表后的索引问题
- 一次难得的分库分表实践
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。
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》 这本书的介绍吧!