数据库优化查询

优化数据库查询是一个关键的任务，可以显著提高应用的性能。

数据库的查询往往耗费饰件，如何优化查询也是重中之重。

以下是一些优化数据库查询的常见策略：

适当的索引：

         确保在关键字段上创建适当的索引，这将加速查询操作。主要考虑用于搜索、排序和连接的字段。然而，不要过度索引，因为过多的索引可能会降低写入性能。

合适的数据类型：

        选择合适的数据类型以减少存储和处理开销。例如，如果一个字段只存储正整数，使用整数数据类型而不是字符串。

规范化数据库：

        通过将数据拆分为多个关联表，避免数据冗余，减小存储开销，并提高查询效率。

避免全表扫描：

        尽量避免在没有索引的情况下对整个表进行扫描。使用索引以及适当的查询条件来限制结果集。

合理使用缓存：

        对于经常被查询的数据，考虑使用缓存技术，如Redis，以减少数据库的负载。

分页查询：

        在涉及大量数据的情况下，使用分页查询，避免一次性获取所有数据。

合理使用连接：

        使用适当的连接（INNER JOIN、LEFT JOIN、RIGHT JOIN）来合并相关的表，以获取所需的数据。

查询优化器：

        现代数据库管理系统通常具有查询优化器，它可以自动优化查询计划。确保数据库系统的统计信息是最新的，以便优化器可以做出更好的决策。

避免使用通配符：

        %： 在LIKE查询中使用通配符百分号（%）会导致全表扫描，应尽量避免使用。

        使用EXPLAIN分析查询计划： 数据库通常提供了 EXPLAIN 命令，可以分析查询计划，以便了解数据库是如何执行查询的，从而进行调整和优化。

定期维护：

        定期进行数据库的维护操作，如重新索引、清理过期数据等，以保持数据库性能。

分区表：

        对于大型表，考虑使用分区表技术，将表分为多个子表，以提高查询效率。

垂直分割：

        将表按照列进行拆分，将频繁访问和不频繁访问的列分开存储，提高热点数据的访问速度。

水平分割：

        将表按照行进行拆分，将数据分布到不同的表中，以减少单个表的数据量。

使用缓存查询结果：

        对于一些不经常变化的查询结果，可以将结果缓存起来，以减少数据库的负载。

综上所述，优化数据库查询是一个综合性的任务，需要考虑多个方面。根据具体的应用场景和需求，采取合适的优化策略，可以有效地提高数据库查询的性能。

一、用 >= 替代 >

例如 DEPTNO 上有一个索引： 

高效：

SELECT *  FROM EMP  WHERE DEPTNO >=4  

低效：

SELECT *  FROM EMP   WHERE DEPTNO >3

两者的区别在于, 前者 DBMS 将直接跳到第一个 DEPT 等于 4 的记录。而后者将首先定位到 DEPTNO=3 的记录并且向前扫描到第一个 DEPT 大于 3 的记录。

二、应尽量避免在 where 子句中对字段判断

例如：

select id from t where num is null

可以在 num 上设置默认值 0，确保表中 num 列没有 null 值，然后这样查询：

select id from t where num=0

三、SELECT 子句中避免使用  “* ”

当你想在 SELECT 子句中列出所有的 columns 时，使用动态 SQL 列引用‘*’是一个方便的方法。不幸的是，这是一个非常低效的方法。 实际上，Oracle 在解析的过程中, 会将“*” 依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。  

四、用 TRUNCATE 替代 DELETE

 当删除表中的记录时，在通常情况下，回滚段（rollback segments）用来存放可以被恢复的信息。如果你没有 COMMIT 事务，Oracle 会将数据恢复到删除之前的状态（准确地说是恢复到执行删除命令之前的状况），而当运用 TRUNCATE 时, 回滚段不再存放任何可被恢复的信息。当命令运行后，数据不能被恢复。因此很少的资源被调用，执行时间也会很短。

五、计算记录条数

 和一般的观点相反count(*) 比count(1)稍快，当然如果可以通过索引检索，对索引列的计数仍旧是最快的。例如 COUNT(EMPNO) 。

六、分页查询：

例子：从新闻文章表中分页获取新闻列表。
优化：使用LIMIT和OFFSET子句来实现分页。
原理：分页查询可以限制返回的数据量，避免一次性获取大量数据。
反例：没有分页限制，导致返回的数据过多，影响性能。

使用EXISTS子查询：

例子：检查是否有相关的记录存在。
优化：使用EXISTS子查询，它会在找到第一个匹配项后停止查询。
原理：EXISTS子查询可以提前终止查询，从而减少开销。
反例：使用IN子查询，它会一直查询所有匹配项。

用 EXISTS 替代 IN

在许多基于基础表的查询中，为了满足一个条件，往往需要对另一个表进行联接。在这种情况下，使用 EXISTS 或 NOT EXISTS 可以提高查询的效率。例如：

低效：

SELECT * 
FROM EMP 
WHERE EMPNO > 0 
AND DEPTNO IN (SELECT DEPTNO FROM DEPT WHERE LOC ='MELB')

高效：

SELECT * 
FROM EMP 
WHERE EMPNO > 0 
AND EXISTS (SELECT ‘X’  FROM DEPT WHERE DEPT.DEPTNO = EMP.DEPTNO AND LOC = 'MELB')

用 EXISTS 替换 DISTINCT  

当提交一个包含一对多表信息( 比如部门表和雇员表 )的查询时，避免在 SELECT 子句中使用 DISTINCT， 一般可以考虑用 EXIST 替换。例如

低效:     

SELECT DISTINCT DEPT_NO,DEPT_NAME  
FROM DEPT D,EMP E 
WHERE D.DEPT_NO = E.DEPT_NO 

高效:  

SELECT DEPT_NO,DEPT_NAME  
FROM DEPT D 
WHERE EXISTS ( SELECT ‘X’ FROM EMP E WHERE E.DEPT_NO = D.DEPT_NO)

避免使用函数在WHERE子句中：

例子：从员工表中查询薪水大于平均薪水的员工。
优化：将计算平均薪水的逻辑移到查询外部，避免使用函数在WHERE子句中。
原理：使用函数会导致数据库无法使用索引，从而影响查询性能。
反例：在WHERE子句中使用AVG函数。

应尽量避免在 where 子句中对字段判断

例如：

select id from t where num is null

可以在 num 上设置默认值 0，确保表中 num 列没有 null 值，然后这样查询：

select id from t where num=0

应避免在 where 中使用 != 或 <> 操作符

将引擎放弃使用索引而进行全表扫描。优化器将无法通过索引来确定将要命中的行数，因此需要搜索该表的所有行。

应避免在 where 子句中使用 or 连接

否则将导致引擎放弃使用索引而进行全表扫描，例如：

select id from t where num=10 or num=20

可以这样查询：

select id from t where num=10 union all select id from t where num=20

应避免在 where 中进行表达式操作

这将导致引擎放弃使用索引而进行全表扫描。例如：

SELECT * FROM T1 WHERE F1/2=100

应改为：

SELECT * FROM T1 WHERE F1=100*2

SELECT * FROM RECORD WHERE SUBSTRING(CARD_NO,1,4)='5378'

应改为：

SELECT * FROM RECORD WHERE CARD_NO LIKE '5378%'

SELECT member_number, first_name, last_name 
FROM members 
WHERE DATEDIFF(yy,datofbirth,GETDATE()) > 21

应改为：

SELECT member_number, first_name, last_name 
FROM members 
WHERE dateofbirth < DATEADD(yy,-21,GETDATE())

注：任何对列的操作都将导致表扫描，它包括数据库函数、计算表达式等等，查询时要尽可能将操作移至等号右边。

应避免在 where 子句中进行函数操作

这将导致引擎放弃使用索引而进行全表扫描。例如：

name 以 abc 开头的 id：

select id from t where substring(name,1,3)='abc'

应改为：

select id from t where name like 'abc%'

'2005-11-30'生成的 id：

select id from t where datediff(day,createdate,'2005-11-30')=0

应改为：

select id from t where createdate>=’2005-11-30′ and createdate<'2005-12-1'

使用UNION ALL代替UNION：

例子：合并两个结果集，要求结果集不重复。
优化：如果不需要去重，使用UNION ALL可以提高查询速度。
原理：UNION会对结果集进行去重操作，而UNION ALL不会。
反例：使用UNION操作，浪费性能在去重上。

使用子查询代替JOIN：

例子：从订单表中查询未支付的订单。
优化：使用子查询来避免使用不必要的JOIN操作。
原理：使用子查询可以避免产生临时中间结果集，提高效率。
反例：使用JOIN操作，导致不必要的数据关

索引的使用：

例子：在一个订单表中，通过订单号查询订单信息。
优化：为订单号列创建索引。
原理：索引可以加速数据定位，使数据库引擎可以直接跳到特定的位置查找数据。
反例：如果没有索引，数据库需要全表扫描以找到匹配的订单号，查询会变得更慢。

合适的列选择：

例子：从客户表中获取客户的姓名和电话号码。
优化：只选择需要的列，避免选择无关列。
原理：只选择需要的列可以减少从磁盘读取的数据量，提高查询速度。
反例：选择了所有列，包括不需要的列，增加了数据传输和处理的负担。

避免通配符开头的LIKE查询：

例子：根据商品名称查询商品信息，例如 "%apple%"。
优化：避免在通配符开头使用LIKE查询。
原理：以通配符开头的LIKE查询无法使用索引，会导致全表扫描。
反例：使用 "%apple%" 这样的查询会导致性能下降。

批量插入数据：

例子：插入大量数据到日志表。
优化：使用批量插入语句，如INSERT INTO ... VALUES (...), (...), (...)。
原理：批量插入可以减少频繁的数据库交互，提高插入效率。
反例：逐条插入数据会增加插入操作的开销。

合理使用连接查询：

例子：从订单表和客户表中查询订单信息及客户信息。
优化：使用INNER JOIN或LEFT JOIN连接表，避免笛卡尔积。
原理：合理使用连接可以避免产生过多的中间结果集，提高查询效率。
反例：未使用连接，导致产生大量冗余数据。

总结：

以上便是日常中我经常使用的优化方法，如果有错误希望大家多多包涵，也请评论指出！！！