sql优化思路以及方式

后端服务的性能优化是一个永恒的话题。这一篇随笔主要记录一下在各种业务场景下，sql优化的一些思路以及方式。

note：
DML（data manipulation language）是数据操纵语言：它们是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言。

DDL（data definition language）是数据定义语言：DDL比DML要多，主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。

DCL（DataControlLanguage）是数据库控制语言：是用来设置或更改数据库用户或角色权限的语句，包括（grant,deny,revoke等）语句。

1.避免使用select *写查询语句。
原因：
-1.查询时需要解析成表的所有字段然后再查询，增加查询解析器的成本；
-2.select * 查询一般不走覆盖索引会产生大量的回表查询；
-3.业务场景中需要使用的一般是较少的某些字段，其他不需要使用的字段也查出来浪费CPU，内存资源。
-4.文本数据，大字段数据传输增加网络消耗。

2.小表驱动大表
释义：指用数据量较小，索引比较完备的表，然后使用索引和条件对大表进行数据筛选，从而减少数据计算量，提高查询效率。
简单来说就是写联合查询的时候，用数据量较少的表去连接数据量较大的表。BNL算法（BLOCK-nested-loop-join）

3.用链接查询代替子查询
原因：
-1.因为子查询需要执行两次数据库查询，一次是外部查询，一次是嵌套子查询。而链接查询可以减少数据库查询次数，从而提高效率。
-2.链接查询可以更好的利用数据库的索引，提高查询性能。子查询通常使用临时表或者内存表。而连接查询可以直接利用表上的索引。这意味着连接查询可以更快访问表中的数据，减少查询的资源消耗。
-3.对于大型数据集，使用连接查询通常比使用子查询更高效。子查询需要扫描整个表，连接查询可以走索引。

4.提fgroup by的效率
创建索引:如果你使用group by的列没有索引，那么查询可能会变得很慢。因此，可以创建一个或多个适当的索引来加速查询。

5.优化批量插入，修改的操作。
1.尽量提供批量插入，修改，删除的mapper接口。
2.如果单次操作数据数量过大，数据库响应也会很慢。可以用List相关工具包，将数据集拆分成较少量的list数据集，再for循环写入。

6.使用limit
1.提高查询效率
一个查询返回成干上方的数据行，不仅占用了大量的系统资源，也会占用更多的网络带宽，影响查询效率。使用LIMIT可以限制返回的数据行数，减轻了系统负担，提高了查询效率。
2.避免过度提取数据
对于大型数据库系统，从数据库提取大量数据可能导致系统崩溃。使用limit可以限制提取的数据量，避免过度提取数据，保护系统不受影响。
3.优化分页查询
分页查询需要查询所有的数据才能进行分页处理，这会浪费大量的系统资源和时间。使用limit优化分页查询可以只查询需要的数据行，缩短查询时间，减少资源浪费。
4.简化查询结果

7.使用union all代替union
union all：获取所有数据但是不去重
union：获取所有数据且去重，需要遍历，排序，比较，消耗cpu资源。
如果必须保证结果集不重复，可以在java代码中做去重排序。

8.join的表不宜过多
理由：查询效率下降:多表JOIN查询数据对比时间边长
1.系统负载增加:JOIN操作需要进行大量的计算，因此会导致系统负载增加。
2.维护难度加大:在一个连接了多个表的查询中，如果需要修改其中一个表的结构或内容，就可能会需要同时修改其他表的结构或内容。
因此，在数据库设计时，应该尽量减少JOIN操作的使用频率，并且简化表之间的关系，以提高查询效率和系统的性能。

9.控制索引数量

10.选择合理的字段类型