623 views
Mysql

7.mysql存储引擎

文章目录

MySQL数据库存储引擎

服务层

第二层服务层是MySQL的核心
MySQL的核心服务层都在这一层,查询解析,SQL执行计划分析,SQL执行计划优化,查询缓存。
以及跨存储引擎的功能都在这一层实现:存储过程,触发器,视图等。
通过下图来观察服务层的内部结构:

存储引擎层

负责MySQL中数据的存储与提取。 
服务器中的查询执行引擎通过API与存储引擎进行通信,通过接口屏蔽了不同存储引擎之间的差异。
MySQL采用插件式的存储引擎。
MySQL为我们提供了许多存储引擎,每种存储引擎有不同的特点。
我们可以根据不同的业务特点,选择最适合的存储引擎。
如果对于存储引擎的性能不满意,可以通过修改源码来得到自己想要达到的性能。
例如阿里巴巴的X-Engine,为了满足企业的需求facebook与google都对InnoDB存储引擎进行了扩充。

-- 查看存储引擎
show engines;

MySQL引擎之MyISAM

MySQL5.5之前的版本默认存储引擎
MyISAM存储引擎表由MYD(数据文件)和MYI(索引文件)组成

1. 什么是锁?
锁主要作用是管理共享资源的并发访问
锁用于实现事务的隔离性

2. 锁的类型
a. 共享锁(也称读锁),针对同一份数据,多个读操作可以同时进行而不会互相影响
b. 独占锁(也称写锁),当前写操作没有完成前,它会阻断其他写锁和读锁

3. 锁的粒度
a. 表级锁
b. 行级锁

4. MyISAM存储引擎特性
a. 并发性与锁级别
b. 表损坏修复
c. MyISAM表支持数据压缩
myisampack -b -f myIsam.MYI 

5. MyISAM存储引擎限制
a. 版本小于5.0时默认表大小为4G,如存储大表则要修改MAX_Rows和AVG_ROW_LENGTH
b. 版本 > MySQL5.0时默认支持256TB

6. 适合场景
a. 非事务型应用
b. 只读类应用

MySQL引擎之Innodb

MySQL5.5 及之后版本默认存储引擎,支持事务的ACID特性,支持行级锁;
提供了具有事务(transaction)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)
多版本并发控制(multi-versioned concurrency control)的事务安全(transaction-safe (ACID compliant))型表

1. Innodb使用表空间进行数据存储
查看:innodb_file_per_table
show variables like '%per_table%';  
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
ON:独立表空间
OFF:系统表空间

2. 系统表空间和独立表空间如何选择?
a. 系统表空间会产生IO瓶颈,刷新数据的时候是顺序进行的所以会产生文件的IO瓶颈
b. 独立表空间可以同时向多个文件刷新数据

3. Innodb存储引擎的特性
a.支持事务的ACID特性
b.Innodb支持行级锁,可以最大程度的支持并发

MyISAM和InnoDB对比

MySQL引擎之CSV

1. 文件系统存储特点
数据以文本方式存储在文件中
.CSV文件存储表内容
.CSM文件存储表的元数据如表状态和数据量
.frm文件存储表结构信息

2. 特点
以CSV格式进行数据存储

3. 使用场景
适合做为数据交换的中间表

MySQL引擎之Memory

也称HEAP存储引擎,所以数据保存在内存中
如果MySQL服务重启数据会丢失,但是表结构会保存下来

-- 功能特点
a. 支持HASH索引和BTree索引
b. 所有字段都为固定长度 varchar(10)=char(10)
c. 不支持BLOB和TEXT等大字段
d. Memory存储引擎使用表级锁

如何选择存储引擎

大部分情况下,InnoDB都是正确的选择,
可以简单地归纳为一句话“除非需要用到某些InnoDB不具备的特性,
并且没有其他办法可以替代,否则都应该优先选择InnoDB引擎

1. 参考条件
-- 事务
如果应用需要事务支持,那么InnoDB(或者XtraDB)是目前最稳定并且经过验证的选择

-- 备份
如果可以定期地关闭服务器来执行备份,那么备份的因素可以忽略。
反之,如果需要在线热备份,那么选择InnoDB就是基本的要求

-- 崩溃恢复
MyISAM崩溃后发生损坏的概率比InnoDB要高很多,而且恢复速度也要慢

2. 应用举例
a. 日志型应用
b. 只读或者大部分情况下只读的表
c. 订单处理

基准测试

-- 基准测试是一种测量和评估软件性能指标的活动用于建立某个时刻的性能基准,以便当系统发生软硬件变化时重新进行基准测试以评估变化对性能的影响
-- 基准测试是针对系统设置的一种压力测试

1. 基准测试特点
直接、简单、易于比较,用于评估服务器的处理能力
可能不关心业务逻辑,所使用的查询和业务的真实性可以和业务环境没关系

2. 压力测试特点
a. 对真实的业务数据进行测试,获得真实系统所能承受的压力
b. 需要针对不同主题,所使用的数据和查询也是真实用到的
c. 基准测试是简化了的压力测试

3. 基准测试的目的
a. 建立MySQL服务器的性能基准线,确定当前MySQL服务器运行情况,确定优化之后的效果
b. 模拟比当前系统更高的负载,已找出系统的扩展瓶颈,可以增加数据库并发,观察QPS(每秒处理的查询数),TPS(每秒处理的事务数)变化,确定并发量与性能最优的关系
c. 测试不同的硬件、软件和操作系统配置
d.证明新的硬件设备是否配置正确

4. 如何进行基准测试
-- 对整个系统进行基准测试
优点
a. 能够测试整个系统的性能,包括web服务器缓存、数据库等
b. MySQL并不总是出现性能问题的瓶颈,如果只关注MySQL可能忽略其他问题,能反映出系统中各个组件接口间的性能问题体现真实性能状况

缺点
a. 基准测试最重要的就是简单,可能对不同的方案进行测试,找到最优的方案,基准测试进行的时间一定要短,否则就要花费大量的时间进行基准测试
b. 测试设计复杂,消耗时间长

5. 对MySQL进行基准测试
优点
a. 测试设计简单,所耗费时间短

缺点
a. 无法全面了解整个系统的性能基线

6. MySQL基准测试的常见指标
a. 单位时间内处理的事务数(TPS)
b. 单位时间内处理的查询数(QPS)


7. MySQL基准测试工具

MySQL基准测试之mysqlslap,可以模拟服务器负载,并输出相关统计信息

-- 常用参数说明

*   --auto-generate-sql 由系统自动生成SQL脚本进行测试
*   --auto-generate-sql-add-autoincrement 在生成的表中增加自增ID
*   --auto-generate-sql-load-type 指定测试中使用的查询类型 读写或者混合,默认是混合
*   --auto-generate-sql-write-number 指定初始化数据时生成的数据量
*   --concurrency 指定并发线程的数量 1,10,50,200
*   --engine 指定要测试表的存储引擎,可以用逗号分割多个存储引擎
*   --no-drop 指定不清理测试数据
*   --iterations 指定测试运行的次数 指定了这个不能指定no-drop
*   --number-of-queries 指定每一个线程执行的查询数量
*   --debug-info 指定输出额外的内存及CPU统计信息
*   --number-int-cols 指定测试表中包含的INT类型列的数量
*   --number-char-cols 指定测试表中包含的varchar类型的数量
*   --create-schema 指定了用于执行测试的数据库的名字
*   --query 用于指定自定义SQL的脚本
*   --only-print 并不运行测试脚本,而是把生成的脚本打印出来

命令:
mysqlslap --concurrency=1,50,100,200 --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

sysbench测试工具:https://www.cnblogs.com/kismetv/archive/2017/09/30/7615738.html

影子专属博客 赣ICP备17013143号