数据库存储引擎
服务层
第二层服务层是MySQL的核心,MySQL的核心服务层都在这一层,查询解析,SQL执行计划分析,SQL执行计划优化,查询缓存。以及跨存储引擎的功能都在这一层实现:存储过程,触发器,视图等。通过下图来观察服务层的内部结构
连接管理器
连接管理器的作用是管理和维持所有MySQL客户端的请求连接,当我们向MySQL发起请求时,连接管理器会负责创建连接并校验用户的权限。
对于已经建立的连接,如果没有太久没有发送请求,连接管理器会自动断开连接,我们可以通过设置变量wait_timeout决定多久断开不活跃的连接。
查询缓存
当我们与连接器建立连接后,如果我们执行的是SELECT语句,那么连接器会先从查询缓存中查询,看看之前是否执行过这条语句,如果没有再往走,如果有则判断相应的权限,符合权限,则直接返回结果。
查询缓存其实是把查询语句当作一个key,查询结果当用value,建立起来的key-value缓存结构。
不过,当数据表的数据发生变化时,其所对应的查询缓存则会失败,因此很多时候往往不能命中查询缓存,所以一般建议不要使用查询缓存。
可能MySQL官方团队也意识到查询缓存的作用不大,在MySQL 8.0版本中已经将查询缓存的整块功能删掉了,所以如果你用的是MySQL 8.0的版本,查询缓存的功能就不存在了。
解析器
当在查询缓存中没有命令查询时,则需要真正执行语句,这时候就交给解析器先进行词法分析,对我们输入的语句进行拆解,折解后再进行语法分析,判断我们输入的语句是不是符合MySQL的语法规则,如果输入的语句不符合MySQL语法规则,则停止执行并提示错误。
比如我们输入下面的错误语句:
select * users
由于上面的语句少了from,所以会返回下面的错误提示:
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘users’ at line 1
查询优化器
我们输入的语句,经过分析器的词法和语法分析,MySQL服务器已经知道我们要查询什么了,不过,在开始查询前,还要交由查询优化器进行优化。
在优化的过程,优化器会根据SQL语句的查询条件决定使用哪一个索引,如果有连接(join),会决定表的查询顺序,最终会根据优化的结果生成一个执行计划交由下面的执行器去执行。
执行器
SQL语句在经过查询优化器的优化后,接下来就交由执行器开始执行,不过执行器在开始执行前,会判断用户对相应的数据表是否有权限。
如果用户有权限,则开始调用数据,与其数据库不同的,MySQL的数据存储与调用交由存储实现,当我们调用时,执行器通过存储引擎API向底层的存储发送相应的指令,存储引擎负责具体执行,并将执行结果告诉执行器,然后再返回给客户端。
存储引擎层
负责MySQL中数据的存储与提取。 服务器中的查询执行引擎通过API与存储引擎进行通信,通过接口屏蔽了不同存储引擎之间的差异。MySQL采用插件式的存储引擎。MySQL为我们提供了许多存储引擎,每种存储引擎有不同的特点。我们可以根据不同的业务特点,选择最适合的存储引擎。如果对于存储引擎的性能不满意,可以通过修改源码来得到自己想要达到的性能。例如阿里巴巴的X-Engine,为了满足企业的需求facebook与google都对InnoDB存储引擎进行了扩充。
查看存储引擎
show engines;
MySQL引擎之MyISAM
MySQL5.5之前的版本默认存储引擎
系统表(安装mysql后系统自带的),临时表默认使用的存储引擎。这个临时表是指,在排序、分组等操作中,当数量超过一定的大小之后,由查询优化器建立的临时表
- 注意:MyISAM存储引擎表由MYD(数据文件)和MYI(索引文件)组成
什么是锁?
锁主要作用是管理共享资源的并发访问
锁用于实现事务的隔离性
锁的类型
共享锁(也称读锁),针对同一份数据,多个读操作可以同时进行而不会互相影响
独占锁(也称写锁),当前写操作没有完成前,它会阻断其他写锁和读锁
锁的粒度
表级锁
行级锁
MyISAM存储引擎特性
1.并发性与锁级别
2.表损坏修复
3.MyISAM表支持数据压缩(索引文件进行压缩,但是压缩后不能进行修改了,查询速度会更快)
myisampack -b -f myIsam.MYI
一般将已经结束的年度日志表进行压缩。
MyISAM存储引擎限制
- 版本 < MySQL5.0时默认表大小为4G,如存储大表则要修改MAX_Rows和- AVG_ROW_LENGTH
- 版本 > MySQL5.0时默认支持256TB
适合场景
1.非事务型应用
2.只读类应用,如果系统读多,写少,对原子性要求低,那么 MyISAM 时最好的选择
测试myisam,innodb读取数据
mysqlslap --concurrency=100 --iterations=3 --number-int-cols=5 --number-char-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --number-of-queries=10 --create-schema=test --auto-generate-sql-load-type read -uroot -proot
MySQL引擎之Innodb
MySQL5.5 及之后版本默认存储引擎,支持事务的ACID特性
Innodb使用表空间进行数据存储
innodb_file_per_table
ON:独立表空间,tablename.ibd
OFF:系统表空间:ibdataX X是一个数字
系统表空间和独立表空间如何选择?
系统表空间会产生IO瓶颈,刷新数据的时候是顺序进行的所以会产生文件的IO瓶颈
独立表空间可以同时向多个文件刷新数据
Innodb存储引擎的特性
1.支持事务的ACID特性
2.Innodb支持行级锁,可以最大程度的支持并发
MyISAM和InnoDB对比
MySQL引擎之CSV
文件系统存储特点
数据以文本方式存储在文件中
- .CSV文件存储表内容
- .CSM文件存储表的元数据如表状态和数据量
- .frm文件存储表结构信息
特点
以CSV格式进行数据存储
使用场景
适合做为数据交换的中间表
MySQL引擎之Memory
也称HEAP存储引擎,所以数据保存在内存中,如果MySQL服务重启数据会丢失,但是表结构会保存下来
功能特点
- 支持HASH索引和BTree索引
- 所有字段都为固定长度varchar(10)=char(10)
- 不支持BLOB和TEXT等大字段
- Memory存储引擎使用表级锁
如何选择存储引擎
大部分情况下,InnoDB都是正确的选择,可以简单地归纳为一句话“除非需要用到某些InnoDB不具备的特性,并且没有其他办法可以替代,否则都应该优先选择InnoDB引擎
参考条件
- 事务
如果应用需要事务支持,那么InnoDB(或者XtraDB)是目前最稳定并且经过验证的选择
- 备份
如果可以定期地关闭服务器来执行备份,那么备份的因素可以忽略。反之,如果需要在线热备份,那么选择InnoDB就是基本的要求
- 崩溃恢复
MyISAM崩溃后发生损坏的概率比InnoDB要高很多,而且恢复速度也要慢
应用举例
- 日志型应用
- 只读或者大部分情况下只读的表
- 订单处理