SQL优化技巧及相关注意事项

最新推荐文章于 2024-02-26 19:32:44 发布
最新推荐文章于 2024-02-26 19:32:44 发布
阅读量734 收藏
点赞数
分类专栏： Relational Database 文章标签： sql
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_42586723/article/details/106871856
版权

SQL优化技巧及相关注意事项

创建索引

字段选取

  • 选择准则

    避免建在大量重复值的字段上，在where、join、order by等字段上合理创建索引来提高性能

索引失效

负向查询

  • 案例脚本

    
    -- 反例
    select name from user where id not in (1,3,4);


    -- 正例
    select name from user where id in (2,5,6);

前导模糊查询

  • 案例脚本

    
    -- 反例
    select name from user where name like '%zhangsan'


    -- 正例
    select name from user where name like 'zhangsan%'

    可以考虑使用 ElasticSearch 等全文索引工具来代替频繁的模糊查询。

数据区分不明显(数据不唯一)

  • 案例脚本

    
    -- 如user表中的性别字段，可以明显区分的才建议创建索引，如身份证等字段

字段上进行函数运算

  • 案例脚本

    

    -- 反例
    select name from user where FROM_UNIXTIME(create_time) < CURDATE();


    -- 正例
    select name from user where create_time < FROM_UNIXTIME(CURDATE());

最左前缀问题

  • 案例脚本

    

    -- 创建组合索引
    create index index_name on user(username,pwd);


    -- 反例(不能命中索引)
    select username from user where pwd = 'axsedf1sd';


    -- 正例(能命中索引)
    select username from user where username='zhangsan' and pwd ='axsedf1sd'

    select username from user where pwd ='axsedf1sd' and username='zhangsan'

    select username from user where username='zhangsan'

多表联查join字段类型不相同

  • 案例脚本

    
    -- join字段类型必须相同，否则不会命中索引

索引列上使用is null 和 is not null

  • 案例脚本

    
    -- 避免在索引中使用任何可以为空的列，oracle将无法使用该索引。

    -- 1. 对于单列索引，如果列包含空值，索引中将不存在此记录。

    -- 2. 对于复合索引，如果每个列都为空，索引中同样不存在此记录，如果至少有一个列不为空，则记录存在于索引中

    -- 举例1：如果唯一性索引建立在表的A列和B列上，并且表中存在一条记录的A，B值为(123,null)，Oracle将不接受下一条具有相同A，B值(123,null)的记录插入。

    -- 举例2：如果所有索引列都为空，Oracle将认为整个键值为空，而空不等于空。因此你可以插入1000条具有相同键值的记录，当然它们都是空，因为空值不存在于索引列中，所以where子句中对索引列进行空值比较将使Oracle停用该索引


    -- 反例
    SELECT … FROM  DEPARTMENT  WHERE  DEPT_CODE IS NOT NULL; 


    -- 正例
    SELECT … FROM  DEPARTMENT  WHERE  DEPT_CODE >=0;

提高查询效率

避免全表扫描

  • 案例脚本

    
    -- 反例(数据库做强制类型转换，会导致全表扫描)
    select username from user where telno = 18722222222

    -- 正例
    select username from user where telno = '18722222222'

明确知道只有一条记录返回

  • 案例脚本

    
    -- 正例(MySQL中关键字limit 1，可以让数据库停止游标移动)
    select name from user where username='zhangsan' limit 1

返回尽量少的字段

  • 案例脚本

    
    
    -- 反例(读取适当的记录及需要的字段，避免用*)
    select * from user;

分组统计禁止排序

  • 案例脚本

    

    -- 正例(MySQL默认情况下对所有group by的字段进行排序，如果查询包括group by 而又不需要排序操作可以禁止)
    select count(1),username from user group by username order by null;

使用join替代子查询

  • 案例脚本

    
    -- 反例(内部执行计划器是这样执行的：先查外表再匹配内表，而不是先查内表t2，当外表的数据很大时，查询速度会非常慢。)
    SELECT * FROM t1 WHERE id in (SELECT id FROM t2 WHERE name= 'hechunyang');

    -- 正例(inner join内连接也叫做等值连接，left/right join是外链接)
    SELECT t1.* FROM t1 INNER JOIN t2 ON t1.id =t2.id;

使用(not) exists替代(not) in

  • 案例脚本

    

    -- 反例(NOT IN都是最低效的 (因为它对子查询中的表执行了一个全表遍历). 为了避免使用NOT IN，我们可以把它改写成外连接(Outer Joins)或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  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);

创建表时选择合适字段属性

  • 案例脚本

    
    -- 建表时尽量让字段not null，这样数据库在执行查询时不用比较null值

减少使用联接列

  • 案例脚本

    
    -- 假定有一个职工表(employee)，对于一个职工的姓和名分成两列存放(FIRST_NAME和LAST_NAME)，现在要查询一个叫比尔.克林顿(Bill Cliton)的职工

    -- 反例(对于有联接的列，即使最后的联接值为一个静态值，优化器是不会使用索引的)
    select * from employee where first_name||' '||last_name = 'Beill Cliton';

    -- 正例
    select * from employee where first_name ='Beill' and last_name ='Cliton';

用union替换or

  • 案例脚本

    
    --通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 在下面的例子中, LOC_ID 和REGION上都建有索引.

    -- 反例(如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面)
    SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE LOC_ID = 10 OR REGION = 'MELBOURNE' 

    -- 正例
    SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE LOC_ID = 10 
    UNION 
    SELECT LOC_ID, LOC_DESC, REGION FROM LOCATION WHERE REGION = 'MELBOURNE'

用union all 替换 union

  • 案例脚本

    
    --  SQL 语句需要UNION两个查询结果集合时，这两个结果集合会以UNION ALL的方式被合并, 然后在输出最终结果前进行排序，这个操作会使用到SORT_AREA_SIZE这块内存. 对于这块内存的优化也是相当重要的

    -- 反例
    SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE ='31-DEC-95' 
    UNION 
    SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE ='31-DEC-95' 

    -- 正例
    SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95'
    UNION ALL 
    SELECT ACCT_NUM, BALANCE_AMT FROM DEBIT_TRANSACTIONS WHERE TRAN_DATE = '31-DEC-95'

用where替换order by

  • 案例脚本

    
    -- order by 子句使用索引条件限制：

    -- 1. order by 中所有的列必须包含在相同的索引中并保持在索引中的排列顺序

    -- 2. order by 中所有的列必须定义为非空

    -- 3. WHERE子句使用的索引和ORDER BY子句中所使用的索引不能并列(这句话含义不是很懂)

优化group by

  • 案例脚本

    
    -- 将不需要的记录在GROUP BY 之前过滤掉

    -- 反例
    SELECT JOB, AVG(SAL) FROM EMP GROUP by JOB HAVING JOB = 'PRESIDENT' OR JOB = 'MANAGER'

    -- 正例
    SELECT JOB, AVG(SAL) FROM EMP WHERE JOB = 'PRESIDENT' OR JOB = 'MANAGER' GROUP by JOB

使用with as

  • 案例脚本

    
    -- 一次分析，多次使用

    -- 把一大堆重复用到的sql语句放在with as里面，取一个别名，后面的查询就可以用它，这样对于大批量的sql语句起到一个优化的作用，而且清楚明了
    insert into table2
    with
        s1 as (select rownum c1 from dual connect by rownum <= 10),
        s2 as (select rownum c2 from dual connect by rownum <= 10)
    select a.c1, b.c2 from s1 a, s2 b where...;

合理使用正则匹配函数

  • 案例脚本

    https://www.cnblogs.com/numen-fan/p/12059707.html

    
    -- 要求将clob字段存在资讯内容中所有富文本标签替换成空返回

    select INF_ID AS infId,INF_PDVDR_CD as infPvdrCd,regexp_replace(pblopn_txt_cntnt,'</?[^>]*>|nbsp;|&','') as pblOpnTxtCntnt from INTELLIGENT_INF;

    -- 执行效率非常堪忧，最后选择直接查询出结果，在代码中处理富文本标签

参考链接

https://www.cnblogs.com/gomysql/p/3632209.html

https://www.cnblogs.com/williamjie/p/9389530.html

其他

MySQL

unix_timestamp/from_unixtime函数使用

https://blog.csdn.net/qq_24309787/article/details/81095238

MySQL之SQL语句优化步骤

https://blog.csdn.net/DrDanger/article/details/79092808
Charles Yan
关注
专栏目录
SQL性能优化的21个小技巧
学Java，找哪吒
08-29 4万+
真正的一文在手，优化我有！
sql 优化语句的几个常用注意事项
11-07
保证数据库性能的sql语句写法，注意事项，使得我们在书写时避免使数据库低效率的执行命令
SQL优化
u011915230的专栏
06-02 427
数据库的优化问题一、问题的提出　在应用系统开发初期，由于开发数据库数据比较少，对于查询SQL语句，复杂视图的的编写等体会不出SQL语句各种写法的性能优劣，但是如果将应用 系统提交实际应用后，随着数据库中数据的增加，系统的响应速度就成为目前系统需要解决的最主要的问题之一。系统优化中一个很重要的方面就是SQL语句的优 化。对于海量数据，劣质SQL语句和优质SQL语句之间的速度差别可以达到上百倍，可见对于
sql优化和注意事项
baiyangfu的专栏
08-10 836
一、SQL的优化和注意事项 现在我们假设我们只有一台MySQL服务器，所有的select/update/insert/delete操作都是在这上面进行的，我们同时有三台Web服务器，通过DNS轮巡来访问，那么我们如何进行我们应用程序和SQL的优化。 1. Where条件 在查询中，WHERE条件也是一个比较重要的因素，尽量少并且是合理的where条件是很重要的，在写每一个where条件的时候
SQL优化及注意事项
ZERO
12-28 494
1.where子句中有"!="或"     select * from emp where amount != 0 (不使用)     select * from emp where amount > 0 (使用) 2.where条件中对字段增加处理函数将不使用该列的索引。     select * from emp where to_char(date,'yyyymmdd')='
sql 优化注意事项
预立数据科技
09-01 304
1.存储过程比sql语句效率高；存储过程经过预编译处理 而SQL查询没有 2.给表建立索引的字段（常见字段是where 后的条件字段） 3.查询表时使用（nolock）查询语句不会阻塞 4.不要使用 select * from dbo.table ，用具体的字段列表代替“*”，不要返回用不到的任何字段。同时避免使用count(*)； 5.From最后面的表为基础表，一般要选择记录数最少的表作为基...
SQL优化技巧
MsSpark的博客
01-09 1615
本文以Mysql数据库为例，总结数据库优化方法。
揭秘SQL优化技巧 改善数据库性能
12-15
以下是一些关键的SQL优化技巧和注意事项： 1. 减少IO次数：数据库的主要任务是管理和检索数据，而IO操作是其性能瓶颈所在。优化SQL的目标之一就是减少对磁盘或网络的读写次数。可以通过创建合适的索引、避免全表...
日常收集整理SqlServer数据库优化经验和注意事项
09-10
在SQL Server数据库优化中，有几个核心的策略和注意事项值得我们关注。首先，索引的创建是提高查询效率的关键。对于高频率查询的列，特别是那些出现在WHERE子句、JOIN条件和ORDER BY/GROUP BY短语中的列，应当考虑...
SQL编程之子查询及注意事项
09-10
以下是关于子查询及其注意事项的详细说明： 1. **子查询的定义与应用** - 子查询通常在WHERE子句中使用，作为筛选条件。它先被执行，生成一个临时结果集，这个结果集随后被父查询使用。 - 子查询可以用来获取满足...
《在Python中SQL Server的优化及注意事项》
最新发布
weixin_41441305的博客
02-26 1269
介绍：本书旨在为使用Python与SQL Server交互的开发者提供一个深入的指南，特别关注性能优化和最佳实践。目标读者：数据科学家、数据库管理员、后端开发者以及任何需要在Python应用中集成SQL Server的专业人士。
mysql优化-sql语句优化注意事项
hyy
11-17 315
1、避免SELECT * 从数据库里读出越多的数据，那么查询就会变得越慢。并且如果你的数据库服务器和 WEB 服务器是两台独立的服务器的话，这还会增加网络传输的负载。 select * from person where lname='x8RJWmQX'; select id from person where lname='x8RJWmQX'; 2、避免在where 子句中使用 != 或 ...
SQL语句的一些注意事项,和相关优化
WangXiaoYuuuuu
10-30 634
注意事项 1.不论一个sql中涉及到多个表,每次都用两个表(结果集)操作,得到新的结果后再和下一个表(结果集)操作. 2.避免在select f1,(select f2 from tableB )… from tableA 这样得到字段列。直接用tableA和tableB关联得到A.f1,B.f2就可以了。 3.避免隐含的类型转换 如 select id from employee where e...
SQL优化需要注意的一些点
qq_42026855的博客
05-04 409
1.对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：[sql] view plain copyselect id from t where num is null  最好不要给数据库留NULL，尽可能的使用 NOT NULL填充数据...
数据库优化和SQL中一些注意事项
weixin_43423579的博客
12-13 189
给DB2数据库建立索引脚本： 1）.在某张表建立单个索引： CREATE INDEX CUSTOMER_IDX_01 ON CUSTOMER (C_FIRST_NAME ASC)； 其中表为：CUSTOMERCUSTOMER 建立索引： CUSTOMER_IDX_01 需要查询字段：C_FIRST_NAME 自增：ASC // 注意！！！：数据库执行建立索引脚本后需要添加 CALL SYS...
oracle基本语法
a304096740的博客
01-17 86
/* 1. 创建一个表空间。 2. 创建一个用户，设置默认表空间。 3. 给用户授权。 4. 用当前用户登录，创建表、视图、存储过程等。 */ create tablespace systemManagerTablespace datafile 'systemManager.dbf' size 50M; --添加表空间大小 alter tablespace systemManagerT...
SQl语句优化应该注意什么？
volcano
12-25 182
具体要注意的： 1.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如： select id from t where num is null 可以在num上设置默认值0，确保表中num列没有null值，然后这样查询： select id from t where num=0 2.应尽量避免在 where 子句中使用!=或&lt;&gt;操...
MySQL执行计划解读
爱吃鱼油
06-26 380
执行计划 select_type  表示查询中每个select子句的类型（简单 OR复杂） a.SIMPLE：查询中不包含子查询或者UNION b.查询中若包含任何复杂的子部分，最外层查询则被标记为：PRIMARY c.在SELECT或WHERE列表中包含了子查询，该子查询被标记为：SUBQUERY d.在FROM列表中包含的子查询被标记为：DERIVED（衍生）
SQL优化技巧与注意事项
"本文主要探讨了SQL优化的重要性以及在实践中应当遵循的一些基本原则，旨在帮助开发者写出性能更优的SQL语句，提升系统响应速度。在大数据背景下，SQL优化成为系统优化的关键环节，优化后的SQL语句能显著提高系统...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值