Day21 PythonWeb全栈课程课堂内容

1. MySQL基准测试

1.1 什么是基准测试

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

• 基准测试是针对系统设置的一种压力测试。

1.2 基准测试特点

• 直接、简单、易于比较,用于评估服务器的处理能力。

• 可能不关心业务逻辑,所使用的查询和业务的真实性可以和业务环境没关系。

1.3 压力测试特点

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

1.4 基准测试的目的

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

1.5如何进行基准测试

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

  • 优点：

  • 能够测试整个系统的性能,包括web服务器缓存、数据库等。

  • MySQL并不总是出现性能问题的瓶颈,如果只关注MySQL可能忽略其他问题,能反映出系统中各个组件接口间的性能问题体现真实性能状况。

  • 缺点：

  • 基准测试最重要的就是简单,可能对不同的方案进行测试,找到最优的方案,基准测试进行的时间一定要短,否则就要花费大量的时间进行基准测试。

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

• 对MySQL进行基准测试

  • 优点：

  • 测试设计简单,所耗费时间短。

  • 缺点：

  • 无法全面了解整个系统的性能基线。

• MySQL基准测试的常见指标

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

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

2. 影响服务器性能的几个方面

1.服务器硬件

2.服务器的操作系统

3.数据库存储引擎的选择

4.数据库参数配置

5.数据库结构设计和SQL语句

SQL性能下降原因

• 查询语句写的不好
• 索引失效
• 关联查询太多join
• 服务器调优及各个参数设置

SQL加载顺序

• 手写SQL的顺序

select distinct 
    <select _list>
from 
    <left_table>
join  <right_table> on <join_codition>
where
    <where_condition>
group by
    <group_by_list>
having
    <having_condition>
order by
    <order_by_condition>
limit <limit number>

• 机读的SQL顺序

MySQL常见瓶颈

• CPU:CPU在饱和的时候一般发生在数据装入内存或从磁盘读取数据的时候
• IO:磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
• 服务器硬件的性能瓶颈

3. explain

explain是什么？

使用explain关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。

explain能干嘛？

• 表的读取顺序
• 数据读取操作的操作类型
• 那些索引可以使用
• 那些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

explain怎么玩？

explain + SQL语句

explain字段解释

id表的读取顺序

select查询的顺序号,包含一组数字,表示查询中执行select子句或操作表的顺序

两种情况:

1.id相同,执行顺序由上至下。

创建三个表分别为t1,t2,t3。

输入sql查询语句，并加入explain语句。

explain select t2.* from t1,t2,t3 where t1.id=t2.id and t1.id=t3.id and t1.other_column=’’;

2.id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行。

输入sql查询语句，并加入explain语句。

explain select t2.* from t2 where id=(select id from t1 where id=(select t3.id from t3 where t3.other_column=’’));

select_type 查询类型

数据读取操作的操作类型

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

---

SIMPLE 可以查看之前的语句就是SIMPLE的 一般没有子查询没有UNION的情况下，就显示的SIMPLE。

---

PRIMARY 和SUBQUERY可以从上面看到，t2为最外层select，t1和t2为子查询select。

---

explain select * from t2 union select * from t4;

union 必须字段数相同才可以使用

t4表为union内层表，t2为外层表PRIMARY。UNION RESULT为 union的结果,此时id为NULL

table 查询的表格

显示这一行的数据时关于那张表的

partitions 查询的分区

查询访问的分区

type

从最好到最差依次是

system > const > eq_ref > ref > range > index > ALL

• system:表只有一行记录(等于系统表),这是const类型的特例。

• const:表示通过索引一次就找到了,const用于比较primary key。

• eq_ref唯一索引扫描,对于每个索引键,表中只有一条记录与之匹配。

其中t1全表扫描所以为ALL。

• ref:非唯一性索引扫描,返回匹配某个单独值得所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行。

• range:只检索给定范围的行,使用一个索引来选择行。

• index:Full Index Scan,index与ALL区别为index类型只遍历索引树。

  t1和t2表中分别添加数据

• ALL:将遍历全表找到匹配的行。

possible_keys

显示可能应用在这张表中的索引,一个或多个。

key

实际使用的索引。如果为null,则没有使用索引

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

key_len

表示索引中使用的字节数,可通过该列计算查询中使用的索引长度。在不损失精确性的情况下,长度越短越好

key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即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中无法利用索引完成的排序操作称为"文件排序"

案例：

为原来t3表格创建一个col1,col2,col3的复合索引。

explain select * from t3 where col1=‘a’ order by col3\G

可以发信在Extra里出现了using filesort，因为order by会排序。

• Using temporary,使用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。

案例：

explain select * from t3 where col1 in (‘ac’,‘ab’) group by col2;

• Using index,使用了索引,避免了全表扫描

• Using where,使用了where过滤

• Using join buffer,使用了连接缓存

  explain select * from t1 inner join t2 on t1.id = t2.id;

• impossible where,不可能的条件,where子句的值总是false

  explain select * from t3 where id=1 and id=2;

其中using filesort和using temporary是不好的，using index 是好的，using where和impossible where没有多少的影响。