通过用户反馈获取存在性能问题的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.log1.1.3 实时获取有性能问题的SQL(推荐)

SELECT id,user,host,DB,command,time,state,infoFROM information_schema.processlistWHERE TIME>=60查询当前服务器执行超过60s的SQL,可以通过脚本周期性的来执行这条SQL,就能查出有问题的SQL。

发送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。1.2.3 第二阶段:MySQL依照执行计划和存储引擎进行交互  这个阶段包括了多个子过程:

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

1、统计信息不准确2、成本估算与实际的执行计划成本不同

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

4、MySQL不考虑并发查询 5、会基于固定规则生成执行计划 6、MySQL不考虑不受其控制的成本,如存储过程,用户自定义函数

可以优化的sql类型

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

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

3、使用等价变换规则;

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

5、将一个表达式转换为常数;  6、子查询优化;

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

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

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的查询优化

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

(推荐)

修改语句这个样子:

alter table sbtest4 modify c varchar(150) not null default ''利用工具修改:

子查询改写为关联查询:

二、分库分表

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

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

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

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

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

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

来自:唐成勇

(本文版权归原作者所有。转载文章仅为传播更多信息之目的,如有侵权请与我们联系,我们将及时处理。)

推荐阅读

【推荐书籍】

扫码购买