sql查询优化

1.通过慢查询日志获取存在性能问题的sql   

2.实时获取存在性能问题的sql

慢查询日志：

show_query_log   启动停止记录慢查询日志 ：set global sql_query_log=on ；set global slow_query_log=on;

show_query_log_file 指定慢查日志的存储路径及文件

long_query_time  指定记录慢查日志sql执行时间的伐值  0.001s合适

log_queries_not_using_indexes   是否记录未使用索引的sql

3.常用慢查询日志分析工具：mysqldumpslow

mysqldumpslow -s r -t 10 slow-mysql.log

-s order（c，t，l，r，at，al，ar）{c：总次数，t ：总时间，l ：锁的时间，r： 总数据行，at al ar ：t，l，r平均数

}

-t top 指定取前几条作为结束输出

pt-query-digest  --explain h=127.0.0.1,u=root,p=p@ssword  show-mysql.log

实时获取有效性能问题的sql

information_schema库 processlist表

//查询时间大于60s的sql  

select id,user,host,DB,command,time,state,info from information_schema.`PROCESSLIST`   

4.对于一个读写频繁的系统使用查询缓存很可能降低查询处理的效率，所以在这种情况下不建议使用查询缓存

query_cache_type 设置查询缓存是否可用    on off demand 表示只有在查询语句中使用sql_cache和sql_no_cache来控制是否需要缓存

query_cache_size  设置查询缓存的内存大小 0

query_cache_limit  设置查询缓存可用存储的最大值

query_cache_wlock_invalidate 设置数据库表被锁后是否返回缓存中的数据

query_cache_min_res_unit 设置查询缓存分配的内存块最小单位

查询缓存对sql性能的影响  

5.mysql依照这个执行计划和存储引擎进行交互这个阶段包括了多个子过程：

解析sql，预处理，优化sql执行计划

语法解析阶段是通过关键字对mysql语句进行解析，

并生成一棵对应的 "解析树"

mysql 解析器将使用mysql语法规则验证和解析查询

6.如何确定查询各个阶段所消耗的时间

使用profile

减少查询所消耗的时间，加快查询的响应速度

使用set profiling=1；启动profile 这是一个session级的配置

执行查询

show profiles；
查看每一个查询所消耗的总时间的信息

show profile for query N；

查询每个阶段消耗的时间

 

 

< END >

程序员NBA

一个有故事的程序员

或者加我微信1414200300一起讨论java技术，有什么问题群里人也可以帮你一起解决