MySQL查询为何仅使用tel_fijo_idx联合索引的部分字段？

表定义 #

CREATE TABLE `prospectos` (
  `provincia` tinyint(3) unsigned NOT NULL,
  `id` int(8) unsigned NOT NULL AUTO_INCREMENT,
  `nombre` varchar(60) COLLATE utf8_bin NOT NULL,
  `telefono_fijo` varchar(15) COLLATE utf8_bin NOT NULL,
  `telefono_movil` varchar(15) COLLATE utf8_bin NOT NULL,
  PRIMARY KEY (`id`,`provincia`),
  KEY `nombre_idx` (`nombre`),
  KEY `tel_fijo_idx` (`provincia`,`telefono_fijo`) USING BTREE,
  KEY `tel_movil_idx` (`provincia`,`telefono_movil`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=35142 DEFAULT CHARSET=utf8 COLLATE=utf8_bin
 PARTITION BY RANGE (`provincia`)
(PARTITION `p01` VALUES LESS THAN (2) ENGINE = InnoDB,
..
..
..
PARTITION `p24` VALUES LESS THAN (25) ENGINE = InnoDB,
PARTITION `p99` VALUES LESS THAN MAXVALUE ENGINE = InnoDB)

情况1：主键索引被完整利用 #

执行查询：

explain format=json select * from prospectos where provincia = 20 and id = 23;

对应的执行计划：

"query_block": {
    "select_id": 1,
    "table": {
      "table_name": "prospectos",
      "partitions": ["p20"],
      "access_type": "const",
      "possible_keys": ["PRIMARY", "tel_fijo_idx", "tel_movil_idx"],
      "key": "PRIMARY",
      "key_length": "5",
      "used_key_parts": ["id", "provincia"],
      "ref": ["const", "const"],
      "rows": 1,
      "filtered": 100
}

这里主键的两个字段id和provincia都被用于索引查找，因为查询条件的类型和字段类型完全匹配（id是int，provincia是tinyint，查询用的都是数字）。

情况2：联合索引tel_fijo_idx仅使用第一个字段 #

执行查询：

explain format=json select * from prospectos where provincia = 20 and telefono_fijo = 3424527000;

对应的执行计划：

"query_block": {
    "select_id": 1,
    "table": {
      "table_name": "prospectos",
      "partitions": ["p20"],
      "access_type": "ref",
      "possible_keys": ["tel_fijo_idx", "tel_movil_idx"],
      "key": "tel_fijo_idx",
      "key_length": "1",
      "used_key_parts": ["provincia"],
      "ref": ["const"],
      "rows": 16042,
      "filtered": 100,
      "attached_condition": "prospectos.telefono_fijo = 3424527000"
}

原因分析 #

核心问题是字段类型不匹配导致的隐式类型转换：

• telefono_fijo字段定义为varchar(15)字符串类型，但查询中使用的是数字3424527000。
• MySQL会自动对telefono_fijo字段做隐式转换，把字符串转成数字来和查询条件匹配，这相当于对字段执行了CAST(telefono_fijo AS UNSIGNED)这类函数操作。
• 索引是按照字符串的字典序排序存储的，一旦对字段做了函数转换，索引的有序性就被破坏了，MySQL无法利用索引的第二个字段telefono_fijo快速定位数据，只能先通过provincia过滤出分区内的数据，再逐行校验telefono_fijo的条件。

验证方案 #

把查询条件中的数字改成字符串形式，执行：

explain format=json select * from prospectos where provincia = 20 and telefono_fijo = '3424527000';

此时字段类型匹配，MySQL会完整利用tel_fijo_idx联合索引的两个字段，执行计划中used_key_parts会包含provincia和telefono_fijo，key_length也会对应变长。

内容的提问来源于stack exchange，提问作者Federico Coco