本来应该先发这篇的，现在才发现漏掉了
项目中SQL优化流程
1.开发人员具备一定的SQL优化基本功
2.在开发阶段，每条写的SQL在测试环境看看他的执行计划
3.上线后让DBA收集查询比较慢的SQL
4.通过explain工具和show profile 分析慢SQL，修改代码，重新上线，重新收集。如果贵公司的DBA关系和你很好，在优化的时候可以拉他一起，多学点理论和经验
5.数据库参数调优
6.操作系统调优
7.更换硬件设备
查询优化器
MySQL有专门负责SELECT的优化器模块，根据先前收集到的统计信息，为SQL生成一条它认为最优的执行计划，但该计划不一定是DBA认为最优的。也就是说它有可能根据错误的统计信息生成了自认为合理的执行计划。这种时候需要DBA介入，重建索引——》重新收集统计信息，这样MySQL才可能按照真正最优的方式运行，如果MySQL还是不能生成想要的执行计划，DBA还可以固化执行计划
查看机器瓶颈
使用top、free、iostat、vmstat命令查看机器的硬件资源负载情况
执行计划
执行计划是MySQL对SQL的执行生成的一套优化策略，以效率为首要目的，提升SQL执行的速度
可以在MySQL中使用explain SQL或者desc SQL来生成执行计划，如：
有了执行计划能干嘛？
1.查看表的读取顺序
2.数据读取操作的操作类型
3.那些索引可以被使用
4.实际使用了那些索引
5.表之间的引用
6.每张表有多少行被优化器查询
执行计划结果集分析
id分析-表的读取顺序
如果ID相同，执行顺序由上而下；
如果ID不同，执行顺序是ID越大越先执行，为什么喃，越早缩小结果集对整个执行越有效，因为过滤的数据量更少，IO次数越少，CPU消耗越低
如果ID相同与不同同时存在，则先执行ID大的，ID相同的则按照顺序执行
其中的derived2中的2就是id=2
select_type
取值范围
| ID | select_type | |
|---|---|---|
| 1 | simple | |
| 2 | primary | |
| 3 | subquery | |
| 4 | derived | |
| 5 | union | |
| 6 | union result |
用于指明这个SQL是一个什么类型的查询语句，简单查询、复合查询、嵌套查询
simple:
sql中不包含子查询或union
primary：
查询中若包含任何复杂的子查询，则最外层就被标记为primary
subquery：
在select或者where部分中包含的查询就被称为子查询
dependent subquery：
相关子查询，MySQL遇到这种情况，不会先将子查询的结果集找出来再匹配，而是从外部表拿到每一个关联的值到子查询表去找捞数据，很多同学经常使用的in，不管是在oracle还是mysql都不推荐使用in(subquery)，可以使用join来代替
derived：
在from部分包含的第一个完整查询，其结果为被放在临时表，在执行计划中被标记为derived(衍生)
union：
在from部分union关键字后面的查询都会被标记为union
depentent union：
相关子查询合并的结果集
union result：
在from部分所有查询union的结果被标记为union result
通过下图可以看到一个比较全的执行计划
SQL：该SQL只是为了演示执行计划，不要去扣里面的写法是否合理
explain select * from (select * from tb_item a
union
select * from tb_item b
union
select * from tb_item c) d
join tb_order_item b
on d.id = b.item_id
where d.id in(
select id from tb_item a1
union
select id from tb_item b1
union
select id from tb_item c1
);
table
就是这一步使用到的表名字，可能是实际的表名，也可能是MySQL处理过程中的中间表别名，如derivedxxx或者unionxxx
type
显示查询使用了什么类型，最好的结果顺序是system>>const>>eq_ref>>ref>>range>>index>>all，在实际的开发和生产维护中，能够做到eq_ref是最好，达到ref级别完全OK，尽量减少index和all两种情况出现。如果出现自己解决不了，找DBA或者你的项目经理帮忙
1.system
一张表只有一行记录，实际系统中应该很少出现这种情况，如果有我估计也是什么配置，但是这种情况完全可以写到配置文件，或者ZK、REDIS中
2.const
表示通过索引一次就找到，比如
explain select * from tb_item_cat where id = 1;
id是表tb_item_cat的主键，所以id=1一次查询只能找到一行记录。无论条件如何，必须要返回一行，多行就不会是const类型
3.eq_ref
唯一索引扫描，对于每个索引建，表中只有一条记录与之匹配。常见于主键和UNIQUE索引，比如
explain select * from tb_item_cat a join tb_order_item b on a.id=b.item_id;
MySQL会先处理b表中的数据，然后b.item_id在a表中根据索引只能找到一行记录，因此符合要求，是eq_ref类型
4.ref
依然走索引扫描，但是一个索引键会对应表中多行数据
explain select * from tb_item_cat a where a.parent_id=2;
因为parent_id和item_id是多对一的关系，因此查询条件为parent_id=2必然会返回多条记录，符合上面的条件，是ref类型
5.range
走索引扫描，但是扫描的是索引的一段范围，比如使用between, < , > ,in等关系运算符
explain select * from tb_item_cat a where a.parent_id in(2,3);
6.index
走索引扫描，但是扫描的整个索引，这种情况比全表扫描会好一点，因为单个索引在磁盘所占空间一段是比整个表要少很多
explain select parent_id from tb_item_cat
因为我想要查询的返回结果集只有parent_id字段，而该字段全部在索引中就能找到，所以走索引全扫描完全OK，不需要再去扫描表
7.all
最惨的扫描方式，速度最慢，如果你的执行计划打出来有它出现一定要注意
explain select * from tb_item_cat
possible key
理论上可能用到的索引，比如一个列上有2个索引，主键和一个复合索引（包含主键列），那么在这里显示的就会有这啷个索引名
key
执行时实际会用的索引
key len
查询条件长度，在不损失查询精度的情况下，可以尽量减少查询条件长度
ref
显示被用于索引扫描的列或常量值
explain select * from tb_item_cat where id=1;
此时ref就是const
explain select * from tb_item_cat a join tb_order_item b on a.id = b.item_id
可以看到使用数据库mydb的表b的item_id列被用于索引的扫描
rows
估算出来的可能被扫描出来的行
extra
三大重要的额外信息，会在这里显示
-
using filesort
索引本身的排序没有被使用到，MySQL会将数据做另外的排序，这样将会很耗费时间。在实际生产中，如果真的出现这种情况，而索引又没有必要创建的话，你可以将数据读出来之后自己在代码中进行排序
-
using temporary
使用临时表来保存中间结果，如果经常出现这种情况，计算会不断的申请内存，耗费内存和CPU，造成数据库吞吐量严重下降，应用无法响应开始排队，最终应用无法响应客户端请求
-
using index
它是一个很好的提示，说明你的SQL写得很不错，将来的执行效果会很好
183
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
