oracle数据库性能优化经验

数据库性能优化：

1. 建立并正确的使用索引，遵循索引生效的规则；

2. 查询时明确指定所需字段而不是使用*通配；

3. 对于where的条件，过滤数据较多的条件放在右边（针对oracle，如果mysql则是从左到右）；

4. 尽量使用多表查询替代子查询；

5. 使用分页查询；

6. 尽量使用绑定，同时可以防止sql注入

7. 可以通过存储过程等在用户较少的情况下执行，从而错开系统使用的高峰时间，提高数据库性能

SQL什么条件会使用索引？

当字段上建有索引时，通常以下情况会使用索引：

INDEX_COLUMN = ?

INDEX_COLUMN > ?

INDEX_COLUMN >= ?

INDEX_COLUMN < ?

INDEX_COLUMN <= ?

INDEX_COLUMN between ? and ?

INDEX_COLUMN in (?,?,...,?)

INDEX_COLUMN like ?||'%'（后导模糊查询）

T1. INDEX_COLUMN=T2. COLUMN1（两个表通过索引字段关联）

 

SQL什么条件不会使用索引？

 

查询条件	不能使用索引原因
INDEX_COLUMN <> ? INDEX_COLUMN not in (?,?,...,?)	不等于操作不能使用索引
function(INDEX_COLUMN) = ? INDEX_COLUMN + 1 = ? INDEX_COLUMN || 'a' = ?	经过普通运算或函数运算后的索引字段不能使用索引
INDEX_COLUMN like '%'||? INDEX_COLUMN like '%'||?||'%'	含前导模糊查询的Like语法不能使用索引，可使用instr代替like instr(title,'oracle’)>0 相当于like instr(title,'oracle’)=0 相当于not like
INDEX_COLUMN is null	B-TREE索引里不保存字段为NULL值记录，因此IS NULL不能使用索引
NUMBER_INDEX_COLUMN='12345' CHAR_INDEX_COLUMN=12345	Oracle在做数值比较时需要将两边的数据转换成同一种数据类型，如果两边数据类型不同时会对字段值隐式转换，相当于加了一层函数处理，所以不能使用索引。
a.INDEX_COLUMN=a.COLUMN_1	给索引查询的值应是已知数据，不能是未知字段值。
注： 经过函数运算字段的字段要使用可以使用函数索引，这种需求建议与DBA沟通。 有时候我们会使用多个字段的组合索引，如果查询条件中第一个字段不能使用索引，那整个查询也不能使用索引 如：我们company表建了一个id+name的组合索引，以下SQL是不能使用索引的 Select * from company where name=? Oracle9i后引入了一种index skip scan的索引方式来解决类似的问题，但是通过index skip scan提高性能的条件比较特殊，使用不好反而性能会更差。

我们一般在什么字段上建索引？

这是一个非常复杂的话题，需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引，其它需要建索引的字段应满足以下条件：

1、字段出现在查询条件中，并且查询条件可以使用索引；

2、语句执行频率高，一天会有几千次以上；

3、通过字段条件可筛选的记录集很小，那数据筛选比例是多少才适合？

这个没有固定值，需要根据表数据量来评估，以下是经验公式，可用于快速评估：

小表(记录数小于10000行的表)：筛选比例<10%；

大表：(筛选返回记录数)<(表总记录数*单条记录长度)/10000/16

      单条记录长度≈字段平均内容长度之和+字段数*2

 

以下是一些字段是否需要建B-TREE索引的经验分类：

 

	字段类型	常见字段名
需要建索引的字段	主键	ID,PK
	外键	PRODUCT_ID,COMPANY_ID,MEMBER_ID,ORDER_ID,TRADE_ID,PAY_ID
	有对像或身份标识意义字段	HASH_CODE,USERNAME,IDCARD_NO,EMAIL,TEL_NO,IM_NO
索引慎用字段,需要进行数据分布及使用场景详细评估	日期	GMT_CREATE,GMT_MODIFIED
	年月	YEAR,MONTH
	状态标志	PRODUCT_STATUS,ORDER_STATUS,IS_DELETE,VIP_FLAG
	类型	ORDER_TYPE,IMAGE_TYPE,GENDER,CURRENCY_TYPE
	区域	COUNTRY,PROVINCE,CITY
	操作人员	CREATOR,AUDITOR
	数值	LEVEL,AMOUNT,SCORE
	长字符	ADDRESS,COMPANY_NAME,SUMMARY,SUBJECT
不适合建索引的字段	描述备注	DESCRIPTION,REMARK,MEMO,DETAIL
	大字段	FILE_CONTENT,EMAIL_CONTENT