我有下表:
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_id
下used_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树索引的文档使我相信应该包括范围比较。
该解决方案最终在自联接中使用邻接列表/父子关系,而不是自联接的嵌套集表示形式:
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] 删除。
我来说两句