专栏首页数据和云技术核心 | MySQL性能结构优化原理

技术核心 | MySQL性能结构优化原理

一、SQL查询优化(重要)


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

  • 通过用户反馈获取存在性能问题的SQL;
  • 通过慢查日志获取存在性能问题的SQL;
  • 实时获取存在性能问题的SQL;

慢查日志分析工具

相关配置参数:

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

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

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

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

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

查询过程描述

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

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

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

第一阶段:

相关配置参数:

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。

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

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

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

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

  • 统计信息不准确
  • 成本估算与实际的执行计划成本不同
  • 给出的最优执行计划与估计的不同
  • MySQL不考虑并发查询
  • 会基于固定规则生成执行计划
  • MySQL不考虑不受其控制的成本,如存储过程,用户自定义函数

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

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

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

可以优化的sql类型

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

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

3. 使用等价变换规则;

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

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

6. 子查询优化;

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

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

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

使用profile

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

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.利用主从复制,先对从服务器进入修改,然后主从切换

2.(推荐)

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

修改语句这个样子:

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

利用工具修改:

优化not in 和 <> 查询

子查询改写为关联查询:

二、分库分表


2.1 分库分表的几种方式

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

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

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

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

该方式只能在一定时间内减少写压力。以上两种方式只能暂时解决读写性能问题。

数据库分片

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

如何选择分区键

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

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

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

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


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

一、垂直拆分

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

二、水平拆分

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

出处:https://www.jianshu.com/p/999537f158b1

本文分享自微信公众号 - 数据和云(OraNews),作者:若丨寒

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-05-07

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 【云和恩墨大讲堂】Oracle线上嘉年华第二讲

    编辑手记:Oracle线上嘉年华,正在持续分享中。本次的主题是系统割接中的SQL解析问题和结合业务的SQL优化改写技巧。 1 嘉宾介绍 小鱼(邓秋爽) 云和恩...

    数据和云
  • 将SQL优化做到极致 - 子查询优化

    编辑手记:子查询是SQL中比较重要的一种语法,恰当地应用会很大程度上提高SQL的性能,若用的不得当,也可能会带来很多问题。因此子查询也是SQL比较难优化的部分。...

    数据和云
  • 【新书连载】一波三折:释放内存导致数据库崩溃

    编辑说明:《Oracle性能优化与诊断案例精选》出版以来,收到很多读者的来信和评论,我们会通过连载的形式将书中内容公布出来,希望书中内容能够帮助到更多的读者朋友...

    数据和云
  • 软件推荐(Qtranslate) -- 宇宙翻译聚合神器

    今天是软件专场的倒数第95场,跟大家分享的是翻译神器,对,它就是Qtranslate。 下面我们把舞台交给QTranslate选手,大家掌声鼓励。

    丰臣正一
  • 白嫖JetBrains全家桶第二波与第三波

    第一波的JetBrains猜谜活动见我另外文章,提醒一下,第一波,第二波,第三波兑奖时间要在中欧时间3月15日之前。因为有许多人将最后一题的code泄露出去了,...

    编程之心
  • 大大提高开发效率的几个IDE:IntelliJ IDEA 相关常用快捷键操作整理

    Fisherman渔夫
  • 前端必备,Adobe Premiere Pro 常用快捷键

    J:倒序播放,多按几次加快播放。(注:应该理解为加速模式下先减速,减到常速后再按会倒序播放) L:正常顺序播放,多按几次快速播放。 K:停止播放。 ~:放...

    风骨散人Chiam
  • Django框架的权限组件rbac

    RBAC(Role-Based Access Control,基于角色的访问控制),就是用户通过角色与权限进行关联。简单地说,一个用户拥有若干角色,一个角色拥有...

    菲宇
  • Python爬虫|你真的会写爬虫吗?

    咱们直接进入今天的主题---你真的会写爬虫吗?为啥标题是这样,因为我们日常写小爬虫都是一个py文件加上几个请求,但是如果你去写一个正式的项目时,你...

    AI算法与图像处理
  • 爬虫入门实战课

    写在最前 通过爬虫,可以搜集互联网上很多信息,有助于科研(比如爬个会议的网站之类的),因此想以应用带动一下学习,因此就有了这个小练手。 爬虫代码的主要结构 一个...

    用户1148523

扫码关注云+社区

领取腾讯云代金券