- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
你好,我有疑问,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
失去 index(idx_uid) where uid eq a big int like 2259613579 ,然后使用 force index(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 */
}
最佳答案
您的查询:
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
条件从一个集合更改为一个范围?如果是这样,那就去做吧。 11 和 12 之间的平台
。不过,看起来查询规划器确实是自己想出来的。
然后尝试以相等标准开始的复合索引,然后是范围标准,然后是分组标准。在这种情况下:
(uid, platform, kw_id)
应该允许您的查询通过索引范围扫描得到满足。将 kw_id
添加到索引使其成为一个覆盖 索引,这意味着索引可以满足查询所需的一切。它还可能允许反向范围扫描以生成 DESC
排序。
此外,因为您已将 kw_id
声明为 NOT NULL
,您可以使用 COUNT(*)
代替 COUNT( kw_id)
。这可能有帮助,但可能作用不大。
专业提示:始终格式化您的查询,以便在您查看它们时您的选择、过滤、分组和排序标准会跳出来。表格中的行越多,这一点就越重要。
关于mysql select sql 很慢,哪个索引丢了,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/44454932/
我有一个 view我拖了一个UITableView在里面,还有 2 UIImageView s(第一个显示背景图像,第二个只是在 View 顶部显示一个非常小的标题和图像)。 它们都设置为 weak特
我尝试用 C# 编写简单的 PostgreSQL 查询。第一个 connection.open() 需要 20 秒。其他连接立即执行。 PGAdmin 工作也很慢。如果我打开“查看所有行”,它也需要大
我制作了一个 html5 视频播放器,我注意到如果当前播放的视频有点大,搜索时间会异常地长。 越接近终点,寻找的时间越长;独立于我之前是否去过那里/与当前时间点的距离有多近,或者我是否缓冲了整个视频。
我正在使用 MaterialDatePicker,但速度很慢。 public class MainActivity extends AppCompatActivity { MaterialDa
我想知道为什么 MyBatis 是 慢 在我的应用程序中。 对于 SELECT COUNT(*) ,所用时间为: 20 秒 - 第一个请求 2-3 秒 - 后续请求 缓存很可能使后续请求更快。 配置
我已经安装了一个默认的开箱即用的 FreeSwitch 实例,但是当我尝试进行内部调用(分机到分机)时,大约需要 12 秒才能建立调用并且我可以听到铃声。 当我查看日志时,我几乎立即看到了连接请求,但
我已经放弃了让它跑得更快的实际尝试。 我最大的问题是,当我插入 html 时,应用程序会变慢到爬行。我有一个进度条,我正在调用 QCoreApplication.processEvents() (顺便
很难说出这里要问什么。这个问题模棱两可、含糊不清、不完整、过于宽泛或夸夸其谈,无法以目前的形式得到合理的回答。如需帮助澄清此问题以便重新打开,visit the help center . 关闭 9
Doxygen 在我们的代码库上运行大约需要 12 个小时。这主要是因为有很多代码要处理(约 1.5M 行)。然而,它很快就会接近我们无法进行夜间文档更新的地步,因为它们需要太长时间。我们已经不得不减
我正在重写我的旧渲染管道。我根据自己的喜好创建了一个非常精简的原型(prototype),令我震惊的是,我原来相当复杂且优化不佳的管道与 super 简单的原型(prototype)具有完全相同的性能
我想为我的网站使用 Gridster,但我需要使用“add_widget”命令添加很多小部件。我做了一个测试,我认为“add_widget”功能存在问题:网格越来越慢并且存在内存泄漏。 您可以在此视频
我有一份包含图表和表格的报告。 我正在使用 html2canvas与 jsPDF将此报告导出为 PDF 文件。 但是这个过程耗时很长,超过11000ms。 我尝试更改格式和质量,但没有任何效果。 请看
我正在查询大于时间戳的类的所有修订,使用: AuditReaderFactory .get(emf.createEntityManager()) .createQuery().forR
我最近想加速一个加密系统。而在这个系统中,它将使用mysql,因此它包括文件。 而且我发现系统运行缓慢并不是因为加解密,而是因为处理一些sql语句。 它将在运行时使用内存数据库,并使用 中的 mys
谁能看出为什么这需要大约 20 秒?我正在运行下面的代码以将 JSON 请求发布到本地服务器 192.168.1.127。 curl -H "Content-type: application/jso
我有两个表:Posts 和Tags,其中存储了用户发布的文章以及他们为文章附加的标签。 PostTags 表用于表示文章 ID 和标签 ID 的关系。结构如下: 帖子: id | title | au
一个我应该能够自己回答但我没有,而且在谷歌中也找不到任何答案的问题: 我有一个表,其中包含具有以下结构的 500 万行: CREATE TABLE IF NOT EXISTS `files_histo
以下查询在具有大约 50 万行的表上执行需要 20 多秒: SELECT images.id, images.user_id, images_locale.filename, extension, s
我正在使用 $.getJSON 来提取对象 list (100 个项目,不是一个大集合),但 XHR 调用需要 8-10 秒。 想了解我是否缺少某些内容或我可以采取哪些措施来加快我的计划? 最佳答案
在这段代码中,我从网站获取一个字符串并将其显示在标签上。在标签上显示字符串真的很慢!大约 10 秒。但是在控制台 println (date) 上打印字符串时是立即的。我该如何解决这个问题?
我是一名优秀的程序员,十分优秀!