【一】MySql高性能优化——基础架构

    MySql作为最受欢迎的数据库之一在我们的工作中经常会被用到，但我发现在工作中面试了很多IT工作者，很多人只是站在了MySql的大门之外，并没有深入理解MySql的一些基本原理，出于这个原因，我产生了自学同时也对MySql的基础原理做一系列文章，其中有阐述有无之处请大家包涵指正。

    MySql主要分为两层，上层是服务层和查询执行引擎层，下层是存储引擎层。大家不用奇怪上层中包含两层，层次的划分完全是根据个人理解，我这里之所以说成两层是因为从存储的角度上划分的，上层主要负责业务性的封装，类似于Java中的Controller和Service，下层类似与Dao。这点大家不必过于纠结。上层中的服务层主要负责连接处理、授权认证、安全等等。上层中的查询执行引擎层主要负责查询解析、分析、优化、缓存、内置函数等的所有跨存储引擎的功能，如：存储过程、触发器、视图等。下层存储引擎层主要负责存储和提取，这层会拆分为各种类型的存储引擎，在后面的文章中我会对几种主要的存储引擎做详细的描述，主要设计其特性和适用场景。

    客户向Mysql的每个请求都会拥有一个独立的线程，当客户请求超过MySql的最大线程数时就会产生排队等待或者拒绝访问的问题，所以对于高并发场景需要根据情况设置合理的线程数或分库。在5.5版本提供了可以使用线程池的API插件，可以有效控制线程数量。客户使用了安全套接字（SSL）的方式连接，还可以使用X.509证书认证。这种权限认证可以细化到查询级别。

    MySql会对客户的查询做内部优化。

    主要优化有：

        ①重写查询，决定表的读取顺序并选择合适的索引。用户可通过关键字hint影响MySql的优化策略；

        ②不同的存储引擎对查询的优化是不通的；

        ③查询是缓存优先，缓存没有命中则执行优化步骤查询数据。

    并发控制：

        ①MySql解决并发问题是通过服务器层和存储引擎层，主要控制方式就是锁（Lock）；

        ②锁：非阻塞共享锁（读锁）、阻塞排他锁（写锁）。排他锁比共享锁优先级更高；

        ③锁粒度：表锁（Table Lock）、行级锁（Row Lock）。这两种锁粒度根据名称可以理解其功能，所以不多描述。两种类型的锁适用于不通级别的并发性，各有优略。行级锁只在存储引擎上实现了，所以不通的存储引擎也会有不同的效果。粒度越小性能消耗越大，但并发效果越好。所以在选择上需要根据具体项目情况进行选择。

    事务就是一组原子性的SQL查询，一个独立的工作单元。事务的语句要么全部执行成功，要么全部执行失败。系统都应该进行过严格的ACID测试。ACID：原子性（atomicity）、一致性（consistency）、隔离性（isolation）和持久性（durability）。

        ①原子性：一个事物必须被视为一个不可分割的最小工作单元；

        ②一致性：数据库总是从一个一致性的状态转换到另外一个一致性的状态；

        ③隔离性：一个事务所做的修改在最终提交以前，对其他事务是不可见的；

        ④持久性：一旦事务提交，则其所做的修改就会永久保存到数据库中。

    事务也会消耗服务器性能。非事务型的存储引擎，可以有更高的性能。非事务型引擎可以通过表锁模拟部分事务的特性。事务的隔离级别较低则会有更高的并发性能，同时系统的开销也更低。隔离级别越高同步性越好，但性能消耗也越多。

        ①READ UNCOMMITTED（未提交读）：事务可以读取未提交的数据，即脏读。性能不会比其他级别好太多，但会有更多的问题，应用较少；

        ②READ COMMITTED（提交读）：事务只可读取已提交的修改，即不可重复读。但在同一事务中多次读取相同查询的结果可能不通，并且会有幻读，即两次相同的查询结果不同。这一级别是多数数据库的默认级别；

        ③REPEATABLE READ（可重复读）：在同一个事务中多次读取同样的记录的结果是一致的。但还是会有幻读。InnoDB和XtraDB通过MVCC（多版本并发控制）解决了幻读问题。这个级别是MySql的默认级别。

        ④SERIALIZABLE（可串行化）：强制事务串行执行，解决了幻读问题，但会在读取的每一行数据上都加锁，所以可能导致大量的超时和锁争用问题。

//设置当前会话隔离级别
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

    事务日志：存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到硬盘。整个存储过程需要刷两次磁盘。事务日志是顺序I/O，而不是随机I/O，因此效率会快很多。

    MySql提供了两种事务型存储引擎：InnoDB和NDB Cluster。另外的三方引擎也有提供，如：XtraDB和PBXT。MySql会自动提交事务，在没有显示声明一个事务时，每个请求都是一个独立的事务，通过设置AUTOCOMMIT变量启用或禁用自动提交功能。对MySql中的非事务型引擎，如：MyISAM。修改此变量不会有影响，相当于一直是启用事务自动提交的状态。在非事务引擎上执行事务操作，MySql不会有任何提醒。MySql的事务是由存储引擎层实现管理的，所以不要在同一个事务中使用多个存储引擎。

//查看AUTOCOMMIT状态
SHOW VARIABLES LIKE 'AUTOCOMMIT';
//设置AUTOCOMMIT状态
SET AUTOCOMMIT = 1;

    InnoDB采用两阶段锁定协议。在事务执行过程中可以随时执行锁定，在COMMIT或ROLLBACK时全部释放。InnoDB会根据隔离级别自动加锁。这种行为属于隐式锁定。InnoDB可以通过SQL执行显式锁定。服务器层实现的表锁（LOCK TABLE）和解表锁（UNLOCK TABLE）与存储引擎无关，不能替代事务操作。这两种服务层锁实现只有在AUTOCOMMIT禁用的情况下才可以使用，其他情况下都不建议使用。

// 显式锁SQL
SELECT ... LOCK IN SHARE MODE
SELECT ... FOR UPDATE

    名词解释：

    幻读：当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行。

    死锁：两个或者多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象。最终导致查询速度非常慢。产生的情况：①当多个事务试图以不同的顺序锁定资源时；②多个事务同时锁定同一个资源时。解决方法：数据库系统实现了各种死锁检测和死锁超时机制。InnoDB：回滚持有最少行级排他锁的事务。锁的行为和顺序是跟存储引擎紧密相关的，所以有的死锁完全可能是因为存储引擎实现锁的方式导致的，而有些则是真正的数据冲突。统一的解决方式就是部分或完全回滚其中一个事务。

    一次性锁协议：在事务开始时一次性获取所有锁，在事务结束时释放所有锁。有一个锁不能获取则事务进入等待阶段。这种方法并发性低。

    两阶段锁定协议：整个事务分为两阶段，前阶段为加锁，后阶段为解锁。在加锁阶段，事务只能加锁，可以操作数据，但不能解锁，直到所释放第一个锁就进入解锁阶段，此时事务只能解锁，也可操作数据，但不能加锁。这样的方式具有高并发性，但因为无顺序性执行，所以并未解决死锁问题。