PS现在orders表中存在idx_user_id(user_id)idx_user_status(user_id, status)idx_remark(remark)三个索引表数据量为10w行1-where order by运行EXPLAINSELECT*FROMordersWHEREuser_id1ORDERBYcreate_time;结果为idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEordersrefidx_user_id,idx_user_statusidx_user_id9const9100Using filesort分析key idx_user_id查询条件user_id 1用到了user_id索引能快速定位到该用户的所有订单rows 9预估只需要扫描9行说明索引过滤效果很好Extra Using filesort这是重点虽然索引帮你定位到了user_id1的行但排序字段是create_time而这个字段不在索引里所以 MySQL 需要额外做一次排序操作filesort。 “filesort”并不是一定写到磁盘它可能在内存里完成但总之是额外的排序步骤如果后续优化的话可以加上联合索引(user_id, create_time)2-单独order by运行EXPLAINSELECT*FROMordersORDERBYcreate_time;结果为idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra1SIMPLEordersALL99520100Using filesort分析(create_time)没有索引所以具体的流程是全表扫描 ↓ 取出99520行 ↓ 按 create_time 排序本质就是create_time字段排序了还是要建立索引提高性能3-order by limit常用SqlSELECT*FROMordersORDERBYcreate_timeDESCLIMIT20;看起来只查20条但数据库仍然要扫描全部数据 → 排序全部数据 → 取20条还是要排序4-order by limit分页SqlSELECT*FROMordersORDERBYcreate_timeLIMIT10000,10;很多人以为数据库会直接跳到第10000条 取10条4-1.无索引的情况ORDER BY create_time会触发全表扫描 排序。数据量大时排序需要额外的内存或磁盘临时空间sort buffer / external sort。然后再应用LIMIT 10000,10意味着先排好所有 10w 行再丢掉前 10000 行只取 10 行。1. 扫描全表 10w 行读取所有数据到内存 2. 对 10w 行进行快速排序O(N log N) 3. 取排序后的第 10001-10010 行 4. 返回 10 行4-2.有create_time索引的情况索引本身就是按create_time排序的BTree查询可以直接在索引上顺序扫描不需要额外排序但是LIMIT 10000,10依然意味着要从索引头开始扫描到第 10010 行然后返回第 10001~10010 行1. 从索引树根节点开始按 create_time 顺序遍历 2. 跳过前 10000 个索引条目仍需数过去但只读索引 3. 取第 10001-10010 个索引条目 4. 根据这 10 个 id 回表取完整数据 5. 返回 10 行所以优化点在于省掉了排序操作但不能跳过前 10000 行的扫描4-3.总结无索引全表排序 → 丢弃前 10 000 → 取 10有索引索引顺序扫描 → 丢弃前 10000 → 取 10优化效果索引省掉了排序操作因为索引本质就是排序的BTree避免了昂贵的排序但 offset 大时仍然会有性能问题因为要扫描很多行PS:无索引情况性能最差直接用表数据相当于全表访问有索引扫描到第 10010 行仅回表10次