上一篇 <<<MySQL性能优化之表设计优化
下一篇 >>>MySQL性能优化之索引调优实战
SQL性能优化的目标：至少要达到range级别，要求是ref级别，consts最高。【阿里巴巴JAVA开发手册】
说明：
1)、consts单表中最多只有一个匹配行（主键/唯一索引），在优化阶段即可读到数据。
2)、ref指的是使用普通的索引(normal index)。
3)、range 对索引进行范围检索
反例：explain表的结果，type=index，索引物理文件全扫描，速度非常慢，这个index级别比较range还低，与全表扫描是小巫见大巫。
最优到最差顺序：system > const > eq_ref > ref > range > index > ALL
语句优化
1.简单查询语句优化
1.1 select 字段部分
- a、返回具体的字段以代替*
1)、用不到的字段会消耗CPU和IO、消耗网络带宽
2)、若可以使用索引的情况下，使用*会导致回表查询，效率更低
3)、数据暴露更不安全。
- b、count(*)会统计null值的行，count(列名)不会统计null值的行
select count(user_id) from dw_user where `order` is null limit 10; 结果：5875
select count(0) from dw_user where `order` is null limit 10; 结果：5875
select count(`order`) from dw_user where `order` is null limit 10; 结果：0
5.6版本：默认会选择辅助索引技术，没有的话才会走主键聚集索引技术
1.select count(1) from employees; 使用辅助联合索引计数
2.select count(id) from employees; 使用辅助索引技术
3.select count(name) from employees; 使用辅助索引技术
4.select count() from employees; 使用辅助索引技术
5.select count(id) from employees force index(PRIMARY) 强制使用主键索引
从效率角度分析：count(1) ==count()>count(name)>count(id)
因为主键索引的id对应的叶子节点中存放data数据，每个节点只能放16k的页数据，所以扫描范围不是非常广，而且比较占内存的耗时。
count(name)统计的时候，叶子节点中存放主键id，每次扫描范围更广，效率更高。
推荐使用count(*)
在MySQL 5.7 COUNT(*)默认会选择聚集索引技术，进行一次内部handler函数调用，即可快速获得该表总数。
如果聚集索引较大（或者说表数据量较大），没有完全加载到buffer pool中的话，MySQL 5.7的查询方式有可能反而会更慢，还不如用原先的方式（MySQL 5.6）
聚集索引：就是主键id索引
非聚集索引：就是自定义的其他字段索引
- c、函数统计，尽量放到内存中执行
select sum(a)+sum(b) as c
- d、tinyint在使用时容易变为布尔型，代码中记得转换，用case时等于更优
case status when 0 then do1 when 1 do2 end
===》
case when status=0 then do1 when status=1 then do2 end
1.2 条件部分
- a、数据类型必须一致，否则索引失效，还会增加转换的开销
SELECT * FROM t WHERE id = '19';
----->
SELECT * FROM t WHERE id = 19;
- b、少用≥，直接使用＞，可提升查询效率
select * from dw_user where user_id>=101; ----—多一次等于的判断
select * from dw_user where user_id>100;
1.3 分组和排序
- 禁止使用order by rand()
SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;
---->
SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;
- 分组查询时，默认分组后，还会排序，可能会降低速度
在group by 后面增加 order by null 就可以防止排序.
explain select * from emp group by deptno order by null;
排序细节可参考order by中的单路和双路排序算法原理
1.4 索引不起效果的写法
- a、使用group by、not in、not like不使用索引
2.查询语句-临时表场景
2.1 必须创建临时表的情况
- 如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；
- 如果数据量不大，为了缓和系统表的资源，应先 create table，然后 insert。
2.2 临时表的显示删除
先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
3.插入语句
//批量插入使用/*+append*/
insert into p_nbsc_ho1 select * from p_nbsc_ho （消耗时间73分钟）
insert /*+append*/ into p_nbsc_ho2 select * from p_nbsc_ho （消耗时间7分钟）
4.更新语句
如果只更改 1、2 个字段，不要 Update 全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。
5.开发代码中少用truncate table
truncate table和delete功能相同，但速度更快，使用的系统和事务日志资源较少，它无事务且不触发trigger，容易造成事故。【阿里巴巴JAVA开发手册】
推荐阅读：
<<<MySQL执行计划示例解读
<<<MySQL性能优化之慢查询定位
<<<MySQL性能优化之表设计优化
<<<MySQL性能优化之索引调优实战
<<<MySQL性能优化之分页查询优化
<<<MySQL性能优化之关联查询优化
<<<MySQL性能优化之in、exists优化
<<<order by中的单路和双路排序算法原理
<<<MySQL如何性能优化面试题完美解答
