建设部人才中心网站,做网站怎么加水平线,网站广告素材,怎样做元古建筑的网站结构图本文介绍关系型数据库及其代表Mysql数据库#xff0c;并介常见面试题目。
一、数据库概述
1. 数据库#xff08;Database, DB#xff09;#xff1a;是长期储存在计算机内的、有组织的、可共享的数据集合。 2. 数据库管理系统#xff08;Database Management System, D…本文介绍关系型数据库及其代表Mysql数据库并介常见面试题目。
一、数据库概述
1. 数据库Database, DB是长期储存在计算机内的、有组织的、可共享的数据集合。 2. 数据库管理系统Database Management System, DBMS是一种操纵和管理数据库的大型软件。 3. 数据库系统Database System, DBS是计算机系统、数据库(DB)、数据库管理系统(DBMS)、应用软件、数据库管理员(DBA)和用户的全体(集合)。 三者之间的关系为
二、关系数据库基础
1. 定义采用了关系模型来组织数据的数据库其以行和列的形式存储数据。 2. 特点
特点说明固定的表结构使用预定义的表结构和关系模型表中的每个列都有明确定义的数据类型和约束条件。强一致性遵循ACID原子性、一致性、隔离性、持久性事务特性保证事务的原子性和数据的一致性。SQL 查询语言使用结构化查询语言SQL进行数据操作和管理支持复杂的查询、连接和数据处理操作。事务支持提供强大的事务支持保证在事务中的操作要么全部提交成功要么全部回滚确保数据的完整性。规范化通过规范化设计数据库模式减少数据冗余确保数据存储的一致性和节省存储空间。复杂的关系支持复杂的表关系一对一、一对多、多对多通过外键保持数据之间的完整性和关联。成熟稳定已经经过长时间的发展和优化成熟的产品和广泛的使用基础具备稳定性和可靠性。ACID事务保证事务的原子性、一致性、隔离性和持久性适合处理需要强一致性和事务控制的应用场景。复杂查询优化通过索引和查询优化器优化复杂查询提高查询效率支持大规模数据处理和高性能应用。数据完整性使用约束如主键、外键、唯一约束和触发器来保持数据的完整性和一致性。
3. 代表Oracle、DB2、MySQL、Microsoft SQL Server、Microsoft Access
4. 候选码、主码、外码
候选码若关系中的某一属性或属性组的值能唯一的标识一个元组而其任何、子集都不能再标识则称该属性组为候选码。主码主键主码是从候选码中选出来的一个实体集中只能有一个主码。外码外键一个关系中的一个属性是另外一个关系中的主码则这个属性为外码。 注不推荐使用建议在应用层建立外键约束而不是在数据库中建立实际外键降低开发复杂性。主属性候选码中出现过的属性称为主属性。非主属性 不包含在任何一个候选码中的属性称为非主属性。
5. ER图实体关系图
6. 数据库范式某大厂数据工程一面
范式定义与要求特点第一范式 (1NF)要求每个字段都是不可再分的数据单元确保数据表中的每一列保持原子性。消除了重复的列确保每列都包含单一的数据类型。第二范式 (2NF)在满足1NF的基础上要求表中的非主属性完全依赖于主键即不存在部分依赖。消除了非主属性对主键部分依赖的问题。第三范式 (3NF)在满足2NF的基础上要求表中的非主属性不依赖于其他非主属性即不存在传递依赖。消除了非主属性之间的传递依赖。巴斯-科得范式 (BCNF)在满足3NF的基础上要求每一个非主属性都完全依赖于候选键的每一个超键即不存在依赖关系的例外情况。强化了对主键的依赖关系确保所有非主属性完全依赖于候选键。第四范式 (4NF)在满足BCNF的基础上要求消除表中的多值依赖关系即一个属性依赖于另一个属性时不能有多个独立的值。消除了多值依赖确保一个属性不依赖于另一个属性的多个独立值。第五范式 (5NF)在满足4NF的基础上要求消除表中的连接依赖即表中的数据在无损分解之后能够重新连接起来且数据不发生丢失。消除了连接依赖确保数据在分解后能够无损连接。第六范式 (6NF)在满足5NF的基础上进一步规范化数据结构确保数据的最小冗余适用于时间相关数据的规范化。在时间相关的数据处理中提供了更高的规范化程度确保数据的最小冗余。
7. 函数依赖
若在一张表中在属性或属性组X 的值确定的情况下必定能确定属性 Y 的值那么就可以说 Y 函数依赖于 X写作 X → Y。部分函数依赖如果 X→Y并且存在 X 的一个真子集 X0使得 X0→Y则称 Y 对 X 部分函数依赖。完全函数依赖在一个关系中若某个非主属性的值依赖于全部关键字称之为完全函数依赖。传递函数依赖在关系模式 R(U)中设 XYZ 是 U 的不同的属性子集如果 X 确定 Y、Y 确定 Z且有 X 不包含 YY 不确定 XX∪Y∩Z空集合则称 Z 传递函数依赖于 X。 8. 存储过程在其中增加了逻辑控制语句的SQL 语句的集合。优点存储过程是预编译过运行速度快适合处理复杂业务缺点难以调试和扩展且没有移植性
三、SQL总结
1. 创建、展示、删除、切换、修改数据库
create database 数据库名称;
show database 数据库名称;
drop database 数据库名称;
user 数据库名称;
alter database 数据库名称 character set gbk2. 数据表操作 1创建、展示、删除数据表
create table 数据表名(字段名 数据类型,字段名 数据类型,字段名 数据类型,
);
show table 数据表名;
drop table 数据表名;2修改数据表某大厂数据工程一面如何添加字段
##修改表名
alter table 数据表名 rename to 修改后数据表名;
##修改字段名
alter table 数据表名 change 字段名 修改后字段名 数据类型;
##修改字段类型
alter table 数据表名 modify 字段名 数据类型;
##增加字段
alter table 数据表名 add 字段名 数据类型;
##删除字段
alter table 数据表名 drop 字段名;3. drop、delete 与 truncate 的联系与区别
特性DROPDELETETRUNCATE作用对象整个表、数据库、视图、索引等表中的特定行整个表是否删除表结构是否否数据恢复不能恢复除非有备份可以通过回滚恢复不能恢复除非有备份触发器触发不触发触发不触发事务控制无法回滚在大多数数据库中可以回滚无法回滚在大多数数据库中自动增量 (AUTO_INCREMENT)移除表后重置不重置重置删除速度最快最慢快删除条件无条件删除整个对象可以指定条件删除特定记录无条件删除整个表的数据SQL 标准SQL 标准SQL 标准SQL 标准空间释放释放所有关联的空间不一定释放空间释放大部分空间
4. 数据表约束
主键约束PRIMARY KEY用于唯一标识对应的记录外键约束FOREIGN KEY用于关联两个表非空约束NOT NULL用于字段值规定唯一约束UNIQUE用于字段约束默认值约束DEFAULT设置字段默认值
5. 数据库查询
普通查询多表连接查询 1交叉连接查询查询两个数据表的笛卡尔积数据组合
SELECT * FROM 表1 CROSS JOIN 表2;2内连接查询查询两个数据表满足连接条件的数据行
SELECT 查询字段1,查询字段2, ... FROM 表1 [INNER] JOIN 表2 ON 表1.关系字段表2.关系字段3左外连接查询查询两个数据表中右表满足连接条件的数据行和左边数据表全部行
SELECT 查询字段1,查询字段2, ... FROM 表1 LEFT [OUTER] JOIN 表2 ON 表1.关系字段表2.关系字段 WHERE 条件4右外连接查询查询两个数据表中左表满足连接条件的数据行和右边数据表全部行
SELECT 查询字段1,查询字段2, ... FROM 表1 RIGHT [OUTER] JOIN 表2 ON 表1.关系字段表2.关系字段 WHERE 条件注outer可省略
子查询在查询中嵌套查询anyallexist
6. 视图
视图是基于 SQL 语句的结果集的可视化的表是虚拟的表本身不包含数据也就不能对其进行索引操作基本语法
##创建
CREATE VIEW 视图名 AS
SELECT 列
FROM 表
WHERE 查询条件;
##删除
DROP VIEW 视图名;7. 触发器
当对表进行插入、更新、删除操作的时候会自动执行的特殊的存储过程作用数据一致性维护、日志记录和审计、自动计算字段基本语法
create trigger 触发器的名字
before|after #定义触发器的触发时机
新增insert|删除delete|修改update #定义数据操作语言DML类型
on 表名 #指定具体是哪张表
for each row #声明为行级触发器只要操作一条记录就能触发触发器执行一次
具体的触发器操作8. 事务用于保证数据的一致性
START TRANSACTION - 指令用于标记事务的起始点。SAVEPOINT - 指令用于创建保留点。ROLLBACK TO - 指令用于回滚到指定的保留点如果没有设置保留点则回退到 START - TRANSACTION 语句处。COMMIT - 提交事务。
四、Mysql数据库
Mysql最流行的关系型数据库管理系统之一将数据以表的形式存储体积小、速度快。某大厂数据工程一面
一基本数据类型
1. 整型TINYINT1字节、SMALLINT2字节、MEDIUMINT3字节、INT4字节常用和 BIGINT8字节 2. 浮点型FLOAT4字节、DOUBLE8字节和DECIMALM,DM表示数据的长度D表示的是小数点后的长度 注FLOAT和DOUBLE存在浮点数精读丢失的问题因此在存储金额等需要准确无误的数据时需要使用DECIMAL。 3. 字符串类型CHAR定长和VARCHAR变长一般不确定长度的时候建议使用节约资源 4. 文本类型存储一些描述性文字TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT 5. 日期与时间类型YEAR、DATE、TIME、DATETIME 和 TIMESTAMP 6. 二进制类型存储图片、文件等TINYBLOB、BLOB、MEDIUMBLOB、LONGBLOB
二基本架构 1. Mysql主要由两部分组成存储引擎和Server层server层又包括连接器、查询缓存、分析器、优化器、执行器和一个日志模块binlog。各个部分的功能如下
连接器:身份认证和权限相关查询缓存 执行查询语句的时候会先查询缓存MySQL 8.0 版本后移除分析器 检查SQL语句语法是否正确优化器 按照 MySQL 认为最优的方案去执行(分析索引执行器 执行语句然后从存储引擎返回数据binlog记录数据库操作语句的原始逻辑用于主从备份数据一致性存储引擎主要负责数据的存储和读取采用的是插件式架构支持 InnoDB、MyISAM、Memory 等多种存储引擎。现在使用的基本上都是InnoDB
2. SQL语句执行过程
查询语句 增删改语句
三Mysql存储引擎某大厂数据工程一面
Mysql存储引擎采用的是插件式架构是基于表的不是基于数据库的MySQL 5.5.5 之前MyISAM 是 默认存储引擎。之后InnoDB 是 MySQL 的默认存储引擎目前主流。二者之间的区别为
特性MyISAMInnoDB锁粒度表级别行级别事务支持不支持支持隔离级别不支持支持 SQL 标准的四个隔离级别外键支持不支持支持MVCC 支持不支持支持索引结构BTree具体实现不同BTree具体实现不同崩溃恢复不支持支持性能较弱更强大
四Mysql索引
1. 定义索引是帮助mysql高效获取数据的数据结构 2. 索引分类
索引类型特点BTree 索引默认索引类型适用于大多数查询操作。支持范围查询、排序查询等。InnoDB 和 MyISAM 都使用 BTree 作为默认索引结构。Hash 索引基于哈希表实现适用于等值查询不支持范围查询。Memory 存储引擎支持此索引。全文索引适用于全文检索用于查找文本数据中的关键词。MyISAM 和 InnoDB从 MySQL 5.6 开始支持全文索引。空间索引用于地理数据类型的索引如 GIS 数据支持复杂的地理空间查询。MyISAM 和 InnoDB从 MySQL 5.7 开始支持空间索引。唯一索引确保索引列的值唯一可以有多个 NULL 值。适用于需要唯一性约束的列。主键索引一种特殊的唯一索引不允许 NULL 值。每个表只能有一个主键索引。复合索引由多个列组成的索引用于提高多列查询的性能。前缀索引对字符列的前缀部分进行索引适用于需要对长字符串列的前几部分进行索引的情况。聚簇索引数据存储与索引顺序一致每个表只能有一个聚簇索引。InnoDB 使用主键作为聚簇索引。如果没有定义主键则选择一个唯一的非空索引。如果没有合适的唯一索引InnoDB 会隐式创建一个行 ID。非聚簇索引索引结构和数据存储分离索引中存储指向数据行的指针可以有多个非聚簇索引。MyISAM 和 InnoDB 都支持非聚簇索引。
3. 最左匹配原则某大厂数据工程一面
使用联合索引查询时索引的使用从最左边的列开始必须依次匹配索引的列不能跳过任何一列。违反最左匹配原则导致索引失效情况查询条件不包含组合索引的最左列在查询条件中跳过了组合索引中的某一列使用了不支持索引的函数或运算符
4. 索引失效
创建了组合索引但查询条件未遵守最左匹配原则在索引列上进行计算、函数、类型转换等操作以 % 开头的 LIKE 查询比如 LIKE ‘%abc’;查询条件中使用 OR且 OR 的前后条件中有一个列没有索引涉及的索引都不会被使用到N 的取值范围较大时会导致索引失效走全表扫描(NOT IN 和 IN 的失效场景相同);发生隐式转换
五 Mysql三大日志
1. binlog日志server层
逻辑日志记录内容是语句的原始逻辑主要用于MySQL 数据库的数据备份、数据同步保证数据一致性三种格式statement、row可能会产生数据不一致情况用开销较大、mixed
2. redo logInnoDB 存储引擎独有
物理日志记录数据页的物理修改用于数据库的崩溃恢复保证数据库的持久性每条 redo 记录由“表空间号数据页号偏移量修改数据长度具体修改的数据”组成刷盘时机事务提交log buffer 空间不足事务日志缓冲区满Checkpoint后台刷新线程正常关闭服务器
3. undo log
逻辑日志记录内容与原始操作相反的 SQL 语句用于事务回滚保证数据库的原子性 注redo log 在事务执行过程中可以不断写入而 binlog 只有在提交事务时才写入
六Mysql事务
1. 事务特性ACID
原子性Atomicity事务是最小的执行单位不允许分割一致性Consistency执行事务前后数据保持一致隔离性Isolation并发访问数据库时各并发事务之间数据库是独立的持久性Durability一个事务被提交之后它对数据库中数据的改变是持久的 A I D → D AID\rightarrow D AID→D
2. 并发事务导致的问题及解决方式
现象说明解决方法脏读一个事务读到了另一个事务尚未提交的修改数据。设置事务隔离级别为 READ COMMITTED 或更高。不可重复读一个事务在前后两次读取同一数据时发现数据被另一个事务修改了导致两次读取结果不一致。设置事务隔离级别为 REPEATABLE READ 或更高对记录加锁。幻读一个事务在前后两次读取时发现插入或删除了一些新的数据行导致读取结果集的行数不一致。设置事务隔离级别为 SERIALIZABLE对记录加锁和加GAP锁。
3. 事务隔离级别 Mysql中有四种事务隔离级别
READ-UNCOMMITTED(读取未提交) 最低的隔离级别允许读取尚未提交的数据变更可能会导致脏读、幻读或不可重复READ-COMMITTED(读取已提交) 允许读取并发事务已经提交的数据可以阻止脏读但是幻读或不可重复读仍有可能发生。通过MVCC实现REPEATABLE-READ(可重复读) 对同一字段的多次读取结果都是一致的除非数据是被本身事务自己所修改可以阻止脏读和不可重复读但幻读仍有可能发生。通过MVCC实现SERIALIZABLE(可串行化) 最高的隔离级别完全服从 ACID 的隔离级别。该级别可以防止脏读、不可重复读以及幻读。通过加锁实现
4. 并发事务控制 1锁悲观
共享锁S锁允许多个事务同时获取锁兼容通常用于读取操作排他锁X锁不允许多个事务同时获取通常用于写操作表级锁针对非索引字段加的锁对当前操作的整张表加锁行级锁记录锁、间隙锁、临键锁针对索引字段加的锁 只针对当前操作的行记录进行加锁 注InnoDB 不光支持表级锁还支持行级锁默认表级锁和行级锁都有X和S两种锁 2MVCC多版本并发控制对一份数据会存储多个版本通过事务的可见性来保证事务能看到自己应该看到的版本 -通过创建数据的多个版本和使用快照读取来实现并发控制实现手段隐藏字段、read view用于控制版本可见性、undo log用于记录某行数据的多个版本的数据 3当前读和快照读当前读一致性锁定读就是给行记录加 X 锁或 S 锁快照读一致性非锁定读就是普通的 SELECT 语句
5. 分库分表 1垂直拆分
垂直分库以表为依据根据业务将不同表拆分到不同库中垂直分表以字段为依据根据字段属性将不同字段拆分到不同表中 2水平拆分水平分库将一个库的数据拆分到多个库中水平分表将一个表的数据拆分到多个表中(可以在同一个库内)
七Mysql优化
1. 优化方法
表设计优化可参考Mysql高性能优化索引优化把握索引创建原则和失效注意事项见上文主从复制读写分离分库分表
2. 定位慢查询 1方法
开源工具arthas、prometheus、skywalkingmysql自带的慢日志 2sql执行很慢的分析方法在sql查询语句前面加explain或desc分析索引等的使用情况
3. 深度分页优化 1深度分页指查询偏移量过大的场景会导致查询耗时多响应慢。 2优化方案
当可以保证 ID 的连续性时根据 ID 范围进行分页先查询出 limit 第一个参数对应的主键值再根据这个主键值再去过滤并 limit延迟关联的优化思路跟子查询的优化思路其实是一样的都是把条件转移到主键索引树减少回表的次数使用内连接包含子查询。建立覆盖索引减少回表次数 注回回表是指根据索引查询到的主键值再去访问主键索引从而获取完整的数据记录。
