分析总结
慢查询开启并捕获
explan+慢sql分析
show profile查询SQL在mysql服务器里面的执行细节和生命周期情况
sql数据库服务器的参数调优
1.查询优化
优化原则：小表驱动大表，即小的数据集驱动大的数据集
EXISTS
SELECT * FROM table WHERE EXISTS (subquery)
该语法理解为：将主查询的数据，放在子查询中做条件验证，根据验证结果(true或false)来决定主查询的数据结果是否得以保留
提示：
1.EXISTS(subquery)只返回true或false，因此子查询中的select * 也可以写成select 1 或select ‘X’ 等，官方说法是实际执行时会忽略select清单，因此没有区别
2.EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题
3.EXISTS子查询往往也可以用条件表达式，其他子查询或者JOIN来替代，何种最优需要具体问题具体分析
#当B表的数据集必须小于A表的数据集时，用in优于exists.
select * from A where id in (select id fromB)
等价于for select id fromBfor select * from A where A.id = B.id
#当A表的数据集小于B表的数据集时，用exists优于in
select * from A where exists (select 1 from B where B.id =A.id)
等价于for select * fromAfor select * from B where B.id = A.id
ORDER BY关键字优化
尽量使用index方式来排序，避免使用filesort方式排序
mysql支持二种方式排序，filesort和index，index效率高，它指mysql扫描索引本身完成排序.filesort方式效率较低
order by 满足两种情况会用index方式排序：
order by语句使用索引最左前缀
使用where子句与order by 子句条件列组合满足索引最左前缀
尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
如果不在索引列上，filesort有两种算法
双路排序
mysql4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段
单路排序
从磁盘读取查询需要的所有列，按照order by 列在 buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但它会使用更多的空间，
因为它把每一行都保存在内存中
优化策略
增大sort_buffer_size参数的设置
增大max_length_for_sort_data参数的设置
提高order by 的速度
1.order by 时select * 是一个大忌，只查询需要的字段，这点很重要
1.1当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会用改进后的算法–单路排序，否则用老算法–多路排序
1.2两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O,但用单排序算法 的风险会更大一些，所以要提交sort_buffer_size
2.尝试提高sort_buffer_size
不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高 ，因为这个参数是针对每个进程的
3.尝试提高max_length_for_sort_data
提高这个参数，会增加用改进算法的效率，但是如果设的太高，数据总容量超出sort_buffer_size的概率就会增大，明显症状是高的磁盘I/O活动和低的处理器使用率
GROUP BY关键字优化
group by 实质是先排序后进行分组，遵照索引建的最佳左前缀
当无法使用索引列，增大sort_buffer_size和max_length_for_sort_data的参数设置
where高于having，能写在where限定条件就不要去having限定了
2.慢查询日志
mysql的慢查询日志是mysql提供的一种日志记录，它用来记录在mysql中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的sql，则会被记录到查询日志中
具体指运行时间超过long_query_time值的sql，则会被记录到慢查询日志中，long_query_time的默认值为10，意思是运行10秒以上的语句
由他来查看哪些SQL超出了我们的最大忍耐时间值，就记录下SQL，结合之前explain进行全面分析
默认情况下，MySQL数据库没有开启慢查询日志，需要手动来设置这个参数
如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件
命令：查看 SHOW VARIABLES LIKE ‘%slow_query_log%’
启动 set global slow_query_log = 1
查看慢查询long_query_time值 SHOW VARIABLES LIKE ‘%long_query_time%’
设置时间 set global long_query_time=3(秒)
得到返回记录集最多的10个sql
mysqldumpslow -s r -t 10 慢sql文件
得到访问次数最多的10个sql
mysqldumpslow -s c -t 10 慢sql文件
得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g “left join” 慢sql文件
建议在使用这些命令时结合|和more使用
mysqldumpslow -s r -t 10 慢sql文件 | more
3.批量数据脚本
设置参数log_bin_trust_function_creators
创建函数，如果报错This function has none of DETERMINISTIC……
由于开启过慢查询日志，因为开启了bin-log，就必须为function指定一个参数
show variables like ‘log_bin_trust_function_creators’
set global log_bin_trust_function_creators=1
这样添后，重启mysql，就失效了，永久设置
windows下my.ini [mysqld]加上log_bin_trust_function_creators=1
linux下/etc/my.cnf [mysqld]加上log_bin_trust_function_creators=1
4.Show Profile
是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于sql调优的测量
分析步骤
1)是否支持，看当前mysql版本是否支持(默认关闭)
show variables like ‘profiling’
2)开启
set profiling=on
3)运行相关sql
4)查看结果
show profiles
5)诊断sql
类型
ALL：显示所有的开销信息
BLOCK IO：显示块IO相关开销
CONTEXT SWITCHES：上下文切换相关开销
CPU：显示CPU相关开销信息
IPC：显示发送和接收相关开销信息
MEMORY：显示内存相关开销信息
PAGE FAULTS：显示页面错误相关开销信息
SOURCE：显示和soure_function,source_file,source_line相关的开销信息
SWAPS：显示交换次数相关开销信息
常用的查询分析：show profile cpu,block io for query Query_ID
6)日常开发需要注意的结论
converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬
Creating tmp table 创建临时表
拷贝数据到临时表
用完再删除
Copying to tmp table on disk 把内存中临时表复制到磁盘，危险
locked
5.全局查询日志(不要在生产环境中开启这个功能)
配置启用
在配置文件下my.cnf
#开启
general_log=1
#记录日志文件的路径
general_log_file=/path/logfile
#输出格式
log_output=FILE
编码启用
set global general_log=1;
set global log_output=’TABLE’;
此后，你所编写的sql语句，都将记录到mysql库里的general_log表里
select * from mysql.general_log;