explain 的解释

https://www.cnblogs.com/butterfly100/archive/2018/01/15/8287569.html

假设慢查询是 100ms。

测试前准备一千万行数据

CREATE TABLE `tb_ins_pay_order` (
) ENGINE=InnoDB AUTO_INCREMENT=100 DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci COMMENT='';


drop database test_db;
create database test_db;
drop table tb_ins_pay_order;

truncate table tb_ins_pay_order;

drop procedure if exists doWhile;
DELIMITER //  tb_ins_pay_order
CREATE PROCEDURE doWhile()
BEGIN
DECLARE i INT DEFAULT 1; 
WHILE (i <= 10000000) DO
-- 在这里插入 insert 语句，直接用 i 作为 id
SET i = i+1;
END WHILE;
END;

CALL doWhile();

-- 900万条 状态为 3
update tb_ins_pay_order set status = '3' where id <= 9900000;

-- 100 万条状态为 1
update tb_ins_pay_order set status = '1' where id >= 9000000;

实验结果

测试环境本地硬盘：

不带 id 翻最深的页

18:14:25 select * from tb_ins_pay_order where status = ‘1’ limit 990000, 1000 1000 row(s) returned 3.563 sec / 0.020 sec

‘1’, ‘SIMPLE’, ‘tb_ins_pay_order’, NULL, ‘ref’, ‘idx_status’, ‘idx_status’, ‘3’, ‘const’, ‘2078340’, ‘100.00’, NULL

extra 里没有使用 file sort，也没有其他信息，可以认为是 in memory 大翻页的结果。
这个 100.00 是 filtered 的结果，证明这是 explain extended 的输出。

使用 id 加速

18:29:21 select * from tb_ins_pay_order where status = ‘1’ and id >= 9000000 limit 990000, 1000 1000 row(s) returned 1.572 sec / 0.0039 sec

快了一倍左右

1, SIMPLE, tb_ins_pay_order, , range, PRIMARY,idx_status, PRIMARY, 8, , 2019920, 10.00, Using where

有了 id，进入范围查询，且使用了 primary，且使用了 where。三种查询手段都用上了，性能增大很多。

进一步使用精确 id 来加速

18:37:21 select id from tb_ins_pay_order where status = ‘1’ limit 990000, 1000 1000 row(s) returned 0.194 sec / 0.000070 sec

‘1’, ‘SIMPLE’, ‘tb_ins_pay_order’, NULL, ‘ref’, ‘idx_status’, ‘idx_status’, ‘3’, ‘const’, ‘2078340’, ‘100.00’, ‘Using index’

Using index 性能非常高

进一步嵌套（注意这个嵌套不能简化）：

select * from tb_ins_pay_order where id in(select id from ( select id from tb_ins_pay_order where status = ‘1’ limit 990000, 1000) a);

18:38:54 select * from tb_ins_pay_order where id in(select id from ( select id from tb_ins_pay_order where status = ‘1’ limit 990000, 1000) a) LIMIT 0, 1000 1000 row(s) returned 0.199 sec / 0.0039 sec

即使加上 id 也会慢查询。

其原理是：

‘1’, ‘PRIMARY’, ‘‘, NULL, ‘ALL’, NULL, NULL, NULL, NULL, NULL, ‘100.00’, NULL

‘1’, ‘PRIMARY’, ‘tb_ins_pay_order’, NULL, ‘eq_ref’, ‘PRIMARY’, ‘PRIMARY’, ‘8’, ‘.id’, ‘1’, ‘100.00’, NULL

‘2’, ‘MATERIALIZED’, ‘‘, NULL, ‘ALL’, NULL, NULL, NULL, NULL, ‘991000’, ‘100.00’, NULL

‘3’, ‘DERIVED’, ‘tb_ins_pay_order’, NULL, ‘ref’, ‘idx_status’, ‘idx_status’, ‘3’, ‘const’, ‘2078340’, ‘100.00’, ‘Using index’

3 使用 Using index 加速，1 使用 PRIMARY

还是慢查询，因为 in 的数据太多反而导致索引出问题。

一个解决方案是，想办法让 MySQL 自己通过索引给数据排好序，然后只查第一页-更新-然后第二页变成第一页再查询。

覆盖索引是类似 Using index 的效果的。

潜在的 last execution id 方案

每次执行的时候记住上一轮的 last execution id。

然后查询条件加入 > id 等语句再进一步执行，用真正的查询条件进行翻页（而不是限制只翻 500 个 id，不然会翻到死），然后真正的查询条件会告诉我们下一页在哪里。