SQL优化

传送门

SpringMVC的源码解析（精品）
Spring6的源码解析（精品）
SpringBoot3框架（精品）
MyBatis框架（精品）
MyBatis-Plus
SpringDataJPA
SpringCloudNetflix
SpringCloudAlibaba（精品）
Shiro
SpringSecurity
java的LOG日志框架
Activiti（敬请期待）
JDK8新特性
JDK9新特性
JDK10新特性
JDK11新特性
JDK12新特性
JDK13新特性
JDK14新特性
JDK15新特性
JDK16新特性
JDK17新特性
JDK18新特性
JDK19新特性
JDK20新特性
JDK21新特性
其他技术文章传送门入口

MySql调优

1、简单总结

1.上面部分索引命中的都是调优的
2.连续的能用between不用in
3.update能更新部分的不要更新全部
4.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。 不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。
5.简化SQL语句的重要方法就是采用临时表暂存中间结果，但是，临时表的好处远远不止这些，将临时结果暂存在临时表，后面的查询就在tempdb中了，这可以避免程序中多次扫描主表，也大大减少了程序执行中“共享锁”阻塞“更新锁”，减少了阻塞，提高了并发性能。
但是也得避免频繁创建和删除临时表，以减少系统表资源的消耗。
6.当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个列名上。这样就可以减少解析的时间并减
少哪些友列名歧义引起的语法错误。
7.用where字句替换HAVING字句
避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前
刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数
的过滤，除此之外，应该将条件写在where字句中
8.尽量将多条SQL语句压缩到一句SQL中
每次执行SQL的时候都要建立网络连接、进行权限校验、进行SQL语句的查询优化、发送执行结果，这个过程
是非常耗时的，因此应该尽量避免过多的执行SQL语句，能够压缩到一句SQL执行的语句就不要用多条来执行。
9.复合索引尽量使用全索引查询，比如a b c三个索引，全部匹配上更快，就像章节目录一样，a大章，b第一节，c第1小节，全部命中定位更快范围更小。

2、explain

explain说明
id：
编号，编号相同从上往下顺序执行；比如select * form a,b,c 会先执行表数据少的，比如a表2条数据，b表3条，c表6条，最后笛卡尔积就是236=66=36 如果交换执行顺序 263=123=36 虽然最后结果都是36但是中间结果6小于12，所以SQL优化器优化后就是按照第一种乘法来计算笛卡尔积，这就是小表驱动大表的由来，数据小的表先执行，数据大的最后执行。
编号不同，id越大越优先执行，这种一般在子查询中会出现；比如select * from a where a.xx in (select xx from b where b.xx in (select xx form c) ), 要查询出a必须先靠子查询b，要查询出b必须先靠子查询c，所以一定是c先执行，然后是b，然后是a，这个和数据量大小就没有关系了，和依赖有关系，先查询最内层。
编号一部分相同一部分不同，先执行越大的，然后相同的从上往下执行（explain的视图看起来不舒服，必须记住这个规则），比如select * from a,b where a.xx=b.xx and b.xx =(select xx from c)看测试结果，子查询的优先级高于联表查询。

select_type查询类型:
primary ，是主查询，是包含子查询SQL中的主查询，一般是最外层，比如这句 select * from a where a.xx in (select xx from b where b.xx in (select xx form c) ), 就是指 select * from a 这部分；
subquery，就是子查询，刚刚例子后面部分全部都是子查询；
simple，简单查询，不包含子查询，不包含union查询；
derived，衍生查询，使用到了临时表；例如 select xx from (select xx form b where xx =xx ) a ； explain后两行看截图，主查询的select_type=primary,table=derived2;临时表 (select xx form b where xx =xx )这种select_type=derived,table=b，例如 select xx from (select xx form b where xx =xx union select xx form c where xx =xx) a ； 主查询同上，临时表是多个，table=b的select_type=derived, table=c的select_type=union;
union,同上例
union result，同上例，告知开发哪些表存在union查询；

table：表名字

type：索引类型（前提必须是有索引）
前提必须是有索引 system>const>eq_ref>ref>range>index>all 调优一般就是调整到ref和range范围
system,就一条数据的系统表；或者衍生表只有一条数据的查询，比如 select xx from (select xx form b where xx =xx ) a ；b表就一条数据
const，只能查出一条数据的SQL，用于primary和union索引有效,比如 select xx form a where xx =5 ;xx是primary和union才有效
eq_ref，唯一索引，联表查询的时候唯一索引所在的这个列都不能有重复，条件非常苛刻，当然单表查询这个列肯定是没有重复的；
ref，非唯一索引
range，检索指定范围的行 between > >= in 这类的，in有时候会失效，in和数据量有关，in大于数据量一半时就会全表扫描转为all
index，查询全部索引数据，命中索引了 例如 select id from a 因为id是主键，查询了全部索引数据
all,没有命中索引，全表扫描

possible_keys：预测用到的索引，不准，比如3个索引用到了1个

key:实际使用的索引

key_len:实际使用索引的长度，用于判断复合索引是否被完全使用了；在MySQL中utf8一个字符占用3个字节，比如xx char(20)，就是key_len=60,这种是xx字段没有null的情况，如果有null，就是key_len=61，会用1个字节来标记null；如果是xx varchar（20），就是key_len=63,会用1个字节来标记null，两个字节标记可变长度

ref:表之间的引用，表明当前表所参照的字段，比如 select a.* from a ,b where a.xx =b.xx2 主要查询a的话，b.xx2就是引用，就是所参照的字段，如果b.xx2是个常量，那么ref=const

rows:通过索引查询到的数据行数

extra：额外的信息

useing filesort ：性能消耗大（常见于order by），需要额外的一次排序（查询），比如select * form a where xx1=xx order by xx1 ,这种xx1索引命中后，查的时候有xx1数据了，order by的时候不用再查询，直接可以用，比如select * form a where xx1=xx order by xx2 ，这种order by xx2的时候，没有查xx2数据，需要重新再查一次xx2数据才能排序。如果是复合索引，符合最左前缀，比如select * from a where xx1=xx order by xx2.这个时候的符合索引是xx1-xx2-xx3，那么这句就不会出现filesort，如果where xx1=xx order by xx3；这个时候不符合最左前缀，就会出现filesort；

useing temporary:性能消耗大（常见于group by），根据xx1查却不根据xx2分组，就会出现

useing index：性能提升了，索引覆盖，不需要回表查询

useing where：需要回表查询的

impossible where：where字句永远为false，比如select * form a where xx1=1 and xx1=2 ,这种xx1又是1又是2是永远不可能的

useing join buffer:连接缓存，优化器觉得你写的联表查询太差了，写了一个缓存，如果你优化了on索引或者where索引，它认为你写好了这个缓存就不见了

SqlServer调优

1、隐式转化

2、扩展CDC

这个是SqlServer的一种功能。update、delete、insert这种mdl操作，能够被监控，记录日志，它的主要作用是捕获数据库中发生的变化，并将这些变化记录下来，从而实现数据同步和数据追踪等功能。记得有几个存储过程来监控这个。通过记录的日志来和具体业务做关联，比如好房通LINK和好房A+为不同的数据库，楼盘字典模块两边的代码统一调用LINK的接口，并通过数据库CDC实时同步数据。这个和水利部的职称证书打印功能类似，水利部内外网也是不同数据库不同环境，通过数据同步功能同步数据库（xml传输处理的数据，提供了api，用的json处理返回结果，可惜的是数据交换核心api在第三方那边，看不到）。MySQL也有这个CDC功能，一样的也是用于数据同步和数据追踪等

Oracle调优

Oracle性能优化之WHERE子句中的连接顺序（下面那个对，偏向下面的，没有影响，连MySQL有索引的情况下都会调整）
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.
实际上，从这条SQL语句的执行计划来分析，Oracle首先会找出条件中使用索引或表间连接的条件，以此来过滤数据集，然后对这些结果数据块所涉及的记录逐一检查是否符合所有条件，所以条件顺序对性能几乎没有影响。

小总结

SQL优化是一种概率优化，不是100%能按照理论优化成功的，原因就是SQL优化器
多表连接：
1.小表驱动大表，on a.xx 10= b.xx300 将小表a放左边 ，由于小表a去笛卡尔积的时候一条要执行300次，所以在a.xx建立索引，左外连接给左表加索引，右外连接给右表加索引。
2.复合索引尽量使用全索引查询，比如a b c三个索引，全部匹配上更快，就像章节目录一样，a大章，b第一节，c第1小节，全部命中定位更快范围更小。
3.不要在索引上计算，或者不要表达式写法
4.复合索引有>这种范围查询,则后面索引全部失效（一般而言，不一定都是这样）
5.%xx%这种一般不会命中索引，但是如果是索引覆盖是可以命中的，挽救一部分，比如 select xx from a where xx like %xx% 这种只查xx就是索引覆盖，就会命中的。
6.隐式转化问题，where xx=123 中xx是个varchar，去匹配一个int，这种就是隐式转化，虽然123会被转化为“123” 但是索引失效了，所以不要使用隐式转化 （在mysql5.7和mysql8都不会隐式转化，都能出发索引效果。可能老版本存在吧。本质就是varchar脱去引号要用到函数，函数就会让索引失效。反过来，int等变成varchar不需要用到函数。）
7.or可能把左右两边的索引都干掉
8.exists和in
如果主查询的数据集大，就用in，如果子查询的数据集大，就用exist
exists语法：将主查询的结果放到子查询中进行校验，如果符合校验（就是有数据），则保留数据，否则不保留；比如 select xx from a where exists（select * from a） ；这种情况子查询范围是满的，肯定符合校验，是可以查出来的。select xx from a where exists（select * from a where xx=9999）；这种情况，假如9999不存在，那么子查询都是不存在的，不符合校验，就查不出来，如果一部分能校验，一部分不能校验，那么主查询为主都能查出来的，由此可见，exists和in不是等价转化的。
order by的单路查询（一次硬盘，现在用的）和双路查询（扫描两次硬盘，拿数据再排序）见颜老师视频p17