mysql的逻辑架构分为3层;
这里有第一层处理,每个客户端的连接都会在服务器进程中拥有一个线程,连接的查询在这个线程中单独进行。
此处由第二层,中间层处理,该处的优化器负责创建内部数据结构,然后优化,包括重写查询,决定表的读取顺序,以及选择合适的索引。
通常也称为共享锁和排他锁;
一种优化的策略,对于不同的锁提供不同的力度,让锁定对象更有选择性。当然加锁的操作也增加系统的开销。包括(获得锁,检查锁是否解除,是否锁)。下面介绍两种最重要的锁力度
事务是一个独立的工作单元,可以用START TRANSACTION语句开始一个事务,然后要么使用COMMIT提交,或者使用ROLLBACK撤销所有的修改。
事务有四大特性;
不是所有的数据库都支持事务,像现在大火的nosql大多都不支持事务,而mysql不同的引擎对事务的支持也不一样,像默认的InnoDB是支持事务的,而Myisam这一的引擎就不支持事务。这个需要根据业务去做相应的选择。
数据库提供了四种事务隔离级别, 不同的隔离级别采用不同的锁类开来实现。
隔离级别 | 脏读可能性 | 不可重复读可能性 | 幻读可能性 | 加锁读 |
---|---|---|---|---|
READ UNCOMMITTED | YES | YES | YES | NO |
READ COMMITED | NO | YES | YES | NO |
REPEATABLE READ | NO | NO | YES | NO |
SERIALIZABLE | NO | NO | NO | YES |
相关概念
脏读
:事务中的修改,即使未提交,对其他事务也是课件的。事务可以读取未提交的数据。不可重复读
:在同一个事务中,再次读取数据时,所读取的数据,和第1次读取的数据,不一样了幻读
:幻读的重点在于新增或者删除,同样的条件, 第1次和第2次读出来的记录数不一样。
幻读是指当一个事务在读取某个范围内的数据时,另一个事务在这个范围内插入了一行记录并提交,于是当前一个事务再次读取该范围内的数据时,发现多出了一行,即幻行。脏读、不可重复读、幻读的级别高低是:
脏读 < 不可重复读 < 幻读
所以,设置了最高级别的SERIALIZABLE_READ就不用在设置REPEATABLE_READ和READ_COMMITTED了
两个活多个事务在同一资源上相互占用,并请求锁定对方占用的资源,从而导致恶性循环的现象。 为解决这种问题,数据库都是些了各种死锁检测和死锁超时机制。
事务日志可以提高事务的效率,使用事务日志,存储引擎在修改表的数据时只要需要修改内存拷贝,再把修改行为记录到硬盘上的事务日志中,而不用每次都将修改的数据持久到磁盘。事务日志持久以后,内存中被修改的数据在后台可以慢慢的刷回到磁盘。目前大多数存储引擎都是这样实现的,我们通常称之为预写式日志。
MySQL中的事务。
MySQL中提供了两种事务型的存储引擎:InnoDB和NDB Cluster。
MySQL默认采用自动提交模式。也就是说,如果不是显式地开始一个事务,则每个查询都被当作一个事务执行提交操作。可以通过设置AUTOCOMMIT变量来启用或者禁用自动提交模式。
SHOW VARIABLES LIKE 'AUTOCOMMIT';SET AUTOCOMMIT = 1;
MySQL也可以通过执行SET TRANSACTION ISOLATION LEVEL命令来设置隔离级别。
也可以只改变当前会话的隔离级别:
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITED;
不要在事务中混合使用存储引擎,例如InnoDB和MyISAM,在正常提交的情况下不会有什么问题。但如果该事务需要回滚,非事务型的表上的变更就无法撤销。
InnoDB采用的是两阶段锁定协议,锁只有在COMMIT或者ROLLBACK的时候才会释放,并且所有的锁是在同一时刻被释放。
另外,InnoDB也支持通过特定的语句进行显式锁定,这些语句不属于SQL规范。
SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE
MySQL也支持LOCK TABLES和UNLOCK TABLES语句,这是在服务层实现的与存储引擎无关,他们有自己的用途,但并不能代替事务处理。
不同的存储引擎MVCC实现不同,典型的有乐观并发和悲观并发控制。下面结合InnoDB说明下。 InnoDB的MVCC,通过在每行记录后面保存两个隐藏列实现,这两个列一个保存行的创建时间,一个保存过期(删除时间),当然这里存储的创建时间不是真正的时间,而是系统版本号。
MVCC只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。
查询表相关信息,命令如下
mysql> show table status like 'city' \G
*************************** 1. row ***************************
Name: city //表名
Engine: InnoDB//引擎名
Version: 10
Row_format: Compact//行的格式
Rows: 600//行数,对于InnoDB,该行是估计值,其他引擎为准确值
Avg_row_length: 81//平均每行字节数
Data_length: 49152//表数据大小,byte为单位
Max_data_length: 0//
Index_length: 16384
Data_free: 0
Auto_increment: 601
Create_time: 2017-10-09 19:59:23
Update_time: NULL
Check_time: NULL
Collation: utf8_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
修改表的引擎采用如下语句:
ALTER TABLE city ENGINE = InnoDB;
下面简单介绍下相关存储引擎的优缺点。
InnoDB
优点:Mysql当前的默认引擎,事务型引擎,用MVCC支持高并发,通过间隙锁在REPEATABLE READ级别下就能防止幻读。支持热备份。 缺点:非常复杂,性能较一些简单的引擎要差一点儿。空间占用比较多。
MyISAM:
优点:Mysql 5.1之前版本的默认引擎。全文索引、压缩、空间函数(GIS)、并发插入、某些场景下性能很好 缺点:非事务型、不支持行锁、崩溃后数据不容易修复
** Archive**:
优点:支持高并发插入,解决不可重复读,针对高速插入和压缩做了优化的简单引擎 缺点:只支持查询和插入操作,非事务型,仅适合日志和数据采集的应用场景
** CSV引擎**:
优点:有效支持CSV格式文件的导入导出。 缺点:作者没说
** Memory引擎**:
优点:用来快速地访问数据的,比MyISAM快一个数量级。支持Hash索引,因此查询操作非常快。 缺点:所有数据保存在内存里,重启只留下表结构。只支持表级锁,并发能力低下。不支持BLOB或TEXT类型的列。且行长度固定,容易导致内存浪费。
NDB集群引擎:
作者没有细讲,后文会细讲的吧,总之就是支持建立集群。
** XtraDB和PBXT等OLTP类引擎**:
优点:可完全替代InnoDB,或者高度相似。还额外提供了一些性能优化、可测量性、操作灵活性 缺点:第三方的引擎,社区支持的存储引擎,可能不能保证质量 其实最常用的是InnoDB和MyISAM。