现场MySQL性能问题分析报告

1、首先把现场环境数据拷贝到本地MySQL
2、本地测试环境机器配置：16G内存，I5-6500 3.2GHz 四核心四线程

3、导入现场数据
host表：217条
interface表：30153条

4、开启profiling

set profiling =1;

5、执行原SQL

select h.* from spider.host h where exists (select i.host_Id from spider.interface i where h.id = i.host_Id and i.mac = '3C:E5:A6:83:C2:18' and tenant_Id='e10adc3949ba59abbe56e057f20f88dd');

6、查看结果

show profiles;

7、查询profile结果(MySQL 5.7之后profiling被移动到了information_schema下，所以show profile for query 2;已经无效)


select
query_id, -- 查询id 它用于标识一个查询
seq, -- 显示序号
(select sum(duration) from information_schema.profiling as innert where innert.query_id = outert.query_id) as total_cost , -- 总用时in seconds
state,-- 状态
duration, -- 持续时间
cpu_user, -- 用户空间的cpu 使用量
cpu_system, -- 内核空间的cpu 使用量
-- context_voluntary, -- 自愿上下文切换
-- context_involuntary, -- 非自愿上下文切换
block_ops_in, -- 块调入次数
block_ops_out, -- 块调出次数
swaps -- 发生swap 的次数
from
information_schema.profiling as outert
where query_id =2
order by
seq;
query_id seq total_cost state duration cpu_user cpu_system block_ops_in block_ops_out swaps
2 355 0.482394 Sending data 0.010085 0 0 null null null
2 356 0.482394 executing 0.000004 0 0 null null null
2 357 0.482394 Sending data 0.010532 0.0156 0 null null null
2 358 0.482394 executing 0.000004 0 0 null null null
2 359 0.482394 Sending data 0.010249 0.0156 0 null null null
2 360 0.482394 executing 0.000005 0 0 null null null
2 361 0.482394 Sending data 0.009931 0 0 null null null
2 362 0.482394 executing 0.000005 0 0 null null null
2 363 0.482394 Sending data 0.010161 0.0156 0 null null null
2 364 0.482394 executing 0.000003 0 0 null null null
2 365 0.482394 Sending data 0.009964 0.0156 0 null null null
2 366 0.482394 executing 0.000003 0 0 null null null
2 367 0.482394 Sending data 0.00998 0 0 null null null
2 368 0.482394 executing 0.000005 0 0 null null null
2 369 0.482394 Sending data 0.010206 0.0156 0 null null null
2 370 0.482394 executing 0.000006 0 0 null null null
2 371 0.482394 Sending data 0.01072 0.0156 0 null null null
2 372 0.482394 executing 0.000005 0 0 null null null
2 373 0.482394 Sending data 0.010745 0 0 null null null
2 374 0.482394 executing 0.000006 0 0 null null null
2 375 0.482394 Sending data 0.010069 0.0156 0 null null null
2 376 0.482394 executing 0.000004 0 0 null null null
2 377 0.482394 Sending data 0.009987 0.0156 0 null null null
2 378 0.482394 executing 0.000004 0 0 null null null
2 379 0.482394 Sending data 0.010143 0 0 null null null
2 380 0.482394 executing 0.000005 0 0 null null null
2 381 0.482394 Sending data 0.010385 0.0156 0 null null null
2 382 0.482394 executing 0.000003 0 0 null null null
2 383 0.482394 Sending data 0.009962 0.0156 0 null null null
2 384 0.482394 executing 0.000004 0 0 null null null
2 385 0.482394 Sending data 0.009858 0 0 null null null
2 386 0.482394 executing 0.000003 0 0 null null null
2 387 0.482394 Sending data 0.009794 0.0156 0 null null null
2 388 0.482394 executing 0.000003 0 0 null null null
2 389 0.482394 Sending data 0.009844 0.0156 0 null null null
2 390 0.482394 executing 0.000004 0 0 null null null
2 391 0.482394 Sending data 0.011117 0 0 null null null
2 392 0.482394 executing 0.000005 0 0 null null null
2 393 0.482394 Sending data 0.009924 0.0156 0 null null null
2 394 0.482394 executing 0.000004 0 0 null null null
2 395 0.482394 Sending data 0.009747 0 0 null null null
2 396 0.482394 executing 0.000004 0 0 null null null
2 397 0.482394 Sending data 0.010594 0.0156 0 null null null
2 398 0.482394 executing 0.000005 0 0 null null null
2 399 0.482394 Sending data 0.00978 0.0156 0 null null null
2 400 0.482394 executing 0.000005 0 0 null null null
2 401 0.482394 Sending data 0.009816 0 0 null null null
2 402 0.482394 executing 0.000006 0 0 null null null
2 403 0.482394 Sending data 0.010401 0.0156 0 null null null
2 404 0.482394 executing 0.000005 0 0 null null null
2 405 0.482394 Sending data 0.009956 0.0156 0 null null null
2 406 0.482394 executing 0.000003 0 0 null null null
2 407 0.482394 Sending data 0.009643 0 0 null null null
2 408 0.482394 executing 0.000005 0 0 null null null
2 409 0.482394 Sending data 0.009805 0.0156 0 null null null
2 410 0.482394 executing 0.000003 0 0 null null null
2 411 0.482394 Sending data 0.009743 0.0156 0 null null null
2 412 0.482394 executing 0.000004 0 0 null null null
2 413 0.482394 Sending data 0.009934 0 0 null null null
2 414 0.482394 executing 0.000005 0 0 null null null
2 415 0.482394 Sending data 0.010076 0.0156 0 null null null
2 416 0.482394 executing 0.000005 0 0 null null null
2 417 0.482394 Sending data 0.00997 0.0156 0 null null null
2 418 0.482394 executing 0.000004 0 0 null null null
2 419 0.482394 Sending data 0.009954 0 0 null null null
2 420 0.482394 executing 0.000002 0 0 null null null
2 421 0.482394 Sending data 0.009765 0.0156 0 null null null
2 422 0.482394 executing 0.000002 0 0 null null null
2 423 0.482394 Sending data 0.009669 0 0 null null null
2 424 0.482394 executing 0.000002 0 0 null null null
2 425 0.482394 Sending data 0.010328 0.0156 0 null null null
2 426 0.482394 executing 0.000003 0 0 null null null
2 427 0.482394 Sending data 0.009812 0.0156 0 null null null
2 428 0.482394 executing 0.000006 0 0 null null null
2 429 0.482394 Sending data 0.010008 0 0 null null null
2 430 0.482394 executing 0.000019 0 0 null null null
2 431 0.482394 Sending data 0.009829 0.0156 0 null null null
2 432 0.482394 executing 0.000004 0 0 null null null
2 433 0.482394 Sending data 0.009756 0.0156 0 null null null
2 434 0.482394 executing 0.000003 0 0 null null null
2 435 0.482394 Sending data 0.009751 0 0 null null null
2 436 0.482394 executing 0.000002 0 0 null null null
2 437 0.482394 Sending data 0.009769 0.0156 0 null null null
2 438 0.482394 executing 0.000003 0 0 null null null
2 439 0.482394 Sending data 0.009678 0 0 null null null
2 440 0.482394 executing 0.000002 0 0 null null null
2 441 0.482394 Sending data 0.009909 0.0156 0 null null null
2 442 0.482394 executing 0.000005 0 0 null null null
2 443 0.482394 Sending data 0.01087 0.0156 0 null null null
2 444 0.482394 executing 0.000006 0 0 null null null
2 445 0.482394 Sending data 0.009896 0 0 null null null
2 446 0.482394 executing 0.000006 0 0 null null null
2 447 0.482394 Sending data 0.009799 0.0156 0 null null null
2 448 0.482394 executing 0.000005 0 0 null null null
2 449 0.482394 Sending data 0.010074 0.0156 0 null null null
2 450 0.482394 end 0.000007 0 0 null null null
2 451 0.482394 query end 0.000005 0 0 null null null
2 452 0.482394 closing tables 0.000009 0 0 null null null
2 453 0.482394 freeing items 0.000146 0 0 null null null
2 454 0.482394 cleaning up 0.00003 0 0 null null null

可以看到每条查询占用CPU为1.5%

8、正常的一条普通查询，可以看到没有那么多的sending data和executing，因为query_id=2的查询用了组合查询，会产生很多临时表（这个我猜的），造成大量CPU占用

解决方案

修改sql，换种查询方式

select h.* from spider.host h , spider.interface i where h.id = i.host_Id and i.mac = '3C:E5:A6:83:C2:18' and h.tenant_Id='e10adc3949ba59abbe56e057f20f88dd';
最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容