MYSQL查询优化

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条件查找同个列的多个不同数据时，该列没有建立索引时随着ｏｒ条件越多效率会明显降低，而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; #使用命令