mysql知识脉络_MySQL知识点记录(一)

应用场景

这个可能要依据所采用的存储引擎(myisam,innerdb)来看。

Django下MySQL读写分离的设计实现

在Django框架下，数据库的读写分离的设计一般有两种方式：

1. 手动实现

获取数据库实例的时候调用using，举个栗子说明：

transaction = PrepayTransactions.objects.using('read').filter(channel='wechat', deactivated_at__is_null=True).first()

transaction.channel = 'alipy'

transaction.save(using='read')

2. 自动路由处理

重写数据库路由类，在settings.py文件中配置DATABASE_ROUTERS

class MasterSlaveDBRouter(object):

"""数据库主从读写分离路由"""

def db_for_read(self, model, **hints):

"""读数据库"""

return "slave" # 其中slave是settings.py配置的数据的别名

def db_for_write(self, model, **hints):

"""写数据库"""

return "default" # 其中default是settings.py配置的数据的别名

def allow_relation(self, obj1, obj2, **hints):

"""是否运行关联操作"""

return True

然后配置数据库路由(settings.py)

DATABASE_ROUTERS = ['path_to_router.MasterSlaveDBRouter']

MySQL主从复制

这个一般是从MySQL的高可用性来说的，都是基于MySQL Replication来实现的，实现的原理一般是数据库主节点对其数据的更新保存到binary log文件中，而从节点通过I/O线程把主节点上bin log拷贝到从节点并保存为relay log，当从节点上的sql线程监测到relay log新增内容时开始执行对应的SQL语句来实现数据的同步。

目前MySQL主从数据复制技术大概有三种：异步复制、同步复制和半同步复制，至于这三种复制的区别可参考mysql系列(四)异步复制、全同步复制与半同步复制网上这篇博文，此处就不再赘述。

MySQL主从架构的模式主要有四种：

一主一从

一主多从

主主互备

双主多从

其中读业务数据量特别大的时候考虑多从架构，另外为了实现一台master出现故障时自动切换，可以采用keepalived(主主互备架构，只会监控到主节点的状态)、MMM(双主多从比较好，同时监控master和slave节点的状态)等工具来实现。

乐观锁和悲观锁

1. 乐观锁

乐观的认为在自己取数据的时候不会有其他线程来更改数据，因此取数据的时候不加锁，但对数据进行update等写的操作时要加锁(这个一般通过版本号设计，CAS设计来实现)

2. 悲观锁

不管是读数据还是更新数据都加上锁，这个一般在数据库级别来实现，比如mysql自己的锁机制(行锁，表锁，读锁，写锁)，像select ... for update就是使用了悲观锁。

选择问题：这个要依据实际的应用场景来决定，一般来讲，读频繁的数据库加乐观锁，而写频繁的数据库加悲观锁。

事务及事务的隔离级别

1. 事务的四大特性(ACID)

原子性：一个事务中的所有操作要么全部执行，要么全部回滚，不可能存在一部分操作成功一部分操作失败的情况；

一致性：事务从一个一致性转化成另一个正确的一致性状态，通常会通过事务的其它三个属性AID来保证；

隔离性：事务在提交完成之前，其他会话无法看到事务的执行结果；

持久性：事务一旦被提交，数据会保存下来，哪怕是提交完之后系统崩溃也不会丢失数据；

2. 事务的隔离级别

读未提交：事务未commit操作之前其他会话能够读到要更新的数据，由于没有commit操作发生数据回滚，造成脏读；

读提交：事务未commit操作之前其他会话无法读到要更新的数据(读到的还是之前的老数据)，待commit操作之后其他会话又读到的是新数据，两次读到的数据不一致，不可重复读；

可重复读：可重复读解决了脏读、不可重复读的问题，但可能会造成幻读，比如一个事务A检测到某个数据不存在准备插入一个新数据(包含主键)，但这个时候事务B先插入了这个新数据并且做了commit，然后事务A再进行插入就会报错，但是查询又发现该数据就是不存在(因为MVCC的原因)，此时就造成了幻读；

串行化：事务中行数据读写都加锁，这样就导致其他进程对该数据的读写都会被阻塞，影响性能；

MySQL默认的事务的隔离级别是可重复读

MVCC

在上面解释可重复读出现幻读提到MVCC，这里就顺便解释一下，全称多版本并发控制，从字面意思不难看出它是为控制并发而设计出来的，它一般配合或者取代行级锁使用，降低系统开销，一般是通过保存数据在某个时间点的快照来实现的。

MySQL InnoDB的MVCC是通过在每行记录后面保存两个隐藏的列来实现的，这两个列分别保存了这个行的创建时间和删除时间，不过这里存储的并不是实际的时间值,而是系统版本号(可以理解为事务的ID)，每开始一个新的事务，系统版本号就会自动递增，事务开始时刻的系统版本号会作为事务的ID。

Innodb检查每行数据，确保他们符合两个标准：

InnoDB只查找版本早于当前事务版本的数据行(也就是数据行的版本必须小于等于事务的版本)，这确保当前事务读取的行都是事务之前已经存在的，或者是由当前事务创建或修改的行

行的删除操作的版本一定是未定义的或者大于当前事务的版本号，确定了当前事务开始之前，行没有被删除

符合了以上两点则返回查询结果。

可序列化的数据库锁

事务在读取数据时，必须先对其加 表级共享锁 ，直到事务结束才释放 — 其他事务通过添加共享锁也可以读取数据

事务在更新数据时，必须先对其加 表级排他锁 ，直到事务结束才释放 — 其他事务不能对其进行读写操作

最后，再细细结合实例理解几个概念：

脏读：事务A修改了某个数据但未提交，然后事务B读了这条数据(更改后)，后来事务A回滚了，这就形成事务B的脏读；

不可重复读：事务A先读了某个数据，而事务B对这个数据进行了更改且提交了，这时候事务A再读这个数据时发现跟之前读的数据不一样了，这就是不可重复读；

幻读：其实跟不可重复读有点类似，只不过幻读侧重新增数据，而不可重复读则侧重更新或者删除；