③解析器(Parser)将客户端发送的SQL进行语法解析,生成"解析树"。预处理器根据一些MySQL
规则进一步检查“解析树”是否合法,例如这里将检查数据表和数据列是否存在,还会解析名字和别
名,看看它们是否有歧义,最后生成新的“解析树”。
④查询优化器(Optimizer)根据“解析树”生成最优的执行计划。MySQL使用很多优化策略生成最
优的执行计划,可以分为两类:静态优化(编译时优化)、动态优化(运行时优化)。
-
等价变换策略
-
5=5 and a>5 改成 a > 5
-
a < b and a=5 改成b>5 and a=5
-
基于联合索引,调整条件位置等
-
优化count、min、max等函数
-
InnoDB引擎min函数只需要找索引最左边
-
InnoDB引擎max函数只需要找索引最右边
-
MyISAM引擎count(*),不需要计算,直接返回
-
提前终止查询
-
使用了limit查询,获取limit所需的数据,就不在继续遍历后面数据
-
in的优化
-
MySQL对in查询,会先进行排序,再采用二分法查找数据。比如where id in (2,1,3),变
成 in (1,2,3)
⑤查询执行引擎负责执行 SQL 语句,此时查询执行引擎会根据 SQL 语句中表的存储引擎类型,以
及对应的API接口与底层存储引擎缓存或者物理文件的交互,得到查询结果并返回给客户端。若开
启用查询缓存,这时会将SQL 语句和结果完整地保存到查询缓存(Cache&Buffer)中,以后若有
相同的 SQL 语句执行则直接返回结果。
-
如果开启了查询缓存,先将查询结果做缓存操作
-
返回结果过多,采用增量模式返回
存储引擎在MySQL的体系架构中位于第三层,负责MySQL中的数据的存储和提取,是与文件打交道的
子系统,它是根据MySQL提供的文件访问层抽象接口定制的一种文件访问机制,这种机制就叫作存储引
擎。
使用show engines命令,就可以查看当前数据库支持的引擎信息。
在5.5版本之前默认采用MyISAM存储引擎,从5.5开始采用InnoDB存储引擎。
-
InnoDB:支持事务,具有提交,回滚和崩溃恢复能力,事务安全
-
MyISAM:不支持事务和外键,访问速度快
-
Memory:利用内存创建表,访问速度非常快,因为数据在内存,而且默认使用Hash索引,但是
一旦关闭,数据就会丢失
-
Archive:归档类型引擎,仅能支持insert和select语句
-
Csv:以CSV文件进行数据存储,由于文件限制,所有列必须强制指定not null,另外CSV引擎也不
支持索引和分区,适合做数据交换的中间表
-
BlackHole: 黑洞,只进不出,进来消失,所有插入数据都不会保存
-
Federated:可以访问远端MySQL数据库中的表。一个本地表,不保存数据,访问远程表内容
-
MRG_MyISAM:一组MyISAM表的组合,这些MyISAM表必须结构相同,Merge表本身没有数据,
对Merge操作可以对一组MyISAM表进行操作。
InnoDB和MyISAM对比
InnoDB和MyISAM是使用MySQL时最常用的两种引擎类型,我们重点来看下两者区别。
- 事务和外键
InnoDB支持事务和外键,具有安全性和完整性,适合大量insert或update操作
MyISAM不支持事务和外键,它提供高速存储和检索,适合大量的select查询操作
- 锁机制
InnoDB支持行级锁,锁定指定记录。基于索引来加锁实现。
MyISAM支持表级锁,锁定整张表。
- 索引结构
InnoDB使用聚集索引(聚簇索引),索引和记录在一起存储,既缓存索引,也缓存记录。
MyISAM使用非聚集索引(非聚簇索引),索引和记录分开。
- 并发处理能力
MyISAM使用表锁,会导致写操作并发率低,读之间并不阻塞,读写阻塞。
InnoDB读写阻塞可以与隔离级别有关,可以采用多版本并发控制(MVCC)来支持高并发
- 存储文件
InnoDB表对应两个文件,一个.frm表结构文件,一个.ibd数据文件。InnoDB表最大支持64TB;
MyISAM表对应三个文件,一个.frm表结构文件,一个MYD表数据文件,一个.MYI索引文件。从
MySQL5.0开始默认限制是256TB。
- 适用场景
MyISAM
-
不需要事务支持(不支持)
-
并发相对较低(锁定机制问题)
-
数据修改相对较少,以读为主
-
数据一致性要求不高
InnoDB
-
需要事务支持(具有较好的事务特性)
-
行级锁定对高并发有很好的适应能力
-
数据更新较为频繁的场景
-
数据一致性要求较高
-
硬件设备内存较大,可以利用InnoDB较好的缓存能力来提高内存利用率,减少磁盘IO
总结
-
两种引擎该如何选择?
-
是否需要事务?有,InnoDB
-
是否存在并发修改?有,InnoDB
-
是否追求快速查询,且数据修改少?是,MyISAM
-
在绝大多数情况下,推荐使用InnoDB
-
扩展资料:各个存储引擎特性对比
InnoDB存储结构
从MySQL 5.5版本开始默认使用InnoDB作为引擎,它擅长处理事务,具有自动崩溃恢复的特性,在日
常开发中使用非常广泛。下面是官方的InnoDB引擎架构图,主要分为内存结构和磁盘结构两大部分。
一、InnoDB内存结构
内存结构主要包括Buffer Pool、Change Buffer、Adaptive Hash Index和Log Buffer</