笔记：高性能MySQL
最后更新于
这有帮助吗？
最后更新于
这有帮助吗？
大多数基于网络的服务都有类似最上层服务的架构（连接处理、授权认证等）
MySQL的核心服务功能（查询解析、分析、优化、缓存以及所有的内置函数等）都放在第二层架构。所有跨存储引擎的功能（存储过程、触发器、视图等）都在第二层架构实现
存储引擎在第三层，存储引擎负责MySQL中数据的存储和提取。服务器通过API与存储引擎通信，这些接口屏蔽了不同存储引擎之间的差异，使得这些差异对上层查询过程透明。不同存储引擎之间不进行通信，只会响应上层服务器的请求。
MySQL服务会维护一个线程池，避免频繁创建或者销毁线程
当客户端连接到MySQL服务器时，服务器需要对其身份进行认证，在执行某个特定查询时进行鉴权。
MySQL解析查询创建解析树（一种内部数据结构），进行如重写查询、决定表的读取顺序以及选择合适的索引等优化。用户可以通过特殊的关键字提示（hint）优化器，影响它的决策过程。也可以请求优化器解释（explain）优化过程的各个因素，使用户可以知道服务器是如何进行优化决策的，并提供一个参考基准。
优化器并不关心表使用的存储引擎类型，但存储引擎对于优化查询是有影响的。
对于SELECT语句，在解析查询之前，服务器会先检查查询缓存（Query Cache），查询成功则会直接返回所命中的结果
无论何时，只要有多个查询需要在同一时刻修改数据，都会产生并发控制问题。
读写锁是一个由读锁和写锁组成的锁系统。读锁是共享的，互不阻塞的，多个用户在同一时刻可以同时读取同一资源而互不干扰。写锁是排他的，一个写锁会则色其他的写锁和读锁，确保在给定的时间里，只有一个用户能执行写入，并防止其他用户读取正在写入的同一资源。
一种提高共享资源并发性的方式就是让锁定对象具有选择性。尽量只锁定需要修改的部分数据，而不是所有资源。（理论上锁定的数据量越少，则系统的并发程度越高）
需要注意的是加锁是需要消耗资源的。锁的各种操作，包括获得锁、检查锁是否已解除、释放锁等操作，都会增加系统的开销。如果花费大量的时间来管理锁，而不是存取数据，可能会降低系统性能。
锁策略：在锁的开销和数据的安全性之间寻求平衡
一般的数据库都是在表上施加行级锁（row-level lock），并以各种复杂的方式来实现，以便在锁比较多的情况下尽可能提供更好的性能
MySQL的每种存储引擎都可以实现自己的锁策略和锁粒度。
在MySQL中有两种重要的锁策略：表锁和行级锁
表锁是MySQL中最基本的锁策略，也是开销最小的锁策略。表锁是一个作用在数据表上的读写锁。
行级锁可以最大程度地支持并发处理，同时也产生了最大的锁开销。行级锁只存在于存储引擎层。
事务是一组原子性的SQL查询，是一个独立的工作单元。事务内的语句，要么全部执行成功，要么全部执行失败。
执行事务的服务系统必须通过严格的ACID测试（A：原子性，C：一致性，I：隔离性，D：持久性）
A：原子性（一个事务是一个最小工作单元，整个事务的操作要么全部成功，要么全部失败回滚，不可能只执行其中一部分操作）
C：一致性（数据库总是从一个一致性状态转换到另一个一致性状态）
I：隔离性（一个事务所做的修改在最终提交之前，对其他事物是不可见的）
D：持久性（一旦事务提交，则其所做的修改就会永久生效（永久保存在数据库中））
就像锁粒度那样，事务处理也会增加系统开销。业务上可以根据自己是否需要事务查询选择存储引擎
在SQL标准中定义了四种隔离级别。较低级别的隔离通常可以执行更高的并发，系统开销也更低。
四种隔离级别分别是：
未提交读：事务中的修改即使没有提交，对于其他事务也是可见的。事务可以读取未提交的数据，也被称为脏读。（从性能上看，未提交读不会比其他级别好太多，但却缺乏其他级别的很多好处，在实际应用中一般很少使用）
提交读（不可重复读）： 一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。两次执行同样的查询，可能会得到不一样的结果。提交读也被称为不可重复读。大多数数据库系统的默认隔离级别是提交读（但MySQL不是）。
可重复读：可重复读解决了脏读的问题。保证了在同一个事务中多次读取同样记录的结果是一致的。可重复读无法解决幻读问题，幻读指的是当某个事物在读取某个范围内的记录时，另一个事务又在该范围插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。（InnoDB和XtraDB存储引擎通过多版本并发控制解决了幻读问题，可重复读是MySQL的默认隔离级别）
可串行化：强制事务串行执行，避免了脏读、幻读问题。可串行化给读取的每一行数据上都加锁，所以可能导致大量的超时和锁竞争问题，在实际应用中很少使用这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别。可串行化是最高的隔离级别
指的是两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。
数据库系统实现了各种死锁检测和死锁超时机制。InnoDB存储引擎中会检测死锁的循环依赖，并立即返回错误；超时机制解决方案就是当查询时间达到锁等待超时的设定后放弃锁请求（这不是一种好的方案）；目前InnoDB处理死锁的方法是，将持有最少行级写锁的事务进行回滚。
死锁发生以后，只有部分或者完全回滚其中一个事务，才能到破死锁。对于事务型的系统，死锁是无法避免的，所以应用程序在设计时必须考虑如何处理死锁。（大多数情况下只需要执行因死锁回滚的事务即可）
事务日志可以帮助提高事务的效率。存储引擎在修改表的数据时只需要修改其内存拷贝，再把修改行为记录事务日志中，而不是每次都将修改的数据本身持久化到磁盘。事务日志采用的是追加方式，写日志操作是在磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快些。持久化到事务日志之后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。这种方式被称为预写式日志，修改数据需要写两次磁盘（第一次是将修改信息追加到事务日志中，第二次是将内存中被修改的数据慢慢刷回到磁盘上）
MySQL提供了两种事务型存储引擎：InnoDB和NDB Cluster
第三方存储引擎支持事务的有：XtraDB和PBXT
MySQL默认采用自动提交模式。如果不是显示地开始一个事务，则没条查询都会被当作一个事务执行提交操作。如果配置AUTOCOMMIT=0或AUTOCOMMIT=OFF时，所有查询都是在一个事务中，直到显式地执行COMMIT提交或者ROLLABACK回滚，该事务结束，同时又开始了另一个事务。
修改参数AUTOCOMMIT对非事务型表没有影响（如：MyISAM）
MySQL服务器层不管理事务，事务是由下层的存储引擎实现的。所以在同一个事务中，使用多种存储引擎是不可靠的。如果在事务中混合使用的是事务型（如：InnoDB）和非事务型（如：MyISAM），在正常提交的情况下不会有什么问题，不过需要注意的是，如果该事务需要回滚，非事务型的表上的变更无法撤销，就会导致数据库处于不一致状态，事务的最终结构将无法确定。
InnoDB采用的是两阶段锁协议。
