编写sql优化细节整理

规范sql编写:
1.SQL语句使用大写的，减少编译时间
2.查询时候使用表的别名，减少编译时间以及拼接表中列歧义引起的错误
3.表名顺序，sql自右往左处理from后的表名，数据量大的表放在后边，多个表的情况下使用数据量少的表作为基础表，3个表的话用交叉表作为基础表
4.where子句中的连接顺序：sql子下往上处理where子句，将能过滤多数数据的条件放到末尾。
5.select 中避免使用*，减少编译时间
6.数据处理尽量放在服务器上执行，建立编译好的存储过程
7.多使用commit提交，不要执行了很多处理语句后再提交
8.尽量减少对表的查询，select中不查表，where后不查表
9.避免耗费资源高的操作：distinct,union,minus(差集),intersect(交集)，order by
10.union all 不会对结果集排序，可以使用。
11.exists替换distinct，exists子查询满足条件会立刻返回结果。
select a.name from users a where exists (select 1 from users b where a.name=b.name);
12.where子句替换having，一般我都不用having，很少接触到，也不了解。
13.truncate代替delete清空表。
14.decode函数可以减少处理时间
15.用exists代替in，not exists代替not in
in 跟exists执行效率会有交集，
not in 效率在各种情况下都是最低效的，用not exists代替
16.全数字的字段尽量不建成字符型
17.varchar/nvarchar 比char/nchar效率高
18.表连接不应太多，5个以内最好
19.避免使用多层嵌套视图
20.in中出现多的放在前边，减少遍历次数
21.尽量使用>=,少使用>
22.批量添加数据，使用批量添加语句
23.尽量少使用触发器
24.合理使用索引，对于数据操作频繁的尽量不要使用索引，影响操作效率
--新建测试表
CREATE TABLE TEST_SQL(
ID VARCHAR2(32) PRIMARY KEY,
NAME VARCHAR2(30) NOT NULL,
AGE INTEGER,
DEPT_ID VARCHAR2(10),
COMP_ID VARCHAR2(10)
);
--插入1万条测试数据
INSERT INTO TEST_SQL
SELECT
SYS_GUID() AS ID,
'TEST'||LEVEL AS NAME,
TRUNC(DBMS_RANDOM.VALUE(18,45)) AS AGE,
'00'||TRUNC(DBMS_RANDOM.VALUE(1,7)) AS DEPT_ID,
CASE TRUNC(DBMS_RANDOM.VALUE(1,4)) WHEN 1 THEN 'HUAWEI'
WHEN 2 THEN 'ALIBABA'
WHEN 3 THEN 'TENCENT' END AS COMP_ID
FROM DUAL CONNECT BY LEVEL<=100000;

--建立索引
CREATE INDEX INDEX_TEST_SQL1 ON TEST_SQL(COMP_ID);
--查看索引生效情况 pl/sql F5查看
EXPLAIN PLAN FOR SELECT A.NAME FROM TEST_SQL A WHERE A.COMP_ID='HUAWEI';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
COMMIT;

测试不使用索引情况
1.查询返回结果超过总量10%左右的情况下不会使用索引
网上说查询结果为总数的10%左右时候不会使用全表扫描，我测试了下只索引列控制8%左右不便不使用全表扫描了，使用合并其他条件控制大于11.01%全表扫描，有知道具体的可以留个言，谢谢。
2.WHERE 子句使用空值判断全表扫描
3.WHERE子句使用!=,<>操作符号全表扫描
4.WHERE子句索引列为函数的一部分全表扫描
5.WHERE子句中使用通配符%：
%WEI:全表扫描,
HUA%:索引不影响;
6.WHERE子句中字符类型转换：
dept_id=002：全表扫描,
dept_id='002'：使用索引;
7.WHERE子句中使用or连接：测试使用or连接不影响索引使用;
8.WHERE子句中使用in,not in:
IN:不影响索引使用，
NOT IN:停止使用索引，全表扫描