SQL优化

1.整体

客户端，Web端，数据库缓存，连接池，MYSQL数据库

2.层次

1.上层异常：数据库上层缓存Redis失效，最佳优化就是先恢复上层缓存。

2.减少查询：思考是否不必要的查询落到了数据库上，尽量减少。

3.查询路径优化：索引是一种数据结构，为查询优化器提供了可供选择的查询路径。

4.锁和缓存优化：避免大事物，长事物，减少锁，增大存储引擎层缓存。

5.硬件优化：优化硬件本身，扩容资源CPU，内存。

优化整体思路：数据库响应慢，负载突增怎么解决。

分析：并不是一上来就考虑资源不足，考虑扩容。也不仅仅考虑SQL和索引优化，首先从整体分析，整个链路其他环节是否有异常，从用户发起请求到收到返回结果，整体耗时多少，在那些地方慢，优先解决上层问题，比如上层缓存失效，根本的解决策略就是恢复失效的缓存。确定其他没有问题之后，再回到数据库层，优化策略是先考虑减少查询，再考虑SQL和索引优化，在考虑增加存储引擎层缓存，减少锁和大事物，最后考虑硬件优化或扩容，增加资源投入。

一.MySQL 优化方法论

减少查询-案例一：对于单条性能很好的SQL，并发高的优化。

1.自动审核业务主库负载不定时偏高，达到正常值10倍，慢查询阀值设置为0.2秒，但没有记录

分析：负载高的查询都是select 没有insert，update，delete 所以把select查询迁移到2台从库，发现查询会引起从库负载增加，问题确认是select。单条sql执行时间为0.06秒，无法进入0.2秒的慢日志，对负载的影响主要是执行的数量多并发高引起的，数据库峰值QPS=1000+

优化：开发根据业务降低库里负载高SQL执行频率，由原来100个线程每2秒执行一次现在改为：

1如果取到小于5条数据，执行完之后线程sleep6秒

2如果没有取到数据，执行完之后线程sleep12秒

结果：优化之后95%的线程执行完之后都进入了6-12秒的sleep，整体执行频率大幅度降低。

减少查询-案例二：对于数据处理前不做判断导致SQL跑大批无用数据。

2.客户调用API修改价格字段频度增加，数据库峰值QPS=15000+

分析：客户操作中，20%的价格修改前后并无变化，只是会触发表中操作时间的修改。

优化：在此类SQL执行之前判断出前后差价，如果相等，不执行SQL。

结果：上线后数据库QPS和负载降低了20%。

二.MySQL架构：查询优化器和InnoDB引擎

引用层->MySQL层->InnoDB存储引擎层

查询优化器-案例一：主从库不同时间执行性能差10倍，未人工指定A，B表关联顺序导致。

1.小表驱动大表 - 小表left join 大表。

2.大表的关联列上有索引。

3.优化器优点利用limit及时终止程序。

4.多张表关联顺序选择可以使用Straight_join关键字查询。

InnoDB存储引擎-案例一：导致死锁-由于默认可重复度隔离级别避免了幻读。

跟加锁顺序相关，两个事务以相反的顺序操作数据，才会产生死锁；解决思路是挖掘出相反的顺序在哪。

InnoDB存储引擎-案例一：去掉间隙锁/降低InnoDB隔离级别-提交读

Insert数据之前先进行了delete操作导致产生了间隙锁，解决思路是在delete之前先判断数据有无

三.MySQL优化：SQL优化和索引优化

索引：存储引擎用于快速定位记录的一种数据结构，索引有很多类别，B+树是InnoDB引擎默认的索引结构。

小型表小于一万的时候，全表主键扫描更高效。

中型以上表大于十万索引更好，但影响写入性能，因为写入到原表和索引里面，单表索引不能过多；对于复杂查询环境需要用多列索引。

聚簇索引：

InnoDB聚簇索引是在同一个结构中保存了B-Tree索引和数据行，InnoDB通过主键聚集数据。

聚簇索引       叶子节点存放数据库一行的数据。优点：访问快。缺点：修改聚簇索引代价高。

非聚簇索引   叶子节点存放数据的主键。缺点：二级索引访问需要两次索引查找。

注意：表都要定义显示主键，主键最好没有业务用途，不修改主键，单调递增，主键占用空间尽量小，避免是用随机主键。

多列索引-案例一：

索引条件下推 ICP-push down -5.6版本推出

select*from表where aid = 123 and date = ‘2022-05-16’；

5.6之前：不使用ICP查询流程

用户请求从 应用层->MySQL层->InnoDB存储引擎层（二级索引->aid相关的两个行->主键索引里面去做树查询拿出两行结果）->MySQL层（where条件根据时间过滤掉一行）->应用层

5.6之后：使用ICP查询流程

用户请求从 应用层->MySQL层->InnoDB存储引擎层（二级索引->aid相关两个行->where条件根据时间过滤掉一行）避免主键树扫描->MySQL层->应用层

任何版本：select 三个字段覆盖在整个索引之内：使用覆盖索引查询流程

应用层->MySQL层->InnoDB存储引擎层（二级索引）不进入主键索引扫描->MySQL层（where条件过滤返回结果）  ->应用层

总结：ICP使数据库查询效率提高，考虑查询最少的列，并建立多列索引。

SQL优化案例一：查询列不能有函数，应放在后面条件列

根据时间做查询的时候，考虑时间字段是否有索引，能否正确使用这个索引，where字段加入函数就会导致索引失效，要把函数处理放在条件之后。

where time函数>‘’ 改为 where time >‘函数处理’