MySQL性能检测工具
性能检测步骤总结
- 1.观察，至少跑一天，看看生产的慢SQL情况
- 2.开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL，并抓取处理，看得到返回记录集最多的几个SQL语句
- 3.explain+慢SQL分析
- 4.show profile
- 5.进行SQL数据库服务器的参数调优（运维orDBA来做）
一、explain-----对单句SQL语句执行性能分析
（一）介绍
定义
- 使用explain关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句
- 可以观察查询优化器，查看SQL怎么执行语句的
- 对单句SQL语句测试性能
explain能干啥
- 表的读取顺序：id
- 数据读取操作的操作类型：select_type
- 哪些索引可以使用：possible_keys
- 哪些索引被实际使用：key
- 表之间的引用：table
- 每张表有多少行被优化器查询：rows
开启explain
- explain + SQL语句 [\G：竖的排列结果]
- 后面接正常语句就可以直接对这句语句性能分析
（二）explain结果解读
id—表的读取顺序
- select查询的顺序号，包含一组数字，表示查询中执行select字句或操作表的顺序，第一行的是驱动表
- 两种情况
- 1.id相同，执行顺序由上至下
- 2.id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
- 3.id相同不同，id值越大优先级越高，越先被执行
select_type操作类型
| 值 | 描述 |
|---|---|
| SIMPLE | 简单的SELECT语句（不包含UNION操作或子查询操作） |
| PRIMARY | 查询中最外层的SELECT（如两表做UNOIN或者存在子查询的外层的表操作为primary，内层操作为UNION） |
| UNION | UNION操作中，查询处于内层的SELECT，即被UNION的SELECT |
| SUBQUERY | 子查询的SELECT |
| DERIVED | 包含在FROM字句中的SELECT查询 |
| UNION RESULT | UNION的结果，此时id为NULL |
table
- 显示这一行的数据是关于哪张表
partitions
- 查询访问的分区
type
-
依次从好到差
- system，const， eq_ret，ref、 range，index ，ALL 需要记住
-
各个含义
- system：表示只有一行记录（等于系统表），这是const类型的特例，不太可能实现
- const：表示通过索引一次就找到了，const用于比较primary key，不太可能实现，eg:指定唯一索引值
- eq_ret：唯一索引扫描，对于每一个索引键只有一条记录与之匹配，优秀了
- ref：非唯一索引扫描，返回匹配某个单独值的所有行本质上也是一种索引访问，它返回所有匹配某个单独值的行，可以多行，应该达到的水平，eg：联合索引
- range：只检索给定范围的行，使用一个索引来选择行，eg:用了between、in、> 针对索引字段
- index：inndex与all的区别为index类型只遍历索引树
- all：将遍历全表找到匹配行，如果数据库的数据量到上百万，可以添加索引来避免全表扫描
possible_keys
- 可能用到的索引
- 显示可能应用相似这张表中的索引，一个或多个
key
- 实际使用的索引。如果为null，则没有使用索引
- 查询中若使用了覆盖索引，则该索引仅出现在key列表中
- 用到的索引以这个为准，如若数据量大，添加索引的效果可以通过这个看，是否用到了索引
key_len
-
表示索引中使用的字节数，可通过该列计算查询中使用的索引长度，观察到用到了索引数量
-
在不损失精确性的情况下，长度越短越好
-
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中无法利用索引完成的排序称为"文件排序"，不好，需要优化
- Using temporary： 使用了临时表来保存中间结果，MYSQL在对查询结果进行排序的时候使用了临时表，常见于排序OrderBy 和分组查询GroupBy，不好
- Using Index：使用了索引，避免了全表扫描
- Using Where：使用了where
- Using Join Buffer：使用了连接缓存（join太多个表，配置文件里面的JoinBuffer的值可以调大一点）
- Impossible Where：不可能的条件，where条件不可能成立，没有行返回
二、showprofile-----分析语句资源消耗情况
（一）介绍
定义
- 是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量
- 分析每一条执行的sql语句执行的资源消耗情况，执行过程
- 默认情况下，参数处于关闭的状态，并保持最近15次运行的结果
开启
- 1.是否支持，查看当前MySQL版本是否支持：
- 2.开启功能，默认是关闭，使用前需要开启
# 查询是否开关闭 show varibales like'profiling' # 设置开启，mysql重启后无效，要重新开启 set profiling=on
（二）结果分析
type
| 种类 | 含义 |
|---|---|
| all | 显示所有的开销信息 |
| block io | 显示块IO相关信息 |
| cpu | 显示cpu相关信息 |
| ipc | 显示发送和接收相关开销信息 |
| memory | 显示内存相关开销信息 |
| page faults | 显示页面错误相关开销信息 |
```
# 显示所有执行过的。默认显示15条
show profiles;
# 根据 queryID查询一条的详情信息
show profile cpu,block io for query2
```
三、全局查询日志、慢查询日志—保存执行的语句
（一）全局查询日志—记录每一条写的SQL语句
注意
- 比较消耗性能。记录每一条写的SQL语句
- 不轻易开，出现问题开，不要在生产环境开，在测试环境开
开启全局查询日志
-- 开启命令,关闭就是=0
set global general_log=1;
-- 将SQL语句写在表中
- set global log_output='TABLE';
-- 你所编写的SQL语句，会记录到MySQL库里的genderal_log表
select * from mysql.general_log;
SQL需要修改、危险的
- converting HEAP TO MYISAM 出巡结果太大，内存不够用了王磁盘上搬、
- creating tmp TABLE创建临时表
- copying TO tmp TABLE ON DISK 把内存红临时表符合到磁盘，危险
- locked锁
（二）慢查询日志—记录超时的SQL语句
定义
- MySQL的慢查询日志MySQL提供的一种日志记录,它用来记录在MySQL中响应时间超过阙值的语句,具体指运行时间超过 long_query_time值得SQL,则会被记录到慢查询日志中
- 具体指运行时间超过long_query_time值得SQL,则会被记录到慢查询日志中。long_query_time的默认值为10,意思是运行10秒以上的语句。
- 由他来查看那些SQL超出了我们的最大忍耐时间值,比如一条SQL执行超过5秒钟,我们就算是慢SQL,希望能收集超过5秒的SQL,结合之前explain进行全面分析
mysql中开启
- 默认情况下,MySQL数据库没有开启慢查询日志,需要**我们手动来设置这个参数
- 当然如果不是调优需要的话,一般不建议启动该参数,因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件
-- 查看慢查询日志是否开启，慢查询日志保存路径 show variables like '%slow_query_log%'; -- 查看阈值是多少（默认10） show variables like 'long_query_time%'; -- 设置阈值，重启mysql会生效 set global long_query_time=3; set global slow_query_log_file=C:\ProgramData\MySQL\MySQL Server 8.0\Data\slow.log -- 如果无效的话，可以在文件my.ini文件设置 global long_query_time=3 global slow_query_log_file=C:\ProgramData\MySQL\MySQL Server 8.0\Data\slow.log -- 测试 select sleep(5); --在slow_log.txt可以看到这条语句，查询的时间及语句
linux中开启
- 借助慢查询日志工具mysqldumpslow
- 参数信息
- s:表示按照何种方式排序
c:访问次数
l:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录数
t:即为返回前面多少条的数据-- 查看mysqldumpslow有什么功能 mysqldumpslow --help -- 得到返回记录集最多的10个SQL mysqldumpslow -s r -t 10 D:/phpStudy/PHPTutorial/MySQL/slow_log.txt -- 得到访问次数最多的10个SQL mysqldumpslow -s c -t 10 D:/phpStudy/PHPTutorial/MySQL/slow_log.txt
- s:表示按照何种方式排序
四、基准测试----- 对系统测试性能—评估服务器的处理能力
（一）基准测试定义
什么是基准测试
- 基准测试是一种测量和评估软件性能指标的活动用于建立某个时刻的性能基准，以便当系统发生软硬件变化时重新进行基准测试以评估变化对性能的影响
- 基准测试是针对系统设置的一种压力测试
基准测试特点
- 直接简单、易于比较，用于评估服务器的处理能力
- 可能不关系业务逻辑，所使用的查询和业务的真实性可以和业务的环境没有关系
压力测试特点
- 对真实的业务数据进行测试，获得真实系统所能承受的压力
- 需要针对不同主题，所使用的的数据和查询也是真实用到的
- 基准测试是简化了的压力测试
基准测试的目的
- 建立MySQL服务器的性能基准线，确定当前MySQL服务器运行情况，确定优化之后的效果
- 模拟比当前系统更高的负载，已找出系统的扩展瓶颈，可以增加数据库并发，观察QPS（每秒处理的查询数），TPS（每秒处理的事务数）变化，确定并发量与性能最优的关系
- 测试不同的硬件、软件和操作系统配置
（二）如何进行基础测试
对整个系统进行基准测试
- 优点
- 能够测试整个系统的性能包括web服务器缓存、数据库等
- MySQL并不总是出现性能问题的瓶颈，如果只关注MySQL可能忽略其他问题，能反映出系统中各个组件接口间的性能问题体现真实性能状况
- 缺点
- 基准测试最重要的就是简单，可能对不同的方案进行测试，找到最优的方案，基准测试进行的时间一定要短，否则就要花费大量时间进行基准测试
- 测试设计复杂消耗时间长
对MySQL进行基准测试
-
优点
- 测试设计简单，所耗费时间短
-
缺点
- 无法全面了解整个系统的性能基准
MySQL基准测试的常见指标
- 单位时间内处理的事务数（TPS）
- 单位时间内处理的查询数（QPS）
MySQL基准测试工具mysqlslap
- 可以模拟服务器负载，并输出相关统计信息
- 在mysql外部使用
常用参数说明
| 参数 | 参数说明 |
|---|---|
| –auto-generate-sql | 由系统自动生成SQL脚本进行测试 |
| –auto-generate-sql-load-type | 测试语句的类型。指定测试的环境是读操作还是写操作还是两者混合的。取值包括：read，key，write，update和mixed(默认)。 |
| –auto-generate-sql-write-number | 指定初始化数据时生成的数据量。 |
| –auto-generate-sql-add-auto-increment | 在生成的表自动增加自增ID |
| –number-char-cols | 指定测试表中包含vachar类型的数量，自动生成的测试表中包含多少个字符类型的列，默认1 |
| –number-int-cols | 指定测试表中包含INT类型列的数量，自动生成的测试表中包含多少个数字类型的列，默认1 |
| –number-of-queries | 制定每一个线程执行的查询数量，总的测试查询次数(并发客户数×每客户查询次数) |
| –query | 用于指定自定义的SQL脚本，使用自定义脚本执行测试，例如可以调用自定义的一个存储过程或者sql语句来执行测试。 |
| –create-schema | 指定了用于执行测试的数据库名字，schema也就是database。测试后会删掉 |
| –concurrency | 指定并发线程的数量，也就是模拟多少个客户端同时执行select。可指定多个值，以逗号或者–delimiter参数指定的值做为分隔符。例如：–concurrency=100,200,500。 |
| –engine | 指定要测试的引擎，可以有多个，用逗号隔开。例如：–engines=myisam,innodb。 |
| –no-drop | 指定不清理测试数据 |
| –iterations | 指定测试运行的次数，代表要在不同并发环境下，各自运行测试多少次。指定了这个不能指定no-drop |
| –only-print | 只打印测试语句而不实际执行。 |
| –debug-info, -T | 指定输出额外的内存及CPU统计信息，打印内存和CPU的相关信息。 |
# 举例
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 -uroot -p root --auto-generate-sql-load-type=read,write
- 在这个测试过程中，myisam的读+写的操作时间没有innodb块
- -uroot -p root 这里需要填写用户名和密码
- –auto-generate-sql-load-type=read,write 这里填写的操作就是读+写，用来测试
五、trace----查看MySQL优化器如何选择执行计划
引入
- 从前面学到了explain可以查看SQL执行计划，但是无法知道它为什么做这个决策，如果想确定多种索引方案之间是如何选择的或者排序时选择的是哪种排序方式，有什么好的办法吗？
- trace可以看为啥MySQL会选择这个执行计划，通过trace，能够进一步了解为什么优化器选择A执行而不是选择B执行，或者知道某个排序使用的排序方式。帮助我们更好地理解优化器行为
定义
- trace：查看优化器如何选择执行计划的工具
开启
- 从MySQL5.6开始，支持使用trace查看优化器如何选择执行计划
- 开启该功能会对MySQL性能有所影响，因此之间以分析问题时临时开启
-- 表示开启trace。JSON输出格式 set session optimizer_trace='enabled=on' ,end_markers_in_json=on -- 开启后再执行SQL语句 -- 查看trace分析结果 select *from information_schema.OPTIMISER_TRACE\G -- 关闭trace set session optimizer_trace='enabled=off'
案例分析
- 执行语句
CREATE TABLE `test_trace` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`a` INT(11) DEFAULT NULL,
`b` INT(11) DEFAULT NULL,
`create_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '记录创建时间',
`update_time` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '记录更新时间',
PRIMARY KEY (`id`),
KEY `idx_a` (`a`),
KEY `idx_b` (`b`)
) ENGINE=INNODB DEFAULT CHARSET=utf8mb4;
- 输出结果
QUERY: select * from t1 where a >900 and b > 910 order by a --SQL语句
TRACE: {
"steps": [
{
"join_preparation": { --SQL准备阶段
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `t1`.`id` AS `id`,`t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`create_time` AS `create_time`,`t1`.`update_time` AS `update_time` from `t1` where ((`t1`.`a` > 900) and (`t1`.`b` > 910)) order by `t1`.`a`"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": { --SQL优化阶段
"select#": 1,
"steps": [
{
"condition_processing": { --条件处理
"condition": "WHERE",
"original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))", --原始条件
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))" --等值传递转换
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))" --常量传递转换
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))" --去除没有的条件后的结构
}
] /* steps */
} /* condition_processing */
},
{
"substitute_generated_columns": {
} /* substitute_generated_columns */ --替换虚拟生成列
},
{
"table_dependencies": [ --表依赖详情
{
"table": "`t1`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [ --预估表的访问成本
{
"table": "`t1`",
"range_analysis": {
"table_scan": {
"rows": 1000, --扫描行数
"cost": 207.1 --成本
} /* table_scan */,
"potential_range_indexes": [ --分析可能使用的索引
{
"index": "PRIMARY",
"usable": false, --为false，说明主键索引不可用
"cause": "not_applicable"
},
{
"index": "idx_a", --可能使用索引idx_a
"usable": true,
"key_parts": [
"a",
"id"
] /* key_parts */
},
{
"index": "idx_b", --可能使用索引idx_b
"usable": true,
"key_parts": [
"b",
"id"
] /* key_parts */
}
] /* potential_range_indexes */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_group_by_or_distinct"
} /* group_index_range */,
"analyzing_range_alternatives": { --分析各索引的成本
"range_scan_alternatives": [
{
"index": "idx_a", --使用索引idx_a的成本
"ranges": [
"900 < a" --使用索引idx_a的范围
] /* ranges */,
"index_dives_for_eq_ranges": true, --是否使用index dive（详细描述请看下方的知识扩展）
"rowid_ordered": false, --使用该索引获取的记录是否按照主键排序
"using_mrr": false, --是否使用mrr
"index_only": false, --是否使用覆盖索引
"rows": 100, --使用该索引获取的记录数
"cost": 121.01, --使用该索引的成本
"chosen": true --可能选择该索引
},
{
"index": "idx_b", --使用索引idx_b的成本
"ranges": [
"910 < b"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 90,
"cost": 109.01,
"chosen": true --也可能选择该索引
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": { --分析使用索引合并的成本
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": { --确认最优方法
"range_access_plan": {
"type": "range_scan",
"index": "idx_b",
"rows": 90,
"ranges": [
"910 < b"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 90,
"cost_for_plan": 109.01,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [ --考虑的执行计划
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`t1`",
"best_access_path": { --最优的访问路径
"considered_access_paths": [ --决定的访问路径
{
"rows_to_scan": 90, --扫描的行数
"access_type": "range", --访问类型：为range
"range_details": {
"used_index": "idx_b" --使用的索引为：idx_b
} /* range_details */,
"resulting_rows": 90, --结果行数
"cost": 127.01, --成本
"chosen": true, --确定选择
"use_tmp_table": true
}
] /* considered_access_paths */
} /* best_access_path */,
"condition_filtering_pct": 100,
"rows_for_plan": 90,
"cost_for_plan": 127.01,
"sort_cost": 90,
"new_cost_for_plan": 217.01,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": { --尝试添加一些其他的查询条件
"original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))",
"attached_conditions_computation": [
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`t1`",
"attached": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`t1`.`a`",
"items": [
{
"item": "`t1`.`a`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`t1`.`a`"
} /* clause_processing */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "ORDER BY",
"index_order_summary": {
"table": "`t1`",
"index_provides_order": false,
"order_direction": "undefined",
"index": "idx_b",
"plan_changed": false
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
},
{
"refine_plan": [ --改进的执行计划
{
"table": "`t1`",
"pushed_index_condition": "(`t1`.`b` > 910)",
"table_condition_attached": "(`t1`.`a` > 900)"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": { --SQL执行阶段
"select#": 1,
"steps": [
{
"filesort_information": [
{
"direction": "asc",
"table": "`t1`",
"field": "a"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"usable": false, --未使用优先队列优化排序
"cause": "not applicable (no LIMIT)" --未使用优先队列排序的原因是没有limit
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": { --排序详情
"rows": 90,
"examined_rows": 90, --参与排序的行数
"number_of_tmp_files": 0, --排序过程中使用的临时文件数
"sort_buffer_size": 115056,
"sort_mode": "<sort_key, additional_fields>" --排序模式（详解请看下方知识扩展）
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0 --该字段表示分析过程丢弃的文本字节大小，本例为0，说明没丢弃任何文本
INSUFFICIENT_PRIVILEGES: 0 --查看trace的权限是否不足，0表示有权限查看trace详情
1 row in set (0.00 sec)
输出结果文本的三个过程
- 准备阶段：对应文本中的join_preparation
- 优化阶段：对应文本中的join_optimization
- 执行阶段：对应文本中的join_execution
使用时，重点关注优化阶段和执行阶段
我们关注
- “rows_estimation”: --预估表的访问成本
- analyzing_range_alternatives": --分析各索引的成本
- 在trace结果的alayzing_range_alternatives这一项可以看到：使用索引idx_a的成本为121.01使用idx_b的成本为109.01，显然使用索引idx_b的成本要低一些，因此优化器选择了idx_b索引
547
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
