mysql - mysql数据库复合键问题
问题描述
我们有一个数据库,其中有一个数据表,定义如下:
CREATE TABLE data
(
msts BIGINT,
variable_id INT,
p_id INT,
value DOUBLE,
PRIMARY KEY(msts,variable_id,p_id)
);
ALTER TABLE data
ADD FOREIGN KEY (p_id)
REFERENCES p(id);
ALTER TABLE data
ADD FOREIGN KEY (variable_id)
REFERENCES variables(id);
该表可以包含数十亿条记录。
进行简单查询时:
SELECT COUNT(msts) from data FORCE INDEX(PRIMARY) where (
msts<1535886000000000000 AND msts>1535796060000000000 AND
variable_id=107 AND p_id=661 );
屈服:
+-------------+
| COUNT(msts) |
+-------------+
| 89873 |
+-------------+
1 row in set (42.51 sec)
计数 89873 需要 42.51 秒。
为什么要花这么长时间才能看到主键应该充当复合索引?
这是解释:
EXPLAIN SELECT COUNT(msts) from data FORCE INDEX(PRIMARY) where
( msts<1535886000000000000 AND msts>1535796060000000000 AND
variable_id=107 AND plant_id=661 );
这使:
rows = 190996998
filtered=0
ref=NULL
type=range
任何帮助将非常感激!
解决方案
您的查询被重写以更改 where 子句的顺序,就是这样。
SELECT COUNT(msts)
from data
where variable_id=107
and p_id=661
and msts>1535796060000000000
and msts<1535886000000000000;
它包含两个相等匹配,在 variable_id 和 p_id 上。然后它在 msts 上包含一个范围过滤器。因此,您需要按该顺序(variable_id, p_id, msts)
在列上建立索引,以帮助快速满足您的查询。
为什么?您可以将 MySQL 索引视为按顺序排序的。为了满足您的查询,MySQL 随机访问第一个符合条件的项目的索引。然后它按顺序扫描它,直到最后一个项目。这称为索引范围扫描。
您预先存在的索引首先列出了 msts。这意味着您的索引无法按顺序扫描,因为索引中的每个 msts 值都可能具有其他两列的许多值。
专业提示 1: 尽可能使用COUNT(*)
而不是COUNT(column)
. 第二个比较慢,因为它必须忽略任何为 NULL 的列值。第一个只是将它们全部计算在内。
专业提示 2:额外的单列索引没有用处,除非它们有助于加快特定查询。
专业提示 3:强制使用索引几乎总是一个糟糕的选择。
专业提示 4:阅读https://use-the-index-luke.com/
编辑:您问如何进行转换。
如果您的表还没有包含数百万行,只需像这样更改主键定义。
ALTER TABLE data
DROP PRIMARY KEY,
ADD PRIMARY KEY (variable_id, p_id, msts);
如果它确实已经包含数十亿行,您可能应该创建一个具有正确定义的新表,将现有表复制到其中。然后复制你的数据。然后将旧表重命名为data_old
or 什么,并将新表重命名为data
. 这可能是一项涉及批量数据的复杂任务;如果您无法弄清楚,请提出另一个问题。
推荐阅读
- pdf - ImageMagick PNG到给定尺寸的PDF
- dataframe - 通过将观察结果移动到新变量来压缩数据框
- javascript - 使用 Yup 的条件 Formik 表单验证
- javascript - 在移动 android 上使用 SWIPER 滚动主页时页面跳转
- python - 用 Python 编程的 Twitter 回复提及机器人工作一次,然后崩溃并出现错误 400:问题是什么?
- java - 实例/对象内的数组:为什么这在 java 上不起作用?
- java - Receiving Java Error - ArrayList$SubList cannot be cast to class
- javascript - How do i call a useReducer dispatch function from another file, which is not a react component? (without using Redux)
- docker - Change entrypoint of a k8s Pod, but keep the CMD
- bpf - Possible to see tracing when using cat or vi opening a text file