mysql - MySQL 索引未在大型数据库中使用
问题描述
我对一个大表(大约 3700 万行)有一个非常简单的查询。这个查询需要 10 多分钟才能运行,并且应该很快,因为索引是正确构建的(我认为)。我不明白为什么这个查询需要这么长时间。我希望有人可以指导我正确的方向:
询问:
select type_id, sub_type_id, max(settlement_date_time) as max_dt
from transaction_history group by type_id, sub_type_id
创建语句:
CREATE TABLE `transaction_history` (
`transaction_history_id` int(11) NOT NULL AUTO_INCREMENT,
`type_id` int(11) NOT NULL,
`sub_type_id` int(11) DEFAULT NULL,
`settlement_date_time` datetime DEFAULT NULL,
PRIMARY KEY (`transaction_history_id`),
KEY `sub_type_id_idx` (`sub_type_id_id`),
KEY `settlement_date` (`settlement_date_time`),
KEY `type_sub_type` (`type_id`,`sub_type_id`)
) ENGINE=InnoDB AUTO_INCREMENT=36832823 DEFAULT CHARSET=latin1;
解释的结果:
id -> 1
select_type -> SIMPLE
table -> transaction_history
type -> index
possible_keys -> NULL
key -> type_sub_type
key_len -> 9
ref -> NULL
rows -> 37025337
filtered -> 100.00
Extra ->
为什么可能的键为 NULL?它说它正在使用索引,但看起来不像。为什么 ref 为 NULL?我怎样才能使这个查询更有效率?索引有问题吗?我是否必须更改任何值 MySQL 配置文件?
谢谢
解决方案
(向已经给出必要信息的两位评论者道歉INDEX
;我会尽量多说一些来证明给出“答案”是合理的。)
使用“复合”(和“覆盖”)索引:
INDEX(type_id, sub_type_id, settlement_date_time)
没有WHERE
,因此无需担心此类列。首先是按 中列出的顺序排列的列GROUP BY
,然后是另一列。优化器可能会非常有效地跳过索引。
为什么NULL
?那么2列索引是无用的。一般来说,如果需要查看超过 20% 的表,最好简单地扫描表,而不是在索引 BTree 和数据 BTree 之间跳来跳去。
推荐阅读
- java - Z3 Java 应用程序开发
- ruby-on-rails - 为什么要设计更改我的发件人姓名?
- java - Cannot apply when mocking spring repository delete with Mockito
- azure - ASP.NET Core 发布到 Azure 失败并出现数据库更新
- react-native - 如何使用 React Native Animate 为不同的 Svg 属性设置动画
- java - YamlConfiguration 写入空文件
- javascript - Convert async/await style to traditional .then style
- php - 在表中插入记录后如何在mysql触发器中调用URL?
- windows - Powershell允许用户根据列表离线集群资源
- asp.net - 如何将所有 ASP 配置文件属性重置为其默认值?