06.5.3.8 QueryBuilder 与查询优化
所属:全栈开发面试题库 / 06 后端框架与工程实践 / 06.5 PHP 后端 / 06.5.3 Yii
06.5.3.8-001 Query 对象结构与构建入口是什么?
一句话结论
yii\db\Query 以属性保存 select、from、where、join、groupBy、orderBy、limit 等查询片段,链式方法追加片段;createCommand() 把片段交给 QueryBuilder 生成 SQL 与参数绑定。
概念解释
Query 是查询描述对象,不直接执行 SQL;ActiveQuery 继承 Query 并补充 AR 实例化。构建入口可以是 new Query()、AR::find() 或连接的查询构造方法,取值时才生成 Command。
原理流程
- 创建 Query 并链式追加片段。
createCommand($db)生成带绑定参数的 Command。- 执行 Command 返回行或 AR 集合。
代码或图示
$query = (new yii\db\Query())
->select(['id', 'title'])
->from('{{%post}}')
->where(['status' => 'published']);
$rows = $query->all(Yii::$app->db);
常见误区
- 把 Query 当 SQL 字符串:它是结构化描述,取值前不发 SQL。
- 复用同一 Query 对象多次追加条件:条件叠加污染,需要时重新构造。
追问
- Query 与 Command 区别?答:Query 描述结构,Command 持有 SQL 与参数并执行;QueryBuilder 负责两者转换。
- 3.0 查询构造器在哪个包?答:
yiisoft/db提供查询构造器,API 形态以包文档为准,思路同为链式片段 + 参数绑定。
06.5.3.8-002 select/from/distinct 基础链如何书写?
一句话结论
select() 指定列或表达式,from() 指定表/子查询,distinct() 去除重复行;列名可加表别名,表达式用数组元素或数据库表达式对象包裹。
概念解释
select 支持列数组、字符串与表达式;from 可接表名、别名或子查询 Query。未指定 select 时默认取全部列。distinct 影响结果集去重,配合选择列使用。
原理流程
- from 确定主表。
- select 确定输出列。
- distinct 标记去重,生成对应 SQL 子句。
代码或图示
$query = (new yii\db\Query())
->select(['id', 'title' => 'title', 'author_name' => 'u.name'])
->from(['p' => '{{%post}}'])
->leftJoin(['u' => '{{%user}}'], 'u.id = p.author_id')
->distinct();
常见误区
- select 列与表别名不一致:SQL 报列歧义或不存在。
- 大表
select *:多余列增加传输与内存,明确列出所需列。
追问
- 表达式列如何写?答:用数组键别名 + 表达式,或传入 Expression 对象避免被当作列名转义。
- from 能接子查询吗?答:可以,传入 Query 并给别名,外层按别名引用。
06.5.3.8-003 where 的三种格式如何区分?
一句话结论
where 支持哈希格式 ['列' => 值]、操作符格式 ['操作符', 列, 值]、字符串格式(配合参数绑定);前两种自动参数绑定,字符串格式必须显式绑定参数防注入。
概念解释
哈希格式生成 列 = 值 等值条件,值数组转 IN;操作符格式表达 and/or/in/like/between/>= 等;字符串格式是原生片段,不能把未绑定的用户输入拼进字符串。
原理流程
- 按格式解析条件片段。
- 哈希/操作符格式收集绑定参数。
- QueryBuilder 拼出 WHERE 子句与占位符。
代码或图示
// 哈希
->where(['status' => 'published', 'category_id' => 3])
// 操作符
->where(['and', ['>', 'created_at', $since], ['status' => 'published']])
// 字符串 + 绑定
->where('title LIKE :kw', [':kw' => "%{$keyword}%"])
常见误区
- 字符串格式直接拼接
$keyword:SQL 注入风险。 - 哈希值为 null 时期望
IS NULL:等值 null 语义需用操作符/专门条件表达,具体生成行为以版本为准。
追问
- 三种格式能嵌套吗?答:操作符格式可嵌套子条件,形成复合 AND/OR 树。
- 条件为空如何处理?答:应用层判断是否追加 where,避免生成无意义恒真条件。
06.5.3.8-004 andWhere/orWhere 与参数绑定如何使用?
一句话结论
andWhere() 追加 AND 条件,orWhere() 追加 OR 条件;params() 登记绑定参数,所有用户输入经占位符绑定,不拼接进 SQL 文本。
概念解释
多次 andWhere 条件以 AND 连接,orWhere 形成 OR 分支;参数与 SQL 片段分离,QueryBuilder 生成占位符并绑定值。嵌套 OR 分组需用操作符格式保持括号正确。
原理流程
- 基础 where 设定主条件。
- andWhere/orWhere 追加复合条件。
- params 登记占位符值,执行时绑定。
代码或图示
$query->andWhere(['author_id' => $authorId])
->orWhere([
'and',
['status' => 'published'],
['>', 'views', 1000],
])
->params([':since' => $since]);
常见误区
- andWhere/orWhere 混用导致括号优先级错误:OR 与 AND 混排时必须嵌套分组。
- 重复绑定同名参数:参数覆盖可能导致条件不符预期。
追问
- where 能否覆盖已有条件?答:
where()重置条件片段,andWhere/orWhere追加;需要替换时重新构造 Query。 - 参数绑定类型如何保证?答:PDO 按值绑定;数组条件展开为 IN 列表,标量按占位符绑定。
06.5.3.8-005 数组条件支持哪些运算符?
一句话结论
操作符格式支持 and、or、not、in、not in、like、between、>、<、=、exists 等;数组值自动转为 IN 列表,like 可指定匹配位置。
概念解释
['in', 'id', $ids] 生成 id IN (...);like 配合值中的 % 通配;between 闭合区间。复杂条件用嵌套操作符树表达,避免手写字符串括号。
原理流程
- 操作符首元素标识条件类型。
- QueryBuilder 按类型生成算子与占位符。
- 数组参数展开为绑定列表。
代码或图示
->where([
'and',
['in', 'category_id', $categoryIds],
['like', 'title', "%{$keyword}%"],
['between', 'created_at', $start, $end],
])
常见误区
in传入空数组:生成IN ()语法错误,应用层先判空或改永假条件。- like 值未转义通配符:用户输入中的
%/_被当通配符,需按需求转义。
追问
- like 能否指定大小写不敏感?答:取决于数据库排序规则;操作符只生成 LIKE,大小写行为由列 collation 决定。
- not in 空集合语义?答:空集合生成方式需核对版本,避免语法错误;应用层先判断集合非空。
06.5.3.8-006 join 类型与 on 条件如何书写?
一句话结论
innerJoin/leftJoin/rightJoin 指定连接类型,第二参数为表别名数组,第三参数为 ON 条件;ON 条件可引用别名表列,参数同样绑定。
概念解释
JOIN 表可命名别名,ON 表达两表匹配关系;附加过滤条件应区分 ON 与 WHERE:LEFT JOIN 时 ON 不影响主表保留,WHERE 会过滤掉 NULL 行。
原理流程
- join 方法登记连接类型、表与别名。
- ON 条件生成连接子句。
- 主查询 where 对结果集二次过滤。
代码或图示
->leftJoin(['c' => '{{%comment}}'], 'c.post_id = p.id')
->innerJoin(['u' => '{{%user}}'], 'u.id = p.author_id')
常见误区
- LEFT JOIN 后在 WHERE 写右表非空条件:等价于 INNER JOIN,主表行被过滤。
- 同名列未加别名:SQL 列歧义报错。
追问
- 能否跨多个表连接?答:链式追加多个 join,别名各自独立;连接顺序影响执行计划。
- ON 条件能用参数绑定吗?答:可以,表达式中的值走占位符,不直接拼接。
06.5.3.8-007 groupBy/having/orderBy/limit/offset 如何组合?
一句话结论
groupBy 分组列、having 分组后过滤、orderBy 排序、limit/offset 分页;生成 SQL 时子句顺序按数据库语法固定,链式调用顺序不影响最终子句位置。
概念解释
having 作用于聚合结果,可引用聚合函数;orderBy 列数组支持升序降序方向;limit/offset 生成分页子句,不同数据库方言由 QueryBuilder 适配。
原理流程
- groupBy 声明分组列。
- having 对聚合结果过滤。
- orderBy 排序,limit/offset 截取分页。
代码或图示
->groupBy('author_id')
->having(['>', 'COUNT(*)', 5])
->orderBy(['created_at' => SORT_DESC, 'id' => SORT_ASC])
->limit(20)
->offset(40)
常见误区
- 在 where 里写聚合条件:分组前过滤无法使用聚合函数,应放 having。
- offset 过深:大偏移扫描成本高,深分页应改游标/键集分页。
追问
- orderBy 随机排序怎么做?答:按数据库表达式生成随机函数,不依赖框架链式排序;具体函数名方言相关。
- groupBy 与 select 列规则?答:数据库 ONLY_FULL_GROUP_BY 模式下,非聚合列必须出现在 groupBy。
06.5.3.8-008 子查询如何在 from/where 中嵌套?
一句话结论
Query 可作为子查询传入 from、where、join:from 接子查询并起别名,where 用 in/exists 包裹子查询对象;子查询参数同样参与绑定。
概念解释
子查询是独立 Query 实例,外层按别名引用其输出列。复杂聚合可拆为子查询分步构造,避免单条原生 SQL 难维护;数据库优化器决定子查询展开方式。
原理流程
- 构造内层 Query。
- 外层 from 或条件中传入内层对象。
- QueryBuilder 递归生成嵌套 SQL 与参数。
代码或图示
$active = (new yii\db\Query())
->select('post_id')->from('{{%comment}}')
->where(['status' => 'approved']);
$query->where(['in', 'id', $active]);
常见误区
- 子查询别名缺失:外层引用列找不到表。
- 子查询引用外层列未关联:相关子查询条件列需带表别名,否则语义错位。
追问
- 子查询能否独立执行?答:可以,单独 createCommand 执行;作为子查询时只取其 SQL 片段。
- 子查询与 JOIN 如何选择?答:简单关联优先 JOIN;存在性判断用 exists,聚合派生表用 from 子查询。
06.5.3.8-009 exists() 存在性判断如何使用?
一句话结论
exists() 执行子查询判断是否存在匹配行,返回布尔;相比 COUNT(*) > 0,数据库可在命中后停止扫描,适合关联存在性判断。
概念解释
exists 子查询返回行即真,不关心具体行数与列;相关子查询按外层列关联。判断“是否有评论/订单”一类场景优先 exists,避免统计全量计数。
原理流程
- 构造判断存在的子查询。
- 外层条件用 exists 包裹。
- 执行后返回布尔结果。
代码或图示
$hasComment = (new yii\db\Query())
->from('{{%comment}}')
->where('comment.post_id = post.id')
->exists(Yii::$app->db);
常见误区
- 用 count 取存在性:大表计数扫描全部匹配行,成本高于 exists。
- exists 子查询 select 列写
*:语义可接受,但只需要存在性,列列表不影响判断。
追问
- not exists 如何表达?答:操作符格式
['not exists', $subQuery],具体形式以版本为准。 - exists 与 IN 性能对比?答:取决于索引与数据分布,执行计划中两者可互相转换;应以 EXPLAIN 实测为准。
06.5.3.8-010 createCommand() 如何生成 Command 与 SQL?
一句话结论
createCommand($db) 把 Query 片段交给 QueryBuilder 编译为 Command,Command 持有 SQL 文本与绑定参数;执行 one()/all()/execute() 前可查看 SQL 用于调试。
概念解释
Command 是可执行对象,提供 bindValue、queryAll、queryOne、execute 等方法;数据库方言适配在 Builder 层完成。调试时可导出 SQL 与参数,不把参数拼进 SQL 字符串。
原理流程
- Query 描述片段与参数。
- createCommand 编译方言 SQL。
- Command 绑定参数并执行。
代码或图示
$command = $query->createCommand(Yii::$app->db);
$sql = $command->getSql();
$rows = $command->queryAll();
常见误区
- 直接拼接
$command->sql . 用户输入:绕过绑定,注入风险。 - 跨连接复用 Command:Command 绑定特定连接,换库需重新 createCommand。
追问
- 如何查看最终 SQL?答:
getSql()取编译文本,参数在绑定清单中;日志可记录 SQL 与绑定值(注意脱敏)。 - QueryBuilder 方言差异?答:limit 占位符、引用符、表达式函数由各数据库 Builder 适配,业务代码不写方言片段。
06.5.3.8-011 one()/all() 取值与 asArray() 如何选择?
一句话结论
one() 取首行,all() 取全部行;asArray(true) 返回纯数组行,不实例化 AR;只读大批量、报表场景用数组,需要 AR 方法与关联时用模型实例。
概念解释
AR 实例携带事件、关联与脏属性跟踪,内存成本高于数组;数组行只保留字段值。两者查询构造相同,差异在结果映射层。
原理流程
- 执行 Command 取结果行。
- asArray 开启:行直接进入结果数组。
- 否则按 ActiveQuery 包装为 AR 实例。
代码或图示
$row = (new yii\db\Query())->from('{{%post}}')->one();
$list = Post::find()->asArray()->limit(100)->all();
常见误区
- 大批量仍实例化 AR:对象开销放大内存占用,批量导出应 asArray 或游标。
- asArray 后调用模型方法:数组无方法,只能取字段值。
追问
- one 未命中返回什么?答:返回 false/null,具体以连接查询方法实现为准,判空需按返回类型核对。
- 数组行字段类型保留吗?答:按驱动返回类型,弱类型转换仍可能发生,数值字段需要时显式转换。
06.5.3.8-012 batch()/each() 批量游标如何防止内存膨胀?
一句话结论
batch($size) 按批取出记录集合,each($size) 逐条迭代;两者基于分页游标分批查询,避免一次 all() 把全部行载入内存,适合大批量遍历与导出。
概念解释
每批执行一次 limit/offset 查询,迭代完一批再取下一批;游标位置随批次推进。批大小权衡查询次数与单批内存,大字段行应减小批 size。
原理流程
- 构造 Query 并设定批次大小。
- 第一批查询取出行集合。
- 迭代完一批后按游标取下一批,结果逐条/逐批产出。
代码或图示
foreach (Post::find()->batch(100) as $posts) {
foreach ($posts as $post) {
// 处理每批 100 条
}
}
常见误区
- 批内累积全量结果到数组:游标省内存的效果被抵消。
- 批次遍历中修改排序键:游标偏移可能跳过或重复记录,遍历期间避免改动排序字段。
追问
- each 与 batch 区别?答:each 逐条产出,batch 产出整批;内存模型相同,遍历粒度不同。
- 深分页游标性能?答:batch/each 内部按 offset 推进,超深偏移仍有扫描成本;超大表考虑键集游标或导出专用通道。
06.5.3.8-013 indexBy 如何按键索引结果集合?
一句话结论
indexBy($column) 用结果某列作为数组键索引集合,避免遍历后手动 array_column 重组;可传回调生成复合键。
概念解释
indexBy 在结果映射阶段建立键映射,重复键后者覆盖前者;适合按 ID 快速查找关联对象。配合 asArray 或 AR 均可使用。
原理流程
- 声明索引列或回调。
- 结果行按键写入映射数组。
- 返回以键为下标的集合。
代码或图示
$map = Post::find()->indexBy('slug')->asArray()->all();
echo $map['hello-world']['title'];
常见误区
- 索引列存在重复值:集合被覆盖,只保留最后一行。
- 把 indexBy 当分组:它是键映射不是分组聚合,多值分组需自行归类。
追问
- 索引键能是表达式吗?答:可传回调根据行生成键;具体参数形式以版本为准。
- indexBy 影响 SQL 吗?答:不影响查询子句,只作用于 PHP 结果数组。
06.5.3.8-014 union 如何组合多个查询?
一句话结论
union($query) 把另一个查询追加为 UNION 分支;各分支列数与类型需对应,排序与 limit 作用于最终结果时需用外层包裹。
概念解释
UNION 去重合并,UNION ALL 保留重复行;分支查询各自独立构造。需要整体排序分页时,把 union 结果作为外层子查询再排序。
原理流程
- 构造主查询。
- union 追加分支 Query。
- Builder 生成 UNION 组合 SQL。
代码或图示
$base = (new yii\db\Query())->select('id')->from('{{%post}}');
$base->union((new yii\db\Query())->select('id')->from('{{%draft}}'));
常见误区
- 分支列数不一致:SQL 报错;列别名以第一分支为准。
- 各分支 orderBy 直接作用于最终结果:分支排序可能被 UNION 抵消,需外层排序。
追问
- UNION ALL 如何指定?答:union 方法可传第二参数控制去重方式,具体参数签名以版本为准。
- 分支过多影响性能吗?答:每个分支独立执行,索引与结果集大小决定成本,应实测执行计划。
06.5.3.8-015 索引提示与强制索引如何落地?
一句话结论
查询构造器可通过数据库表达式传入索引提示(USE INDEX/FORCE INDEX),引导优化器选择指定索引;提示只是建议/强制,最终仍需结合 EXPLAIN 验证执行计划。
概念解释
索引提示用于优化器误选索引的场景;框架 Query 通用片段之外的方言写法用 Expression 对象包裹,避免被转义。滥用强制索引会在数据分布变化后劣化,应记录原因并复测。
原理流程
- 确认慢查询与候选索引。
- 表达式注入索引提示片段。
- EXPLAIN 核对执行计划是否采用目标索引。
代码或图示
$query = (new yii\db\Query())
->from(['p' => new yii\db\Expression('{{%post}} FORCE INDEX (idx_status_created)')])
->where(['p.status' => 'published']);
常见误区
- 不看 EXPLAIN 直接加索引提示:可能掩盖缺失索引或统计信息问题。
- 提示片段跨数据库方言照搬:不同数据库语法不同,提示应按连接方言书写。
追问
- 索引提示属于框架通用能力吗?答:通用 Query 不提供专门方法时,用表达式对象写方言片段;不确定语法时在目标数据库实测。
- 统计信息过时怎么办?答:先更新表统计信息,再判断是否需要索引提示。
06.5.3.8-016 如何用 EXPLAIN 定位慢查询?
一句话结论
对最终 SQL 执行 EXPLAIN,关注 type 访问类型、key 命中索引、rows 扫描行数、Extra 中的临时表/文件排序;结合慢查询日志定位高成本查询再优化索引或改写。
概念解释
全表扫描(type 为 ALL)、未命中索引、大行数扫描、Using filesort/Using temporary 是常见劣化信号。优化顺序:补索引 → 改写条件/分页 → 减少回表与大字段。
原理流程
- 从日志或命令取得最终 SQL 与参数。
- EXPLAIN 查看执行计划各列。
- 针对性调整索引、条件结构或查询写法并复测。
代码或图示
EXPLAIN SELECT id, title FROM post
WHERE status = 'published'
ORDER BY created_at DESC LIMIT 20;
常见误区
- 只看耗时不看计划:偶发慢查询可能由锁等待或缓存冷启动引起,计划与等待时间都要记录。
- 盲目加单列索引:复合索引顺序需匹配条件与排序列。
追问
- 回表如何识别?答:Extra 与 key 列判断是否覆盖索引;需要的列不在索引中时通过主键回表。
- 慢查询阈值怎么定?答:按业务 P99 响应时间设定,记录 SQL、参数、扫描行数与执行时间,定期复盘。
06.5.3.8-017 分页查询的 limit/offset 如何估算成本?
一句话结论
分页用 limit + offset 截取页面,offset 越大扫描跳过的行越多;深分页应改用键集游标(按上一页末值继续过滤)或限定可翻页范围。
概念解释
offset N 先扫描前 N 行再丢弃,深页成本随偏移增长;索引排序字段连续时,键集分页 WHERE sort_key < last_value LIMIT size 可避免大偏移。总页数 count 在大表上同样有成本,可估算或延迟统计。
原理流程
- 常规分页:limit 页大小,offset 页码偏移。
- 深分页:记录上一页排序末值。
- 下一页按末值条件继续取同方向排序结果。
代码或图示
// 键集分页思路
$query->where(['<', 'id', $lastId])
->orderBy(['id' => SORT_DESC])
->limit(20);
常见误区
- 任意深页都允许跳转:大偏移拖垮数据库,前台应限制页码范围。
- 实时 count 总数:大表 count 扫描成本高,可用近似值或只取下一页标记。
追问
- 排序字段不唯一怎么办?答:追加唯一键(如 id)作为第二排序键,保证游标稳定。
- 复合排序如何键集化?答:把排序列组合成游标条件,方向与顺序保持一致。
06.5.3.8-018 事务中查询一致性与隔离级别如何考虑?
一句话结论
事务内查询受隔离级别约束:读已提交/可重复读等级别决定能否读到其他事务未提交或已提交变更;一致性读与锁读按业务需要选择,事务边界由连接事务方法控制。
概念解释
查询在事务内看到的快照取决于隔离级别;需要锁定读取行时使用锁读(如 FOR UPDATE)。长事务持有锁与快照,影响并发与回滚成本,应尽量缩短事务边界。
原理流程
- 连接开启事务,设定隔离级别(如需要)。
- 事务内执行查询与写入。
- 提交或回滚,锁与快照释放。
代码或图示
$db->transaction(function () use ($db, $post) {
$db->createCommand()->update('{{%post}}', ['status' => 'published'], ['id' => $post->id])->execute();
// 同事务内一致性读取后续操作
});
常见误区
- 事务内调用远程接口/发通知:事务拉长,连接与锁占用增加,外部副作用应移到提交后。
- 默认隔离级别假设太强:跨数据库默认级别不同,一致性要求需显式确认。
追问
- 隔离级别在哪里设置?答:连接配置或事务选项中设置,具体配置键以数据库连接实现为准。
- 脏读/不可重复读/幻读区别?答:按隔离级别演进记忆:读已提交避免脏读,可重复读避免不可重复读,串行化避免幻读。
06.5.3.8-019 Query 与 ActiveRecord 如何取舍?
一句话结论
AR 适合单表/关联的对象化操作、校验与生命周期事件;Query/Command 适合大批量只读、报表聚合、批量写入与复杂联表,降低对象开销并直接控制 SQL。
概念解释
AR 提供模型方法、关联、事件与脏跟踪,但每行实例有内存成本;Query 返回数组行或直接执行命令,适合导出、统计、后台批处理。写入批量场景可用批量命令,绕过逐行 AR 事件。
原理流程
- 按场景判断:需要对象行为选 AR,数据处理选 Query。
- 列表详情走 AR 关联,报表统计走 Query 聚合。
- 批量写入用 Command 批量构造,必要时在服务层补事件通知。
代码或图示
// 报表聚合:Query 直连
$stats = (new yii\db\Query())
->select(['author_id', 'cnt' => 'COUNT(*)'])
->from('{{%post}}')
->groupBy('author_id')
->all($db);
常见误区
- 报表导出逐行 AR 查询:对象与懒加载拖慢大批量任务。
- 复杂业务校验下沉到裸 SQL:输入合法性与规则校验应在模型/服务层完成。
追问
- 批量插入用什么?答:Command 批量 insert 或分批构造,具体批量方法以连接 Command 能力为准。
- 两种方式混用如何保持一致?答:字段约定与迁移统一,AR 与 Query 映射同一表;变更后回归两边读取路径。
06.5.3.8-020 常见误区:拼接注入、N+1 与全表 count
一句话结论
查询层高频问题包括:用户输入拼进 SQL 字符串、循环内懒加载、大表无差别 count、深分页 offset、JOIN 未去重、子查询别名缺失、未按 EXPLAIN 验证索引。
概念解释
评审查询时核对:参数全部绑定、列表预加载或批量游标、聚合 count 必要性、分页方式、JOIN 去重与索引覆盖。慢查询记录 SQL、计划、扫描行数与业务场景,作为索引优化依据。
原理流程
- 检查 where/join/order 是否存在字符串拼接输入。
- 检查列表访问路径是否触发 N+1。
- 用 EXPLAIN 复核索引、扫描行数与排序方式。
代码或图示
// 自检清单(示意)
// [x] 条件参数走绑定占位符
// [x] 列表 with 预加载或 batch 游标
// [x] JOIN 结果已去重
// [ ] 深分页已改键集游标
常见误区
- 认为框架自动防注入就可以拼字符串:构造器只保护通过数组/参数传入的部分,原生字符串片段仍需自行绑定。
- count 总数无条件缓存:每次请求实时统计大表,成本高且无必要。
追问
- 如何审计不安全 SQL?答:代码评审排查字符串 where/Expression 中的变量;数据库日志记录全量 SQL 与参数做复盘。
- 查询优化先做哪一步?答:先确认是否需要该查询(缓存/冗余汇总),再补索引,最后改写 SQL 与分页方式。