如何使用自联接优化此查询?

horcle_buzz

我有下表:

CREATE TABLE lab_data (
  id int(11) NOT NULL,
  patient_sid int(11) DEFAULT NULL,
  double_value double DEFAULT NULL,
  string_value varchar(7) DEFAULT NULL,
  data_type_id int(11) DEFAULT NULL,
  event_date datetime DEFAULT NULL,
  attribute_id int(11) DEFAULT NULL,
  lft int(11) DEFAULT NULL,
  rgt int(11) DEFAULT NULL,
  parent int(11) DEFAULT NULL,
  num_children int(11) DEFAULT NULL,
  PRIMARY KEY (id),
  KEY idx_bucket (attribute_id,string_value),
  KEY idx_test (attribute_id,double_value,event_date,patient_id,lft,rgt)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

这是一个非常大的表(1100万行),我确实需要优化以下自联接查询:

SELECT  distinct(patient_sid) as patient_sid
FROM lab_data l1 
LEFT JOIN (SELECT patient_sid, lft, rgt
           FROM lab_data
           WHERE attribute_id = 36 AND double_value >= 1.2 AND event_date >= '1776-01-01' 
         ) AS l2 
ON l1. patient_sid = l2.patient_sid AND l1.lft >= l2.lft AND l1.rgt <= l2.rgt
WHERE l1.attribute_id = 33 AND l1.string_value = '2160-0' 

(我尝试将范围搜索AND l1.lft >= l2.lft AND l1.rgt <= l2.rgt移到外部where子句中,但没有太大区别。)

当我执行EXPLAIN查询计划时,索引idx_bucket正确用于外部查询,但是idx_test未用于内部子查询。相反,它也使用idx_bucket。

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'l1', NULL, 'ref', 'idx_bucket,idx_test', 'idx_bucket', '29', 'const,const', '517298', '100.00', 'Using temporary'
'1', 'SIMPLE', 'lab_data', NULL, 'ref', 'idx_bucket,idx_test', 'idx_bucket', '5', 'const', '13657', '100.00', 'Using where; Distinct'

如果我强制内部子查询使用idx_test,则会得到以下查询计划:

# id, select_type, table, partitions, type, possible_keys, key, key_len, ref, rows, filtered, Extra
'1', 'SIMPLE', 'l1', NULL, 'ref', 'idx_bucket,idx_test', 'idx_bucket', '29', 'const,const', '517298', '100.00', 'Using temporary'
'1', 'SIMPLE', 'lab_data', NULL, 'ref', 'idx_test', 'idx_test', '5', 'const', '21808', '100.00', 'Using where; Distinct'

而从JSON输出,我只看到attribute_idused_key_parts用于该指数?根据MySQL文档(B树索引特性),btree索引的含义是,“ B树索引可用于使用=,>,> =,<,<=或在运营商之间。”

 "table": {
  "table_name": "lab_data",
  "access_type": "ref",
  "possible_keys": [
    "idx_test"
  ],
  "key": "idx_test",
  "used_key_parts": [
    "attribute_id"
  ],
  "key_length": "5",
  "ref": [
    "const"
  ],
  "rows_examined_per_scan": 8898041,
  "rows_produced_per_join": 988473,
  "filtered": "11.11",
  "index_condition": "((`ns_large2_2016`.`lab_data`.`double_value` >= 1.2) and (`ns_large2_2016`.`lab_data`.`event_date` >= '1776-01-01'))",
  "cost_info": {
    "read_cost": "339069.00",
    "eval_cost": "197694.69",
    "prefix_cost": "2118677.20",
    "data_read_per_join": "82M"
  },
  "used_columns": [
    "patient_sid",
    "double_value",
    "event_date",
    "attribute_id",
    "lft",
    "rgt"
  ]

我误会是什么used_key_parts吗?我假设这些是正在使用的索引的列。b树索引的文档使我相信应该包括范围比较。

horcle_buzz

该解决方案最终在自联接中使用邻接列表/父子关系,而不是自联接的嵌套集表示形式:

SELECT  distinct(patient_sid) as patient_sid
FROM lab_data l1 
LEFT JOIN (SELECT parent
           FROM lab_data
           WHERE attribute_id = 36 AND double_value >= 1.2 AND event_date >= '1776-01-01' 
         ) AS l2 
ON l1.id = l2.parent
WHERE l1.attribute_id = 33 AND l1.string_value = '2160-0' 

然后,我使用

KEY idx_test (attribute_id, parent)

这样最终使查询速度提高了80倍(使用嵌套集表示形式,执行和获取结果花费了40多分钟,而使用邻接列表表示形式,则只花了28秒的时间)。现在,我需要进行范围扫描的唯一值可能是double_value和event_date。

本文收集自互联网,转载请注明来源。

如有侵权,请联系[email protected] 删除。

编辑于
0

我来说两句

0条评论
登录后参与评论

相关文章

来自分类Dev

如何使用表联接优化此查询?

来自分类Dev

优化MySQL自联接查询

来自分类Dev

如何优化此MYSQL查询-联接多个表

来自分类Dev

如何使用条件联接优化查询?

来自分类Dev

如何使用Rails优化此查询

来自分类Dev

如何使用whereHas优化此查询?

来自分类Dev

如何使用Rails优化此查询

来自分类Dev

如何优化此查询?

来自分类Dev

如何优化此查询?

来自分类Dev

在左联接中使用子查询时如何优化查询

来自分类Dev

使用联接优化 SQL 查询

来自分类Dev

如何使用10个以上的联接优化查询?

来自分类Dev

如何使用多个左联接优化SQL查询

来自分类Dev

使用内部联接时如何优化Doctrine查询的数量?

来自分类Dev

如何使用两个内部联接优化查询

来自分类Dev

如何优化此LinQ查询?

来自分类Dev

如何优化此ActiveRecord查询?

来自分类Dev

如何优化此专用查询?

来自分类Dev

如何使用两个联接编写此查询

来自分类Dev

如何使用联接多个表编写此mysqli查询?

来自分类Dev

使用LAG / LEAD分析功能优化自联接Oracle SQL查询?

来自分类Dev

在 where 子句中使用自联接和 group bys 优化查询

来自分类Dev

使用自联接的SQL查询

来自分类Dev

使用左联接优化MySql查询

来自分类Dev

是否可以使用SubQuery而不是自联接重写此CosmosDb SQL查询?

来自分类Dev

如何在MySQL中使用子查询为报表优化多个联接的查询

来自分类Dev

如何加快此mysql联接查询

来自分类Dev

如何优化此SharePoint查询条款?

来自分类Dev

如何优化此MySQL查询以执行Fastar?