MySQL性能调优(2)存储引擎介绍、体系结构及运行机理

存储引擎介绍

• 插拔式的插件方式（存储引擎本身是数据库服务器的组件，负责对在物理服务器层面上维护的基本数据进行实际操作）
• 存储引擎是指定在表之上的，即一个库中的每一个表都可以指定专用的存储引擎
• 最新的MySQL 8.0 发布之后，对数据库数据字典方面做了较大的改进。

首先是，将所有原先存放于数据字典文件中的信息，全部存放到数据库系统表中，即将之前版本的.frm,.opt,.par,.TRN,.TRG,.isl文件都移除了，不再通过文件的方式存储数据字典信息。
其次是对INFORMATION_SCHEM，MySQL，sys系统库中的存储引擎做了改进，原先使用MyISAM存储引擎的数据字典表都改为使用InnoDB存储引擎存放。
从不支持事务的MyISAM存储引擎转变到支持事务的InnoDB存储引擎，为原子DDL的实现，提供了可能性。

1
2
3
4
1// 查看数据库当前支持的存储引擎

2show engines;

3

4

1
2
1 ![](http://img.5iqiqu.com/images7/03/0324a4380b10d35d21105018f402dcd2.png)  \#\#\#\# 存储引擎 \#\#\#\#\# CVS存储引擎 ![](http://img.5iqiqu.com/images7/f1/f1852b2848b74927b1fa2f03736ebe56.png) - 数据存储以CSV文件 - 特点： - 不能定义没有索引、列定义必须为NOT NULL、不能设置自增列 -->不适用大表或者数据的在线处理 - CSV数据的存储用,隔开，可直接编辑CSV文件进行数据的编排 -->数据安全性低 注：编辑之后，要生效使用flush table 表名 命令 - 应用场景： >数据的快速导出导入 >表格直接转换成CSV 

2

Archive存储引擎

• 压缩协议进行数据的存储

• 数据存储为ARZ文件格式

• 特点：

• 只支持insert和select两种操作

  • 只允许自增ID列建立索引
  • 行级锁
  • 不支持事务
  • 数据占用磁盘少

• 应用场景：

日志系统
大量的设备数据采集

• 我同时在Archive和innodb同结构表中插入1000000条数据:看看大小区别：这是只有一个id和name的情况下。如果字段更多的话，archive的压缩率区别就更大了。

Memory存储引擎

• 数据都是存储在内存中，IO效率要比其他引擎高很多

• 服务重启数据丢失，内存数据表默认只有16M

• 特点：

• 支持hash索引，B tree索引，默认hash（查找复杂度0(1)）

  • 字段长度都是固定长度varchar(32)=char(32)
  • 不支持大数据存储类型字段如 blog，text
  • 表级锁

• 应用场景：

等值查找热度较高数据
查询结果内存中的计算，大多数都是采用这种存储引擎作为临时表存储需计算的数据
不过现在基本上用缓存数据库了

Myisam存储引擎

• MySQL5.5版本之前的默认存储引擎

• 特点：

• select count(*) from table 无需进行数据的扫描

  • 数据（MYD）和索引（MYI）分开存储
  • 表级锁
  • 不支持事务

Innodb存储引擎

• 现在是数据库默认引擎

• 特点：

• 支持事务

  • 行级锁

  • 聚集索引（主键索引）存储，如果没有主键，那么会有唯一键来做索引，如果还没有就生成一个隐藏的主键索引。

  • 支持外键关系保证数据完整性（不过我现在开发中比较少用了。依开发项目实质性来取决于做不做外键关联。）

1
2
3
4
5
6
7
1为何说外键有性能问题：

21.数据库需要维护外键的内部管理；

32.外键等于把数据的一致性事务实现，全部交给数据库服务器完成；

43.有了外键，当做一些涉及外键字段的增，删，更新操作之后，需要触发相关操作去检查，而不得不消耗资源；

54.外键还会因为需要请求对其他表内部加锁而容易出现死锁情况；

6

7

对比

MVCC是多版本并发控制：后续单独分析；

MySQL体系结构及运行机理

• 先看看体系结构

1
2
3
4
5
6
7
8
9
10
11
12
1客户端连接器：JDBC,ODBC,.NET,PHP等等

2连接管理：

3    Connection Pool连接池：管理缓冲用户连接、用户名、密码、权限校验、线程处理等需要缓存的需求

4    Management Serveices & Utilities系统管理和控制工具，mysqldump、 MySQL复制集群、分区管理等

5SQL接口：接受用户的SQL命令，并且返回用户需要查询的结果

6SQL解析器:SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的

7查询优化器:SQL语句在查询之前会使用查询优化器对查询进行优化

8Cache和Buffer（高速缓存区）：如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据

9存储引擎：pluggable storage Engines 插件式存储引擎、存储引擎是MySQL中具体的与文件打交道的子系统

10系统管理：file system 文件系统，数据、日志（redo，undo）、索引、错误日志、查询记录、慢查询等

11

12