MySQL逻辑架构
MySQL具有分层架构,上层是服务器层的查询和执行引擎下层是存储引擎。其中,服务器层又包含两层,第一层负责连接/线程处理、授权认证、安全,第二层负责查询解析、分析、优化、缓存以及所有的内置函数,所有的跨存储引擎功能。
每个客户端连接都会在服务器端拥有一个线程,MySQL服务器端维护线程池。
认证基于用户名、原始主机信息和密码,可使用SSL。
收到查询后,MySQL会解析查询,建立解析树,然后进行各种优化:重写查询,决定表的读取顺序,选择合适的索引等等。
用户可以使用特殊关键字提示优化器,以影响它的决策,也可以令优化器解释优化过程。
优化器并不关心使用的是什么存储引擎,只是要求存储引擎提供容量和开销信息。
并发控制
MySQL在两个层面并发控制:服务器和存储引擎。
读锁:共享锁
写锁:排它锁
锁粒度越小,每次锁定的资源就越少,并发性能更强。
锁策略:在锁的开销和数据安全性做取舍。
每种存储引擎都可以实现自己的锁粒度和锁策略。
表锁:开销最小,读锁共享,写锁互斥,写锁优先级高于读锁。并发性差。
行级锁:最大程度支持并发处理。
事务
事务具有ACID特性,具体来说:
原子性:事务是最小工作单元。
一致性:数据库总是从一个一致性的状态转到另一个一致性的状态。
隔离性:一个事务做的修改,在提交之前,对于其他事务是不可见的。
持久性:一旦事务提交,所做的修改就永久保存到数据库中。
SQL定义了四种隔离级别,较低的隔离带来较低的开销和更高的并发。
READ UNCOMMITTED:事务的没提交修改,对于别的事务可见,带来脏读。
READ COMMITTED:只有已提交修改,其他事务才可见。但是同一个事务两次读数据库,结果是不同的。
REPEATABLE READ:可重复读,会有幻读问题。
SERIALIZABLE:事务串行执行,无并发。
死锁:多个事务以不同的顺序锁定资源,或者多个事务同时锁定一个资源(?)
处理死锁的方法:将持有最少行级锁的事务回滚(InnoDB)
预写式日志:修改表后,首先写在日志中,后台根据日志将改变慢慢刷回磁盘,修改数据需要写两次磁盘。
AUTOCOMMIT:自动提交事务,每个查询都被当作一个事务。关闭则需要显式提交事务。
有一些命令,运行时强制提交当前活动事务。
显式锁定:服务器层实现,不推荐使用。
多版本并发控制
MVCC是行级锁的变种,很多情况下避免了加锁,开销更低。
实现是通过保存数据在某个时间点的快照。
每条记录加入两个列:行的创建时间,过期时间(删除时间)
读的时候,按照事务的时间决定返回那些数据,从而实现可重复读。
MySQL的存储引擎
SHOW TABLE STATUS
InnoDB存储引擎
默认级别可重复读,通过间隙锁避免幻读。
表通过聚簇索引建立,主键查询性能高,二级索引必须包含主键。
事务性存储引擎,支持真正的热备份。
MyISAM存储引擎
MyISAM不支持事务和行级锁。
MyISAM压缩表
除非用到某些InnoDB某些不具备的特性,并且没有其他的办法可以替代,那么都应该使用InnoDB引擎。