在MySQL 5.7中并发查询执行速度较慢

Question

在MySQL 5.7中，并发查询执行速度很慢。

当我只运行this below查询时，它需要-5.28秒

select pkid,lastname
    from Table1
    where pkid in (select fkid from Table2)
    order by 2 desc limit 10; 

但是，如果我同时执行相同的查询10次，则每次查询大约需要11秒。我不确定为什么会发生这种情况，即使我的innodb_thread_concurrency是10。

并发执行统计数据- no_of_queries与each_query_time：

1 time - 5.3sec
5 time - 7.8sec
10 times - 11sec

变量：

max_connections - 1500
innodb_thread_concurrency - 10

Cpu - 16core

谁能告诉我这里我错过了什么。

注意:这不是查询优化。我这里的问题是并发查询执行很慢。只是为了显示单独执行单个查询和并发执行相同的查询10/5项之间的区别，我使用了这个查询。

解释

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "1541542.63"
    },
    "ordering_operation": {
      "using_temporary_table": true,
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "1.00"
      },
      "nested_loop": [
        {
          "table": {
            "table_name": "Table2",
            "access_type": "index",
            "possible_keys": [
              "Table2_FK4_IDX"
            ],
            "key": "Table2_FK4_IDX",
            "used_key_parts": [
              "FKID"
            ],
            "key_length": "9",
            "rows_examined_per_scan": 1246072,
            "rows_produced_per_join": 732208,
            "filtered": "58.76",
            "using_index": true,
            "loosescan": true,
            "cost_info": {
              "read_cost": "2586.21",
              "eval_cost": "146441.71",
              "prefix_cost": "149027.92",
              "data_read_per_join": "2G"
            },
            "used_columns": [
              "TABLE2ID",
              "FKID"
            ],
            "attached_condition": "(`db1234`.`Table2`.`FKID` is not null)"
          }
        },
        {
          "table": {
            "table_name": "Table1",
            "access_type": "eq_ref",
            "possible_keys": [
              "PRIMARY"
            ],
            "key": "PRIMARY",
            "used_key_parts": [
              "PKID"
            ],
            "key_length": "8",
            "ref": [
              "db1234.Table2.FKID"
            ],
            "rows_examined_per_scan": 1,
            "rows_produced_per_join": 1,
            "filtered": "100.00",
            "cost_info": {
              "read_cost": "1246072.00",
              "eval_cost": "0.20",
              "prefix_cost": "1541541.63",
              "data_read_per_join": "19K"
            },
            "used_columns": [
              "PKID",
              "LASTNAME"
            ]
          }
        }
      ]
    }
  }
}

Answer 1

查询正在争用资源来做同样的事情。它们相互干扰。

如果您使用exists编写查询并具有正确的索引，那么整个过程可能会更快：

select t1.pkid, t1.lastname
from Table1 t1
where exists (select 1 from table2 t2 where t2.fkid = t1.pkid)
order by 2 desc
limit 10; 

您肯定想在table2(fkid)上建立一个索引(尽管您可以通过MySQL中的foreign key声明免费获得此索引)。有可能在table1(lastname desc, pkid)上建立索引也会有所帮助。