mysql数据库组合关键问题

atmip9wb  于 2021-06-18  发布在  Mysql
关注(0)|答案(1)|浏览(446)

我们有一个数据库,其中的数据表定义如下:

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

任何帮助都将不胜感激!

zmeyuzjn

zmeyuzjn1#

您的查询,重写以更改where子句的顺序,是这样的。

SELECT COUNT(msts) 
  from data 
 where variable_id=107 
   and p_id=661
   and msts>1535796060000000000
   and msts<1535886000000000000;

它包含两个相等匹配项,在变量\u id和p\u id上,然后在msts上包含一个范围筛选器。因此,需要在 (variable_id, p_id, msts) 以帮助快速满足查询。
为什么?您可以认为mysql索引是按顺序排序的。为了满足您的查询,mysql随机访问第一个符合条件的项的索引。然后按顺序扫描,直到最后一项。这叫做索引范围扫描。
预先存在的索引首先列出mst。这意味着不能按顺序扫描索引,因为索引中的每个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 或者别的什么,然后将新表重命名为 data . 这可能是一项涉及批量数据的复杂任务;如果你想不出来,再问一个问题。

相关问题