MySQL优化篇(二)：索引失效与强制索引使用

在项目中需要做个报表统计，需要做多表联查，同一个 SQL 语句 Where 字段相同，但条件值不同，索引使用情况仅然不同，还存在索引失效的情况，使用强制索引与是否一起使用分页对查询效率的影响还存在关联影响。

于是做了如下分析。

数据表

一个数据表，表结构如下，表中的数据每天新增大概 3000 多条。

CREATE TABLE `record_1.1` (
  `id` bigint(20) unsigned NOT NULL COMMENT '主键ID',
  `org_code` varchar(32) DEFAULT NULL COMMENT '机构编码',
  `department_code` varchar(32) DEFAULT NULL COMMENT '部门编码',
  `department_name` varchar(32) DEFAULT NULL COMMENT '部门名称',
  `status` varchar(32) CHARACTER SET utf8mb4 DEFAULT 'undiagnose' COMMENT '未完成=unfinished,已完成=finished,未诊断=undiagnose',
  `read_status` varchar(32) DEFAULT NULL COMMENT '已读=read,未读=unread',
  `created_date` date DEFAULT NULL COMMENT '创建日期',
  PRIMARY KEY (`id`),
  KEY `idx_status` (`status`),
  KEY `idx_deptCode` (`department_code`),
  KEY `idx_createdDate_deptCode` (`created_date`,`department_code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci COMMENT='记录表'

• 表中已有数据 296871 条，
• status，默认值为 undiagnose，还有状态 unfinished 和 finished两个状态
• 建了 status字段的普通索引
• status为 undiagnose 的有 288083 条，占比超 97%，unfinished有 8429 条，finished有 359 条

统计需求

需求：统计出所有部门每天的每种状态的数据量

如下 SQL，统计所有部门每天的状态为 undiagnose 的数据量， 其它两种状态只需改下 status的值。

SELECT
    r.`created_date` AS `date`,
    r.`department_code` AS `department_code`,
    count( 1 ) AS `undiagnose` 
FROM
    record r
WHERE
    ( r.`status` = 'undiagnose' ) 
GROUP BY
    r.`department_code`,
    r.`created_date`;

索引失效

1. 查询所有部门每天的状态为 undiagnose 的数据量，对该条 SQL 使用 EXPLAIN 获取执行计划详情，发现使用索引，扫许了全表扫描。而状态为 unfinished 和 finished 则执行了索引。

   分析：undiagnose 的数据量占比超 97%，优化器认为全表扫描比使用索引效率更优，就会放弃使用索引。

   在执行查询时，默认未使用索引，undiagnose 的查询毫时 1.39 秒左右；

   增加强制索引（force index(idx_name)）测试，毫时反而到 1.58秒左右。

2. 加上分页条件 Limit 0,20，未使用索引毫时，undiagnose 的查询毫时 1.39秒左右，而增加强制索引的查询，毫时又降低到 1.2 秒左右。