MySQL索引优化之分页探索

MySQL索引优化与分页查询策略

最新推荐文章于 2025-07-20 20:05:25 发布

原创最新推荐文章于 2025-07-20 20:05:25 发布 · 564 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库 #database

本文探讨了MySQL中索引优化对于分页查询的影响，以450003条数据的`demo`表为例，分析了使用主键和非主键索引进行分页查询的不同效率。当使用`limit`进行分页时，如果偏移量过大，全表扫描可能更为高效。案例分析了主键连续自增和非主键排序的查询优化方法，提出利用子查询和联合索引避免回表操作，提高查询性能。

`MySQL`索引优化之分页探索

表结构

CREATE TABLE `demo` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(50) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL DEFAULT '' COMMENT '职位',
  `card_num` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '工卡号',
  PRIMARY KEY (`id`),
  KEY `index_union` (`name`,`age`,`position`)
) ENGINE=InnoDB AUTO_INCREMENT=450003 DEFAULT CHARSET=utf8;

450003条数据

limit分页执行情况

像select * from demo limit 90000,10;考虑到回表，所以mysql干脆选择全表扫描。mysql不是直接
从第90000行开始计算10条，而是从第一个叶子节点开始计数，计算90010行。

案例一

针对上图，当id是连续自增的时候，可以用主键筛选出id=90000之后的数据。因为主键的索引是B+树结构，本身就是有序的。

案例二

	先按照name排序，然后再从第90000行起找10行，虽然name是索引，但select的列在index_union索引
树上并没有保存。所以还会涉及到回表，于是mysql直接选择扫主键索引树的叶子结点，先将40多万数据根据name
排好序，然后计算90000行+10行。
	优化方法：利用子查询解决最消耗时间的排序和回表问题，联合索引树种保存有主键id，order by name的话
可以将name、age、position整个索引充分使用因为确定了最左列的排序，其余的俩列age、和position其实也是
排好序的了，通过Extra字段也可以是使用了索引树做排序。
	最外层的查询是根据主键来关联的，所以几乎可以忽略。10+10 因为id是主键，可以直接拿临时表10条数据去扫。