MySQL 引擎

1. 介绍

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

MySQL逻辑架构图

mysql index

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 索引机制

mysql index

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 索引机制

mysql index

4. 对比

/ MyISAM InnoDB
索引类型 非聚簇索引 聚簇索引
支持事务 否 是
支持表锁 是 是
支持行锁 否 是
支持外键 否 是
支持全文索引 是 是（5.6后支持）
适合操作类型 大量select 大量insert、delete、update

两种存储引擎各有各的有点，MyISAM专注性能，InnoDB专注事务。两者最大的区别就是InnoDB支持事务，和行锁。

如何在两种存储引擎中进行选择？

是否有事务操作？有，InnoDB。

是否存储并发修改？有，InnoDB。

是否追求快速查询，且数据修改较少？是，MyISAM。

是否使用全文索引？如果不引用第三方框架，可以选择MyISAM，但是可以选用第三方框架和InnDB效率会更高。