Sql优化v-1.0

了解优化之前先了解一下搜索原理B树、B-树、B+树、B*树之间的关系！分享2篇博客！

https://blog.csdn.net/u013411246/article/details/81088914
https://tonydong.blog.csdn.net/article/details/104020721

以下优化SQL法则适用于各种关系型数据库，包括但不限于：MySQL、Oracle、SQL Server、PostgreSQL 以及 SQLite 等.

一、避免使用select * from 表；这样会检索出所有得数据，网络传输数据太多也会降低性能。
二、确保查询使用正确的索引。
缺少合适的索引在查询时候也不会通过索引查找数据。因此记录一些经常用到需要建立索引的地方！
1、where后面的赛选条件可以添加索引，可避免全表扫描。
2、order by 排序的字段加入到索引中，避免排序操作，索引自带排序规则。
3、多链接查询关联字段建立索引，可提高链接查询功能。
4、group by分组操作的字段建立索引，可利用索引完成分组。

即使为关键字段建立了索引，单sql写的有问题，也会导致索引失效！个人知道以下几点。
1、where子句中，对索引字段进行表达式运算或者调用函数，会导致索引失效，这种情况，还包括数据类型的不匹配，例如字符串和整数比较大小。
2、在like模糊查询时候，通配符在左侧也会导致索引失效，一般大型的文本数据模糊匹配考虑使用弹性搜索引擎(全文搜索引擎)（elasticSearch）
3、在where后加索引的字段，尽量设置为not null，虽然不是对所有的数据库都有效果。

执行计划

执行计划（execution plan，也叫查询计划或者解释计划）是数据库执行 SQL 语句的具体步骤，例如通过索引还是全表扫描访问表中的数据，连接查询的实现方式和连接的顺序等。如果 SQL 语句性能不够理想，我们首先应该查看它的执行计划，通过执行计划（EXPLAIN）确保查询使用了正确的索引。
关于各种主流数据库中执行计划的查看和解释，可以参考这篇文章和这篇文章。

https://tonydong.blog.csdn.net/article/details/103579177
https://blog.csdn.net/horses/article/details/106905110

三、尽量减少使用子查询
以 MySQL 为例，以下查询返回月薪大于部门平均月薪的员工信息：

EXPLAIN ANALYZE
 SELECT emp_id, emp_name
   FROM employee e
   WHERE salary > (
     SELECT AVG(salary)
       FROM employee
       WHERE dept_id = e.dept_id);
-> Filter: (e.salary > (select #2)) (cost=2.75 rows=25) (actual time=0.232..4.401 rows=6 loops=1)
    -> Table scan on e  (cost=2.75 rows=25) (actual time=0.099..0.190 rows=25 loops=1)
    -> Select #2 (subquery in condition; dependent)
        -> Aggregate: avg(employee.salary) (actual time=0.147..0.149 rows=1 loops=25)
            -> Index lookup on employee using idx_emp_dept (dept_id=e.dept_id) (cost=1.12 rows=5) (actual time=0.068..0.104 rows=7 loops=25)

 从执行计划可以看出，MySQL 中采用的是类似 Nested Loop Join 实现方式；子查询循环了 25 次，而实际上可以通过一次扫描计算并缓存每个部门的平均月薪。以下语句将该子查询替换为等价的 JOIN 语句，实现了子查询的展开（Subquery Unnest）：

EXPLAIN ANALYZE
 SELECT e.emp_id, e.emp_name
   FROM employee e
   JOIN (SELECT dept_id, AVG(salary) AS dept_average
           FROM employee
          GROUP BY dept_id) t
     ON e.dept_id = t.dept_id
  WHERE e.salary > t.dept_average;
-> Nested loop inner join (actual time=0.722..2.354 rows=6 loops=1)
    -> Table scan on e (cost=2.75 rows=25) (actual time=0.096..0.205 rows=25 loops=1)
    -> Filter: (e.salary > t.dept_average)  (actual time=0.068..0.076 rows=0 loops=25)
        -> Index lookup on t using <auto_key0> (dept_id=e.dept_id) (actual time=0.011..0.015 rows=1 loops=25)
            -> Materialize (actual time=0.048..0.057 rows=1 loops=25)
                -> Group aggregate: avg(employee.salary) (actual time=0.228..0.510 rows=5 loops=1)
                    -> Index scan on employee using idx_emp_dept (cost=2.75 rows=25) (actual time=0.181..0.348 rows=25 loops=1)

改写之后的查询利用了物化（Materialization）技术，将子查询的结果生成一个内存临时表；然后与 employee 表进行连接。通过实际执行时间可以看出这种方式更快。
以上示例在 Oracle 和 SQL Server 中会自动执行子查询展开，两种写法效果相同；在 PostgreSQL 中与 MySQL 类似，第一个语句使用 Nested Loop Join，改写为 JOIN 之后使用 Hash Join 实现，性能更好

另外，对于 IN 和 EXISTS 子查询也可以得出类似的结论。由于不同数据库的优化器能力有所差异，我们应该尽量避免使用子查询，考虑使用 JOIN 进行重写。

四、不乱用offset进行分页的实现，
分页查询的原理就是先跳过指定的行数，再返回 Top-N 记录。

数据库一般支持 FETCH/LIMIT 以及 OFFSET 实现 Top-N 排行榜和分页查询。当表中的数据量很大时，这种方式的分页查询可能会导致性能问题。以 MySQL 为例：

-- MySQL
SELECT *
  FROM large_table
 ORDER BY id
 LIMIT 10 OFFSET N;

以上查询随着 OFFSET 的增加，速度会越来越慢；因为即使我们只需要返回 10 条记录，数据库仍然需要访问并且过滤掉 N（比如 1000000）行记录，即使通过索引也会涉及不必要的扫描操作。