## MySQL 

### 





![](https://guide-blog-images.oss-cn-shenzhen.aliyuncs.com/java-guide-blog/5e3c1a71724a38245aa43b02_99bf70d46cc247be878de9d3a88f0c44.png)

 SQL (ACID)

****

MySQLPostgreSQLOracleSQL ServerSQLite SQLite ......

### MySQL 

![](https://img-blog.csdnimg.cn/20210327143351823.png)

**MySQL **

 MySQL MySQL  GPL(General Public License) MySQL **3306**

## 

### 

** MySQL **

```sql
mysql> show engines;
```

![MySQL](https://my-blog-to-use.oss-cn-beijing.aliyuncs.com/2019-6/mysql-engines.png)

 MySQL  InnoDB, 5.7  InnoDB  InnoDB 

** MySQL **



```sql
mysql> show variables like '%storage_engine%';
```

****

```sql
show table status like "table_name" ;
```

![](https://my-blog-to-use.oss-cn-beijing.aliyuncs.com/2019-6/.png)

### MyISAM  InnoDB 

![](https://img-blog.csdnimg.cn/20210327145248960.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzM0MzM3Mjcy,size_16,color_FFFFFF,t_70)

MySQL 5.5 MyISAM  MySQL 

MyISAM MyISAM 

5.5 MySQL  InnoDBMySQL 5.5  InnoDB InnoDB  MySQL 



**1.**

MyISAM (table-level locking) InnoDB (row-level locking),

MyISAM  InnoDB 

**2.**

MyISAM 

InnoDB (commit)(rollback)

**3.**

MyISAM  InnoDB 

 



**4.**

MyISAM  InnoDB 

 InnoDB  `redo log` 

 

- MySQL InnoDB  **redo log()** **** **undo log()** ****
- MySQL InnoDB  ******MVCC**   **`REPEATABLE-READ`** 
- 

**5. MVCC**

MyISAM  InnoDB 

 MyISAM 

MVCC 

###  MyISAM  InnoDB 

 InnoDB  MyISAM  MyISAM ~

MySQL :

> MyISAM  InnoDB InnoDB  MyISAM 

 InnoDB  MyISAM 

 MyISAM  MySQL 

##  InnoDB 

**MyISAM  InnoDB **

- MyISAM (table-level locking)
- InnoDB (row-level locking),

****

- **** MySQL  **** MyISAM  InnoDB 
- **** MySQL  ****  

**InnoDB **

- Record lock
- Gap lock
- Next-key lockrecord+gap 

## 

MySQL 8.0 

`my.cnf`  MySQL 

```properties
query_cache_type=1
query_cache_size=600000
```

MySQL 

```properties
set global  query_cache_type=1;
set global  query_cache_size=600000;
```

****MySQL 

MySQL 

****  MB ** sql_cache  sql_no_cache **

```sql
select sql_no_cache count(*) from usr;
```

## 

### 

****

****

 1000 

1.  1000 
2.  1000 





### 



********

****

 SQL **,** 

```sql
# 
START TRANSACTION;
#  SQL 
SQL1,SQL2...
## 
COMMIT;
```

![](https://guide-blog-images.oss-cn-shenzhen.aliyuncs.com/2020-12/640-20201207160554677.png)

`MySQL``SQL Server``Oracle`  **ACID** 

![](https://my-blog-to-use.oss-cn-beijing.aliyuncs.com/2019-6/%E4%BA%8B%E5%8A%A1%E7%89%B9%E6%80%A7.png)

###  ACID 

1. ****`Atomicity`  
2. ****`Consistency` 
3. ****`Isolation` 
4. ****`Durabilily` 

****

 MySQL  InnoDB 

MySQL InnoDB  **redo log()** **** **undo log()** ****

MySQL InnoDB  ******MVCC**   **`REPEATABLE-READ`** 



### ?



- **Dirty read:** 
- **Lost to modify:**   1  A=20 2  A=20 1  A=A-1 2  A=A-1 A=19 1 
- **Unrepeatableread:** 
- **Phantom read:** T1T2T1

****



### ?

SQL 

- **READ-UNCOMMITTED()** ****
- **READ-COMMITTED()** ****
- **REPEATABLE-READ()** ****
- **SERIALIZABLE()**  ACID ****

---

|          |  |  |  |
| :--------------: | :--: | :--------: | :--: |
| READ-UNCOMMITTED |     |           |     |
|  READ-COMMITTED  |     |           |     |
| REPEATABLE-READ  |     |           |     |
|   SERIALIZABLE   |     |           |     |

### MySQL ?

MySQL InnoDB  **REPEATABLE-READ**`SELECT @@tx_isolation;`MySQL 8.0 `SELECT @@transaction_isolation;`

```sql
mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
```

~~ SQL  InnoDB  **REPEATABLE-READ**  Next-Key Lock ( SQL Server) InnoDB  **REPEATABLE-READ**  SQL  **SERIALIZABLE()** ~~

 **MySQL InnoDB  REPEATABLE-READ Next-Key Locks**

 **READ-COMMITTED()**  InnoDB  **REPEAaTABLE-READ** 

InnoDB  ****  **SERIALIZABLE()** 

 (MySQL InnoDB ( 2 )7.7 )

> InnoDB  XA  XA transactional resources ACID InnoDB  SERIALIZABLE

## 

-  MySQL

- https://www.omnisci.com/technical-glossary/relational-database