Innodb
對同一張表的第四次內連接顯著增加了查詢持續時間
一旦我向特定表添加第四個內部聯接，查詢持續時間就會顯著增加：從 1 秒到 45 秒。
有問題的表是
deployments d4。SQL 查詢：
select a.id as asset_id, a.asset_group_id, ac.id as asset_configuration_id, ac.domain_id, ac.server_id, e.acronym as environment, e.id as environment_id, d.name as domain, -- last deployment d1.id as last_deployment_id, d1.revision as last_revision, d1.deployed_date as last_deployed_date, d1.deployed_by as last_deployed_by, d1.is_failed as last_is_failed, -- last successful deployment d2.id as last_successful_deployment_id, d2.revision as last_successful_revision, d2.deployed_date as last_successful_deployed_date, d2.deployed_by as last_successful_deployed_by, -- last successful environment deployment d3.revision as last_successful_environment_revision, d3.deployed_date as last_successful_environment_deployed_date, -- last environment deployment d4.revision as last_environment_revision, d4.deployed_date as last_environment_date, d4.is_failed as last_environment_is_failed from assets a inner join asset_configurations ac on a.id=ac.asset_id inner join domains d on ac.domain_id=d.id inner join servers s on ac.server_id=s.id inner join environments_servers es on s.id=es.server_id inner join environments e on es.environment_id=e.id -- last deployment inner join deployments d1 on ac.id=d1.asset_configuration_id inner join vw_last_deployment_statuses ld on d1.asset_configuration_id = ld.asset_configuration_id and d1.deployed_date=ld.deployed_date -- last successful deployment inner join deployments d2 on ac.id=d2.asset_configuration_id inner join vw_last_successful_deployment_statuses lsd on d2.asset_configuration_id = lsd.asset_configuration_id and d2.deployed_date=lsd.deployed_date -- last successful environment deployment inner join deployments d3 on ac.id=d3.asset_configuration_id inner join vw_last_successful_environment_deployment_statuses lsed on a.id=lsed.asset_id and e.id=lsed.environment_id and d3.deployed_date=lsed.deployed_date -- last environment deployment inner join deployments d4 on ac.id=d4.asset_configuration_id inner join vw_last_environment_deployment_statuses led on a.id=led.asset_id and e.id=led.environment_id and d4.deployed_date=led.deployed_date where ac.id = 169;更新#1
這是沒有第四個內部連接的解釋：
id,select_type,table,type,possible_keys,key,key_len,ref,rows,Extra 1,PRIMARY,ac,const,"PRIMARY,FK_asset_configurations_assets_id,FK_asset_configurations_domains_id,FK_asset_configurations_servers_id",PRIMARY,4,const,1, 1,PRIMARY,d,const,PRIMARY,PRIMARY,4,const,1, 1,PRIMARY,s,const,PRIMARY,PRIMARY,4,const,1,"Using index" 1,PRIMARY,a,const,PRIMARY,PRIMARY,4,const,1, 1,PRIMARY,es,ref,"PRIMARY,FK_environments_servers_servers_id2",FK_environments_servers_servers_id2,4,const,1,"Using index" 1,PRIMARY,e,eq_ref,PRIMARY,PRIMARY,4,aps_cmdb.es.environment_id,1, 1,PRIMARY,d1,ref,FK_deployments_asset_configurations_id,FK_deployments_asset_configurations_id,4,const,55, 1,PRIMARY,d2,ref,FK_deployments_asset_configurations_id,FK_deployments_asset_configurations_id,4,const,55, 1,PRIMARY,d3,ref,FK_deployments_asset_configurations_id,FK_deployments_asset_configurations_id,4,const,55, 1,PRIMARY,<derived4>,ALL,NULL,NULL,NULL,NULL,171,"Using where; Using join buffer" 1,PRIMARY,<derived3>,ALL,NULL,NULL,NULL,NULL,227,"Using where; Using join buffer" 1,PRIMARY,<derived2>,ALL,NULL,NULL,NULL,NULL,230,"Using where; Using join buffer" 4,DERIVED,a,index,PRIMARY,FK_assets_asset_package_deployment_methods_id,1,NULL,101,"Using index; Using temporary; Using filesort" 4,DERIVED,ac,ref,"PRIMARY,FK_asset_configurations_assets_id,FK_asset_configurations_servers_id",FK_asset_configurations_assets_id,4,aps_cmdb.a.id,1, 4,DERIVED,s,eq_ref,PRIMARY,PRIMARY,4,aps_cmdb.ac.server_id,1,"Using index" 4,DERIVED,es,ref,FK_environments_servers_servers_id2,FK_environments_servers_servers_id2,4,aps_cmdb.ac.server_id,1,"Using index" 4,DERIVED,d,ref,FK_deployments_asset_configurations_id,FK_deployments_asset_configurations_id,4,aps_cmdb.ac.id,42,"Using where" 3,DERIVED,deployments,index,NULL,FK_deployments_asset_configurations_id,4,NULL,9877,"Using where" 2,DERIVED,deployments,index,NULL,FK_deployments_asset_configurations_id,4,NULL,9877,這是第四個內部連接的解釋：
id select_type table type possible_keys key key_len ref rows Extra 1 PRIMARY ac const PRIMARY,FK_asset_configurations_assets_id,FK_asset_configurations_domains_id,FK_asset_configurations_servers_id PRIMARY 4 const 1 1 PRIMARY d const PRIMARY PRIMARY 4 const 1 1 PRIMARY s const PRIMARY PRIMARY 4 const 1 "Using index" 1 PRIMARY a const PRIMARY PRIMARY 4 const 1 1 PRIMARY es ref PRIMARY,FK_environments_servers_servers_id2 FK_environments_servers_servers_id2 4 const 1 "Using index" 1 PRIMARY e eq_ref PRIMARY PRIMARY 4 aps_cmdb.es.environment_id 1 1 PRIMARY d1 ref FK_deployments_asset_configurations_id FK_deployments_asset_configurations_id 4 const 55 1 PRIMARY d2 ref FK_deployments_asset_configurations_id FK_deployments_asset_configurations_id 4 const 55 1 PRIMARY d3 ref FK_deployments_asset_configurations_id FK_deployments_asset_configurations_id 4 const 55 1 PRIMARY d4 ref FK_deployments_asset_configurations_id FK_deployments_asset_configurations_id 4 const 55 1 PRIMARY <derived4> ALL NULL NULL NULL NULL 171 "Using where; Using join buffer" 1 PRIMARY <derived5> ALL NULL NULL NULL NULL 174 "Using where; Using join buffer" 1 PRIMARY <derived3> ALL NULL NULL NULL NULL 227 "Using where; Using join buffer" 1 PRIMARY <derived2> ALL NULL NULL NULL NULL 230 "Using where; Using join buffer" 5 DERIVED a index PRIMARY FK_assets_asset_package_deployment_methods_id 1 NULL 101 "Using index; Using temporary; Using filesort" 5 DERIVED ac ref PRIMARY,FK_asset_configurations_assets_id,FK_asset_configurations_servers_id FK_asset_configurations_assets_id 4 aps_cmdb.a.id 1 5 DERIVED s eq_ref PRIMARY PRIMARY 4 aps_cmdb.ac.server_id 1 "Using index" 5 DERIVED es ref FK_environments_servers_servers_id2 FK_environments_servers_servers_id2 4 aps_cmdb.ac.server_id 1 "Using index" 5 DERIVED d ref FK_deployments_asset_configurations_id FK_deployments_asset_configurations_id 4 aps_cmdb.ac.id 42 4 DERIVED a index PRIMARY FK_assets_asset_package_deployment_methods_id 1 NULL 101 "Using index; Using temporary; Using filesort" 4 DERIVED ac ref PRIMARY,FK_asset_configurations_assets_id,FK_asset_configurations_servers_id FK_asset_configurations_assets_id 4 aps_cmdb.a.id 1 4 DERIVED s eq_ref PRIMARY PRIMARY 4 aps_cmdb.ac.server_id 1 "Using index" 4 DERIVED es ref FK_environments_servers_servers_id2 FK_environments_servers_servers_id2 4 aps_cmdb.ac.server_id 1 "Using index" 4 DERIVED d ref FK_deployments_asset_configurations_id FK_deployments_asset_configurations_id 4 aps_cmdb.ac.id 42 "Using where" 3 DERIVED deployments index NULL FK_deployments_asset_configurations_id 4 NULL 9877 "Using where" 2 DERIVED deployments index NULL FK_deployments_asset_configurations_id 4 NULL 9877更新#2
範例
vw_last_deployment_statusselect `aps_cmdb`.`deployments`.`asset_configuration_id` AS `asset_configuration_id`, max(`aps_cmdb`.`deployments`.`deployed_date`) AS `deployed_date` from `aps_cmdb`.`deployments` group by `aps_cmdb`.`deployments`.`asset_configuration_id`更新#3
提取所有視圖選擇語句並將其全部捆綁到一個語句中：
select a.id as asset_id, a.asset_group_id, ac.id as asset_configuration_id, ac.domain_id, ac.server_id, e.acronym as environment, e.id as environment_id, d.name as domain, -- last deployment d1.id as last_deployment_id, d1.revision as last_revision, d1.deployed_date as last_deployed_date, d1.deployed_by as last_deployed_by, d1.is_failed as last_is_failed, -- last successful deployment d2.id as last_successful_deployment_id, d2.revision as last_successful_revision, d2.deployed_date as last_successful_deployed_date, d2.deployed_by as last_successful_deployed_by, -- last successful environment deployment d3.revision as last_successful_environment_revision, d3.deployed_date as last_successful_environment_deployed_date -- last environment deployment -- d4.revision as last_environment_revision, -- d4.deployed_date as last_environment_date, -- d4.is_failed as last_environment_is_failed from assets a inner join asset_configurations ac on a.id=ac.asset_id inner join domains d on ac.domain_id=d.id inner join servers s on ac.server_id=s.id inner join environments_servers es on s.id=es.server_id inner join environments e on es.environment_id=e.id -- last deployment inner join deployments d1 on ac.id=d1.asset_configuration_id inner join ( select `aps_cmdb`.`deployments`.`asset_configuration_id` AS `asset_configuration_id`, max(`aps_cmdb`.`deployments`.`deployed_date`) AS `deployed_date` from `aps_cmdb`.`deployments` group by `aps_cmdb`.`deployments`.`asset_configuration_id`) ld on d1.asset_configuration_id = ld.asset_configuration_id and d1.deployed_date=ld.deployed_date -- last successful deployment inner join deployments d2 on ac.id=d2.asset_configuration_id inner join ( select `aps_cmdb`.`deployments`.`asset_configuration_id` AS `asset_configuration_id`, max(`aps_cmdb`.`deployments`.`deployed_date`) AS `deployed_date` from `aps_cmdb`.`deployments` where (`aps_cmdb`.`deployments`.`is_failed` = 0) group by `aps_cmdb`.`deployments`.`asset_configuration_id`) lsd on d2.asset_configuration_id = lsd.asset_configuration_id and d2.deployed_date=lsd.deployed_date -- last successful environment deployment inner join deployments d3 on ac.id=d3.asset_configuration_id inner join ( select `a`.`id` AS `asset_id`, `es`.`environment_id` AS `environment_id`, max(`d`.`deployed_date`) AS `deployed_date` from ((((`aps_cmdb`.`deployments` `d` join `aps_cmdb`.`asset_configurations` `ac` ON ((`d`.`asset_configuration_id` = `ac`.`id`))) join `aps_cmdb`.`assets` `a` ON ((`ac`.`asset_id` = `a`.`id`))) join `aps_cmdb`.`servers` `s` ON ((`ac`.`server_id` = `s`.`id`))) join `aps_cmdb`.`environments_servers` `es` ON ((`s`.`id` = `es`.`server_id`))) where (`d`.`is_failed` = 0) group by `a`.`id` , `es`.`environment_id`) lsed on a.id=lsed.asset_id and e.id=lsed.environment_id and d3.deployed_date=lsed.deployed_date -- last environment deployment inner join deployments d4 on ac.id=d4.asset_configuration_id inner join ( select `a`.`id` AS `asset_id`, `es`.`environment_id` AS `environment_id`, max(`d`.`deployed_date`) AS `deployed_date` from ((((`aps_cmdb`.`deployments` `d` join `aps_cmdb`.`asset_configurations` `ac` ON ((`d`.`asset_configuration_id` = `ac`.`id`))) join `aps_cmdb`.`assets` `a` ON ((`ac`.`asset_id` = `a`.`id`))) join `aps_cmdb`.`servers` `s` ON ((`ac`.`server_id` = `s`.`id`))) join `aps_cmdb`.`environments_servers` `es` ON ((`s`.`id` = `es`.`server_id`))) where (`d`.`is_failed` = 0) group by `a`.`id` , `es`.`environment_id` ) led on a.id=led.asset_id and e.id=led.environment_id and d4.deployed_date=led.deployed_date where ac.id = 169;更新#4
即使使用提取的視圖 SQL，使用第四個內部連接的單個語句也需要 40 秒。
我還從省略了單個 blob 欄位的部署表中創建了一個部署視圖。仍然需要40秒。
更新#5
做更多的分析……
沒有內連接：
- Innodb_rows_read 188 548 從 InnoDB 表中讀取的行數。
- Innodb_data_read 19 349 504 到目前為止讀取的數據量，以字節為單位。
使用內部連接：
- Innodb_rows_read 19 207 716 從 InnoDB 表中讀取的行數。
- Innodb_data_read 2 147 205 120 到目前為止讀取的數據量，以字節為單位。
嘗試僅使用您需要的數據製作一個臨時表並對其進行連接。對於每個自我加入，做另一個臨時表。我會從一個開始，然後檢查性能。
測試內部查詢以查看它的執行情況。如果您缺少外鍵上的索引，性能可能會很慢。
結果集的大小可能會導致內部聯接出現性能問題。內連接可能需要為結果集中的每一行執行一次。
沒有足夠的記憶體可能會導致問題，因為可能需要從磁碟重新讀取數據。嘗試
sar在查詢執行時使用 來監視磁碟活動。如果它顯著增加，那麼這可能是一個問題。連續兩次執行查詢時進行監視，以查看數據是否正在緩衝。