mysql取错索引，排序索引而不是过滤索引

Question

我的 Web 应用程序对 DBMS Mysql 5.7.23 进行了一些查询。

该表有大约 80 万条记录。这是具有实际索引的表的 DDL:

CREATE TABLE `contactlens` 
(
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `createdBy` varchar(255) DEFAULT NULL,
  `createdDate` datetime(6) NOT NULL,
  `lastModifiedBy` varchar(255) DEFAULT NULL,
  `lastModifiedDate` datetime(6) DEFAULT NULL,
  `sid` varchar(36) NOT NULL,
  `version` bigint(20) NOT NULL,
  `brand` varchar(255) DEFAULT NULL,
  `category` varchar(255) DEFAULT NULL,
  `colorCode` varchar(255) DEFAULT NULL,
  `colorDescription` varchar(255) DEFAULT NULL,
  `description` longtext,
  `imageUrl` varchar(255) DEFAULT NULL,
  `lastPurchase` datetime(6) DEFAULT NULL,
  `lastPurchasePrice` decimal(19,2) DEFAULT NULL,
  `lastSell` datetime(6) DEFAULT NULL,
  `lastSellPrice` decimal(19,2) DEFAULT NULL,
  `line` varchar(255) DEFAULT NULL,
  `manufacturer` varchar(255) DEFAULT NULL,
  `manufacturerCode` varchar(255) DEFAULT NULL,
  `name` varchar(255) NOT NULL,
  `preset` bit(1) NOT NULL DEFAULT b'0',
  `purchasePrice` decimal(19,2) DEFAULT NULL,
  `salesPrice` decimal(19,2) DEFAULT NULL,
  `sku` varchar(255) NOT NULL,
  `stock` bit(1) NOT NULL DEFAULT b'1',
  `thumbUrl` varchar(255) DEFAULT NULL,
  `trial` bit(1) NOT NULL DEFAULT b'0',
  `upc` varchar(255) DEFAULT NULL,
  `additionMax` decimal(10,2) DEFAULT NULL,
  `additionMin` decimal(10,2) DEFAULT NULL,
  `axisMax` int(11) DEFAULT NULL,
  `axisMin` int(11) DEFAULT NULL,
  `baseCurveMax` decimal(10,2) DEFAULT NULL,
  `baseCurveMin` decimal(10,2) DEFAULT NULL,
  `cylinderMax` decimal(10,2) NOT NULL,
  `cylinderMin` decimal(10,2) NOT NULL,
  `design` varchar(30) NOT NULL,
  `diameterMax` decimal(10,1) DEFAULT NULL,
  `diameterMin` decimal(10,1) DEFAULT NULL,
  `dominant` bit(1) DEFAULT NULL,
  `duration` int(11) NOT NULL,
  `family` varchar(30) DEFAULT NULL,
  `material` varchar(255) DEFAULT NULL,
  `pack` int(11) NOT NULL,
  `source` varchar(30) NOT NULL,
  `sphereMax` decimal(10,2) NOT NULL,
  `sphereMin` decimal(10,2) NOT NULL,
  `type` varchar(30) NOT NULL,
  `taxRate_id` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_sku` (`sku`),
  UNIQUE KEY `UK_elol05sqtuwi88exc8cdmqul1` (`sid`),
  UNIQUE KEY `idx_upc` (`upc`),
  KEY `idx_design` (`design`),
  KEY `FKq7sw02khmcn1nqil9pcxkgmfa` (`taxRate_id`),
  KEY `idx_manufacturer_line_duration_sph_cyl_add` (`type`,`design`,`line`,`duration`,`sphereMin`,`sphereMax`,`cylinderMin`,`cylinderMax`,`axisMin`,`axisMax`,`additionMin`,`additionMax`,`manufacturer`),
  KEY `idx_sorting` (`manufacturer`,`line`,`duration`,`sphereMin`,`cylinderMin`,`additionMin`),
  CONSTRAINT `FKq7sw02khmcn1nqil9pcxkgmfa` FOREIGN KEY (`taxRate_id`) REFERENCES `taxrate` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2572246 DEFAULT CHARSET=utf8

Web 应用程序显示其中一些数据，并允许用户对每一列进行过滤(如 Google 电子表格文档中的过滤器)。

使用可以过滤这些列:

manufacturer | line | type | design | duration | pack | baseCurve (mix/max) | Sph (min/max) | Cyl (min/max) | Axis (min/max) | Addition (min/max)

所有查询都有一个默认的排序依据。最常见的查询是:

查询 1

SELECT *
FROM `ContactLens` contactlen0_
WHERE 1=1 
ORDER BY contactlen0_.`manufacturer` ASC, contactlen0_.`line` ASC, contactlen0_.`duration` ASC , contactlen0_.`sphereMin` ASC, contactlen0_.`cylinderMin` ASC, contactlen0_.`additionMin` ASC 
LIMIT 10

如您所见，idx_sorting 按预期使用。查询耗时大约 6 毫秒，这很好。

查询 2

这次我有过滤器和排序方式。

SELECT *
FROM `ContactLens` contactlen0_
WHERE 1=1 
AND contactlen0_.`sphereMin`<=1.25 
AND contactlen0_.`sphereMax`>=1.75 
AND contactlen0_.`additionMin`<=2.25 
AND contactlen0_.`additionMax`>=2.5
AND contactlen0_.`type`='MULTI_FOCAL'
ORDER BY contactlen0_.`manufacturer` ASC, contactlen0_.`line` ASC, contactlen0_.`duration` ASC , contactlen0_.`sphereMin` ASC, contactlen0_.`cylinderMin` ASC, contactlen0_.`additionMin` ASC 
LIMIT 10

对此进行解释，您可以看到使用了 idx_sorting，而我预计它使用了 idx_manufacturer_line_duration_sph_cyl_add 索引。查询需要 4 秒:很多!我不明白:

1. 使用了“错误的索引”

2. 为什么即使使用 idx_manufacturer_line_duration_sph_cyl_add，考虑到行基数为 20，查询仍需要这么长时间

这是优化轨迹:

{
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `contactlen0_`.`id` AS `id`,`contactlen0_`.`createdBy` AS `createdBy`,`contactlen0_`.`createdDate` AS `createdDate`,`contactlen0_`.`lastModifiedBy` AS `lastModifiedBy`,`contactlen0_`.`lastModifiedDate` AS `lastModifiedDate`,`contactlen0_`.`sid` AS `sid`,`contactlen0_`.`version` AS `version`,`contactlen0_`.`brand` AS `brand`,`contactlen0_`.`category` AS `category`,`contactlen0_`.`colorCode` AS `colorCode`,`contactlen0_`.`colorDescription` AS `colorDescription`,`contactlen0_`.`description` AS `description`,`contactlen0_`.`imageUrl` AS `imageUrl`,`contactlen0_`.`lastPurchase` AS `lastPurchase`,`contactlen0_`.`lastPurchasePrice` AS `lastPurchasePrice`,`contactlen0_`.`lastSell` AS `lastSell`,`contactlen0_`.`lastSellPrice` AS `lastSellPrice`,`contactlen0_`.`line` AS `line`,`contactlen0_`.`manufacturer` AS `manufacturer`,`contactlen0_`.`manufacturerCode` AS `manufacturerCode`,`contactlen0_`.`name` AS `name`,`contactlen0_`.`preset` AS `preset`,`contactlen0_`.`purchasePrice` AS `purchasePrice`,`contactlen0_`.`salesPrice` AS `salesPrice`,`contactlen0_`.`sku` AS `sku`,`contactlen0_`.`stock` AS `stock`,`contactlen0_`.`thumbUrl` AS `thumbUrl`,`contactlen0_`.`trial` AS `trial`,`contactlen0_`.`upc` AS `upc`,`contactlen0_`.`additionMax` AS `additionMax`,`contactlen0_`.`additionMin` AS `additionMin`,`contactlen0_`.`axisMax` AS `axisMax`,`contactlen0_`.`axisMin` AS `axisMin`,`contactlen0_`.`baseCurveMax` AS `baseCurveMax`,`contactlen0_`.`baseCurveMin` AS `baseCurveMin`,`contactlen0_`.`cylinderMax` AS `cylinderMax`,`contactlen0_`.`cylinderMin` AS `cylinderMin`,`contactlen0_`.`design` AS `design`,`contactlen0_`.`diameterMax` AS `diameterMax`,`contactlen0_`.`diameterMin` AS `diameterMin`,`contactlen0_`.`dominant` AS `dominant`,`contactlen0_`.`duration` AS `duration`,`contactlen0_`.`family` AS `family`,`contactlen0_`.`material` AS `material`,`contactlen0_`.`pack` AS `pack`,`contactlen0_`.`source` AS `source`,`contactlen0_`.`sphereMax` AS `sphereMax`,`contactlen0_`.`sphereMin` AS `sphereMin`,`contactlen0_`.`type` AS `type`,`contactlen0_`.`taxRate_id` AS `taxRate_id` from `contactlens` `contactlen0_` where ((1 = 1) and (`contactlen0_`.`sphereMin` <= 1.25) and (`contactlen0_`.`sphereMax` >= 1.75) and (`contactlen0_`.`additionMin` <= 2.25) and (`contactlen0_`.`additionMax` >= 2.5) and (`contactlen0_`.`type` = 'MULTI_FOCAL')) order by `contactlen0_`.`manufacturer`,`contactlen0_`.`line`,`contactlen0_`.`duration`,`contactlen0_`.`sphereMin`,`contactlen0_`.`cylinderMin`,`contactlen0_`.`additionMin` limit 10"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
          {
            "condition_processing": {
              "condition": "WHERE",
              "original_condition": "((1 = 1) and (`contactlen0_`.`sphereMin` <= 1.25) and (`contactlen0_`.`sphereMax` >= 1.75) and (`contactlen0_`.`additionMin` <= 2.25) and (`contactlen0_`.`additionMax` >= 2.5) and (`contactlen0_`.`type` = 'MULTI_FOCAL'))",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((1 = 1) and (`contactlen0_`.`sphereMin` <= 1.25) and (`contactlen0_`.`sphereMax` >= 1.75) and (`contactlen0_`.`additionMin` <= 2.25) and (`contactlen0_`.`additionMax` >= 2.5) and (`contactlen0_`.`type` = 'MULTI_FOCAL'))"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((1 = 1) and (`contactlen0_`.`sphereMin` <= 1.25) and (`contactlen0_`.`sphereMax` >= 1.75) and (`contactlen0_`.`additionMin` <= 2.25) and (`contactlen0_`.`additionMax` >= 2.5) and (`contactlen0_`.`type` = 'MULTI_FOCAL'))"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`contactlen0_`.`sphereMin` <= 1.25) and (`contactlen0_`.`sphereMax` >= 1.75) and (`contactlen0_`.`additionMin` <= 2.25) and (`contactlen0_`.`additionMax` >= 2.5) and (`contactlen0_`.`type` = 'MULTI_FOCAL'))"
                }
              ]
            }
          },
          {
            "substitute_generated_columns": {
            }
          },
          {
            "table_dependencies": [
              {
                "table": "`contactlens` `contactlen0_`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ]
              }
            ]
          },
          {
            "ref_optimizer_key_uses": [
              {
                "table": "`contactlens` `contactlen0_`",
                "field": "type",
                "equals": "'MULTI_FOCAL'",
                "null_rejecting": false
              }
            ]
          },
          {
            "rows_estimation": [
              {
                "table": "`contactlens` `contactlen0_`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 728004,
                    "cost": 171586
                  },
                  "potential_range_indexes": [
                    {
                      "index": "PRIMARY",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_sku",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "UK_elol05sqtuwi88exc8cdmqul1",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_upc",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "FKq7sw02khmcn1nqil9pcxkgmfa",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_manufacturer_line_duration_sph_cyl_add",
                      "usable": true,
                      "key_parts": [
                        "type",
                        "design",
                        "line",
                        "duration",
                        "sphereMin",
                        "sphereMax",
                        "cylinderMin",
                        "cylinderMax",
                        "axisMin",
                        "axisMax",
                        "additionMin",
                        "additionMax",
                        "manufacturer",
                        "id"
                      ]
                    },
                    {
                      "index": "idx_sorting",
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_design",
                      "usable": false,
                      "cause": "not_applicable"
                    }
                  ],
                  "setup_range_conditions": [
                  ],
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  },
                  "analyzing_range_alternatives": {
                    "range_scan_alternatives": [
                      {
                        "index": "idx_manufacturer_line_duration_sph_cyl_add",
                        "ranges": [
                          "MULTI_FOCAL <= type <= MULTI_FOCAL"
                        ],
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 364002,
                        "cost": 436803,
                        "chosen": false,
                        "cause": "cost"
                      }
                    ],
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    }
                  }
                }
              }
            ]
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ],
                "table": "`contactlens` `contactlen0_`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "idx_manufacturer_line_duration_sph_cyl_add",
                      "rows": 364002,
                      "cost": 145601,
                      "chosen": true
                    },
                    {
                      "rows_to_scan": 728004,
                      "access_type": "scan",
                      "resulting_rows": 4492.1,
                      "cost": 171584,
                      "chosen": false
                    }
                  ]
                },
                "condition_filtering_pct": 1.2341,
                "rows_for_plan": 4492.1,
                "cost_for_plan": 145601,
                "chosen": true
              }
            ]
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`contactlen0_`.`sphereMin` <= 1.25) and (`contactlen0_`.`sphereMax` >= 1.75) and (`contactlen0_`.`additionMin` <= 2.25) and (`contactlen0_`.`additionMax` >= 2.5) and (`contactlen0_`.`type` = 'MULTI_FOCAL'))",
              "attached_conditions_computation": [
              ],
              "attached_conditions_summary": [
                {
                  "table": "`contactlens` `contactlen0_`",
                  "attached": "((`contactlen0_`.`sphereMin` <= 1.25) and (`contactlen0_`.`sphereMax` >= 1.75) and (`contactlen0_`.`additionMin` <= 2.25) and (`contactlen0_`.`additionMax` >= 2.5))"
                }
              ]
            }
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`contactlen0_`.`manufacturer`,`contactlen0_`.`line`,`contactlen0_`.`duration`,`contactlen0_`.`sphereMin`,`contactlen0_`.`cylinderMin`,`contactlen0_`.`additionMin`",
              "items": [
                {
                  "item": "`contactlen0_`.`manufacturer`"
                },
                {
                  "item": "`contactlen0_`.`line`"
                },
                {
                  "item": "`contactlen0_`.`duration`"
                },
                {
                  "item": "`contactlen0_`.`sphereMin`"
                },
                {
                  "item": "`contactlen0_`.`cylinderMin`"
                },
                {
                  "item": "`contactlen0_`.`additionMin`"
                }
              ],
              "resulting_clause_is_simple": true,
              "resulting_clause": "`contactlen0_`.`manufacturer`,`contactlen0_`.`line`,`contactlen0_`.`duration`,`contactlen0_`.`sphereMin`,`contactlen0_`.`cylinderMin`,`contactlen0_`.`additionMin`"
            }
          },
          {
            "added_back_ref_condition": "((`contactlen0_`.`type` <=> 'MULTI_FOCAL') and ((`contactlen0_`.`sphereMin` <= 1.25) and (`contactlen0_`.`sphereMax` >= 1.75) and (`contactlen0_`.`additionMin` <= 2.25) and (`contactlen0_`.`additionMax` >= 2.5)))"
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "index_order_summary": {
                "table": "`contactlens` `contactlen0_`",
                "index_provides_order": true,
                "order_direction": "asc",
                "index": "idx_sorting",
                "plan_changed": true,
                "access_type": "index"
              }
            }
          },
          {
            "refine_plan": [
              {
                "table": "`contactlens` `contactlen0_`"
              }
            ]
          }
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}

强制使用idx_manufacturer_line_duration_sph_cyl_add索引，解释查询:

SELECT *
FROM `ContactLens` contactlen0_
USE INDEX (idx_manufacturer_line_duration_sph_cyl_add)
WHERE 1=1 
 AND contactlen0_.`sphereMin`<=1.25 
 AND contactlen0_.`sphereMax`>=1.75 
 AND contactlen0_.`additionMin`<=2.25 
 AND contactlen0_.`additionMax`>=2.5
 AND contactlen0_.`type`='MULTI_FOCAL'
ORDER BY contactlen0_.`manufacturer` ASC, contactlen0_.`line` ASC, contactlen0_.`duration` ASC , contactlen0_.`sphereMin` ASC, contactlen0_.`cylinderMin` ASC, contactlen0_.`additionMin` ASC 
LIMIT 10

这是查询的解释:

所以使用了“正确”的索引；即使行基数为 403245(因此比以前大得多)，查询也需要 400 毫秒。

我基于 this paper 创建了索引。

什么是我可以使用的最好的索引来涵盖在我的案例中可以生成的大多数查询？

为什么在查询 2 中“正确的”索引比“错误的”索引快得多，即使它适用于 40 万行？

我如何提示 mysql(不明确地这样做)使用正确的索引？

======== 与 MYSQL 8 和 RDS AURORA 的比较 ======

根据@oysteing 的建议，我在 Mysql 8(8.0.11 和 8.0.13)和 RDS Aurora 上测试了查询 2。这是我得到的结果

Mysql 8.0.11

优化器跟踪:https://codeshare.io/2BXVeK

Mysql 8.0.13

优化器跟踪:https://codeshare.io/5Ookyr

并尝试在列上使用直方图:

https://codeshare.io/2KdgO7

RDS 极光

优化器跟踪:https://codeshare.io/5X4Ngj

Answer 1

在这里查看我的回答:https://stackoverflow.com/a/52033986/3481706

在你的例子中，查询优化器显然认为它可以从 idx_sorting 索引中读取几行 (20) 来找到满足 WHERE 条件的前 10 行。这似乎有点奇怪，因为 EXPLAIN 说它估计只有 0.62% 可以满足条件。 (也许这里的 LIMIT 优化有一个错误。如果我有优化器跟踪，我可能会告诉更多。)最终结果是，通过使用 idx_sorting，它将访问远远超过它找到之前估计的 20 行满足查询的 10 行。

您可以尝试升级到MySQL 8.0，看看这个问题是否已经解决。在 8.0 中，您还可以在用于改善过滤估计的列上创建直方图。

一般情况下，在创建复合索引时，应将等式条件下使用的列放在最前面。很难创建一个能很好地覆盖所有类型查询的索引，因为 MySQL 只能使用索引的前缀，除了最后一列外，所有列都具有相等条件。这在 MySQL 8.0 中有了新的 skip-scan access method 改进。 .此方法允许使用索引前缀中的一个间隙。