数据库优化第8讲 - MySQL存储引擎&基准测试

一、存储引擎—MyISAM

1.1 MySQL引擎之MyISAM

MyISAM存储引擎表由MYD(数据文件)和MYI(索引文件)组成

MyISAM存储引擎特性：
1.并发性与锁级别
2.表损坏修复
3.MyISAM表支持数据压缩

myisampack -b -f myIsam.MYI 

1.2 锁：

锁主要作用是管理共享资源的并发访问

锁用于实现事务的隔离性

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

锁的粒度
表级锁
行级锁

适合场景
1.非事务型应用
2.只读类应用

二、存储引擎—Innodb

MySQL5.5 及之后版本默认存储引擎,支持事务的ACID特性

2.1 Innodb使用表空间进行数据存储

innodb_file_per_table   
	
ON:独立表空间,tablename.ibd

OFF:系统表空间:ibdataX X是一个数字

2.2 系统表空间和独立表空间如何选择?

系统表空间会产生IO瓶颈,刷新数据的时候是顺序进行的所以会产生文件的IO瓶颈

独立表空间可以同时向多个文件刷新数据

2.3 Innodb存储引擎的特性

1.支持事务的ACID特性

2.Innodb支持行级锁,可以最大程度的支持并发

2.4 MyISAM和InnoDB对比

三、MySQL引擎—CSV

3.1 文件系统存储特点

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

3.2 特点

以CSV格式进行数据存储

3.3 使用场景

适合做为数据交换的中间表

四、MySQL引擎—Memory

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

4.1 功能特点

1、支持HASH索引和BTree索引
2、所有字段都为固定长度 varchar(10)=char(10)
3、不支持BLOB和TEXT等大字段
4、Memory存储引擎使用表级锁

4.2 怎么选择存储引擎

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

4.3 应用举例

1、日志型应用
2、只读或者大部分情况下只读的表
3、订单处理

4.4 参考条件

①事务
如果应用需要事务支持，那么InnoDB(或者XtraDB)是目前最稳定并且经过验证的选择

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

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

五、基准测试

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

5.1 压力测试特点

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

5.2 基准测试的目的

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

5.2 对整个系统进行基准测试

MySQL基准测试的常见指标

• 单位时间内处理的事务数(TPS)
• 单位时间内处理的查询数(QPS)

MySQL基准测试工具（mysqlslap）

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