数据库(mysql)sql语句优化

最新推荐文章于 2024-05-14 19:18:37 发布

原创最新推荐文章于 2024-05-14 19:18:37 发布 · 489 阅读

1 ·

CC 4.0 BY-SA版权

mysql 专栏收录该内容

20 篇文章

订阅专栏

本文详细介绍了导致全表扫描的常见情况，包括模糊查询、不当使用OR语句等，并提供了优化建议。同时，分享了一系列SQL实践技巧，如使用EXISTS替代IN、优化GROUP BY语句等，帮助提升数据库查询性能。

一、引起全表扫描的情况
1.1、模糊查询效率很低
1.2、查询条件中含有is null的select语句执行慢
1.3、查询条件中使用了不等于操作符(<>、!=)的select语句执行慢
1.4、使用组合索引
1.5、or语句使用不当会引起全表扫描
1.6、组合索引
1.7、Update 语句
1.8、表JOIN
1.9、查询不带where条件
1.10、sql的where条件要绑定变量
二、一些常见的SQL实践
2.1 负向条件查询不能使用索引
2.2 前导模糊查询不能使用索引
2.3 数据区分度不大的字段不宜使用索引
2.4 在属性上进行计算不能命中索引
2.5 如果业务大部分是单条查询，使用Hash索引性能更好，例如用户中心
2.6 允许为null的列，查询有潜在大坑
2.7 复合索引最左前缀，并不是值SQL语句的where顺序要和复合索引一致
2.8 使用ENUM而不是字符串
2.9 如果明确知道只有一条结果返回，limit 1能够提高效率
2.10 把计算放到业务层而不是数据库层，除了节省数据的CPU，还有意想不到的查询缓存优化效果
2.11 强制类型转换会全表扫描
三、SQL的优化技巧
3.1 Where子句中的连接顺序(Oracle)
3.2 用Where子句替换HAVING子句
2.3 删除全表时，用truncate 替代 delete
3.4 删除重复记录-rowid(oracle,pgsql ctid)
3.5 尽量多使用commit：
3.6 用EXISTS替代IN
3.7 用NOT EXISTS替代NOT IN
3.8 用EXISTS替换DISTINCT
3.9 优化group by
3.10 union-all 替代union
3.11 在含有子查询的SQL语句中，要特别注意减少对表的查询
3.12 SELECT子句中避免使用’*’
3.13 一些函数的使用技巧
3.14 计算记录条数
3.15 使用DECODE函数来减少处理时间
3.16 索引的引用
一、引起全表扫描的情况
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=mysqlquery(′select∗fromorderwheredate<=res=mysqlquery(′select∗fromorderwheredate<=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);
1
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替换
1
例如:
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