CREATE TABLE `person` ( `id` int NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `address` varchar(100) DEFAULT NULL, `gmt_created` timestamp NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), KEY `idx_name` (`name`)) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4

备注:主健id自增,name索引,address没有索引,创建韶光为当前韶光

1.2 测试数据

一百万的测试数据,详细创建过程见mysql快速创建一百万条数据

jsp使用limit分页展示mysql调优系列  limit分页 Java

表中记录如下

二、分页查询

2.1 根据name查询一页数据

-- 偏移量小语句1)select from person where name like 'name1%' order by id limit 0, 10;-- 偏移量大语句2)select from person where name like 'name1%' order by id limit 900000, 10;

实行操持和均匀耗时

结果:语句2的耗时是语句1的150倍

2.2 缘故原由剖析

1)Page是 Innodb 用于管理数据的最小磁盘单位。
常见的页类型有数据页、Undo 页、系统页、事务数据页等。
默认的页大小为 16KB,每个页中至少存储有 2 条或以上的记录。

2)当偏移量大时,遍历范围变大,增加了磁盘IO次数

2.3 推举写法

select from person where id in (select temp.id from (select id from person where name like 'name1%' order by id asc limit 900000,10) as temp);

优化结果:

均匀耗时60.33ms,比优化前提升2.53倍。
当表中字段比较多时,差异会更大!

缘故原由剖析:

1)内部查询语句在实行过程中,只需筛选Id,而不是全部列。
增加了单次IO读取的数据量。