- 现象
表结构如下
复制代码
CREATE TABLE ACT_HI_INST
(
ID
varchar(64) COLLATE utf8_bin NOT NULL COMMENT ‘主键’,
INST_ID_
varchar(64) COLLATE utf8_bin NOT NULL COMMENT ‘流程实例id’,
BUSINESS_KEY_
varchar(255) COLLATE utf8_bin DEFAULT ‘’ COMMENT ‘流程编号’,
CREATE_TIME_
datetime DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
UPDATE_TIME_
datetime DEFAULT CURRENT_TIMESTAMP COMMENT ‘创建时间’,
PRIMARY KEY (ID
),
UNIQUE KEY UIDX_INST
(INST_ID_
),
UNIQUE KEY UIDX_BKEY
(BUSINESS_KEY_
)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
复制代码
执行下面语句, 发现没有走索引
explain
SELECT
*
FROM ACT_HI_INST hi
ORDER BY hi.BUSINESS_KEY_ DESC;
select count(*) from ACT_HI_INST ;
– 4332
同时我们发现rows与实际表的数量存在差异,使用下面语句重新统计索引信息, 更正统计信息, 更新之后发现 rows 就数据表的数量基本一致(不完全一样是因为rows是采样统计而来的)。
analyze table ACT_HI_INST;
也就是说对于下面这条语句,尽管BUSINESS_KEY_ 上有索引,MySQL还是选择了全表扫描。为什么会这样呢?
SELECT * FROM ACT_HI_INST hi ORDER BY hi.BUSINESS_KEY_ DESC;
-
猜想
我猜想原因是 因为MySQL认为即使 使用 BUSINESS_KEY_ 索引树进行查询,不需要排序,但是最后仍然需要回表,回表的次数 == 表的大小,这种代价比全表扫描然后排序的代价更大。 -
验证
为了验证我的猜想,我加上在SQL语句最后加上 LIMIT 10 。如下所示
explain
SELECT
*
FROM ACT_HI_INST hi
ORDER BY hi.BUSINESS_KEY_ DESC limit 10;
果然我们看到加上 LIMIT 的语句走了索引。因为这个时候MySQL认为回表的代价比排序的代价更小,所以这个时候选择了走 BUSINESS_KEY_ 索引。
除了上面这种方式可以验证我的猜想,还有一种方式,如下所示。我们只查询BUSINESS_KEY_ 。
因为此时叶子节点就包含我们需要查询的字段,这个时候不需要再回表,所以MySQL选择BUSINESS_KEY_ 代价最小。
explain
SELECT
hi.BUSINESS_KEY_
FROM ACT_HI_INST hi
ORDER BY hi.BUSINESS_KEY_ DESC ;
- 补充
这部分主要来源于极客时间林晓斌老师的《MySQL实战45讲》第16讲
order by a
亚马逊测评 www.yisuping.com