第一章 MySQL架构与历史
1.1 MYSQL逻辑架构
1.1.1 链接管理与安全性
- 每个客户端都会在服务器进程中拥有一个线程,这个线程的查询只会在这个单独的线程中执行,该现场只会轮流在某个CPU中运运行。
- 服务器会负责缓存线程,因此不需要为每个新建的链接创建或销毁线程。
- 当客户短端链接到mysql服务器时,服务器需要对其进行认证。认证基于用户名、原始主机信息和密码。
1.1.2 优化与执行
- mysql会解析查询,并创建内部数据结构(解析树),然后对其进行各种优化,包括重写查询,决定表的读取顺序,以及选择合适的索引等。
- 优化器并不关心表使用的时什么存储引擎。
- 对select语句,在解析查询之前,服务器会先检查缓存,有缓存则直接返回缓存的结果。
1.2 并发控制
无论何时,只要有多个查询需要在同一时刻修改数据,都会产生并发控制的问题。
1.2.1 读写锁
- 共享锁(shared lock)也叫 读锁(read lock)
共享的,多个客户在同一时刻可以同时读取同一个资源。 - 排他锁(exclusive lock) 也叫 写锁(write lock)
排他的,一个写锁会阻塞其他的写锁和读锁。
1.2.2 锁粒度
- 表锁(table lock)
- 行级锁(row lock)
1.3 事务
- 原子性(atomicity)
- 一致性(consistency)
- 隔离性(isolation)
- 持久性(durability)
1.3.1 隔离级别
- READ UNCOMMITED(读未提交)
- READ COMMITED(读已提交)
- REPEATABLE READ(可重复读):mysql默认隔离级别
- SERIALIZABLE(可串行化)
1.3.2 死锁
- 死锁是指两个或多个事务在同一资源上互相占用,并请求锁定对方占用的资源,从而导致恶性循环的现象。
- 当多个事务试图以不同的顺序锁定资源时,就可能会产生死锁。
- 多个事务同时锁定同一个资源时,也会产生死锁。
- 锁的行为和顺序是和存储引擎相关的。
- 死锁的产生有双重原因:有些是因为真正的数据冲突,这种情况通常很难避免,但有些完全是由存储引擎的实现方式导致的。
1.3.2 事物日志
- 事务日志可以提高事务的效率
- 事务日志采用追加的方式,因此写日志的操作是磁盘上一小块区域内的顺序I/O。(顺序I/O比随机快)
- 预写式日志(Writ-Ahead-Loggin)先持久化事务日志,内存中被修改的数据在后台可以慢慢的刷回磁盘。修改数据需要写两次磁盘。
1.3.2 MySQL中的事务
MySQL提供了两种事物型存储引擎:InnoDB 、NDB Cluster. 另外还有一些第三方存储引擎也支持事物。
- 自动提交(autocommit) :mysql默认采用自动提交模式。
- 在事务中混合使用存储引擎
mysql 服务器层不管理事务,事务是由下层的存储引擎实现的。所以在同一个事务中,使用多种存储引擎是不可靠的。
- 隐式和显示锁定
- InnoDB采用的是两阶段锁定协议(two-phase locking protocol). 事务在执行过程中,随时可以执行锁定,锁只有在执行commit或者rollback的时候才会释放,并且锁是在同一时刻被释放。
- InnoDB支持显示锁定
- select …… lock in share mode;
- select …… for update;
1.4 多版本并发控制(MVCC)
- MVCC的实现是通过保存数据在某一时间点的快照来实现的。
- InnoDB的MVCC,是通过在每个行记录后面保存两个隐藏的列(创建时间,过期时间:实际存储系统版本号:每开始一个新的事务,系统版本号都会自动递增)来实现的。
- 保存这两个额外的系统版本号,使大多数读操作都可以不用加锁。
- MVCC只在REPEATABLE READ 和 READ COMMITED 两个隔离级别下工作。
1.5 MySQL的存储引擎
1.5.1 InnoDB 存储引擎
- InnoDB的数据存储在表空间中,表空间是由InnoDB管理的一个黑盒子,由一系列的数据文件组成。
- InnoDB采用MVCC来支持高并发,并且实现了4个标准的隔离级别。默认级别是REPEATABLE READ ,并通过间隙锁(next-key locking)策略防止幻读的出现。间隙锁不仅锁定查询涉及的行,还会对索引中的间隙进行锁定。
- InnoDB表是给予聚簇索引建立的。
1.5.2 MyISAM 存储引擎
- MyISAM不支持事务和行级锁
- MyISAM会将表存储在两个文件中:数据文件和索引文件,分别以.MYD和.MYI为扩展名
- MyISAM对整张表枷锁。而不是针对行
- MyISAM支持全文索引
- MyISAM可以对BLOB,TEXT等长字段,也可以基于前500个字符创建索引
- MyISAM压锁表:如果数据导入后不在进行修改,可以对表进行压缩(打包pack)
- MyISAM性能:设计简单,数据以紧密格式存储,某些场景下性能很好。
1.5.3 MySQL内建的其他存储引擎
- Archive引擎
- Blackhole引擎
- CSV引擎
- Federated引擎
- Memory引擎
- Merge引擎
- NDB引擎
1.5.4 第三方存储引擎
- OLTP类引擎
- 面向列的引擎
- 社区存储引擎
1.5.5 选择合适的存储引擎
- 除非需要用到某些InnoDB不具备的特性,并且没有办法可以替代,否则都应该优先选择InnoDB引擎。
- 除非万不得已,否则建议不要混合使用多种存储引擎
如果应用需要不同的存储引擎,请先考虑一下几个因素:
- 事务
- 备份
- 崩溃恢复
- 特有的特性
1.5.6 转换表的存储引擎
- ALTER TABLE :按行复制到一张新表,同时原表会加锁,消耗系统所有的I/O能力,需要很长的时间。在一个繁忙的表上操作时需要小心。
- 导出与导入:导出sql脚本,修改脚本引擎设置
- 创建与查询:新建一个相同字段的目标引擎的表,然后利用insert …select … 语句将原表数据插入到目标比表中,删除原表(或者修改表名为其他),然后修改目标表的表名。
1.6 MySQL时间线(TimeLine)
- 版本3.23(2001)
- 版本4.0(2003)
- 版本4.1(2005)
- 版本5.0(2005年10月):这是MySQL历史上最有里程碑意义的一个版本,在5.0版本加入了游标、存储过程和触发器的支持。
- 版本5.1(2008)
- 版本5.5(2010):Oracle对MySQL版本重新进行了划分,分成了社区版和企业版。默认引擎更换为InnoDB、增加表分区等。
- 版本5.6(2013年2月):MySQL5.6首个正式版5.6.10发布。MySQL5.6对InnoDB引擎进行了改造,提供全文索引能为,使InnoDB适合各种应用场景。
- 版本5.7(2015年10月):首个GA正式版5.7.9发布。
- 版本8.0 (2016年9月):MySQL开发版本增加了数据字典、账号权限角色表、InnoDB 增强、JSON增强等等
- 版本8.0 (2018年4月):MySQL 8.0首个GA正式版8.0.11发布
PS: https://blog.csdn.net/weixin_42201180/article/details/127229297
1.7 MySQL的开发模式
1.8 总结
- 了解MySQL服务器基本架构
- 深刻理解AICD 以及MySQL 隔离级别
- 深入了解InnoDB存储引擎, 了解其他常见的存储引擎
- 深入了解MVCC
- 了解MySQL发展历程