高性能MySQL之架构与历史

MySQL逻辑架构

优化与执行

• 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）

隔离级别	脏读可能性	不可重复读可能性	幻读可能性	加锁读
Read Uncommitted	Yes	Yes	Yes	No
Read Committed	No	Yes	Yes	No
Repeatable Read	No	No	Yes	No
Serializable	No	No	No	Yes

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

多版本并发控制

• MVCC是行级锁的变种，但在很多情况下避免加锁，开销更低；
• MVCC的实现：保存数据在某个时间点的某个快照；
• InnoDB的MVCC：在每行记录后面保存两个隐藏的列来实现，一个保存行的创建时间，一个保存行的过期时间，存储的是系统版本号，每开始一个新的事务，系统版本号会自动递增，事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号比较；
• MVCC只在REPEATABLE READ、READ COMMITTED两个隔离级别下工作（因为，READ UNCOMMITTED总是读取最新的行，而非符合当前事务版本的行；SERIALIZABLE会对所有读取的行都加锁）。

MySQL的存储引擎

• InnoDB存储引擎：InnoDB的数据保存在表空间中，表空间是由InnoDB管理的一个黑盒子，由一系列数据文件组成；InnoDB采用MVCC来支持高并发，并实现了四个标准的隔离级别，默认级别是REPEATABLE READ，并通过间隙锁（next-key locking）策略防止幻读的出现（间隙锁使InnoDB不仅锁定查询涉及的行，还会对索引中的间隙进行锁定，防止幻影行的插入）；InnoDB表基于聚簇索引建立，聚簇索引对主键查询由很高的性能，但它的二级索引必须包含主键列，所以如果主键列很大，其他所有的索引都会很大。
• MyISAM存储引擎：MyISAM支持全文索引、压缩、空间函数（GIS）等，但不支持事务和行级锁，崩溃后无法安全恢复；MyISAM会将表存储在两个文件中：数据文件和索引文件；MyISAM特性：加锁与并发（读取时会对需要读到的所有表加共享锁，写入时对表加排他锁，但在表有读取查询的同时，也可以往表中插入新的记录，即并发插入）、BLOB和TEXT等长字段也可以基于前500字符创建索引、支持全文索引，这是一种基于分词创建的索引；MyISAM压缩表，不能修改，极大的减少了磁盘空间占用，减少磁盘IO，提升查询性能，压缩表也支持索引，但索引也是只读的；MyISAM最典型的性能问题是表锁的问题。
• 存储引擎的选择：除非需要用到某些InnoDB不具备的特性，并且没有其他办法替代，否则都应该优先选择InnoDB引擎；最好不要混用存储引擎，需要混用时首先考虑后面几个因素，事务（InnoDB、XtraDB最稳定，不需要事务且主要是SELECT和INSERT，如日志型应用，则可选择MyISAM）、备份（InnoDB支持在线热备份）、崩溃恢复（MyISAM崩溃后发生损坏的概率比InnoDB高很多，且恢复速度慢）、特有的特性（应用依赖聚簇索引的优化-InnoDB，只有MyISAM支持地理空间搜索）。
• 例子：日志型应用（MyISAM、Archive，开销低、插入速度非常快，日志记录表名字包含日期，在没有插入操作的历史表上可以做频繁的查询操作，不会干扰到最新的当前表）、只读或大部分情况下只读的表（MyISAM，但要注意崩溃恢复的问题）；订单处理（InnoDB，需要事务的支持，对外键的支持）。
• 转换表引擎：ALTER TABLE（执行时间长，会先将数据复制到一张新表，复制可能会消耗系统所有的IO能力，原表还会加上读锁；转换表引擎，会失去原引擎相关的所有特性）、手动导入导出、创建与查询（创建一个同样结构的表，改变存储引擎，然后INSERT INTO innodb_table SELECT * FROM myisam_table，可以在执行过程中对原表加锁，以确保新表和原表的数据一致）。