首页 > 数据库 >SQL优化

SQL优化

时间:2022-11-20 22:13:03浏览次数:90  
标签:EXISTS 使用 索引 USER SQL 优化 ID

1 SQL基本优化规则(Oracle)

1.1 SQL 语句尽量用大写的,因为 oracle 总是先解析 SQL 语句,把小写的字母转换成大写的再执行。

1.2 使用表的别名,当在 SQL 语句中连接多个表时, 尽量使用表的别名并把别名前缀于每个列上。减少解析的时间并减少那些由列歧义引起的语法错误。

1.3 SELECT 子句中避免使用 *, ORACLE 在解析的过程中, 会将*依次转换成所有的列名, 这个工作是通过查询数据字典完成的, 这意味着将耗费更多的时间。

1.4 删除重复记录

1.5 避免使用耗费资源的操作带有 DISTINCT、UNION、MINUS、INTERSECT、ORDER BY 的 SQL 语句会启动 SQL 引擎执行,耗费资源的排序(SORT)功能。

1.6 用 UNION ALL 代替UNION

UNION 是最常用的集操作,使多个记录集联结成为单个集,对返回的数据行有唯一性要求,所以 oracle 就需要进行 SORT UNIQUE 操作(与使用 distinct 时操作类似),如果结果集又比较大,则操作会比较慢; UNION ALL 操作不排除重复记录行,所以会快很多,如果数据本身重复行存在可能性较小时,用 union all 会比用 union 效率高很多!
1.7 用 EXISTS 替换 DISTINCT:当 SQL 包含一对多表查询时,避免在 SELECT 子句中使用 DISTINCT,一般用 EXIST 替换,EXISTS查询更为迅速

(低效): SELECT DISTINCT USER_ID,BILL_ID FROM USER_TAB1 D,USER_TAB2 E WHERE D.USER_ID= E.USER_ID;

(高效): SELECT USER_ID,BILL_ID FROM USER_TAB1 D WHERE EXISTS(SELECT 1 FROM USER_TAB2 E WHERE E.USER_ID= D.USER_ID);

1.8 尽量多使用 COMMIT,只要有可能,在程序中尽量多使用 COMMIT,这样程序的性能得到提高,需求也会因为 COMMIT所释放的资源而减少。
COMMIT 所释放的资源:
a. 回滚段上用于恢复数据的信息。
b. 被程序语句获得的锁。
c. redo log buffer 中的空间。
d. ORACLE 为管理上述 3 种资源中的内部花销。

1.9 用 TRUNCATE 替代 DELETE,注:TRUNCATE 只在删除全表适用,TRUNCATE 是 DDL 不是 DML。

TRUNCATE 时,回滚段不再存放任何可被恢复的信息。

当删除表中的记录时,在通常情况下, 回滚段(rollback segments)用来存放可以被恢复的信息。

2.0 尽量使用inner join,避免left join

如果连接方式是inner join,在没有其他过滤条件的情况下MySQL会自动选择小表作为驱动表,但是left join在驱动表的选择上遵循的是左边驱动右边的原则,即left join左边的表名为驱动表。

2.1 insert优化,多次插入数据时,采用手动提交事务

start transaction;   执行sql;   commit;

2.2 count 优化     速度:count(*)>count(1)>count(字段)

2.3 update优化  (避免出现表锁)
innodb引擎使用update时,会有行锁/表锁两种模式, 如果where字段没有索引的时候会升级成表锁

2 SQL 索引优化

2.1 尽量少用 IN 操作符(IN->EXISTS, NOT IN->NOT EXISTS)

基本上所有的 IN 操作符都可以用 EXISTS 代替,在选择 IN 或 EXIST 操作时,要根据主子表数据量大小来具体考虑

尽量用 NOT EXISTS 或者外连接替代 NOT IN 操作符, 因为 NOT IN 不能应用表的索引. NOT IN 都是最低效的(要对子查询中的表执行了一个全表遍历)。

2.2 尽量不用“<>”或者“!=”操作符

不等于操作符是永远不会用到索引的,因此对它的处理只会产生全表扫描。比如:a<>0 改为a>0 or a<0

2.3 在设计表时,把索引列设置为NOTNULL, 判断字段是否为空一般是不会应用索引的,因为 B 树索引是不索引空值的。

2.4 尽量不用通配符“%”或者“_”作为查询字符串的第一个字符

当通配符“%”或者“_”作为查询字符串的第一个字符时,索引不会被使用。比如用 T 表中 Column1 LIKE „%5400%‟ 这个条件会产生全表扫描,如果改成 Column1 ‟X5400%‟ OR Column1 LIKE ‟B5400%‟ 则会利用 Column1 的索引进行两个范围的查询,性能肯定大大提高

2.5 用 UNION 替换 OR(适用于索引列)

2.6 避免在索引列上使用 IS NULL 和 IS NOT NULL

2.7 order by 排序优化 (排序时,使用有索引的字段进行排序) 

注:使用索引,避免使用全表扫描,和没有索引的sql。

 

3 Explain SQL分析

- 实际SQL,查找用户名为Jefabc的员工
select * from emp where name = 'Jefabc';
-- 查看SQL是否使用索引,前面加上explain即可
explain select * from emp where name = 'Jefabc';

 

 概要描述:
id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时,可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

 

 

资源

https://blog.csdn.net/m0_51740882/article/details/125082830

 

标签:EXISTS,使用,索引,USER,SQL,优化,ID
From: https://www.cnblogs.com/ningshare/p/16909602.html

相关文章

  • 寻找目标文件(优化版)
    此程序比上一个程序寻找文件更快#pragmawarning(disable:4996)#include<stdio.h>intmain(){intys[]={1,2,3,4,5,6,7,8,9,10};//可自行修改intn=0;......
  • SQLmap使用手册
    SQLmap使用手册参考连接https://www.tr0y.wang/2018/03/21/sqlmap-guide/#SQLmap-介绍SQLmap介绍什么是SQLmapSqlmap是由Python写成的,开源的自动化SQL......
  • SqlServer中的更新锁(UPDLOCK)
    优点: 允许读取数据(不阻塞其它事务)并在以后更新数据,同时确保自从上次读取数据后数据没有被更改当用UPDLOCK来读取记录时可以对取到的记录加上更新锁,从而加上锁的记录在其......
  • mysql-备忘录
    mysql基础注入知识参考资料:https://pureqh.top/?p=1882bypass安全狗常用函数ascii()length()substr()#左闭右闭hex()isnull()......
  • mysql this is incompatible with sql_mode=only_full_group_by
    MySQL分组查询时报错mysqlthisisincompatiblewithsql_mode=only_full_group_bymysql5.7[mysqld]sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGIN......
  • ORACLE SQL获取时间字段
    是本周第几天Selectto_char(sysdate,'D')-1fromdual 24小时的形式显示出来要用HH24selectto_char(sysdate,'yyyy-MM-ddHH24:mi:ss')fromdual;......
  • MySQL、Oracle、SQL Server时间类型字段
    关系型数据库时间类型字段MySQL(以及对应格式)SQLServer(以及对应格式)Oracletime(HH-MM-SS)time(12:35:29.1234567)date(YYYY-MM-DDHH:MM:SS)date(YYYY-MM-DD......
  • P8195 [传智杯 #4 决赛] 小智的疑惑 ----- 字符串匹配、KMP算法优化next数组
    题目描述传智专修学院给了小智一个仅包含小写字母的字符串 ss,他想知道,里面出现了多少次子串 chuanzhi 呢。我们称一个字符串 tt 是 ss 的子串,当且仅当将 ss 的......
  • mysql主从配置
    主mysql-uroot-p'd60v#-MXf>qn'createdatabaseywgl;grantreplicationslave,replicationclienton*.*to'repl'@'192.168.100.125'identifiedby'd60v#-MXf>q......
  • 使用位运算优化 N 皇后问题
    使用位运算优化N皇后问题作者:Grey原文地址:博客园:使用位运算优化N皇后问题CSDN:使用位运算优化N皇后问题问题描述N皇后问题是指在n*n的棋盘上要摆n个皇......