问题描述

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; 

并发执行统计信息-no_of_queries vs each_Query_time：

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

变量：

max_connections - 1500
innodb_thread_concurrency - 10

CPU-16core

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

解释

{
  "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"
            ]
          }
        }
      ]
    }
  }
}

推荐答案

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

如果您使用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)上的索引也有可能有所帮助。

这篇关于MySQL 5.7中的并发查询执行速度较慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！