第四，对同一个表的内部联接显着地增加了查询持续时间。

Question

一旦我将第四个内部连接添加到特定的表中，查询的持续时间就会显著增加:从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_status实例

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`

更新#3

提取所有视图select语句并将其捆绑到一个语句中：

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到目前为止读取的数据量，以字节为单位。

Answer 1

尝试使用所需的数据创建一个临时表，并对其进行连接。对于每个自联接，执行另一个临时表。我会从一次一次开始，检查一下表演。

Answer 2

测试内部查询以查看它的执行情况。如果缺少外键上的索引，性能可能会非常慢。

结果集的大小会导致内部联接的性能问题。可能需要对结果集中的每一行运行一次内部联接。

内存不足会导致问题，因为数据可能需要从磁盘重新读取。在查询运行时，尝试使用sar监视磁盘活动。如果大幅增加，这可能是一个问题。在一行中运行两次查询时监视数据是否正在缓冲。