3.1 查询优化
clip_image155.jpg
3.1.1 永远小表驱动大表 类似嵌套循环Nested Loop
Case
clip_image157.jpg
clip_image159.jpg
clip_image161.jpg
3.1.2 order by关键字优化
ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序
建表SQL
Case
1
clip_image163.jpg
2
clip_image165.jpg
MySQL支持二种方式的排序，FileSort和Index,Index效率高。 它指MySQL扫描索引本身完成排序。FileSort方式效率较低。
ORDER BY满足两情况，会使用Index方式排序
ORDER BY语句使用索引最左前列
使用where子句与OrderBy子句条件列组合满足索引最左前列
尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
如果不在索引列上，filesort有两种算法： mysql就要启动双路排序和单路排序
双路排序
MySQL4.1之前是使用双路排序，字面意思是两次扫描磁盘，最终得到数据。 读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据传输
从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。
取一批数据，要对磁盘进行两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二张改进的算法，就是单路排序。
单路排序
从磁盘读取查询需要的所有列，按照orderby列在buffer对它们进行排序，然后扫描排序后的列表进行输出， 它的效率更快一些，避免了第二次读取数据，并且把随机IO变成顺序IO，但是它会使用更多的空间， 因为它把每一行都保存在内存中了。
结论及引申出的问题
参阅: 优化策略
由于单路是后出来的，总体而言好过双路
但是用单路有问题
clip_image167.jpg
优化策略
增大sort_buffer_size参数的设置
增大max_length_for_sort_data参数的设置
why
clip_image169.jpg
小总结
clip_image171.jpg
3.1.3 GROUP BY关键字优化
groupby实质是先排序后进行分组，遵照索引建的最佳左前缀
当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
where高于having,能写在where限定的条件就不要去having限定了。
3.2 慢查询日志
3.2.1 是什么
clip_image173.jpg
3.2.2 怎么玩
说明
clip_image175.jpg
查看是否开启及如何开启
默认
SHOW VARIABLES LIKE '%slow_query_log%'
clip_image177.jpg
开始
set global slow_query_log = 1
clip_image179.jpg
clip_image181.jpg
那么开启慢查询日志后，什么样的SQL参会记录到慢查询里面？
clip_image183.jpg
Case
查看当前多少秒算慢
SHOW VARIABLES LIKE 'long_query_time%';
设置慢的阙值时间
clip_image185.jpg
set global long_query_time=3;
为什么设置后看不出变化？
需要重新连接或者新开一个回话才能看到修改值。 SHOW VARIABLES LIKE 'long_query_time%';
show global variables like 'long_query_time';
记录慢SQL并后续分析
clip_image187.jpg
查询当前系统中有多少条慢查询记录
clip_image189.jpg
配置版
clip_image191.jpg
3.2.3 日志分析工具mysqldumpshow
clip_image193.jpg
查看mysqldumpshow的帮助信息
clip_image195.jpg
s:是表示按何种方式排序
c:访问次数
l:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
t:即为返回前面多少条的数据
g:后边搭配一个正则匹配模式，大小写不敏感的
工作常用参考
clip_image197.jpg
3.3 批量数据脚本
3.3.1 往表里插入1000W数据
1.建表
2.设置参数log_trust_function_createors
clip_image199.jpg
3创建函数保证每条数据都不同
clip_image201.jpg
随机产生字符串
随机产生部门编号
4.创建存储过程
clip_image203.jpg
创建往emp表中插入数据的存储过程
创建往dept表中插入数据的存储过程
5.调用存储过程
dept
emp
3.4 Show profiles
3.4.1 是什么：是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优测量
3.4.2 官网：http://dev.mysql.com/doc/refman/5.5/en/show-profile.html
3.4.3 默认情况下，参数处于关闭状态，并保存最近15次的运行结果
3.4.4 分析步骤
1.是否支持，看看当前的SQL版本是否支持
clip_image205.jpg
2.开启功能，默认是关闭，使用前需要开启
3.运行SQL
select * from emp group by id%10 limit 150000
select * from emp group by id%20 order by 5
4.查看结果，show profiles;
5.诊断SQL，show profile cpu,block io for query 上一步前面的问题SQL 数字号码；
clip_image207.jpg
参数备注
clip_image209.jpg
6.日常开发需要注意的结论
converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。
Creating tmp table 创建临时表
clip_image211.jpg
拷贝数据到临时表
用完再删除
Copying to tmp table on disk 把内存中临时表复制到磁盘，危险！！！
locked
3.5 全局查询日志
3.5.1 配置启用
clip_image213.jpg
3.5.2 编码启用
clip_image215.jpg
网友评论