MySQL性能管理及架构设计: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、成本估算与实际的执行计划成本不同
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 大表的结构修改
利用主从复制,先对从服务器进入修改,然后主从切换
(推荐)
添加一个新表(修改后的结构),老表数据导入新表,老表建立触发器,修改数据同步到新表, 老表加一个排它锁(重命名), 新表重命名, 删除老表。
修改语句这个样子:
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
Mysql相关视频内容推荐:http://www.roncoo.com/course/list.html?courseName=mysql
低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。
持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。
转载内容版权归作者及来源网站所有,本站原创内容转载请注明来源。
- 上一篇
为什么说 Java 程序员到了必须掌握 Spring Boot 的时候?
Spring Boot 2.0 的推出又激起了一阵学习 Spring Boot 热,就单从我个人的博客的访问量大幅增加就可以感受到大家对学习 Spring Boot 的热情,那么在这么多人热衷于学习 Spring Boot 之时,我自己也在思考: Spring Boot 诞生的背景是什么?Spring 企业又是基于什么样的考虑创建 Spring Boot? 传统企业使用 Spring Boot 会给我们带来什么样变革? 带着这些问题,我们一起来了解下 Spring Boot 到底是什么? Spring 历史 说起 Spring Boot 我们不得不先了解一下 Spring 这个企业,不仅因为 Spring Boot 来源于 Spirng 大家族,而且 Spring Boot 的诞生和 Sping 框架的发展息息相关。 时间回到2002年,当时正是 Java EE 和 EJB 大行其道的时候,很多知名公司都是采用此技术方案进行项目开发。这时候有一个美国的小伙子认为 EJB 太过臃肿,并不是所有的项目都需要使用 EJB 这种大型框架,应该会有一种更好的方案来解决这个问题。 为了证明他的想法...
- 下一篇
阿里架构师分享的Java程序员需要突破的技术要点
一、源码分析 源码分析是一种临界知识,掌握了这种临界知识,能不变应万变,源码分析对于很多人来说很枯燥,生涩难懂。 源码阅读,我觉得最核心有三点:技术基础+强烈的求知欲+耐心。 我认为是阅读源码的最核心驱动力。我见到绝大多数程序员,对学习的态度,基本上就是这几个层次(很偏激哦): 1、只关注项目本身,不懂就baidu一下。 2、除了做好项目,还会阅读和项目有关的技术书籍,看wikipedia。 3、除了阅读和项目相关的书外,还会阅读IT行业的书,比如学Java时,还会去了解函数语言,如LISP。 4、找一些开源项目看看,大量试用第三方框架,还会写写demo。 5、阅读基础框架、J2EE规范、Debug服务器内核。 大多数程序都是第1种,到第5种不光需要浓厚的兴趣,还需要勇气:我能读懂吗?其实,你能够读懂的 耐心,真的很重要。因为你极少看到阅读源码的指导性文章或书籍,也没有人要求或建议你读。你读的过程中经常会卡住,而一卡主可能就陷进了迷宫。这时,你需要做的,可能是暂时中断一下,再从外围看看它:如API结构、框架的设计图。 下图是我总结出目前最应该学习的源码知识点: 二、分布式架构 分布式系...
相关文章
文章评论
共有0条评论来说两句吧...
文章二维码
点击排行
推荐阅读
最新文章
- CentOS6,7,8上安装Nginx,支持https2.0的开启
- CentOS7编译安装Gcc9.2.0,解决mysql等软件编译问题
- SpringBoot2配置默认Tomcat设置,开启更多高级功能
- Docker使用Oracle官方镜像安装(12C,18C,19C)
- Linux系统CentOS6、CentOS7手动修改IP地址
- Red5直播服务器,属于Java语言的直播服务器
- CentOS7,8上快速安装Gitea,搭建Git服务器
- CentOS8,CentOS7,CentOS6编译安装Redis5.0.7
- Windows10,CentOS7,CentOS8安装MongoDB4.0.16
- CentOS7编译安装Cmake3.16.3,解决mysql等软件编译问题