mysql - 您能解释一下使用 Composite MySQL INDEX 进行多个 INNER JOIN 的方面吗？

Question

查询是

SELECT v.value
FROM products
         JOIN products_attributes_options pao
              ON 1 = 1
                  AND products.id = pao.product_id
         JOIN attributes_options ao
              ON 1 = 1
                  AND pao.attribute_option_id = ao.id
                  AND attribute_id = 12
         JOIN attributes_options_values aov
              ON
                      1 = 1
                      AND ao.id = aov.attribute_option_id
         JOIN `values` v
              ON
                      1 = 1
                      AND aov.value_id = v.id

         JOIN values_words vw_1
              ON
                      1 = 1
                      AND v.id = vw_1.value_id
         JOIN values_words vw_2
              ON
                      1 = 1
                      AND v.id = vw_2.value_id

         JOIN words w_1
              ON
                      1 = 1
                      AND vw_1.word_id = w_1.id
                      AND w_1.value LIKE '%a%'
         JOIN words w_2
              ON
                      1 = 1
                      AND vw_2.word_id = w_2.id
                      AND w_2.value LIKE '%a%'

GROUP BY v.id, v.value
ORDER BY v.id, v.value

对于这部分查询

JOIN products_attributes_options pao
     ON 1 = 1
        AND products.id = pao.product_id
JOIN attributes_options ao
     ON 1 = 1
        AND pao.attribute_option_id = ao.id
        AND attribute_id = 12

我有复合索引

ix_aov_p (attribute_option_id, product_id)
ix_p_aov (product_id, attribute_option_id)

通过EXPLAIN我看到的查询 - 其中两个可能是可能的键。但我认为只有 ix_aov_p 就足够了。

您能否解释一下复合索引如何适用于这些情况？我需要两个索引还是只需要一个索引？我需要使用其中哪一个？ix_aov_p 或 ix_p_aov

我研究过类似的问题，但没有足够的解释来理解 - 会发生什么？

Separate Join clause in a Composite Index - 类似的问题 - 接受的答案没有充分解释发生的情况以及复合索引不起作用的原因。

很高兴解释这些 JOIN 在 INDEX 方面发生的情况，以帮助理解在这种情况下使用的复合索引。

<小时/>

数据库转储要使用的表 - DUMP(结构)

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `attributes_options`
--

DROP TABLE IF EXISTS `attributes_options`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `attributes_options` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `attribute_id` int(11) NOT NULL,
  `option_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `attributes_options`
--

LOCK TABLES `attributes_options` WRITE;
/*!40000 ALTER TABLE `attributes_options` DISABLE KEYS */;
/*!40000 ALTER TABLE `attributes_options` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `attributes_options_values`
--

DROP TABLE IF EXISTS `attributes_options_values`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `attributes_options_values` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `attribute_option_id` int(11) NOT NULL,
  `value_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `attributes_options_values`
--

LOCK TABLES `attributes_options_values` WRITE;
/*!40000 ALTER TABLE `attributes_options_values` DISABLE KEYS */;
/*!40000 ALTER TABLE `attributes_options_values` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `products`
--

DROP TABLE IF EXISTS `products`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `products`
--

LOCK TABLES `products` WRITE;
/*!40000 ALTER TABLE `products` DISABLE KEYS */;
/*!40000 ALTER TABLE `products` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `products_attributes_options`
--

DROP TABLE IF EXISTS `products_attributes_options`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `products_attributes_options` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `attribute_option_id` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ix_p_aov` (`product_id`,`attribute_option_id`),
  UNIQUE KEY `ix_aov_p` (`attribute_option_id`,`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `products_attributes_options`
--

LOCK TABLES `products_attributes_options` WRITE;
/*!40000 ALTER TABLE `products_attributes_options` DISABLE KEYS */;
/*!40000 ALTER TABLE `products_attributes_options` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `values`
--

DROP TABLE IF EXISTS `values`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `values` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value` varchar(190) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `values`
--

LOCK TABLES `values` WRITE;
/*!40000 ALTER TABLE `values` DISABLE KEYS */;
/*!40000 ALTER TABLE `values` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `values_words`
--

DROP TABLE IF EXISTS `values_words`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `values_words` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `value_id` int(11) NOT NULL,
  `word_id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `values_words`
--

LOCK TABLES `values_words` WRITE;
/*!40000 ALTER TABLE `values_words` DISABLE KEYS */;
/*!40000 ALTER TABLE `values_words` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `words`
--

DROP TABLE IF EXISTS `words`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `words` (
  `id` int(11) NOT NULL,
  `value` varchar(190) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `words`
--

LOCK TABLES `words` WRITE;
/*!40000 ALTER TABLE `words` DISABLE KEYS */;
/*!40000 ALTER TABLE `words` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

Answer 1

这太标准化了。

如果 Products 表包含大量信息(名称、尺寸等)，则最好使用 product_id 标准化 product。我看到是名称。因此，去掉 product_id 和 Products，只保留一列 product_name。

完成此操作后，确定“many:many”映射表。它们不需要 id，只需要两列，每列要么是一个 id，要么是一个值。有关此类表的最佳复合索引的建议，请参阅此内容:http://mysql.rjweb.org/doc.php/index_cookbook_mysql#many_to_many_mapping_table

But I thought only ix_aov_p is enough.

这为“给定 aov，找到 p 值”提供了一种有效的方法。在其他情况下，您需要相反的顺序。复合索引是有序的。对于这样的映射表，“给定”必须放在第一位。

在其他一些情况下，顺序很重要:

WHERE b > 4 AND a = 8

需要 INDEX(a, b) 按该顺序。它可以快速找到a=8和b=4的所有条目，它们向前扫描直到a>8。考虑搜索lastname='James' 和firstname LIKE 'R%'。

同时，INDEX(a,b) 的任一顺序都适合

WHERE b = 4 AND a = 8

更多讨论:http://mysql.rjweb.org/doc.php/index_cookbook_mysql