1.Mysqlslap
mysqlslap是mysql自带的基准测试工具。
基准测试分为两种主要的策略:一是针对整个系统的整体测试(也被称为集成式),二是单独测试MYSQL(也被称为单组件式)
其优点: 查询数据,语法简单,灵活容易使用.该工具可以模拟多个客户端同时并发的向服务器发出查询更新,给出了性能测试数据而且提供了多种引擎的性能比较。
./mysqlslap -a -u root -p -c 300 -i 2
上面是简单的一个例子,我们进入到mysql的bin目录便可以执行改命令,其中 -c 代表用户数,- i 代表执行几次,输入命令回车输出mysql密码即可得出压测结果
另外,--create-schema 代表即将压测的数据库,-q 代表压测的SQL -e 代表数据表的存储引擎
2. explain 分析
explain的基本语法如下:
explain select select_options;
语句执行后会返回优化器的执行计划,下面详细分析下返回的字段信息的解释:
explain 命令一共返回12列信息,分别是:
id,select_type, table, partitions,type,possible_keys,key,ken_len,ref,rows,filtered,Extra
其中
id 列 : 优化器选定的执行计划中查询的序列号。
select_type列 :
DEPENDENT UNION:子查询中的UNION,且为UNION中从第二个SELECT开始的后面所有
SELECT,同样依赖于外部查询的结果集。
SIMPLE :除子查询或UNION之外的其他查询。
PRIMARY : 子查询中最外层查询,注意并不是主键查询。
SUBQUERY: 子查询内层查询的第一个SELECT,结果不依赖与外部查询结果集。
eg:explain select * from role where id = (select role_id from admin_user where user_id = 1) \G;
system、const、eq_ref、ref、full_text、ref_or_null、unique_subquery、
index_subquery、range、index_merge、index、all
fulltext 全文检索索引。
ref_or_null 和ref类似,增加了null值判断
unique_subquery、 index_subquery 都是子查询,前者返回唯一值,后者返回可能有重复
range 重要 索引范围扫描,常用于 ><,is null,between,in,like等
index_merge(索引合并)
表示查询使用了两个或者以上的索引数量,常见于and或者or查询匹配上了多个不同索引的字段
index(辅助索引) 减少回表次数,因为要查询的索引都在一颗索引树上
all 全表扫描
possible_keys 列
此次查询中,可能选用的索引
key 列
查询实际使用的索引,select_type为index_merge时,key列可能有多个索引,其它时候这里只会有一个
key_len 列
- 用于处理查询的索引长度,如果是单列索引,那么整个索引长度都会计算进去,如果是多列索引,那么查询不一定能使用到所有的列,具体使用了多少个列的索引,这里就会计算进去,没有使用到的索引,这里不会计算进去。
- 留意一下这个长度,计算一下就知道这个索引使用了多少列
- 另外,key_len 只计算 where 条件使用到索引长度,而排序和分组就算用到了索引也不会计算key_len
ref
- 如果是使用的常数等值查询,这里会显示const
- 如果是连接查询,被驱动表的执行计划这里会显示驱动表的关联字段
- 如果是条件使用了表达式或者函数,或者条件列发生了内部隐式转换,这里可能会显示
rows
执行计划估算的扫描行数,不是精确值(innodb不是精确值,myisam是精确值,主要是因为innodb使用了mvcc)。
extra
这个列包含很多不适合在其它列显示的重要信息,有很多种,常用的有:
- using temporary
- 表示使用了临时表存储中间结果
- MySQL在对 order by和group by 时使用临时表
- 临时表可以是内存临时表和磁盘临时表,执行计划中看不出来,需要查看status变量:used_tmp_table、used_tmp_disk_table才可以看出来
- no table used
- using filesort
- 排序时无法使用到所以就会出现这个,常见于order by和group by
- 说明MySQL会使用一个外部的索引进行排序,而不是按照索引顺序进行读取
- MySQL中无法利用索引完成的排序就叫“文件排序”
- using where
- 表示存储引擎返回的记录并不都是符合条件的,需要在server层进行筛选过滤,性能很低
- using index condition
- 索引下推,不需要再在server层进行过滤,5.6.x开始支持
- first match
- 5.6.x 开始出现的优化子查询的新特性之一,常见于where字句含有in()类型的子查询,如果内表数据量过大,可能出现
- loosescan
- 5.6.x 开始出现的优化子查询的新特性之一,常见于where字句含有in()类型的子查询,如果内表返回有重复值,可能出现
filtered 列
5.7之后的版本默认就有这个字段,不需要使用explain extended了。这个字段表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例,注意是百分比,不是具体记录数。
3. profiling
使用 profiling 可以清楚的知道每一条SQL的每个阶段耗时分布,以及在什么地方消耗了多少资源
使用流程:
1. 开启profiling参数
此参数默认是关闭状态,线上不建议打开,建议在会话级别的设置排查问题
set profiling=1;
show variables like "%profi";
2. 执行所要分析的语句
insert into table value (1,'小a');
show profiles;
show profile for query 3;
此处的query 3则为上面查出来的Query_ID,此处的时间单位是秒
+----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.003476 | | checking permissions | 0.000021 | | Opening tables | 0.000024 | | init | 0.000498 | | System lock | 0.000015 | | update | 0.013241 | | end | 0.000301 | | query end | 0.003210 | | closing tables | 0.000023 | | freeing items | 0.000032 | | cleaning up | 0.001089 | +----------------------+----------+ 11 rows in set, 1 warning (0.01 sec)
使用这条sql的执行 cpu、io、内存、交换内存等信息
show profile cpu ,block,io,memory,swaps,context switches, source for query 3;
执行SQL,得出以下输出