# 开启慢查询日志 使用mysql慢查询日志对有效率问题的sql进行监控 ~~~ # 查看慢查询是否开启 show variables like 'slow_query_log' # 设置全局慢查询日志文件存放 set global slow_query_log_file='/home/mysql/sql_log/mysql-slow.log' # 设置没有用到索引的sql记录到慢查询中 set global log_queries_not_using_indexes=on; # 设置超时多长时间的就捕获 set global long_query_time=1 #这边设置的是1秒,一般我们设置100ms也就是0.01 ~~~ # 慢查询日志的格式  --- ## mysqldumpslow工具 使用mysqldumpslow工具对慢查询日志进行查看 ~~~ mysqldumpslow -h ~~~ 查看帮助命令 ~~~ 通过什么方式对慢查询结果进行排序 mysqldumpslow -s 查看前多少条日志 mysqldumpslow -t num 文件路径 ~~~ -s指定按那种排序方式输出结果  --- ## pt-query-digest 工具 这个工具对慢查询日志的分析比mysqldumpslow好  如何通过慢查询日志发现有问题的sql?  ## 实时获取有性能问题的sql 在information_schema数据库中查询processlist表 # 查询速度为什么会慢 5步 * 客户端发送sql请求给服务器 * 服务器检查是否可以在查询缓存中命中该sql,有就直接返回给客户端 * 服务器端进行sql解析,预处理,再由优化器生成对应的执行计划 * 根据执行计划,调用存储引擎API来查询数据 * 将结果返回给客户端 **第二步** 优先检查这个查询是否命中查询缓存中的数据 通过一个对大小写敏感的哈希查找实现 hash查找只能进行全值匹配 在查询缓存中命中该sql也会对缓存加锁,对于一个读写频繁的系统使用查询缓存很可能会降低查询处理的效率,如果这种情况下不建议使用 如果我们事先知道查询结果很大不会缓存,就加上sql_no_cache可以提高效率 设置变量 query_cache_type设置查询缓存是否可用 on,off,DEMAND DEMAND表示在查询语句中使用sql_cache和sql_no_cache来控制是否需要缓存 query_cache_size设置查询缓存的内存大小,1024整数倍 query_cache_limit设置查询缓存可用存储的最大值 query_cache_wlock_invalidate设置表被锁后是否返回缓存中的数据 query_cache_min_res_unit设置查询缓存分配的内存块最小单位 --- **第三步** mysql依照这个执行计划和存储引擎进行交互 这个阶段包括了多个子进程: 解析sql,预处理,优化sql执行计划 语法解析阶段是通过关键字对mysql语句进行解析,并生成一棵对应的"解析树" mysql解析器将使用mysql语法规则验证和解析查询,包括检查语法是否使用了正确的关键字 关键字的顺序是否正确 预处理阶段是根据mysql规则进一步检查解析树是否合法 检查查询中所涉及的表和数据列是否存在及名字或别名是否存在歧义等等 语法检查全都通过了,查询优化器就可以生成查询计划了 会造成mysql生成错误的执行计划的原因 * 统计信息不准确,mysql依赖存储引擎提供的信息来评估这个成本,不同的存储引擎提供的信息不同 * * 执行计划中的成本估算不等于实际的执行计划的成本 mysql服务层并不知道哪些页面在内存中 哪些页面在磁盘上,哪些需要顺序读取,哪些需要随机读取 * mysql查询优化器认为最优的可能与你认为的最优不一样 mysql是基于成本模型给出的最优执行计划 * mysql从不考虑其他的并发查询,这可能影响当前的查询的速度,比如锁 * mysql有时候也会基于一些固定规则来生成执行计划,不如使用了索引,mysql还是会使用全表扫描 * mysql不会考虑其不受控制的成本,存储过程,用户自定义函数这些都不在mysql考虑的范围之内 mysql优化器可以优化的sql类型 * 重新定义表的关联顺序 * 将外连接转化为内连接,left join where * 使用等价变化规则 * 优化count(),min(),max() 我们有时候会看到这样一个信息 select tables optimized away 优化器已经从执行计划中移除了该表,并以一个常数取而代之 * 将一个表达式转化为常数表达式 * 子查询优化 * 提前终止查询 * 对in()优化 如何确定查询处理各个阶段所消耗的时间 使用profile set profiling=1; 执行查询 show profiles; show profile for query N; N就是上面结果的query_id 查询每个阶段消耗的时间 show profiling cpu for query N; 查询cpu的消耗 使用performance_schema 启动 update 'setup_instruments' set enabled='yes' where name like 'stage%'; update setup_consumers set enabled='yes' where name like 'events%'; 分析