SQL优化

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 排序优化 (排序时,使用有索引的字段进行排序) 

2.8 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

注：使用索引，避免使用全表扫描和没有索引的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