分页优化 - wwq0912/mysql GitHub Wiki

https://www.cnblogs.com/fanyong/p/3351350.html

CREATE TABLE `test` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `TITLE` varchar(255) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6624029 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;
  

mysql> select count(*) from test;
+----------+
| count(*) |
+----------+
|  3584710 |
+----------+

需求：要迁移表的全部数据且该表数据量很大

普通写法：

INSERT INTO otherTable(
   ID,
   TITLE
) ( SELECT 
   ID,
   TITLE
   FROM test
   ORDER BY ID
   LIMIT i,2000
);
SET i = i + 2000;

这种写法copy过程进行的越久速度越慢。而且如果删除了前面的数据就会丢数据 为什么呢？

1. 普通写法的列子

select * from test order by id limit 2000000,2000;

1.547s 看下执行计划

mysql> explain select * from test order by id limit 2000000,2000;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+----------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | filtered | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+----------+-------+
|  1 | SIMPLE      | test  | index | NULL          | PRIMARY | 4       | NULL | 2002000 |   100.00 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+----------+-------+
1 row in set, 1 warning (0.00 sec)

2.优化后的例子

   select * from test
    where id>= (select id from test order by id limit 2000000,1)
  order by id
  limit 2000
  ;

0.64s

mysql> explain select * from test where id >= (select id from user_email_template order by id limit 2000000,1) 
order by id  limit 2000;

+----+-------------+---------+-------+---------------+---------+---------+------+----------+----------+-------------+
| id | select_type | table   | type  | possible_keys | key     | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+---------+-------+---------------+---------+---------+------+----------+----------+-------------+
|  1 | PRIMARY     | test    | range | PRIMARY       | PRIMARY | 4       | NULL |  1834458 |   100.00 | Using where |
|  2 | SUBQUERY    | test    | index | NULL          | PRIMARY | 4       | NULL | 3668917  |   100.00 | Using index |
+----+-------------+---------+-------+---------------+---------+---------+------+----------+----------+-------------+
2 rows in set, 1 warning (0.00 sec)

为什么会快呢， 因为 select id from user_email_template order by id limit 2000000,1 这里按主键排序 然后取第2000000条的主键值， 因为索引本来就是排序的 所以定位到第2000000条特别快。 然后再执行 select * from test where id >= XX order by id limit 2000; 这里再去定位结果集的第一条就比 limit 2000000,2000 定位快