mysql优化器选择不使用索引

来源

这篇文章原文来源于某厂的文章，看到后摘抄记录一下。

前置

在数据库中，创建合适的索引是一个比较有效的优化方式，会根据常用场景建立一些合适的索引达到提升查询效率的目的。然后数据库引擎最终选择执行计划时不一定会按你设想的方式去选择你期望的索引。
索引失效的场景应该大部分都知道：

1、对索引使用左或者左右模糊匹配
2、对索引使用函数
3、对索引进行表达式计算
4、对索引隐式类型转换
5、联合索引非最左匹配
6、WHERE子句当中OR
7、索引列上有大量NULL值
8、表的统计信息不准确，优化器可能会错误地评估索引使用成本
9、数据分布不均衡，索引列上有大量重复值
10、......

场景现场

某表中数据共有二十多万条，发现某些查询的扫描行数达到了二十多万条，执行了全表扫描。创建的索引并没有使用上，执行计划中看到type是ALL。
sql语句大致如下(字段名和表名均有调整)：

SELECT f1 AS f1,
       f2 AS f2,
       f3  AS f3,
       f4  AS f4,
       f5 AS f5
FROM table1
WHERE yn = 1
  AND pin = '******'
  AND isexpire = 0
  AND timeline_id IN (400, 401, 466, 520);

表结构：

CREATE TABLE `jr_timeline_account_node`
(
    `id`                 int(10)                       NOT NULL AUTO_INCREMENT COMMENT '主键ID',
    `pin`                varchar(128) COLLATE utf8_bin NOT NULL COMMENT '用户pin',
    `timeline_id`        bigint(15)                    NOT NULL COMMENT '时间轴ID',
    `instance_id`        varchar(70) COLLATE utf8_bin  NOT NULL COMMENT '时间轴账户实例ID',
    `node_position`      int(10)                       NOT NULL COMMENT '时间轴节点',
    `ff` tinyint(2)                    NOT NULL DEFAULT '0' COMMENT '节点完成状态',
    `ff`  tinyint(2)                    NOT NULL DEFAULT '0' COMMENT '节点领奖状态',
    `ff`        datetime                               DEFAULT NULL COMMENT '节点完成时间',
    `ff`         datetime                               DEFAULT NULL COMMENT '节点领奖时间',
    `isexpire`           tinyint(2)                    NOT NULL DEFAULT '0' COMMENT '是否过期 0未过期 1过期',
    `ff`       varchar(150) COLLATE utf8_bin NOT NULL COMMENT '创建人',
    `ff`       datetime(6)                   NOT NULL COMMENT '创建时间',
    `ff`      varchar(150) COLLATE utf8_bin NOT NULL COMMENT '修改人',
    `ff`      timestamp                     NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    `ff`            varchar(512) COLLATE utf8_bin          DEFAULT NULL COMMENT '扩展字段map',
    `yn`                 int(11)                       NOT NULL DEFAULT '1' COMMENT '是否删除标记 0 删除 1 正常',
    `ff`     int(11)                       NOT NULL DEFAULT '1' COMMENT '最后更新编号',
    PRIMARY KEY (`id`),
    KEY `idx_pin` (`pin`),
    KEY `idx_timeline_id` (`timeline_id`),
    KEY `idx_instance_id` (`instance_id`),
    KEY `idx_node_position` (`node_position`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8
  COLLATE = utf8_bin COMMENT ='xxx记录表'

pin和timeline_id字段都单独建立了索引的，按说上面的sql语句至少会使用pin的索引的，然而却没有使用到。
前面说到的索引失效的场景，前面7种场景都不合适，就验证8,9两点了。

1. mysql数据统计分析不正确 ，经查后发现该表里面实际是有二十多万条数据，但是mysql统计出来只有七百多条。
2. 数据分布不均匀 按pin字段分组，发现有八千多个不同的pin，但是有几个pin的数据行是超过1000的，大部分就在100条以下；按timeline_id分组，少的只有几条，多的达到几万条数据。

当数据分布不均匀时，例如一个字段有大量的重复的值，mysql可能会选择不使用索引，而认为全表扫描更高效

解决方案：添加复合索引

如果单列索引由于数据分布不均匀而失效，可以考虑创建复合索引。通过结合其他列，可以增加索引的选择性，使得MySQL更倾向于使用索引。
修改后的表结构建立了pin、timeline_id、isexpire三者的复合索引。经验证sql查询从全表扫描二十多万条数据变成了现在的执行索引只扫描了4条数据。