Mysql select - 提高性能

Question

我正在一家仅通过贷款销售产品的网上商店工作。我在任何类别中每页显示 10 个产品，每个产品都有 3 个不同的价格标签 - 3 种不同的贷款类型。测试期间一切进展顺利，查询执行时间也很完美，但今天将更改传输到生产服务器时，站点在大约 2 分钟内“崩溃”了。用于选择贷款类型的查询有时会挂起约 10 秒，而且这种情况经常发生，因此无法跟上并且非常慢。用于存储数据的表大约有 200 万条记录，每次选择如下所示：

SELECT * 
FROM products_loans 
WHERE KOD IN("X17/Q30-10", "X17/12", "X17/5-24") 
AND 369.27 BETWEEN CENA_OD AND CENA_DO;

3 种贷款类型以及需要在 CENA_OD 和 CENA_DO 之间的价格，因此返回 3 行。

但由于我需要每页显示 10 个产品，因此我需要使用 OR 通过修改后的选择来运行它，因为我没有找到任何其他解决方案。我在此处询问过这个问题，但没有得到答案。正如参考文章中提到的，这必须单独完成，因为没有可以在连接中使用的列（当然价格和代码除外，但结果非常非常糟糕）。这是 show create table，kod 和 CENA_OD/CENA_DO 通过 INDEX 建立了索引。

CREATE TABLE `products_loans` (
  `KOEF_ID` bigint(20) NOT NULL,
  `KOD` varchar(30) NOT NULL,
  `AKONTACIA` int(11) NOT NULL,
  `POCET_SPLATOK` int(11) NOT NULL,
  `koeficient` decimal(10,2) NOT NULL default '0.00',
  `CENA_OD` decimal(10,2) default NULL,
  `CENA_DO` decimal(10,2) default NULL,
  `PREDAJNA_CENA` decimal(10,2) default NULL,
  `AKONTACIA_SUMA` decimal(10,2) default NULL,
  `TYP_VYHODY` varchar(4) default NULL,
  `stage` smallint(6) NOT NULL default '1',
 PRIMARY KEY  (`KOEF_ID`),
 KEY `CENA_OD` (`CENA_OD`),
 KEY `CENA_DO` (`CENA_DO`),
 KEY `KOD` (`KOD`),
 KEY `stage` (`stage`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

而且选择所有贷款类型然后通过 php 过滤它们效果也不好，因为每种类型都有超过 50k 条记录，而且选择也花费太多时间......

任何有关提高速度的想法都值得赞赏。

编辑：

这是我尝试过组合索引的解释

+----+-------------+----------------+-------+---------------------+------+---------+------+--------+-------------+
| id | select_type | table          | type  | possible_keys       | key  | key_len | ref  | rows   | Extra       |
+----+-------------+----------------+-------+---------------------+------+---------+------+--------+-------------+
|  1 | SIMPLE      | products_loans | range | CENA_OD,CENA_DO,KOD | KOD  | 92      | NULL | 190158 | Using where |
+----+-------------+----------------+-------+---------------------+------+---------+------+--------+-------------+

，它将测试服务器上的性能从 0.44 秒提高到 0.06 秒，但我无法从家里访问生产服务器，所以我明天必须尝试一下。

Answer 1

您的问题是您正在搜索包含一个点的间隔（而不是对间隔中的所有点进行更正常的查询）。这些查询不适用于标准 B 树索引，因此您需要使用 R 树索引。不幸的是，MySQL 不允许您在列上选择 R-Tree 索引，但您可以通过将列类型更改为 GEOMETRY 并使用几何函数检查间隔是否包含该点来获得所需的索引。

请参阅 Quassnoi 的文章 邻接列表与嵌套集：MySQL 他在其中更详细地解释了这一点。用例不同，但涉及的技术是相同的。以下是文章相关部分的摘录：

还有一类任务需要搜索包含已知值的所有范围：

• 在 IP 范围禁止列表中搜索 IP 地址
• 搜索日期范围内的给定日期

还有其他几个。这些任务可以通过使用 MySQL 的 R-Tree 功能来改进。

Answer 2

尝试重构您的查询，例如：

SELECT * FROM products_loans 
WHERE KOD IN("X17/Q30-10", "X17/12", "X17/5-24") 
AND CENA_OD >= 369.27
AND CENA_DO <= 369.27;

（mysql 在选择索引时不是很聪明）并检查性能。

下一个尝试是添加组合键 - (KOD,CENA_OD,CENA_DO)

下一个主要尝试是重构您的基础，将产品与价格分开。这应该确实有帮助。

PS：你也可以迁移到postgresql，在选择正确的索引时它比mysql更聪明。

Answer 3

MySQL 只能使用 1 个密钥。如果您总是按 3 列获取条目，则根据列中的实际数据（范围），以下其中一项很可能会显着提高性能：

ALTER TABLE products_loans ADD INDEX(KOD, CENA_OD, CENA_DO);
ALTER TABLE products_loans ADD INDEX(CENA_OD, CENA_DO, KOD);

请注意，列的顺序很重要！如果这不能提高性能，请向我们提供查询的 EXPLAIN 输出。