核心内容摘要
17c在线视频：解锁无限精彩，点亮你的数字生活
核心摘要DQL (Data Query Language) 是 SQL 语言中最复杂、最灵活也是对性能影响最大的部分。
本章将带你从“会写 SELECT”进化到“理解 SQL 执行原理”。
我们将深入探讨SELECT *的性能隐患、NULL值的逻辑陷阱、模糊查询的索引失效问题以及深分页 (Deep Paging)的性能杀手与优化方案。
环境准备请确保已执行前几章的建表语句。
为了方便演示查询我们将先初始化一批更丰富的测试数据。
0 数据准备 (Data Seeding)为了让查询结果更有说服力我们需要往products表里多塞点数据。
USEshop_biz;-- 清空表慎用仅限测试环境TRUNCATETABLEproducts;-- 插入多条不同类型的商品数据INSERTINTOproducts(sku_code,product_name,price,stock_qty,status,created_at)VALUES(IPHONE13,iPhone 13 128G 黑色,
4
00,100,1,
10:00:
,(IPHONE14,iPhone 14 256G 紫色,
5
00,50,1,
12:00:
,(XM_13,Xiaomi 13 Pro,
3
00,200,1,
09:00:
,(XM_REDMI,Redmi K60,
1
00,500,1,
14:00:
,(DELL_XPS,Dell XPS 13 Laptop,
8
00,20,1,
08:00:
,(LOGI_M590,Logitech M590 Mouse,
1
00,1000,1,
11:00:
,(KEY_K3,Keychron K3 Keyboard,
4
00,300,0,
16:00:
,-- status0 下架(TEST_NULL,残次品数据,
00,0,0,NULL);-- 故意留 NULL 用于测试
1 基础查询 (Basic Query)
5.
1SELECT *的原罪-- 偷懒写法查询所有字段SELECT*FROMproducts;为什么禁止在生产环境代码中使用SELECT *网络 I/O 浪费如果表里有TEXT或JSON大字段即使你不需要它们也会被传输消耗带宽。
覆盖索引 (Covering Index) 失效如果只查SELECT id, price FROM productsMySQL 可以直接从索引树上拿数据不需要回表回表意味着随机磁盘 I/O。
写了*就必须回表。
Schema 变更风险如果应用层代码依赖字段顺序如row[2]数据库加减字段会导致代码崩溃。
正确写法-- 按需查询指定列名SELECTsku_code,product_name,priceFROMproducts;
5.
2 别名 (Alias) 的艺术别名不仅仅是为了少打字更是为了代码的可读性和多表关联时的消歧义。
SELECTproduct_nameASname,-- 列别名前端展示可能直接用这个 keyprice*stock_qtyAStotal_value-- 计算字段必须给别名否则结果集列名会很乱FROMproductsASp;-- 表别名常用于 Join 查询
5.
3 去重 (DISTINCT) 的代价-- 查询所有的商品价格档位SELECTDISTINCTpriceFROMproducts;底层原理DISTINCT会让 MySQL 对结果集进行排序或建立临时表来去重。
在大数据量下这是一个非常消耗 CPU 和内存Sort Buffer的操作。
优化建议如果业务允许尽量在应用层Java/Python去重或者通过GROUP BY优化。
2 条件查询 (WHERE) —— 数据的过滤器
5.
1 比较与逻辑运算SELECT*FROMproductsWHEREprice2000-- 价格大于等于 2000ANDstatus1-- 且 状态为上架ANDstock_qty100;-- 且 库存紧张
5.
2NULL的陷阱 —— 必须掌握的知识点在 SQL 中NULL代表“未知”。
任何与 NULL 进行的算术比较, !, , 结果都是 NULL即 False。
-- 错误写法这查不到 created_at 为 NULL 的记录SELECT*FROMproductsWHEREcreated_atNULL;-- 正确写法使用 IS NULL / IS NOT NULLSELECT*FROMproductsWHEREcreated_atISNULL;实战思考这就是为什么我们在
建表时强烈建议字段NOT NULL DEFAULT 。
NULL不仅让代码逻辑变复杂必须特判还会影响索引效率。
5.
3 模糊查询 (LIKE) 与索引失效--
前缀匹配推荐可以使用索引-- 查找所有以 iPhone 开头的商品SELECT*FROMproductsWHEREproduct_nameLIKEiPhone%;--
后缀匹配/全模糊性能杀手索引失效-- 查找名字里包含 Pro 的商品SELECT*FROMproductsWHEREproduct_nameLIKE%Pro%;原理B 树索引是按照字母顺序排列的。
iPhone%我知道去索引树的哪里找找 I 开头的。
%Pro%我怎么知道它在开头、中间还是结尾只能全表扫描 (Full Table Scan)。
注对于
7 版本全模糊搜索建议使用 Full Text Index全文索引或 ElasticSearch。
3 排序 (ORDER BY) 与 SQL 执行顺序
5.
1 多字段排序-- 先按状态排序上架的在前再按价格从高到低排SELECTproduct_name,price,statusFROMproductsORDERBYstatusDESC,priceDESC;
5.
2 SQL 执行顺序之谜初学者常遇到的报错-- 错误Unknown column total in where clauseSELECTprice*stock_qtyAStotalFROMproductsWHEREtotal10000;为什么报错这涉及 SQL 的逻辑执行顺序FROM(找到表)WHERE(过滤行) -- 此时total还没算出来呢GROUP BYHAVINGSELECT(计算列、生成别名) -- 这里才生成totalORDER BY(排序) -- 所以 ORDER BY 可以用别名LIMIT修正-- 写法 1WHERE 里重复计算逻辑SELECTprice*stock_qtyAStotalFROMproductsWHEREprice*stock_qty10000;-- 写法 2 (高级)用 HAVING (通常配合 GROUP BY但此处也能用但不推荐)-- 写法 3 (推荐)嵌套子查询 (Derived Table)SELECT*FROM(SELECTprice*stock_qtyAStotalFROMproducts)AStWHEREt.total10000;
4 分页查询 (LIMIT) —— 性能优化的深水区
5.
1 基础分页MySQL 的分页语法很简单LIMIT offset, count-- 第 1 页每页 2 条SELECT*FROMproductsLIMIT0,2;-- 第 2 页每页 2 条 (跳过前2条取2条)SELECT*FROMproductsLIMIT2,2;-- 第 3 页SELECT*FROMproductsLIMIT4,2;
5.
2 深分页 (Deep Paging) 性能问题当你的数据量达到百万级时翻到第 100 万页会发生什么-- 极其慢可能需要几秒甚至几十秒SELECT*FROMproductsLIMIT1000000,10;原理MySQL 执行LIMIT 1000000, 10的逻辑是先扫描读取 1,000,010 行数据。
抛弃前 1,000,000 行。
返回最后 10 行。
这意味着大量的磁盘 I/O 和 CPU 浪费。
5.
3 优化方案延迟关联 (Deferred Join)我们利用覆盖索引先只查主键 ID速度快再通过 ID 回表查完整数据。
SELECTp.*FROMproducts pINNERJOIN(-- 子查询只查主键利用覆盖索引不用回表速度极快SELECTproduct_idFROMproductsLIMIT1000000,
AStmpONp.product_idtmp.product_id;或者如果 ID 是连续且递增的且无 WHERE 条件可以使用游标法-- 记录上一页最后一条的 ID (last_id)SELECT*FROMproductsWHEREproduct_id1000000LIMIT10;这种方式性能最好O(
但只适用于特定场景。
5 综合案例构建商品搜索列表我们将模拟一个电商后台的商品搜索接口 SQL。
需求搜索名称包含 “iPhone” 的商品。
状态必须是上架 (status
。
价格在 4000 到 8000 之间。
按创建时间倒序排列。
查询第 1 页每页 10 条。
SELECTproduct_id,sku_code,product_name,price,created_atFROMproductsWHEREstatus1ANDpriceBETWEEN4000AND8000ANDproduct_nameLIKEiPhone%-- 注意把模糊匹配放在最后或者利用最左前缀ORDERBYcreated_atDESCLIMIT0,10;索引设计思考预告为了让这条 SQL 跑得快我们可能需要创建一个联合索引INDEX idx_status_price_created (status, price, created_at)关于索引的详细设计我们将在
深入探讨通过本章你已经掌握了从单表获取数据的核心能力。
但真实世界的数据往往分散在多张表中如何把它们拼起来下一章我们将解锁 SQL 最强大的功能多表连接 (Joins) 与 高级查询。