高性能mysql学习笔记

此文已由作者朱笑天授权网易云社区发布。

欢迎访问网易云社区，了解更多网易技术产品运营经验。

笔者在工作之余阅读了一下高性能mysql，以下的内容对mysql的介绍以及书中涉及一些概念的总结归纳。

1.mysql架构

1.最上层负责链接处理、认证授权、安全等

2.中间一层涵盖了mysql的大多数核心功能。包括查询解析、分析、优化、缓存、内置函数；所有的夸存储引擎的功能都在这一层实现（存储过程、触发器、视图等）

3.第三层包含了存储引擎，存储引擎与上层使用API进行通讯，引擎之间不会有交互。

1.1连接层

    当MySQL启动，等待客户端连接，每一个客户端连接请求，服务器都会新建一个线程处理，每个线程独立，拥有各自的内存处理空间。

a.连接处理流程

    

b.认证流程

  连接到服务器，服务器需要对其进行验证，也就是用户名、IP、密码验证，一旦连接成功，还要验证是否具有执行某个特定查询的权限。

1.2sql处理层

    这一层主要功能有：SQL语句的解析、优化，缓存的查询，MySQL内置函数的实现，跨存储引擎功能，例如：存储过程、触发器、视图等。

a.执行过程：

    

1.如果是查询语句（select语句），首先会查询缓存是否已有相应结果，有则返回结果，无则进行下一步（如果不是查询语句，同样调到下一步）； 
2.解析查询，创建一个内部数据结构（解析树），这个解析树主要用来SQL语句的语义与语法解析；
3.优化：优化SQL语句，例如重写查询，决定表的读取顺序，以及选择需要的索引等。这一阶段用户是可以查询的，查询服务器优化器是如何进行优化的，便于用户重构查询和修改相关配置，达到最优化。这一阶段还涉及到存储引擎，优化器会询问存储引擎，比如某个操作的开销信息、是否对特定索引有查询优化等。

1.3 存储引擎

    存储引擎，主要用来存储数据的，不同的存储引擎采用不同的技术（存储机制、索引机制、锁定机制）存储数据，这主要是为了满足数据存储要求，比如有的数据不需要大量的改动，只用来查询，而有的数据则需要常常修改（数据插入、删除、更新），针对各种业务情况，为了更好的数据处理效率采用不同的数据存储技术（即不同存储引擎）。 
　　MySQL的存储引擎是插件式的，也就是说，用户可以随时切换MySQL的存储引擎：针对表或针对库都可（通过SQL语句命令）。这种灵活性也是为什么MySQL受到欢迎的一个重要原因。MySQL集合了多种引擎：MyISAM、InnoDB、BDB、Merge、Memory等，默认的是InnoDB（MySQL5.5开始，以前是MyISAM）。

2.并发控制

    MySQL是多线程应用，并且共享存储数据，很显然，当两个及以上线程对同一块数据进行写将会发生数据不一致等各种问题，比如，同时对一个表增加一条记录，后一个增加的记录可能会覆盖前一条，造成数据丢失。若仅仅是读不会发生错误，但是当读写一同，就有可能发生读错误，所以，对读也是需要必要的控制。

    以上问题就需要并发控制来解决，所谓的并发，就是每一次只允许一个线程对某一块数据（可以是某个数据库，或某张表，或表里某条记录）写，实现并发控制有多种方式，MySQL采用的是锁以及MVCC（多版本控制）。 

    a.读写锁

    MySQL提供了两种锁实现并发控制：读锁和写锁。读锁是共享的，也叫共享锁（也叫S锁），相互不会阻塞，多个读锁（多个线程用户）可以同一时刻读取统一资源；写锁则是排他的，也叫排他锁（也叫X锁），同一时间一个资源只能有一个写锁，也就是说，写锁会阻塞其他写锁和读锁。即读锁上面可以加读锁，但不能加写锁，而写锁则不能加任何锁。 

　　每次操作数据先判断该数据是否加锁，加了什么锁，然后以此判断是否允许本次操作执行，但这样是不是觉得很麻烦？很耗性能？所以才有了数据库事务隔离级别，统一设置一个隔离级别，数据库系统会根据隔离级别隐式的给数据加锁，然后根据这个级别来判断本次操作执行权限。

    b.乐观锁悲观锁

    悲观锁：操作前，悲观地认为所操作数据在操作期间会被其他事务修改，所以，在操作前我要先给我操作的数据加锁才放心。至于加的是读锁还是写锁则看具体应用场景。 

　乐观锁：操作前，乐观地认为所操作数据在操作期间不会被其他事务修改，只在最后更新的时候（如果操作是更新的话）查看原始数据是否被修改，如果没修改，更新数据，否则失败。至于如何知道原始数据被修改，这就是涉及到具体实现方式了，最常用的就是MVCC。

    理论上，尽量锁定需要修改的部分，而不是所有的数据，锁定的数据单元越小，系统的并发控制度越高，比如行级锁，修改的时候只锁定这一行记录，这个时候其他线程对该表的其他记录修改不影响。但是，加锁也是需要消耗资源的，锁的各种操作：获得锁、检查锁状态、释放锁等都会增加开销，越细粒度的锁开销越大，过多的所操作所带来的是性能急剧下降。 

　所以我们要采用一种锁策略来平衡并发度和系统性能，MySQL由于存储引擎的插件式，每个存储引擎可以实现自己的锁策略，所以不需要通用的锁策略，只需要在相应应用场景下选择相应的存储引擎即可。 

    表锁：

    MySQL最基本的锁策略，顾名思义，对整张表加读锁和写锁。一般由MySQL服务器层实现，如果这个时候存储引擎层还有锁，优先表锁。

   行级锁：

　更大细粒度的锁，只锁住一行记录，即对不同行记录可并发操作。行级锁只由存储引擎层实现，MySQL服务器层没有实现。

3.事务

    一个实现了事务处理系统（保证事务的ACID）的数据库，相比没有实现的，需要更强的CPU处理能力、更大的内存和更多的存储空间，但是有些场景是不太需要事务处理能力的，也就不需要具有事务处理能力的数据库（因为他们需要更大的资源）。 

    MySQL中InnoDB、NDB cluster存储引擎实现了事务功能，当然还有其他第三方存储引擎也实现了，默认下采用自动提交模式，即若不显式开始一个事务，每一个操作当做一个事务进行操作，可以通过设置AUTOCOMMIT变量来启用或禁用自动提交模式，如果禁用了则需显式执行COMMIT或ROLLBACK结束事务。

    下面针对connection_type值的不同做的一些实验：

create table test(a int, primary key (a))engine=innodb;

set @@completion_type=1; 

begin;

insert into test select 1;

commit work;

insert into test select 2;

insert into test select 2;

rollback;

得到如下结果：

    测试中，将completion_type设置成1，第一次通过commit work来insert这条记录。之后insert 2的时候并没有启用begin（start transaction）来开启一个事务，之后再插入一条重复的记录2，这时会抛出异常rollback后，最后发现只有1这样一条记录，2并没有被insert进去。因为completion_type为1的时候，commit work会开启另外一个事务，因此2个insert语句是在同一个事务里面的，所以回滚后就没有insert进去。

set @@completion_type=2;

begin;

insert into test select 3;

commit work;

select @@versison;

通过上面的测试发现，completion_type设置成2时，commit work之后，再通过select获取db服务器版本信息的时候出现2006的error，说明以及断开了与db的连接。

参数completion_type为2时，commit work等同于commit and release。当事务提交时候会自动断开与db的连接。

待续。。。

免费体验云安全(易盾)内容安全、验证码等服务

更多网易技术、产品、运营经验分享请点击。

相关文章：
【推荐】 如何有效的杜绝“羊毛党“的薅羊毛行为？