数据库开发技术的课程记录
數據庫開發技術復習「部分」
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，記錄存儲。事務處理型數據庫中 “太多索引≈設計不夠穩定”。
索引的問題
索引不起作用的原因
總結：
我們在使用B+樹索引，而且謂詞中沒有使用索引的最前列。T(x,y)中過濾條件為y相關。
使用SELECT COUNT(*) FROM T，而且T上有索引，但是優化器仍然全表掃描。
對于一個有索引的列作出函數查詢
隱形函數查詢
SLOW此時如果用了索引，實際反而會更慢
WRONG沒有正確的統計信息造成CBO無法做出正確的選
總結：歸根到底，不使用索引的通常愿意就是“不能使用索引，使用索引會返回不正確的結果”，或者“不該使用索引，如果使用了索引就會變得更慢”
4.B+與Hash索引的差異與查詢適用條件
哈希索引：MySQL
Hash(x)，僅支持等值查詢（碰撞率的問題）
位圖索引：Oracle7.3
每行數據用1，0來表示其身份
| 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基于需求和關系理論，查詢優化器基于實現和關系理論。
優化器借助關系理論提供的語義無誤的原始查詢進行有效的等價變換。優化器根據數據庫的實際實現情況，對理論上等價的不同優化方案做出權衡， 產生可能的最優查詢執行方案
SQL執行順序
注意，優化器的局限性，優化的起點為SQL語句，影響最終選擇。有些東西需要程序員手工進行。不能優化的內容包括1.實際環境信息-過濾條件；2.中間結果集（臨時存儲，開銷大）；3.SQL本身有太多操作操作。
優化器只能對關系領域進行優化！
優化器的有效范圍
- 優化器需要借助數據庫中找到的信息
- 能夠進行數學意義上的等價變換
- 優化器考慮整體響應時間
- 優化器改善的是獨立的查詢
2.優化的核心邏輯
優化考慮因素
查詢的過濾條件
高效過濾條件是查詢的主要驅動力
//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的表進行比較）
什么時候使用嵌套子查詢？？？？
找到分辨率最強的條件
- 解決方案不止一種，查詢和數據隱含的假設密切相關
- 預先考慮優化器的工作，以確定它能找到所需要的數據
- 平衡二叉樹的優化/哈夫曼樹的優化
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 4select 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）
隔離級別越高，越能保證數據的完整性和一致性，但是對并發性能的影響也越大。除了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.分區–
數據表的物理實現
讀寫如何和諧？？？不太能
分頁
頁模式，優化查詢效率，目錄頁只想行，但是會有行遷移，多次IO操作。大量的行遷移使得讀取和更新都變慢。page/block的鏈表，索引的目錄頁
索引當成數據倉庫IOT
順序文件組織----IOT索引組織表，聚簇索引
索引中添加額外字段，提高某個頻繁運行的查詢的速度。
索引組織表，把數據存儲在索引中。
索引組織表（順序表，災難：寬表的隨機插入，長字符串）vs只有索引的表
分區
一個大型的表物理存儲為多個小型的表，在DBMS引擎下邏輯表現為一張表。
數據的自動分組，屏蔽了底層物理實現，提高了并發性和并行性，增強了架構的可伸縮性
分區是可以嵌套的
分區是雙刃劍！
所有的插入都會對一個分區進行并發插入，分散的數據理論上避免訪問過于集中的問題。但是針對某一個分區插入的并發很不利，爭奪同一個數據塊，內存開銷極大。
對分區的查詢，當數據按分區鍵均勻分布，收益最大
最佳的數據分區
要整體考慮
任何存儲方式都會帶來復雜性，選錯存儲方式都會帶來大幅度的性能降低！關系模型-有點為靈活性，物理組織可能會喪失靈活性。
5.樹狀結構–
數據庫模式設計-層次結構
即樹狀結構，如何把樹狀結構存儲到數據庫中？
-歷史：層次結構、網狀、關系型
-關系理論出現，數據庫變成了科學，層次性數據廣泛存在
-層次結構復雜度在于訪問樹的方式
樹狀結構的保存只要一張表- 深度、所有權、多重父節點
1/鄰接模型 ：id + pid(parent id)即可，遞歸的
2/物化路徑模型：每個節點和樹中的位置作為數據，1.2 代表節點， 1.2.3的父節點為1.2，比起鄰接模型含有兄弟節點的排序。（家譜）
3/嵌套集合模型:節點是左右的值，他包含了所有介于左右值之間的節點
都是滿足三范式的！
自頂向上的查詢
自底向上的查詢
聚合來自樹的值
6.并發和大數據量
負載增加未必是造成性能的原因，只不過是使得性能問題暴露出來了而已。
設計-反范式模式
1NF一個字段只當作一個值來使用（身份證號碼
2NF一條記錄可以被唯一組建區分
3NF字段和字段間不存在函數依賴（不能互相推倒，一個字段的全部，不能拆分字段，否則不滿足第一范式。）
打破三范式，提高數據庫性能，降低表連接的數量，大幅度提升查詢效率，降低查詢優化器的優化難度。
打破范式是引入可控制的冗余！
規范弱化，降低靈活性；需要更新時手動添加額外的操作，多處更新冗余；加速查詢效率，讓更新變慢。
逆規范化的方法：
并發與大數據量處理的基本邏輯
-并發讀取數據
-并發修改數據
加鎖
鎖的粒度。行級鎖(細粒度)，不同進程可以修改同一張表的不同行；表級鎖，一次只能修改一張表。選擇更細粒度的鎖，支持更多并發數量，處理器更多工作，提高資源利用率。
系統資源要為業務過程服務！
- 避免SQL語句上的循環（SQL本身就是一個循環！）
- 減少IO交互次數，一個SQL完成所有工作
- 跨機器交互次數越少越好
- 把所有不重要不必要的SQL語句放在邏輯工作單元之外
資源競爭
插入與競爭。需要串形化處理。競爭主要發生在表和索引。數據庫層面的競，爭會引起進程的競爭。當然可以降低鎖的粒度，避免對鎖的等待，快速提交事務，跟新可能會慢一點。再業務滿足情況下，取最小的事務。
- 分區
- 逆序索引
- IOT，基本表和索引融合，降低競爭數量
主鍵索引是案例的瓶頸！使用生成數字。限制并發連接的session使得系統成為最佳的性能。
-大數據量
操作對數據量增加的敏感程度？
無影響
線性增長
返回記錄數量和查詢毫無關系
SQL操作的數據和最后返回的結果無關（聚合函數）
增加缺省條件-時間范圍，設定上限，這依賴于業務需求。
非線性
-大數據量的一些基本邏輯
綜合的考量
需求之外加上限制條件，作為一個普遍的規則
1.數據量增加對性能的預估
2.排序的影響
最大問題是排序的數據是否都在內存中，都在內存中的話還好，如果在硬盤中那么很耗費資源。JOIN，order by的順序需要考慮
3.通過分區提高性能（數據庫的設計具有時效性）
高性能數據庫開發原則-為性能而設計
關系理論
縮小中間結果集，未必包含全部的字段。
有限數據的查詢中，記錄的存放順序對查詢有影響。
規范化
真正的風險是數據的不一致性，犯錯的概率，手工保持一致性。迭代更新重構的困難。
保護工作！
有值、空值、無值
少用Boolean類型
提高信息密度
但是要保證原子性1NF
不能為了數據而數據
子類型SubType
互斥模型-唯一身份
多繼承模型- 多個子表
約束應明確說明
數據中的隱含約束是一種不良設計，可能會導致運行錯誤。
健壯性、可讀性、可擴展性
數據的語義屬于DBMS！！！
過于靈活的危險
成本急劇上升，性能令人失望
如何處理歷史數據
某一時刻的商品價格，商品ID，生效時間，價格
獲取當前價格很笨拙！
保存價格有效的終止時間？比較當前和終止時間的關系。不符合業務要求。雙十一—價格變化的生效時間，自動改變，價格表中保存未來的價格，并不是由終止時間產生的。
保存兩者？終止時間和生效時間必須連續！
當前價格表，歷史價格表？維護太難了，未來的價格，定時觸發器，開始寫入當前價格。雙十一-批處理過程。
處理流程
批處理
實時交易-同步處理//必須及早發現問題
數據模型必須考慮數據處理流程
數據集中化
分布式數據系統，復雜度++，健壯性–，管理投入++，
離數據越近，訪問速度越快
系統復雜性
數據庫的錯誤很多- 硬件故障(磁盤故障)、錯誤操作…
數據恢復往往是RD和DBA爭論焦點
DBA，即便確保數據庫本身工作正常，依然無法了解數據是否正確
RD，在數據庫恢復后進行所有的功能性的檢查
錯誤的設計是導致災難性后果的源泉
? 解決設計問題會浪費驚人的精力和智慧
? 性能問題非常普遍
? 打著“改善性能”的旗號進行非規范化處理，常常使性能問題變得更糟
? 成果的數據建模和數據操作應嚴格遵循基本的設計原則。
數據庫本身就是一個大尺度問題。
圣誕節零點？時區？？UTC
性能，監控，審計，錯誤處理–大尺度，大復雜度
解決方案沒有對錯，只有結果好壞
Q：SQL語句很慢，怎么辦？
1.分析問題
如何慢，怎么慢的。
偶爾？一直？過去快現在慢？
2.結合幾種優化對應的具體場景
索引：有沒有使用？數據量增大了，索引可能起副作用。
SQL語句優化：過濾條件？連接？要不要讀寫分離？
并發訪問？
3.整體評估
總結
以上是生活随笔為你收集整理的数据库开发技术的课程记录的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 22条创业军规(读书)
- 下一篇： 3D炫彩动画效果