MySQL SQ调优系列之逻辑架构简介

本是古典何须时尚 2021-08-13 22:21 420阅读 0赞

### 文章目录 ###

*   *  一、逻辑结构简介
     *  二、连接管理
     *  三、解析优化查询
     *  四、锁机制
     *   *  4.1 读锁和写锁
         *  4.2 锁粒度
     *  五、MySQL事务
     *   *  5.1 事务特性
         *  5.2 隔离级别
         *  5.3 事务日志
     *  六、MySQL的存储引擎

最近在看《高性能MySQL》一书，并做了笔记，整理成博客，分享出来：

## 一、逻辑结构简介 ##

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]  
Mysql服务器逻辑架构图，图来自《高性能MySQL》一书：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1]  
第一层结构主要处理客户端与mysql服务端的连接、授权认证、安全等；第二层是Mysql服务端的核心，功能包括查询解析、分析、优化、缓存等，存储过程、触发器、视图等都在这一层实现；第三层的存储引擎主要负责数据存储和提取，存储引擎不会去解析sql，不同存储引擎之间不会通讯，只会简单地响应上层服务器的请求

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 2]

## 二、连接管理 ##

每个客户端连接成功，都会在服务器进程中拥有一个线程，服务器会缓存线程，该线程只能轮流在某个CPU中运行，所以不需要创建和销毁线程

## 三、解析优化查询 ##

对于SELECT查询语句，解析查询之前会先查询缓存，如果缓存能找到是不会去解析的，如果缓存查找不到，就会重现解析查询，创建解析树，然后对其进行查询优化、决定表的读取顺序、选择合适的索引等

## 四、锁机制 ##

数据库引进锁机制是为了控制并发，举个例子，电商抢购案例，加入某类商品的库存只有一件了，然后这时候有两个用户在抢这个商品，按照逻辑，一个用户购物成功，库存就应该减1的，然后事务并发没控制好，这种情况是很容易出现商品没库存了，然后还可以下单的案例，这种是不合理的，这时候可以用数据库的锁机制来做，一个用户在下单时，锁存商品库存的表，操作成功后才释放锁，另外一个用户才可以操作

### 4.1 读锁和写锁 ###

然后介绍一下mysql的读锁(read lock)和写锁(write lock)，读锁也可以称作共享锁(shared lock)，写锁称为排他锁(exclusive lock)，读锁是共享的，是允许多个用户同个时刻同时读取同个资源的，写锁是排他的，这也是出于安全策略考虑的，写锁会阻塞其它的读锁和写锁，然后操作完成后才释放锁，接着其它的锁才可以操作

### 4.2 锁粒度 ###

锁按照读写可以分为读锁和写锁，按照加锁的粒度就可以分为表锁(table lock)和行锁(row level lock)

*  表锁：是MySQL中最基本的锁策略，也是开销最小的策略，表锁就是锁住整张表，用户对数据表进行写操作(更新、删除、新增等)时，会获得写锁，然后因为加了表锁，所以其他用户是不可以对表进行读写操作的，只有等写锁释放之后
 *  行锁：行锁是可以最大程度保证并发处理，不过锁开销也是最大的。并不是所有的库引擎都可以支持行锁的，InnoDB是可以支持的，不过MyISAM不支持的

## 五、MySQL事务 ##

### 5.1 事务特性 ###

提到事务肯定会想到事务的4个特性ACID，要保证业务的正常使用，必须保证ACID，ACID表示原子性(atomicity)、一致性(consistency)、隔离性(isolation)、持久性(durability)，一个运行良好的事务系统也是要求具备这些特征

*  原子性(atomicity)：一个事务必须被视为一个不可分割的最小工作单位，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，不能只执行一部分操作
 *  一致性(consistency)：一致性要求数据库总是从一个一致性的状态转换为另外一个一致性的状态，比如银行转账的例子，一个用户转账账号里减了200元，另外一个收账账号必须增加，一个失败数据必须全部回退状态，要保证事务一致性
 *  隔离性(isolation)：一般来说，一个事务所做的修改在提交之前对其它事务来说都是不可见的
 *  持久性(durability)：事务一旦提交，所做的状态就会永久保存在数据库中，即使系统奔溃，修改的数据也不会丢

### 5.2 隔离级别 ###

SQL标准中定义四种隔离级别，每种存储引擎实现的隔离级别是不同的

*  READ UNCOMMITTED(未提交读)  
    在READ UNCOMMITTED级别，事务即使没提交，对其它事务也是可见，允许事务读取未提交的数据，这也被称为脏读(Dirty Read)，所以在实际生产中很少用
 *  READ COMMIT(提交读)  
    大部分数据库系统的默认隔离级别都是READ COMMIT，很明显这种隔离级别只能读取到已经提交事务的数据
 *  REPEATABLE READ(可重复读)  
    REPEATABLE READ可以解决脏读的问题，不过不能解决幻读问题，所谓幻读是指，在读取某个范围数据时候，另外一个事务又向这个范围写数据了，当之前事务再次读取这个范围数据时候就会产生幻读
 *  SERIALIZABLE(可串行化)  
    SERIALIZABLE是最高的隔离级别，SERIALIZABLE强制事务串行执行，可以避免幻读问题，其实保证SERIALIZABLE是常用行锁的方式来保证一致性的，SERIALIZABLE会在每一行的数据上都加上锁，SERIALIZABLE虽然可以保证事务安全性，不过服务器性能不好也会导致锁争用问题

查看当前会话隔离级别

SELECT @@tx_isolation;

查看系统全局隔离级别

SELECT @@global.tx_isolation;

设置当前会话隔离级别：

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

设置mysql系统全局隔离级别：

SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;

### 5.3 事务日志 ###

事务日志可以用于提高事务的效率，有了事务日志，数据存储引擎在修改表数据时就只需要修改其内存拷贝，然后再将修改的行为数据持久化到硬盘，而不用修改数据本身再持久化到硬盘。事务日志采用的是追加的方式

注意：Mysql中能支持事务的存储引擎只有两种：InnoDB和NDB Cluster。Mysql中默认采用自动提交的模式，oracle写数据的话，都是需要自己手动提交事务的，而mysql不需要，因为默认的是自动提交事务的

SQL查询，1或者ON表示启动，0或者OFF表示关闭

SHOW VARIABLES LIKE 'AUTOCOMMIT';

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 3]

可以通过SQL来开启关闭自动提交：

SET AUTOCOMMIT =1;

**多版本控制**  
MySQL支持事务的存储引擎有InnoDB和NDB Cluster，而这些存储引擎实现事务一致性并不是通过简单的行级锁，因为前面也说到，行级而且可以很好的保证事务一致性，但是性能并不是很好，会导致锁竞争的情况，所以mysql这些事务型的存储引擎采用的是多版本并发控制(MVCC)，多版本并发控制可以说是行级锁的变种，但是多版本并发控制相对的开销是比较低的，多版本控制是通过保存在某个时间点的快照信息来实现的，InnoDB是通过在每行记录后面保存两个隐藏的列来实现的，这两个列，一个保存行的创建时间，一个保存行的过期时间，当然不是具体的时间值，而是系统版本号(system version number)，每开始一个事务，系统版本号都会自动递增的

## 六、MySQL的存储引擎 ##

Mysql的事务回滚等处理是给存储引擎负责的，下面介绍一下几种常见的MySQL存储引擎

*  InnoDB存储引擎  
    InnoDB存储引擎是mysql默认的存储引擎，也是使用最广泛的存储引擎，前面也说了，InnoDB是一种可以支持事务的存储引擎，InnoDB是mysql 5.1才开始出现的，之前名称是称为InnoDB plugin，直到被oracle收购mysql后，才正式命名为InnoDB。InnoDB采用MVCC来支持事务一致性和并发，并且实现了4个基本的隔离级别，默认级别是PREATABLE READ(可重复读)，并且通过间隙锁(next-key loking)策略来防止欢读的出现，
 *  MyISAM存储引擎  
    在MySQL5.1之前版本，MyISAM是默认的存储引擎，MyISAM提供了全文索引、压缩、空间函数等，但是MyISAM是不支持事务和行级锁的，MyISAM会将表存储在两个文件中，数据文件和索引文件，拓展名分别是.MYD和.MYI，MyISAM虽然没有行级锁，不过是可以支持表锁的，所以在一些业务系统还是可以做到并发控制的

来自尚硅谷老师归纳的图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 4]

*  Archive存储引擎  
    Archive存储引擎只支持INSERT和SELECT操作，在Mysql5.1版本之前不支持索引的，Archive引擎会缓存所有的写数据，利用zlib对写入的数据进行压缩，所以需要的磁盘/IO比MyISAM更少的，但是SELECT是全表扫描的，因此Archive表适合用于日志和数据采集类的应用，Archive引擎是支持行级锁的
 *  Blackhole存储引擎

Blackhole引擎没有存储机制，不做保存处理，但是服务器还是会记录Blackhole表的日志，所以可以用于复制数据到备库，或者用于记录日志

*  CSV存储引擎  
    CSV引擎可以将普通的CSV文件作为MySql的表处理，可以在数据库运行时拷贝文件，将Excel表另存为CSV文件，然后就可以直接复制到数据库，不过这种表是不支持索引的
 *  Federated存储引擎  
    Federated引擎是访问其它服务器的代理，会 创 建 一 个 到 远 程 MySQL 服 务 器 的 客 户 端 连 接， 并 将 查 询 传 输 到 远 程 服 务 器 执 行， 然 后 提 取 或 者 发 送 需 要 的 数 据。
 *  Memory存储引擎  
    Memory引擎适用与需要快速访问数据，这些数据是保存在内存的，Memory支持hash索引，所以查询速度非常快，不过不支持BLOB或者TXT类型数据，是用表锁做并发的
 *  Merge存储引擎  
    Merge引擎是由多个MyISAM表合并而来的表，可以用于存储日志或者用于当数据存库
 *  NDB集群存储引擎  
    这是Mysql推出的可以用于集群的存储引擎，提供了SQL和NDB协议之间的接口，原先是收购了NDB数据库，然后发展来的
 *  自定义存储引擎  
    Mysql的存储引擎是支持可以自己开发和修改的，比如阿里的mysql就不是用默认InnoDB

图来自尚硅谷老师归纳：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 5]

修改存储引擎SQL:

ALTER TABLE mytable ENGINE = InnoDB;

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70]: /images/20210813/b6c75c9b3cca45d59994e53b0fb8368e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 1]: /images/20210813/4d09dd70529841169b5d170676c0e775.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 2]: /images/20210813/50883bb79482485bba6d2a19b951c82b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 3]: /images/20210813/8a5c4f51591d41be9d57cc52e0141991.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 4]: /images/20210813/89520a5ef8074b6a8d26a0a7c7471be5.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9zbWlsZW5pY2t5LmJsb2cuY3Nkbi5uZXQ_size_16_color_FFFFFF_t_70 5]: /images/20210813/a6fc3184c6ad476ab0b5a0f13f605bc8.png