type
Post
status
Published
date
Jul 17, 2017
slug
use-force-index
summary
tags
MySQL
category
技术分享
icon
password
Property
Dec 18, 2022 02:32 PM
mysql 有查询优化器，在我们发起一个查询的时候， msql 会尝试分析他，看看是否能做一些优化手段来加快查询速度。最常见的比如说有个 (a,b)的联合索引，无论我们使用
SELECT * FROM t WHERE a=? AND b=?还是SELECT * FROM t WHERE b=? AND a=?都能利用到(a,b)这个联合索引，又或者是在很多复杂的查询条件中，分析出使用哪个索引能最大化查询速度，这都是查询优化器做的事情。然而毕竟程序是死的，有的时候查询优化器并不如我们想象中的那么聪明。这时候就需要我们强制 mysql 按照我们的意愿去查询了。
还是我大B站的弹幕系统(^_^)，我们给 UP主提供了『弹幕转移』的功能，方便他们在上传同一个视频的更高清的版本以后，能把之前视频的弹幕转移到新的视频下面来。因为分表分库加业务逻辑限制的原因，我们只能通过复制数据的方式来转移这些弹幕。为了减小对数据库造成的压力，我通过分页查询的方式来限制一次查询的数量：
SELECT id ... FROM table WHERE type=? AND oid=? AND id >? ORDER BY id limit 1000
id 为弹幕的主键。（oid 是视频 id，与弹幕 id 是一对多的关系。）在一个循环中，先初始化 startID = 0，从查询出来的结果中找到最大的 id 赋值给 startID，作为下次查询的最小弹幕 id，这样以分页的方式分批转移弹幕。
数据库中有 oid 的索引，那么我理所应当的认为这次查询走 oid 索引，找到这个 oid 下的所有弹幕 id 以后再进行排序，筛选。然而这个功能上线以后，很快就收到了数据库的报警，提醒我『扫描行数大于XX万行』。但是这些报警的 oid 对应的弹幕 id 数量只有几千个，理论上只会扫描这个 oid 下的弹幕数量的行数的。那为什么数据库还是扫描了这么多行呢？
一般这种情况无需瞎猜，
explain就能发现很多信息，如图：
问题就在于，mysql 自作聪明的使用了主键索引，可以设想一下如果条件是
id > 0那么就相当于全表扫描了，明显是很不效率的行为。因为我们期望 MySQL 通过oid_state的索引来查询，所以我们可以通过force index(ix_oid_state)来强制MySQL使用这个索引：
这样就符合我们的预期了，可以看到扫描的行数只有几千行，使用的索引也是我们指定的
oid_state索引。但是要注意的是，因为使用了
oid_state索引，而这个索引里面字段顺序并不是完全的 id 顺序的，因此会导致额外的filesort。这也是影响查询速度的地方。这两杯毒酒选择喝哪一杯，还是要看各自的业务情况。弹幕的话，因为大部分视频下的弹幕数量比较少（四位数一下），所以使用oid_state索引还是可以提高查询速度的。