Mysql高性能学习笔记-01

 

Mysql高性能学习笔记-01

 

刘岩

 

Blog:suhuanzheng7784877.iteye.com

 

1.Mysql的架构

Mysql整体的架构模块如下图所示

 

 

模块1：连接客户端，这个一般是指mysql基于mysql通讯协议的驱动程序。比如mysql的Java的JDBC驱动程序——mysql-connector-java-5.1.26-bin.jar。再比如.net平台的操作数据库的规范ADO。

 

模块2：连接池，Mysql自身有个连接池，为了连接线程的复用，Mysql将其进行了池化。将连接资源进行了池化，使得连接线程可以复用。（mysql 5.5以后）

 

模块3：SQL接口，接受SQL命令后，调用SQL接口，执行并返回相应的结果集。

 

模块4：SQL解析引擎，SQL语句需要进行解析。将SQL语句解析成为抽象语法树。从中也要识别SQL语句是否合法。

 

模块5：优化引擎，抽象成语法树后，就会对该命令进行一系列的对用户来说透明的优化措施——查询语句可能重新构造；目标表的读取顺序；

 

模块6：缓存以及缓冲引擎，对于select语句，解析sql语句之前。mysqld先检查查询缓存区域，如果能够在其中找到对应的查询，服务器不会继续解析，而是直接返回查询缓存的内容直接返回。

 

模块7：可插拔的存储引擎，常用的存储引擎：innodb、myisam、memroy。存储引擎可以根据业务需求，随意进行变更。

 

2.Mysql中的锁机制

 

表锁：

 

表锁是读写锁的一种实现，而mysqll的表锁是在服务层，也就是在存储引擎的上层，也是mysql最基本的锁策略。读锁之间不会相互阻塞，但是一旦遇到了写锁，所有其他的锁请求都会被阻塞住。而且写锁的执行优先级会高于读锁（双向队列可以实现非FIFO的场景）。

 

行锁：

 

行锁是存储引擎对于mysql基本锁策略的扩展，行锁定是存储引擎才具备的特性。最典型的行级锁存储引擎是innodb。而此时就又引出了innodb对于行锁的实现，也就是该引擎实现了MVCC——多版本并发控制。

 

在mysql的MVCC实现中，基本原则就是实现非阻塞的读取操作；写操作的时候仅仅锁定必要的目标行。

 

从MVCC的目标就可以基本猜出，它使用空间换取的时间。

 

简要概述：innodb的MVCC机制是在每个表每条记录的后面隐藏两个字段，第一个隐藏字段是该行的创建时间，第二个字段代表改行的删除时间。注意：这里的时间是指的系统版本号码。新的事务开启，该行版本号自动递增。事务开始的系统版本号需要和每行记录的版本号进行对比。在innodb中只有隔离级别是：repeatable read和read committed两个隔离级别才支持MVCC机制。

默认事务隔离级别——repeatable read对于CRUD的MVCC实现机制是

插入操作：为每一个新增的行记录，新增的记录隐藏字段-创建时间保存当前事务。

删除操作：为每一条目标记录保存为当前事务系统版本号，存储到删除时间字段中。

更新操作：插入一条新记录，保存当前事务版本号作为创建字段，并且将事务版本号保存到原来的行的删除字段中。

最麻烦的查询操作：事务的每次读取会读取创建字段中<=自身事务版本的记录，确保读取的行是自己创建的或者是事务开始前已经存在的。对于删除字段呢，本身事务会读取大于自身事务或者该删除字段并未有任何值的记录，确保在本身事务开始之前未被删除的事务被读取到。

 

 

 

我们将查询操作事务和其他操作的事务进行一个简单的结合来进行分析，即：查询->插入、查询->删除、查询->更新。

 

查询->插入：首先说明，若是先进行插入事务（id为1），那么新增记录的创建字段为1，删除字段为NULL。后执行的查询事务（id为2），那么该条记录被查询事务作为有效记录返回给结果集，因为该记录的创建字段满足<=查询事务的id，而该条记录的删除字段又是空。

 

若是查询事务先执行，而插入事务后执行的话，那么。根据MVCC的规则，此新增的记录，并不会返回给结果集，因为查询事务的id要小于插入事务的id。

 

查询->删除：删除事务先于查询事务执行，那么删除事务的id将保存到该目标记录的删除字段上，而查询事务查询到此记录时，因为，该记录的删除事务小于查询事务，那么此信息不会被作为有效地结果返回给结果集。比较麻烦的是查询事务优于删除事务，先执行查询事务，而删除事务又很慢，还未改变该记录的删除字段的值，就执行了查询事务，那么查询事务会将此记录返回给结果集，因为该记录的删除字段的值小于该查询事务的id。那么返回到客户端后，客户端程序又想对此无效的记录做操作，而后续执行的删除事务，很给力，执行完毕后，该记录就是“幽灵记录”。那么此时应当在客户端程序中，对“幽灵记录”进行严密的防范措施——比如无效记录的异常信息提示等等。

 

查询->修改：这个结合了查询->新增、查询->删除这两个用例。当查询事务优于更新事务的时候，自不必说，查询出来的记录保持着原有的数据。当更新事务优于查询事务执行的时候，过程如下。更新事务将一条新的纪录插入到表中，将新纪录的创建字段赋值为自身事务id，并将自身事务id赋值给原记录，那么查询事务执行的时候，会查找低于自身事务id的记录，新增记录符合条件的，原始记录的删除字段的值小于查询事务自身的id，不会作为结果集记录返回的。

下图是每种操作事务类型对于那两个隐藏字段的关注点示意图。

 

 

 

3.Mysql的MyISAM与InnoDB的大致对比

 

因为后续还会对Mysql这两个重要的引擎继续讨论，那么咱们在这里先大致看一下这两个引擎的大致区别。后续更加细粒度化的区别，还是继续会讨论的。

 

评论

8 楼 cuishuangjia 2014-04-17  

DBTOOLS支持ORACLE，MYSQL，SQLSERVER，POSTGRE，DB2数据库相互转换功能
目前支持中文和日文
功能：
1。将数据库中的表结构和数据保存到EXCEL中。
2。将EXCEL中的数据，同步到数据库中。
3。当表结构发生变化时，数据不会丢失。
4。程序执行前后，表中的数据发生了哪些变化
5。根据EXCEL中的表结构，生成建表语句SQL文。
6。多用户使用该软件时，可以随时记录某个用户对数据库的操作。
7。可以为进行压力测试，自动生成数据。
8。导出表结构，根据表结构和数据库中的表结构进行差分。
9。导出DB结构，和现有环境DB进行表结构差分
10。导出用户自定义表结构，根据表结构导入，导入数据前对EXCEL校验进行数据库验证
和业务逻辑验证
11。单体测试，结合测试解决方案。

作者邮件：[e-mail]
企鹅群：数据库第三方工具交流  184715368


DbTools的YouTu展示视频
http://youtu.be/jfRwDUpPRLw

百度网盘下载地址：
http://pan.baidu.com/s/1pCjPt
（包含有视频讲解文件）

目前测试中，免费可获得永久注册码，基本满足工作中的对数据库操作的需求。
ITEYE博客：http://cuishuangjia.iteye.com/

7 楼 whereqinghuang 2014-04-17  

更期待您的《品程序人生》系列，好久不更新啦，久违了

6 楼 wentao_tang 2014-04-17  

期待后续文章

5 楼 huafengyu_jin 2014-04-16  

学习了，期待您这个系列的后续章节。

4 楼 focus2008 2014-04-16  

顶一个，写的很好。期待后续文章。。。

3 楼 Ronbay 2014-04-16  

写的不错

2 楼 suhuanzheng7784877 2014-04-16  

colver 写道

拜读

过奖，这还远远不够！后续会有更多东西的。

1 楼 colver 2014-04-16  

拜读