sql优化

优化查询基础

• 为所有的join,where,order by和group by 中的条件简历索引
• 避免在条件中使用函数,in oracle If the function cannot be avoided in the SQL, you need to create a function-based index in Oracle or generated columns in DB2 to improve performance.
• 避免在谓词的开头使用通配符%
• 避免查询不必要的列
• 尽量使用inner jion而不是outer查询
• distinct与union能不用就不用

基础知识

• inner join 产生的结果是AB的交集
• left outer join 产生的结果是A的完全集,B表中匹配到的则有值,没有的以null替代
• cross join 产生笛卡尔积
• full join AB产生并集,没有的以null替代

1.左外连接将右表查询条件从where迁移到on中

SELECT TAB_A.COL1, TAB_B.COL1 FROM TAB_A LEFT OUTER JOIN TAB_B ON TAB_A.COL3 = TAB_B.COL3 WHERE TAB_A.COL1=123 AND TAB_B.COL2=456;
//优化
SELECT TAB_A.COL1, TAB_B.COL1 FROM TAB_A LEFT OUTER JOIN TAB_B ON TAB_A.COL3 = TAB_B.COL3 AND TAB_B.COL2=456 WHERE TAB_A.COL1=123;
###为不同的连接表设置同样的条件

SELECT TAB_A.COL1, TAB_B.COL1 FROM TAB_A LEFT OUTER JOIN TAB_B ON TAB_A.COL3 = TAB_B.COL3 WHERE TAB_A.COL1 IN (123, 456) AND TAB_B.COL2=TAB_A.COL1;
//优化后
SELECT TAB_A.COL1, TAB_B.COL1 FROM TAB_A LEFT OUTER JOIN TAB_B ON TAB_A.COL3 = TAB_B.COL3 AND TAB_B.COL2 IN (123, 456) AND TAB_B.COL2=TAB_A.COL1 WHERE TAB_A.COL1 IN (123, 456);
//如果只有一个也应该如此

2.切分查询,将一个查询拆分为多步查询

3.oracle中可以将查询结果放入一个temporary表中

BEGIN_ASSOCIATION_SQL_STATEMENT

name=IBM_CatalogEntryAttributeValue
base_table=CATENTRY

sql =
SELECT 
      CATENTRY.$COLS:CATENTRY$, ATTRVALUE.$COLS:ATTRVALUE$, 
      ATTRVALUE2.$COLS:ATTRVALUE$
FROM CATENTRY, ATTRVALUE 
JOIN ATTRIBUTE 
      ON ATTRIBUTE.LANGUAGE_ID = ATTRVALUE.LANGUAGE_ID 
      AND ATTRIBUTE.ATTRIBUTE_ID = ATTRVALUE.ATTRIBUTE_ID 
LEFT OUTER JOIN ATTRVALUE ATTRVALUE2 
      ON ATTRIBUTE.ATTRIBUTE_ID = ATTRVALUE2.ATTRIBUTE_ID 
      AND ATTRVALUE2.CATENTRY_ID = 0 
      AND ATTRIBUTE.LANGUAGE_ID = ATTRVALUE2.LANGUAGE_ID 
WHERE CATENTRY.CATENTRY_ID IN ( $ENTITY_PKS$) 
      AND ATTRVALUE.CATENTRY_ID = CATENTRY.CATENTRY_ID 
      AND ATTRVALUE.LANGUAGE_ID IN ($CTX:LANG_ID$) 

END_ASSOCIATION_SQL_STATEMENT

\\结果存入一个临时表
BEGIN_ASSOCIATION_SQL_STATEMENT

name=IBM_CatalogEntryAttributeValue
base_table=CATENTRY

sql =
WITH TEMP_TABLE AS (
SELECT 
      CATENTRY.CE_$COLS:CATENTRY$, ATTRVALUE.ATTR_$COLS:ATTRVALUE$, 
      ATTRVALUE2.ATTR2_$COLS:ATTRVALUE$
FROM CATENTRY, ATTRVALUE 
JOIN ATTRIBUTE 
      ON ATTRIBUTE.LANGUAGE_ID = ATTRVALUE.LANGUAGE_ID 
      AND ATTRIBUTE.ATTRIBUTE_ID = ATTRVALUE.ATTRIBUTE_ID 
LEFT OUTER JOIN ATTRVALUE ATTRVALUE2 
      ON ATTRIBUTE.ATTRIBUTE_ID = ATTRVALUE2.ATTRIBUTE_ID 
      AND ATTRVALUE2.CATENTRY_ID = 0 
      AND ATTRIBUTE.LANGUAGE_ID = ATTRVALUE2.LANGUAGE_ID 
WHERE CATENTRY.CATENTRY_ID IN ( $ENTITY_PKS$) 
      AND ATTRVALUE.CATENTRY_ID = CATENTRY.CATENTRY_ID 
      AND ATTRVALUE.LANGUAGE_ID IN ($CTX:LANG_ID$) 
) SELECT * FROM TEMP_TABLE

END_ASSOCIATION_SQL_STATEMENT

这可以让oracle优化器将查询的关键词提前放入view中,数据提前筛选出来后,后续的查询就可以从较小的set中获取

数据库

oracle面试1
oracle面试2
oracle分页

oracle数据库索引
mysql之innoDB和MyISAM的区别
mysql索引