MySQL查询优化：索引选择与强制索引使用

场景描述

假设我们有一个名为orders的表，其中包含order_id、customer_id、status和created_at等字段。

查看代码

DROP TABLE IF EXISTS orders;
CREATE TABLE `orders` (
  `order_id` INT NOT NULL,
  `customer_id` INT NOT NULL,
  `status` VARCHAR(50) NOT NULL,
  `created_at` DATETIME NOT NULL,
  PRIMARY KEY (`order_id`),
  INDEX `idx_customer_id` (`customer_id`),
  INDEX `idx_created_at` (`created_at`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

现在我们需要执行一个查询：选择customer_id等于某个值且status为'completed'的所有记录，并按照order_id字段的降序排列，限制结果为10条记录。

查询语句如下：

SELECT * FROM orders WHERE customer_id=1 AND status='completed' ORDER BY order_id DESC LIMIT 10;

在实际应用中，我们发现在某些customer_id值的情况下，查询速度很快，但在其他customer_id值的情况下，查询速度非常慢。

模拟插入数据

创建存储过程，为指定客户ID的客户插入一定数量的订单，每个订单都具有指定的状态和当前时间加上1分钟的时间戳。

查看代码

DELIMITER $$

-- 如果存储过程已经存在，先删除它
DROP PROCEDURE IF EXISTS InsertOrdersForCustomer $$

-- 创建新的存储过程
CREATE PROCEDURE InsertOrdersForCustomer(
    IN p_customer_id INT,
    IN p_status VARCHAR(50),
    IN p_count INT
)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE p_created_at DATETIME DEFAULT NOW();
    DECLARE p_order_id INT;
    
    -- 获取当前最大的order_id
    SELECT MAX(order_id) INTO p_order_id FROM orders;
    -- 如果表中没有数据，则从1开始
    IF p_order_id IS NULL THEN
        SET p_order_id = 1;
    ELSE
        -- 递增order_id
        SET p_order_id = p_order_id + 1;
    END IF;

    -- 循环插入订单
    WHILE i <= p_count DO
        INSERT INTO orders (`order_id`, `customer_id`, `status`, `created_at`)
        VALUES (p_order_id + i - 1, p_customer_id, p_status, p_created_at);
        
        -- 更新创建时间，模拟不同的订单时间
        SET p_created_at = DATE_ADD(p_created_at, INTERVAL 1 MINUTE);
        SET i = i + 1;
    END WHILE;
END $$

DELIMITER ;

使用存储过程，为客户1插入完成订单10条。

为客户2插入完成订单5条，再插入处理订单1000000条，再插入完成订单5条，这样完成订单共10条。

CALL InsertOrdersForCustomer ( 1, 'completed', 10 );
CALL InsertOrdersForCustomer ( 2, 'processing', 10000 );
CALL InsertOrdersForCustomer ( 2, 'completed', 5 );
CALL InsertOrdersForCustomer ( 2, 'processing', 10000 );
CALL InsertOrdersForCustomer ( 2, 'completed', 5 );
DROP PROCEDURE InsertOrdersForCustomer;

理解执行计划

为了理解查询性能的差异，我们首先需要查看MySQL的执行计划。执行计划可以帮助我们了解MySQL是如何执行查询的，包括它使用了哪些索引以及如何对数据进行排序和过滤。

执行计划可以通过EXPLAIN关键字来获取。例如：

EXPLAIN SELECT * FROM orders WHERE customer_id=xxx AND status='completed' ORDER BY order_id DESC LIMIT 10;

执行计划会显示一系列的信息，包括possible_keys（可能使用的索引）和key（实际使用的索引）。

在我们的场景中，我们注意到possible_keys中包含了idx_customer_id索引，但实际使用的索引却是主键索引PRIMARY。这意味着MySQL选择了主键索引进行扫描，并通过WHERE条件进行数据过滤。

索引选择的原理

在MySQL中，查询优化器会根据一系列的规则和成本估算来决定使用哪个索引。优化器会尝试预测使用不同索引的代价，并选择代价最低的方案。

索引扫描与排序

查看代码

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "59057.80"
    },
    "ordering_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "orders",
        "access_type": "ref",
        "possible_keys": [
          "idx_customer_id"
        ],
        "key": "idx_customer_id",
        "used_key_parts": [
          "customer_id"
        ],
        "key_length": "4",
        "ref": [
          "const"
        ],
        "rows_examined_per_scan": 568708,
        "rows_produced_per_join": 56870,
        "filtered": "10.00",
        "backward_index_scan": true,
        "cost_info": {
          "read_cost": "2187.00",
          "eval_cost": "5687.08",
          "prefix_cost": "59057.80",
          "data_read_per_join": "11M"
        },
        "used_columns": [
          "order_id",
          "customer_id",
          "status",
          "created_at"
        ],
        "attached_condition": "(`sql_cengxuyuan`.`orders`.`status` = 'completed')"
      }
    }
  }
}

当我们执行以下查询时：

SELECT * FROM orders WHERE customer_id=2 AND status='completed' ORDER BY order_id DESC LIMIT 10;

MySQL首先会考虑使用idx_customer_id索引来查找满足customer_id=xxx条件的记录。然而，由于status='completed'这个条件不在idx_customer_id索引中，MySQL需要额外的步骤来找到满足status='completed'的记录。如果idx_customer_id索引的选择性不高（即有很多记录具有相同的customer_id值），那么索引扫描可能会检索大量的记录。

回表查询

即使idx_customer_id索引能够快速定位到满足customer_id=xxx的记录，MySQL还需要进行回表查询来获取这些记录的完整数据，因为idx_customer_id是一个非聚簇索引，它不包含所有列的数据。这意味着每个匹配的索引项都需要额外的磁盘I/O来查找对应的行数据。

排序操作

查询中的ORDER BY created_at DESC要求结果集按照order_id字段的降序排列。如果MySQL选择使用idx_customer_id索引，它可能需要在内存或磁盘中对检索到的记录进行排序，因为索引本身可能不保证order_id的顺序。排序操作是成本很高的，尤其是在数据量大的时候。

LIMIT子句

LIMIT 10表示只需要最前面的10条记录。如果MySQL认为使用idx_customer_id索引会导致大量的排序工作，而最终只需要10条记录，它可能会认为这种方式的成本太高。

因此，优化器可能会选择另一种方式：直接扫描主键索引（聚簇索引），因为它已经按照order_id字段排序，这样可以避免额外的排序操作。

MySQL的选择

综合以上因素，MySQL的查询优化器可能会认为直接扫描主键索引并应用WHERE条件过滤会更有效率。这种方式可以避免大量的回表查询和排序操作，尤其是在查询的结果集很小的情况下。然而，当customer_id值很大时，如果满足条件的记录在整个表中分布得很广，那么扫描主键索引可能会导致大量的磁盘I/O，从而使得查询变得非常慢。

强制索引的优势

通过使用FORCE INDEX，我们可以告诉MySQL忽略它的成本估算，直接使用指定的索引。在我们的例子中，FORCE INDEX (idx_customer_id)可能会导致MySQL首先使用idx_customer_id索引来快速定位到满足customer_id=xxx的记录，然后进行必要的回表查询和排序操作。如果idx_customer_id索引的选择性足够高，那么这种方式的性能可能会比扫描主键索引要好。

强制索引的使用

为了解决上述问题，我们可以采用强制索引（force index）的方法。

在我们的场景中，我们可以通过在查询语句中添加FORCE INDEX来强制使用idx_customer_id索引。

SELECT * FROM orders FORCE INDEX (idx_customer_id) WHERE customer_id=2 AND status='completed' ORDER BY created_at DESC LIMIT 10;

通过使用强制索引，我们可以看到执行计划中实际使用的索引变为了idx_customer_id，查询性能得到了显著提升。