前言

项目系统开发过程中的一个重要性能优化步骤就是SQL数据库的优化

基于SQL语句开发执行流程优化简介

在解释SQL优化前，先简述一下SQL的执行过程，并提出一些简单的优化方案；
1、客户端编写并发送SQL语句：此处是SQL语句的创建过程，我们需要合理编写正确的SQL语句，比如减少不必要的数据操作、是否合理建立索引、是否利用了合理的数据操作等；
而在发送阶段还应考虑是否使用了连接池和长连接，当然这些既有好处也有坏处，客户端也需要考虑是否需要建立本地缓存，如果需要操作数据库、需要建立多少并发度的连接；

2、当客户端把SQL语句传送到服务器后，服务器进程会对该语句进行解析。解析的第一个步骤是进行高速缓存查询，这里的缓存存储的主要是已经经过SQL解析的SQL语句，它存储SQL语句的解析模板，以后遇到同一语句可以做一个快速的处理，从而跳过下一解析处理步骤；
3、当在高速缓存中找不到对应的SQL语句时，会对SQL语句的语法进行检查，看看其是否合乎语法规则。若SQL 语句符合语法上的定义的话，接下来对语句中涉及的表、索引、视图等对象进行解析，并对照数据字典检查这些对象的名称以及相关结构，看看这些字段、表、视图等是否在数据库中。当语法、语义都正确后，系统就会对我们需要查询的对象加锁。这主要是为了保障数据的一致性，防止我们在查询的过程中，其他用户对这个对象的结构发生改变。当语法、语义通过检查之后，还会检查连接用户是否有这个数据访问的权限。然后进行最佳执行计划的确定；

4、经过一系列的SQL解析处理，进入SQL的执行过程各个SQL语句都会先尝试在缓冲池中操作数据，这个缓冲池是MySQL的缓存系统，淘汰策略是LRU，如果可以扩大这个缓存的命中率，也可以优化效率，不过当缓存池的大小超过数据本身的大小，对于数据文件的操作几乎都在内存中执行，此时数据库的缓冲池性能可能达到最优，无法上升；

5、如果缓冲池中没有数据，会尝试从磁盘来进行数据的操作，如果是DML语句，还会进行redo log和undo log的记录，进行数据库的操作后会进行缓冲池的备份；在硬盘层面，使用固态硬盘，利用闪存的低延迟性、低功耗、以及防震性可以进一步提升数据传输吞吐量；整个数据库操作也是基于CPU操作，对于是IO密集型事务或者CPU密集型事务也可以选择合适的硬件处理；

查询语句性能优化

应用最广泛的查询语句，除了建立良好的数据表和索引位，查询语句的编写是SQL语句优化的重要一部分，对于SQL查询的优化，我的理解是剪掉一些MySQL冗余操作和尽量减少IO次数来实现；具体以以下几种。
确认应用程序是否在检索大量超过需要的数据。进行了过多不必要的IO。
确认MySQL服务器层是否在分析大量超过需要的数据行。执行不必要的SQL冗余操作；

是否由不需要的数据被请求了

如果需要查找所有数据的前几条，最好选择使用limit ，而不是查到全部数据再操作。
如果需要查询所有数据，避免使用 SELECT * ………，比如如果需要查询表A的所有列数据，可以列出所有列或者使用·SELECT A.* from A ;因为使用SELECT * ,优化器无法进行覆盖索引这类的优化。而且会导致一些额外的硬件消耗。
除非需要缓存这些额外的数据，否则尽量避免。

MySQL是否执行了额外的记录扫描操作
首先是索引的使用，索引可以快速的进行数据查询的原理之一就是它可以减少查找数据时的遍历操作；
通过EXPLAIN语句可以查到SQL的执行计划，从中可以查到访问的类型如全表扫描到索引扫描、范围扫描、唯一索引查询、常数引用等，通过索引或者覆盖索引的策略（所有需要用的列都放到索引中）来进行查找可以使得效率更高。
但并非使用索引就可以完全避免扫描不需要的数据行，很多时候查10行数据即使通过索引也要扫描更多的行数据。整体来说的一些解决方案：

使用索引覆盖扫描
改变库表结构
重写复杂查询
1、部分情况下将非常复杂查询细分为小而简单的查询
似乎与前文相悖，MySQL设计使得内部每秒能够扫描内存中上百万行数据，相比之下，MySQL响应数据给客户端就慢得多了，使用尽可能少的查询当然是更好的。
但是如果拆分查询可以减少工作量，未必需要死板的执行复杂语句，这个过程应该时灵活的；比如在删除特别大量数据时，如果不进行任务拆分，可能使得服务器的压力相当大，这时并非一件好事；
对于一些关联查询，如果将其拆分成多条SQL语句，也会有不少优点
1）缓存的利用率变高；
2）减少查询锁的竞争；
3）减少一部分关联查询中的冗余数据访问；

多表关联时表连接设计

1、嵌套循环与HASH
指通过子查询的语句来充当条件作为驱动表，从而在主语句即被驱动表中查找数据。并以前面的条件表的每个条件在后面的表中查找数据；
两种连接方式各有其优劣，MySQL在8.0版本之后支持Hash连接

#这个条件可能不是1对1的；
select * from a,b where a.id=b.id;
# 当a.id与b.id的对应关系很少时，通过在被驱动表表id上建立索引从而避免全表扫描，只需要在索引列快速查找即可；即在驱动表中的每个条件去依次从大表中查，假设有n个驱动表条件，一共需要查n次大表；
# 如果这个对应的关系特别大，比如a的id对应了b的非常高倍。此时应该走hash连接，将较小的表建立hash值表，在大表中进行hash值匹配的全表扫描；匹配到为关联上；只需要扫描一次大表，但需要建立额外的hash表和计算值；当然仅支持等值连接；

在这里面，两个表的连接列需要设计索引；

嵌套循环查询过程本质时驱动表返回一行数据，通过连接列传值给被驱动表。驱动表返回多少行，被驱动表就要被扫描多少次；因此嵌套循环中被驱动表必须走索引。如果嵌套循环被驱动表的连接列没包含在索引中，那么被驱动表就只能走全表扫描，而且是反复多次全表扫描。当被驱动表很大的时候，SQL 就执行慢或者不出结果。
HASH连接的算法：两表等值关联，返回大量数据，将较小的表选为驱动表，将驱动表的“select列和join列”读入PGA中的work area，然后对驱动表的连接列进行hash运算生成hash table，当驱动表的所有数据完全读入PGA中的work area之后，再读取被驱动表（被驱动表不需要读入PGA中的work area），对被驱动表的连接列也进行hash运算，然后到PGA中的work area去探测hash table，找到数据就关联上，没找到数据就没关联上。因为HASH连接需要将驱动表的select列和join列放入PGA中，所以，我们应该尽量避免书写select * from…语句，将需要的列放在select list中，这样可以减少驱动表对PGA的占用，避免驱动表被溢出到临时表空间，从而提升查询性能。

2、排序合并连接（Oracle中的基本连接方式之一）
有时需要处理非等值的连接。比如>，>=，<，<=，<>。

select * from a,b where a.id>=b.id;

对于这类型的，如果是对应关系大，即返回的结果值大的情况，无法使用嵌套循环，因为不是等值连接，也无法使用hash连接，这种在Oracle中是使用的排序合并连接，两表关联，先对两个表根据连接列进行排序，将较小的表作为驱动表（Oracle官方认为排序合并连接没有驱动表，笔者认为是有的），然后从驱动表中取出连接列的值，到已经排好序的被驱动表中匹配数据，如果匹配上数据，就关联成功。驱动表返回多少行，被驱动表就要被匹配多少次，这个匹配的过程类似嵌套循环，但是嵌套循环是从被驱动表的索引中匹配数据，而排序合并连接是在内存中（PGA中的work area）匹配数据。

SQL优化的几种情况

1、like语句中以通配符开始的字段
select .. from .. where name like '%t%'
2、not 运算无法使用索引,如果需要使用not in 可以改为使用exists
select .. from .. where sal != 3000 ;
3、索引列上的or操作会全表扫描，可使用union操作；
4、索引遇到表达式会失效，避免使用where后面的表达式；
5、索引并非越多越好，因为需要维护；
6、分页语句中如果分页语句中有排序（order by），要利用索引已经排序特性，将order by的列按照排序的先后顺序包含在索引中，同时要注意排序是升序还是降序。且使用limit。
7、多关联的分页语句可以利用排序、行号等特性；
8、对于特别大型的数据行操作，比如要删掉大于100000的数据，可以尝试多次进行操作，一次性删除太多数据，可能会有lock wait timeout exceed的错误，所以建议分批操作。
9、exist和in都能用于范围查找，如果从a中查b中有的数据号

select * from A where deptId in (select deptId from B);
select * from A where exists (select 1 from B where A.deptId = B.deptId);
#如果B的数据量小于A，使用in，如果B的数据量大于A，选择exist。

10、善于利用explain来查看执行计划
id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明