MySQL 8.0 多字段排序实战3种场景解析与性能影响分析在数据处理和分析过程中排序是最基础也是最关键的操作之一。MySQL 8.0 提供了强大的多字段排序功能能够满足各种复杂业务场景的需求。本文将深入探讨三种典型的多字段排序应用场景并通过实际案例展示如何优化排序性能。1. 多字段排序基础与核心原理MySQL 的ORDER BY子句支持同时对多个字段进行排序语法结构为SELECT column1, column2, ... FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;排序规则遵循以下原则字段顺序决定优先级越靠前的字段优先级越高当优先级高的字段值相同时才会使用后续字段排序默认排序方式为升序ASC可显式指定降序DESC排序字段类型影响字段类型排序特点性能考虑整数类型直接比较数值大小排序效率最高字符串类型按字符编码顺序比较较长字符串影响性能日期时间类型按时间先后顺序比较通常性能良好浮点数类型需考虑精度问题比较运算稍慢枚举/集合类型按定义顺序或数值比较内部转换为数值比较在实际应用中理解这些基础原理是构建高效排序查询的前提。下面我们将通过具体场景来展示多字段排序的实际应用。2. 典型业务场景与实现方案2.1 分页列表排序电商商品展示电商平台通常需要展示按多种条件排序的商品列表。假设我们有一个商品表productsCREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(100), category_id INT, price DECIMAL(10,2), sales_volume INT, rating DECIMAL(3,2), created_at TIMESTAMP, INDEX idx_category_price (category_id, price), INDEX idx_sales (sales_volume), INDEX idx_rating (rating) );场景需求默认按销量降序、评分降序、价格升序排列支持按价格、上架时间等字段排序需要高效分页查询实现方案-- 基础排序查询 SELECT id, name, price, sales_volume, rating FROM products WHERE category_id 5 -- 某个分类 ORDER BY sales_volume DESC, rating DESC, price ASC LIMIT 0, 20; -- 按价格排序价格相同时按销量排序 SELECT id, name, price, sales_volume FROM products WHERE category_id 5 ORDER BY price ASC, sales_volume DESC LIMIT 0, 20; -- 按上架时间排序 SELECT id, name, created_at FROM products WHERE category_id 5 ORDER BY created_at DESC, sales_volume DESC LIMIT 0, 20;性能优化建议为常用排序字段组合创建复合索引如(category_id, sales_volume, rating, price)对于分页查询避免使用OFFSET过大可采用记住上一页最后一项的方式优化考虑使用覆盖索引减少回表操作2.2 排行榜系统游戏玩家排名游戏玩家排行榜通常需要综合多个指标进行排序。考虑以下玩家表playersCREATE TABLE players ( player_id INT PRIMARY KEY, username VARCHAR(50), level INT, experience BIGINT, pvp_rating INT, last_active DATE, guild_id INT, INDEX idx_guild_level (guild_id, level), INDEX idx_pvp (pvp_rating), INDEX idx_active (last_active) );场景需求全服等级排行榜等级降序经验值降序公会内部排行榜相同规则PVP竞技场排名PVP积分降序最后活跃时间降序实现方案-- 全服等级排行榜 SELECT player_id, username, level, experience FROM players ORDER BY level DESC, experience DESC LIMIT 100; -- 某个公会内部排行榜 SELECT player_id, username, level, experience FROM players WHERE guild_id 1024 ORDER BY level DESC, experience DESC LIMIT 50; -- PVP竞技场排名 SELECT player_id, username, pvp_rating, last_active FROM players ORDER BY pvp_rating DESC, last_active DESC LIMIT 200;特殊处理技巧对于大型排行榜考虑使用定时任务预计算并缓存结果使用CASE WHEN实现条件排序如VIP玩家优先显示SELECT player_id, username, level, is_vip FROM players ORDER BY is_vip DESC, level DESC LIMIT 100;2.3 报表数据分析销售业绩统计数据分析报表通常需要对聚合结果进行多维度排序。假设有订单表orders和员工表employeesCREATE TABLE orders ( order_id INT PRIMARY KEY, employee_id INT, order_date DATE, amount DECIMAL(12,2), region VARCHAR(50), INDEX idx_employee_date (employee_id, order_date), INDEX idx_region_date (region, order_date) ); CREATE TABLE employees ( employee_id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50) );场景需求按区域和月份统计销售额并按销售额降序排列员工销售业绩排名按部门和销售额排序多级分组排序报表实现方案-- 区域月度销售报表 SELECT region, DATE_FORMAT(order_date, %Y-%m) AS month, SUM(amount) AS total_sales FROM orders GROUP BY region, DATE_FORMAT(order_date, %Y-%m) ORDER BY region ASC, total_sales DESC; -- 部门员工销售排名 SELECT e.department, e.name, SUM(o.amount) AS total_sales, COUNT(o.order_id) AS order_count FROM employees e JOIN orders o ON e.employee_id o.employee_id WHERE o.order_date BETWEEN 2023-01-01 AND 2023-12-31 GROUP BY e.department, e.name ORDER BY e.department ASC, total_sales DESC; -- 带有多级排序的复杂报表 SELECT e.department, o.region, DATE_FORMAT(o.order_date, %Y-%m) AS month, SUM(o.amount) AS total_sales, COUNT(DISTINCT o.employee_id) AS employee_count FROM orders o JOIN employees e ON o.employee_id e.employee_id GROUP BY e.department, o.region, DATE_FORMAT(o.order_date, %Y-%m) ORDER BY e.department ASC, month ASC, total_sales DESC;报表优化技巧为报表查询创建专用的汇总表考虑使用物化视图MySQL 8.0支持对大时间范围的报表使用分区表提高查询效率在排序前尽量通过WHERE条件减少数据集3. 性能影响分析与优化策略多字段排序可能对查询性能产生重大影响特别是在处理大数据集时。本节将分析排序性能的关键因素并提供优化建议。3.1 索引对排序性能的影响索引使用原则索引最左前缀原则同样适用于排序排序字段顺序应与索引字段顺序一致混合ASC和DESC排序时需要考虑索引方向示例分析-- 表结构 CREATE TABLE user_actions ( id INT PRIMARY KEY, user_id INT, action_type VARCHAR(20), action_time DATETIME, points_earned INT, INDEX idx_user_action (user_id, action_time DESC), INDEX idx_action_points (action_type, points_earned DESC) ); -- 有效使用索引的排序 EXPLAIN SELECT * FROM user_actions WHERE user_id 1001 ORDER BY action_time DESC; /* 可以使用idx_user_action索引 */ -- 无法使用索引的排序 EXPLAIN SELECT * FROM user_actions WHERE user_id 1001 ORDER BY action_time ASC, points_earned DESC; /* 只能部分使用索引points_earned排序需要filesort */索引设计决策表排序场景推荐索引注意事项单字段排序(排序字段)简单高效多字段等值过滤排序(过滤字段, 排序字段)过滤字段在前多字段混合排序(排序字段1, 排序字段2...)注意字段顺序和方向过滤多字段排序(过滤字段, 排序字段1, 排序字段2)确保过滤条件是等值查询多表JOIN后排序驱动表的排序字段索引考虑JOIN算法的影响3.2 排序算法与性能考量MySQL 使用两种排序算法内存排序当排序数据量较小时在内存中完成文件排序(filesort)当数据量大时使用磁盘临时文件影响因素sort_buffer_size排序缓冲区大小max_length_for_sort_data决定使用哪种算法查询涉及的字段总长度性能优化方法只选择必要的列避免SELECT *增加sort_buffer_size建议8M-16M对于大文本字段排序考虑使用前缀索引-- 查看排序性能状态执行后检查 SHOW STATUS LIKE Sort%; -- 优化示例减少排序字段长度 SELECT user_id, LEFT(comment, 100) AS short_comment, created_at FROM user_comments ORDER BY short_comment, created_at DESC;3.3 复杂查询中的排序优化当查询包含JOIN、GROUP BY等复杂操作时排序性能优化更具挑战性。优化策略子查询优化先在子查询中完成排序再JOIN-- 不推荐 SELECT e.name, d.department_name, e.salary FROM employees e JOIN departments d ON e.dept_id d.id ORDER BY e.salary DESC LIMIT 10; -- 推荐先排序再JOIN SELECT e.name, d.department_name, e.salary FROM ( SELECT name, dept_id, salary FROM employees ORDER BY salary DESC LIMIT 10 ) e JOIN departments d ON e.dept_id d.id;使用派生表延迟排序-- 延迟排序到最终结果 SELECT * FROM ( SELECT e.id, e.name, COUNT(o.id) AS order_count FROM employees e LEFT JOIN orders o ON e.id o.employee_id GROUP BY e.id, e.name ) AS emp_stats ORDER BY order_count DESC LIMIT 100;利用UNION优化混合排序-- 合并不同条件的排序结果 (SELECT id, title, news AS type, publish_time AS sort_time FROM news ORDER BY publish_time DESC LIMIT 10) UNION ALL (SELECT id, title, blog AS type, created_at AS sort_time FROM blogs ORDER BY created_at DESC LIMIT 10) ORDER BY sort_time DESC LIMIT 15;4. 高级技巧与实战案例4.1 动态排序实现在实际应用中经常需要根据用户选择动态改变排序方式。以下是几种实现方式1. 应用层动态SQL# Python示例 def get_products(sort_fieldprice, sort_orderASC): field_map { price: price, sales: sales_volume, rating: rating } order_map {ASC: ASC, DESC: DESC} sql f SELECT id, name, price, sales_volume, rating FROM products ORDER BY {field_map[sort_field]} {order_map[sort_order]} LIMIT 100 # 执行查询...2. 使用CASE表达式实现动态排序-- 单一字段动态排序 SELECT id, name, price, sales_volume FROM products ORDER BY CASE WHEN :sort_param price_asc THEN price END ASC, CASE WHEN :sort_param price_desc THEN price END DESC, CASE WHEN :sort_param sales_asc THEN sales_volume END ASC, CASE WHEN :sort_param sales_desc THEN sales_volume END DESC LIMIT 100; -- 多字段动态排序更灵活 SELECT id, name, price, sales_volume, rating FROM products ORDER BY CASE WHEN :sort_by_price ! ignore THEN price END CASE WHEN :sort_by_price asc THEN ASC ELSE DESC END, CASE WHEN :sort_by_sales ! ignore THEN sales_volume END CASE WHEN :sort_by_sales asc THEN ASC ELSE DESC END LIMIT 100;4.2 特殊排序需求处理1. 自定义排序规则-- 按特定顺序排序非字母数字顺序 SELECT product_id, product_name, category FROM products ORDER BY CASE category WHEN Electronics THEN 1 WHEN Clothing THEN 2 WHEN Books THEN 3 ELSE 4 END, product_name;2. NULL值处理-- 将NULL值排在最后默认ASC时NULL最先DESC时NULL最后 SELECT id, name, commission FROM salespeople ORDER BY CASE WHEN commission IS NULL THEN 1 ELSE 0 END, commission DESC; -- MySQL 8.0 专用语法 SELECT id, name, commission FROM salespeople ORDER BY commission DESC NULLS LAST;3. 按计算结果排序-- 按计算字段排序 SELECT product_id, name, price, discount, price * (1 - discount) AS final_price FROM products ORDER BY final_price DESC; -- 按字符串函数结果排序 SELECT user_id, username, last_login FROM users ORDER BY LENGTH(username), last_login DESC;4.3 分页与排序结合的最佳实践分页查询结合排序时常见性能问题是大偏移量的效率低下。以下是优化方案1. 基于键的分页推荐-- 第一页 SELECT id, name, created_at FROM products ORDER BY created_at DESC, id DESC LIMIT 20; -- 后续页面记住上一页最后一条记录的created_at和id SELECT id, name, created_at FROM products WHERE created_at 2023-05-20 15:30:00 OR (created_at 2023-05-20 15:30:00 AND id 10567) ORDER BY created_at DESC, id DESC LIMIT 20;2. 使用覆盖索引优化-- 普通分页性能随offset增大而下降 SELECT id, name, price FROM products ORDER BY price DESC LIMIT 10000, 20; -- 优化版本先通过覆盖索引获取主键 SELECT p.id, p.name, p.price FROM products p JOIN ( SELECT id FROM products ORDER BY price DESC LIMIT 10000, 20 ) AS tmp ON p.id tmp.id ORDER BY p.price DESC;3. 预计算分页键对于极端大规模数据考虑预先计算并存储分页定位点-- 分页定位表 CREATE TABLE product_page_anchors ( page_num INT PRIMARY KEY, min_price DECIMAL(10,2), max_price DECIMAL(10,2), min_id INT, max_id INT ); -- 查询时使用 SELECT id, name, price FROM products WHERE price (SELECT max_price FROM product_page_anchors WHERE page_num 50) AND price (SELECT min_price FROM product_page_anchors WHERE page_num 50) ORDER BY price DESC, id DESC LIMIT 20;
MySQL 8.0 多字段排序实战3种场景解析与性能影响分析在数据处理和分析过程中排序是最基础也是最关键的操作之一。MySQL 8.0 提供了强大的多字段排序功能能够满足各种复杂业务场景的需求。本文将深入探讨三种典型的多字段排序应用场景并通过实际案例展示如何优化排序性能。1. 多字段排序基础与核心原理MySQL 的ORDER BY子句支持同时对多个字段进行排序语法结构为SELECT column1, column2, ... FROM table_name ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;排序规则遵循以下原则字段顺序决定优先级越靠前的字段优先级越高当优先级高的字段值相同时才会使用后续字段排序默认排序方式为升序ASC可显式指定降序DESC排序字段类型影响字段类型排序特点性能考虑整数类型直接比较数值大小排序效率最高字符串类型按字符编码顺序比较较长字符串影响性能日期时间类型按时间先后顺序比较通常性能良好浮点数类型需考虑精度问题比较运算稍慢枚举/集合类型按定义顺序或数值比较内部转换为数值比较在实际应用中理解这些基础原理是构建高效排序查询的前提。下面我们将通过具体场景来展示多字段排序的实际应用。2. 典型业务场景与实现方案2.1 分页列表排序电商商品展示电商平台通常需要展示按多种条件排序的商品列表。假设我们有一个商品表productsCREATE TABLE products ( id INT PRIMARY KEY, name VARCHAR(100), category_id INT, price DECIMAL(10,2), sales_volume INT, rating DECIMAL(3,2), created_at TIMESTAMP, INDEX idx_category_price (category_id, price), INDEX idx_sales (sales_volume), INDEX idx_rating (rating) );场景需求默认按销量降序、评分降序、价格升序排列支持按价格、上架时间等字段排序需要高效分页查询实现方案-- 基础排序查询 SELECT id, name, price, sales_volume, rating FROM products WHERE category_id 5 -- 某个分类 ORDER BY sales_volume DESC, rating DESC, price ASC LIMIT 0, 20; -- 按价格排序价格相同时按销量排序 SELECT id, name, price, sales_volume FROM products WHERE category_id 5 ORDER BY price ASC, sales_volume DESC LIMIT 0, 20; -- 按上架时间排序 SELECT id, name, created_at FROM products WHERE category_id 5 ORDER BY created_at DESC, sales_volume DESC LIMIT 0, 20;性能优化建议为常用排序字段组合创建复合索引如(category_id, sales_volume, rating, price)对于分页查询避免使用OFFSET过大可采用记住上一页最后一项的方式优化考虑使用覆盖索引减少回表操作2.2 排行榜系统游戏玩家排名游戏玩家排行榜通常需要综合多个指标进行排序。考虑以下玩家表playersCREATE TABLE players ( player_id INT PRIMARY KEY, username VARCHAR(50), level INT, experience BIGINT, pvp_rating INT, last_active DATE, guild_id INT, INDEX idx_guild_level (guild_id, level), INDEX idx_pvp (pvp_rating), INDEX idx_active (last_active) );场景需求全服等级排行榜等级降序经验值降序公会内部排行榜相同规则PVP竞技场排名PVP积分降序最后活跃时间降序实现方案-- 全服等级排行榜 SELECT player_id, username, level, experience FROM players ORDER BY level DESC, experience DESC LIMIT 100; -- 某个公会内部排行榜 SELECT player_id, username, level, experience FROM players WHERE guild_id 1024 ORDER BY level DESC, experience DESC LIMIT 50; -- PVP竞技场排名 SELECT player_id, username, pvp_rating, last_active FROM players ORDER BY pvp_rating DESC, last_active DESC LIMIT 200;特殊处理技巧对于大型排行榜考虑使用定时任务预计算并缓存结果使用CASE WHEN实现条件排序如VIP玩家优先显示SELECT player_id, username, level, is_vip FROM players ORDER BY is_vip DESC, level DESC LIMIT 100;2.3 报表数据分析销售业绩统计数据分析报表通常需要对聚合结果进行多维度排序。假设有订单表orders和员工表employeesCREATE TABLE orders ( order_id INT PRIMARY KEY, employee_id INT, order_date DATE, amount DECIMAL(12,2), region VARCHAR(50), INDEX idx_employee_date (employee_id, order_date), INDEX idx_region_date (region, order_date) ); CREATE TABLE employees ( employee_id INT PRIMARY KEY, name VARCHAR(50), department VARCHAR(50) );场景需求按区域和月份统计销售额并按销售额降序排列员工销售业绩排名按部门和销售额排序多级分组排序报表实现方案-- 区域月度销售报表 SELECT region, DATE_FORMAT(order_date, %Y-%m) AS month, SUM(amount) AS total_sales FROM orders GROUP BY region, DATE_FORMAT(order_date, %Y-%m) ORDER BY region ASC, total_sales DESC; -- 部门员工销售排名 SELECT e.department, e.name, SUM(o.amount) AS total_sales, COUNT(o.order_id) AS order_count FROM employees e JOIN orders o ON e.employee_id o.employee_id WHERE o.order_date BETWEEN 2023-01-01 AND 2023-12-31 GROUP BY e.department, e.name ORDER BY e.department ASC, total_sales DESC; -- 带有多级排序的复杂报表 SELECT e.department, o.region, DATE_FORMAT(o.order_date, %Y-%m) AS month, SUM(o.amount) AS total_sales, COUNT(DISTINCT o.employee_id) AS employee_count FROM orders o JOIN employees e ON o.employee_id e.employee_id GROUP BY e.department, o.region, DATE_FORMAT(o.order_date, %Y-%m) ORDER BY e.department ASC, month ASC, total_sales DESC;报表优化技巧为报表查询创建专用的汇总表考虑使用物化视图MySQL 8.0支持对大时间范围的报表使用分区表提高查询效率在排序前尽量通过WHERE条件减少数据集3. 性能影响分析与优化策略多字段排序可能对查询性能产生重大影响特别是在处理大数据集时。本节将分析排序性能的关键因素并提供优化建议。3.1 索引对排序性能的影响索引使用原则索引最左前缀原则同样适用于排序排序字段顺序应与索引字段顺序一致混合ASC和DESC排序时需要考虑索引方向示例分析-- 表结构 CREATE TABLE user_actions ( id INT PRIMARY KEY, user_id INT, action_type VARCHAR(20), action_time DATETIME, points_earned INT, INDEX idx_user_action (user_id, action_time DESC), INDEX idx_action_points (action_type, points_earned DESC) ); -- 有效使用索引的排序 EXPLAIN SELECT * FROM user_actions WHERE user_id 1001 ORDER BY action_time DESC; /* 可以使用idx_user_action索引 */ -- 无法使用索引的排序 EXPLAIN SELECT * FROM user_actions WHERE user_id 1001 ORDER BY action_time ASC, points_earned DESC; /* 只能部分使用索引points_earned排序需要filesort */索引设计决策表排序场景推荐索引注意事项单字段排序(排序字段)简单高效多字段等值过滤排序(过滤字段, 排序字段)过滤字段在前多字段混合排序(排序字段1, 排序字段2...)注意字段顺序和方向过滤多字段排序(过滤字段, 排序字段1, 排序字段2)确保过滤条件是等值查询多表JOIN后排序驱动表的排序字段索引考虑JOIN算法的影响3.2 排序算法与性能考量MySQL 使用两种排序算法内存排序当排序数据量较小时在内存中完成文件排序(filesort)当数据量大时使用磁盘临时文件影响因素sort_buffer_size排序缓冲区大小max_length_for_sort_data决定使用哪种算法查询涉及的字段总长度性能优化方法只选择必要的列避免SELECT *增加sort_buffer_size建议8M-16M对于大文本字段排序考虑使用前缀索引-- 查看排序性能状态执行后检查 SHOW STATUS LIKE Sort%; -- 优化示例减少排序字段长度 SELECT user_id, LEFT(comment, 100) AS short_comment, created_at FROM user_comments ORDER BY short_comment, created_at DESC;3.3 复杂查询中的排序优化当查询包含JOIN、GROUP BY等复杂操作时排序性能优化更具挑战性。优化策略子查询优化先在子查询中完成排序再JOIN-- 不推荐 SELECT e.name, d.department_name, e.salary FROM employees e JOIN departments d ON e.dept_id d.id ORDER BY e.salary DESC LIMIT 10; -- 推荐先排序再JOIN SELECT e.name, d.department_name, e.salary FROM ( SELECT name, dept_id, salary FROM employees ORDER BY salary DESC LIMIT 10 ) e JOIN departments d ON e.dept_id d.id;使用派生表延迟排序-- 延迟排序到最终结果 SELECT * FROM ( SELECT e.id, e.name, COUNT(o.id) AS order_count FROM employees e LEFT JOIN orders o ON e.id o.employee_id GROUP BY e.id, e.name ) AS emp_stats ORDER BY order_count DESC LIMIT 100;利用UNION优化混合排序-- 合并不同条件的排序结果 (SELECT id, title, news AS type, publish_time AS sort_time FROM news ORDER BY publish_time DESC LIMIT 10) UNION ALL (SELECT id, title, blog AS type, created_at AS sort_time FROM blogs ORDER BY created_at DESC LIMIT 10) ORDER BY sort_time DESC LIMIT 15;4. 高级技巧与实战案例4.1 动态排序实现在实际应用中经常需要根据用户选择动态改变排序方式。以下是几种实现方式1. 应用层动态SQL# Python示例 def get_products(sort_fieldprice, sort_orderASC): field_map { price: price, sales: sales_volume, rating: rating } order_map {ASC: ASC, DESC: DESC} sql f SELECT id, name, price, sales_volume, rating FROM products ORDER BY {field_map[sort_field]} {order_map[sort_order]} LIMIT 100 # 执行查询...2. 使用CASE表达式实现动态排序-- 单一字段动态排序 SELECT id, name, price, sales_volume FROM products ORDER BY CASE WHEN :sort_param price_asc THEN price END ASC, CASE WHEN :sort_param price_desc THEN price END DESC, CASE WHEN :sort_param sales_asc THEN sales_volume END ASC, CASE WHEN :sort_param sales_desc THEN sales_volume END DESC LIMIT 100; -- 多字段动态排序更灵活 SELECT id, name, price, sales_volume, rating FROM products ORDER BY CASE WHEN :sort_by_price ! ignore THEN price END CASE WHEN :sort_by_price asc THEN ASC ELSE DESC END, CASE WHEN :sort_by_sales ! ignore THEN sales_volume END CASE WHEN :sort_by_sales asc THEN ASC ELSE DESC END LIMIT 100;4.2 特殊排序需求处理1. 自定义排序规则-- 按特定顺序排序非字母数字顺序 SELECT product_id, product_name, category FROM products ORDER BY CASE category WHEN Electronics THEN 1 WHEN Clothing THEN 2 WHEN Books THEN 3 ELSE 4 END, product_name;2. NULL值处理-- 将NULL值排在最后默认ASC时NULL最先DESC时NULL最后 SELECT id, name, commission FROM salespeople ORDER BY CASE WHEN commission IS NULL THEN 1 ELSE 0 END, commission DESC; -- MySQL 8.0 专用语法 SELECT id, name, commission FROM salespeople ORDER BY commission DESC NULLS LAST;3. 按计算结果排序-- 按计算字段排序 SELECT product_id, name, price, discount, price * (1 - discount) AS final_price FROM products ORDER BY final_price DESC; -- 按字符串函数结果排序 SELECT user_id, username, last_login FROM users ORDER BY LENGTH(username), last_login DESC;4.3 分页与排序结合的最佳实践分页查询结合排序时常见性能问题是大偏移量的效率低下。以下是优化方案1. 基于键的分页推荐-- 第一页 SELECT id, name, created_at FROM products ORDER BY created_at DESC, id DESC LIMIT 20; -- 后续页面记住上一页最后一条记录的created_at和id SELECT id, name, created_at FROM products WHERE created_at 2023-05-20 15:30:00 OR (created_at 2023-05-20 15:30:00 AND id 10567) ORDER BY created_at DESC, id DESC LIMIT 20;2. 使用覆盖索引优化-- 普通分页性能随offset增大而下降 SELECT id, name, price FROM products ORDER BY price DESC LIMIT 10000, 20; -- 优化版本先通过覆盖索引获取主键 SELECT p.id, p.name, p.price FROM products p JOIN ( SELECT id FROM products ORDER BY price DESC LIMIT 10000, 20 ) AS tmp ON p.id tmp.id ORDER BY p.price DESC;3. 预计算分页键对于极端大规模数据考虑预先计算并存储分页定位点-- 分页定位表 CREATE TABLE product_page_anchors ( page_num INT PRIMARY KEY, min_price DECIMAL(10,2), max_price DECIMAL(10,2), min_id INT, max_id INT ); -- 查询时使用 SELECT id, name, price FROM products WHERE price (SELECT max_price FROM product_page_anchors WHERE page_num 50) AND price (SELECT min_price FROM product_page_anchors WHERE page_num 50) ORDER BY price DESC, id DESC LIMIT 20;
![[C++]内存管理：串顺序存储的内存回收](http://pic.xiahunao.cn/yaotu/[C++]内存管理：串顺序存储的内存回收)
