我正试图尽快执行SQL查询。
只有一个有~ 1000万条记录的表,这个表有3个索引以便更快地读取,但不幸的是我想要选择的列没有。
让我解释一下:我想从表bugs(id, token, title, category, device, reported_at, created_at, updated_at)
:
中选择标题我在做什么:SELECT title FROM (SELECT id FROM bugs WHERE reported_at = '2020-08-30' and token = 'token660')
它工作,但它很慢,虽然我使用了两个索引reportted_at和token,我怎么能加快它?
索引如下:
{
"records": [
{
"Table": "bugs",
"Non_unique": 0,
"Key_name": "PRIMARY",
"Seq_in_index": 1,
"Column_name": "id",
"Collation": "A",
"Cardinality": 9791826,
"Sub_part": null,
"Packed": null,
"Null": "",
"Index_type": "BTREE",
"Comment": "",
"Index_comment": ""
},
{
"Table": "bugs",
"Non_unique": 1,
"Key_name": "index_bugs_on_category_and_token_and_reported_at",
"Seq_in_index": 1,
"Column_name": "category",
"Collation": "A",
"Cardinality": 1,
"Sub_part": null,
"Packed": null,
"Null": "YES",
"Index_type": "BTREE",
"Comment": "",
"Index_comment": ""
},
{
"Table": "bugs",
"Non_unique": 1,
"Key_name": "index_bugs_on_category_and_token_and_reported_at",
"Seq_in_index": 2,
"Column_name": "token",
"Collation": "A",
"Cardinality": 29946,
"Sub_part": null,
"Packed": null,
"Null": "YES",
"Index_type": "BTREE",
"Comment": "",
"Index_comment": ""
},
{
"Table": "bugs",
"Non_unique": 1,
"Key_name": "index_bugs_on_category_and_token_and_reported_at",
"Seq_in_index": 3,
"Column_name": "reported_at",
"Collation": "A",
"Cardinality": 6085027,
"Sub_part": null,
"Packed": null,
"Null": "YES",
"Index_type": "BTREE",
"Comment": "",
"Index_comment": ""
}
]
}
In MySQL ,多列索引只能使用最左边的列。其他数据库没有此限制。
这是因为MySQL索引(默认情况下)是b树。多列索引是树的树。要使用索引(category, token, reported_at)
, MySQL必须首先在类别树中找到一个类别,然后在该类别中会有令牌的子树,最后在该类别和令牌中有一个子树。
在您的示例中,您在(category, token, reported_at)
上有一个多列索引。如果只按类别搜索,MySQL可以使用索引。或者根据类别和令牌,MySQL可以使用索引。或者按类别、令牌和reported_at。
您正在通过令牌和reported_at搜索,但由于您不是通过类别搜索,MySQL将不使用索引。它必须扫描类别索引中的每个条目以寻找匹配的令牌。其他数据库使用索引的方式更灵活,可能会尝试这样做,但MySQL不会。
Use The Index, Luke很好地解释了MySQL中的多列索引
一般来说,超过两列的索引的值是有问题的。
所以,就像之前一样,答案是在您正在搜索的字段上创建一个新的索引。或者使用一个更好的数据库。
需要用搜索条件的列创建索引。在本例中:
CREATE INDEX search_index
ON bugs (reported_at, token);
因为你在用等号搜索,这应该很快。查询必须是:
SELECT title FROM bugs WHERE reported_at = '2020-08-30' and token = 'token660'
如果不能更改数据库,可以单独选择行,将它们相交,然后得到标题:
SELECT r.title
FROM (
SELECT * FROM bugs WHERE reported_at = '2020-08-30'
) r
JOIN (
SELECT id FROM bugs WHERE token = 'token660'
) t
ON r.id = t.id
SELECT id
FROM bugs
WHERE reported_at = '2020-08-30'
and token = 'token660'
需要这个复合索引:INDEX(token, reported_at)
我不知道category
或title
的其他乱码是什么。