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';
网友评论