在where子句和order by子句之间的MySQL索引 [英] MySQL index in between where clause and order by clause

问题描述

我的表结构如下:

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之间.

On above table the fields rank and status will have integer value between 0-9 and 0-4 respectively.

当前该表中充满了大约950K数据，我正在尝试尽可能优化我的查询.

Currently the table is filled with around 950K data and I am trying to optimize my queries as much as possible.

基本上，我需要选择具有某些where子句的字段，该子句在字段rank上按降序排列.

Basically I need to select fields with some where clause with a descending order on field rank.

例如，以下是一些SQL查询:

For example, below are few sql queries:

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非常重要.所以我很困惑应该在单列还是多列上添加索引.

On above query ORDER BY rank DESC is very important. So I am quite confuse whether I should add index on single column or multi-column.

谁能建议我最好的解决方案.

Could anyone suggest me the best solution.

推荐答案

您的关键问题是，超过950k的行，您的status列最多包含4个不同的值.在BTREE索引上，这将是一个真正的难题.

Your key problem is that over 950k rows your status column has up to 4 distinct values. On a BTREE index, this will be a real pain to process.

以下提到的3个查询中使用的一些更有效的索引可能是 以下

Some more effective indexes to use for the 3 queries mentioned are probably the following

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很可能会退回到表扫描中您的EXPLAIN可能会显示LIMIT来减轻这种影响.提到的索引应该适合于确定要返回哪些行.

You'll find that the second query especially should benefit however you're still going to have the issue where the variance of the data is very low for the size of dataset and most likely MySQL will fall back to a table scan though your EXPLAIN will probably show the LIMIT to lessen the effect of that. The indexes mentioned should be suitable for determining which rows to return though.

有关MySQL如何使用索引的更多信息，请浏览 13.1.13.创建索引语法，尤其是关于 B树索引特征的部分以及以下摘录

For further information on how MySQL uses indexes take a look through 13.1.13. CREATE INDEX Syntax in particular the section on B-Tree Index Characteristics and the following extracts

如果表具有多列索引，则该表的最左前缀 优化器可以使用index来查找行.例如，如果您 在(col1，col2，col3)上有一个三列索引，您已经建立了索引 (col1)，(col1，col2)和(col1，col2，col3)的搜索功能.

If the table has a multiple-column index, any leftmost prefix of the index can be used by the optimizer to find rows. For example, if you have a three-column index on (col1, col2, col3), you have indexed search capabilities on (col1), (col1, col2), and (col1, col2, col3).

如果列未形成最左边的前缀，MySQL将无法使用索引 索引.假设您在这里显示了SELECT语句:

MySQL cannot use an index if the columns do not form a leftmost prefix of the index. Suppose that you have the SELECT statements shown here:

和

有时，即使索引可用，MySQL也不使用索引.一 这种情况发生的时间是优化程序估算的时间 使用索引将需要MySQL访问非常大的 表中行的百分比. (在这种情况下，表扫描是 可能会更快，因为它需要更少的搜寻.)但是，如果 这样的查询使用LIMIT只检索某些行，MySQL使用 无论如何还是索引，因为它可以更快地找到要查找的几行 返回结果.

Sometimes MySQL does not use an index, even if one is available. One circumstance under which this occurs is when the optimizer estimates that using the index would require MySQL to access a very large percentage of the rows in the table. (In this case, a table scan is likely to be much faster because it requires fewer seeks.) However, if such a query uses LIMIT to retrieve only some of the rows, MySQL uses an index anyway, because it can much more quickly find the few rows to return in the result.

作为附加说明，您无需引用数字数据类型，因此field_2 = 5 && status IN ( 1 , 2 )是有效的(实际上，过去我曾因引用整数数据类型而不是将它们指定为数字而遇到一些奇怪的问题)

As an additional note, you don't need to quote numeric data types and so field_2 = 5 && status IN ( 1 , 2 ) is valid (and in fact I have had some strange issue in the past due to quoting integer datatypes instead of specifying them as numerics)

这篇关于在where子句和order by子句之间的MySQL索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！