MySQL体系结构
标签:MySQL

MySQL体系结构

客户端(各种可以通过MySQL协议连接到MySQL的客户端,主要做连接处理,授权,安全认证等,连接到MySQL的客户端的都会在服务器拥有一个线程,该连接的操作只能在这个线程中执行,即每个连接只能用到一个CPU核心)

MySQL服务层(定义了各种查询语句,各种跨存储引擎的功能都在这一层来实现的,实现了与存储引擎无关的特性,如Select语句就是查询出数据,而具体的实现方式则是由下一层来实现的)

存储引擎层 (MySQL定义了一系列的存储引擎的接口,只要符合该接口要求就可以自己实现一个存储引擎,如innodb,myisam,csv等,根据不同的应用特点选择不同的存储引擎,存储引擎是针对于表而不是针对于库的,一个库中的不同表可以使用不同的存储引擎)

1. MyISAM

MySQL5.5之前版本默认存储引擎。大部分系统表和临时表(在排序,分组等操作中,当数量超过一定的大小顺序之后,由查询优化器建立的临时表)使用的存储引擎。

MyISAM存储引擎表由.MYD(数据文件)和.MYI(索引文件)组成。

1.1 特性

  1. MyISAM使用的是表级锁而不是行级锁,故在对表中数据进行修改的时候要对整个表进行加锁。对表中数据读取的时候也要加共享锁,故对读写的并发性支持的不太好。
  2. MyISAM可以对任意意外关闭的表进行检查和修复,整个修复不是事务恢复,因为MyISAM不是一种事务存储引擎,没有事务的相关日志,故对表进行修复的时候可能会造成数据的丢失。
#对表进行检查
check table tablename;
mysql> check table myIsam;
+--------------+-------+----------+----------+
| Table        | Op    | Msg_type | Msg_text |
+--------------+-------+----------+----------+
| study.myIsam | check | status   | OK       |
+--------------+-------+----------+----------+

#对表进行修复
repair table tablename;
mysql> repair table myIsam;
+--------------+--------+----------+----------+
| Table        | Op     | Msg_type | Msg_text |
+--------------+--------+----------+----------+
| study.myIsam | repair | status   | OK       |
+--------------+--------+----------+----------+
  1. MyISAM表支持全文索引

  2. MyISAM表支持数据压缩 ,使用myisampack进行压缩,由于是对数据进行单独压缩,读取单行数据不必对整个表进行解压。压缩后的表只能进行读操作不能进行写操作。

1.2 限制

  1. 版本小于MySQL5.0时,默认表的大小的为4G,要使用大表要修改 MAX_RowsAVG_ROW_LENGTH
  2. 版本大于MySQL5.0时默认支持为256TB

1.3 适用场景

  1. 非事务型应用
  2. 只读类应用
  3. 空间类应用,如存GPS数据等

2. Innodb

MySQL5.5之后版本默认的存储引擎。Innodb使用表空间进行数据存储。

设置 innodb_file_per_table 如果为ON则独立表空间存储:tablename.ibd,为OFF则系统表空间存储:ibdataX

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
mysql> set global innodb_file_per_table=off; #关闭
Query OK, 0 rows affected (0.01 sec)

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+

系统表空间和独立表空间如何选择:

如何把原来存在于系统表空间中标转移到独立表空间中的方法:

  1. 使用mysqldump导出所有数据库表数据
  2. 停止mysql服务,修改参数,并删除Innodb相关文件
  3. 重启mysql服务,重建Innodb系统表空间
  4. 重新导入数据

2.1 特性

  1. 事务:

  2. Innodb是一种事务性存储引擎

  3. 完全支持事务的ACID特性

  4. Redo Log(已经提交的事务)和Undo Log(未提交的事务)

    1. Innodb支持行级锁
    2. 行级锁可以最大程度的支持并发
    3. 行级锁是由存储引擎层实现的
  5. 状态检查

    show engine Innodb status;
    

什么是锁:

  1. 锁的主要作用是管理共享资源的并发访问
  2. 锁用于实现事务的隔离性

锁的类型:

锁的粒度:


阻塞与死锁

阻塞:是操作在等待另一个其他操作释放锁而发生了等待,死锁:是连个操作占用了对方的资源。

2.2 使用场景

Innodb适合于大多数OLTP应用,在MySQL5.7后也支持全文索引和空间函数了。

3. CSV

3.1 特点

  1. 数据以文本方式存储在文件中,以CSV格式进行数据存储
    1. .csv文件存储表内容
    2. .csm文件存储表的元数据如表状态和数据量
    3. .frm文件存储表结构信息
  2. 所有列必须都是不能为null的,建表的时候必须给所有字段添加not null
  3. 不支持索引的,故不适合大表,不适合在线处理
  4. 可以对数据文件直接编辑

3.2 适用场景

适合作为数据交换的中间表

4. Archive

4.1 特点

  1. 以zlib对表数据进行压缩,磁盘I/O更少
  2. 数据存储在ARZ为后缀的文件中
  3. 只支持insert和select操作
  4. 只允许在自增ID列上加索引

4.2 适用场景

日志和数据采集类应用

5. Memory

也称HEAP存储引擎,所有数据保存在内存中,一旦重启数据全部丢失,只有表结构保留下来

2. 总结

存储引擎 事务 锁粒度 主要应用 忌用
MyISAM 不支持 支持并发插入的表级锁 SELECT,INSERT 读写操作频繁
Innodb 支持 支持MVCC的行级锁 事务处理
Archive 不支持 行级锁 日志记录,只支持insert,select 需要随机读取,更新,删除
  • 6 min read

CONTRIBUTORS


  • 6 min read