1. 慢查询日志
1.1 慢查询日志启动需要配置以下参数
slow_query_log #启动停止记录慢查日志 ON 开启 OFF关闭。
slow_query_log_file #指定慢查日志的存储路径及文件(默认和二进制日志保存在一个地方,最好分开)。
long_query_time #指定记录慢查日志SQL执行时间的伐值(默认时间为10秒,最好对这个值进行修改,单为秒级,通常设置为0.001秒也就是1毫秒)
log_queries_not_using_indexes #是否记录未使用索引的SQL
/*可以使用如下命令查看参数和设置参数的值*/
show variables like 'xxxx'; #查看参数
set global xxx = xxxx; #设置参数
1.2 慢查询日志记录的内容
(1)记录所有符合条件的sql;
(2)包括查询语句;
(3)数据修改语句;
(4)已经回滚的SQL。
1.3 常用的慢查询日志分析工具
(1)mysqldumpslow
mysqldumpslow -s r -t 10 slow-mysql.log # -s order(c,t,l,r,at,al,ar)
参数解释:c:总次数 t:总时间 l:锁的时间 r:总数据行 前面加a表示平均xx的次数 比如at=总时间/总次数。
(2)pt-query-digest (比较常用,建议使用这个工具来查询慢查询日志)
pt-query-digest --explain h=127.0.0.1,u=root,p=p@ssQ0rd show-mysql.log
2. 实时获取有性能的问题SQL
2.1 借助mysql的information_schema数据库下的processlist表
SELECT id,`USER`,`host`,DB,command,`time`,state,info
FROM information_schema.PROCESSLIST
WHERE TIME>=60; # 查询sql查询时间大于60秒的sql语句,其中time设置时间根据实际情况设置,单位为秒。
3. SQL的解析预处理及生成执行计划
原理:(1)客户端发送SQL请求给服务器(几乎不影响效率);
(2)服务器检查是否可以在查询缓存中命中该SQL;
(3)服务器端进行SQL解析,预处理,再由优化器生成对应的执行计划;
(4)根据执行计划,调用存储引擎API来查询数据;
(5)将结果返回给客户端。
其中(2)~(5)会对查询造成影响。
3.1 查询缓存对SQL性能的影响
query_cache_type #设置查询缓存是否可用
query_cache_size #设置查询缓存的内存大小
query_cache_limit #设置查询缓存可用存储的最大值
query_cache_wlock_invalidate #设置数据表被锁后是否返回缓存中的数据
query_cache_min_res_unit #设置查询荤菜分配的内存块最小单位
建议:对于读写缓存比较频繁的系统不建议开启查询缓存,应该把query_cache_type设置为off,query_cache_size设置为0
3.2 MYSQL优化器可优化的SQL类型
(1)优化count(),min()和max(); # 比如可以使用统计表,建立索引等。
(2)将一个表达式转化为常数表达式;
(3)使用等价变换规则;
(4)子查询优化转换为关联查询;
(5)提前终止查询;
(6)对in()条件进行优化;#比如使用or条件查找同个列的多个不同数据时,该列没有建立索引时随着or条件越多效率会明显降低,而in则几乎不影响,因为mysql的in查询,mysql优化器会把数据先进行排序再进行二分查找法。
(7)重新定义表的关联顺序;
(8)将外连接转化为内连接。
3.3 确定查询处理各个阶段所消耗的时间
可以使用performance_schema (mysql5.6以后的版本对性能开销很小,建议在5.6以后的版本使用)
3.4 特定SQL的查询优化
(1)大表的数据修改最好要分批处理
比如:1000万行记录的表中 删除/更新100万行记录 一次只删除/更新5000行记录然后暂停几秒(可以给从服务器的同步时间)
(2)如果修改大表的表结构可以使用工具 pt-online-schema-change
原理:建立新表和老表结构一样,将老表的数据导入新表,建立触发器同步数据到新表,然后在老表建立一个排它锁,重新新表的名字为老表的名字,再删除老表。
pt-online-schema-change --alter="MODIEY c VACHAR(150) NOT NULL DEFAULT '' " --user=root --password=root D=数据库,t=数据表 --charset=utf --execute; #使用命令