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，提升存储和查询效率日常习惯身份证号包括了大量信息，但通常采用一个字段保存：3600034违反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开发