简单谈谈MySQL的loose index scan

MySQL支持多种类型的索引扫描方法，其中一种比较特殊的方法是loose index scan。来看看loose index scan是如何工作的。

什么是loose index scan

loose index scan是一种基于索引前缀的扫描方法。所谓索引前缀，指的是索引中前面一个或多个列的值，例如索引为(a,b,c)的表，其前缀可以是(a)，(a,b)或(a,b,c)。套用loose index scan的思路，只需要在索引前缀上做扫描，不需要扫描整个索引。这种方法可以减少IO操作，提高查询效率。

如何启用loose index scan呢？

严格来说，MySQL并不需要我们手动启用loose index scan。当MySQL认为该方法比其他方法更优秀，并且可行的时候，就会自动选择loose index scan扫描索引。因此，要想启用这个方法，我们需要让MySQL认为它是最好的选择。

如何让MySQL认为loose index scan是最好的选择

让MySQL选择loose index scan的前提条件是：索引前缀是包含有足够的数据的子集。如果前缀所对应的数据不是很多，那么使用loose index scan就无法跳过大量的数据，此时使用一般的全索引扫描反而会更快。

那么，如何让索引前缀包含有足够的数据呢？

这个问题的答案是：更改查询列的顺序。

假设有以下的表和索引：

CREATE TABLE t (
  a INT NOT NULL,
  b INT NOT NULL,
  c INT NOT NULL,
  d INT NOT NULL,
  PRIMARY KEY (a,b,c,d),
  INDEX idx (c,b,a,d)
);

我们按照下面的顺序查询这张表：

SELECT * FROM t WHERE c=1 AND a=2 AND d>3 AND b>4;

按照顺序c->a->d->b，该查询适合使用loose index scan方法。因为此时，索引前缀idx(c,a,d)所对应的数据，几乎包括了整个查询结果。而如果我们按照其他顺序，很可能就无法利用loose index scan，需要扫描整个索引。

再看一个例子：

SELECT * FROM t WHERE a>1 AND c<9 AND b=3;

如果我们将索引字段的顺序改成(b,c,a)，那么就可以使用loose index scan技术了。

总之，如果想要使用loose index scan方法，在设计索引和写查询语句时一定要注意顺序。

示例一

现在，我们做一个简单的对比实验，看看使用loose index scan的查询和一般的查询有什么不同。

-- 执行时间：100ms
SELECT * FROM t WHERE a=1 AND b=1 AND c=1 AND d=1;

-- 执行时间：80ms
SELECT * FROM t WHERE a=1 AND b=1 AND c=1 AND d BETWEEN 0 AND 100;

前者是一般的查询，后者是加入了BETWEEN的查询。两者的查询结果可能相同，但后者可以使用loose index scan方法，明显 faster than 前者。

示例二

让我们再来模拟一个大一点的表：

CREATE TABLE t_1 (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  status INT NOT NULL,
  created_date DATE NOT NULL,
  INDEX idx_status_name (status,name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

-- 10w行随机数据插入
INSERT INTO t_1 (name,status,created_date) VALUES
  ('名字1',1,'2019-12-01'),
  ('名字2',0,'2019-12-02'),
  ('名字3',1,'2019-12-02'),
  ...
  ('名字99998',0,'2020-08-08'),
  ('名字99999',1,'2020-08-09'),
  ('名字10000',1,'2020-08-10');

以下是查询语句：

-- 执行时间：13ms
-- 使用loose宽度扫描
SELECT * FROM t_1 WHERE status=1 AND name>='名字1000' AND created_date>='2020-07-01';

-- 执行时间：610ms
-- 一般扫描
SELECT * FROM t_1 WHERE status=1 AND name>='名字1000' AND created_date>='2019-07-01';

两个查询条件基本一样，但第一个使用了loose index scan，查询速度明显优于第二个方法。