SQL查询优化

如何获取有性能问题的SQL

1. 通过用户反馈获取存在性能问题的SQL
2. 通过慢查日志获取存在性能问题的SQL
3. 实时获取存在性能问题的SQL

慢查询日志

mysqldumpslow 慢查询日志分析

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

• -s order(c t l r at al ar)
  c 总次数 t总时间 l锁时间 r总数据行 带a表示平均数
• -t 指定前几条作为结束输出

pt-query-digest 慢查询日志分析

实时获取性能问题的SQL

infomation_scheme -> processlist

了解MySql查询过程

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

读写频繁的SQL 不使用查询缓存
query_cache_type 设置查询缓存是否可用
query_cache_size 设置查询缓存内存大小

query_cache_limit 设置缓存可存储的最大值

不使用查询缓存 查询语句加上 SQL_NO_CACHE

大表的数据修改最好要分批处理

大表结构修改

对表中列的字段类型或者长度进行修改

1. 主从服务器 现在从服务器更改再同步到主服务器

2. 在主服务器新建一个表
   老表加上触发器 最后再加个排他锁，最后删除老表

3. 使用pt-online-schema-change修改大表的表结构

pt-online-schema-change \
--alter="MODIFY c VARCHAR(150) NOT NULL DEFAULT ''"\
--USER=ROOT --password=PassWord D=database, t=sbtest4\
--charset=utf8 --execute