第五章：数据查询基础 (DQL) —— SQL 的核心力量

核心摘要：
DQL (Data Query Language) 是 SQL 语言中最复杂、最灵活，也是对性能影响最大的部分。
本章将带你从“会写 SELECT”进化到“理解 SQL 执行原理”。我们将深入探讨SELECT *的性能隐患、NULL值的逻辑陷阱、模糊查询的索引失效问题，以及深分页 (Deep Paging)的性能杀手与优化方案。

环境准备：
请确保已执行前几章的建表语句。为了方便演示查询，我们将先初始化一批更丰富的测试数据。

---

5.0 数据准备 (Data Seeding)

为了让查询结果更有说服力，我们需要往products表里多塞点数据。

USEshop_biz;-- 清空表（慎用，仅限测试环境）TRUNCATETABLEproducts;-- 插入多条不同类型的商品数据INSERTINTOproducts(sku_code,product_name,price,stock_qty,status,created_at)VALUES('IPHONE13','iPhone 13 128G 黑色',4999.00,100,1,'2023-01-01 10:00:00'),('IPHONE14','iPhone 14 256G 紫色',5999.00,50,1,'2023-02-01 12:00:00'),('XM_13','Xiaomi 13 Pro',3999.00,200,1,'2023-03-01 09:00:00'),('XM_REDMI','Redmi K60',1999.00,500,1,'2023-03-15 14:00:00'),('DELL_XPS','Dell XPS 13 Laptop',8999.00,20,1,'2023-04-01 08:00:00'),('LOGI_M590','Logitech M590 Mouse',159.00,1000,1,'2023-05-01 11:00:00'),('KEY_K3','Keychron K3 Keyboard',499.00,300,0,'2023-06-01 16:00:00'),-- status=0 下架('TEST_NULL','残次品数据',0.00,0,0,NULL);-- 故意留 NULL 用于测试

---

5.1 基础查询 (Basic Query)

5.1.1SELECT *的原罪

-- 偷懒写法：查询所有字段SELECT*FROMproducts;

为什么禁止在生产环境代码中使用SELECT *？

1. 网络 I/O 浪费：如果表里有TEXT或JSON大字段，即使你不需要，它们也会被传输，消耗带宽。
2. 覆盖索引 (Covering Index) 失效：如果只查SELECT id, price FROM products，MySQL 可以直接从索引树上拿数据，不需要回表（回表意味着随机磁盘 I/O）。写了*就必须回表。
3. Schema 变更风险：如果应用层代码依赖字段顺序（如row[2]），数据库加减字段会导致代码崩溃。

正确写法：

-- 按需查询，指定列名SELECTsku_code,product_name,priceFROMproducts;

5.1.2 别名 (Alias) 的艺术

别名不仅仅是为了少打字，更是为了代码的可读性和多表关联时的消歧义。

SELECTproduct_nameASname,-- 列别名，前端展示可能直接用这个 keyprice*stock_qtyAStotal_value-- 计算字段，必须给别名，否则结果集列名会很乱FROMproductsASp;-- 表别名，常用于 Join 查询

5.1.3 去重 (DISTINCT) 的代价

-- 查询所有的商品价格档位SELECTDISTINCTpriceFROMproducts;

底层原理：
DISTINCT会让 MySQL 对结果集进行排序或建立临时表来去重。在大数据量下，这是一个非常消耗 CPU 和内存（Sort Buffer）的操作。
优化建议：如果业务允许，尽量在应用层（Java/Python）去重，或者通过GROUP BY优化。

---

5.2 条件查询 (WHERE) —— 数据的过滤器

5.2.1 比较与逻辑运算

SELECT*FROMproductsWHEREprice>=2000-- 价格大于等于 2000ANDstatus=1-- 且 状态为上架ANDstock_qty<100;-- 且 库存紧张

5.2.2NULL的陷阱 —— 必须掌握的知识点

在 SQL 中，NULL代表“未知”。任何与 NULL 进行的算术比较（=, !=, >, <）结果都是 NULL（即 False）。

-- 错误写法：这查不到 created_at 为 NULL 的记录！SELECT*FROMproductsWHEREcreated_at=NULL;-- 正确写法：使用 IS NULL / IS NOT NULLSELECT*FROMproductsWHEREcreated_atISNULL;

实战思考：
这就是为什么我们在第三章建表时，强烈建议字段NOT NULL DEFAULT ''。
NULL不仅让代码逻辑变复杂（必须特判），还会影响索引效率。

5.2.3 模糊查询 (LIKE) 与索引失效

-- 1. 前缀匹配（推荐）：可以使用索引-- 查找所有以 'iPhone' 开头的商品SELECT*FROMproductsWHEREproduct_nameLIKE'iPhone%';-- 2. 后缀匹配/全模糊（性能杀手）：索引失效！-- 查找名字里包含 'Pro' 的商品SELECT*FROMproductsWHEREproduct_nameLIKE'%Pro%';

原理：
B+ 树索引是按照字母顺序排列的。

• iPhone%：我知道去索引树的哪里找（找 I 开头的）。
• %Pro%：我怎么知道它在开头、中间还是结尾？只能全表扫描 (Full Table Scan)。
• 注：对于 5.7+ 版本，全模糊搜索建议使用 Full Text Index（全文索引）或 ElasticSearch。

---

5.3 排序 (ORDER BY) 与 SQL 执行顺序

5.3.1 多字段排序

-- 先按状态排序（上架的在前），再按价格从高到低排SELECTproduct_name,price,statusFROMproductsORDERBYstatusDESC,priceDESC;

5.3.2 SQL 执行顺序之谜

初学者常遇到的报错：

-- 错误：Unknown column 'total' in 'where clause'SELECTprice*stock_qtyAStotalFROMproductsWHEREtotal>10000;

为什么报错？
这涉及 SQL 的逻辑执行顺序：

1. FROM(找到表)
2. WHERE(过滤行) <-- 此时total还没算出来呢！
3. GROUP BY
4. HAVING
5. SELECT(计算列、生成别名) <-- 这里才生成total
6. ORDER BY(排序) <-- 所以 ORDER BY 可以用别名
7. LIMIT

修正：

-- 写法 1：WHERE 里重复计算逻辑SELECTprice*stock_qtyAStotalFROMproductsWHEREprice*stock_qty>10000;-- 写法 2 (高级)：用 HAVING (通常配合 GROUP BY，但此处也能用，但不推荐)-- 写法 3 (推荐)：嵌套子查询 (Derived Table)SELECT*FROM(SELECTprice*stock_qtyAStotalFROMproducts)AStWHEREt.total>10000;

---

5.4 分页查询 (LIMIT) —— 性能优化的深水区

5.4.1 基础分页

MySQL 的分页语法很简单：LIMIT offset, count

-- 第 1 页，每页 2 条SELECT*FROMproductsLIMIT0,2;-- 第 2 页，每页 2 条 (跳过前2条，取2条)SELECT*FROMproductsLIMIT2,2;-- 第 3 页SELECT*FROMproductsLIMIT4,2;

5.4.2 深分页 (Deep Paging) 性能问题

当你的数据量达到百万级时，翻到第 100 万页会发生什么？

-- 极其慢！可能需要几秒甚至几十秒SELECT*FROMproductsLIMIT1000000,10;

原理：
MySQL 执行LIMIT 1000000, 10的逻辑是：

1. 先扫描读取 1,000,010 行数据。
2. 抛弃前 1,000,000 行。
3. 返回最后 10 行。
   这意味着大量的磁盘 I/O 和 CPU 浪费。

5.4.3 优化方案：延迟关联 (Deferred Join)

我们利用覆盖索引先只查主键 ID（速度快），再通过 ID 回表查完整数据。

SELECTp.*FROMproducts pINNERJOIN(-- 子查询只查主键，利用覆盖索引，不用回表，速度极快SELECTproduct_idFROMproductsLIMIT1000000,10)AStmpONp.product_id=tmp.product_id;

或者，如果 ID 是连续且递增的（且无 WHERE 条件），可以使用游标法：

-- 记录上一页最后一条的 ID (last_id)SELECT*FROMproductsWHEREproduct_id>1000000LIMIT10;

这种方式性能最好（O(1)），但只适用于特定场景。

---

5.5 综合案例：构建商品搜索列表

我们将模拟一个电商后台的商品搜索接口 SQL。

需求：

1. 搜索名称包含 “iPhone” 的商品。
2. 状态必须是上架 (status=1)。
3. 价格在 4000 到 8000 之间。
4. 按创建时间倒序排列。
5. 查询第 1 页，每页 10 条。

SELECTproduct_id,sku_code,product_name,price,created_atFROMproductsWHEREstatus=1ANDpriceBETWEEN4000AND8000ANDproduct_nameLIKE'iPhone%'-- 注意：把模糊匹配放在最后，或者利用最左前缀ORDERBYcreated_atDESCLIMIT0,10;

索引设计思考（预告）：
为了让这条 SQL 跑得快，我们可能需要创建一个联合索引：
INDEX idx_status_price_created (status, price, created_at)
（关于索引的详细设计，我们将在第八章深入探讨）

通过本章，你已经掌握了从单表获取数据的核心能力。但真实世界的数据往往分散在多张表中，如何把它们拼起来？下一章，我们将解锁 SQL 最强大的功能：多表连接 (Joins) 与 高级查询。