mysql limit

---

批量插入数据参考文章：https://blog.csdn.net/qq_24613517/article/details/80526735

概述

• limit offset, N ： 需要查询 （offset+N）次索引节点，（offset+N）次聚簇索引的数据，最后再将结果过滤掉前 offset 条，取出最后N条。MySQL耗费了大量随机I/O在查询聚簇索引的数据上，而有 offset 次随机I/O查询到的数据是不会出现在结果集当中的。
• 优化：使用 select * from table_name a inner join (select id from table_name where value=xxx limit offset,N) b on a.id=b.id; ，但是有时这个优化语句会失效（value 是聚集索引时就会失效）。

比较

建表

CREATE TABLE `tb_limit_test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `key` bigint(20) DEFAULT NULL,
  `value` varchar(255) DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `key_ind` (`key`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

灌数

DROP PROCEDURE  IF EXISTS batch_insert;
delimiter //   -- 把界定符改成双斜杠
CREATE PROCEDURE batch_insert(IN init INT, IN loop_time INT)  -- 第一个参数为初始ID号（可自定义），第二个位生成MySQL记录个数
BEGIN
    DECLARE Var INT;
    DECLARE init_var INT;
    SET Var = 0;
    SET init_var = init;
    WHILE Var < loop_time DO
        insert into tb_limit_test(`key`,`value`) values (init_var, CONCAT(init_var, '_val'));
        SET init_var = init_var + 1;
        SET Var = Var + 1;
    END WHILE;
END;
//
delimiter ;  -- 界定符改回分号
CALL batch_insert(2, 5000000);   -- 调用存储过程插入函数

• 这个实际还是慢，我插入的时候时间是：从 下午 16:32 到晚上 22:00 左右，只插入了60多万条数据。我粗略算了一下按着这个速度得41个小时多才能完成 500w 数据的插入。
• 坦白说这个时间很长，长到我去床上躺了一下看了个庆克隆美国上市的视频；又写了一个 m3u8 下载的程序。
• 为了加快速度可以用开头给的文章链接中 LOAD DATA 试试，用法可参考：https://blog.csdn.net/u012815136/article/details/88953289
• 或者修改全局变量 innodb_flush_log_at_trx_commit ：

show VARIABLES  like 'innodb_flush_log_at_trx_commit';
set GLOBAL innodb_flush_log_at_trx_commit=0;

• 设置之后 100w 数据 用时：42.898s 贼快；300w 数据：154.917s 快多了
• 有关这个变量说明 ：https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit；https://blog.csdn.net/fengbangyue/article/details/6401564/

直接查

• SELECT * FROM tb_limit_test t where t.key=4000000 limit 3500000,5;：这个结果是已经有 buffer_pool 的情况下的 ，实际更慢。

使用 inner join

• select * from tb_limit_test a inner join (select id from tb_limit_test where key =4000000 limit 3500000,5) b on a.id=b.id;

查看存储过程

• show procedure status;
• show procedure status like '%batch%';