第一章 MySQL架构与历史
mysql 的逻辑架构图：
- 连接、授权认证以及安全等都在第一层完成
- 内置函数、存储过程、视图、触发器都在第二层实现。第二层不知道存储引擎层到底如何实现存储或锁
- 存储引擎提供数据的存储与提取
连接管理与安全性：
- 每个连接在服务器进程中拥有一个线程，服务器缓存多个线程减少创建销毁开销
- 通过用户名、原始主机信息、密码等认证。也可以通过证书认证
优化与执行
- mysql解析查询语句，然后优化，比如重写查询语句、决定表读取顺序、选择合适索引
- 可以通过hint影响mysql的优化器优化决策
- 使用explain查询优化决策
并发控制
- 读写锁，读锁又叫共享锁，写锁又叫独占锁。
- 锁粒度，分为行锁与表锁。锁粒度变小，会增加系统的开销。而锁粒度变大，则会降低系统的并发性能。也会有锁策略实现对二者的均衡。
- 行锁：InnoDB实现了行锁，行锁只在存储引擎层实现。
- 表锁：存储引擎会实现表锁，但是使用ALTER TABLE之类的语句，服务器层会使用表锁，而忽略存储引擎的表锁
事务：
- ACID：原子性、一致性、隔离性、持久性
- 针对不同的场景，可以选择支持或不支持事务的存储引擎。即便是不支持事务的存储引擎，也可以使用mysql服务器的LOCK TABLES提供一定的保护
- 事务的隔离级别，说的是两个事务对相同的或是交叉的数据进行读取修改时的关系。那么两个事务就属于两个线程
- READ UNCOMMITED：事务中修改，未提交，其他事务也能看到
- READ COMMITED：读到其他事务提交的数据，会出现不可重复读。此处的不可重复读包括了脏读（读取一条记录）与幻读（读取某个范围内的记录）
- REPEATABLE READ：可重复读，只解决了脏读（读取一条记录）的问题。但是并没有解决幻读。MYSQL的默认事务隔离级别是可重复读。需要注意的是InnoDB存储引擎通过多版本并发控制解决了幻读问题。
- SERIALIZABLE：强制事务串行化执行，也就是会在读取的每一行数据上加锁，导致大量的超时与锁争用。
- 死锁：多个事务请求占用对方占用的资源，且互不想让。
- InnoDB可以检测到死锁，并立即返回一个错误。然后将持有该最少行级排他锁的事务进行回滚
- 也可以等待到超时设定后放弃锁请求
- 事务日志：可以提高事务效率。
- 第一步：存储引擎修改表数据时，只修改表数据在内存中的拷贝。
- 第二步：将该修改行为（不是修改后的数据）记录到磁盘中，防止系统崩溃丢失数据
- 第三步：将修改后的数据慢慢写入磁盘中
- MySQL中的事务：
- mysql提供两种事务引擎，InnoDB与NDB Cluster
- 默认自动提交。show variables like ‘AUTOCOMMIT’; set AUTOCOMMIT=1;
- 设置隔离级别：set session transaction isolation level SERIALIZABLE
多版本并发控制MVCC：
- 大多数的事务型存储引擎不是简单的行级锁，一般他们都是实现了MVCC
- MVCC是行级锁的变种，很多情况下可以避免加锁操作，因此开销更低。
- MVCC的实现过程
- 在每行记录后面保存两个隐藏的列来实现，一个保存了行的创建时间（行的版本号），一个保存了行的过期时间。这两个不是时间，而是系统版本号
- select查找符合以下两个条件的数据：创建时间早于当前事务版本的数据行。行的删除版本号要么未定义要么大于当前事务版本号
- insert：插入的行保存当前系统版本号
- delete：为删除的每一行保存当前系统版本号作为删除标志
- update：插入一条记录，保存当前系统版本号作为创建时间，同时保存当前系统版本到原来行作为删除标志
- MVCC只在REPEATABLE READ和READ COMMITTED级别下工作。而READ UNCOMMITED总是读取最新数据行。而SERIALIZABLE会对所有读取的行都加锁
mysql存储引擎
- 创建表后，会创建一个与表同名的.frm文件保存表定义，比如创建MyTable表，则会在文件系统中创建MyTable.frm，所以表名大小写是否敏感与文件系统有关。并且mysql可以配置是否大小写敏感。查看表信息可以使用 show table statue like ‘MyTable’
- InnoDB
- 支持四个隔离级别，默认使用REPEATABLE READ，并使用MVCC阻止幻读
- 表基于聚簇索引创建。不过其非主键索引必须包含主键列。如果主键列大，那么整个索引都会很大。所以主键应尽可能小
- 表与索引的存储格式平台独立，所以可以直接拷贝文件到其他平台
- 支持真正的热备
- MyISAM
- 表数据会存储两个文件：.MYD与.MYI
- 使用表锁，且支持读写锁，最关键的是在读取操作时可以插入新数据
- 数据库崩溃后无法全部恢复数据，可以恢复一部分，但是恢复的速度比较慢
- 支持全文索引
- 延迟更新索引键：每次修改完成时不将索引块写入磁盘中，而是等到清理缓存区或关闭表时执行，数据库崩溃后，需要专门修复
- 压缩表：建表且导入数据后，如果不再进行修改，那么久可以压缩。可以减少空间占用。且压缩是压缩表中记录独立压缩的。且使用时解压缩性能损失不大
- 其他内建存储引擎、第三方存储引擎
- 选择合适的存储引擎：大量使用InnoDB
- 优先使用InnoDB
- 使用全文索引时，优先使用InnoDB+Sphinx，而不是MyISAM
- 需要事务，使用InnoDB或XtraDB
- 一般日志型应用可以使用MYISAM或Archive
- 需要在线热备份，使用InnoDB
- 崩溃恢复，InnoDB损毁概率较小
- 转换表的引擎
- alter table mytable engine = InnoDB ,执行时间较长，且有可能失去原有引擎相关的特性
- 使用mysqldump工具进行导入导出，导出后修改引擎，然后导入
- 先创建表，然后使用 insert into … select * from …进行导入
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
