一、数据库三大范式
- 第一范式:每个列都不可再拆分;
- 第二范式:在第一范式的基础上,非主键字段完全依赖于主键字段,而不是依赖部分主键字段;
- 第三范式:在第二范式的基础上,非主键字段只依赖于主键字段,不依赖其他非主键字段。
二、索引
定义:建立在表一列或多列的辅助对象,目的是加快访问表的数据。
1.索引的优点
- 创建唯一性索引,可以确保数据的唯一性;
- 大大加快数据检索速度;
- 加速表与表之间的连接;
- 在查询过程中,使用优化隐藏器,提高系统性能。
2.索引的缺点
- 创建和维护索引需要耗费时间,随数据量增加而增加;
- 索引占用物理空间;
- 对表的数据进行增删改时,索引需要动态维护,降低了数据的维护速度。
3.常用索引sql语句
- 建立索引:create index index_name on table_name([column1 [asc|desc])
- 重命名索引:alter index index_name rename to index_new;
- 合并索引:alter index index_name coalesce;
- 重建索引:alter index index_name rebuild;
- 删除索引:drop index index_name;
- 查看索引:select * from all_indexes where table_name='表名';
4.应该建立索引的列
- 经常搜索的列,可加快搜索的速度;
- 主键列,确保数据唯一;
- 经常用于连接的列,加快连接速度;
- 经常排序的列,索引已经排序,可节省排序时间;
- 经常使用在where子句的列,加速判断时间;
5.不应该建立索引的列
- 查询中很少使用的列;
- 单一值的列;
- blob数据类型的列;
6.索引限制
- 索引列使用不等于操作符<>、!=;
- 使用 is null 或is not null;
- where子句中使用函数;
- 比较不匹配的数据类型(不匹配的数据类型之间比较会让Oracle自动限制索引的使用);
7.索引分类
根据索引的数据结构:
-
B-tree索引:最常见的索引类型,适用于所有数据类型,除了LONG、LOB等大对象类型。
-
位图索引:专为某些数据特性而设计,适用于频繁进行相同值查询的情况(如,市场分析、科学计算等)。
-
函数索引:可以是包含SQL函数的索引,用于提高那些在查询中包含函数表达式的查询性能。
-
索引组织表(IOT):特殊的表,其数据存储方式是按照索引的B-tree结构存储的。
根据索引的唯一性:
-
唯一索引:确保索引列的每个值都是唯一的。
-
非唯一索引:允许索引列中有重复的值。
根据索引的作用:
-
聚集索引(聚集因子):表中数据行的物理顺序与索引的排序相同。
- 非聚集索引:不改变表中数据行的物理顺序的索引。
根据索引的创建方式:
-
自动创建的索引:例如,为了实现约束(如主键或唯一约束)而自动创建的索引。
-
手动创建的索引:用户根据查询性能需要手动创建的索引。
三、储存过程
四、游标
五、视图
六、事务和锁
1.事务
定义:是用户定义的一个操作序列
特点:
- 原子性,要么不发生,要么全发生;
- 一致性,数据库是从一个一致性状态变成另一个一致性状态;
- 隔离性,同时发生的多个事务互不干扰
- 持久性,事务一旦提交,对数据库造成的影响是永久的;
结论:事务支持多个保存点,可以在返回最近的保存点后另外再返回其他的保存点,但一旦
保存点返回,保存立即回收。事务提交之后,保存点失效。
隔离级别:
脏读、幻读和不可重复读:
- 脏读:一个事务读取到另外一个事务未提交的动作(解决办法:如果在第一个事务提交前,任何其他事务不可读取其修改过的值,则可以避免该问题。)
- 不可重复读:在同一事务中多次查询,由于其他事务所做的修改和删除,导致每次返回不同的结果集,此时发生非重复读(解决办法:如果只有在修改事务完全提交之后才可以读取数据,则可以避免该问题。)
- 幻读:在同一事务中多次查询,由于其他事务所做的插入,导致每次返回不同的结果集,此时发生幻读(解决办法:如果在操作事务完成数据处理之前,任何其他事务都不可以添加新数据,则可避免该问题。)
2.锁
分类:排它锁(X锁),共享锁(S锁)
排他锁:写锁.这种模式的锁防止资源的共享,用做数据的修改。
共享锁:读锁.该模式锁下的数据只能被读取,不能被修改。
死锁:当两个用户希望持有对方的资源时就会发生死锁.(即两个用户互相等待对方释放资源时,oracle认定为产生了死锁,在这种情况下,将以牺牲一个用户作为代价,另一个用户继续执行,牺牲的用户的事务将回滚。)
七、常用函数
1.字符串函数
NVL(string1, replace_with)
:如果string1
为NULL,则返回replace_with
的值,否则返回string1
的值。INITCAP, LOWER, UPPER
:INITCAP
将字符串第一个字母变为大写,LOWER
将字符串所有字母小写,UPPER
将字符串所有字母大写。SUBSTR(string, start_position, length)
:从string
中截取从start_position
开始的length
个字符。INSTR(X, STR[, START[, N]])
:从X
中查找STR
的位置,可选参数指定开始位置或查找次数。LENGTH(X)
:返回X
的长度。LOWER(X)
:将X
转换为小写。UPPER(X)
:将X
转换为大写。LTRIM(X[, TRIM_STR])
,RTRIM(X[, TRIM_STR])
,TRIM([TRIM_STR FROM]X)
:分别用于去除字符串左边、右边或两边的特定字符,缺省时去除空格。REPLACE(X, old, new)
:在X
中查找old
字符串并替换为new
字符串。
2.数学函数
ROUND(number, decimals)
:将数字四舍五入到指定的小数位数。ABS(value)
,CEIL(value)
,FLOOR(value)
,SIGN(value)
,POWER(value, exponent)
,SQRT(value)
,TRUNC(value, precision)
:分别用于求绝对值、向上取整、向下取整、判断正负、求幂、求平方根、截断数字。
3.转换函数
TO_NUMBER(string)
:将字符串转换为数字。TO_CHAR(value)
:将数值转换为字符串。- TO_DATE(value):将字符串转换为日期类型
4.其他函数
DECODE(field_or_expression, value1, result1 [, value2, result2 ...] [, default])
:类似于其他编程语言中的switch语句,根据字段或表达式的值返回不同的结果。SIGN()
:根据数值是正数、负数还是零返回相应的值。IPAD()
:在ID前追加值,总长度为指定长度。
八、数据库优化
优化流程:
分析数据库压力来源 ——> 调整业务合理性——> 调整数据设计 ——> 调整流程设计 ——> 添加索引 ——> 调整sql语句 ——> 调整物理结构(分库,分表) ——> 调整服务器内存分配 ——> 调整IO ——> 调整内存竞争 ——> 调整操作系统参数
优化原则
(1)目标:减少服务器资源消耗(主要是磁盘IO);
(2)设计方面:合适的索引,索引的双重效应,列的选择性;
(3)编码方面:
利用索引,避免大表FULL TABLE SCAN;
合理使用临时表;
避免写过于复杂的sql,不一定非要一个sql解决问题;
在不影响业务的前提下减小事务的粒度;
1.分析数据库压力来源
2.调整业务合理性
3.调整数据设计
4.添加索引
5.优化sql语句
流程
(1)、定位有问题的语句;
(2)、检查执行计划;
(3)、 检查执行过程中优化器的统计信息;
(4)、分析相关表的记录数、索引情况;
(5)、 改写SQL语句、使用HINT、调整索引、表分析;
(6)、有些SQL语句不具备优化的可能,需要优化处理方式;
细节
(1)查询尽量用确定的列名,少用*号。
select * from bdc_zc_qs;
性能优化后的语句: select bzm_qs,ywh,... from bdc_zc_qs;
(2)尽量少嵌套子查询,这种查询会消耗大量的CPU资源
(3)比较多or运算的查询能使用union all 替换 or运算,建议分成多个查询,用union all联结起来;
(4)多表查询选择最有效率的表名顺序;oracle解析器对表解析从右到左,所以记录少的表放在右边;
(5) 尽量多用commit语句提交事务,可以及时释放资源、解锁、释放日志空间、减少管理花费;减小事务粒度;
(6)尽量避免使用dblink访问数据库,使用dblink会产生数据上的网络交互,极大影响性能;
(7)union all 替换union,union 原理是把两个集合union all起来在进行排序去重复;
(8)尽量不要使用distint,distinct会产生排序;
(9)视图尽量不要使用order by;
(10)避免在索引列上使用函数,计算等;
(11)避免在索引列上使用not条件;
(12)注意查询列字段类型,避免索引列自动转换;
例如:select * from bdc_zc_qs qs where qs.ywh=666,这种情况oracle会转换成to_number(qs.ywh)=666
(13) 用not exists 替代not in;
(14)符号运算使用顺序:=、>=、>、<>;‘’
(15)使用where条件时候把最优的条件放最后,oracle采用自下而上的顺序解析WHERE子句,根据这个原理, 当在where 子句中有多个表联接时,where 子句中排在最后的表应当是返回行数可能最少的表,有过滤条件的子句应放在where 子句中的最后
sql语句执行过程
sql语句处理过程