一、MySql逻辑架构
1.连接层
2.SQL服务层
-
组成部分
（1）Sql Interface: 处理sql命令的接收及结果的返回 （2）Parser: 解析器，解析sql （3）Optimizer: 优化器，优化sql （4）Caches&Buffer: 缓存
-
查询命令的执行流程
-
解析器
-
优化器
explain SQL查询语句 ：查看查询计划
- 缓存
sql的缓存是默认的，数据缓存需要手动开启(在my.cnf(linux)文件中配置)
1// 查看缓存是否开启 2show variables like '%query_cache_type%' 3// 设置缓存大小 4SET GLOBAL query_cache_size = 4000; 5SET GLOBAL query_cache_size = 134217728;
3. 引擎层
1mysql> show engines; 2+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 3| Engine | Support | Comment | Transactions | XA | Savepoints | 4+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 5| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | 6| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | 7| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | 8| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | 9| MyISAM | YES | MyISAM storage engine | NO | NO | NO | 10| CSV | YES | CSV storage engine | NO | NO | NO | 11| ARCHIVE | YES | Archive storage engine | NO | NO | NO | 12| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | 13| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL | 14+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+ 159 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)
1mysql> create table t2(id int,name varchar(100)); 2Query OK, 0 rows affected (0.03 sec) 3 4mysql> show variables like '%datadir%'; 5+---------------+-----------------+ 6| Variable_name | Value | 7+---------------+-----------------+ 8| datadir | /var/lib/mysql/ | 9+---------------+-----------------+ 101 row in set (0.00 sec)
1[root@192 ~]# cd /var/lib/mysql/ 2[root@192 mysql]# ll 3....... 4drwxr-x---. 2 mysql mysql 90 Jul 5 05:04 test 5[root@192 mysql]# cd test 6[root@192 test]# ll 7total 128 8....... （ /var/lib/mysql/test） 9-rw-r-----. 1 mysql mysql 8586 Jul 5 05:04 t2.frm 10-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)
1mysql> create table t3(id int,name varchar(100)); 2Query OK, 0 rows affected (0.01 sec) 3 4[root@192 test]# pwd 5/var/lib/mysql/test 6[root@192 test]# ll 7...... 8-rw-r-----. 1 mysql mysql 8586 Jul 5 05:08 t3.frm 9[root@192 test]# cd ../ 10[root@192 mysql]# ll 11total 188488 12...... 13-rw-r-----. 1 mysql mysql 79691776 Jul 5 05:09 ibdata1 14...... 15[root@192 mysql]# pwd 16/var/lib/mysql -
（3）系统表空间和独立表空间区别
- 系统表空间无法简单的收缩表空间的大小
- 独立表空间可以通过optimize tableName收缩文件大小
- 系统表空间会产生IO瓶颈
- 独立表空间可以向多个文件刷新数据
-
（4）特性
- Innodb是一种事务性存储引擎 2.完全支持事务ACID的特性
- Redo log和Undo log
- Innodb支持行级锁（并发程度更高）
-
（5）适合场景
有事务处理的业务（OLTP）
-
（6）MyISAM与Innodb的比较
对比项
MyISAM
InnoDB
主外键
不支持
支持
事务
不支持
支持
行表锁
表锁，即使操作一条记录也会锁住整个表，不适合高并发的操作行
行锁,操作时只锁某一行，不对其它行有影响；适合高并发的操作
缓存
只缓存索引，不缓存真实数据
不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间
小
大
关注点
性能
事务
默认安装
Y
Y
4 存储层
数据文件及日志文件