mysql选择sql非常慢，哪一个索引丢失
mysql选择sql非常慢，哪一个索引丢失
提问于 2017-06-09 10:04:12
呵呵，我有疑问，uid列不同的积分不能使用索引，是什么原因？
请有人告诉我，谢谢！
表item_sort_20170525有222466057行，显示创建表如下：
CREATE TABLE `item_sort_20170525` (
`id` int(10) NOT NULL AUTO_INCREMENT
`iid` bigint(20) NOT NULL DEFAULT '0'
`uid` bigint(20) NOT NULL DEFAULT '0'
`kw_id` int(10) NOT NULL DEFAULT '0'
`platform` tinyint(2) NOT NULL DEFAULT '0'
`is_p4p` tinyint(1) NOT NULL DEFAULT '0'
`page` tinyint(2) NOT NULL DEFAULT '1'
`pos` smallint(4) NOT NULL DEFAULT '0'
`real_pos` char(6) NOT NULL DEFAULT ''
`created` int(10) NOT NULL DEFAULT '0'
PRIMARY KEY (`id`),
KEY `idx_keyword` (`kw_id`) USING BTREE,
KEY `idx_iid` (`iid`,`platform`) USING BTREE,
KEY `idx_uid` (`uid`,`platform`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8如果uid = 896588234
SELECT `kw_id`, COUNT(kw_id) AS `count` FROM `item_sort_20170525`
WHERE `uid` = 896588234 AND `platform` IN (12, 11) GROUP BY `kw_id` ORDER BY `kw_id` DESC LIMIT 21;表演解释：
select_type : SIMPLE
table : item_sort_20170525
type : range
possible_keys : idx_keyword,idx_uid
key : idx_uid
key_len : 9
ref :
rows : 585
Extra : Using index condition; Using temporary; Using filesort如果uid = 2259613579
SELECT `kw_id`, COUNT(kw_id) AS `count` FROM `item_sort_20170525` force index(`idx_uid`)
WHERE `uid` = 2259613579 AND `platform` IN (12, 11) GROUP BY `kw_id` ORDER BY `kw_id` DESC LIMIT 21;表演解释：
select_type : SIMPLE
table : item_sort_20170525
type : ALL
possible_keys : idx_keyword,idx_uid
key :
key_len :
ref :
rows : 225015710
Extra : Using where; Using temporary; Using filesort丢失索引(Idx_uid)，其中uid一个大的int，像2259613579，然后使用强制索引(Idx_uid)只是相同的失败！这个mysql optimer_trace：
{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `tem_sort_20170525`.`kw_id` AS `kw_id`,count(`tem_sort_20170525`.`kw_id`) AS `count` from `tem_sort_20170525` where ((`tem_sort_20170525`.`uid` = 2259613579) and (`tem_sort_20170525`.`platform` in (12,11))) group by `tem_sort_20170525`.`kw_id` order by `tem_sort_20170525`.`kw_id` desc limit 21"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`tem_sort_20170525`.`uid` = 2259613579) and (`tem_sort_20170525`.`platform` in (12,11)))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`tem_sort_20170525`.`platform` in (12,11)) and multiple equal(2259613579, `tem_sort_20170525`.`uid`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`tem_sort_20170525`.`platform` in (12,11)) and multiple equal(2259613579, `tem_sort_20170525`.`uid`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`tem_sort_20170525`.`platform` in (12,11)) and multiple equal(2259613579, `tem_sort_20170525`.`uid`))"
}
] /* steps */
} /* condition_processing */
},
{
"table_dependencies": [
{
"table": "`tem_sort_20170525`",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
{
"table": "`tem_sort_20170525`",
"field": "uid",
"equals": "2259613579",
"null_rejecting": false
}
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`tem_sort_20170525`",
"const_keys_added": {
"keys": [
"idx_keyword"
] /* keys */,
"cause": "group_by"
} /* const_keys_added */,
"range_analysis": {
"table_scan": {
"rows": 225015710,
"cost": 4.61e7
} /* table_scan */,
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_keyword",
"usable": true,
"key_parts": [
"kw_id",
"id"
] /* key_parts */
},
{
"index": "idx_iid",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_uid",
"usable": true,
"key_parts": [
"uid",
"platform",
"id"
] /* key_parts */
}
] /* potential_range_indices */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_applicable_aggregate_function"
} /* group_index_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_uid",
"ranges": [
"2259613579 <= uid <= 2259613579 AND 11 <= platform <= 11",
"2259613579 <= uid <= 2259613579 AND 12 <= platform <= 12"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 29,
"cost": 36.81,
"chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idx_uid",
"rows": 29,
"ranges": [
"2259613579 <= uid <= 2259613579 AND 11 <= platform <= 11",
"2259613579 <= uid <= 2259613579 AND 12 <= platform <= 12"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 29,
"cost_for_plan": 36.81,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`tem_sort_20170525`",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_uid",
"rows": 36,
"cost": 43.2,
"chosen": true
},
{
"access_type": "range",
"rows": 22,
"cost": 42.61,
"chosen": true
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 42.61,
"rows_for_plan": 22,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`tem_sort_20170525`.`uid` = 2259613579) and (`tem_sort_20170525`.`platform` in (12,11)))",
"attached_conditions_computation": [
{
"table": "`tem_sort_20170525`",
"rechecking_index_usage": {
"recheck_reason": "low_limit",
"limit": 21,
"row_estimate": 22,
"range_analysis": {
"table_scan": {
"rows": 225015710,
"cost": 2.7e8
} /* table_scan */,
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_keyword",
"usable": true,
"key_parts": [
"kw_id",
"id"
] /* key_parts */
},
{
"index": "idx_iid",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_uid",
"usable": false,
"cause": "not_applicable"
}
] /* potential_range_indices */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "cannot_do_reverse_ordering"
} /* group_index_range */
} /* range_analysis */
} /* rechecking_index_usage */
}
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`tem_sort_20170525`",
"attached": "((`tem_sort_20170525`.`uid` = 2259613579) and (`tem_sort_20170525`.`platform` in (12,11)))"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`tem_sort_20170525`.`kw_id` desc",
"items": [
{
"item": "`tem_sort_20170525`.`kw_id`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`tem_sort_20170525`.`kw_id` desc"
} /* clause_processing */
},
{
"clause_processing": {
"clause": "GROUP BY",
"original_clause": "`tem_sort_20170525`.`kw_id`",
"items": [
{
"item": "`tem_sort_20170525`.`kw_id`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`tem_sort_20170525`.`kw_id`"
} /* clause_processing */
},
{
"refine_plan": [
{
"table": "`tem_sort_20170525`",
"access_type": "table_scan"
}
] /* refine_plan */
},
{
"reconsidering_access_paths_for_index_ordering": {
"clause": "GROUP BY",
"index_order_summary": {
"table": "`tem_sort_20170525`",
"index_provides_order": true,
"order_direction": "desc",
"index": "idx_keyword",
"plan_changed": true,
"access_type": "index_scan"
} /* index_order_summary */
} /* reconsidering_access_paths_for_index_ordering */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
] /* steps */
} /* join_execution */
}
] /* steps */
}{
"steps": [
{
"join_preparation": {
"select#": 1,
"steps": [
{
"expanded_query": "/* select#1 */ select `item_sort_20170525`.`kw_id` AS `kw_id`,count(`item_sort_20170525`.`kw_id`) AS `count` from `item_sort_20170525` FORCE INDEX (`idx_uid`) where ((`item_sort_20170525`.`uid` = 896588234) and (`item_sort_20170525`.`platform` in (12,11))) group by `item_sort_20170525`.`kw_id` order by `item_sort_20170525`.`kw_id` desc limit 21"
}
] /* steps */
} /* join_preparation */
},
{
"join_optimization": {
"select#": 1,
"steps": [
{
"condition_processing": {
"condition": "WHERE",
"original_condition": "((`item_sort_20170525`.`uid` = 896588234) and (`item_sort_20170525`.`platform` in (12,11)))",
"steps": [
{
"transformation": "equality_propagation",
"resulting_condition": "((`item_sort_20170525`.`platform` in (12,11)) and multiple equal(896588234, `item_sort_20170525`.`uid`))"
},
{
"transformation": "constant_propagation",
"resulting_condition": "((`item_sort_20170525`.`platform` in (12,11)) and multiple equal(896588234, `item_sort_20170525`.`uid`))"
},
{
"transformation": "trivial_condition_removal",
"resulting_condition": "((`item_sort_20170525`.`platform` in (12,11)) and multiple equal(896588234, `item_sort_20170525`.`uid`))"
}
] /* steps */
} /* condition_processing */
},
{
"table_dependencies": [
{
"table": "`item_sort_20170525` FORCE INDEX (`idx_uid`)",
"row_may_be_null": false,
"map_bit": 0,
"depends_on_map_bits": [
] /* depends_on_map_bits */
}
] /* table_dependencies */
},
{
"ref_optimizer_key_uses": [
{
"table": "`item_sort_20170525` FORCE INDEX (`idx_uid`)",
"field": "uid",
"equals": "896588234",
"null_rejecting": false
}
] /* ref_optimizer_key_uses */
},
{
"rows_estimation": [
{
"table": "`item_sort_20170525` FORCE INDEX (`idx_uid`)",
"const_keys_added": {
"keys": [
"idx_keyword"
] /* keys */,
"cause": "group_by"
} /* const_keys_added */,
"range_analysis": {
"table_scan": {
"rows": 225015710,
"cost": 2e308
} /* table_scan */,
"potential_range_indices": [
{
"index": "PRIMARY",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_keyword",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_iid",
"usable": false,
"cause": "not_applicable"
},
{
"index": "idx_uid",
"usable": true,
"key_parts": [
"uid",
"platform",
"id"
] /* key_parts */
}
] /* potential_range_indices */,
"setup_range_conditions": [
] /* setup_range_conditions */,
"group_index_range": {
"chosen": false,
"cause": "not_applicable_aggregate_function"
} /* group_index_range */,
"analyzing_range_alternatives": {
"range_scan_alternatives": [
{
"index": "idx_uid",
"ranges": [
"896588234 <= uid <= 896588234 AND 11 <= platform <= 11",
"896588234 <= uid <= 896588234 AND 12 <= platform <= 12"
] /* ranges */,
"index_dives_for_eq_ranges": true,
"rowid_ordered": false,
"using_mrr": false,
"index_only": false,
"rows": 585,
"cost": 704.01,
"chosen": true
}
] /* range_scan_alternatives */,
"analyzing_roworder_intersect": {
"usable": false,
"cause": "too_few_roworder_scans"
} /* analyzing_roworder_intersect */
} /* analyzing_range_alternatives */,
"chosen_range_access_summary": {
"range_access_plan": {
"type": "range_scan",
"index": "idx_uid",
"rows": 585,
"ranges": [
"896588234 <= uid <= 896588234 AND 11 <= platform <= 11",
"896588234 <= uid <= 896588234 AND 12 <= platform <= 12"
] /* ranges */
} /* range_access_plan */,
"rows_for_plan": 585,
"cost_for_plan": 704.01,
"chosen": true
} /* chosen_range_access_summary */
} /* range_analysis */
}
] /* rows_estimation */
},
{
"considered_execution_plans": [
{
"plan_prefix": [
] /* plan_prefix */,
"table": "`item_sort_20170525` FORCE INDEX (`idx_uid`)",
"best_access_path": {
"considered_access_paths": [
{
"access_type": "ref",
"index": "idx_uid",
"rows": 585,
"cost": 702,
"chosen": true
},
{
"access_type": "range",
"rows": 439,
"cost": 821.01,
"chosen": false
}
] /* considered_access_paths */
} /* best_access_path */,
"cost_for_plan": 702,
"rows_for_plan": 585,
"chosen": true
}
] /* considered_execution_plans */
},
{
"attaching_conditions_to_tables": {
"original_condition": "((`item_sort_20170525`.`uid` = 896588234) and (`item_sort_20170525`.`platform` in (12,11)))",
"attached_conditions_computation": [
{
"access_type_changed": {
"table": "`item_sort_20170525` FORCE INDEX (`idx_uid`)",
"index": "idx_uid",
"old_type": "ref",
"new_type": "range",
"cause": "uses_more_keyparts"
} /* access_type_changed */
}
] /* attached_conditions_computation */,
"attached_conditions_summary": [
{
"table": "`item_sort_20170525` FORCE INDEX (`idx_uid`)",
"attached": "((`item_sort_20170525`.`uid` = 896588234) and (`item_sort_20170525`.`platform` in (12,11)))"
}
] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},
{
"clause_processing": {
"clause": "ORDER BY",
"original_clause": "`item_sort_20170525`.`kw_id` desc",
"items": [
{
"item": "`item_sort_20170525`.`kw_id`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`item_sort_20170525`.`kw_id` desc"
} /* clause_processing */
},
{
"clause_processing": {
"clause": "GROUP BY",
"original_clause": "`item_sort_20170525`.`kw_id`",
"items": [
{
"item": "`item_sort_20170525`.`kw_id`"
}
] /* items */,
"resulting_clause_is_simple": true,
"resulting_clause": "`item_sort_20170525`.`kw_id`"
} /* clause_processing */
},
{
"refine_plan": [
{
"table": "`item_sort_20170525` FORCE INDEX (`idx_uid`)",
"pushed_index_condition": "((`item_sort_20170525`.`uid` = 896588234) and (`item_sort_20170525`.`platform` in (12,11)))",
"table_condition_attached": null,
"access_type": "range"
}
] /* refine_plan */
}
] /* steps */
} /* join_optimization */
},
{
"join_execution": {
"select#": 1,
"steps": [
{
"creating_tmp_table": {
"tmp_table_info": {
"table": "intermediate_tmp_table",
"row_length": 13,
"key_length": 4,
"unique_constraint": false,
"location": "memory (heap)",
"row_limit_estimate": 161319
} /* tmp_table_info */
} /* creating_tmp_table */
},
{
"filesort_information": [
{
"direction": "desc",
"table": "intermediate_tmp_table",
"field": "kw_id"
}
] /* filesort_information */,
"filesort_priority_queue_optimization": {
"limit": 21,
"rows_estimate": 540,
"row_size": 12,
"memory_available": 720896,
"chosen": true
} /* filesort_priority_queue_optimization */,
"filesort_execution": [
] /* filesort_execution */,
"filesort_summary": {
"rows": 22,
"examined_rows": 530,
"number_of_tmp_files": 0,
"sort_buffer_size": 440,
"sort_mode": "<sort_key, rowid>"
} /* filesort_summary */
}
] /* steps */
} /* join_execution */
}
] /* steps */
}回答 2
Stack Overflow用户
发布于 2017-06-09 10:15:55
您的查询：
SELECT `kw_id`, COUNT(kw_id) AS `count`
FROM `item_sort_20170525`
WHERE `uid` = 896588234
AND `platform` IN (12, 11)
GROUP BY `kw_id`
ORDER BY `kw_id` DESC
LIMIT 21;这里有两个过滤条件:集合中的uid相等和platform。然后你就有了一个分组准则，也就是反向排序准则。
您能将platform标准从一个集合更改为一个范围吗？如果是的话，那就去做。platform BETWEEN 11 AND 12。不过，它看起来确实像是查询规划师自己发现的。
然后尝试一个以相等标准开始的复合索引，然后有范围标准，然后有分组标准。在这种情况下：
(uid, platform, kw_id)这将使您的查询能够通过索引范围扫描得到满足。向索引添加kw_id使其成为覆盖索引，这意味着查询所需的所有内容都可以由索引来满足。它还可以允许反向范围扫描来产生DESC排序。
另外，由于您已经将kw_id声明为NOT NULL，所以可以使用COUNT(*)代替COUNT(kw_id)。这可能会有帮助，但可能不会有多大帮助。
Pro提示：总是格式化您的查询，以便您的选择、筛选、分组和排序条件在您查看它们时跳出。表中的行越多，这一点就越重要。
Stack Overflow用户
发布于 2017-06-09 10:36:32
O.Jones的答案很好。还有两件事你可以尝试：
SELECT kw_id, SUM(cnt)
FROM ((SELECT `kw_id`, COUNT(*) AS cnt
FROM `item_sort_20170525`
WHERE `uid` = 896588234 AND `platform` = 11
) UNION ALL
(SELECT `kw_id`, COUNT(*) AS cnt
FROM `item_sort_20170525`
WHERE `uid` = 896588234 AND `platform` = 12
)
) i
GROUP BY `kw_id`
ORDER BY `kw_id` DESC
LIMIT 21;你想要同样的索引，item_sort_20170525(uid, platform, kw_id)。MySQL应该能够删除内部组的文件排序。因此，如果没有太多的kw_id，那么外部group by就不应该很昂贵。
另一个选项是使用关联子查询。这假设您在某个地方有一个kw_id列表。这个查询看起来像
select kw_id,
(select count(*)
from `item_sort_20170525` i
where i.kw_id = k.kw_id and `uid` = 896588234 and
`platform` in (11, 12)
) as cnt
from kw
order by kw.kw_id desc;如果大多数/所有kw_id的项表中至少有一个匹配行，则此版本将有效。对于此查询，您需要item_sort_20170525(kw_id, uid, platform)上的索引。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/44454932
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号