IT忍者神龟之MySQL性能管理及架构设计SQL查询优化、分库分表

标签: Msql
4人阅读 评论(0) 收藏 举报
分类:

一、SQL查询优化(重要)


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


  1. 通过用户反馈获取存在性能问题的SQL;

  2. 通过慢查日志获取存在性能问题的SQL;

  3. 实时获取存在性能问题的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 查询过程描述(重点!!!)

images/TG8fyXa3hAQiwhdFpaCEsQEhbWtFWnmR.png


通过上图可以清晰的了解到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依照执行计划和存储引擎进行交互


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

images/hPkYaZ2QJ5mZN62CKaZN5wQ3zRDZHAKc.png


images/NQaGWKc8xhp6TKFR5Cnedcbn3eBANEiB.png


images/JJBYR8jH8esnxfsNiXchtAZBnFK8DyQ6.png


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


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

    1、统计信息不准确

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

images/Mb735icPzQRSeEBdGFpJjyiBK4cGYwT3.png


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

images/rjX2bczcfQFRw7XKQM7kYwz5BekZ6hEE.png


    4、MySQL不考虑并发查询

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

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


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

查询优化器:对查询进行优化并查询mysql认为的成本最低的执行计划。 为了生成最优的执行计划,查询优化器会对一些查询进行改写

 可以优化的sql类型


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

images/c3SfefTJ3FFNPEbfWAA84WSnfAHQQZmT.png

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


  3、使用等价变换规则;

images/inemjB2YP2z8FFctzfiD6fSGc4y6SyZp.png


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

images/Nn3Ga3yTQBRGBedSX4shadshfbsjJyKC.png


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

  6、子查询优化;

images/RXw35HWZxJSYdD6dWWQ5yYJHW3WBspQb.png


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

images/kQjcnze6ynHQRJ4MNYrDinh5s74cKB7d.png


images/5XpaY6RYessYeKthYDHWYB3GCiCYTZpA.png


1.6 特定SQL的查询优化

1.6.1 大表的数据修改

images/Sfpz5f5h5sx4rrw8FKPHFsRpWpHjfeKk.png


images/DymnpWHCDW5nKRxsDPQrNEx45KsTn33r.png


1.6.2 大表的结构修改

images/J78HeYG58DXxCkMXaXSczjSZPXMKeKfS.png


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

  2. (推荐)


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

images/JjtMkyDXpi6MQmxNdd2pDSaDN8AApCAi.png


修改语句这个样子:

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


利用工具修改:

images/Nw3XQ4ZrBKY6c8NWQ7SbzEcCE4XeGF6p.png


1.6.3 优化not in 和 <> 查询

  子查询改写为关联查询:

images/XmanjEbGEh5Hw2DktsjXRWyHix2w7rsr.png


二、分库分表

2.1 分库分表的几种方式

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

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

images/nhXGH7seRay5cWNPs566kKnxYbWMSa2j.png


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


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

images/3P5zmFey4RXJxP65mKaymmyYhKxJRakQ.png


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


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


2.1.3 数据库分片

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

images/PYYtMQEiYe8DiKpM7DmiznskFj7ACirJ.png


2.1.3.1 如何选择分区键


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

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


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

images/587WEjGKamJcd3KMK85J8sJbQG8RwpdY.png


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

查看评论

05 MYSQL性能管理及架构设计

  • 2018年03月03日 14:37
  • 55B
  • 下载

MySQL性能管理及架构设计(一):什么影响了数据库查询速度、什么影响了MySQL性能

一、什么影响了数据库查询速度1.1 影响数据库查询速度的四个因素    1.2 风险分析QPS:Queries Per Second意思是“每秒查询率”,是一台服务器每秒能够相应的查询次数,是对一个特...
  • u011652364
  • u011652364
  • 2018-03-30 09:44:19
  • 38

MySQL性能调优与架构设计

说明:本文转自 简朝阳(MySQL ACE)的 《MySQL性能调优与架构设计》 一.逻辑模块组成 总的来说,MySQL 可以看成是二层架构,第一层我们通常叫做SQL La...
  • sunshine_silence
  • sunshine_silence
  • 2017-03-17 17:58:56
  • 380

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

一、SQL查询优化(重要) 1.1 获取有性能问题SQL的三种方式 通过用户反馈获取存在性能问题的SQL; 通过慢查日志获取存在性能问题的SQL; 实时获取存在性能问题的SQL; 1.1.2...
  • whs_321
  • whs_321
  • 2018-03-27 10:55:12
  • 50

MySQL性能管理及架构设计(二):数据库结构优化、高可用架构设计、数据库索引优化

上一篇:MySQL性能管理及架构设计(一):什么影响了数据库查询速度、什么影响了MySQL性能一、数据库结构优化(非常重要)1.1 数据库结构优化目的    1、减少数据冗余:(数据冗余是指在数据库中...
  • u011652364
  • u011652364
  • 2018-03-30 09:48:47
  • 16

读《MySQL性能调优与架构设计》笔记之充分利用 Explain和Profiling

1.1. Explain 的使用     我们先看一下在MySQL Explain 功能中给我们展示的各种信息的解释:     ◆ ID:MySQL Query Optimizer选定的执行计划中...
  • lihuayong
  • lihuayong
  • 2015-01-18 18:08:23
  • 1273

【慕课网_性能优化之MySQL优化_学习】【03】

使用explain查询sql的执行计划 table: 查询的表 type: 可选值: const,eq_reg,ref,range,index,all  possible_keys: 可能应用在这个表...
  • HYH8557
  • HYH8557
  • 2017-10-25 17:55:51
  • 75

MySQL性能管理及架构设计(三):SQL查询优化、分库分表 - 完结篇

上一篇:MySQL性能管理及架构设计(二):数据库结构优化、高可用架构设计、数据库索引优化一、SQL查询优化(重要)1.1 获取有性能问题SQL的三种方式通过用户反馈获取存在性能问题的SQL;通过慢查...
  • u011652364
  • u011652364
  • 2018-03-30 09:55:37
  • 7

慕客网-MySql性能优化视频

http://www.imooc.com/view/194?from=csdn
  • zhpengfei0915
  • zhpengfei0915
  • 2014-10-27 10:59:42
  • 2149

MySQL数据库优化(五)——MySQL查询优化

一、mysql查询类型(默认查询出所有数据列) 1、内连接       默认多表关联查询方式,查询出两个表中所有字段;可省略inner join 关键字 2、外连接 查询出某一张表中的所有数据 ...
  • Daybreak1209
  • Daybreak1209
  • 2016-06-11 21:30:13
  • 2413
    个人资料
    持之以恒
    等级:
    访问量: 42万+
    积分: 1万+
    排名: 1818
    文章分类
    最新评论