数据库开发技术的课程记录

数据库开发技术复习「部分」

oracle的无阻塞读。修改才会加锁，行级锁。读写器不会阻塞写入器。提高了并发效率，缺点，无阻塞设计需要保证一次最多只有一个用户读取一行。

where xxx【 FOR UPDATE】关键字

NULL处理的差异，基本差别（并发控制机制）

SQL优化方向：索引，执行计划，SQL语句优化，物理分库分表，数据库表结构，整体结构设计。

1.索引

结构-能做什么-不能做什么-try

1.关系&非关系型数据库

2.B-树，B+树

B-树

分层

B+树

分层，数据全在叶结点，并且叶节点顺序排列。叶节点内包含rowid。

应用场景

• 全键值 where x=123
• 键值范围查询 where 45<x<125
• 键前缀查找 where x LIKE ‘J%’

3.索引一定能提高查询效率吗？不一定！

索引是一种以原子粒度访问数据库的手段而不是为了检索大量数据

索引的代价

• 磁盘空间的开销（存储索引）
• 处理的开销（大幅提高更新开销，索引也要修改）
• 数据库系统处理的开销（更新需要更多的工作）

索引发挥作用的条件

索引适用性依据为检索比例

• 有时候通过索引访问基本表的很少一部分

• 如果要处理表中的多行，可以使用索引而不用表。Index(x,y)//

  【复合索引，本质上索引是按照排名第一的字段进行的索引

索引是查询工作的第一步，读取基本表的数据才是查询的结束。同样的索引，但不同的物理结构，会引起查询的千差万别-磁盘访问的速率，物理I/O，记录存储。事务处理型数据库中 “太多索引≈设计不够稳定”。

索引的问题

1. 函数和类型转换：含有日期函数/隐式类型转换的检索条件无法使用索引。where substr(name, 3, 1) = ‘R’ ❌
2. 索引与外键：建立索引必须有理由。无论是对外键，或是其他字段都是如此
3. 同一个字段，多个索引。如果系统为外键自动增加索引，常常会导致同一字段属于多个索引的情况
4. 系统生成键：系统生产序列号，远好于寻找当前最大值并加1 /用一个专用表保存”下一个值“且加锁更新。但如果插入并发性过高，在主键索引的创建操作上会发生十分严重的资源竞争

索引不起作用的原因

总结：

1. 我们在使用B+树索引，而且谓词中没有使用索引的最前列。T(x,y)中过滤条件为y相关。

2. 使用SELECT COUNT(*) FROM T，而且T上有索引，但是优化器仍然全表扫描。

3. 对于一个有索引的列作出函数查询

4. 隐形函数查询

5. SLOW此时如果用了索引，实际反而会更慢

6. WRONG没有正确的统计信息造成CBO无法做出正确的选

   总结：归根到底，不使用索引的通常愿意就是“不能使用索引，使用索引会返回不正确的结果”，或者“不该使用索引，如果使用了索引就会变得更慢”

4.B+与Hash索引的差异与查询适用条件

哈希索引：MySQL

Hash(x)，仅支持等值查询（碰撞率的问题）

位图索引：Oracle7.3

每行数据用1，0来表示其身份

Value/Row	1	2	3	4
A	1	1	0	1
B	0	0	1	0
C·	0	0	0	0
D	0	0	0	0

适用场景：

• 相异基数低（）
• 大量临时查询的聚合

位图联结索引：Oracle

允许使用另外某个表的列对一个给定表建立索引。实际上，这就是允许对一个索引结构（而不是表本身）中的数据进行逆规范化。

MySQL没有位图索引，1）优化替代索引组合；2）低选择性添加特殊索引.order by xx limit 1000,10 限制用户查看的页数

函数索引：function-based index

对函数F(x)的值构建索引，通过对索引读取x所指向的记录行。

引用场景：

• 不区分大小写upper(name) = ‘KING’
• T、F的巨大差异下的索引
• 有选择的唯一性 Create unique index ax_name on project(xxx内容)

2.SQL***

1.SQL执行顺序

查询优化器

SQL基于需求和关系理论，查询优化器基于实现和关系理论。

优化器借助关系理论提供的语义无误的原始查询进行有效的等价变换。优化器根据数据库的实际实现情况，对理论上等价的不同优化方案做出权衡， 产生可能的最优查询执行方案

1. RBO：基于规则的优化器，操作符权重，权重和最少的
2. CBO基于成本，如索引、物理存储、CPU、内存等因素

SQL执行顺序

1. SQL语句
2. 语义语法检查
3. 解析【消耗内存，等价变化，生成解析树，评估】
4. 执行计划【可执行的二进制代码】
5. 执行引擎
6. 存储引擎Oracle, MylSAM, InnoDB, MEMORY
7. 数据库

注意，优化器的局限性，优化的起点为SQL语句，影响最终选择。有些东西需要程序员手工进行。不能优化的内容包括1.实际环境信息-过滤条件；2.中间结果集（临时存储，开销大）；3.SQL本身有太多操作操作。

优化器只能对关系领域进行优化！

优化器的有效范围

• 优化器需要借助数据库中找到的信息
• 能够进行数学意义上的等价变换
• 优化器考虑整体响应时间
• 优化器改善的是独立的查询

2.优化的核心逻辑

优化考虑因素

1. 获得结果集所需访问的数据总量
2. 定义结果集所需查询条件//多个where子句，过滤条件的效率又高有低，受到其他因素的影响大
3. 结果集的大小//从技术角度来看，查询结果集的大小并不重要，重要的是用户的感觉，该努力使响应时间与返回的记录数成比例//百度谷歌，分页，只显示数字//对输入进行结果集预测
4. 获得结果集所涉及的表的数量//表太多，join操作开销极大，太多的表连接->设计问题//复杂查询和复杂视图，基本的原则是当是视图返回的数据远多于上级查询所需要的时候，就放弃使用该视图
5. 同时修改这些数据用户