数据库存储引擎一
1.数据库存储引擎
1.1 服务层
第二层服务层是MySQL的核心，MySQL的核心服务层都在这一层，查询解析，SQL执行计划分析，SQL执行计划优化，查询缓存。以及跨存储引擎的功能都在这一层实现：存储过程，触发器，视图等。通过下图来观察服务层的内部结构
1.2 存储引擎层
负责MySQL中数据的存储与提取。 服务器中的查询执行引擎通过API与存储引擎进行通信，通过接口屏蔽了不同存储引擎之间的差异。MySQL采用插件式的存储引擎。MySQL为我们提供了许多存储引擎，每种存储引擎有不同的特点。我们可以根据不同的业务特点，选择最适合的存储引擎。如果对于存储引擎的性能不满意，可以通过修改源码来得到自己想要达到的性能。例如阿里巴巴的X-Engine，为了满足企业的需求facebook与google都对InnoDB存储引擎进行了扩充。
1.3 查看存储引擎
show engines;
2. MySQL引擎之MyISAM
MySQL引擎之MyISAM
MySQL5.5之前的版本默认存储引擎
MyISAM存储引擎表由MYD(数据文件)和MYI(索引文件)组成
什么是锁？
锁主要作用是管理共享资源的并发访问
锁用于实现事务的隔离性
锁的类型
共享锁(也称读锁),针对同一份数据,多个读操作可
以同时进行而不会互相影响
独占锁(也称写锁),当前写操作没有完成前,它会阻
断其他写锁和读锁
锁的粒度
表级锁
行级锁
MyISAM存储引擎特性
-
1.并发性与锁级别
MyISAM使用的就是表级锁 对表中数据修改的时候，把整张表锁住 并发性不好 -
2.表损坏修复
检查表 check table 表名;
修复表 repair table 表名;
-
3.MyISAM表支持数据压缩
myisampack -b -f myIsam.MYI -
4.MyISAM存储引擎限制
版本 < MySQL5.0时默认表大小为4G,如存储大表则要修改MAX_Rows和AVG_ROW_LENGTH
版本 > MySQL5.0时默认支持256TB
3. 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对比
4. MySQL引擎之CSV
创建表不支持主键 索引 自增 不支持空
文件系统存储特点
数据以文本方式存储在文件中
.CSV文件存储表内容
.CSM文件存储表的元数据如表状态和数据量
.frm文件存储表结构信息
特点
以CSV格式进行数据存储
使用场景
适合做为数据交换的中间表
5. MySQL引擎之Memory
MySQL引擎之Memory
也称HEAP存储引擎,所以数据保存在内存中,如果MySQL服务重启数据会丢失,但是表结构会保存下来
功能特点
- 支持HASH索引和BTree索引
- 所有字段都为固定长度 varchar(10)=char(10)
- 不支持BLOB和TEXT等大字段
- Memory存储引擎使用表级锁
如何选择存储引擎
大部分情况下，InnoDB都是正确的选择，可以简单地归纳为一句话“除非需要用到某些InnoDB不具备的特性，并且没有其他办法可以替代，否则都应该优先选择InnoDB引擎
参考条件
事务
如果应用需要事务支持，那么InnoDB(或者XtraDB)
是目前最稳定并且经过验证的选择
备份
如果可以定期地关闭服务器来执行备份，那么备份的因素可以忽略。
反之，如果需要在线热备份，那么选择InnoDB就是基本的要求
崩溃恢复
MyISAM崩溃后发生损坏的概率比InnoDB要高很多，
而且恢复速度也要慢
应用举例
- 日志型应用
MyISAM - 只读或者大部分情况下只读的表
MyISAM - 订单处理
InnoDB
6. MySQL基准测试
6.1 什么是基准测试
基准测试是一种测量和评估软件性能指标的活动用于建立某个时刻的性能基准,以便当系统发生软硬件变化时重新进行基准测试以评估变化对性能的影响
基准测试是针对系统设置的一种压力测试
基准测试特点
直接、简单、易于比较,用于评估服务器的处理能力
可能不关心业务逻辑,所使用的查询和业务的真实性可以和业务环境没关系
压力测试特点
- 对真实的业务数据进行测试,获得真实系统所能承受的压力
- 需要针对不同主题,所使用的数据和查询也是真实用到的
- 基准测试是简化了的压力测试
基准测试的目的
- 建立MySQL服务器的性能基准线,确定当前MySQL服务器运行情况,确定优化之后的效果
- 模拟比当前系统更高的负载,已找出系统的扩展瓶颈,可以增加数据库并发,观察QPS(每秒处理的查询数),TPS(每秒处理的事务数)变化,确定并发量与性能最优的关系
- 测试不同的硬件、软件和操作系统配置
- 证明新的硬件设备是否配置正确
6.2 对整个系统进行基准测试
优点
- 能够测试整个系统的性能,包括web服务器缓存、数据库等
- MySQL并不总是出现性能问题的瓶颈,如果只关注MySQL可能忽略其他问题,能反映出系统中各个组件接口间的性能问题体现真实性能状况
缺点
基准测试最重要的就是简单,可能对不同的方案进行测试,找到最优的方案,基准测试进行的时间一定要短,否则就要花费大量的时间进行基准测试
- 测试设计复杂,消耗时间长
对MySQL进行基准测试
优点
- 测试设计简单,所耗费时间短
缺点
- 无法全面了解整个系统的性能基线
MySQL基准测试的常见指标
- 单位时间内处理的事务数(TPS)
- 单位时间内处理的查询数(QPS)
6.3 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
| MyISAM | InnoDB | |
|---|---|---|
| 存储结构 | 每张表被存放在三个文件：frm-表格定义MYD(MYData)-数据文件MYI(MYIndex)-索引文件 | 所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB |
| 存储空间 | MyISAM可被压缩，存储空间较小 | InnoDB的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引 |
| 可移植性、备份及恢复 | 由于MyISAM的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作 | 免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十G的时候就相对痛苦了 |
| 事务安全 | 不支持 每次查询具有原子性 | 支持 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表 |
| AUTO_INCREMENT | MyISAM表可以和其他字段一起建立联合索引 | InnoDB中必须包含只有该字段的索引 |
| SELECT | MyISAM更优 | |
| INSERT | InnoDB更优 | |
| UPDATE | InnoDB更优 | |
| DELETE | InnoDB更优 它不会重新建立表，而是一行一行的删除 | |
| COUNT without WHERE | MyISAM更优。因为MyISAM保存了表的具体行数 | InnoDB没有保存表的具体行数，需要逐行扫描统计，就很慢了 |
| COUNT with WHERE | 一样 | 一样，InnoDB也会锁表 |
| 锁 | 只支持表锁 | 支持表锁、行锁 行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的 |
| 外键 | 不支持 | 支持 |
| FULLTEXT全文索引 | 支持 | 不支持 可以通过使用Sphinx从InnoDB中获得全文索引，会慢一点 |
195
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
