MySQL通过动态公式提高排序速度

Question

我有一个包含多个列的 MySQL 表，以及一个基于不同列且针对不同查询而变化的效率公式。该表包含超过 1000 万条条目，并且是静态的，因此不会添加新条目。

CREATE TABLE `table1` (
    `col1` INT(2) UNSIGNED NOT NULL, # this is an ID from another table used as a filter
    `col2` INT(5) NOT NULL,          # about 20 fixed integer
    `col3` INT(1) NULL DEFAULT NULL,
    `col4` DECIMAL(4,2) NOT NULL,    # fixed decimals -2:0.5:2
    `col5` DECIMAL(4,2) NOT NULL,    # fixed decimal 5:0.5:10
    `col6` INT(2) NOT NULL,
    `col7` INT(2) NOT NULL,          # fixed integer 0:5:15
    `col8` DECIMAL(4,2) NOT NULL,    # unknown decimals
    `col9` DECIMAL(4,2) NOT NULL,    # unknown decimals
    `col10` DECIMAL(4,2) NOT NULL,   # unknown decimals
    `col11` INT(3) NOT NULL,         # unknown integer
    `col12` DECIMAL(4,2) NOT NULL,   # unknown decimals
    `col13` DECIMAL(4,2) NOT NULL,   # unknown decimals
    `col14` DECIMAL(4,2) NOT NULL,   # unknown decimals
    `col15` DECIMAL(4,2) NOT NULL,   # unknown decimals
    INDEX `Index1` (`col1`, `col5`, `col4`, `col2`, `col7`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
;

这是两个常见的自动生成的查询:

SELECT col6,col5,col2,col3,col13,col14,col7,col1,col11,
       col13*col14*col2/col6 AS efficiency
    FROM `table1`
    WHERE `col1` IN (19,1,2,39,40,34,35)
      AND `col5` = '6'
      AND col2 >= '1000' AND col2 <= '5600'
      AND `col4` = '0'
      AND col7 >= 0 AND col7 <= 15
      AND col13 >= 3.00 AND col13 <= 4.50
      AND col14 >= 0.60
    ORDER BY efficiency ASC, col13 ASC
    LIMIT 0, 1;

SELECT col6,col5,col2,col3,col8,col9,col10,col11,col12,col7,col1,col8*col10*col2*col9/col6 AS efficiency
    FROM `table1`
    WHERE `col1` IN (8,11,9,12,16,17,19,24,42,20,43,21,44,22,45,23,25,1,2,3,4,5,28,31,27,39,40,41,34,35)
      AND `col5` = '6' AND col2 >= '1000' AND col2 <= '5600'
      AND `col4` = '0'
      AND col7 >= 0 AND col7 <= 15
      AND col8 >= 0.50
      AND col9 >= 0.35
      AND col10 >= 0.40
      AND col11 <= 15
      AND col12 >= 0.30
    ORDER BY efficiency ASC, col6 DESC
    LIMIT 0, 1

第二个查询包含 col1 的所有值以强制使用索引

不带 ORDER BY 子句的查询比带 ORDER BY 子句的查询快得多。

我有很多这样的表，因此数据库总体需要大约 65 GB 的存储空间。另一个索引会增加所需的空间，对吗？

处理没有 order 和 limit 子句的查询的时间是 0,390 秒。 (+ 1,922 秒网络才能获得数百个条目)。两个子句都需要 1,781 秒。

由于效率公式有时会有所不同并导致 float ，因此附加索引似乎是错误的方法。

另一个问题是，我必须对相同的结果进行第二次排序。目前，我只是再次调用查询，这需要(如预期)双倍的时间。有没有办法对给定的结果再次排序？

查询必须处理 where 子句之后的数百个条目。我认为这应该比 2 秒快得多。

这种情况下的瓶颈是什么？

Answer 1

Because the efficiency formula is sometimes different and results in a floating point number, an additional index seems to be the wrong way.

一种选择是绝对使用索引。为什么效率公式不同？如果您只有几个这样的公式，我建议将它们存储在同一个或不同的表中，并在每列上放置一个索引。是的，您可以在 float 上建立索引。

您可以使用触发器使公式保持最新(在其他数据库中，您可以简单地使用计算列，但 MySQL 在 v8 之前不支持这些)。

The query has to process about 800 entries after the where clause.

这与 30 分钟的查询时间不一致，除非您的行非常非常宽。扫描一个有数百万行的表应该需要时间，但是几十秒，而不是几十分钟。如果可以将条件调整为严格相等条件(没有or、没有in)，那么使用索引就可以很快找到这800个条目。

正如现在所写，索引不会特别有用，除非 col2 具有高度选择性。