Imagine a MySQL table with one field id containing 1 billion rows from number 1 to a billion.
假设有一个MySQL表，其中一个字段id包含10亿行，从第1行到10亿行。
When I do a query like this
当我做这样的查询时。
SELECT * FROM table WHERE id > 2000 AND id < 5000;
It is obvious that an index on id will improve the performance of that query.
很明显，id上的索引将提高该查询的性能。
However does such an index also help with modulo as in the following query
但是，这样的索引也有助于使用modulo，如下面的查询
SELECT * FROM table WHERE (id % 4) = 0;
Does using an index help when using modulo?
使用modulo时使用索引有帮助吗?
2 个解决方案
#1
4
No.
不。
Functions on columns used in an index (almost) always preclude the use of the index. Even if this weren't true, the optimizer might decide not to use an index anyway. Fetching just one out of four records may not be selective enough for the index to be worthwhile.
在索引中使用的列上的函数(几乎)总是不能使用索引。即使这不是真的，优化器也可能决定不使用索引。仅从四项记录中提取一项可能不够有选择性，从而使索引具有价值。
#2
2
In Oracle DB for example you can define so called function based indices for your purpose where you define that modulo function in the index. But I'm pretty sure function based indices do not exist with MySQL.
例如，在Oracle DB中，您可以为您的目的定义所谓的基于函数的索引，在索引中定义模块函数。但是我很确定基于函数的索引在MySQL中是不存在的。
What you could do as a workaround is adding a additional column where you store the result of your modulo function. You have to modify your insert scripts fill it for future inserts and update the existing data sets. Then you can add an index to that column and use it in your where clause.
作为一个解决方案，您可以添加一个附加列，在其中存储模块函数的结果。您必须修改插入脚本，为以后的插入填充它，并更新现有的数据集。然后可以向该列添加一个索引，并在where子句中使用它。
#1
4
No.
不。
Functions on columns used in an index (almost) always preclude the use of the index. Even if this weren't true, the optimizer might decide not to use an index anyway. Fetching just one out of four records may not be selective enough for the index to be worthwhile.
在索引中使用的列上的函数(几乎)总是不能使用索引。即使这不是真的，优化器也可能决定不使用索引。仅从四项记录中提取一项可能不够有选择性，从而使索引具有价值。
#2
2
In Oracle DB for example you can define so called function based indices for your purpose where you define that modulo function in the index. But I'm pretty sure function based indices do not exist with MySQL.
例如，在Oracle DB中，您可以为您的目的定义所谓的基于函数的索引，在索引中定义模块函数。但是我很确定基于函数的索引在MySQL中是不存在的。
What you could do as a workaround is adding a additional column where you store the result of your modulo function. You have to modify your insert scripts fill it for future inserts and update the existing data sets. Then you can add an index to that column and use it in your where clause.
作为一个解决方案，您可以添加一个附加列，在其中存储模块函数的结果。您必须修改插入脚本，为以后的插入填充它，并更新现有的数据集。然后可以向该列添加一个索引，并在where子句中使用它。