中国移动网站建设网站建设用自助建站系统好不好

张

张建站

2026/4/9 17:24:28

10分钟阅读

中国移动网站建设,网站建设用自助建站系统好不好,做网站要注意的,做破解软件网站赚广告费MySQL之Limit深度分页性能问题与优化指南目录问题背景性能问题分析优化方案最佳实践性能对比问题背景什么是深度分页深度分页是指在数据量较大的情况下#xff0c;查询偏移量#xff08;offset#xff09;很大的分页场景。例如#xff1a; -- 第一页#xff1a;…MySQL之Limit深度分页性能问题与优化指南目录问题背景性能问题分析优化方案最佳实践性能对比问题背景什么是深度分页深度分页是指在数据量较大的情况下查询偏移量offset很大的分页场景。例如-- 第一页查询很快SELECT*FROMordersORDERBYidLIMIT0,10;-- 第1000页开始变慢SELECT*FROMordersORDERBYidLIMIT9990,10;-- 第100000页非常慢SELECT*FROMordersORDERBYidLIMIT999990,10;典型场景电商商品列表百万级商品订单历史查询千万级订单日志数据查询亿级日志社交媒体动态海量用户内容性能问题分析MySQL Limit 执行原理当执行SELECT * FROM table ORDER BY column LIMIT offset, limit时MySQL 根据索引找到排序后的前offset limit条记录丢弃前offset条记录返回剩余的limit条记录关键问题MySQL 必须扫描并读取前offset limit条记录即使最终只需要limit条。性能问题根源查询: SELECT * FROM orders ORDER BY id LIMIT 1000000, 10 执行过程: ┌─────────────────────────────────────────────────────┐ │ 1. 扫描 1000010 条记录 │ │ 2. 读取前 1000000 条记录到内存 │ │ 3. 丢弃前 1000000 条记录 │ │ 4. 返回最后 10 条记录 │ └─────────────────────────────────────────────────────┘ 时间复杂度: O(offset limit) 空间复杂度: O(offset limit)性能测试数据假设orders表有 1000 万条记录Offset执行时间扫描行数返回行数00.01s101010000.05s101010100000.3s10010101000002.5s10001010100000025s1000010105000000120s500001010结论执行时间与 offset 呈线性关系offset 越大性能越差。EXPLAIN 分析EXPLAINSELECT*FROMordersORDERBYidLIMIT1000000,10;输出示例------------------------------------------------------------------------------------------------------------- | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | ------------------------------------------------------------------------------------------------------------- | 1 | SIMPLE | orders | NULL | index | NULL | PRIMARY | 4 | NULL | 1000010 | 100.00 | NULL | -------------------------------------------------------------------------------------------------------------关键观察type: index- 使用了索引扫描rows: 1000010- 预计扫描 1000010 行即使有索引仍需扫描大量数据优化方案方案一子查询优化ID 范围查询原理先查询出当前页起始 ID再通过 ID 范围查询数据。实现-- 原始查询慢SELECT*FROMordersORDERBYidLIMIT1000000,10;-- 优化后快SELECT*FROMordersWHEREid(SELECTidFROMordersORDERBYidLIMIT1000000,1)ORDERBYidLIMIT10;或者更简洁的写法-- 先获取上一页最后一条记录的 IDSELECTidFROMordersORDERBYidLIMIT1000000,1;-- 假设返回: 1000001-- 使用 ID 范围查询SELECT*FROMordersWHEREid1000001ORDERBYidLIMIT10;性能提升场景原始查询优化后查询性能提升Offset: 100000025s0.05s500xOffset: 5000000120s0.08s1500x适用场景主键是连续的自增 ID按主键排序不需要跳页查询局限性不支持跳页如直接跳到第 500 页如果主键不连续需要额外处理方案二延迟关联Deferred Join原理先通过索引查询出符合条件的 ID再根据 ID 关联查询完整数据。实现-- 原始查询慢SELECT*FROMordersWHEREstatuscompletedORDERBYcreate_timeLIMIT1000000,10;-- 优化后快SELECTo.*FROMorders oINNERJOIN(SELECTidFROMordersWHEREstatuscompletedORDERBYcreate_timeLIMIT1000000,10)AStmpONo.idtmp.id;为什么有效子查询只查询 ID 列数据量小可以在索引中完成避免了读取完整行数据到内存减少了 I/O 操作性能对比-- 测试表结构CREATETABLEorders(idBIGINTPRIMARYKEYAUTO_INCREMENT,user_idINTNOTNULL,statusVARCHAR(20),create_timeDATETIME,-- 其他字段...INDEXidx_status_create_time(status,create_time));-- 数据量: 1000万条-- 查询: statuscompleted 的记录约 500万条查询方式执行时间扫描行数读取数据量原始查询18s1000010完整行数据延迟关联2.5s1000010仅 ID 10 行完整数据方案三游标分页Cursor-based Pagination原理使用上一页最后一条记录的某个字段作为游标查询大于该游标的记录。实现-- 第一页SELECT*FROMordersORDERBYidLIMIT10;-- 假设返回最后一条记录的 id 10-- 下一页使用游标SELECT*FROMordersWHEREid10ORDERBYidLIMIT10;-- 再下一页SELECT*FROMordersWHEREid20ORDERBYidLIMIT10;复杂排序场景-- 按多个字段排序SELECT*FROMordersORDERBYcreate_timeDESC,idDESCLIMIT10;-- 下一页SELECT*FROMordersWHERE(create_time,id)(2024-01-01 12:00:00,10000)ORDERBYcreate_timeDESC,idDESCLIMIT10;API 设计示例// 请求GET/api/orders?limit10cursoreyJpZCI6MTAwMDAsImNyZWF0ZV90aW1lIjoiMjAyNC0wMS0wMSAxMjowMDowMCJ9// 响应{data:[...],pagination:{next_cursor:eyJpZCI6MTAwMTAsImNyZWF0ZV90aW1lIjoiMjAyNC0wMS0wMSAxMjowMTowMCJ9,has_more:true}}优缺点优点缺点性能稳定不受 offset 影响不支持跳页适合无限滚动场景需要客户端保存游标减少数据库压力实现相对复杂方案四覆盖索引优化原理创建覆盖索引使查询可以直接从索引获取数据无需回表。实现-- 原始查询SELECTid,user_id,statusFROMordersORDERBYcreate_timeLIMIT1000000,10;-- 创建覆盖索引CREATEINDEXidx_coveringONorders(create_time,id,user_id,status);-- 优化后的查询可以直接从索引获取所有字段EXPLAIN 对比-- 优化前EXPLAINSELECTid,user_id,statusFROMordersORDERBYcreate_timeLIMIT1000000,10;-- Extra: Using filesort-- 优化后有覆盖索引EXPLAINSELECTid,user_id,statusFROMordersORDERBYcreate_timeLIMIT1000000,10;-- Extra: Using index适用场景查询字段较少且固定可以接受额外的索引存储空间查询模式相对稳定方案五预计算/缓存原理预先计算分页数据并缓存减少实时查询压力。实现-- 创建分页缓存表CREATETABLEorders_page_cache(page_numINTPRIMARYKEY,start_idBIGINT,end_idBIGINT,updated_atTIMESTAMPDEFAULTCURRENT_TIMESTAMPONUPDATECURRENT_TIMESTAMP);-- 定期更新缓存INSERTINTOorders_page_cache(page_num,start_id,end_id)SELECTFLOOR((rn-1)/10)1ASpage_num,MIN(id)ASstart_id,MAX(id)ASend_idFROM(SELECTid,ROW_NUMBER()OVER(ORDERBYid)ASrnFROMorders)ASnumberedGROUPBYpage_numONDUPLICATEKEYUPDATEstart_idVALUES(start_id),end_idVALUES(end_id);-- 查询时使用缓存SELECTo.*FROMorders oINNERJOINorders_page_cache cONo.idc.start_idANDo.idc.end_idWHEREc.page_num100000ORDERBYo.idLIMIT10;Redis 缓存实现importredisimportjson rredis.Redis(hostlocalhost,port6379,db0)defget_page_data(page_num,page_size10):cache_keyforders:page:{page_num}:{page_size}cachedr.get(cache_key)ifcached:returnjson.loads(cached)# 查询数据库offset(page_num-1)*page_size datadb.query(SELECT * FROM orders ORDER BY id LIMIT %s, %s,(offset,page_size))# 缓存结果设置过期时间r.setex(cache_key,3600,json.dumps(data))returndata方案六搜索引擎替代原理对于超大数据量的分页查询使用专门的搜索引擎如 Elasticsearch。实现示例// Elasticsearch 查询GET/orders/_search{from:1000000,size:10,sort:[{create_time:desc},{_id:desc}]}// 使用 search_after 进行深度分页GET/orders/_search{size:10,sort:[{create_time:desc},{_id:desc}],search_after:[2024-01-01T12:00:00,10000]}适用场景数据量超过亿级需要复杂的搜索条件对实时性要求不高最佳实践1. 根据场景选择合适的方案场景推荐方案小数据量10万原始 LIMIT 即可中等数据量10万-1000万延迟关联、子查询优化大数据量1000万游标分页、搜索引擎需要跳页子查询优化缓存无限滚动游标分页2. 索引优化建议-- 确保排序字段有索引CREATEINDEXidx_sort_columnONtable_name(sort_column);-- 复合索引注意顺序CREATEINDEXidx_status_timeONorders(status,create_time);-- 覆盖索引优化CREATEINDEXidx_coveringONorders(create_time,id,user_id,status);3. 查询优化技巧-- 避免 SELECT *SELECTid,user_id,statusFROMordersLIMIT1000000,10;-- 使用 FORCE INDEX 提示SELECT*FROMordersFORCEINDEX(PRIMARY)LIMIT1000000,10;-- 限制最大 offset-- 在应用层控制如不允许查询超过 10000 页4. 应用层优化# Python 示例限制最大分页MAX_OFFSET100000defget_orders(page1,page_size10):offset(page-1)*page_sizeifoffsetMAX_OFFSET:raiseValueError(分页超出限制)# 使用游标分页ifpage1:last_idget_last_id_of_page(page-1)querySELECT * FROM orders WHERE id %s ORDER BY id LIMIT %sreturndb.query(query,(last_id,page_size))else:querySELECT * FROM orders ORDER BY id LIMIT %sreturndb.query(query,(page_size,))5. 监控与告警-- 慢查询监控SETGLOBALslow_query_logON;SETGLOBALlong_query_time1;-- 分析慢查询-- 使用 pt-query-digest 或 MySQL 慢查询日志分析工具性能对比综合性能测试测试环境MySQL 8.0表数据量1000 万条测试查询SELECT * FROM orders ORDER BY id LIMIT offset, 10Offset原始 LIMIT子查询优化延迟关联游标分页00.01s0.01s0.01s0.01s1,0000.05s0.02s0.03s0.02s10,0000.3s0.03s0.05s0.02s100,0002.5s0.04s0.3s0.02s1,000,00025s0.05s2.5s0.02s5,000,000120s0.08s12s0.02s方案选择决策树是否需要支持跳页 ├─ 是 → 是否数据量 1000万 │ ├─ 是 → 考虑搜索引擎 │ └─ 否 → 子查询优化缓存 └─ 否 → 是否是无限滚动场景 ├─ 是 → 游标分页 └─ 否 → 延迟关联总结MySQL Limit 深度分页性能问题的核心在于 MySQL 必须扫描并读取 offset 之前的所有记录。针对不同场景有多种优化方案子查询优化适合主键连续的场景性能提升显著延迟关联通用性强适合各种查询条件游标分页性能最优但不支持跳页覆盖索引减少回表适合固定查询模式预计算/缓存适合查询模式固定的场景搜索引擎适合超大数据量和复杂搜索在实际应用中应根据具体场景选择合适的方案并结合索引优化、查询优化和缓存策略构建高性能的分页系统。