where子句与order子句之间的MySQL索引

Question

我的桌子结构如下：

CREATE TABLE test (
    id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,

    field_1 VARCHAR(60) NOT NULL,
    field_2 INT(10) UNSIGNED NULL,
    field_3 INT(10) UNSIGNED NULL,
    field_4 INT(10) UNSIGNED NULL,
    field_5 CHAR(2) NULL,
    field_6 INT(10) UNSIGNED NOT NULL,

    rank TINYINT(2) NOT NULL DEFAULT '0',   
    status TINYINT(3) NOT NULL DEFAULT '0',

    PRIMARY KEY (id),
    INDEX (status)

) DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci ENGINE = MyISAM;

在上面的表中，字段rank和status的整数值分别在0-9和0-4之间。

目前，表中填充了大约950 K的数据，我正在尽可能地优化我的查询。

基本上，我需要使用where子句在字段rank上选择带有降序顺序的字段。

例如，下面是一些sql查询：

SELECT field_1, field_2, field_3 FROM test WHERE field_1 = 'data1' && status IN ('0', '1', '2') ORDER BY rank DESC LIMIT 0, 20;
SELECT field_1, field_2, field_3 FROM test WHERE field_2 = '5' && status IN ('1', '2') ORDER BY rank DESC LIMIT 0, 20;
SELECT field_1, field_2, field_3 FROM test WHERE field_5 = 'US' && status IN ('0', '2') ORDER BY rank DESC LIMIT 0, 20;

在上面的查询中，ORDER BY rank DESC非常重要。因此，我很困惑是应该在单列还是多列上添加索引。

有人能给我建议最好的解决办法吗。

Answer 1

您的关键问题是，超过950 k行的status列有多达4个不同的值。在BTREE指数上，这将是一个真正的痛苦处理。

对于上述3个查询使用的一些更有效的索引可能如下

INDEX forQuery1 ( field_1 , status , rank ) USING BTREE,
INDEX forQuery2 ( field_2 , status , rank ) USING BTREE,
INDEX forQuery3 ( field_5 , status , rank ) USING BTREE,

您会发现，第二个查询尤其应该受益，但是您仍然会遇到这样的问题:数据集大小的数据差异非常小，而且MySQL很可能会退回到表扫描，尽管您的解释可能会显示出减少这种影响的限制。但是，上述索引应该适合确定要返回的行。

有关MySQL如何使用索引的更多信息，请查看13.1.13.创建索引语法，特别是关于B-树索引特性的章节和以下摘录

如果表有多列索引，则优化器可以使用索引的任何最左前缀来查找行。例如，如果您有一个三列索引on (col1，col2，col3)，那么您已经在(col1)、(col1、col2)和(col1、col2、col3)上建立了索引搜索功能。 如果列没有构成索引的最左边前缀，则MySQL无法使用索引。假设这里显示了SELECT语句：

和

有时MySQL不使用索引，即使索引是可用的。出现这种情况的情况之一是，优化器估计使用索引将需要MySQL访问表中很大百分比的行。(在这种情况下，表扫描可能要快得多，因为它需要更少的搜索。)但是，如果这样的查询只使用限度检索部分行，那么MySQL无论如何都会使用索引，因为它可以更快地在结果中找到要返回的几行。

另外，您不需要引用数字数据类型，因此field_2 = 5 && status IN ( 1 , 2 )是有效的(事实上，由于引用整数数据类型而不是将它们指定为数字，我在过去遇到了一些奇怪的问题)。