我们有一个数据库，其中的数据表定义如下：
CREATE TABLE data(msts BIGINT,variable_id INT,p_id INT,value DOUBLE,PRIMARY KEY(msts,variable_id,p_id));ALTER TABLE dataADD FOREIGN KEY (p_id)REFERENCES p(id);ALTER TABLE dataADD FOREIGN KEY (variable_id)REFERENCES variables(id);
这个表可以包含数十亿条记录。
执行简单查询时：
SELECT COUNT(msts) from data FORCE INDEX(PRIMARY) where (msts<1535886000000000000 AND msts>1535796060000000000 ANDvariable_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 ANDvariable_id=107 AND plant_id=661 );
它给出：
rows = 190996998filtered=0ref=NULLtype=range
任何帮助都将不胜感激！
1条答案
按热度按时间zmeyuzjn1#
您的查询，重写以更改where子句的顺序，是这样的。
它包含两个相等匹配项，在变量\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/
编辑：您询问如何进行转换。
如果您的表还不包含数百万行，只需像这样更改主键定义。
如果它已经包含数十亿行，那么您可能应该创建一个具有正确定义的新表，将现有表复制到其中。然后复制数据。然后将旧表重命名为
data_old或者别的什么，然后将新表重命名为data. 这可能是一项涉及批量数据的复杂任务；如果你想不出来，再问一个问题。