專場8.5-程序員必須掌握的數據庫原理-葉正盛.pdf

1、程序員必須掌握的數據庫原理葉正盛玖章算術CEOwww.ninedata.cloud讓每個人輕松用好數據和云葉正盛資深數據庫與云計算領域專家，精通云計算、數據庫、企業級軟件研發、互聯網等核心技術曾擔任阿里云數據庫產品管理與解決方案部總經理，阿里云技術架構與產品決策委員會核心成員。帶領阿里云數據庫成功進入 Gartner DBMS 魔力象限全球領導者位置，是中國基礎軟件的重大突破阿里巴巴去 IOE、異地多活、云計算多次技術變革核心成員帶領團隊構建阿里巴巴&螞蟻集團數據庫業務研發支撐體系創立了全球領先的云計算數據傳輸、數據管理、數據庫備份、數據庫自動駕駛服務等多款云計算數據庫產品玖章算術CEO 創始

2、人數據庫簡介數據庫內部結構數據庫設計與SQL優化數據傳輸與安全管理01020304目錄CONTENTS數據庫簡介Introduction of database操縱和管理數據庫的大型軟件，用于建立、使用和維護數據庫提供數據采集、存儲、查詢、分析等功能數據庫管理系統(DBMS)數據庫系統數據庫 VS Excel靈活的開發接口數據安全管理多用戶高性能管理大數據手工記賬Excel管理數據庫管理數據庫系統分類大圖大大小小SQLSQL強強弱弱數據量NoSQL大數據OLTPOLAP數據庫內部結構Internal structure of database數據庫架構單機模式計算節點(RW)CPU+內存存儲節

3、點本地磁盤基礎架構MySQLOracleSQL ServerPostgreSQL分布式(Share nothing)計算節點(RW)CPU+內存存儲節點本地磁盤計算節點(RW)CPU+內存存儲節點本地磁盤MongoDBOceanBaseGreenplum ClickHouseTiDBAWS Redshift主備讀寫分離計算節點(RW)CPU+內存存儲節點本地磁盤計算節點(R)CPU+內存存儲節點本地磁盤Log同步MySQL(Master-Slave)Oracle(Active Data Guard)SQL Server(Always on)log實時傳輸與應用主備狀態管理、讀寫分離分布式(sh

4、are everything)計算節點(RW)CPU+內存存儲節點共享存儲設備/云存儲計算節點(R/RW)CPU+內存共享緩存/鎖中心Oracle RAC阿里云PolarDBAWS AuroraDB2 Parallel Sysplex(大型機)DB2 pureScaleSnowflake(云存儲)AWS Redshift(RA3)共享存儲、RDMA緩存同步、MPP數據分片MPP、分布式事務數據庫引擎內部架構外部接口（通訊協議）外部接口（通訊協議）JDBC、ODBC、OLEDB會話管理會話管理連接、狀態、配置連接、狀態、配置事務事務處理處理ACID鎖計算引擎（查詢引擎）計算引擎（查詢引擎）SQL

5、SQL、存儲過程、執行計劃、執行算子存儲過程、執行計劃、執行算子存儲引擎存儲引擎數據、索引、分區、日志緩存管理緩存管理數據、META、SQL、排序、JOIN分布式分布式管理管理通訊 事務同步 計算存儲安全安全管理管理用戶 權限角色 認證加密 脫敏Extent 3Extent 2存儲引擎（HEAP）塊頭行偏移量(ROW1:?,ROW2:?,)ROW nROW2ROW3ROW1塊校驗信息空閑空間block1block2block3block4block5block6block7block8Block nblock9block10Extent 1DatafileBlock 示例 Oracle(堆表)

6、SQL Server(堆表)MySQL(MyISAM)PostgreSQL存儲引擎（B+TREE）Oracle SQL Server PostgreSQL MySQL MongoDB根節點葉子節點1葉子節點1.1葉子節點1.2葉子節點2葉子節點2.1葉子節點2.2數據頁索引/索引表存儲引擎（COLUMN-STORE）示例：超市貨架管理Row group 3Row group 2Row group 1Column 1Column 2Column 3BlockBlockBlockBlockBlockBlockBlockBlock-type/path/encodings/codec-num valu

7、es-Offset of first data page-Offset of first index page-compressed/uncompressed size-Extra key/values pairsValuesParquet FileHeader ORC PARQUET Greenplum ClickHouse Snowflake MySQL(Infobright)MySQL(HeatWave)存儲引擎（LSM-TREE）BigTable HBase OceanBase LevelDB RocksDB TiDB順序寫、日志存儲、大數據量管理圖片來自X-DB LSM存儲架構常用數

8、據庫存儲引擎類型優勢劣勢應用場景數據庫HEAP基本數據結構結構簡單寫入方便沒有查詢優化OLTP表數據Oracle(堆表)MySQL(ISAM)SQL Server(堆表)PostgreSQLB+TREE小數據量查詢性能高響應時間穩定全表查詢性能差維護復雜OLTP索引OLTP索引表Oracle(索引組織表)MySQL(InnoDB)SQL Server(Cluster表)MongoDBCOLUMN-STORE壓縮存儲效率高按字段查詢性能好單行查詢差實時更新難OLAPClickHouseSnowflakeGreenplumLSM-TREE寫入友好可壓縮存儲查詢效率差（二級索引，范圍查詢）日志寫入大

9、數據量管理HBaseTiDBOceanBase緩存管理數據塊數據緩存（全局）索引塊元數據表、視圖、存儲過程等對象定義日志（REDO LOG）SQL與對象緩存SQL文本與執行計劃會話緩存連接事務狀態運算緩存排序/分組JOIN批處理OLTPOLTP：數據緩存非常重要：數據緩存非常重要OLAPOLAP：運算緩存非常重要：運算緩存非常重要事務處理ReadUncommittedRead CommittedRepeatable ReadSerializableRead讀取到未提交的數據會不會不會不會不可重復讀會會不會不會幻讀會會不會？不會默認數據庫SQL Server Synapse AnalyticsO

10、racleDB2SQL ServerPostgreSQLGreenplumSnowflakeMySQL(InnoDB)AWS REDSHIFTTeradataAtomicityWAL(redo log)硬件Consistency主外鍵約束Isolation快照MVCC鎖DurabilityWAL(redo log)Undo logOLTP數據庫：建議默認采用Read committed4種標準事務隔離級別查詢引擎（查詢語言）查詢語言支持的數據庫描述關系型SQLMySQL、Oracle、SQL Server、PG、Snowflake、Teradata、Hive結構化查詢語言KVget/setRe

11、dis，AWS DynamoDB文檔JSONMongoDB，Google Firestore類SQLCouchbase，MongoDB圖OpenCypherNeo4j、AWS NeptuneTigerGraph（gSQL）Nebula(nQL)與SQL類似GremlinJanusGraph、AWS NeptuneSPARQLAWS Neptune與SQL類似時序數據庫類SQLInfluxDB、TDengineSQL+時序計算功能ODBC(通用)、JDBC(java)、ADO.NET/OLE DB(.net)數據庫設計與SQL優化Database Design and SQL Optimizat

12、ion解決問題（優化SQL）找到問題Active sessionShow processlistSlow logTOP SQL(AWR)Full SQL logSQL執行計劃磁盤IO：數據訪問量，緩存命中率網絡IO：數據網絡傳輸量，傳輸速率/延時鎖：分析SQL鎖類型和粒度CPU：排序、函數計算SQL優化三板斧修改SQL增加索引調整配置數據整理提升硬件性能分布式改造分析問題（分析瓶頸）SQL執行計劃SELECT t1.id,t2.nameFROM t1INNER JOIN t2 ON t1.id=t2.idWHERE t1.name=NineData AND t2.email=abcnineda

13、ta.cloud AND t1.status=deletedORDER BY t2.create_time描述SQL的詳細執行路徑和算法已知：t1.id、t2.id分別是t1和t2表的主鍵t1.name、t1.status、t2.email、t2.create_time 字段上都分別有單個字段的索引這條SQL的執行計劃？先訪問那張表，使用哪些索引？SQL執行計劃訪問方式優先級主鍵查詢1唯一鍵索引查詢2普通索引等值查詢3索引范圍查詢4全表掃描5JOIN算法成本計算公式適合場景Nested Loop JoinM+rows(M)*N有比較好的過濾索引定位Hash Join(*Grace Hash J

14、oin)M+hash(M)+N+rows(N)*hashMatch(M)M和N比較大并且是等值匹配(需要臨時內存)Broadcast Hash JoinM*x+hash(M)*x+N+rows(N)*hashMatch(M)分布式查詢，M比較小,N比較大Shuffle Hash JoinM+hash(M)+N+hash(N)+M+Hash(M)+N+rows(N)*hashMatch(M)分布式查詢，M,N都比較大Merge JoinM+sort(M)+N+sort(N)M和N比較大(需要排序內存)描述SQL的詳細執行路徑和算法表訪問方式常見表JOIN算法MySQL 通過EXPLAIN語法 查

15、看SQL執行計劃M:表1訪問成本；N：表2訪問成本;x：集群節點數據SQL執行計劃（優化器）交通工具速度排名速度當前交通狀態飛機1800公里/小時交通管制，大量航班延誤火車2200公里/小時正常汽車380公里/小時大霧天，高速限行輪船450公里/小時正常走路55公里/小時正常優化器類型描述常見數據庫類型備注RBO基于規則定義數據庫早期實現主鍵、索引等值、索引范圍、全表掃描CBO基于成本計算（磁盤IO,CPU）基于歷史統計數據?(HBO)主流數據庫默認規則最快返回首條數據？最快返回全部數據？Adaptive自適應，運行中動態選擇SQL Server 2017+Oracle 12c+Join、Pa

16、rallel Process示例：北京-杭州，選擇交通方案？優化器：數據庫大腦數據庫設計（范式與反范式設計）3NF2NF1NF范式場景描述與示例違反范式說明信息組合多個狀態標記組合，Bit類型，101011101違反1NF，提升存儲和查詢效率日常習慣身份證號包括了大量信息，但通常采用一個字段保存：360111202212150034違反1NF，提升可讀性計算列單價*數量=金額，訂單總金額違反3NF，提升性能和可讀性冗余字段數據倉庫中事實表大量冗余維表名稱數據違反3NF，提升查詢性能歷史快照歷史數據保留快照時間點詳細信息違反2NF、3NF、BNCF提升可讀性字段分表根據訪問場景把字段拆分的不同的

17、表增加管理復雜度，提升查詢性能常見反范式設計場景5NF4NFBCNF數據庫設計（主鍵）主鍵類型優點缺點適用場景自增IDSEQUENCE數據庫內置功能簡單易用存儲高效連續ID，容易被攻擊不適合分布式適合內部系統全局SEQUENCE簡單易用分布式專用需要部署SEQUENCE服務受網絡性能影響緩存后不一定時間有序分布式系統UUID示例：d23b0fcf-8205-4d12-a173-edcbde904809數據庫內置功能簡單易用分布式場景占用存儲空間大容易產生隨機IOV1有MAC地址泄露風險分布式系統雪花算法（時間+機器+序列號）性能良好，安全復雜，需要由應用程序產生通用組合字段SQL編寫復雜索引表

18、的二級索引效率下降遺留設計淘寶、微信公眾號、微博、滴滴、抖音、美團、BOSS直聘互聯網平臺數據模型匹配算法AI訂閱搜索推薦信息流時空匹配庫存記錄表(電商)會員表會員、買家、粉絲、乘客、求職人服務提供者表賣家、作家、博主、司機、播主、公司服務內容表商品、文章、微博、用車、視頻、崗位服務反饋表評價、點贊、留言訂單表付款表物流/配送表企業軟件-權限模型RBAC(Role Base Access Control)用戶權限角色用戶角色關系用戶權限關系角色權限關系組織與部門權限分組、繼承數據域高級擴展企業軟件-工作流程模型流程模型定義表用戶權限系統工單表工單流轉表待辦工作表/視圖請假、報銷、權限申請、軟件

19、發布、數據庫生產變更開始結束變更工單申請主管審批工單執行數據庫生產變更流程示例DBA審批高風險低風險變更風險自動審核（自動）消息通知表模型基本信息節點配置流轉路徑節點受理人數據傳輸與安全管理Data transmission and security management數據傳輸技術典型產品特點DTS(阿里云)云服務，與阿里云集成緊密NineData混合SaaS服務，無需安裝，功能強大，支持多個云平臺Canal開源軟件讀取寫入轉換結構遷移讀取寫入轉換全量數據復制讀取寫入轉換增量數據復制結構定期/增量/抽樣數據對比源端（OLTP）目標端（數倉/搜索）目標端（異地同步）數據安全管理做好備份、管好密碼、加密傳輸、補丁升級（非云數據庫）硬件故障服務器、硬盤軟件bug機房災難火災、地震數據備份異地容災數據加密數據脫敏流程與規范操作審計安全策略軟硬件故障黑客入侵（數據泄露、勒索）SQL注入、密碼泄露、系統漏洞內部數據泄露、刪庫跑路數據誤操作人為故障謝謝！www.ninedata.cloud讓每個人輕松用好數據和云數據備份數據復制數據對比SQL開發