MySQL 引擎
1. 介绍¶
存储引擎在MySQL的逻辑架构中位于第三层,负责MySQL中的数据的存储和提取。MySQL存储引擎有很多,不同的存储引擎保存数据和索引的方式是不同的。每一种存储引擎都有它的优势和劣势,本文只讨论最常见的InnoDB和MyISAM两种存储引擎进行讨论。本文中关于数据存储形式和索引的可以查看
MySQL逻辑架构图

1.1 连接器¶
连接器负责跟客户端建立连接,获取权限、维持和管理连接.
- 用户名密码验证
- 查询权限信息,分配对应的权限
- 可以使用show processlist查看现在的连接
- 如果太长时间没有动静,就会自动断开,通过wait_timeout控制,默认8小时
连接可以分为两类,
- 长连接:推荐使用,但是要周期性的断开长连接.
- 短链接
1.2 查询缓存¶
当执行查询语句的时候,会先去查询缓存中查看结果,之前执行过的sql语句及其结果可能以key-value的形式存储在缓存中,如果能找到则直接返回,如果找不到,就继续执行后续的阶段,但是,不推荐使用查询缓存
- 查询缓存的失效比较频繁,只要表更新,缓存就会清空
- 缓存对应新更新的数据命中率比较低
1.3 分析器¶
词法分析:Mysql需要把输入的字符串进行识别每个部分代表什么意思,根据语法规则判断这个sql语句是否满足mysql的语法,如果不符合就会报错You have an error in your SQL synta
.
1.4 优化器¶
在具体执行SQL语句之前,要先经过优化器的处理
- 当表中有多个索引的时候,决定用哪个索引
- 当sql语句需要做多表关联的时候,决定表的连接顺序等等
不同的执行方式对SQL语句的执行效率影响很大
- RBO:基于规则的优化
- CBO:基于成本的优化[比较多]
2. InnoDB存储引擎¶
InnoDB是默认的事务型存储引擎,也是最重要,使用最广泛的存储引擎。在没有特殊情况下,一般优先使用InnoDB存储引擎
2.1 数据存储形式¶
使用InnoDB时,会将数据表分为.frm 和 idb两个文件进行存储。
(base) root@leco:/var/lib/mysql/cmz# ll leco* -rw-r----- 1 mysql mysql 8656 Feb 28 2019 leco.frm 存储表结构 -rw-r----- 1 mysql mysql 98304 Mar 6 2019 leco.ibd 存储表的数据和索引
2.2 锁的粒度¶
InnoDB采用MVCC(多版本并发控制)来支持高并发,InnoDB实现了四个隔离级别,默认级别是REPETABLE READ,并通过间隙锁策略防止幻读的出现。它的锁粒度是行锁。
2.3 事务¶
InnoDB是典型的事务型存储引擎,并且通过一些机制和工具,支持真正的热备份。
2.4 数据的存储特点¶
InnoDB表是基于聚簇索引建立的,聚簇索引对主键的查询有很高的性能,不过他的二级索引(非主键索引)必须包含主键列,索引其他的索引会很大。
2.5 索引机制¶

3. MyISAM存储引擎¶
3.1 数据存储形式¶
MyISAM采用的是索引与数据分离的形式,将数据保存在三个文件中.frm.MYD,.MYIs。
建表指定数据库引擎
use test; create table test( id int(10) unsigned not null auto_increment, name varchar(10) character set utf8, age int(10), primary key(id) )engine=MyISAM; mysql> use test; Database changed mysql> use test;create table test(id int(10) unsigned not null auto_increment,name varchar(10) character set utf8,age int(10),primary key(id))engine=MyISAM; Database changed Query OK, 0 rows affected (0.02 sec) mysql> desc test; +-------+------------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+------------------+------+-----+---------+----------------+ | id | int(10) unsigned | NO | PRI | NULL | auto_increment | | name | varchar(10) | YES | | NULL | | | age | int(10) | YES | | NULL | | +-------+------------------+------+-----+---------+----------------+ 3 rows in set (0.02 sec) mysql> show create table test; +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(10) CHARACTER SET utf8 DEFAULT NULL, `age` int(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 | +-------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec)
2018/09/10 12:35 10,684 user.frm 存储表结构 2018/09/10 12:35 296 user.MYD 存储表数据 2018/09/10 12:35 2,048 user.MYI 存储表引擎
3.2 锁的粒度¶
MyISAM不支持行锁,所以读取时对表加上共享锁,在写入是对表加上排他锁。由于是对整张表加锁,相比InnoDB,在并发写入时效率很低。
3.3 事务¶
MyISAM不支持事务。
3.4 数据的存储特点¶
MyISAM是基于非聚簇索引进行存储的。
3.5 索引机制¶

4. 对比¶
/ | MyISAM | InnoDB |
---|---|---|
索引类型 | 非聚簇索引 | 聚簇索引 |
支持事务 | 否 | 是 |
支持表锁 | 是 | 是 |
支持行锁 | 否 | 是 |
支持外键 | 否 | 是 |
支持全文索引 | 是 | 是(5.6后支持) |
适合操作类型 | 大量select | 大量insert、delete、update |
两种存储引擎各有各的有点,MyISAM专注性能,InnoDB专注事务。两者最大的区别就是InnoDB支持事务,和行锁。
如何在两种存储引擎中进行选择?
是否有事务操作?有,InnoDB。
是否存储并发修改?有,InnoDB。
是否追求快速查询,且数据修改较少?是,MyISAM。
是否使用全文索引?如果不引用第三方框架,可以选择MyISAM,但是可以选用第三方框架和InnDB效率会更高。