数据库SQL优化

最新推荐文章于 2023-12-19 16:10:01 发布

有恒则成

最新推荐文章于 2023-12-19 16:10:01 发布

阅读量1.3w

点赞数 1

CC 4.0 BY-SA版权

分类专栏：数据库文章标签： SQL优化 SQL调优 SQL技巧 SQL全表扫描

本文链接：https://blog.youkuaiyun.com/hardworking0323/article/details/81029564

数据库专栏收录该内容

17 篇文章

订阅专栏

本文详细介绍了导致全表扫描的原因及解决方案，列举了一些常见的SQL实践问题及其优化建议，并分享了一系列SQL优化技巧，帮助提升数据库查询效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一、引起全表扫描的情况

1.1、模糊查询效率很低

原因
like本身效率就比较低，应该尽量避免查询条件使用like;对于like ‘%…%’(全模糊)这样的条件，是无法使用索引的，全表扫描自然效率很低;另外，由于匹配算法的关系，模糊查询的字段长度越大，模糊查询效率越低。
解决办法
首先尽量避免模糊查询，如果因为业务需要一定要使用模糊查询，则至少保证不要使用全模糊查询，对于右模糊查询，即like ‘…%’，是会使用索引的;左模糊like%…’无法直接使用索引，但可以利用reverse + function index 的形式，变化成 like ‘…%’;全模糊是无法优化的，一定要的话考虑用搜索引擎。
出于降低数据库服务器的负载考虑，尽可能地减少数据库模糊查询。

1.2、查询条件中含有is null的select语句执行慢

原因：
Oracle 9i中，查询字段is null时单索引失效，引起全表扫描。
解决方法
SQL语法中使用NULL会有很多麻烦，最好索引列都是NOT NULL的;对于is null，可以建立组合索引，nvl(字段,0),对表和索引analyse后，is null查询时可以重新启用索引查找,但是效率还不是值得肯定;is not null 时永远不会使用索引。一般数据量大的表不要用is null查询。

1.3、查询条件中使用了不等于操作符(<>、!=)的select语句执行慢

原因
SQL中，不等于操作符会限制索引，引起全表扫描，即使比较的字段上有索引
解决方法
通过把不等于操作符改成or，可以使用索引，避免全表扫描。例如，把column<>’aaa’，改成column<’aaa’ or column>’aaa’，就可以使用索引了。

1.4、使用组合索引

如果查询条件中没有前导列，那么索引不起作用，会引起全表扫描;但是从Oracle9i开始，引入了索引跳跃式扫描的特性，可以允许优化器使用组合索引，即便索引的前导列没有出现在WHERE子句中。例如：create index skip1 on emp5(job,empno); 全索引扫描 select count() from emp5 where empno=7900; 索引跳跃式扫描 select /+ index(emp5 skip1)/ count() from emp5 where empno=7900; 前一种是全表扫描，后一种则会使用组合索引。

1.5、or语句使用不当会引起全表扫描

原因
where子句中比较的两个条件，一个有索引，一个没索引，使用or则会引起全表扫描。例如：where A=：1 or B=：2，A上有索引，B上没索引，则比较B=：2时会重新开始全表扫描。

1.6、组合索引

排序时应按照组合索引中各列的顺序进行排序，即使索引中只有一个列是要排序的，否则排序性能会比较差。例如：create index skip1 on emp5(job,empno，date); select job，empno from emp5 where job=’manager’and empno=’10’ order by job,empno,date desc; 实际上只是查询出符合job=’manager’and empno=’10’条件的记录并按date降序排列，但是写成order by date desc性能较差。

1.7、Update 语句

如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。

updateByPrimaryKeySelective
updateByExampleSelective

1.8、表JOIN

对于多张大数据量(这里几百条就算大了)的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。

1.9、查询不带where条件

select count(*) from table;这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。

1.10、sql的where条件要绑定变量

比如where column=：1，不要写成where column=‘aaa’，这样会导致每次执行时都会重新分析，浪费CPU和内存资源。

二、一些常见的SQL实践

2.1 负向条件查询不能使用索引

select * from order where status!=0 and stauts!=1

not in/not exists都不是好习惯

可以优化为in查询：

select * from order where status in(2,3)

2.2 前导模糊查询不能使用索引

select * from order where desc like ‘%XX’

而非前导模糊查询则可以：

select * from order where desc like ‘XX%’

2.3 数据区分度不大的字段不宜使用索引

select * from user where sex=1-

原因：性别只有男，女，每次过滤掉的数据很少，不宜使用索引。

经验上，能过滤80%数据时就可以使用索引。对于订单状态，如果状态值很少，不宜使用索引，如果状态值很多，能够过滤大量数据，则应该建立索引。

2.4 在属性上进行计算不能命中索引

select * from order where YEAR(date) < = ‘2017’
即使date上建立了索引，也会全表扫描，可优化为值计算：
select * from order where date < = CURDATE()
或者：
select * from order where date < = ‘2017-01-01’

2.5 如果业务大部分是单条查询，使用Hash索引性能更好，例如用户中心

select * from user where uid=?
select * from user where login_name=?
原因：
B-Tree索引的时间复杂度是O(log(n))
Hash索引的时间复杂度是O(1)

2.6 允许为null的列，查询有潜在大坑

单列索引不存null值，复合索引不存全为null的值，如果列允许为null，可能会得到“不符合预期”的结果集

select * from user where name != ‘shenjian’

如果name允许为null，索引不存储null值，结果集中不会包含这些记录。

所以，请使用not null约束以及默认值。

2.7 复合索引最左前缀，并不是值SQL语句的where顺序要和复合索引一致

用户中心建立了(login_name, passwd)的复合索引

select * from user where login_name=? and passwd=?
select * from user where passwd=? and login_name=?
都能够命中索引
select * from user where login_name=?

也能命中索引，满足复合索引最左前缀

select * from user where passwd=?
不能命中索引，不满足复合索引最左前缀

2.8 使用ENUM而不是字符串

ENUM保存的是TINYINT，别在枚举中搞一些“中国”“北京”“技术部”这样的字符串，字符串空间又大，效率又低。

2.9 如果明确知道只有一条结果返回，limit 1能够提高效率

select * from user where login_name=?
可以优化为：
select * from user where login_name=? limit 1
原因：
你知道只有一条结果，但数据库并不知道，明确告诉它，让它主动停止游标移动

2.10 把计算放到业务层而不是数据库层，除了节省数据的CPU，还有意想不到的查询缓存优化效果

select * from order where date < = CURDATE()
这不是一个好的SQL实践，应该优化为：
$curDate = date(‘Y-m-d’);
$res = mysql_query( 'select * from order where date < =$ curDate’);

原因：
释放了数据库的CPU
多次调用，传入的SQL相同，才可以利用查询缓存

2.11 强制类型转换会全表扫描

select * from user where phone=13800001234
*字段会全表扫描
优化

select username,password,phone from user where phone=13800001234

三、SQL的优化技巧

3.1 Where子句中的连接顺序(Oracle)

oracle采用自下而上的顺序解析where子句，根据这个原理，表之间的连接必须写在其他where条件之前，那些可以过滤掉大量记录的条件
必须写在where子句的末尾。；连接查询时，大表放在前面，小表放在后面例如:
低效：

SELECT
    *
FROM
    report_sale_account e
WHERE
    hsje > 5000
AND dzxl = ‘000001’
AND 25 < (
    SELECT
        COUNT (*)
    FROM
        report_sale_account
    WHERE
        code = e.code
);

高效：

SELECT
    *
FROM
    report_sale_account e
WHERE
    25 < (
        SELECT
            COUNT (*)
        FROM
            report_sale_account
        WHERE
            code = e.code
    )
AND hsje > 5000
AND dzxl = ‘000001’;

3.2 用Where子句替换HAVING子句

避免使用HAVING子句，HAVING 只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序、总计等操作，如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销, 例如:
Sql代码

--低效   
SELECT REGION，AVG(LOG_SIZE) FROM LOCATION GROUP BY REGION HAVING REGION != ‘SYDNEY’ AND REGION != ‘PERTH’   
--高效   
SELECT REGION，AVG(LOG_SIZE)  FROM  LOCATION WHERE REGION != ‘SYDNEY’ AND REGION != ‘PERTH’ GROUP BY REGION

2.3 删除全表时，用truncate 替代 delete

同时注意truncate只能在删除全表时适用，因为truncate是ddl而不是dml。

例如删除掉一个100万行的数据。

Truncate table report_sale_account;

比

delete from report_sale_account;

至少快1000倍。

3.4 删除重复记录-rowid(oracle,pgsql ctid)

最高效的删除重复记录方法 ( 因为使用了ROWID)
Sql代码

DELETE FROM EMP E WHERE E.ROWID > (SELECT MIN(X.ROWID) FROM EMP X WHERE X.EMP_NO = E.EMP_NO);

3.5 尽量多使用commit：

只要有可能就在程序中对每个delete、insert、update操作尽量多使用commit,这样系统性能会因为commit所释放的资源而大大提高。

3.6 用EXISTS替代IN

在许多基于基础表的查询中,为了满足一个条件,往往需要对另一个表进行联接.在这种情况下, 使用EXISTS(或NOT EXISTS)通常将提高查询的效率.
Sql代码

--低效   
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’)

3.7 用NOT EXISTS替代NOT IN

在子查询中，NOT IN子句将执行一个内部的排序和合并. 无论在哪种情况下，NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN，我们可以把它改写成外连接(Outer Joins)或NOT EXISTS. 例如:

SELECT …FROM EMP  WHERE DEPT_NO NOT IN (SELECT DEPT_NO FROM DEPT WHERE DEPT_CAT=’A’);

Sql代码

--为了提高效率改写为: (方法一: 高效)   
SELECT ….FROM EMP A,DEPT B WHERE A.DEPT_NO = B.DEPT(+) AND B.DEPT_NO IS NULL AND B.DEPT_CAT(+) = ‘A’   
-- (方法二: 最高效)   
SELECT ….FROM EMP E WHERE NOT EXISTS (SELECT ‘X’  FROM DEPT D WHERE D.DEPT_NO = E.DEPT_NO AND DEPT_CAT = ‘A’);

3.8 用EXISTS替换DISTINCT

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

例如:
Sql代码

--低效:    
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);   
--EXISTS 使查询更为迅速,因为RDBMS核心模块将在子查询的条件一旦满足后,立刻返回结果.

3.9 优化group by

提高group by语句的效率，可以将不需要的记录在group by之前过滤掉。

例如：
低效：

SELECT
    dzxl,
    AVG (hsje)
FROM
    report_sale_account
GROUP BY
    dzxl
HAVING
    dzxl = ‘000001’
OR dzxl = ’000002’;

高效：

SELECT
    dzxl,
    AVG (hsje)
FROM
    report_sale_account
WHERE
    dzxl = ‘000001’
OR dzxl = ’000002’
GROUP BY
    dzxl;

避免使用HAVING子句，HAVING只会在检索出所有记录之后才对结果集进行过滤，这个处理需要排序、统计等操作。如果能通过WHERE子句限制记录的数目，那就能减少这方面的开销。

3.10 union-all 替代union

有条件的使用union-all 替代 union：这样做效率会提高3到5倍。

3.11 在含有子查询的SQL语句中，要特别注意减少对表的查询

例如：

低效

SELECT
    SUM (HSJE)
FROM
    REPORT_SALE_ACCOUNT 　　WHERE DZXL = (
        SELECT
            DZXL 　　
        FROM
            BASEINFO_GOODS 　　
        WHERE
            CODE = ‘0001’
    ) 　　
AND PP = (
    SELECT
        PP 　　
    FROM
        BASEINFO_GOODS 　　
    WHERE
        CODE = ‘0001’
)

　　
高效

SELECT
    SUM (HSJE)
FROM
    REPORT_SALE_ACCOUNT 　　
WHERE
    (DZXL, PP) = (
        SELECT
            DZXL,
            PP 　　
        FROM
            BASEINFO_GOODS 　　
        WHERE
            CODE = ‘0001’
    )

Update多个Column同样适用于以上的例子。

3.12 SELECT子句中避免使用’*’

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

3.13 一些函数的使用技巧

虽然在SQL中盲目引用函数会使性能降低，但如果正确使用合适的函数不仅会使SQL可读性加强，并且能对SQL性能得到提高，使复杂的查询能很方便地实现

低效的做法：

Select name,score,'优' 成绩等级  From score  Where score>=90
UNION ALL
Select name,score,'良' 成绩等级 From score Where score>=80 and score<90
UNION ALL
Select name,score,'中' 成绩等级 From score  Where score>=60 and score<80
UNION ALL
Select name,score,'差' 成绩等级 From score   Where score<60;
高效的做法1_decode函数：

高效的做法1_decode函数：

SELECT
    NAME,
    score,
    decode(
        sign(score - 90),
        - 1,
        decode(
            sign(score - 80),
            - 1,
            decode(
                sign(score - 60),
                - 1,
                '差',
                '中'
            ),
            '良'
        ),
        '优'
    ) AS 成绩等级
FROM
    score;

高效做法2_case when:

SELECT
    stuname,
    score,
    (
        CASE sign(score - 90)
        WHEN - 1 THEN
            CASE sign(score - 80)
        WHEN - 1 THEN
            CASE sign(score - 60)
        WHEN - 1 THEN
            '差'
        ELSE
            '中'
        END
        ELSE
            '良'
        END
        ELSE
            '优'
        END
    ) grade
FROM
    student;

3.14 计算记录条数

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

3.15 使用DECODE函数来减少处理时间

使用DECODE函数可以避免重复扫描相同记录或重复连接相同的表.
例如: Sql代码

SELECT COUNT(*)，SUM(SAL) FROM EMP WHERE DEPT_NO = 0020 AND  ENAME LIKE ‘SMITH%’;   
SELECT COUNT(*)，SUM(SAL) FROM EMP WHERE DEPT_NO = 0030 AND ENAME LIKE ‘SMITH%’;

你可以用DECODE函数高效地得到相同结果:

Sql代码

SELECT COUNT(DECODE(DEPT_NO,0020,’X’,NULL)) D0020_COUNT,   
        COUNT(DECODE(DEPT_NO,0030,’X’,NULL)) D0030_COUNT,   
        SUM(DECODE(DEPT_NO,0020,SAL,NULL)) D0020_SAL,   
        SUM(DECODE(DEPT_NO,0030,SAL,NULL)) D0030_SAL   
FROM EMP WHERE ENAME LIKE ‘SMITH%’;

类似的，DECODE函数也可以运用于GROUP BY 和ORDER BY子句中。
decode含义解释：
- decode(条件,值1,翻译值1,值2,翻译值2,…值n,翻译值n,缺省值)

3.16 索引的引用

当插入的数据为数据表中的记录数量的10%以上，首先需要删除该表的索引来提高数据的插入效率，当数据插入后，再建立索引
- 用索引提高效率
  索引是表的一个概念部分，用来提高检索数据的效率，实际上ORACLE使用了一个复杂的自平衡B-tree结构，通常通过索引查询数据比全表扫描要快，当ORACLE找出执行查询和Update语句的最佳路径时， ORACLE优化器将使用索引，同样在联结多个表时使用索引也可以提高效率，另一个使用索引的好处是，它提供了主键(primary key)的唯一性验证，除了那些LONG或LONG RAW数据类型, 你可以索引几乎所有的列. 通常, 在大型表中使用索引特别有效. 当然,你也会发现, 在扫描小表时,使用索引同样能提高效率，虽然使用索引能得到查询效率的提高,但是我们也必须注意到它的代价. 索引需要空间来存储，也需要定期维护，每当有记录在表中增减或索引列被修改时，索引本身也会被修改，这意味着每条记录的INSERT , DELETE , UPDATE将为此多付出4 , 5 次的磁盘I/O，因为索引需要额外的存储空间和处理，那些不必要的索引反而会使查询反应时间变慢。
  注：定期的重构索引是有必要的.
- 避免在索引列上使用函数或计算
  在where子句中，如果索引是函数的一部分，优化器将不再使用索引而使用全表扫描。如：

低效：

SELECT
    *
FROM
    report_sale_account
WHERE
    hsjj * 10 > 1000;

高效：

SELECT
    *
FROM
    report_sale_account
WHERE
    hsjj > 1000 / 10;

避免在索引上用!=
尽量避免在索引列上使用not和 “!=”和“<>”，索引只能告诉什么存在于表中，而不能告诉什么不存在于表中，当数据库遇到not 和 “!=” 和“<>”时，就会停止使用索引而去执行全表扫描。
避免在索引上使用函数
请务必注意，检索中不要对索引列进行处理，如：TRIM，TO_DATE，类型转换等操作，破坏索引，使用全表扫描，影响SQL执行效率
避免在索引上使用is null
避免在索引列上使用IS NULL和IS NOT NULL
避免在索引中使用任何可以为空的列，ORACLE将无法使用该索引
对于单列索引，如果列包含空值，索引中将不存在此记录；
对于复合索引，如果每个列都为空，索引中同样不存在此记录。如果至少有一个列不为空，则记录存在于索引中
因为空值不存在于索引列中，所以WHERE子句中对索引列进行空值比较将使ORACLE停用该索引
“>=”代替“>”
索引列上“>=”代替“>”
Sql代码

--如果DEPTNO上有一个索引   
--高效：   
   SELECT *  FROM EMP  WHERE DEPTNO >=4      
--低效：   
   SELECT *  FROM EMP   WHERE DEPTNO >3

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

比较大小函数 sign
sign(n)
函数说明：
取数字n的符号,大于0返回1,小于0返回-1,等于0返回0
示例：

select sign( 100 ),sign(- 100 ),sign( 0 ) from dual;


　　SIGN(100) SIGN(-100) SIGN(0)
　　———- ———- ———-
　　1 -1 0