mysql - MySQL:使用 IN(...) 的索引强制使用不同的 access_type
问题描述
我正在尝试调整一个非常简单的查询:
select * from log where user_id in (...) order by id desc limit 25
我只想显示一组不同用户 ID(大约 40 个 ID)的最后 25 个事件。此查询大约需要 50 秒才能运行(表中的记录超过 8000 万条)。
通过执行,EXPLAIN format=json
我可以看到access_type
is range
。经过一番探索,a了解到如果我将ID数改为9,查询规划器将使用另一种访问方式:index
.
所以我假设对于大量的 ID,MySQL 将在组的较小和较大的 id 之间进行范围扫描,如果 ID 是“接近”的,这可能是有意义的,但情况并非总是如此。也许不知何故,这些额外的数据量在进行排序时会成为一个问题(如下面的解释计划所示)。
40个ID解释
{
"query_block": {
"select_id": 1,
"ordering_operation": {
"using_filesort": true,
"table": {
"table_name": "log",
"access_type": "range",
"possible_keys": [
"app_log_user_id"
],
"key": "log_user_id",
"used_key_parts": [
"user_id"
],
"key_length": "4",
"rows": 6150,
"filtered": 100,
"index_condition": "(`app`.`log`.`user_id` in (<43 different ids from 12000 to 330000>))"
}
}
}
}
9个ID解释
{
"query_block": {
"select_id": 1,
"ordering_operation": {
"using_filesort": false,
"table": {
"table_name": "log",
"access_type": "index",
"possible_keys": [
"app_log_user_id"
],
"key": "PRIMARY",
"used_key_parts": [
"id"
],
"key_length": "4",
"rows": 6901,
"filtered": 4552.8,
"attached_condition": "(`app`.`log`.`user_id` in (< 9 ids from 12000 to 18000))"
}
}
}
}
我做了一个实验:我将该查询分成 5 个只有 9 个或更少 ID 的子查询,并对UNION
所有子查询应用一个,最后以 ORDER 和 LIMIT 子句结束。这个查询的查询计划变得有点混乱,即使有奇怪的值表示其中一个子查询的搜索行数是 86737713(我认为这是一个非常错误的估计,其他的都在 10246 左右)。你猜怎么了?查询“仅”用了 6 秒,比 50 秒好。
我不知道使用哪些策略来优化这种查询,但据我所知,如果我可以告诉优化器使用acess_type
ofindex
代替range
,它的性能会更好。那可能吗?
额外细节
user_id
有一个外键和一个索引。- 我们使用 MySQL 5.6 (InnoDB)
- 表有大约 80kk 行。
显示创建表
CREATE TABLE `app_log` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`timestamp` datetime NOT NULL,
`user_id` int(11) NOT NULL,
`content_type_id` int(11) NOT NULL,
`object_id` int(10) unsigned NOT NULL,
`status` int(11) DEFAULT NULL,
`type` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `app_log_content_type_id` (`content_type_id`),
KEY `app_log_144dd2a9` (`timestamp`),
KEY `app_log_user_id` (`user_id`, `id`)
)
ENGINE = InnoDB
AUTO_INCREMENT = 108628300
DEFAULT CHARSET = latin1
解决方案
可能的解释:
您使用的是什么版本的 MySQL/MariaDB?我猜你正在使用 MySQL 5.6?(您的使用FORMAT=JSON
确认“至少 5.6.5”。)
- 5.6.5 引入
eq_range_index_dive_limit
,默认为 10。 - 5.7.4
eq_range_index_dive_limit
默认提高到 200 - 影响IN()
可能的解决方法:
这篇笔记可能解释了列表中的 9 对 43 项IN
。建议你玩eq_range_index_dive_limit
。
琐事
KK = 千米
,会计师 = 'mille' = 千
毫米,会计师 = 百万,即 KK
Lakh,印第安人 =
10 万千万,印第安人 = 1000 万(1000 万)
十亿,英国人过去的意思是百万-百万; 幸运的是,这种困惑似乎已经消失了。
出于所有实际目的,本论坛可以忽略 1000 和 1024(以及 KB 与 KiB)等之间的区别。
推荐阅读
- python - 如何使用 PySide2 实现响应式画廊视图
- matlab - Warning: Unable to load Toolbox Path Cache in a for loop
- c - 有人可以解释模数是如何工作的吗?
- sql-server - 在 SQL Server 中计算每周给定开始日期的行数
- angular - 如何在 Angular 和 Firebase 中进行三向数据绑定?
- android - Cordova ble.write i can't send data (Android)
- java - 尝试在 java 中使用递归创建指数计算器时出现堆栈溢出错误
- angularjs - 错误:[$injector:unpr] 未知提供程序 - 将 AngularJS1 迁移到 Angular6
- android - How to custom claims token to firebase firestore rules via Android App?
- debugging - Having an issue importing a tlb file into my MFC Application