<commit_before>---
layout: page
title: 高性能MySQL
---

## 架构
他的架构设计把**查询处理以及系统任务**和**数据的存储和提取**相分离。

![架构图](https://i.loli.net/2020/05/25/r4DdlefLk1gFtqY.png)

### 并发控制
通过创建锁，来控制并发的资源占用问题。
#### 读写锁
读锁，也叫共享锁，互相不阻塞。
写锁，也叫排他锁，会阻塞其他的读锁和写锁。
#### 锁粒度
通过区分加锁的数据量，来提升系统的并发量。但是锁操作都会增加系统的开销。所以要在安全性和锁开销之间寻求一个平衡。
##### 两种重要的锁策略
表锁：开销最小的策略。一般采用mysql自己实现的表锁而忽略存储引擎的锁机制。
行锁：最大程度的并发处理，伴随最大的开销。且只在存储引擎中实现。

### 事务
四大特性：
* 原子性：要么全部成功，要么全部失败
* 一致性：从一个一致性转换成另一种一致性
* 隔离性：所做的改变在最终提交之前，对于其他事务来说是不可见的
* 持久性：事务提交之后改动不会丢失

#### 四种隔离级别
较低级别的隔离通常可以执行更高的并发，开销也更低。
* read uncommitted（未提交读）：很少使用，问题很多
* read uncommitted（提交读/不可重复读）：大多数数据库系统的默认级别（Mysql不是），其他事务执行两次相同的读可能得到不一样的结果。
* repeatable read（可重复读）：MySQL默认隔离级别。解决脏读问题，会有幻行的问题（多了一行）。Mysql已经解决了这一问题。
* serializable（串行）：强制事务串行执行。对读取的所有行都加锁。

避免不可重复读锁行就行
避免幻读锁表就行

#### 多版本并发控制MVCC
行级锁的变种，很多情况下避免了加锁操作，开销更低。

InnoDB的MVCC通过在每一行后面保存两个隐藏的列来实现，一个是行的创建时间，一个是行的过期时间（版本号）

### 引擎
#### InnoDB
数据存储在表空间中。表是基于聚簇索引建立的。
#### MyISAM
不支持事务和行级锁，崩溃后无法恢复。

MYD和MYI文件存储，分别存储数据文件和索引文件。

<commit_msg>update LeetCode<commit_after>---
layout: page
title: 高性能MySQL
---

## 架构
他的架构设计把**查询处理以及系统任务**和**数据的存储和提取**相分离。

![架构图](https://i.loli.net/2020/05/25/r4DdlefLk1gFtqY.png)

### 并发控制
通过创建锁，来控制并发的资源占用问题。
#### 读写锁
读锁，也叫共享锁，互相不阻塞。
写锁，也叫排他锁，会阻塞其他的读锁和写锁。
#### 锁粒度
通过区分加锁的数据量，来提升系统的并发量。但是锁操作都会增加系统的开销。所以要在安全性和锁开销之间寻求一个平衡。
##### 两种重要的锁策略
表锁：开销最小的策略。一般采用mysql自己实现的表锁而忽略存储引擎的锁机制。
行锁：最大程度的并发处理，伴随最大的开销。且只在存储引擎中实现。

### 事务
四大特性：
* 原子性：要么全部成功，要么全部失败
* 一致性：从一个一致性转换成另一种一致性
* 隔离性：所做的改变在最终提交之前，对于其他事务来说是不可见的
* 持久性：事务提交之后改动不会丢失

#### 四种隔离级别
较低级别的隔离通常可以执行更高的并发，开销也更低。
* read uncommitted（未提交读）：很少使用，问题很多
* read uncommitted（提交读/不可重复读）：大多数数据库系统的默认级别（Mysql不是），其他事务执行两次相同的读可能得到不一样的结果。
* repeatable read（可重复读）：MySQL默认隔离级别。解决脏读问题，会有幻行的问题（多了一行）。Mysql已经解决了这一问题。
* serializable（串行）：强制事务串行执行。对读取的所有行都加锁。

避免不可重复读锁行就行
避免幻读锁表就行

#### 多版本并发控制MVCC
行级锁的变种，很多情况下避免了加锁操作，开销更低。

InnoDB的MVCC通过在每一行后面保存两个隐藏的列来实现，一个是行的创建时间，一个是行的过期时间（版本号）

### 引擎
#### InnoDB
数据存储在表空间中。表是基于聚簇索引建立的。
#### MyISAM
不支持事务和行级锁，崩溃后无法恢复。

MYD和MYI文件存储，分别存储数据文件和索引文件。


## 服务器性能剖析
性能优化并不是降低cpu利用率、也不是提升每秒查询量
### 应用程序的性能剖析
对任何需要消耗时间的任务都可以进行性能剖析。性能瓶颈可能有很多的影响因素：
* 外部资源。调用了外部的服务或者搜索引擎
* 应用处理大量的数据，分析一个超大规模的XML文件
* 在循环中执行昂贵的操作，滥用正则表达式
* 使用了低效的算法

性能剖析会导致服务器变慢，但是为了定位一些性能平静下来，还是值得的，可以通过采样的方式发现严重问题。
### 关于MySQL的查询
#### 整体负载
**慢查询日志**
记录执行时间超过某一值的语句。是开销最低、精度最高的测量查询时间的工具。
分析慢查询日志时，先使用pt-query-digest生成一个剖析报告，它能够提供日志的一个整体的概览，接下来再去定为精确所在之处也不迟。
![pt-query-digest](https://i.loli.net/2020/05/28/eIOhlKNU8JgpwX5.png)

#### 单条查询优化
* `show profile`：服务器上执行的所有语句都会测量器耗费的时间
* `show status`：返回全局计数器，也有某个会话级别的计数器。显示某些活动的频繁程度。
* 慢查询日志
* `performance schema`：5.5之后会变得很强大，待考证。

#### 如何诊断间歇性问题
使用`show global status`/`show process list`/`查询日志`确定是单条查询还是服务器问题
{'fraction_non_alphanumeric': 0.14677177177177178, 'fraction_numerical': 0.012012012012012012, 'mean_word_length': 9.833333333333334, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 3, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '3943223', 'n_tokens_mistral': 2263, 'n_tokens_neox': 2680, 'n_words': 118}