【数据库】-CSDN博客

数据库索引

1. 索引的作用

 （1）通过唯一性索引，可以保证数据库表中每一行数据的唯一性。（定义主键时服务器自动创建）
 （2）可以加快数据的检索速度。（快速定位）
 （3）实现表与表之间的参照完整性。
 （4）使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间

2. 索引的创建与删除

普通模式创建：CREATE INDEX 索引名 ON 表名(列名1，列名2,...);
	CREATE INDEX name_index ON index_table(name);
	此为在index_table表上的name列上创建一个索引name_index。

	在表上增加: ALTER TABLE 表名ADD INDEX 索引名 (列名1，列名2,...);
	alter table index_table add index name_index(name)

	创建表时指定索引：CREATE TABLE 表名 ( [...], INDEX 索引名 (列名1，列名 2,...) );

索引删除
	1) 修改表的方式删除索引
	ALTER TABLE 表名 DROP INDEX 索引名;
	2) 删除主键索引
	ALTER TABLE 表名 DROP PRIMARY KEY;

索引类型
	1) 主键索引 PRIMARY KEY：它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引。注意：一个表只能有一个主键。
	2) 唯一索引 UNIQUE：唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。可以通过ALTER TABLE table_name ADD UNIQUE (column);
	3) 普通索引 INDEX：这是最基本的索引，它没有任何限制

3. 索引创建的优缺点

创建索引的优势
	1）提高数据的检索速度，降低数据库IO成本：使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度;
	2）降低数据排序的成本，降低CPU消耗：索引之所以查的快，是因为先将数据排好序，若该字段正好需要排序，则真好降低了排序的成本。
	
创建索引的劣势
	1）占用存储空间：索引实际上也是一张表，记录了主键与索引字段，一般以索引文件的形式存储在磁盘上。
	2）降低更新表的速度：表的数据发生了变化，对应的索引也需要一起变更，从而减低的更新速度。否则索引指向的物理数据可能不对，这也是索引失效的原因之一。
	3）优质索引创建难：索引的创建并非一日之功，也并非一直不变。需要频繁根据用户的行为和具体的业务逻辑去创建最佳的索引。

4.使用方法

什么时候适用索引
	1) 字段数值有唯一限制的，比如ID；
	2) 频繁作为 WHERE 查询条件的字段，尤其在数据表大的情况下；
	3) 经常要用于排序(order by)，分组(group by)的列，因为索引已经排好序了;

什么时候不适用索引

	1) 数据更新性能比查询性能要求要高的情况下不要使用索引，因为数据的更新的同时索引也要进行维护和更新（加了索引查询快但更新就会慢）;
	2) 不要盲目的给表建太多索引，因为索引本身的存储也要占用存储空间，一旦更新操作频繁反而降低新性能;
	3) 不要给不经常使用的列建索引，不怎么查询还建索引干嘛;
	4) 不要给高重复值的列建索引，索引本身就是为了提高查询速度，然而数据值高度重复，数据区别性不高，索引起不了效果）（比如说：性别）;
	5) 表数据比较小的；

什么时候索引失效

	1) 隐式转换导致索引失效
		由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效。
 		错误的例子：select * from test where tu_mdn=13333333333;
 		正确的例子：select * from test where tu_mdn='13333333333';

	2) 对索引列进行运算导致索引失效,我所指的对索引列进行运算包括(+，-，*，/，! 等)
	    错误的例子：select * from test where id-1=9;
	    正确的例子：select * from test where id=10;
	    
	3) 使用Oracle内部函数导致索引失效.对于这样情况应当创建基于函数的索引。
		错误的例子：select * from test where round(id)=10; 说明，此时id的索引已 经不起作用了。
		正确的例子：首先建立函数索引，create index test_id_fbi_idx on test(round(id));然后 select * from test where round(id)=10; 这时函数索引起作  用了。
		
	4) like "%_" 百分号在前。

	5) 复合索引未用左列字段。

5.索引优化

	数据库的优化方法有很多种，应用层来说，主要是基于索引的优化。

	1) 避免不必要的类型转换；
		例：表tab1中的列col1是字符型（char)，则以下语句存在类型转换：
		select col1,col2 from tab1 where col1>10，		
		应该写为：  select col1,col2 from tab1 where col1>'10'。

	2) 增加查询的范围限制，使查询在一个完整的范围之内。
		例：以下查询表record 中时间ActionTime小于2001年3月1日的数据：
		select * from record where ActionTime < to_date ('20010301' ,'yyyymm')

		修改如下： select * from record where
		ActionTime < to_date ('20010301' ,'yyyymm')
		and ActionTime > to_date ('20010101' ,'yyyymm')	

	3) 尽量去掉"IN"、"OR"
		含有"IN"、"OR"的Where子句常会使用工作表，使索引失效；如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引。
			select count(*) from stuff where id_no in('0','1')（23秒）
		可以考虑将IN子句分开：
			select count(*) from stuff where id_no='0'
			select count(*) from stuff where id_no='1'
		然后再做一个简单的加法，与原来的SQL语句相比，查询速度更快。

	4) 尽量去掉“<>”，避免全表扫描。
	
	5) 去掉Where子句中的IS NULL和IS NOT NULL
		Where字句中的IS NULL和IS NOT NULL将不会使用索引而是进行全表搜索，因此需要通过改变查询方式，分情况讨论等方法，去掉Where子句中的IS NULL和IS NOT NULL。

	6) 分解复杂查询，用常量代替变量

		对于复杂的Where条件组合，Where中含有多个带索引的字段，考虑用IF语句分情况进行讨论；同时，去掉不必要的外来参数条件，减低复杂度，以便在不同情况下用不同字段上的索引。

	7)  like子句尽量前端匹配
		因为like参数使用的非常频繁，因此如果能够对like子句使用索引，将很高的提高查询的效率。所以避免通配符‘%’在字段的前面，避免全表扫描。

	8) 使用分区索引
		在用分析命令对分区索引进行分析时，每一个分区的数据值的范围信息会放入Oracle的数据字典中。Oracle可以利用这个信息来提取出那些只与SQL查询相关的数据分区。