MySQL学习(六)--慢查询日志

sql查询优化
查询优化，索引优化，库表结构优化需要齐头并进。
1.通过用户反馈获取存在性能问题的SQL
2.通过慢查询日志获取存在性能问题SQL
3.实时获取存在性能问题的SQL

使用慢查询日志获取性能问题的SQL
show_query_log 启动停止巨鹿慢查日志 ON  set
show_query_log_file 指定慢查询日志的存储路径及文件
long_query_time 指定记录慢查询日志SQL执行时间的伐值
log_queries_not_using_indexes 是否记录未使用索引的SQL

慢查询分析工具(mysqldumpslow)

1.先看mysql是否开启慢查询:

注： 
long_query_time     ：  设定慢查询的阀值，超出次设定值的SQL即被记录到慢查询日志，缺省值为10s
slow_query_log      ：  指定是否开启慢查询日志
log_slow_queries    ：  指定是否开启慢查询日志(该参数要被slow_query_log取代，做兼容性保留)
slow_query_log_file ：  指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log
min_examined_row_limit：查询检查返回少于该参数指定行的SQL不被记录到慢查询日志
log_queries_not_using_indexes: 不使用索引的慢查询日志是否记录到索引

我服务器用的宝塔，直接输入mysql命令都在www/server/mysql/bin里:
mysqldumpslow -s r -t 10 slow-mysql.log 
-s order(c,t,l,r,at,al,ar)//指定按哪种排序方式输出结果
-t top //指定取前几条作为结束输出
我输入mysqldumpslow错误  -bash: mysqldumpslow: command not found:
解决：ln -d mysql的安装目录/bin /usr/bin；
 工具pt-query-digest
 --explain h=127.0.01,u=root,p=p@ssword \slow-mysql.log

 实时获取有性能问题的SQL
 information_schema=PROCESSLIST

 select id,'user','host',DB,command,'time',state,info FROM information_schema.PROCESSLIST WHERE TIME>=60

 客户端发送SQL请求给服务器
 服务器检查是否可以在查询缓存中命中该SQL
 服务器端进行SQL解析，预处理，再由优化器生成对应的执行计划
 根据执行计划，调用存储引擎API来查询数据

 优先检查这个查询是否命中查询缓存中的数据。
 通过一个对大小写敏感的哈希查找实现的。

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

 query_chche_type 设置查询缓存是否可用 OFF
 query_chche_size 设置查询缓存的内存大小 0
 query_cache_limit 设置查询缓存可用存储的最大值
 query_chche_elock_invalidate 设置数据表被锁后是否返回缓存中的数据
 query_cache_min_res_unit设置查询缓存分配的内存最小单位

 解析sql，预处理，优化SQl执行计划
 重新定义表的关联顺序
 将外连接转化成内连接
 使用等价变换的规则

 优化count()、min()和max()

如何确定查询处理各个阶段所消耗的时间:
工具:
使用profile
set profiling=1;
执行查询
show profiles;
查看每一个查询所消耗的总时间的信息;
show profile for query N;
show profile cpu for queery 3;

使用performance_schema
启动查询：
UPDATE  'SETUP_INSTRUMENTS'SET enabled='YES' WHERE NAME LIKE 'stage%';

UPDATE setup_consumers SET enabled='YES' WHERE NAME LIKE'evens%';

大表的更新和删除
DELIMIER && USE 'imooc' &&DROP PROCEDURE IF EXIXTS 'p_delete_rows' && CREATE DEFINER='root@127.0.0.1' PRCOEDURE 'P_DELETE_ROWS'()
BEFGIN
 DECLARE verws INT;
 SET v_rows=1;
 WHERE v_rows>0
 DO
  (DELETE FROM sbtest1 WHERE id >=90000 AND id<=19000LIMIT 5000;)
  SELECT ROW_COUNT() INTO V_RPWS;
  select sleep(5);
  END WHILE;
  END$$
  DELIMITER; 

  如何修改大表的表结构
      对表中的列的字段类型进行修改改变字段的宽度时还是会锁表
      主从切换
      主服务器建立新表，老表数据导过去，写触发器，同步到新表上，老表加排他锁，最后新表重新命名，删除老表(降低主从延迟)
      pt_online-schema-change\
      --alter='MODIFY c VARCHAR(150) NOT NULL DEFAULT'\
      --user=root--password=PassWord D=imooc,t=sbtest4\
      --charset=utf-8 --execute

      如何优化not in和<>查询
      用LEFT JOIN替换not in

      使用汇总表优化查询
      汇总表就是提前医药统计的数据进行汇总并记录到表中以备后续的查询使用