der 2019-03-01
关于优化这个体系博大精深,例如内核优化、系统优化、中间件优化、oracle优化、sql优化、网络优化等等,网上很多思路都比较偏,比较杂,后面小编会花比较多时间去梳理下这方面内容,深入研究下这方面,试着去做个大致优化的体系和思路。
下面主要讲索引方面的内容,基本概念就不做介绍啦。
1. 若没有索引,搜索某个记录时(例如查找name='wish')需要搜索所有的记录,因为不能保证只有一个wish,必须全部搜索一遍
2. 若在name上建立索引,oracle会对全表进行一次搜索,将每条记录的name值哪找升序排列,然后构建索引条目(name和rowid),存储到索引段中,查询name为wish时即可直接查找对应地方
3.创建了索引并不一定就会使用,oracle自动统计表的信息后,决定是否使用索引,表中数据很少时使用全表扫描速度已经很快,没有必要使用索引
1、索引类型
从总的概念上来说,索引分为B树索引(也叫平衡树索引,即就是什么都不写,最常用)和位图索引(多用于数据仓库)。这两种索引在逻辑结构(存储)上完全不同。
1.1、B树索引
其中B树索引 又可以具体分为:
(1)唯一索引:
唯一索引确保在定义索引的列中没有重复值,Oracle 自动在表的主键列上创建唯一索引。
使用CREATE UNIQUE INDEX语句创建唯一索引
语法:create unique index index_name on table_name (column_name);
具体列值: 索引相关列上的值必须唯一,但可以不限制NULL值。
(2)组合索引:
组合索引是在表的多个列上创建的索引,索引中列的顺序是任意的。
如果 SQL 语句的 WHERE 子句中引用了组合索引的所有列或大多数列,则可以提高检索速度
语法:create index index_name on table_name (column_name1,column_name2);
具体列值:该表中的元组由两列共同确定一行,例如班级号 学号 唯一确定一个学生。
(3)反向键索引:
反向键索引反转索引列键值的每个字节,为了实现索引的均匀分配,避免b树不平衡。
通常建立在值是连续增长的列上,使数据均匀地分布在整个索引上
创建索引时使用REVERSE关键字
语法:create index index_name on table_name (column_name) reverse;
具体列值: 适用于某列值前面相同,后几位不同的情况,例如
sno: 1001 1002 1003 1004 1005 1006 1007
索引转化:1001 2001 3001 4001 5001 6001 7001
(4)位图索引:
位图索引适合创建在低基数列上
位图索引不直接存储ROWID,而是存储字节位到ROWID的映射
节省空间占用
如果索引列被经常更新的话,不适合建立位图索引
总体来说,位图索引适合于数据仓库中,不适合OLTP中
语法:create bitmap index index_name on table_name (column_name);
具体列值: 不适用于经常更新的列,适用于条目多但取值类别少的列,例如性别列。
(5)基于函数的索引:
基于一个或多个列上的函数或表达式创建的索引,表达式中不能出现聚合函数
不能在LOB类型的列上创建
创建时必须具有 QUERY REWRITE 权限
语法:create index index_name on table_name (函数(column_name));
具体列值: 不能在LOB类型的列上创建,用户在该列上对该函数有经常性的要求。
例如:用户不知道存储时候姓名是大写还是小写,使用
select * from student where upper(sname)=’TOM’;
2、索引结构
索引的结构是一个倒立的树状结构,其中每个节点的左子树比他的右子树小,索引最终指向表里面的数据与表里面的数据对应。
如上图,前三行是索引的内部构造,第三行与最后一行,这是索引指向表里数据的一个指向。索引是建立在列上的。最后一行是索引建立在表中某列上的值。
根节点块 :如果索引列的值>0时,指向B1这个分支节点块,如果索引列的值>500时,指向B2这个分支节点块,如果索引列的值>1000时,指向B3这个分支节点块。
分支节点块:对于B1来说,再进行细分 如果索引列的值>0且<200时,指向L1这个分支节点块,如果索引列的值>200且<400时,指向L2这个分支节点块,如果索引列的值>400且<500时,指向L3这个分支节点块。
叶子节点块: 对于L1来说,如果数据行的值为0,那就放在R1这个数据行中,如果数据行的值为29,那就放在R2这个数据行中,如果数据行的值为190,那就放在R3这个数据行中,等。
当字段上建有索引时,通常以下情况会使用索引:
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(两个表通过索引字段关联)
主键及外键通常都要有索引,其它需要建索引的字段应满足以下条件:
1、字段出现在查询条件中,并且查询条件可以使用索引;
2、语句执行频率高,一天会有几千次以上;
以下是一些字段是否需要建B-TREE索引的经验分类:
这里还没有去做实验认真测试过,主要与每个表记录的大小及索引字段大小密切相关,参考了其他人对一个普通表的测试数据:
索引对于Insert性能降低56%
索引对于Update性能降低47%
索引对于Delete性能降低29%
因此对于写IO压力比较大的系统,表的索引需要仔细评估必要性,另外索引也会占用一定的存储空间。如果不涉及增删改,可以考虑使用索引。
PS:顺便问一个问题:B+树跟B树索引有什么区别?
关于索引这块讲的还是比较浅显,后面再介绍相关的实验加深下对于索引这一块的印象,感兴趣的朋友可以关注下!