mysql系列(三)——mysql架构与存储引擎

一、MySql逻辑架构

1.连接层

mysql系列(三)——mysql架构与存储引擎

2.SQL服务层

组成部分

（1）Sql Interface: 处理sql命令的接收及结果的返回（2）Parser: 解析器，解析sql （3）Optimizer: 优化器，优化sql （4）Caches&Buffer: 缓存
查询命令的执行流程
解析器
优化器

explain SQL查询语句：查看查询计划

缓存

sql的缓存是默认的，数据缓存需要手动开启(在my.cnf(linux)文件中配置)

//  查看缓存是否开启
show variables like '%query_cache_type%'
// 设置缓存大小
SET GLOBAL query_cache_size = 4000;
SET GLOBAL query_cache_size = 134217728;

3. 引擎层

mysql> show engines;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)

MyISAM

（1）MyISAM是Mysql5.5之前建表的默认引擎
（2）MyISAM创建表示创建的文件有三种如下案例

mysql> create table t1(id int)ENGINE=MyISAM;

Query OK, 0 rows affected (0.00 sec)

[root@192 mysql]# ll ...... drwxr-x---. 2 mysql mysql 62 Jul 5 01:47 test [root@192 mysql]# cd test/ [root@192 test]# ll total 20 -rw-r-----. 1 mysql mysql 65 Jul 5 01:32 db.opt -rw-r-----. 1 mysql mysql 8556 Jul 5 01:47 t1.frm -rw-r-----. 1 mysql mysql 0 Jul 5 01:47 t1.MYD -rw-r-----. 1 mysql mysql 1024 Jul 5 01:47 t1.MYI

.frm文件: 存储表结构的文件，任何引擎都有的文件 .MYD文件: 存储库数据的文件 .MYI文件: 存储索引的文件
(3)特性

--并发性及锁级别：表锁 --支持全文索引 --支持数据压缩 myisampack -b -f tableName.MYI
(4)适合场景

--支持非事务场景（数据仓库、报表、日志数据） --只读类应用 --空间类应用（空间函数、坐标）

Innodb

（1）Innodb是Mysql5.5以后建表的默认引擎
（2）innodb_file_per_table

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

1 row in set (0.00 sec)

----ON : 独立表空间（Mysql5.6及以后默认独立表空间）

----OFF : 系统表空间

案例一：验证独立表空间(独立表空间在目录下生成".frm"文件和".idb"文件) mysql> use test; Database changed mysql> set global innodb_file_per_table=1; Query OK, 0 rows affected (0.00 sec) mysql> create table t2(id int,name varchar(100)); Query OK, 0 rows affected (0.03 sec) mysql> show variables like '%datadir%'; +---------------+-----------------+ | Variable_name | Value | +---------------+-----------------+ | datadir | /var/lib/mysql/ | +---------------+-----------------+ 1 row in set (0.00 sec)
```
[root@192 ~]# cd /var/lib/mysql/
[root@192 mysql]# ll
.......
drwxr-x---. 2 mysql mysql       90 Jul  5 05:04 test
[root@192 mysql]# cd test
[root@192 test]# ll
total 128
....... （ /var/lib/mysql/test）
-rw-r-----. 1 mysql mysql  8586 Jul  5 05:04 t2.frm
-rw-r-----. 1 mysql mysql 98304 Jul  5 05:04 t2.ibd
```
案例二：验证系统表空间(在/var/lib/mysql 下生成共有的文件ibdataX,在/var/lib/mysql/test 下生成.frm文件) mysql> use test Database changed mysql> set global innodb_file_per_table=0; Query OK, 0 rows affected (0.00 sec)
```
mysql> create table t3(id int,name varchar(100));
Query OK, 0 rows affected (0.01 sec)

[root@192 test]# pwd
/var/lib/mysql/test
[root@192 test]# ll
......
-rw-r-----. 1 mysql mysql  8586 Jul  5 05:08 t3.frm
[root@192 test]# cd ../
[root@192 mysql]# ll
total 188488
......
-rw-r-----. 1 mysql mysql 79691776 Jul  5 05:09 ibdata1
......
[root@192 mysql]# pwd
/var/lib/mysql
```
（3）系统表空间和独立表空间区别
1. 系统表空间无法简单的收缩表空间的大小
2. 独立表空间可以通过optimize tableName收缩文件大小
3. 系统表空间会产生IO瓶颈
4. 独立表空间可以向多个文件刷新数据
（4）特性
1. Innodb是一种事务性存储引擎
2.完全支持事务ACID的特性 3. Redo log和Undo log 4. Innodb支持行级锁（并发程度更高）
（5）适合场景

有事务处理的业务（OLTP）
（6）MyISAM与Innodb的比较