MySQL逻辑架构
连接管理与安全性
每个客户端连接在服务器都拥有一个线程,这个连接的查询都在这个线程中进行,而服务器会事先缓存这些连接,以便于客户端连接时,不用新建或销毁连接
连接时,会对其进行用户名和密码的验证,通过后,还会验证其基于某个特定查询的权限
优化与执行
MySQL会解析查询,建立自己的数据结构,并对其进行各种优化,包括重写查询、读取表的顺序以及选择合适的索引等,用户可以通过关键字影响优化的决策过程
并发控制
多个查询在修改同一个数据时,会产生并发问题
读写锁
在处理并发读或者写时,通过实现两种类型的锁来组成锁系统,解决并发问题,这两种锁称为共享锁和排它锁,也称为读锁和写锁
-
读锁:
共享且相互不阻塞,多个用户同一时刻可以读取同一个资源而不互相干扰
-
写锁:
排他,一个写锁会阻塞其他的写锁和读锁
锁粒度
让锁对象具有选择性,尽量只锁定需要修改的部分,而不是全部资源
加锁也需要消耗资源,包括获取锁、检查锁是否已解除、释放锁等
锁策略即是在数据安全性和加锁的消耗方面寻找平衡
对于MySQL,不同的存储引擎有不同的锁策略和锁粒度
-
表锁(table lock)
最基本的锁策略,并且开销最小。锁定整张表,一个用户在对表进行写操作时,会阻塞其他用户的操作
通常在服务器层实现
-
行级锁(row lock)
可以最大程度的支持并发操作,开销也很大
InnoDB等引擎中实现了行级锁。行级锁只在存储引擎层面进行了实现,服务器没有实现
事务
事务是一组原子性的sql查询,是一个独立的工作单元 ,即该组sql语句要么全部成功,要么全部失败
A(atomicity) | C(consistency) | I(isolation) | D(durability) |
---|---|---|---|
原子性 | 一致性 | 隔离性 | 持久性 |
组成单元不可分割,全部成功或全部回滚 | 一致性的状态到另一个一致性的状态 | 当前事务在提交前,对其他事务不可见 | 事务 一旦提交,所做修改会永久保存到数据库中 |
隔离级别
SQL标准中定义了四种隔离级别,规定了事务中的修改在事务间的可见性。(每种引擎实现的隔离级别不尽相同)
READ UNCOMMITED | READ COMMITED | REPAETABLE READ | SERIALIZABLE |
---|---|---|---|
(读未提交) | (读已提交) | (可重复读) | (可串行化) |
事务可读取其他事务未提交的数据,会造成脏读 | 事务只能读取到已提交的数据,也称为不可重复读 | 在同一个事务中,多次读取同样记录的结果是一样的,解决了脏读的问题(mysql默认) | 强制事务串行执行,每行数据加锁 |
死锁
两个或多个事务在同一个资源上相互占用,并请求锁定对方占用的资源,从而导致的恶性循环
多个事务以不同的顺序锁定资源,会产生死锁
多个事务同时锁定同一个资源时,会产生死锁
解决办法:死锁检测和死锁超时机制
事务日志
存储引擎只需要修改内存拷贝,然后将修改行为记录到持久在硬盘中的事务日志中,而不用每次都将修改的数据立即持久到硬盘中。
事务日志采用追加的方式,使用硬盘上一小块区域的顺序IO,因此速度较快
事务日志持久后,在后台慢慢将修改的数据持久回硬盘,这称为预写式日志,需要写两次磁盘
MySQL中的事务
MySQL两种事务型存储引擎:InnoDB和NDB cluster
-
自动提交(AUTOCOMMIT)
MySQL默认使用此模式,即每次查询都当做一个事务执行提交操作
通过设置AUTOCOMMIT变量来开启或禁止自动提交,1或者ON表示启用,0或者OFF表示禁用。禁用时,所有查询都在同一个事务中,直到显式的提交或回滚事务,再开启新的事务
-
在事务中混合使用存储引擎
在MySQL中,事务是由存储引擎实现,而非服务器层
-
隐式和显式锁定
InnoDB采用两阶段锁定协议
事务执行时,可随时锁定,只有在commit或rollback时,才会释放锁,且所有锁同时释放
多版本并发控制(MVCC)
存储引擎实现的都不是简单的行级锁,基于性能考虑,都实现了多版本并发控制
MVCC的实现,是通过保存数据在某个时间点的快照来实现的
MVCC只在REPAETABLE READ和READ COMMITED两个隔离级别下工作,其他隔离级别不兼容
MySQL的存储引擎
“SHOW TABLE STATUS”命令,查询表的相关信息,包括名字、存储引擎、索引等
InnoDB存储引擎
InnoDB是MySQL默认的事务型存储引擎,使用最广泛、最重要的,用来处理大量短期事务
-
InnoDB概览
InnoDB的数据存储在表空间中,MYSQL4.1以后,InnoDB可以将每个表的数据和索引存放在单据的文件中
InnoBD默认级别为REPAETABLE READ,使用间隙锁防止幻读,间隙锁不仅锁住了涉及查询的行,也锁住了索引中的间隙
InnoDB表是基于聚簇索引建立的。
InnoDB的存储格式是平台独立的,可以适用于各个系统平台
MyISAM存储引擎
MySQL5.1之前,默认使用此存储引擎。
MyISAM存储引擎不支持事务和行级锁,且崩溃后无法安全恢复
MyISAM会将表存储在两个文件中:数据文件和索引文件,分别以.MYD和.MYI扩展名结尾
MyISAM的特性:
-
加锁与并发
它会对整张表加锁,而不是数据行
-
修复
-
索引特性
支持全文索引
-
延迟更新索引键
MyISAM压缩表:
对于导入表中的数据不再进行修改,则可以使用压缩表,能减少空间占用,减少磁盘IO,提高查询性能