MySQL 基准测试&explain (第八讲)

一.基准测试

1.什么是基准测试

• 什么是基准测试呢？它是用来测量还有评估软件性能指标的,用来建立一些时刻性能基准，比如在我们的服务器升级之前，在我们升级之后就可以用来测试软硬件发生变化之后，有没有大的性能提升，或者反向升级。(也就是为了给出一个当前的标准，当我们对服务器进行升级以后，可以看得出升级的幅度，或者有没有变化。)
• 基准测试是针对系统设置的一种压力测试。

2.基准测试的特点

• 直接、简单、易于比较、用于评估服务器的处理能力
• 可自行生成数据进行查询和模拟业务，数据有真实性，可以和你的业务环境没有关系

3.压力测试的特点

• 进行测试的业务数据是具有真实性的，可以获得真是系统的压力承受能力
• 可以针对不同的应用场景，所用到的测试数据和查询也是真是所使用到的
• 基准测试就是简化了的压力测试

4.基准测试目的

• 建立一条MySQL服务器的性能基准线，确定当前MySQL服务器的运行情况，也可以用来比较优化后的效果
• 模拟比当前系统更高的负载，找出系统的扩展瓶颈，可增加数据库并发，观察QPS(每秒处理的查询数)，TPS(每秒处理的事务数)变化，确定并发量与性能的最优关系(当然这个QPS和TPS越大越好)
• 再对服务器的硬件、软件、操作系统的配置发生变更之后的测试
• 测试新的软硬件是否配置正确(如果低于基准线那肯定没有配置正确)

5.对整个系统进行基准测试

优点

• 能够对整个服务器的性能进行测试，例如整个系统的性能、web服务器的缓存、数据库等等
• MySQL数据库不会总有性能问题的瓶颈，不能总把关注点都放在它身上，很多的问题都能影响服务器的性能，对整个系统进行基准测试才能反映出系统里各个组件接口间的性能问题，才能得到真是性能状况

缺点

    基准测试一定要在短时间内完成，所以基准测试最重要的其实就是简单

• 测试设计复杂，消耗时间长

6.对MySQL进行基准测试

优点

• 符合测试时间段的特点，测试的设计也很简单

缺点

• 但只能测试MySQL数据库，并不能更全面的了解整个服务器的性能

7.MySQL基准测试的常见指标

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

8.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 并不运行测试脚本,而是把生成的脚本打印出来
• 注意： 测试之前要退出断开MySQL的连接

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中获得全文索引，会慢一点

explain 分析SQL语句

    explain关键字可以模拟优化器来执行SQL语句，让我们可以直观的看到优化器是怎么去执行我们的SQL语句。

explain能干嘛？

• 看出这条SQL读取表的顺序
• 数据读取操作的操作类型
• 可能会使用到的索引
• 实际使用到的索引
• 表之间的引用
• 优化器总共查询了多少张表

explain如何使用？

• 在要使用的sql语句前加上explain即可

explain + 'sql'

• 例如：

explain select * from text;
-- 一般实际的操作中尽量别用星号

explain字段解释

id(表的读取顺序)

    表示select查询的顺序，表示查询的时候select查询的子句，或者操作表的顺序

    有两种情况：

• id相同，顺序从上往下
• id不相同，数值大的优先

select_type(数据读取操作的操作类型)

值	描述
SIMPLE	简单的SELECT语句(不包括UNION操作或子查询操作)
PRIMARY	查询中最外层的SELECT（如两表做UNION或者存在子查询的外层的表操作为PRIMARY，内层的操作为UNION）
UNION	UNION操作中，查询中处于内层的SELECT，即被union的SELECT
SUBQUERY	子查询中的SELECT
SUBQUERY	表示包含在 From 子句中的 Select 查询
UNION RESULT	union的结果,此时id为NULL

table(使用的表)

• 这行是显示使用到的是哪张表的

partitions(分区)

type

• 从好到坏分别是

system	表只有一行记录(等于系统表),这是const类型的特例
const	通过索引一下就找到了，用于比较primary key
eq_ref	唯一索引扫描，对于每个索引建，表中只有一条记录与之匹配
ref	非唯一性索引扫描,返回匹配某个单独值得所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行
range	只检索给定范围的行,使用一个索引来选择行。
index	index,Full Index Scan,index与ALL区别为index类型只遍历索引树。
ALL	遍历全表找到匹配的行

possible_keys

• 可能用在这张表上的索引

key

• 实际用到的索引
• 查询中若使用了覆盖索引,则该索引仅出现在key列表中

key_len

• 索引使用的字节数，再不损失精确度的基础上越短越好

• 显示的是最大可能使用到的字节数，不是实际上用到的字节数，是通过表的定义计算出来的

• 计算方式：

varchr(24)变长字段且允许NULL
24*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)

varchr(10)变长字段且不允许NULL  
10*(Character Set：utf8=3,gbk=2,latin1=1)+2(变长字段)

char(10)固定字段且允许NULL      
10*(Character Set：utf8=3,gbk=2,latin1=1)+1(NULL)

char(10)固定字段且不允许NULL    
10*(Character Set：utf8=3,gbk=2,latin1=1)

ref

• 显示索引用到了哪一列

rows

• 根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数

Extra

• 相对于其他信息，但是非常重要

名称	描述
Using filesort	说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,MySQL中无法利用索引完成的排序操作称为"文件排序"
Using temporary	使用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。
Using index	使用了索引,避免了全表扫描
Using where	使用了where过滤
Using join buffer	使用了连接缓存
impossible where	不可能的条件,where子句的值总是false

• 注意！！！如果出现了前两个，那么必须优化！