菜单

周围难点,mysql数据库相关收拾

2019年11月4日 - 数据网络

三范式

三范式定义(范式和反范式)

1NF:每个数据项都是最小单元,不可分割,确定行列之后只能对应一个数据。

2NF:每一个非主属性完全依赖于候选码(属性组的值能唯一的标识一个元组,但是其子集不可以)。

3NF:每一个非主属性既不传递依赖于,也不部分依赖于(主码=候选码为多个市,从中选出一个作为主码)。

BCNF主属性(候选码中的某一个属性)内部也不能部分或传递依赖于码。

4NF :没有多值依赖。

MySQL的复制原理以及流程

基本原理流程,3个线程以及之间的关联;

  1. 主:binlog线程——记录下所有改变了数据库数据的语句,放进master上的binlog中;
  2. 从:io线程——在使用start slave 之后,负责从master上拉取 binlog
    内容,放进 自己的relay log中;
  3. 从:sql执行线程——执行relay log中的语句;

数据库相关

数据类型

MySQL数据类型-菜鸟教程 

MYSQL中数据类型介绍

整数: int(m)里的m是表示数据显示宽度,浮点数,定点数。

字符串:char(n)4.0 n 代表字节,5.0 n 代表字符 (UTF-8=3zj,GBK=2zj)

 char 固定的字符数,空格补上;检索速度快。

 varchar 字符数+1个字节(n<=255)或2个字节(n>255)

 text 字符数+2个字节;不能有默认值;索引要指定前多少个字符;文本方式存储

 blob 二进制方式存储

mysql中myisam与innodb的区别

1.InnoDB的日志

InnoDB有很多日志,日志中有2个概念需要分清楚,逻辑日志和物理日志.

存储引擎

各种存储引擎的区别与联系   
 
(存储数据技术和策略,存储机制、索引技巧、锁定水平等)

数据库存储引擎 
   show table status 显示表的相关信息

InnoDB与MyISAM的比较(从5.7开始innodb存储引擎成为默认的存储引擎。)

 锁机制:行级锁,表级锁

 事务操作:事务安全,不支持

InnoDB
(1)可靠性要求比较高,要求事务;(2)表更新和查询都相当的频繁,并且行锁定的机会比较大的情况。

 MySQL4.1之后每个表的数据和索引存储在一个文件里。

 InnoDB
采用了MVCC来支持高并发,并且实现了四个标准的隔离级别。其默认级别是REPEATABLE
READ(可重复读) ,行级锁。

 自动灾难恢复。与其它存储引擎不同,InnoDB表能够自动从灾难中恢复。

 外键约束。MySQL支持外键的存储引擎只有InnoDB。

 支持自动增加列AUTO_INCREMENT属性。

MyIsam  (1)做很多count
的计算;(2)插入不频繁,查询非常频繁;(3)没有事务。

 表存储在两个文件中,数据文件(MYD)和索引文件(MYI)

 表级锁,读=共享锁,写=排它锁。

 适合选择密集型的表,插入密集型的表。

5点不同

2.事务的实现原理

事务的作用: 事务会把数据库从一种一致的状态转换为另一种一致状态。

事务的机制通常被概括为“ACID”原则即原子性(A)、一致性(C)、隔离性(I)和持久性(D)。

  1. 原子性:构成事务的的所有操作必须是一个逻辑单元,要么全部执行,要么全部不执行。
  2. 一致性:数据库在事务执行前后状态都必须是稳定的。
  3. 隔离性:事务之间不会相互影响。
  4. 持久性:事务执行成功后必须全部写入磁盘。

数据库ACID

数据库的ACID

数据库事务介绍

原子性(Atomicity)一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败回滚,对于一个事务来说,不可能只执行其中的一部分操作。

一致性(Consistency)数据库总是从一个一致性的状态转换到另一个一致性的状态。

隔离性(Isolation)一个事务所做的修改在最终提交以前,对其他事务是不可见的。

持久性(Durability)一旦事务提交,则其所做的修改不会永久保存到数据库。

4 种隔离级别

MVVC的简单介绍

READ
UNCOMMITTED(未提交读)脏读
:事务中的修改,即使没有提交,对其他事务也都是可见的。

READ
COMMITTED(提交读)不可重复读
:事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。

REPEATABLE
READ(可重复读):幻读:
一个事务按相同的查询条件读取以前检索过的数据,其他事务插入了满足其查询条件的新数据。产生幻行。

SERIALIZABLE(可串行化) 强制事务串行执行

MVVC是个行级锁的变种,它在普通读情况下避免了加锁操作,自特定情况下加锁

innodb引擎的4大特性

2.1 事务的隔离性由存储引擎的锁来实现

  数据库事务会导致脏读、不可重复读和幻影读等问题。
  1)脏读:事务还没提交,他的修改已经被其他事务看到。
  2)不可重复读:同一事务中两个相同SQL读取的内容可能不同。两次读取之间其他事务提交了修改可能会造成读取数据不一致。
  3)幻影数据:同一个事务突然发现他以前没发现的数据。和不可重复读很类似,不过修改数据改成增加数据。

InnoDB提供了四种不同级别的机制保证数据隔离性。
不同于MyISAM使用表级别的锁,InnoDB采用更细粒度的行级别锁,提高了数据表的性能。InnoDB的锁通过锁定索引来实现,如果查询条件中有主键则锁定主键,如果有索引则先锁定对应索引然后再锁定对应的主键(可能造成死锁),如果连索引都没有则会锁定整个数据表。

4种隔离级别: 
1) READ UNCOMMITTED(未提交读)
事务中的修改,即使没有提交,对其它事务也是可见的. 脏读(Dirty Read).
2) READ COMMITTED(提交读)
一个事务开始时,只能”看见”已经提交的事务所做的修改.
这个级别有时候也叫不可重复读(nonrepeatable read).
3) REPEATABLE READ(可重复读)
该级别保证了同一事务中多次读取到的同样记录的结果是一致的.
但理论上,该事务级别还是无法解决另外一个幻读的问题(Phantom Read). 
幻读:
当某个事务读取某个范围内的记录时,另外一个事务又在该范围内插入了新的记录.当之前的事务再次读取该范围时,会产生幻行.(Phantom
Row).
幻读的问题理应由更高的隔离级别来解决,但mysql和其它数据库不一样,它同样在可重复读的隔离级别解决了这个问题. 
mysql的可重复读的隔离级别解决了”不可重复读”和“幻读”2个问题. 
而oracle数据库,可能需要在“SERIALIZABLE”事务隔离级别下才能解决幻读问题.
mysql默认的隔离级别也是:REPEATABLE READ(可重复读)
4) SERIALIZABLE (可串行化)
强制事务串行执行,避免了上面说到的 脏读,不可重复读,幻读 三个的问题.

Mysql死锁问题

Mysql悲观锁总结和实践

Mysql乐观锁总结和实践

SELECT … LOCK IN SHARE MODE SELECT … FOR UPDATE:(LOCK IN SHARE
MODE 在有一方事务要Update 同一个表单时很容易造成死锁)

乐观锁:取锁失败,产生回溯时影响效率。

 取数据时认为其他线程不会对数据进行修改。

 更新时判断是否对数据进行修改,版本号机制或CAS操作。

悲观锁:每次取数据都会加锁。

innodb_lock_wait_timeout 等待锁超时回滚事务:  【超时法】

直观方法是在两个事务相互等待时,当一个等待时间超过设置的某一阀值时,对其中一个事务进行回滚,另一个事务就能继续执行。在innodb中,参数innodb_lock_wait_timeout用来设置超时时间。

wait-for graph算法来主动进行死锁检测:  【等待图法】

innodb还提供了wait-for
graph算法来主动进行死锁检测,每当加锁请求无法立即满足需要并进入等待时,wait-for
graph算法都会被触发。

2者selectcount(*)哪个更快,为什么

myisam更快,因为myisam内部维护了一个计数器,可以直接调取。

2.2 原子性和持久性的实现

redo log 称为重做日志(也叫事务日志),用来保证事务的原子性和持久性. 
redo恢复提交事务修改的页操作,redo是物理日志,页的物理修改操作.

当提交一个事务时,实际上它干了如下2件事:
一: InnoDB存储引擎把事务写入日志缓冲(log
buffer),日志缓冲把事务刷新到事务日志.
二: InnoDB存储引擎把事务写入缓冲池(Buffer pool).

这里有个问题, 事务日志也是写磁盘日志,为什么不需要双写技术?
因为事务日志块的大小和磁盘扇区的大小一样,都是512字节,因此事务日志的写入可以保证原子性,不需要doublewrite技术

重做日志缓冲是由每个为512字节大小的日志块组成的. 日志块分为三部分:
日志头(12字节),日志内容(492字节),日志尾(8字节).

索引

索引(存储引擎 快速找到记录的一种数据结构,索引的基本功能)

什么是B-Tree

MySQL索引背后的数据结构及算法原理

MySQL性能优化-慢查询分析、优化索引和配置

MySQL中varchar与char的区别以及varchar(50)中的50代表的涵义

(1)、varchar与char的区别
char是一种固定长度的类型,varchar则是一种可变长度的类型

(2)、varchar(50)中50的涵义
最多存放50个字符,varchar(50)和(200)存储hello所占空间一样,但后者在排序时会消耗更多内存,因为order
by col采用fixed_length计算col长度(memory引擎也一样)

(3)、int(20)中20的涵义
是指显示字符的长度
但要加参数的,最大为255,比如它是记录行数的id,插入10笔资料,它就显示00000000001
~~~00000000010,当字符的位数超过11,它也只显示11位,如果你没有加那个让它未满11位就前面加0的参数,它不会在前面加0
20表示最大显示宽度为20,但仍占4字节存储,存储范围不变;

(4)、mysql为什么这么设计
对大多数应用没有意义,只是规定一些工具用来显示字符的个数;int(1)和int(20)存储和计算均一样;

2.3 一致性的实现

undo log 用来保证事务的一致性. undo 回滚行记录到某个特定版本,undo
是逻辑日志,根据每行记录进行记录.
undo 存放在数据库内部的undo段,undo段位于共享表空间内.
undo 只把数据库逻辑的恢复到原来的样子.

undo日志除了回滚作用之外, undo
实现MVCC(多版本并发控制),读取一行记录时,发现事务锁定,通过undo恢复到之前的版本,实现非锁定读取.

    myisam引擎不支持事务, innodb和BDB引擎支持

相关文章

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图