Java笔记4.5--数据库调优

三范式：字段间不存在传递关系。就是说每个字段都跟主键有直接关系，而不能有间接关系。

比如有张订单流水表，（订单编号，总价，商品编号，商品名）

通过商品编号能再次传递找到商品名，所以不符三范式

需要拆分成两个表，订单流水表，（订单编号，总价，商品编号）。商品表，（商品编号，商品名）

能避免数据冗余和更新异常问题。

案例：

在过去一个月里，找出过去一个月的所有买过Java书籍的会员的邮箱，以便于发广告。

可以从表中发现，三张表关联查询涉及到的数量太大了

运行时间长的SQL会导致有更新操作的SQL阻塞，从而连接数打满。

建表权衡因素：在“三范式”和“连接代价”间权衡。

数据量较小，可用三范式。

数据量大，不能用，在单表放入足量字段，宁可数据冗余，宁可多次更新。

总结：

项目工作：数据库设计（参与了建表）

数据库调优：（数据量大建表不用三范式）

范式等概念。

调优方式：索引，建表。

深入调优方法：

项目里是否用到存储过程？可以回答不用。

在同一存储过程中，会组合多个SQL语句，会有入参和返回值。

会预编译SQL，但是提升性能方面帮助有限。

移植性不好，比如SQL Server移植到Oracle就很困难。

排查问题只能定位到是哪个存储过程，没法定位到具体的SQL语句。

不利于SQL性能调优。

具体措施：

1.建表，索引，预处理，批处理，不写select *

2.delete from table where id in (1,2,3,4)，括号里别太长（最多一两百，一个语句批量删除）

3.insert into tables (字段列表) values (1,2),(3,4)。（多个一起插入）oracle不支持，mysql 和sql server支持。

4.select语句尽可能减少大表关联（会产生长sql），万不得已合并表或者合理索引

5.合理用oracle力的exist和in，并非在任何情况下都用一个。

6.合理选用Mysql的引擎。

7对于慢SQL,用执行计划分析原因，解决问题。

5.exists和in

select * from t1 where id in (select id from t2)

select * from t1 where exists (select id from t2 where t1.id = t2.id)

in中子查询执行一次，查询出id并缓存与ti做笛卡尔积，随后一次检查t1的id是否与缓存中的一致（用到t1的索尼因），若相等就记录下来。

假设t1有1000条，exists会执行1000次去和子查询中的t2表匹配（用到t2的索引），并不会有缓存结果集。

结论：若t2 规模小于t1,可以用in， 反之t1规模小于t2,用exists，如果差不多，则随意。

not in 和not exists：如果使用not in，那么

6.Mysql引擎：

Mysql5.5开始默认使用InnoDB，之前是MyISAM。

InnoDB支持事务，支持外键，用到行级锁。

MyISAM不支持事务，不支持外键，但是访问速度快，以select,insert为主的应用可用。

一般的项目，可以用Mysql读写分析外带热备冗余。

MariaDB是Mysql的分支，不是引擎，性能优于Mysql。

MariaDB可构建数据库集群，支持多主复制。

7.监控慢SQL：

项目中可以通过CAT监控慢SQL。

linux下把slow_query_log设置成on，设置long_query_time为10，就时输出运行时间超出10s的SQL，甚至可以发邮件通知。

或者，运维人员定期输出运行时间top 10的Sql语句。

项目中一般数据库服务器不是本地，可以通过navicat连接mysql，oracle和sql server。在navicat中通过explain查看计划。看到计划后，需要关注sql语句里各部分动作的cost和time。

比如列出了sql里全表扫描/索引扫描，order by 和 group by 等的代价。

说辞：在xx项目的查询语句里发现一个长sql，经过分析执行计划，发现虽然用到了xx索引，但是需要回表找其他信息，而且该sql运行频繁，综合考虑，建立复合索引。

执行计划的调优：

oracle三种连接方式以及驱动表：

Hash join， Sort Merge Join和NESTED LOOP是oracle里关于连接的内部实现方式，oracle引擎会根据算法选取最适当的链接，但是不一定是最好的。

select * from t1, t2 where t1.id = t2.id and t1.name = 'tom'  由于能用t1.name过滤掉更多数据，所以从ti表开始找，找到后再匹配t2表，因此t1是驱动表，t2是被驱动表。

nestloop: 驱动表返回的每一行都到被驱动表中检索匹配行，适用于被驱动表比较小，且关联字段有索引，(  USE_NL(t1 t2) 可以强制使用该链接方式  )

hash join:两个表都很大，或者一个大表和一个小表关联，用 USE_HASH(t1 t2) 强制使用该方式

sort merge join： 会优先排序再匹配，如果驱动表已经排过，可以用此方式，一般场景不多。

优化说辞：项目中发现一个长sql，通过执行计划发现其中关联用到了nest loop方式，但是执行很长，再分析，驱动表和被驱动表很大，所以用USE_HASH(t1 t1)方法强制使用hash join运行时间就变短了。

  总结：

1.先讲sql层面，尽量优化，比如建表方式和不用select *

2.如何确认SQL要优化，CAT或者mysql参数。

3.通过可执行计划可以看长SQL的时间耗费点。

4.根据执行计划的分析结果，举例说明调优步骤。比如建复合索引，修改连接方式。

5.仅仅单机版不够，引入分布式组件层面的redis缓存和mycat分库分表。