MySQL

2023-03-04 11:25:33 浏览数 (2)

1. 数据库三大范式

  1. 数据库实体的每个属性均不可再分
  2. 在1NF的基础上，非主属性完全依赖于主属性且不存在部分依赖
  3. 在2NF的基础上，非主属性之间不能有依赖关系且必须直接依赖于主属性

2. 数据库的ACID特性

  1. 原子性：事务是最小的执行单位，不可再分
  2. 一致性：执行事务前后，数据保持一致
  3. 隔离性：并发访问数据库时，一个用户的事务不可被其他事务所干扰
  4. 持久性：一个事务被提交后，它对数据库中数据的改变是持久的

3. 并发事务带来的问题

  1. 脏读（Dirty Read）：当一个事务访问数据并对数据进行了修改但尚未提交时，另一个事务访问并使用了这个未被提交的数据
  2. 丢失修改（Lost to Modify）：在一个事务读取数据并进行了修改时，另一个事务也访问了该数据也对该数据进行了修改，使得第一个事务中的修改结果丢失
  3. 不可重复读（Unrepeatable Read）：在一个事务内多次读取同一个数据，当这个事务尚未结束时，另一个事务也访问了该数据并对其进行了修改，则第一个事务多次读取到的数据结果可能是不同的
  4. 幻读（Phantom Read）：在一个事务读取了部分数据时，另一个事务在其中插入了部分内容，导致第一个事务在随后的查询中多出了一些原本不存在的记录

4. 数据库事务的隔离级别

  1. 读未提交（Read Uncommitted）：最低的隔离级别，允许事务读取尚未被提交的数据
  2. 读已提交（Read Committed）：允许读取已被提交的数据，可以避免脏读的问题
  3. 可重复读（Repeatable Read）：对同一字段的多次读取结果都是一致的，除非是当前事务自身的修改，可以避免脏读以及不可重复读
  4. 可串行化（Serializable）：最高的隔离级别，完全服从数据库的ACID特性，避免了脏读、幻读以及不可重复读

5. drop、delete以及truncate的区别

  • drop（丢弃数据）：drop table table_name 将表全部删除
  • delete（删除数据）：delete from table_name where col_name=value 删除某一列的数据
  • truncate（清空数据）：truncate table table_name 只删除表中所有的数据

6. 索引的类型

  • 从数据结构角度
    1. 树索引
    2. 哈希索引
  • 从物理存储角度
    1. 聚簇索引
    2. 非聚簇索引
  • 从逻辑角度
    1. 普通索引
    2. 唯一索引
    3. 主键索引
    4. 联合索引

7. InnoDB存储引擎采用B 树而非B树的原因

  • B 树是基于B树和叶子节点顺序访问指针进行实现的，其具有B树的平衡性，并且通过顺序访问指针来提高区间查询的性能
  • 在B 树中，一个节点中的key从左到右非递减排列，如果某个指针的左右相邻key分别时key i与key i 1且不为null，则该指针指向节点的所有key均大于等于key i且小于等于key i 1
  • 在进行查找操作时，B 树首先在根节点进行二分查找，找到一个key所在的指针，然后递归的在指针所指向的节点进行查找，直到查找到叶子节点，随后在叶子节点进行二分查找，找到key所对应的data
  • 插入、删除操作会破坏平衡树的平衡性，因此在插入删除操作之后，需要对树进行分裂、合并、旋转等操作维护平衡性

用B 树而非B树考虑的是IO对性能的影响。B树的每个节点都存储数据，而B 树只有叶子节点才存储数据，所以查找相同数据量时，B树的高度更高，IO更频繁。数据库索引是存储在磁盘中的，当数据量大时，就不能把整个索引全部加载到内存中，只能逐一加载每一个磁盘页

8. 索引失效的情况

索引列参与表达式计算

代码语言：javascript复制
select 'name' from 'stu' where 'age'   10 = 30;

函数的运算

代码语言：javascript复制
select 'name' from 'stu' where left('date', 4) < 1990;

%词语%的模糊查询

代码语言：javascript复制
select * from 'manong' where 'name' like '码农%'；  --正常使用索引
select * from 'manong' where 'name' like '%码农%'； --索引失效

字符串与数字的比较

代码语言：javascript复制
create table 'a'('a' char(10));
explain select * from 'a' where 'a' = "1";  --正常使用索引
explain select * from 'a' where 'a' = 1;    --索引失效

查询条件中有or时，即使其中条件带有索引，索引也会失效

代码语言：javascript复制
select * from dept where name = 'a' or loc = 'b' or deptno = 42;

正则表达式不使用索引

当MySQL内部优化器认为全表扫表快于使用索引时，索引也会失效

9. MySQL的行锁与表锁

  • 表级锁 是MySQL中锁定粒度最大的锁，加锁速度快，开销小，不会出现死锁，但发生锁冲突的概率最大，并发量最低
  • 行级锁 是MySQL中锁定粒度最小的锁，加锁速度慢，开销大，会出现死锁，但发生锁冲突的概率小，并发度最高

10. MySQL问题排查的方法

  1. 使用show processlist查看当前所有连接信息
  2. 使用explain命令查询sql语句执行计划
  3. 开启慢查询日志，查找慢查询的sql语句

11. MySQl的主从复制流程

  1. master中的binlog dump线程将binlog event传到slave中
  2. slave中的IO线程接收传递的binlog并将其写入到relay log中
  3. slave上的sql线程读取并执行relay log中的内容

12. MySQL中的redo log、undo log以及bin log的作用

  • redo log 重做日志 确保事务的持久性。防止在发生故障的时间点尚有脏页未写入磁盘，在重启mysql服务时，可以根据redo log进行重做，从而达到事务的持久性
  • undo log 回滚日志 保存事务发生之前的数据版本，可用于回滚，同时可以提供多并发控制下的读（MVCC），也即非锁定读
  • bin log 归档日志（二进制日志） 用于复制。在主从复制中，从库利用主库上的bin log进行重播，实现主从同步，还可以用于数据库基于时间点的还原

13. MySQL读写分离的实现方案

MySQl读写分离的实现主要基于主从复制，通过路由的方式使应用对数据库的写请求只在master上实现，读请求只在slave上实现，具体有四种实现方案：

  1. 基于MySQL Proxy代理 在应用与数据库之间增加代理层，代理层接收应用对数据库的请求，根据不同请求类型转发到不同的实例，在实现读写分离的同时还可以实现负载均衡，常见代理有mysql-proxy、cobar、mycat、Atlas等
  2. 基于应用内路由 在应用程序中实现，针对不同的请求类型转发到不同实例执行sql，可基于spring的aop，通过aop拦截dao层的方法，根据方法名称判断要执行的类型并动态切换主从数据库
  3. 基于MySQL-Connector-Java的JDBC驱动方式 Java程序可以在连接MySQL的JDBC中配置主库与从库地址，JDBC会自动将读请求发送给从库，写请求发送给主库，此外JDBC驱动还可以实现多从库之间的负载均衡
  4. 基于sharding-jdbc的方式

14. MySQL优化方案

  1. 服务器优化：增加CPU、内存、网络、更换高性能磁盘
  2. 表设计优化：字段长度控制、添加必要索引
  3. SQL优化：避免SQL命中不到索引的情况
  4. 架构部署优化：一主多从集群部署
  5. 编码优化实现读写分离

15. 为什么要使用数据库连接池

数据库连接是一种重要但有限且昂贵的资源，对数据库连接的管理可以有效地提高程序的伸缩性与健壮性。数据库连接池便是针对这一目标提出的

数据库连接池负责分配、管理并释放数据库连接，它允许应用程序重复使用一个现有的数据库连接并释放空闲时间超过最大时间的数据库连接来避免数据库连接泄漏

数据库连接池在初始化时会创建一定数量的数据库连接，数量由最小数据库连接库保证。数据库的最大连接数量限定了连接池中能占有的最大连接数，当应用程序向连接池请求的连接数超过最大连接数时，请求就会被加入等待队列中

16. 数据库连接技术

hikariCP > druid > tomcat-jdbc > dbcp > c3p0

数据库 sql jdbc 云数据库SQLServer

0 人点赞