1.问题

先建立一张表：

  1. # 创建表
  2. CREATE TABLE t
  3. (
  4.     id           INT UNSIGNED NOT NULL AUTO_INCREMENT,
  5.     key1         VARCHAR(100),
  6.     common_field VARCHAR(100),
  7.     PRIMARY KEY (id),
  8.     KEY idx_key1 (key1)
  9. ) Engine = InnoDB
  10.   CHARSET = utf8;
  11. # 定义变量
  12. SELECT @i := 1;
  13. # 整个存储过程往表中插入10000数据
  14. DELIMITER $
  15. CREATE PROCEDURE my_b()
  16. BEGIN
  18.     while @i <= 10000
  19.         do
  21.             insert into t(key1, common_field) VALUES (uuid(), uuid());
  22.             select @i := @i + 1; # 自增
  23.         end while;
  25. END;
  26. CALL my_b();

表t包含3个列，id列是主键，key1列是二级索引列。表中包含1万条记录。

当我们执行下边这个语句的时候，是使用二级索引idx_key1的：

1.png

这个很好理解，因为在二级索引idx_key1中，key1列是有序的。而查询是要取按照key1列排序的第1条记录，那MySQL只需要从idx_key1中获取到第一条二级索引记录，然后直接回表取得完整的记录即可。

但是如果我们把上边语句的LIMIT 1换成LIMIT 5000, 1，则却需要进行全表扫描，并进行filesort，执行计划如下：

image.png

LIMIT 5000, 1也可以使用二级索引idx_key1呀，我们可以先扫描到第5001条二级索引记录，对第5001条二级索引记录进行回表操作不就好了么，这样的代价肯定比全表扫描+filesort强呀。但是由于MySQL实现上的缺陷，不会出现上述的理想情况，它只会去执行全表扫描+filesort，下边我们来看一下。

2.LIMIT

MySQL是在server层准备向客户端发送记录的时候才会去处理LIMIT子句中的内容。拿下边这个语句举例子：

  1. SELECT * FROM t ORDER BY key1 LIMIT 5000, 1;

如果使用idx_key1执行上述查询，那么MySQL会这样处理：

  • server层向InnoDB要第1条记录，InnoDB从idx_key1中获取到第一条二级索引记录，然后进行回表操作得到完整的聚簇索引记录，然后返回给server层。server层准备将其发送给客户端，此时发现还有个LIMIT 5000, 1的要求，意味着符合条件的记录中的第5001条才可以真正发送给客户端，所以在这里先做个统计，我们假设server层维护了一个称作limit_count的变量用于统计已经跳过了多少条记录，此时就应该将limit_count设置为1。
  • server层再向InnoDB要下一条记录，InnoDB再根据二级索引记录的next_record属性找到下一条二级索引记录，再次进行回表得到完整的聚簇索引记录返回给server层。server层在将其发送给客户端的时候发现limit_count才是1，所以就放弃发送到客户端的操作，将limit_count加1，此时limit_count变为了2。
  • … 重复上述操作
  • 直到limit_count等于5000的时候，server层才会真正的将InnoDB返回的完整聚簇索引记录发送给客户端。

从上述过程中可以看到，由于MySQL中是在实际向客户端发送记录前才会去判断LIMIT子句是否符合要求，所以如果使用二级索引执行上述查询的话，意味着要进行5001次回表操作。server层在进行执行计划分析的时候会觉得执行这么多次回表的成本太大了，还不如直接全表扫描+filesort快呢，所以就选择了后者执行查询。

3.优化手段

由于MySQL实现LIMIT子句的局限性，在处理诸如LIMIT 5000, 1这样的语句时就无法通过使用二级索引来加快查询速度了么？其实也不是，只要把上述语句改写成：

  1. SELECT * FROM t, (SELECT id FROM t ORDER BY key1 LIMIT 5000, 1) AS d    WHERE t.id = d.id;

这样，SELECT id FROM t ORDER BY key1 LIMIT 5000, 1作为一个子查询单独存在，由于该子查询的查询列表只有一个id列，MySQL可以通过仅扫描二级索引idx_key1执行该子查询，然后再根据子查询中获得到的主键值去表t中进行查找。

这样就省去了前5000条记录的回表操作，从而大大提升了查询效率！

3.png