如何使用双重连接查询使用正确的索引？

Question

我有一个带有2个内联接语句的查询，并且仅获取几列，但是即使我在所有必需的列上都有索引，也很慢。

我的查询

SELECT 
  dysfonctionnement, 
  montant, 
  listRembArticles, 
  case when dys.reimputation is not null then dys.reimputation else dys.responsable end as responsable_final
FROM 
  db.commandes AS com
  INNER JOIN db.dysfonctionnements AS dys ON com.id_commande = dys.id_commande
  INNER JOIN db.pe AS pe ON com.code_pe = pe.pe_id
WHERE 
  com.prestataireLAD REGEXP '.*' 
  AND pe_nom REGEXP 'bordeaux|chambéry-annecy|grenoble|lyon|marseille|metz|montpellier|nancy|nice|nimes|rouen|strasbourg|toulon|toulouse|vitry|vitry bis 1|vitry bis 2|vlg' 
  AND com.date_livraison BETWEEN '2022-06-11 00:00:00' 
  AND '2022-07-08 00:00:00';

大约需要20秒钟才能计算和获取4123行。

问题

以查找出了什么问题以及为什么这么慢，我使用了 Divell 语句，这是输出：

| id | select_type | table | partitions | type   | possible_keys              | key         | key_len | ref                    | rows   | filtered | Extra       |
|----|-------------|-------|------------|--------|----------------------------|-------------|---------|------------------------|--------|----------|-------------|
|  1 | SIMPLE      | dys   |            | ALL    | id_commande,id_commande_2  |             |         |                        | 878588 |   100.00 | Using where |
|  1 | SIMPLE      | com   |            | eq_ref | id_commande,date_livraison | id_commande | 110     | db.dys.id_commande     |      1 |     7.14 | Using where |
|  1 | SIMPLE      | pe    |            | ref    | pe_id                      | pe_id       | 5       | db.com.code_pe         |      1 |   100.00 | Using where |

我可以看到 dysfinctionNements JOIN> JOIN IS索具的，即使可以...

表定义

命令（仅包括相关列）

CREATE TABLE `commandes` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `id_commande` varchar(36) NOT NULL DEFAULT '',
  `date_commande` datetime NOT NULL,
  `date_livraison` datetime NOT NULL,
  `code_pe` int(11) NOT NULL,
  `traitement_dysfonctionnement` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_commande` (`id_commande`),
  KEY `date_livraison` (`date_livraison`),
  KEY `traitement_dysfonctionnement` (`traitement_dysfonctionnement`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

的功能障碍NNNEMENTS（仅相关列）

CREATE TABLE `dysfonctionnements` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `id_commande` varchar(36) DEFAULT NULL,
  `dysfonctionnement` varchar(150) DEFAULT NULL,
  `responsable` varchar(50) DEFAULT NULL,
  `reimputation` varchar(50) DEFAULT NULL,
  `montant` float DEFAULT NULL,
  `listRembArticles` text,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_commande` (`id_commande`,`dysfonctionnement`),
  KEY `id_commande_2` (`id_commande`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

PE（仅相关列）（仅相关列）

CREATE TABLE `pe` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `pe_id` int(11) DEFAULT NULL,
  `pe_nom` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `pe_nom` (`pe_nom`),
  KEY `pe_id` (`pe_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

调查，

如果我可以... db.pe 来自查询的表和 子句 pe_nom ，查询需要1.7秒才能获取7k行，然后使用解释语句，我可以看到它正在按照我的期望来使用键：

| id | select_type | table | partitions | type  | possible_keys              | key            | key_len | ref                    | rows   | filtered | Extra                                         |
|----|-------------|-------|------------|-------|----------------------------|----------------|---------|------------------------|--------|----------|-----------------------------------------------|
|  1 | SIMPLE      | com   |            | range | id_commande,date_livraison | date_livraison | 5       |                        | 389558 |   100.00 | Using index condition; Using where; Using MRR |
|  1 | SIMPLE      | dys   |            | ref   | id_commande,id_commande_2  | id_commande_2  | 111     | ooshop.com.id_commande |      1 |   100.00 |                                               |

我对任何建议开放，我认为当它在非常相似的查询上执行时，我没有理由不使用钥匙，而且绝对是做到这一点 快点...

Answer 1

当MySQL优化器选择一个远不是最佳的连接表序列时，我也有类似的经验。那时我使用mySQL特定 artigh_join> areath_join 操作员以克服默认值优化行为。在您的情况下，我会尝试的：

SELECT 
  dysfonctionnement, 
  montant, 
  listRembArticles, 
  case when dys.reimputation is not null then dys.reimputation else dys.responsable end as responsable_final
FROM 
  db.commandes AS com
  STRAIGHT_JOIN db.dysfonctionnements AS dys ON com.id_commande = dys.id_commande
  INNER JOIN db.pe AS pe ON com.code_pe = pe.pe_id

另外，在您的Where子句中，Regexp之一可能会更改为在操作员中，我假设它可以使用索引。

Answer 2

删除 com.prestatairalad Regexp'。*'。优化器可能不会意识到这对结果集没有影响。如果要动态构建 子句，请消除其他任何您可以的东西。

id_commande_2 是多余的。在可能有用的查询中，唯一可以照顾它。

这些索引可能会有所帮助：

com:  INDEX(date_livraison, id_commande,  code_pe)
pe:  INDEX(pe_nom, pe_id)