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

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. 同时修改这些数据用户的多少//数据块访问争用，阻塞，闩定，保证读取一致性//整体吞吐量>个体响应时间

查询的过滤条件

高效过滤条件是查询的主要驱动力

//1.通过表的连接来筛选数据
//注意可以在join内部on可以指定条件更好一些
select distinct c.custname from customer cjoin orders oon o.cusid=c.cusidand o.ordered >= 日期换算join orderdetail odon od.ordid = o.ordidjoin articles a....where c.city="Nanjing"....
//2.古老的自然连接方式，大量的笛卡尔积运算
select distinct xxfrom table1, table2, table3where a and b and c and d
***避免在最高层distinct应该是一条基本规则***
***难以判断结果的错误，发现不准确的连接难！***
*使用exists 关联子查询/ in 非关联子查询替代* 
*distinct隐藏了一个排序，现排序再删除重复行*
----------------
select c.custnamefrom customers cwhere c.city = ‘Nanjing'and exists (select  null //这里选择什么不重要from orders o,orderdetail od,articles awhere a.artname = ‘BMW'and a.artid = od.artidand od.ordid = o.ordidand o.custid = c.custid   //与外层相关and o.ordered >= somefunc )//客户在Nanjing市，而且满足Exists存在性测试即在最近六个月买了BMW
------
select custnamefrom customerswhere city = ‘Nanjing'and custid in (select o.custid  //注意custidfrom orders o,orderdetail od,articles awhere a.artname = ‘BMW'and a.artid = od.artidand od.ordid = o.ordidand o.ordered >= somefunc)
//关联子查询中，orders表中custid字段要有索引，而对非关联子查询则不需要，因为要用到的索引是customers的主键索引							
//内层查询不再依赖外层查询，只需要执行一次
---------
注意⚠️
一共有4张表的连接，可以（1+（1+（1+1）））嵌套两层的exist/in	
--------
上述的非关联子查询都是放置于where之下的
对于很多数据库来说，非关联子查询还可以写成from子句的内嵌视图
select custnamefrom customerswhere city = ‘Nanjing'and custid in(select o.custidfrom orders o,(select distinct od.ordidfrom orderdetail od,articles awhere a.artname = ‘BMW'and a.artid = od.artid) xwhere o.ordered >= somefuncand x.ordid = o.ordid)

外层条件好的话，使用exist（过滤掉了大量的外层，验证存在性）

外层条件不好的话，使用in（还有大量的一一与in的表进行比较）

什么时候使用嵌套子查询？？？？

1. 顶层出现distinct
2. 出现多个过滤条件·

找到分辨率最强的条件

• 解决方案不止一种，查询和数据隐含的假设密切相关
• 预先考虑优化器的工作，以确定它能找到所需要的数据
• 平衡二叉树的优化/哈夫曼树的优化

3.用户感受-SQL语句的优化技巧

• 努力使响应时间与返回的记录数成比例
• 分页
• 返回有限条
• 增加条件的效率
• 预测结果集，空结果集的预处理

where子句的比较运算符

a=1000 == a=500+500

a-500=500??全表变量+取值计算

注意当左侧含运算符时，包含了对列的运算，不能使用常规索引，但是可以使用函数索引，通用性差，并且表达式匹配。表达式的左边最好干干净净！

表达式左侧有对日期的表达式/函数，无法使用索引，所以效率极差！

尽量直接比较，忘掉any, all between, not。

大数据量查询

越快剔除不需要的数据，查询的后续阶段必须处理的数据量就越少，查询效率就越高。多层嵌套的exist/in。

union仅仅对非共用的表进行union，将其降级为内嵌视图！！！

滥用子查询

分辨过滤条件，避免顶层出现distinct(隐藏了一个排序，现排序再删除重复行)

不包含聚合函数，不出现多种过滤条件的选择，可以不需要子查询/尽量把选择权交给查询优化器！！！

查询不存在的内容

查找表中间不匹配的行？使用外连接

不要使用NOT IN!!!非关联嵌套子查询，糟糕的性能，建立临时表，临时表用于查找外层查询，索引也不会被使用，会进行全表扫描。

from salary left outer join on (x xx)

将聚合子查询转换为JOIN或内嵌视图

和聚合有关的条件都应该放在having中间，group by之后才会得到分组，进行过滤。所有与聚合无关的条件都要放在where中间。聚合之后，情况复杂了。

关联嵌套子查询？？多次重复执行？？改为非关联子查询？？

非关联子查询？？变为内嵌式图！！！

尽量用连接而不用子查询！！！！

进一步改进？打破范式，冗余地存一个新的状态！！访问++/触发器使得开销++/

比较容易的改进？查询添加新的条件！！时间限定！！

4.日期与字符串

**字符串

• 1.1 遍历字符串

没有循环功能，数据透视表T1，T10，T100对应ID来获取每行一个子符

select substr(e.ename,iter.pos,1) as C
from (select ename from emp where ename = 'KING') e,(select id as pos from t10) iter
where iter.pos <= length(e.ename) 
//
KING 1   //然后进行substring函数
KING 2
KING 3
KING 4
////
select substr(e.ename, iter.pos,length(e.ename))a
from (select ename ...) e,(select id as pos from t10) iter
where iter.pos <= length(e.ename)

• 1.2 嵌入引号

常量中添加引号

“’” ==“ 双中夹单为双

‘"’ == ‘

• 1.3 统计字符出现的次数

字符串总长度length()/len()，减去去掉该字符后字符串的长度replace(str, words, words_new)

有时需要除以查找字符的长度

• 1.4 删除不想要的字符

replace函数

Oracle特有的translate函数replace(translate(ename,‘AEIOU’,‘aaaaa’) , ‘a’, ‘’)

Mysql？replace层层嵌套

• 1.5 分离数字和字符数据

translate+replace，把非数字替换成z/0，再删除

MySQL程序内进行分离

• 1.6 判断含有字母和数字的字符串

MySQL regexp’[0-9a-zA-Z]'正则表达式

Oracle 先找出所有的字母数字字符转换成同一个字符，然后长度不改变比较a*length和转换后的式子。

• 1.7 提取姓名每个单词的首字母

复杂复杂

MySQL很多函数concat, concat_ws, substr, substring_index. 先删除句号，在找到空格位置，将字符串拆分

**数值处理

减少程序和数据库引擎的交互

• 2.1 计算平均值

AVG() 聚合函数 =》 如何处理空值？？？

遇到空值怎么办？null， 存在空值，不计入|||作为0计算。

select avg(sal) from t2 //删去这一行
-----
select avg(coalesce(sal,0)) 
//返回第一个非空值，空值的话置为0
from  t2
group by deptno

• 2.2 计算最大值最小值

max(sal) , min(sal) from xx group by xx1

• 2.3 求和

sum(sal) 不需要管空值

• 2.4 计算行数

count（*） from t1 group by xx_no

count(comn) from amp 会删除空值

• 2.5 累计求和Running Total

Oracle：： sum(sal) over (order by sal,empn排他列) 窗口函数

MySQL：累加窗口函数，标量子查询

• 2.6 计算众数

Max() count(*) group by

• 2.7 计算中位数

Oracle median(sal)

MySQL 返回笛卡尔集

• 2.8 计算百分比

select (sum() / sum(sal)) * 100 as pct

• 2.9 计算平均值时去掉最大值和最小值

where sal not in(（select min()）（select max()）)

Oracle 窗口函数 建立视图sal maxsal minsal 进行一次where过滤

• 2.10 修改累计值

Oracle有窗口函数：；sum(case表达式决定类型)over()

**日期处理

• 3.1 年月日加减法

oracle

• add_mouth(data, -5)
• add_mouth(data, -5*12)

MySQL

+_interval 5 day

-_interval_5_mouth

date_add(this data, interval 5 day)

• 3.2 计算两个日期之间的天数

Oracle 日期直接相减

MySQL datediff(晚的，早的)

• 3.3 计算两个日期之间的工作日天数

数据透视条！ T500，给每一天当作一行返回

• 3.4 计算当前记录和下一条记录之间的日期差

• 3.5 判断闰年

• 3.6 计算一年有多少天

• 3.7 找到当前月份的第一个和最后一个星期一

• 3.8 依据特定时间单位检索数据

• 3.9 识别重叠的日期区间

连接

外键组建相等进行等值连接

叠加行集Union & Union all

列的数据类型必须相同，字段数目匹配，Union all会保留重复项，union会删去重复记录。但是union是在union all上使用distinct，排序操作删除重复项。排序是非关系型操作。

select xx,xx
from emp
where xx
union all //
select '------',null
from t1
union all //
select xx,xx
from emp2
where xx

差（查找只存在于一张表的数据）

select xx,xx
from emp
minis //Oracle 过滤重复值 ：： except //DB2 
select xx,xx
from emp2
-----
MYSQL：：
select [distinct] deptno  //需要考虑排除重复行
from dept 
where deptno not in (select deptno from emp)
空值怎么办？
where deptno in(10,50,null) //不会出现问题
where deptno not in(10,50,null) //出现空值会出现问题
//In /not in 都是or运算 
// false or null => null
//避免在存在空值的情况下使用in/not in，使用关联嵌套子查询exist

从一个表检索另一个表不相关的行（外连接）

select xx
from dept d left outer join emp e

确定两个表是否有相同的数据？

(T1 - T2) union all (T2 - T1) 逻辑上，如果两张表相同的话，应该为null

create view V 
as 
select* from emp where deptno != 10
union all
select * from emp where ename = 'WARD'

（全外连接）从多个表中返回缺失值

FULL OUT JOIN

连接和聚合函数的使用

连接如何不干扰到聚合操作？

3.事务（ACID）

1. 事务是一个原子性质的操作单元，事务里面的对数据库的操作要么都执行，要么都不执行
2. 在事务开始之前和完成之后，数据都必须保持一致状态，必须保证数据库的完整性。也就是说，数据必须符合数据库的规则。
3. 数据库允许多个并发事务同事对数据进行操作，隔离性保证各个事务相互独立，事务处理时的中间状态对其它事务是不可见的，以此防止出现数据不一致状态。可通过事务隔离级别设置：包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）
4. 一个事务处理结束后，其对数据库的修改就是永久性的，即使系统故障也不会丢失。

---

1. 脏读：一个事务中访问到了另外一个事务未提交的数据。并发事务AB同时开始，A包含一个读操作，B一个写操作，AB同时提交结束。A读了B的写操作发生后的数据。
2. 幻读：一个事务读取2次，得到的记录条数不一致。并发事务AB同时开始，A读数据，B该数据，B提交更新，A第二次读数据。A读了B的写操作发生后的数据。[锁住满足条件及相近的记录，关键在于新增和删除]
3. 不可重复读：一个事务读取同一条记录2次，得到的结果不一致：单行记录。[只需要锁住满足条件的记录，关键在于修改]

---

1. 未提交读：排他写锁。会读取到未提交的修改了的数据。更新不能丢失，写会阻塞写，写不会阻塞读。
2. 已提交读RC：瞬间共享读锁，排他写锁。只能读取到已经提交的数据。读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。
3. 可重复读取RR：“共享读锁”和“排他写锁”。在对于数据库中的某个数据，一个事务范围内多次查询却返回了不同的数据值，这是由于在查询间隔，被另一个事务修改并提交了。读取数据的事务将会禁止写事务（但允许读事务），写事务则禁止任何其他事务。
4. 可串行化：提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，不能并发执行。读用读锁，写用写锁，读锁和写锁互斥

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。除了MySQL默认采用RR隔离级别之外，其它几大数据库都是采用RC隔离级别。

隔离界别	脏读	不可重复读	幻读
未提交读RU	可能	可能	可能
已提交读RC	❌	可能	可能
RR可重复读	❌	❌	可能
Serializable可串行化	❌	❌	❌

RR能否防止幻读？可以

MySQL的RR隔离级别也解决了幻读的问题，理论上不行。RR 支持 gap lock(next-key lock)，而RC则没有gap lock。RC 隔离级别，通过 where 条件过滤之后，不符合条件的记录上的行锁，会释放掉(虽然这里破坏了“两阶段加锁原则”)；但是RR隔离级别，即使不符合where条件的记录，也不会是否行锁和gap lock；所以从锁方面来看，RC的并发应该要好于RR

事务处理如何影响开发？

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大。除了MySQL默认采用RR隔离级别之外，其它几大数据库都是采用RC隔离级别。

MVCC多版本并发控制

---

悲观锁；对数据被外界（包括本系统当前的其他事务，以及来自外部系统的事务处理）修改持保守态度，因此，在整个数据处理过程中，将数据处 于锁定状态。往往依靠数据库提供的锁机制。/读取数据时给加锁，其它事务无法修改这些数据。修改删除数据时也要加锁，其它事务无法读取这些数据。

乐观锁：乐观锁，大多是基于数据版本（ Version ）记录机制实现。

4.分区–

数据表的物理实现

读写如何和谐？？？不太能

1. 关系型数据库？读写冲突！冲突的目标
2. 并发用户数很大的系统 紧凑的方式存储VS数据分散存储
3. 没有并发的修改密集型 数据查询要快VS数据更新也要快
4. DBMS所处理的基本单元(page,block)基本不可分割。

分页

页模式，优化查询效率，目录页只想行，但是会有行迁移，多次IO操作。大量的行迁移使得读取和更新都变慢。page/block的链表，索引的目录页

索引当成数据仓库IOT

顺序文件组织----IOT索引组织表，聚簇索引

索引中添加额外字段，提高某个频繁运行的查询的速度。

索引组织表，把数据存储在索引中。

索引组织表（顺序表，灾难：宽表的随机插入，长字符串）vs只有索引的表

1. 开销不一定谁大。
2. 记录是排序的，对索引组织表而言效率惊人
3. 顺序文件的组织，禁止更新

分区

一个大型的表物理存储为多个小型的表，在DBMS引擎下逻辑表现为一张表。

数据的自动分组，屏蔽了底层物理实现，提高了并发性和并行性，增强了架构的可伸缩性

1. 循环分区：为了高效管理。不受数据影响的内部机制，无逻辑。分区为磁盘的存储区域。并发随机的数据查询++
2. 数据驱动分区：依赖于数据逻辑。根据一个或多个字段的值来决定在哪个分区。- 哈希分区（算术运算随机分区负载均衡使用的） - 范围分区 - 列表分区///滑动窗口最近一年？1月表，2月表

分区是可以嵌套的

分区是双刃剑！

所有的插入都会对一个分区进行并发插入，分散的数据理论上避免访问过于集中的问题。但是针对某一个分区插入的并发很不利，争夺同一个数据块，内存开销极大。

对分区的查询，当数据按分区键均匀分布，收益最大

最佳的数据分区

要整体考虑

任何存储方式都会带来复杂性，选错存储方式都会带来大幅度的性能降低！关系模型-有点为灵活性，物理组织可能会丧失灵活性。

1. 测试、测试、测试
2. 设计都是最重要的
3. 任何设计都有时效性！！！

5.树状结构–

数据库模式设计-层次结构

即树状结构，如何把树状结构存储到数据库中？

-历史：层次结构、网状、关系型

-关系理论出现，数据库变成了科学，层次性数据广泛存在

-层次结构复杂度在于访问树的方式

树状结构的保存只要一张表- 深度、所有权、多重父节点

---

1/邻接模型 ：id + pid(parent id)即可，递归的

2/物化路径模型：每个节点和树中的位置作为数据，1.2 代表节点， 1.2.3的父节点为1.2，比起邻接模型含有兄弟节点的排序。（家谱）

3/嵌套集合模型:节点是左右的值，他包含了所有介于左右值之间的节点

都是满足三范式的！

自顶向上的查询

自底向上的查询

聚合来自树的值

6.并发和大数据量

负载增加未必是造成性能的原因，只不过是使得性能问题暴露出来了而已。

设计-反范式模式

1NF一个字段只当作一个值来使用（身份证号码

2NF一条记录可以被唯一组建区分

3NF字段和字段间不存在函数依赖（不能互相推倒，一个字段的全部，不能拆分字段，否则不满足第一范式。）

打破三范式，提高数据库性能，降低表连接的数量，大幅度提升查询效率，降低查询优化器的优化难度。

打破范式是引入可控制的冗余！

规范弱化，降低灵活性；需要更新时手动添加额外的操作，多处更新冗余；加速查询效率，让更新变慢。

逆规范化的方法：

1. 一对一关系。部分参与的1…1对0…1可以进行合并，全参与++
2. 1:*关系。在多的关系中加一列，触发器自动更新。
3. 1:*关系。复制外键列来减少连接
4. 复制多对多关系中的列
5. 引入重复组。电话/地址多个，一组地址-一组电话号码/重复组打包放在主表中，重复组的绝对数量是一致的，该数量是静态的，且不是很大。
6. 创建额外表extra tables，不能做实时计算，但是太好用了。后续维护太难了，而且更新开销大。体虚表？
7. 分区

并发与大数据量处理的基本逻辑

-并发读取数据

1. where中使用有索引的列和无索引的列性能差异很大 5000次/分钟。有索引-快速查询。
2. 排队，数据库引擎能否快速服务，首先是数据库引擎性能（阀值问题）；数据服务的请求复杂度（用户）。快查询会被慢查询拖累。–整体吞吐量

-并发修改数据

加锁

锁的粒度。行级锁(细粒度)，不同进程可以修改同一张表的不同行；表级锁，一次只能修改一张表。选择更细粒度的锁，支持更多并发数量，处理器更多工作，提高资源利用率。

系统资源要为业务过程服务！

1. *不要随便使用表级锁
2. *尽量缩短加锁时间，释放锁的资源。快速会被慢速拖累。delete没有where子句，用truncate(?)———必须频繁提交，可以承担额外的失败，但是每个逻辑单元完成后提交会增加大量开销。
3. 索引维护！
4. 语句性能高，程序性能不一定高
   • 避免SQL语句上的循环（SQL本身就是一个循环！）
   • 减少IO交互次数，一个SQL完成所有工作
   • 跨机器交互次数越少越好
   • 把所有不重要不必要的SQL语句放在逻辑工作单元之外
5. 可伸缩性。行级锁能产生更佳的吞吐量。行级锁大都性能曲线很快达到极限。

资源竞争

插入与竞争。需要串形化处理。竞争主要发生在表和索引。数据库层面的竞，争会引起进程的竞争。当然可以降低锁的粒度，避免对锁的等待，快速提交事务，跟新可能会慢一点。再业务满足情况下，取最小的事务。

1. DBA解决方案。无关业务逻辑，针对数据容器的改变。解决插入问题-事务空间配置，可用列表的配置
2. 架构解决问题/设计问题，在insert上效果不好
   • 分区
   • 逆序索引
   • IOT，基本表和索引融合，降低竞争数量
3. 开发解决方案
   1. 限制最高session数
   2. 不使用系统生成键，连续值只在范围查询中得到使用，ID本身和业务无关时，就不使用连续值，使用随机数来进行。

主键索引是案例的瓶颈！使用生成数字。限制并发连接的session使得系统成为最佳的性能。

-大数据量

操作对数据量增加的敏感程度？

1. 无影响

   1. 主键检索等单一查询，B树索引

2. 线性增长

   1. 返回记录数量和查询毫无关系

   2. SQL操作的数据和最后返回的结果无关（聚合函数）

      增加缺省条件-时间范围，设定上限，这依赖于业务需求。

3. 非线性

   1. 排序性能影响非线性
   2. 间歇性性能降低，看内存大小

-大数据量的一些基本逻辑

综合的考量

需求之外加上限制条件，作为一个普遍的规则

1.数据量增加对性能的预估

1. 隐藏在查询背后对数据量的高敏感性
2. 如max（）对数据量的高敏感度，而直接引起子查询性能缓慢降低，必须使用非关联子查询（in只执行一次），关联嵌套子查询（exist每次执行，指数增加！）。

2.排序的影响

最大问题是排序的数据是否都在内存中，都在内存中的话还好，如果在硬盘中那么很耗费资源。JOIN，order by的顺序需要考虑

1. 字节数量而不是记录数量
2. 排序的总数据量（非关系操作不会被优化，会切一刀，在非关系优化前后分开进行）
3. Join要被延后到最后阶段，对尽量少的数据进行排序//降低中间结果集的大小！
4. 消除关联子查询，外层条件够好才用这个关联子查询，每次被调用，分散调度会导致性能障碍。
5. 减少嵌套的子查询
6. 同时看看能不能将where中的变为from中的视图

3.通过分区提高性能（数据库的设计具有时效性）

1. 小数据量有用的索引可能会越来越低效。条件变差了搜索比例增高。
2. 全表遍历可能会不那么慢，需要检索大量数据时，效率会更高。当符合条件的记录达到一定阀值。
3. 分区中大扫荡数据提高效率。
4. 分区键需要均衡性
5. 范围查询，单边范围查询，对索引分区不会带来很大好处。多个分区/使用索引太慢了。单边变成区间
6. insert插入需要维护索引。update需要维护更新上的索引，但一定含有where语句，面临加锁死锁的可能性，需要存储元数据来回滚。delete包含insert和update所有缺点。把DELETE变成UPDATE！！
   1. 使用标志位
   2. 构建删除的时间戳GOOD
7. 无可奈何？读写分离阶段！数据仓库阶段

高性能数据库开发原则-为性能而设计

关系理论

缩小中间结果集，未必包含全部的字段。

有限数据的查询中，记录的存放顺序对查询有影响。

规范化

真正的风险是数据的不一致性，犯错的概率，手工保持一致性。迭代更新重构的困难。

保护工作！

有值、空值、无值

1. 空值意味着关系模型存在严重的问题
2. 空值对程序逻辑是很危险的

少用Boolean类型

提高信息密度

但是要保证原子性1NF

不能为了数据而数据

子类型SubType

互斥模型-唯一身份

多继承模型- 多个子表

约束应明确说明

数据中的隐含约束是一种不良设计，可能会导致运行错误。

健壮性、可读性、可扩展性

数据的语义属于DBMS！！！

过于灵活的危险

成本急剧上升，性能令人失望

如何处理历史数据

某一时刻的商品价格，商品ID，生效时间，价格

获取当前价格很笨拙！

保存价格有效的终止时间？比较当前和终止时间的关系。不符合业务要求。双十一—价格变化的生效时间，自动改变，价格表中保存未来的价格，并不是由终止时间产生的。

保存两者？终止时间和生效时间必须连续！

当前价格表，历史价格表？维护太难了，未来的价格，定时触发器，开始写入当前价格。双十一-批处理过程。

处理流程

批处理

实时交易-同步处理//必须及早发现问题

数据模型必须考虑数据处理流程

数据集中化

分布式数据系统，复杂度++，健壮性–，管理投入++，

离数据越近，访问速度越快

系统复杂性

数据库的错误很多- 硬件故障(磁盘故障)、错误操作…

数据恢复往往是RD和DBA争论焦点

DBA，即便确保数据库本身工作正常，依然无法了解数据是否正确

RD，在数据库恢复后进行所有的功能性的检查

---

错误的设计是导致灾难性后果的源泉

• 解决设计问题会浪费惊人的精力和智慧

• 性能问题非常普遍

• 打着“改善性能”的旗号进行非规范化处理，常常使性能问题变得更糟

• 成果的数据建模和数据操作应严格遵循基本的设计原则。

---

数据库本身就是一个大尺度问题。

圣诞节零点？时区？？UTC

性能，监控，审计，错误处理–大尺度，大复杂度

解决方案没有对错，只有结果好坏

Q：SQL语句很慢，怎么办？

1.分析问题
如何慢，怎么慢的。
偶尔？一直？过去快现在慢？
2.结合几种优化对应的具体场景
索引：有没有使用？数据量增大了，索引可能起副作用。
SQL语句优化：过滤条件？连接？要不要读写分离？
并发访问？
3.整体评估