【Mysql高级】你对Mysql是怎样优化的

ゝ一纸荒年。 2023-02-17 12:25 17阅读 0赞

## 一、Mysql体系结构 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 1][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 2][]

![20200614072657458.png][]

## 二、存储引擎 ##

存储数据，建立索引，更新数据等技术的实现方式。

基于表，不是基于库的。

查看数据库的存储引擎

show engines;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 3][]

![20200614073136752.png][]

### 各种存储引擎的特性 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 4][]

## InnoDB ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 5][]

### 存储方式 ###

表结构+数据

![20200614075138548.png][]

## MyISAM ##

### 不支持事务 ###

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 6][]

### 存储方式 ###

![20200614075115746.png][]

## MEMORY ##

存放于内存中，对应一个磁盘文件格式为.frm。

有利于数据快速处理，但是内存资源有限，服务器关闭数据会丢失。

## MERGE ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 7][]

## 存储引擎的选择 ##

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 8][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200614072553650.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/2020061407262250.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200614072638370.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[20200614072657458.png]: https://img-blog.csdnimg.cn/20200614072657458.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/20200614072952974.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[20200614073136752.png]: https://img-blog.csdnimg.cn/20200614073136752.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200614073325878.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200614074823219.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[20200614075138548.png]: https://img-blog.csdnimg.cn/20200614075138548.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200614075027686.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[20200614075115746.png]: https://img-blog.csdnimg.cn/20200614075115746.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 7]: https://img-blog.csdnimg.cn/20200614075622436.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ_size_16_color_FFFFFF_t_70 8]: https://img-blog.csdnimg.cn/20200614075755893.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3pneTk1NjY0NTIzOQ==,size_16,color_FFFFFF,t_70