分析工具
Mysqldumpslow
mysqldumpslow 是 MySQL 自带的用来分析慢查询的工具, 基于 perl 开发.
Windows 下需要下载安装 perl 编译器, 下载地址: http://pan.baidu.com/s/1i3GLKAp
参考:
C:\Program Files\MySQL\MySQL Server 5.6\bin>perl mysqldumpslow.pl --help
image.PNG
perl mysqldumpslow.pl -r -s c -a -t 3 "C:\ProgramData\MySQL\MySQL Server 5.7\Data\bxg_mysql_slow.log"
image.PNG
- Count: 4(执行了多少次)
- Time=375.01s(每次执行的时间) (1500s)(一共执行了多少时间)
- Lock=0.00s (0s)(等待锁的时间)
- Rows=10200.3(每次返回的记录数) (40801)(总共返回的记录数), username[password]@[10.194.172.41]
- mysqlsla
Mysqlsla 是 daniel-nichter 用 perl 写的一个脚本, 专门用于处理分析 MySQL 的日志而存在. 通过 MySQL 的日志主要分为: General log,slow log,binary log 三种. 通 过 query 日志, 我们可以分析业务的逻辑, 业务特点. 通过 slow log, 我们可以找到服务器的瓶颈. 通过 binary log, 我们可以恢复数据. Mysqlsla 可以处理其中的任意日志.
参考: https://yq.aliyun.com/articles/59260
pt-query-digest
pt-query-digest 是用于分析 MySQL 慢查询的一个工具, 它可以分析 binlog,General log,slowlog, 也可以通过 SHOWPROCESSLIST 或者通过 tcpdump 抓取的 MySQL 协议数据来进行分析. 可以把分析结果输出到文件中, 分析过程是先对查询语句的条件进行参数化, 然后对参数化以后的查询进行分组统计, 统计出各查询的执行时间, 次数, 占比等, 可以借助分析结果找出问题进行优化.
参考:
EXPLAIN 执行计划
用法
EXPLAIN SELECT ......
经常使用的方式, 查看 sql 的执行计划
EXPLAIN EXTENDED SELECT ......
将执行计划 "反编译" 成 SELECT 语句, 运行 SHOW WARNINGS , 可得到被 MySQL 优化器优化后的查询语句.
EXPLAIN PARTITIONS SELECT ......
用于分区表的 EXPLAIN 生成 QEP 的信息, 用来查看索引是否正在被使用, 并且输出其使用的索引的信息.
- EXPLAIN SELECT id,fname,lname FROM person WHERE lname='x8RJWmQX' AND
- id in (select id from person where id BETWEEN 0 and 6000);
image.PNG
id
包含一组数字, 表示查询中执行 select 子句或操作表的顺序, id 相同执行顺序由上至下. 如果是子查询, id 的序号会递增, id 值越大优先级越高, 越先被执行
select_type
所使用的 SELECT 查询类型, 包括以下常见类型:
a. SIMPLE: 表示为简单的 SELECT, 查询中不包含子查询或者 UNION
b. PRIMARY: 查询中若包含任何复杂的子部分, 最外层查询则被标记为 PRIMARY
c. SUBQUERY: 在 SELECT 或 WHERE 列表中包含了子查询, 该子查询被标记为 SUBQUERY
d. UNION: 表连接中的第二个或后面的 select 语句, 若第二个 SELECT 出现在 UNION 之后, 则被标记为 UNION.
e. DERIVED:DERIVED(衍生)用来表示包含在 from 子句中的子查询的 select. 若 UNION 包含在 FROM 子句的子查询中, 外层 SELECT 将被标记为 DERIVED.MySQL 会递归执行并将结果放到一个临时表中. 服务器内部称为 "派生表", 因为该临时表是从子查询中派生出来的
f.UNION RESULT: 从 UNION 表获取结果的 SELECT 被标记为 UNION RESULT
g.DEPENDENT: 意味着 select 依赖于外层查询中发现的数据.
h.UNCACHEABLE: 意味着 select 中的某些特性阻止结果被缓存于一个 item_cache 中.
table
所使用的的数据表的名字, 他们按被读取的先后顺序排列.
type
表示 MySQL 在表中找到所需行的方式, 又称 "访问类型". 取值按优劣排序为 NULL****>system> const> eq_ref> ref> fulltext> ref_or_null> index_merge> unique_subquery> index_subquery> range> index> ALL. 一般来说, 得保证查询至少达到 range 级别, 最好能达到 ref.
a.ALL:Full Table Scan 全表扫描, MySQL 将遍历全表以找到匹配的行.
b.index:Full Index Scan 全索引扫描, index 与 ALL 区别为 index 类型只遍历索引树
c. range: 索引范围扫描, 对索引的扫描开始于某一点, 返回匹配值域的行. 显而易见的索引范围扫描是带有 between 或者 where 子句里带有 <,> 查询. 当 MySQL 使用索引去查找一系列值时, 例如 IN()和 OR 列表, 也会显示 range(范围扫描), 当然性能上面是有差异的.
d. ref_or_null: 该联接类型如同 ref, 但是添加了 MySQL 可以专门搜索包含 NULL 值的行.
e. index_merge: 该联接类型表示使用了索引合并优化方法.
f. unique_subquery: 该类型替换了下面形式的 IN 子查询的 ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) .unique_subquery 是一个索引查找函数, 可以完全替换子查询, 效率更高.
g. index_subquery: 该联接类型类似于 unique_subquery. 可以替换 IN 子查询, 但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)
h.ref: 就是连接程序无法根据键值只取得一条记录, 使用索引的最左前缀或者索引不是 primary key 或 unique 索引的情况. 当根据键值只查询到少数几条匹配的记录时, 这就是一个不错的连接类型.
i.eq_ref: 类似 ref, 区别就在使用的索引是唯一索引, 对于每个索引键值, 表中只有一条记录匹配, 简单来说, 就是多表连接中使用 primary key 或者 unique key 作为关联条件.
j.const,system: 当 MySQL 对查询某部分进行优化, 并转换为一个常量时, 使用这些类型访问. 如将主键置于 where 列表中, MySQL 就能将该查询转换为一个常量.
注: system 是 const 类型的特例, 当查询的表只有一行的情况下, 使用 system
k.NULL:MySQL 在优化过程中分解语句, 执行时甚至不用访问表或索引, 例如从一个索引列里选取最小值可以通过单独索引查找完成.
explain select * from address where id = (select min(id) from person);
image.PNG
possible_keys
指出 MySQL 能使用哪个索引在表中找到记录, 查询涉及到的字段上若存在索引, 则该索引将被列出, 但不一定被查询使用
key
显示 MySQL 在查询中实际使用的索引, 若没有使用索引, 显示为 NULL
key_len
表示索引中使用的字节数, 可通过该列计算查询中使用的索引的长度(key_len 显示的值为索引字段的最大可能长度, 并非实际使用长度. 如果键是 NULL, 则长度为 NULL.
ref
显示索引的哪一列被使用了, 有时候会是一个常量: 表示哪些列或常量被用于用于查找索引列上的值, 可能值为库. 表. 字段, 常量, null.
rows
MySQL 根据表统计信息及索引选用情况, 估算的找到所需的记录所需要读取的行数.
filtered
显示了通过条件过滤出的行数的百分比估计值.
extra
包含不适合在其他列中显示但十分重要的额外信息, 提供了与关联操作有关的信息, 没有则什么都不写.
a.Using index: 该值表示相应的 select 操作中使用了覆盖索引(Covering Index).
MySQL 可以利用索引返回 select 列表中的字段, 而不必根据索引再次读取数据文件包含所有满足查询需要的数据的索引称为覆盖索引(Covering Index). 注意: 如果要使用覆盖索引, 一定要注意 select 列表中只取出需要的列, 不可 select *, 因为如果将所有字段一起做索引会导致索引文件过大, 查询性能下降.
b.Using where: 表示 MySQL 服务器将在存储引擎检索行后再进行过滤. 许多 where 条件里涉及索引中的列, 当 (并且如果) 它读取索引时, 就能被存储引擎检验, 因此不是所有带 where 字句的查询都会显示 "Using where". 有时 "Using where" 的出现就是一个暗示: 查询可受益与不同的索引.
c.Using temporary: 表示 MySQL 需要使用临时表来存储结果集, 常见于排序和分组查询. 这个值表示使用了内部临时 (基于内存的) 表. 一个查询可能用到多个临时表. 有很多原因都会导致 MySQL 在执行查询期间创建临时表. 两个常见的原因是在来自不同表的上使用了 DISTINCT, 或者使用了不同的 ORDER BY 和 GROUP BY 列. 可以强制指定一个临时表使用基于磁盘的 MyISAM 存储引擎. 这样做的原因主要有两个: 1)内部临时表占用的空间超过 min(tmp_table_size,max_heap_table_size)系统变量的限制; 2)使用了 TEXT/BLOB 列.
d. Using filesort:MySQL 中无法利用索引完成的排序操作称为 "文件排序"
e. Using join buffer: 改值强调了在获取连接条件时没有使用索引, 并且需要连接缓冲区来存储中间结果. 如果出现了这个值, 那应该注意, 根据查询的具体情况可能需要添加索引来改进能.
f. Impossible where: 这个值强调了 where 语句会导致没有符合条件的行.
h. Select tables optimized away: 这个值意味着仅通过使用索引, 优化器可能仅从聚合函数结果中返回一行.
I. Index merges: 当 MySQL 决定要在一个给定的表上使用超过一个索引的时候, 就会出现以下格式中的一个, 详细说明使用的索引以及合并的类型.
- Using sort_union(...)
- Using union(...)
- Using intersect(...)
小结
• EXPLAIN 不考虑各种 Cache
• EXPLAIN 不能显示 MySQL 在执行查询时所作的优化工作
• 部分统计信息是估算的, 并非精确值
• EXPALIN 只能解释 SELECT 操作, 其他操作要重写为 SELECT 后查看执行计划.
• EXPLAIN 不会告诉你关于触发器, 存储过程的信息或用户自定义函数对查询的影响情况
Profiling 的使用
要想优化一条 Query, 就须要清楚这条 Query 的性能瓶颈到底在哪里, 是消耗的 CPU 计算太多, 还是需要的 IO 操作太多? 要想能够清楚地了解这些信息, 可以通过 Query Profiler 功能得到.
Query Profiler 是 MySQL 自带的一种 query 诊断分析工具, 通过它可以分析出一条 SQL 语句的性能瓶颈在什么地方. 通常我们是使用的 explain, 以及 slow query log 都无法做到精确分析, 但是 Query Profiler 却可以定位出一条 SQL 语句执行的各种资源消耗情况, 比如 CPU,IO 等, 以及该 SQL 执行所耗费的时间等.
用法
(1)通过执行 "set profiling" 命令, 可以开启关闭 QueryProfiler 功能
MySQL> SET global profiling=on;
(2)查看相关变量
show VARIABLES like '%profiling%';
(3)设置保存数量默认 15 条, 最大值为 100
MySQL> set profiling_history_size=100;
(4)在开启 Query Profiler 功能之后, MySQL 就会自动记录所有执行的 Query 的 profile 信息, 下面执行 n 条 Query 作为测试
select * from person limit 10000,100;
(3)获取当前系统中保存的多个 Query 的 profile 的概要信息
MySQL> show profiles;
image.PNG
(4)针对单个 Query 获取详细的 profile 信息.
可以根据概要信息中的 Query_ID 来获取某个 Query 在执行过程中详细的 profile 信息. 例如查看 CPU 和 io 的详细信息
show profile CPU,block io for query 501;
image.PNG
show profile ALL for query 501;
ALL : 显示所有信息
|BLOCK IO : 块设备 IO 输入输出次数
|CONTEXT SWITCHES: 上下文切换相关开销
|CPU: 用户和系统的 CPU 使用情况
|IPC: 显示发送和接收消息的相关消耗
|MEMORY: 内存消耗情况(该版本 is not currently implemented)
|PAGE FAULTS: 显示主要和次要页面故障相关的开销
|SOURCE: 显示和 Source_function,Source_file,Source_line 相关的开销信息
|SWAPS: 显示交换次数相关的开销
注意: profiling 被应用在每一个会话中, 当前会话关闭后, profiling 统计的信息将丢失.
last_query_cost
查上一个查询的代价, 而且它是 io_cost 和 cpu_cost 的开销总和, 它通常也是我们评价一个查询的执行效率的一个常用指标. last_query_cost 对于简单的查询可以精确的得到计算, 但于包含子查询或 union 的复杂查询值是 0.
show status like 'last_query_cost';
timestampdiff 查看执行时间
这种方法有一点要注意, 就是三条 sql 语句要尽量连一起执行, 不然误差太大, 根本不准.
- set @d=now();
- select id from person where lname='x8RJWmQX';
- select timestampdiff(second,@d,now());
如果是用命令行来执行的话, 有一点要注意, 就是在 select timestampdiff(second,@d,now()); 后面, 一定要多 copy 一个空行, 不然最后一个 sql 要你自己按回车执行, 这样就不准了.
第三方工具查看执行时间
第三方 MySQL 客户端工具都自带 sql 执行时间显示功能, 如 navicat,sqlyog 等等.
数据库连接进程列表
show processlist;
image.PNG
来源: http://www.jianshu.com/p/97240fad5385