MySQL 高性能编程学习（1）—架构与特性

1. MySQL的应用场景

    Mysql具有高灵活性。应用于Web类应用，可嵌入到程序中，也可支持数据仓库、内容索引和部署软件、高可用的冗余系统、在线事务处理系统等。

   Mysql的灵活性在于：

       通过配置使它在不同的硬件上运行，支持多种不同的数据结构

   最大特性：

       存储引擎架构。将查询处理(Query Processing）及其他系统任务（Server Task)和数据的存储/提取想分离。可以根据性能、特性和需求来选择合适的数据存储的方式。

2. MySQL服务器的逻辑架构

   （1）第一层【连接层】：大多数基于网络的客户端/服务器的工具或者服务都有类似架构。如连接处理、授权认证、安全管理等。

    (2) 第二层【SQL处理层】 ：提供核心服务功能。

        包含查询解析、分析、优化、缓存以及所有的内置函数（日期、时间、数字、加密函数）。

        所有跨存储引擎的功能也都在这一层实现：存储过程、触发器、试图。

   （3）第三层【存储引擎层】 ：存储引擎负责MySQL中数据的存储于提取。服务器通过接口API函数与存储引擎进行通信，接口屏蔽了不同引擎之间差异。

         每种引擎都有自己的优势和不足。存储引擎不会去解析SQL，不同存储引擎之间不会相互通信，只是简单响应上层服务器的请求。

       注：InnoDB是个例外，它会解析外键定义。

 2.1 连接管理与安全性。

    ---每个客户端连接，对应于服务器中的一个线程。这个连接的查询只会在该线程中独立执行。该线程只能轮流在某个CPU核心中运行。

    ---服务器负责缓存线程，不需要为每个新建的客户端连接创建或者销毁线程。 版本 >= MySQL 5.5 ,提供了一个支持线程池插件的API，可以使用线程

池中少量的线程来服务大量的连接。

    ====客户端连接到mysql服务器是，服务器需要对其进行认证。认证基于用户名、原始主机信息和密码。如果使用了安全套接字(SSL）的方式，则还可使用

X.5.09证书认证。一旦连接成功，服务器继续验证该客户端是否据用执行某个特定查询的权限。

  2.2 优化与执行

     MySQL会解析查询，并创建内部数据结构（解析树），再进行各种优化（重写查询、决定表的读取顺序、选择合适的索引等）。

  --可通过关键字提示(hint)优化器，影响其决策过程；可请求优化器解释（explain）优化过程的各个因素，获得服务器是如何进行优化决策的，提供一个参考基准。

     优化器不关心表使用的是什么存储引擎，但是存储引擎对于优化查询有影响。优化器会请求存储引擎提供容量或某个具体操作的开销信息、表数据的统计信息。

  ---对于select语句，在解析查询前，服务器先检查查询缓存(Query Cache），若找到对应的查询，服务器就不必在执行查询解析和执行，而是直接返回结果。   

3. 并发控制

   解决多个查询对共享资源的互斥访问（读取和修改）。——锁机制

   MySQL的并发控制：服务器层 + 存储引擎层

   

  3.1 读写锁

  --读写锁其实是共享互斥锁。同一时刻，允许多个查询线程同时进行读操作（读锁），写线程被阻塞；只能有一个线程进行写操作（写锁），其他读线程、写线程都会被阻塞。

    

  3.2 锁粒度

     提高共享资源并发性的一种方式：让锁定对象更有选择性，尽量只锁定需要修改的部分数据，而不是所有的资源。

     在任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互不发生冲突即可。

   ----锁问题：需要消耗资源，增加系统开销。

    

  3.3 锁策略

    ---在锁的开销与数据的安全性之间寻求平衡。每种MySQL存储引擎都可实现自己的锁策略和锁粒度。

   （1）表锁（table lock） ：

      表锁是最基本的锁策略，且实开销最小的策略。表锁会锁定整张表。对表进行写操作（插入、删除、更新）前，需要先获得写锁，阻塞其他所有用户对其的读、写操作；只有

没有写锁时，其他用户才能获得读锁，读锁之间不互相阻塞。

     READ LOCAL 表锁支持某些类型的并发写操作；  写锁比读写优先级更高，一个写锁请求可能会被插入到读锁队列的前面；   

     服务器会为诸如alter table 之类语句使用表锁，而忽略存储引擎的锁机制。

   （2）行级锁(row lock):

      最大程度地支持并发处理（但却带来了最大的锁开销）。InnoDB和XtraDB以及其他一些存储引擎中实现了行级锁。行级锁只在存储引擎层实现，而MySQL服务器层没有实现。

4. 事务---实现类似于撤销、回退的功能

    事务就是一组原子性的SQL查询、或说一个独立的工作单元。如果能对数据库应用该组查询的全部语句，就执行该组查询。若其中有任何一条语句因为奔溃或其他原因无法执行，那么所有的都不会执行；回滚该组所有操作。

    

  

    SCID测试：

          原子性（atomicity） + 一致性（consistency） + 隔离性（isolation） + 持久性（durability）

    (1)原子性：

      一个事务视为一个不开分割的额最大工作单元；要么所有操作去不提交成功，要么全部失败回滚。

       

   （2）一致性：

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

   （3）隔离性：

      一个事务所做的修改在最终提交之前，对其他事务是不可见的。

      在上面银行应用中，当执行完第3条语句，第4条语句还没开始时，此时有另外一个账户汇款程序开始运行，则其看到的支票账户余额

并没有减去200美元。

   （4）持久性：

      一旦事务提交，则其所做的修改就会永久保存到数据库中。持久性分不同的级别，不可能做到100%的持久性保证的策略。

     事务处理增加了安全性，但会增加系统的开销（需要更强的CPU处理能力、更大的内存和更多的磁盘空间）。

   用户根据业务是否需要事务需求，来选择合适的存储引擎。

4.1 隔离级别

      每种存储引擎实现的隔离级别不尽相同。较低级别的隔离通常可以执行更高的并发，系统的开销也更低。

    分为四种隔离级别：

   （1）READ UNCOMMITTED(未提交读)——脏读

      事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，称为脏读。

      该级别的性能不太好，会导致很多问题，实际应用中很少使用。

   （2）READ COMMITTED(提交读）——不可重复读

      大多数数据库系统的默认隔离级别都是READ COMMITTED，但MySQL不是。该级别中，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。

      两次执行同样的查询，可能得到不一样的结果（不可重复读）

   （3）REPEATABLE READ（可重复读）——MySQL的默认事务隔离级别

       可重复性读解决了脏读的问题，保证了在同一个事务中多次读取同样记录的结果是一致的。但无法解决幻读问题。

       【幻读】（Phantom Read）：当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，就会产生幻行（Phantom Row）.

        InnoDB和XtraDB存储引擎通过多版本并发控制(MVCC,multiversion concurrency control)解决幻读问题。

   （4）SERIALIZABLE（可串行化）——最高的隔离级别

       通过强制事务串行执行，避免了幻读问题。serializable会在读取的每一行数据上都加锁，可能导致大量的超时和锁竞争问题。

       实际应用中很少使用这个级别，只有在非常需要确保数据一致性而且可以接受没有并发的情况下，考虑。

    

4.2 死锁

           

   InnoDB目前处理死锁的方法：

      将持有最少行级排他锁的事务进行回滚。

   锁的行为和顺序是和存储引擎相关的。死锁的产生有两大原因：真正的数据冲突；由于存储引擎的实现方式导致。

   大部分情况下，只需要重新执行因死锁回滚的事务即可。

4.3 事务日志

   使用事务日志，存储引擎在修改表的数据时只需要修改器内存拷贝（而不直接修改原始磁盘数据），再把该修改行为记录到持久在硬盘上的事务

日志中，事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘。

   事务日志采用追加的方式，写日志的操作是磁盘上一小块区域内的顺序I/O，因此采用事务日志的方式相对来说比较快。

   大多数存储引擎都采用事务日志，称为预写式日志(write-ahead logging)，修改数据需写两次磁盘。 

   

   若数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统奔溃，存储引擎在重启时能自动恢复这部分修改的数据。

4.4 MySQL中的事务

   MySQL提供两种事务型的存储引擎：InnoDB和NDB Cluster。  包括第三方存储引擎：XtraDB和PBXT.

  自动提交(AUTOCOMMIT）

      MySQL默认采用自动提交模式。如果不是显示地开始一个事务，则每个查询都被当作一个事务执行提交操作。

      在当时连接中，可以通过设置AUTOCOMMIT变量来启用或者禁用自动提交模式。

      

      当AUTOCOMMIT=0时，所有的查询都是在一个事务中，直到显式地执行COMMIT提交或ROLLBACK回滚，该事务结束，同时又开始了另一个新事务。

      修改AUTOCOMMIT对非事务型的表，比如MyISAM或者内存表，不会有任何影响。对这类表来说，没有COMMIT或者ROLLBACK的概念，相当于一直处于AUTOCOMMIT启用的模式。

     在数据定义语言（DDL)中，如果是会导致大量数据改变的操作（如ALTER TABLE）还有LOCK TABLES等其他语句，需要在执行之前强制执行COMMIT

提交当前的活动事务。

     MySQL可通过执行 SET TRANSACTION ISOLATION LEVEL 命令来设置隔离级别。新的隔离级别会在下一个事务开始的时候生效。

     可在配置文件中设置整个数据库的隔离级别，也可只改变当前回话的隔离级别：

     mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED

    【在事务中混合使用存储引擎】

        MySQL服务器层不管理事务，事务是由下层的存储引擎实现的。在同一个事务中，使用多种存储引擎是不可靠的。

    【隐式和显示锁定】

       InnoDB采用的是两阶段锁定协议。在事务执行过程中，随时都可以执行锁定；锁只有在执行COMMIT或ROLLBACK的时候才会释放，并且所有锁

是在同一时刻被释放。前面描述的锁都是隐式锁定，InnoDB会根据隔离级别在需要的时候自动加锁。

       MySQL支持LOCK TABLES 和 UNLOCK TABLES语句，是在服务器层实现的，和存储引擎无关。

  ------除了事务中禁用了AUTOCOMMIT，可以使用LOCK TABLES之外，其他任何时候都不要显式执行LOCK TABLES，不管使用的是什么存储引擎。

   

5. 多版本并发控制

     MySQL的大多数事务型存储引擎实现的都不是简单的行级锁，同时实现了多版本并发控制（MVCC）。

     MVCC是行级锁的一个变种，但它在很多情况下避免了加锁操作，开销更低；大都实现了非阻塞的读操作，写操作也只锁定必要的行。

     

     MVCC是通过保持数据在某个时间点的快照来实现的。不管需要执行多长时间，每个事务看到的数据都是一致的。

     根据事务开始时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。

     典型有乐观并发控制和悲观并发控制

     

  InnoDB的MVCC

    通过在每行记录后面保存两个隐藏的列实现。一个列保存了行的创建时间，一个列保存行的过期时间（或删除时间）。存储的并不是实际的实际的时间值，而是系统版本号。

    每开始一个新事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

  在repeatable read隔离级别下，mvcc具体操作如下：

  （1）SELECT

     InnoDB会根据以下两个条件检查每行记录

     a. InnoDB只查找版本早于当前事务版本的数据行（行的系统版本号小于或等于事务的系统版本号），这样可以保证事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。

     b, 行的删除版本要么未定义，要么大于当前事务版本号。这可保证事务读取到的行，在事务开始之前未被删除。

    只有符号上述两个条件的记录，才能返回作为查询结果。

  （2）INSERT

     InnoDB为新插入的每一行保存当前系统版本号作为行版本号。

  （3）DELETE

     InnoDB为删除的每一行保存当前系统版本号作为行删除标识。

  （4）UPDATE

     InnoDB为插入一行新记录，保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为行删除标识。

 

  保存着两个额外的系统版本号，使大多数读操作都可以不用加锁。

  优点：使得读数据操作简单，性能好，而且能保证只会读取到符合标准的行

  缺点：每行记录都需要额外的存储空间，需要做更多的行检查工作，以及额外的维护工作。

  MVCC只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作。

   

6. MySQL的存储引擎