MariaDB 教程 / 在保留结果的前提下改进筛选、排序与分页
改进查询应保留目标记录,同时让筛选、排序和分页适合可用的访问路径。明确时间上下界与唯一的同值次序,比较 OFFSET 和键集分页,再用实际计划与代表性数据验证收益。
先证明结果等价,再讨论成本降低
如果新查询漏掉边界时间或同时间记录,即使更快,也不是等价改进。先比较返回主键与顺序,再检查计划、实际工作量与延迟。删除原本的筛选或匹配要求,是改变问题,不是单纯优化。
| 关注点 | 先作出的决定 | 示例 |
|---|---|---|
| 时间筛选 | 包含起点,排除下一期间起点 | 覆盖完整的一天 |
| 排序 | 相同值之间有明确次序 | ordered_at 与 order_id |
| 分页 | 导航与一致性需求 | 偏移量或末尾排序值 |
| 文本搜索 | 真正需要的匹配方式 | 前缀与任意位置包含并不相同 |
ORDER BY 与 LIMIT 教程介绍分页边界,EXPLAIN 对照教程说明怎样区分计划与实测。索引设计还应结合 复合键列顺序,而不是只改一段 SQL 后就推断成本已经下降。
准备按日期排序的独立样本与索引
以 course_user 连接学习服务器,核对 SELECT VERSION(), DATABASE(); 和 SHOW TABLES;。本节独立使用 c41_customers、c41_products、c41_orders、c41_items;已有同名对象时,先检查定义与记录,不要覆盖用途未知的数据。
下面是完整准备代码,应在 MariaDB 客户端执行。也可按照 SQL 文件与 SOURCE 教程保存 UTF-8 文件,用实际路径加载,不能直接当作操作系统命令运行。
USE shopcourse;
CREATE TABLE c41_customers (
customer_id INT NOT NULL PRIMARY KEY,
customer_name VARCHAR(50) NOT NULL,
city VARCHAR(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c41_products (
product_id INT NOT NULL PRIMARY KEY,
product_name VARCHAR(100) NOT NULL,
category VARCHAR(30) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT NOT NULL CHECK (stock >= 0)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c41_orders (
order_id INT NOT NULL PRIMARY KEY,
customer_id INT NOT NULL,
ordered_at DATETIME NOT NULL,
status VARCHAR(20) NOT NULL,
shipping_fee DECIMAL(10,2) NOT NULL DEFAULT 1000,
CONSTRAINT fk_c41_order_customer FOREIGN KEY (customer_id)
REFERENCES c41_customers(customer_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
CREATE TABLE c41_items (
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL CHECK (unit_price >= 0),
PRIMARY KEY (order_id, product_id),
CONSTRAINT fk_c41_item_order FOREIGN KEY (order_id)
REFERENCES c41_orders(order_id) ON DELETE RESTRICT,
CONSTRAINT fk_c41_item_product FOREIGN KEY (product_id)
REFERENCES c41_products(product_id) ON DELETE RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
INSERT INTO c41_customers VALUES
(1,'김민수','서울'),(2,'이서연','부산'),
(3,'박지우','서울'),(4,'최하늘','대전');
INSERT INTO c41_products VALUES
(1,'노트','문구',3500,20),(2,'볼펜','문구',1500,50),
(3,'머그컵','생활',8000,12),(4,'스탠드','생활',25000,5);
INSERT INTO c41_orders
(order_id,customer_id,ordered_at,status,shipping_fee) VALUES
(101,1,'2026-01-10 10:00:00','paid',1000),
(102,1,'2026-01-20 11:00:00','paid',1000),
(103,2,'2026-02-05 12:00:00','paid',1000),
(104,3,'2026-02-10 13:00:00','canceled',1000);
INSERT INTO c41_items VALUES
(101,1,2,3500),(101,2,3,1500),(102,3,1,8000),
(103,1,1,3500),(103,3,2,8000),(104,2,1,1500);
姓名、城市与商品字符串保留原始韩文。商品 1~4 分别是笔记本、圆珠笔、马克杯和台灯;客户 1、3 位于首尔,2 位于釜山,4 位于大田。请按编号核对结果,并保持 UTF-8 字面量一致。
CREATE INDEX idx_c41_time ON c41_orders(ordered_at,order_id);
四个订单可以按时间与编号比较。数据很少时,全扫描仍可能合理;这个样本用于验证行、顺序与边界,不是生产性能基准。保留原定义、索引和数据分布,便于比较时确认没有同时改变其他条件。
比较 DATE 条件与明确的时间范围
SELECT order_id FROM c41_orders WHERE DATE(ordered_at)='2026-01-10'
ORDER BY order_id;
SELECT order_id FROM c41_orders
WHERE ordered_at>='2026-01-10' AND ordered_at<'2026-01-11' ORDER BY order_id;
两条查询都返回订单 101。范围条件明确表示从 1 月 10 日零点开始,到 1 月 11 日零点之前,能够包含当天最后一秒的小数部分。不要把上界写成当天 23:59:59,就假定适合所有字段精度。
从 MariaDB 11.1 起,符合条件的 DATE 或 YEAR 比较可以被改写为可利用索引的范围;对应列需为索引中的 DATE、DATETIME 或 TIMESTAMP,比较形式也有规定。因此不能笼统地说“列外面有函数,就一定不能使用索引”。适用条件见官方 DATE 与 YEAR 范围优化说明。
明确上下界有助于跨版本阅读意图,但写成范围不自动证明更快。应比较真实 key 与访问范围。把日期先格式化为展示文本再比较,还可能混入字符串与日期类型的问题,需要分别检查。
下一页游标同时保存时间与编号
SELECT order_id,ordered_at FROM c41_orders
ORDER BY ordered_at DESC,order_id DESC LIMIT 2;
SELECT order_id,ordered_at FROM c41_orders
WHERE ordered_at<'2026-02-05 12:00:00'
OR(ordered_at='2026-02-05 12:00:00' AND order_id<103)
ORDER BY ordered_at DESC,order_id DESC LIMIT 2;
第一条降序页返回 104、103。上一页末尾是订单 103,其时间与编号一起成为下一页游标,第二条返回 102、101。因为按时间降序、编号降序,后续条件也使用相应的小于方向。
键集分页按当前位置继续,可以避免某些深偏移访问,但它与直接跳任意页码不是相同接口。并发插入、删除或排序字段更新仍可能改变列表;需要固定快照时,应另外设计一致性策略,不能只换成游标就声称数据已被冻结。
用下面的可回滚变化观察恰好同时间的页边界:
START TRANSACTION;
UPDATE c41_orders SET ordered_at='2026-02-05 12:00:00' WHERE order_id=104;
SELECT order_id,ordered_at FROM c41_orders
ORDER BY ordered_at DESC,order_id DESC LIMIT 1;
SELECT order_id,ordered_at FROM c41_orders
WHERE ordered_at<'2026-02-05 12:00:00'
OR (ordered_at='2026-02-05 12:00:00' AND order_id<104)
ORDER BY ordered_at DESC,order_id DESC LIMIT 1;
ROLLBACK;
第一条一行页返回 104,下一条应返回同时间的 103。若只保留“时间小于当前时间”的条件,103 会被跳过,直接到 102。编号条件不是多余装饰,而是完整排序规则的一部分。
ROLLBACK 后查询订单 104,确认时间恢复为原来的 2 月 10 日 13:00。对照时只改变游标完整性或一个时间值,不要同时改页长与排序方向,导致无法判断差异来源。静态样本中 OFFSET 2 与完整游标的下一页可相同,但这不是并发条件下的固定保证。
检查输出列、筛选比例与真实搜索模式
SELECT * 可能传输不需要的大文本,增加内存或回表读取成本。减少输出列与减少符合条件的行不同;改列清单之前,应确认调用方仍获得所需数据。计划可以帮助检查这些值是否已由覆盖访问提供。
普通 B-tree 对前缀 LIKE 与前导通配符的包含搜索,可能有不同访问机会。但把 %alpha% 改成 alpha%,会把“任意位置包含”变成“从开头匹配”;例如 xalpha 只符合前者。结果变少不是相同问题被加速。
先确定搜索模式与排序规则,再评估合适策略。索引设计的基本考虑见官方 按 SELECT 设计索引的指南,具体效果仍需由当前版本和数据验证。
记录比较条件并审查共享的访问需求
保留新旧 SQL、返回键与顺序、服务器版本、索引定义、行数和分布。把缓存影响与结构改进分开,不要为了让计划符合预想就强制索引。更改查询后,也应检查共用这条访问路径的排序与分页是否仍符合要求。
对日期边界,至少核对期间起点、最后一个可表示的小数秒与下一天零点。对分页,至少核对首末行和相同排序值;对文本,核对前缀与包含的反例。结果等价应有具体证据,而不是只看两个总数相同。
所有试验结束后,检查四表记录是否回到原值。只在确认本节表可以丢弃后,保存需要的内容并按下面子表优先顺序清理。DROP TABLE 会删除定义与全部数据,普通 ROLLBACK 无法恢复,应与事务实验分开。
DROP TABLE shopcourse.c41_items;
DROP TABLE shopcourse.c41_orders;
DROP TABLE shopcourse.c41_products;
DROP TABLE shopcourse.c41_customers;




