DBA专题
DBA授课
DBA公开课
DBA训练营三天
01.Mysql基础入门-数据库简介
02.Mysql基础入门-部署与管理体系
03.MySQL主流版本版本特性与部署安装
04.Mysql-基础入门-用户与权限
05 MySQL-SQL基础2
06 SQL高级开发-函数
07 MySQL-SQL高级处理
08 SQL练习 作业
09 数据库高级开发2
10 Mysql基础入门-索引
11 Mysql之InnoDB引擎架构与体系结构
12 Mysql之InnoDB存储引擎
13 Mysql之日志管理
14 Mysql备份,恢复与迁移
15 主从复制的作用及重要性
16 Mysql Binlog Event详解
17 Mysql 主从复制
18 MySQL主从复制延时优化及监控故障处理
19 MySQL主从复制企业级场景解析
20 MySql主从复制搭建
21 MySQL高可用-技术方案选型
22 MySQL高可用-MHA(原理篇)
23 MySQL MHA实验
24 MySQL MGR
25 部署MySQL InnoDB Cluster
26 MySQL Cluster(MGR)
27 MySQL ProxySQL中间件
相信可能就有无限可能
-
+
首页
09 数据库高级开发2
# 1.数据库Schema设计与开发 ## 1.1.schema设计简介 对于数据库而言，在日常开发中我们·主要的关注点有两块，一个是**schema的结构设计**，另一个就是**索引的优化**，这两块是影响我们最终系统结构和性能的关键部分。 - 数据库设计过程简图 - E/R模型和UML模型 - 三级模式-两级映像 - 逻辑模型:schema设计  - 解决软件危机,1968年提出后软件工程概念.数据库系统也称数据库工程 - 对开发:开发代码规范.接口规范,协议规范,流程图规范 - 对DBA:库表结构设计 - 数据库库设计步骤内化版 ```sql 1.需求分析阶段 2.概要设计阶段:E-R图和UML图设计 3.逻辑设计阶段:schema设计,范式设计,一半范式、反范式设计、（还有数据切分,路由,合并）,遵循开发规范 4.后端开发-->自测-->前端开发-->测试介入（功能 性能 回归 基准）-->接口联调(dev test ontest ptest)---> 数据库资源申请-->部署交付:自动化CI/CD--->项目维护 5.维护与优化(数据量级,业务量级QPS(每秒钟数据库有多少次访问),索引优化,数据库架构优化) 6.功能迭代 or 下线 ``` - 数据库库设计步骤标准版 ```sql ## 1.1.规划,设计与需求分析阶段 1.规划: - 系统调查:组织层次图 - 可行性分析 - 数据库总目标 2.需求分析 - 产品经理收集用户需求—→反馈开发 - 系统功能:业务流程图—>系统关联图—>数据流图—>数据字典 ## 1.2.概念设计阶段 - 什么叫概念设计:概念设计反映用户需求的数据库概念结构,硬件独立,软件独立(桥梁) - 步骤: 1.进行数据抽象,设计 局部概念模型 - 从人物事抽取共同特性,对现实世界抽象 2.局部概念模型综合为全局概念模型 - 反映全部用户需求 - 处理局部模式各类对象定义冲突(属性冲突,命名冲突,结构冲突) 3.评审 - 冲突消除后,提交评审 - 产品经理评审+DBA及开发人员评审(开发规范) - 概念设计方法 - 最著名实体联系方法(ER方法)以及面向对象问世后的UML模型 - 目的:得到一个与DBMS无关的概念模型 ## 1.3.逻辑设计阶段 - 目的:概念模型转化为DBMS逻辑结构(库表结构),软件相关 - 步骤 - 概念模型转换为逻辑模型 - 设计外模型 - 设计应用程序与数据库接口 - 评价模型 - 修正模型 ## 1.4.物理设计阶段 - 物理设计概念:对数据库模型选取一个最合适的物理结构。 - 步骤: - 存储记录结构设计:开发规范与索引设计 - 数据存放位置:(SSD,RAID10)? - 存取方法设计:SQL语句的使用,DML和DQL - 完整性和安全型考虑:事务,备份,误删除恢复,账号认证,网络隔离 - 程序设计:后端开发 - 外模式:视图,存储过程(MySQL摒弃掉) - 内模式:物理存储,设计索引 ## 1.5.数据库实现 1.步骤 - DDL定义数据库结构 - 组织数据入库(测试数据) - 编制与调试应用程序 - 数据库运行 ## 1.6.数据库运行与维护 - DBA完成 - 应用变化太大,则设计新得数据库系统,周期结束 ``` 概念集 ```sql （1） 实体（entity）：就是实际应用中要用数据描述的事物，一般是名词。 （2） 字段（fields）：就是一项数据，也就是我们平常所说的“列”。 （3） 记录（record）：一个实体的一个实例所特有的相关数据项的集合，也就是我们平常所说的“行”。 （4） 键（key）：可唯一标识一条记录的一个字段或字段集，有时翻译为“码”。 （5） 主键（primary key）：用于唯一标识一个表中的一条记录的键。每个主键应该具有下列特征： - 唯一 最小 费控 不可更新 （6） 外键（foreign keys）：对连接父表和子表的相关记录的主键字段的复制。 （7） 实体完整性：每个表必须有一个有效的主 键。 （8） 参照完整性：没有不相匹配的外键值。 (9) 候选键：K 完全函数决定R(U).K为R的候选键 (10) 主属性: 包含在一个候选键中的属性，叫主属性,不包含成为非主属性 举例:(学号,班级号，成绩) ，学号+班级号-->成绩,(SNO和CNO)为主键 （学号，学科，姓名）,学号候选键，学号-->学科和姓名确定. 如果没重名,姓名也是候选键。重名则只有学号做主键 ``` ## 1.2.概念设计阶段，E-R - Entity Relationship Model 实体联系模式,1976提出 ### 1.2.1.ER模型的基本元素 > 三要素:实体 属性 联系 > 1.实体 ```sql 实体(Entity): 数据对象,应用中区别的客观存在事物 实体集(Entity Set):同一类实体构成的集合 例子: 教务管理的数据库 实体: 实体集: 一名学生 全部学生 一门课程 全部课程 一位老师 全部老师 ER模型:一般实体和实体集统称实体,Student为一个实体。 Student(SId varchar(10),Sname varchar(10),Sage datetime,Ssex varchar(10)); ER模型实体方块表示 ``` 2.属性 ```sql 属性：实体某一个特征为属性(Attribute) 实体标识符：一个实体中，能够唯一标识实体的属性或者属性集 实体主键：一个实体只有一个标识符. ``` - ER模型中 椭圆标识属性 - 标识符用下划线 - 实体一组属性确定了一个特定实体  - 标识符:实体的组合,比如电影名+年份确定唯一一个特定实体  - 如何区别实体和属性 - 读者和书籍是实体 - 红色下划线属性  3.联系 ```sql - 联系(Realtionship)：表示一个或者多个实体之间的关联关系 - 联系集(Realtionship set):同一类联系构成的集合 - ER图中统称为联系 - 联系的元数:一个联系涉及到的实体集个数,称为该联系的元数或度数 - 一元联系(递归关系): 同一个实体集内部实体之间的联系 - 二元联系:两个不同的实体集实体之间的联系 - 三元联系：三个不同的实体集之间的联系 ... - 联系类型: - 二元联系 类型:1:1,1:n,m:n ``` **(1)联系元数** - 举例:学生和课程联系 - 菱形框表示联系,联系是动词 - 二元联系  - 举例2:三元联系 - 课程,教师,参考书被讲授联系 - 仓库体系  - 一元联系:  （**2)联系类型:** - 1:1联系 不常见  - 1:n - 联系也有属性:描述联系特征  - m:n  ### 1.2.2.ER模型概念设计步骤 - ER模型概念设计步骤: 1. 设计局部ER模型 ```sql - 确定局部结构范围: - 确定实体:避免冗余 - 确定属性:属性不可分解,实体与属性关系 1:n，不同实体类型属性之间无直接关联关系 - 确定实体间联系:确定联系,联系类型，防止冗余 ``` 1. 局部ER模型综合成一个全局ER模型 2. 对全局ER模型进行优化,得到最终ER模型,即概念模型 ```sql DBA:高级DBA - 低:只会维护业务(只维护数据库或一小部分)-交付专家 - 中:维护业务+会运维开发(局部ER模型) - 高:维护业务+设计业务+设计平台(全部ER模型) ``` 举例:图书管理系统 ### 举例:图书管理系统  - E-R图 - BID和RID实体标识符 - 联系类型m:n  - 另一种ER图  ### 作业: 企业工程管理数据库 E-R图  ### 1.2.4.ER模型转换为关系模型 **1.ER图转换关系模式集规则** ```sql #步骤1: 实体类型转换:每个实体转换为一个关系模式,实体属性为关系模式的属性.实体标识符就是关系模式的主键. #步骤2: 联系转换:不同情况 不同处理 1:n,m:n 1. 1:1：实体关系模式的属性可以互相加入 2. 1:n：n端关系模式加入1端实体的键和属性 3. m:n: 联系类型转换为关系模式,属性为 两端实体类型键+联系类型属性。 ``` - 举例:二元联系转换  ```sql #1..转换实体 系:(系编号,系名,电话) #标识符 编号 教师:(教工号,姓名，性别，职称) #标识符 教工号 课程：（课程号，课程名，学分） #标识符 课程号 #2.联系转换 ##系和教师+主管 1:1 一方加入另一方主键作为外键(开发规范不用) 系:(系编号,系名,电话,##教工号##) 教师:(教工号,姓名，性别，职称) ##系聘用教师:1:n,系开设课程:1:n,多方关系模式加入一方属性,联系有属性一样加入 系:(系编号,系名,电话,##教工号##) 教师:(教工号,姓名，性别，职称，##系编号##，##聘期##) 课程：（课程号，课程名，学分,##系编号##） ##教师任教课程: m:n, 联系转换成关系(表),属性(column)为实体主键和和联系的属性 系:(系编号,系名,电话,##教工号##) 教师:(教工号,姓名，性别，职称，##系编号##，##聘期##) 课程：（课程号，课程名，学分,##系编号##） ##任教(教工号,课程号,教材) #教工号,课程号联合主键和外键 ``` - 三元关系转换:课间练习(10分钟) ```sql #实体转化 仓库：(仓库号,名，地址) #顺丰今天进了多少商品 商店:（商店号，名） #好未来今天进了多少 商品: (商品号，商品名) #辣条进了多少 #关系转化 进货(仓库号,商店号,商品号，数量，日期) ```  **2.采用 ER模型的逻辑设计步骤** ```sql 1.导出初始关系模式集 2.规范化处理 是否满足范式 3.模式评价和修正 4.设计子模式:物理存储,设计索引 ``` ### 小结 1. ER模型基本元素:实体 联系 属性 2. 联系设计 3. ER模型的概念设计步骤 4. ER模型转换关系模型: 实体转换 联系转换 # 1.3.UML设计 - 数据建模:一个业务应用，在数据库中表示数据关系 - 方法 - 概念设计模型 - E-R模型 - UML(统一建模语言) - 关系模型设计理论 ### 1.3.1.UML - 统一建模语言,面向对象和数据库建模 - 与E/R模型相似,没有多元联系(都是二元联系)  (1)UML 类 - 类型,属性,方法  - UML设计数据库删除方法(后期设计CMDB系统 UML比较好用)  (2）关联 - 概念:两个类间对象的联系成为关联 - 表示 - 两个类之间用直线(箭头可选)连接 - 连接名字写在直线下方 - 直线连接断点处表上关联类型 - 例子1 - 每个学生最多在5个校园申请课程 - 每个校园容纳学生数为1W-2W之间 - m..* 无上限,0..n 无下限,0..*无限制  - 例子2:Movie数据库设计 E/R与UML  **UML图** - 一个公司排0..*个电影 - 一个电影属于一个公司() - 一个明星排多个电影，一个电影有多个明星  E/R与UML图对比 - 实心0..1 - 空心1..1  (3)关联类 - 关联类：关联也可以有属性，与E/R中联系的属性类似 - 例子:明星出演电影需要片酬  (4)子类 ```sql - UML类都可以包含下级子类，空三角指向父类 - 主键来自父类 - 子类继承父类属性 - 子类自己拥有属性与其他类关联 - 4种类:完全，部分，分散，重叠 ```  (5)UML转换关系模型  - ER  - UML  - 如何转换UML为关系模型(课间练习) - 类的转换 - 关系的转换 - 1:n,多对多转换关系  ## 1.4.关系数据库设计理论-逻辑设计 - 关系数据库逻辑设计 - 构造一个数据模式(几个关系，几个属性) - 逻辑设计工具- 规范化理论  ### 1.4.1.数据依赖 ```sql - 关系:描述实体与属性,实体键联系 - 关系模式: student(snm,sname,sex,age,dept) - 关系数据库:基于关系模式数据库，利用关系描述现实世界 #关系模式五元素 R(U,D,DOM,F)，R(U,F)三元组 R: 关系名 U：组成该关系的属性集合 D：属性U中属性的域 DOM: 属性向域的映像集合 F: 属性间数据依赖关系集合 ``` 1.什么叫数据依赖 - 完整性约束：属性取值范围,比如学生成绩在0-100之间 - 概念:属性值间的相互关联: - 关系中属性间值是否相等 - 现实世界属性间相互联系的抽象 - 类型 - 函数依赖 - 多值依赖 - 连接依赖 - 例子：  ```sql 关系属性集合: U = {Sno,Sdept,Mname,Cname,Grade} #如何评价这个关系模式 ##已知条件 1.一个系多个学生，一个学生一个系,Sno-->Sdept 2.一个系一个主任:Sdept->Mname 3.一个学生选多门课，课有多个学生,选修，每个学生每门课程一个成绩 ##上述关系模式缺点 1.数据冗余:Sno,出现一个 Mname出现一次 2.更新异常（维护数据完整性代价太大）:王主任调走,大量数据更新 3.插入异常（该插的插入不进去）:有一个新学生，刚入学,没有成绩没选课,则Sdept和Mname，Cname都有问题 4.删除异常（不该删除的数据删除）:学生毕业，需要删除数据，则系都没有了 本质:关系模式之间不合理的依赖关系 ``` - 解决:分解不合理的关系模式,利用函数依赖  ```sql F ={Sno-->Sdept, Sdept-->Mname, (Sno,Cname)-->Grade} #利用规范化理论 - 改造关系模式 - 分解关系模式,消除不合适数据依赖 - 解决上述异常问题 ``` - 函数依赖 ```sql 概念:设R(U)是一个属性集U上的关系模式,X和Y是U的子集。X-->Y，X为函数依赖的决定属性集。 Student(Sno,Sname,Ssex,Sage,Sdept) #所有函数依赖，就是一个值能够推出另一个值 sno-->sex sno-->sage sno-->sdept sno-->sname sname-->sex sname-->sage sname-->sdept ``` - 码 ```sql K为关系模式R(U,F)中的属性或属性集合，f(K)-->U(K完全决定U),K为R的一个候选码(主键候选) #流水表 订单号 候选码(主键)-->事件，人，消费金额，消费类型. ``` ### 1.4.2.范式-关系模式规范化 - 概念:关系模式中关系满足一定要求,不同程度对应不同范式 - 范式是符合一种级别的关系模式集合 - 范式种类：1NF,2NF,3NF,BCNF,4NF,5NF （依次包含满足） **1NF** ```sql 定义:关系模式R每个属性不可分,最基本要求,但是满足第一范式的关系模式不一定是好关系模式 #例子 SLC(Sno,Sdept,Sloc,Cno,Grade)，学号，所在系，宿舍楼，课程号，成绩。 #规定 一个学生属于一个系， 每个系的学生住在同一个宿舍楼 一个学生学得每一门课程都有唯一成绩 #函数依赖 Sno-->Sdept Sdept-->Sloc （Sno,Cno）-->Grade,Sloc,Sdept,Cno #部分函数依赖 #选出候选码 码是 （Sno,Cno） #结论 SLC满足第一范式 Sdept和sloc部分函数依赖与码(Sno,Cno) 部分函数依赖还是存在上述问题 ```  - 如何解决：分解 ```sql SLC(Sno,Sdept,Sloc,Cno,Grade) SC(Sno,Cno,Grade) CL(Sno,Sdept,Sloc) ```  **2NF** ```sql 定义:关系模式R属于1NF，每一个非主属性,完全函数依赖R的码,为2NF 例子：（Sno，Cno）-->Grade Sno-->Sdept-->Sloc 完全函数依赖 问题:仍不能消除各类异常情况和数据冗余 ```  问题分析: ```sql Sloc传递函数依赖Sno，存在非主属性对码的传递函数依赖 ```  - 解决 ```sql 投影分解法，SL分成两个关系模式 SD(Snom,Sdept) Dl(Sno,Sloc) #这就是第三范式 ``` **3NF** ```sql 3NF:在1NF，2NF基础上，不存在非主属性对码的传递函数依赖。 概念:3NF基础上,R的每一个非主属性 既 不部分函数依赖与候选码 也 不传递函数依赖于候选码 特点:一定程度解决2NF问题，有些情况则不能消除 问题:主属性依赖其他属性 ``` **BCNF** ```sql 概念:BC是修正的第三范式 定义:关系模式 R属于1NF，如果对R的每个函数依赖X-->Y，若Y不属于X，则X必含有候选码，则为BCNF 每一个决定属性集都包含候选码,R一定是BCNF 性质: 1.所有 非主属性 都完全函数依赖与每个候选码 2.所有 主属性 都完全函数依赖与每个不包含他的候选码 3.没有任何属性 完全函数依赖于 非码 的任何一组属性 3NF和BCNF：R属于3NF,且R就一个候选码，则R为BCNF #举例 关系模式STC（S,T,C） S学生 T教师 C课程 ##函数依赖： 每个教师教一门课,每门课若干教师教 T-->C 某一学生选定某门课,固定一个教师 (S,C) -->T 某个学生选修某个教师课程-->课名称 (S,T) -->C (S,C) (S,T)都可以作为候选码,STC是第三范式，SCT都是主属性 问题:T-->C，主属性C依赖与码 #解决,分解 SC(S,C) TC(T,C) ``` ### 1.4.2.关系模式规范化的步骤 - 规范化理论:数据库**逻辑设计**的工具 - 一个关系,数据项(属性)不可分,最基本范式 - 规范化分6个级别,即6个范式 - 规范化过低,DML异常；通过规范化解决—>转化为高级范式 - 低级范式通过模式分解—》高级范式，过程叫**关系模式的规范化** ```sql #关系模式步骤 1NF:最基本 2NF:消除非主属性对码的部分函数依赖（则是要求数据库中的每条记录都要有其对应的主键id存在，这样要求的主要目的是为了能够满足上层业务要求唯一标识每条记录的需求） 3NF：消除非主属性对码的传递函数依赖(将不同的业务单元属性字段拆分到不同的表里，然后通过关系表进行关联。) BCNF：消除主属性对码的部分和传递函数依赖(函数依赖最高级别) 4NF: 消除非平凡且非函数依赖的多值依赖 5NF：消除不是由候选码包含的连接依赖 ``` ### 1.4.3.反范式设计 - 反范式的过程就是通过冗余数据来提高查询性能，但冗余数据会牺牲数据一致性 ```sql 优点 所有的数据都在同一张表中，可以减少表关联 更好进行索引优化 缺点 存在大量冗余数据 数据维护成本更高（删除异常，插入异常，更新异常） 最总决策 无论是范式还是反范式 以业务响应时间和代价 ``` 1.关于数据冗余 ```sql #举例,会员系统. 四张表合并成三张表或者更好 减少范式查询 ``` 2.去关联化 ```sql 问题：范式设计业务中会有join操作,两个大表数据量级大的时候资源消耗也大 解决：通过字段合并和数据冗余将join消除 ``` 3.去一致性约束 ```sql 问题：外键和唯一性约束:每次Mysql都会去检查，浪费资源 方法：删除外键,约束校验放在业务代码中去做，让MySQL减负 ``` 4.去SQL化 ```sql 0.客户端：需要 1.网络安全：去掉用户权限认证 2.数据库减负：去掉SQL，逻辑放在业务API层做 3.访问控制：一个开发一个业务，去掉 4.存储引擎：保留索引提高查询效率，留个主键索引就好了 5.事务:查询-->不用事务保证 6.锁:解决并发控制保留 精简后 ==》NoSQL：关系型数据库做了退化，回归基础本质 ``` # 2.MySQL临时表与表分区 ## 2.1.MySQL有哪几种表 - 从事务安全分类 - 事务安全型(有事务保证,不会丢失数据,事务保证成批的 SQL 语句要么全部执行，要么全部不执行) ```sql Berkeley DB（支持事务,已经废除） InnoDB（默认存储引擎的数据表类型） ``` - - 非事务安全型(没有事务安全保证数据完整性,异常情况会丢失数据) ```sql MEMORY(HEAP): 内存表,使用散列索引,存储速度非常快,宕机数据丢失,长应用与临时表 MyISAM: 执行大量SELECT语句性能较好，但是不支持事务 MERGE: MyISAM表集合 archive: 压缩引擎,只允许插入和查询，不允许修改和删除,插入快,查询快,占用空间小,数据归档场景.支持压缩，它的引擎使用zlib无损数据压缩算法，压缩率非常好。存储空间大概是innodb的15%.支持分区 ``` ## 2.2.MySQL-内部临时表 ### 2.2.1.临时表定义及触发操作 定义 ```sql 定义:SQL读取数据无法直接得到结果,需要额外内存保存中间结果.这个额外内存就是 临时表 三种处理类型: TempTable,MEMORY 存储引擎,InnoDB存储引擎存储在磁盘上(物理表) 例子:group by,保存聚合函数结果,select a,count(*) from table_b group by a; 限制:内存超过设置阈值,存储在磁盘上,防止内存溢出.OOM ``` 触发操作 ```sql 哪些操作引擎内部临时表: explain SQL 有 (1)union语句 (2)视图:使用TEMPTABLE,union,聚合 (3)派生表:FORM后的独立子查询,查询后清楚 SELECT * FROM (SELECT * FROM table_a WHERE columa_a='广州') table_x (4)子查询,半连接查询 (5)ORDER BY + DISTINCT (6) INSERT...SELECT 保存SELECT用临时表 (7) GROUP_CONCAT OR COUNT(DISTINCT) (8)窗口函数 怎么查看 show global status like '%tmp_%tables% ``` ### 2.2.2.临时表不同版本特性 **MySQL5.6** ```sql 1.临时表超过内存限制,在临时目录创建 2.临时表有自己的表空间,SQL执行删除临时表和文件 3.禁用innodb_file_per_table,则在InnoDB 共享表空间（ibdata1）中创建. ``` **MySQL5.7** ```sql 1.改进:讲临时表的tablespace从InnoDB分离,叫共享临时表空间 2.优势:减少每个临时表创建和删除的成本,因为都在一个表空间 单独划分表空间,可以随时重置大小,避免5.6集中ibdata1 释放难问题; 3.表现:MySQL启动在 datadir 创建 ibtmp1文件,默认无限扩展.（比如20MB临时表,ibtmp1为20MB） 执行完删除临时表，释放空间用于新临时表(ibtmp1文件大小不变,重启MySQL服务器真正回收) 4.相关参数： (1)tmp_table_size&max_heap_table_size，临时表是存在内存中的，使用 MEMORY 存储引擎，超过两者较小的值，转化为磁盘临时表； (2)internal_tmp_disk_storage_engine：内部临时表转化为磁盘临时表，指定了磁盘临时表的存储引擎，默认是 INNODB，还可以设置为 MYISAM； (3)innodb_temp_data_file_path:指定了临时表空间的位置和大小，默认值为 ibtmp1:12M:autoextend 5.遗留问题 (1)VARCHAR的变长存储。varchar(255)变成char(255)浪费空间 (2)大对象的默认磁盘存储.比如 TEXT，BLOB,JSON等，直接转化为InnoDB磁盘存储。 (3) ibtmp1需要重启mysql实例才能释放 ``` **MySQL8.0** ```sql 1.新增参数： internal_tmp_mem_storage_engine：指定内部临时表 存储引擎,默认TempTable引擎 temptable_max_ram:定义TempTable存储引擎开始在磁盘上存储数据之前可以占用的最大内存量，默认值1G。超过则存储在#ibtemp1.性能字典表 memory_summary_global_by_event_name 存储 mysql> SELECT * FROM performance_schema. memory_summary_global_by_event_name WHERE event_name like '%temptable%'\G temptable_use_mmap:超过temptable_max_ram后,TempTable为内存内部临时表分配空间,作为映射临时文件.禁用则用InnoDB引擎内部临时表,8.0.16弃用. temptable_max_mmap:设置0禁止,其他为开始将数据存储到磁盘上的 InnoDB 内部临时表之前，被允许从内存映射的临时文件分配的最大内存量（以字节为单位)官方建议设置为0。 2.新增引擎：门实现了一个临时表的引擎 TempTable,解决varchar变长和大对象存储. - 可以支持变长类型，例如 varchar(100)的数据”abcd”应该只占用4个字节而非100个字节，节省内存； - 支持二进制大对象，例如 blob, text 等。如果使用 MEMORY 引擎，这样的内部临时表会直接使用磁盘临时表，这个是为了提升性能。 - 问题：8.0.20,26,28都是有问题的 执行sql报错 - 建议设置:internal_tmp_mem_storage_engine=MEMORY 3.临时表空间变化 8.0 - 会话级别临时表空间: SQL 产生的内部临时表将存储在会话临时表空间 - 全局级别临时表空间: 与5.7一样 - 参数:innodb_temp_tablespaces_dir ：定义了创建会话临时表空间的位置，默认位置是数据目录中 #innodb_temp的目录 shell> ls datadir/#innodb_temp temp_10.ibt temp_2.ibt temp_4.ibt temp_6.ibt temp_8.ibt temp_1.ibt temp_3.ibt temp_5.ibt temp_7.ibt temp_9.ibt - 会话临时表空间:10个临时表空间的池,1个session两个表空间(1个用于用户创建临时表,一个用于优化器创建临时表)，会话断开,清除空间释放回池 ``` **查看用户创建的临时表：** 任何 session 都可以执行下面的语句； 查看用户创建的当前 active 的临时表(不提供 optimizer 使用的内部 InnoDB 临时表信息) ```sql SELECT * FROM INFORMATION_SCHEMA.INNODB_TEMP_TABLE_INFO\G ``` **注意：** 通过 INNODB_TEMP_TABLE_INFO 查看到的临时表的 NAME 是#sql开头的名字， 另外 information_schema.tables 表中是不会记录临时表的信息的。 **用户创建的临时表的回收：** 1. 会话断开，自动回收用户创建的临时表； 2. 可以通过 drop table 删除用户创建的临时表，例如：drop table t1; **用户创建的临时表的的其他信息&参数(8.0)：** ```sql select * from information_schema.innodb_session_temp_tablespaces ; ``` 查询全局临时表空间的数据文件大小： ```sql SELECT FILE_NAME, TABLESPACE_NAME, ENGINE, INITIAL_SIZE, TOTAL_EXTENTS*EXTENT_SIZE AS TotalSizeBytes, DATA_FREE, MAXIMUM_SIZE FROM INFORMATION_SCHEMA.FILES WHERE TABLESPACE_NAME = 'innodb_temporary'\G ``` 监控 TempTable 从内存和磁盘上分配的空间： ```sql select * from performance_schema.memory_summary_global_by_event_name \ where event_name in('memory/temptable/physical_ram','memory/temptable/physical_disk') \G ``` 具体的字段含义见：Section 27.12.20.10, “Memory Summary Tables”. ## 2.3 总结: 普通的磁盘临时表|临时文件(一般需要较小的空间)： 临时表|临时文件的一般所需的空间较小，会优先存放于内存中，若超过一定的大小，则会转换为磁盘临时表|临时文件； 磁盘临时表默认为 InnoDB 引擎，其存放在临时表空间中，由 innodb_temp_tablespaces_dir 定义表空间的存放目录，表空间文件类似：temp_[1-20].ibt ；MySQL 未定义 InnoDB 临时表空间的最大使用上限； 当临时表|临时文件使用完毕后，会自动回收临时表空间文件的大小； innodb_temp_data_file_path 定义了用户创建的临时表使用的回滚段的存储文件的相对路径、名字、大小和属性，该文件是全局临时表空间(ibtmp1)，该文件可以设置文件最大使用大小； **例外项(一般需要较大的空间)：** load data local 语句，客户端读取文件并将其内容发送到服务器，服务器将其存储在 tmpdir 参数指定的路径中； 在 replica 中，回放 load data 语句时，需要将从 relay log 中解析出来的数据存储在 slave_load_tmpdir(replica_load_tmpdir) 指定的目录中，该参数默认和 tmpdir 参数指定的路径相同； 需要 rebuild table 的在线 alter table 需要使用 innodb_tmpdir 存放排序磁盘排序文件，如果 innodb_tmpdir 未指定，则使用 tmpdir 的值； 若用户判断产生的临时表|临时文件一定会转换为磁盘临时表|临时文件，那么可以设置 set session big_tables=1;让产生的临时表|临时文件直接存放在磁盘上； **猜测其设计：** 对于需要较小空间的临时表|临时文件，MySQL 要么将其存储于内存，要么放在统一的磁盘临时表空间中，用完即释放； 对于需要较大空间的临时表|临时文件，可以通过设置参数，将其存储于单独的目录|挂载点；例如：load local data 语句或需要重建表的在线 alter table 语句，都有对应的参数设置其存放临时表|临时文件的路径； 当前只有 innodb_temp_data_file_path 参数可以限制 用户创建的临时表使用的回滚段的存储文件的大小，无其他参数可以限制临时表|临时文件可使用的磁盘空间； - 示例 ```sql CREATE temporary TABLE IF NOT EXISTS `test1`( `id` INT UNSIGNED AUTO_INCREMENT, `name` VARCHAR(100) NOT NULL, `email` VARCHAR(40) NOT NULL, `mobile` BIGINT(11) NOT NULL, `submission_date` DATETIME, PRIMARY KEY ( `id` ) )ENGINE=InnoDB DEFAULT CHARSET=utf8; DELIMITER $$ CREATE PROCEDURE proc() BEGIN DECLARE num INT; SET num = 1; truncate table test1; WHILE num < 1000000 DO INSERT INTO `test1` ( `id`, `name`, `email`, `mobile`, `submission_date` ) VALUES ( num, concat(num,'tester'), concat(num,'tester@qq.com'), 15000000000 + num, now() ); SET num = num + 1; END WHILE; END $$ DELIMITER ; call proc(); drop procedure proc; ``` ## 2.4.MySQL-表分区 ### 2.4.1.表分区简介 - 概念:数据，逻辑上还在一个表中，但物理上，可以根据一定的规则放在不同的文件中。这是MySQL5.1之后支持的功能，**业务代码**无需改动. - 缺点 ```sql 大数据量，高并发量的业务难以接受的： （1）如果SQL不走分区键，很容易出现全表锁； （2）在分区表实施关联查询，性能差； （3）分库分表，自己掌控业务场景与访问模式，可控；分区表，工程师写了一个SQL，自己无法确定MySQL是怎么玩的，不可控；画外音：类似于，不要把业务逻辑实现在存储过程，用户自定义函数，触发器里，而要实现在业务代码里一样。 （4）比起同级数量表性能差5-10% ``` - 限制 ```sql 1）分区键必须包含在表的所有主键、唯一键中。 2）MYSQL只能在使用分区函数的列本身进行比较时才能过滤分区，而不能根据表达式的值去过滤分区，即使这个表达式就是分区函数也不行。 3）ALTER TABLE … ORDER BY： 对分区表运行的ALTER TABLE … ORDER BY列语句只会导致每个分区中的行排序。 4）全文索引。 分区表不支持全文索引，即使是使用InnoDB或MyISAM存储引擎的分区表。 5）分区表无法使用外键约束。 6）Spatial columns： 具有空间数据类型（如POINT或GEOMETRY）的列不能在分区表中使用。 7）临时表： 临时表不能分区。 8）subpartition问题： subpartition必须使用HASH或KEY分区。 只有RANGE和LIST分区可能被分区; HASH和KEY分区不能被子分区。 ``` - 类型 ```sql RANGE分区：最为常用，基于属于一个给定连续区间的列值，把多行分配给分区。最常见的是基于时间字段。 LIST分区：LIST分区和RANGE分区类似，区别在于LIST是枚举值列表的集合，RANGE是连续的区间值的集合。 HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。 KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。 ``` - 应用 ```sql #创建分区表 CREATE TABLE `test_11` ( `id` int(11) NOT NULL, `t` date NOT NULL, PRIMARY KEY (`id`,`t`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 PARTITION BY RANGE (to_days(t)) (PARTITION p20170801 VALUES LESS THAN (736907) ENGINE = InnoDB, PARTITION p20170901 VALUES LESS THAN (736938) ENGINE = InnoDB, PARTITION pmax VALUES LESS THAN maxvalue ENGINE = InnoDB); #插入数据 insert into test_11 values (1,"20170722"),(2,"20170822"),(3,"20170823"),(4,"20170824"); #查询分区表信息 select PARTITION_NAME as "分区",TABLE_ROWS as "行数" from information_schema.partitions where table_schema="tmp_test" and table_name="test_11"; #普通表转为分区表 alter table jxfp_data_bak PARTITION BY KEY(SH) PARTITIONS 8; #分区表常见操作 CREATE TABLE t1 ( id INT, year_col INT ) PARTITION BY RANGE (year_col) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (1999) ); 1、ADD PARTITION （新增分区） ALTER TABLE t1 ADD PARTITION (PARTITION p3 VALUES LESS THAN (2002)); 2、DROP PARTITION （删除分区） ALTER TABLE t1 DROP PARTITION p0, p1; 3、TRUNCATE PARTITION（截取分区） ALTER TABLE t1 TRUNCATE PARTITION p0; ALTER TABLE t1 TRUNCATE PARTITION p1, p3; 4、COALESCE PARTITION（合并分区） CREATE TABLE t2 ( name VARCHAR (30), started DATE ) PARTITION BY HASH( YEAR(started) ) PARTITIONS 6; ALTER TABLE t2 COALESCE PARTITION 2; 5、REORGANIZE PARTITION（拆分/重组分区） 1）拆分分区 ALTER TABLE table ALGORITHM=INPLACE, REORGANIZE PARTITION; ALTER TABLE employees ADD PARTITION ( PARTITION p5 VALUES LESS THAN (2010), PARTITION p6 VALUES LESS THAN MAXVALUE ); 2）重组分区 ALTER TABLE members REORGANIZE PARTITION s0,s1 INTO ( PARTITION p0 VALUES LESS THAN (1970) ); ALTER TABLE tbl_name REORGANIZE PARTITION partition_list INTO (partition_definitions); ALTER TABLE members REORGANIZE PARTITION p0,p1,p2,p3 INTO ( PARTITION m0 VALUES LESS THAN (1980), PARTITION m1 VALUES LESS THAN (2000) ); ALTER TABLE tt ADD PARTITION (PARTITION np VALUES IN (4, 8)); ALTER TABLE tt REORGANIZE PARTITION p1,np INTO ( PARTITION p1 VALUES IN (6, 18), PARTITION np VALUES in (4, 8, 12) #如何快速获取分区信息 1.mysql> show create table e/G 2.mysql>show table status/G 3.INFORMATION_SCHEMA.PARTITIONS 4.EXPLAIN PARTITIONS SELECT ```
sunrise
2024年3月19日 20:37
转发文档
收藏文档
上一篇
下一篇
手机扫码
复制链接
手机扫一扫转发分享
复制链接
Markdown文件
分享
链接
类型
密码
更新密码