高性能MySQL之架构与历史

MySQL逻辑架构

MySQL逻辑架构.png

优化与执行

• MySQL会解析查询，创建内部数据结构（解析树），对齐进行优化（重写查询、决定表的读取顺序、选择合适的索引）；
• 使用explain，可以解释优化过程的各个因素，使用户知道服务器是如何进行优化决策的，并提供一个参考基准，便于重构查询和表、修改配置；
• 优化器不关心表用的是什么存储引擎，但存储引擎对优化查询是有影响的；
• 例：SELECT语句，解析查询之前，服务器先检查查询缓存（Query Cache），若找到对应的查询，服务器就不必再执行查询解析、优化和执行的整个过程，而直接返回查询缓存中的结果。

并发控制

• 多个查询需要在同一时刻修改数据，即会产生并发控制的问题；
• 并发控制有两个层面：服务器层、存储引擎层；
• 读写锁：处理并发读写时，用共享锁/读锁（shared lock/read lock）和排他锁/写锁（exclusive lock/write lock）来进行并发控制；
• 锁粒度：要提高共享资源的并发性，需尽量只锁定需要修改的部分数据，给定的资源，锁定的数据量越少，并发度就越高；加锁也消耗资源（锁的操作：获得锁、检查锁是否解除、释放锁）；需要采取合适的锁策略（在锁的开销和数据的安全性之间制衡）；
• 表锁：开销最小的策略，对表插入/更新/删除，需要先获取写锁，阻塞其他读写操作，没有写锁的时候，其他读取操作才能获得读锁，读锁之间不互相阻塞；写锁比读锁优先级高，写锁可以插队到锁队列中读锁的前面；服务器会为ALTER TABLE之类的语句加表锁，而忽略了存储引擎的锁机制；
• 行锁：开销最大的策略，最大程度支持并发处理。

事务

• 事务是一组原子性的SQL查询，其中的语句要么全部执行成功，要么全部执行失败；
• ACID特性：原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）；
• 四种隔离级别：未提交读（Read Uncommitted）、提交读（Read Committed）、可重复读（Repeatable Read）、可串行化（Serializable）

• 死锁：两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，导致恶行循环
• 死锁的处理：死锁会造成非常慢的查询，InnoDB处理死锁的方法是，将持有最少行级排他锁的事务进行回滚
• 死锁的原因：锁的行为和顺序和存储引擎相关，以同样顺序执行语句，有些存储引擎会死锁，有些不会；死锁产生原因有两个，真正的数据冲突、存储引擎的实现方式。
• MySQL中的事务：自动提交（AUTOCOMMIT）默认开启，不是显示地开始一个事务，则每个查询都当作一个事务执行提交操作；数据定义语言（DDL）中，若是会导致大量数据改变的操作，如ALTER TABLE、LOCK TABLES，会在执行前强制执行COMMIT提交当前的活动事务；
• MYSQL服务器不管理事务，事务是由下层的存储引擎实现的，在同一个事务中，使用多种存储引擎是不可靠的，因为非事务型的表上的变更无法撤销；
• InnoDB可以进行显示锁定：SELECT ... LOCK IN SHARE MODE；SELECT ... FOR UPDATE。