1、基础架构1.1 客户端和连接服务最上层是一些客户端和连接服务包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。1.2 核心服务第二层架构主要完成大多少的核心服务功能如SQL接口并完成缓存的查询SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现如过程、函数等。在该层服务器会解析查询并创建相应的内部解析树并对其完成相应的优化如确定查询表的顺序是否利用索引等最后生成相应的执行操作。如果是select语句服务器还会查询内部的缓存。1.3 存储引擎层存储引擎层存储引擎真正的负责了MySQL中数据的存储和提取服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同这样我们可以根据自己的实际需要进行选取。1.4 数据存储层数据存储层主要是将数据存储在运行于设备的文件系统之上并完成与存储引擎的交互。1.5 并发控制和锁的概念当数据库中有多个操作需要修改同一数据时不可避免的会产生数据的脏读。这时就需要数据库具有良好的并发控制能力这一切在MySQL中都是由服务器和存储引擎来实现的。解决并发问题最有效的方案是引入了锁的机制锁在功能上分为共享锁(shared lock)和排它锁(exclusive lock)即通常说的读锁和写锁。当一个select语句在执行时可以施加读锁这样就可以允许其它的select操作进行因为在这个过程中数据信息是不会被改变的这样就能够提高数据库的运行效率。当需要对数据更新时就需要施加写锁了不在允许其它的操作进行以免产生数据的脏读和幻读。锁同样有粒度大小有表级锁(table lock)和行级锁(row lock)分别在数据操作的过程中完成行的锁定和表的锁定。这些根据不同的存储引擎所具有的特性也是不一样的。MySQL大多数事务型的存储引擎都不是简单的行级锁基于性能的考虑他们一般都同时实现了多版本并发控制(MVCC)。这一方案也被Oracle等主流的关系数据库采用。它是通过保存数据中某个时间点的快照来实现的这样就保证了每个事务看到的数据都是一致的。详细的实现原理可以参考《高性能MySQL》第三版。2、mysql执行原理2.1 解析和预处理解析器通过关键字将SQL语句进行解析并生成对应的解析树。MySQL解析器将使用MySQL语法规则验证和解析查询。预处理器则根据一些MySQL规则进行进一步检查解析书是否合法例如检查数据表和数据列是否存在还会解析名字和别名看看它们是否有歧义。2.2 查询优化器查询优化器会将解析树转化成执行计划。一条查询可以有多种执行方法最后都是返回相同结果。优化器的作用就是找到这其中最好的执行计划。生成执行计划的过程会消耗较多的时间特别是存在许多可选的执行计划时。如果在一条SQL语句执行的过程中将该语句对应的最终执行计划进行缓存当相似的语句再次被输入服务器时就可以直接使用已缓存的执行计划从而跳过SQL语句生成执行计划的整个过程进而可以提高语句的执行速度。MySQL使用基于成本的查询优化器(Cost-Based OptimizerCBO)。它会尝试预测一个查询使用某种执行计划时的成本并选择其中成本最少的一个。优化器会根据优化规则对关系表达式进行转换这里的转换是说一个关系表达式经过优化规则后会生成另外一个关系表达式同时原有表达式也会保留经过一系列转换后会生成多个执行计划然后CBO会根据统计信息和代价模型计算每个执行计划的成本从中挑选Cost最小的执行计划。由上可知CBO中有两个依赖统计信息和代价模型。统计信息的准确与否、代价模型的合理与否都会影响CBO选择最优计划。2.3 索引下推MySQL5.6引入索引下推优化index condition pushdown)可以在索引遍历过程中对索引中包含的字段先做判断直接过滤掉不满足条件的记录减少回表次数。比如根据name,age联合索引查询所有满足名称以“张”开头的索引然后直接再筛选出年龄小于等于10的索引之后再回表查询全行数据。注意innodb 引擎的表索引下推只能用于二级索引。3、锁 事务3.1 锁粒度尽量只锁定需要修改的部分数据而不是所有的资源更理想的方式是只对会修改的数据片进行精确的锁定。加锁获得锁检查锁是否已经解除释放锁等都会增加系统的开销。如果话费大量的时间来管理锁而不是存取数据那么系统的性能可能会受到影响。所谓的锁策略就是在锁的开销和数据的安全性之间寻求平衡这种平衡当然也会影响到性能大多数的商业数据库系统没有提供更好的选择一般都是在表上施加行级锁mysql提供了更多的选择每种引擎都可以实现自己的锁策略和锁粒度。3.2 表锁是MySQL中最基本的策略并且是开销最小的策略。表锁会锁定整张表一个用户在对表进行写操作插入删除更新前需要先获得写锁这会阻塞其他用户对该表的所有读写操作。写锁比读锁有更高的优先级因此一个写锁请求可能会被插入到读锁队列的前面。反之则不行尽管存储引擎可以管理自己的锁MySQL本身还是会使用各种有效的锁来实现不同的目的。例如服务器会为alter table之类的语句使用表锁而忽略存储引擎的锁机制。3.3 行锁能够最大程度的支持并发但同时也带来了锁开销。3.4 事务的隔离级别read uncommitted - 读未提交1.事务的修改即使没有提交对其他事务也都是可见的。2.事务可以读取未提交的数据也叫做脏读。3.除非必要实在不建议使用read committed - 提交读1.大多数数据库系统的默认隔离级别都是read committed,但是MySQL不是read committed可以满足隔离性的要求一个事务开始时只能读取已经提交的事务所作的修改。换句话说一个事务从开始直到提交之前所作的任何修改对其他事务都是不可见的也叫做不可重复读因为两次执行同样的查询可能会得到不一样的结果。repeatable read - 可重复读1.解决了脏读的问题可以保证在同一个事务中多次读取同业记录的结果是一致的。2.理论上说可重复度还是无法解决另外一个幻读的问题。3.是MySQL的默认事务隔离级别幻读: 指的是当某个事务在读取某个范围内的记录时另外一个事务又在该范围插入了新的记录当之前的事务再次读取该范围的记录时会产生幻行.mvcc多版本并发控制InnoDB引擎通过多版本并发控制解决了幻读的问题serializable - 串行化1.是最高的隔离级别通过强制事务串行执行避免了幻读的问题2.简答来说串行会在读取每一行数据上都加锁所以可能导致大量的超时和锁竞争问题3.5 死锁死锁是指两个或多个事务在同一资源上相互占用并请求锁定对方占用的资源。当多个事务试图以不同的顺序锁定资源时就可能会产生死锁。多个事务同时锁定同一个资源时也会产生死锁。例如-- 事务1starttransaction;updatestockpricesetclose45.5wherestock_id4anddate2002-05-01;updatestockpricesetclose19.8wherestock_id3anddate2002-05-02;commit;-- 事务2starttransaction;updatestockpricesetclose20.12wherestock_id3anddate2002-05-02;updatestockpricesetclose47.81wherestock_id4anddate2002-05-01;commit;如果凑巧两个事务都执行了第一条update语句更新了一行数据同时也锁定了该行数据接着每个事务都尝试去执行第二条update语句却发现该行已经被对方锁定然后两个事务都等待对方释放同时又持有对方需要的锁则陷入死循环。除非有外部因素介入才可能接触死锁。为了解决这种问题数据库系统实现了各种死锁检测和死锁超时机制。越复杂的系统比如InnoDB存储引擎越能检测到死锁的循环依赖并立即返回一个错误。锁的行为和顺序是和存储引擎相关的。以同样的顺序执行语句有些存储引擎会产生死锁有些则不会。死锁产生有双重原因有些是因为真正的数据冲突这种情况难以避免有些则是由于存储引擎的实现方式导致的。死锁发生之后只有部分或者完全回滚其中一个事务才能打破死锁。对于事务型的系统这是无法避免的所以应用程序在设计时候必须考虑如何处理死锁。3.6 事务日志事务日志可以帮助提高事务的效率。使用事务日志存储引擎在修改表的数据时只需要修改内存拷贝再把该修改行为记录到持久在硬盘上的事务日志中而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加的方式因此写日志的操作是磁盘上一小块区域内的顺序IO,而不像随机IO需要在磁盘的多个地方移动磁头所以采用事务日志的方式相对来说要快的多。事务日志持久以后内存中被修改的数据在后台可以慢慢地刷回磁盘。目前大多数存储引擎就是这样实现称为预写式日志修改数据需要写两次磁盘。