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