AnyPS5开源模拟器：从原理到实践，深度解析PS5模拟器搭建与调优
2026/10/15 7:40:33
核心内核：SQL优化本质，是减少数据库服务器的IO开销、CPU计算开销、锁等待开销，用最小成本拿到查询结果。类比：给仓库查询订单。原始SQL是写了一句笼统的查找指令；优化就是建立索引（仓库目录）、缩小检索范围、减少全库翻找、避免锁表，让检索快速返回结果。承接前面LNMP、MySQL底层知识，SQL不是写出来能跑就行，要兼顾单次查询性能、并发、锁与存储资源。
一、本源基础术语
- SQL优化
在业务需求不变前提下，改写SQL语句、调整索引、修改表结构、调整数据库参数，降低查询消耗，缩短执行时间，减少资源占用。目标：减少磁盘IO（最核心），其次降低CPU。 - 执行计划 explain
MySQL分析SQL之后生成的查询执行方案。告诉数据库用什么索引、表连接顺序、扫描行数、是否全表扫描。是SQL优化的诊断工具，优化第一步必看explain。 - 慢查询 Slow Query
执行时长超过预设阈值的SQL。MySQL可以记录慢查询日志，用来定位需要优化的候选SQL。阈值由long_query_time配置。 - 扫描行数 rows
explain输出的预估扫描记录数。扫描行数越大，磁盘IO越高，查询越慢。优化核心目标就是减少扫描行数。 - 覆盖索引
索引里包含SQL查询所需要的全部字段，查询不需要回表读取主键对应的完整行数据，直接从索引拿到结果，大幅减少IO。 - 回表
二级索引只存储索引列+主键，找到主键之后，再根据主键去聚簇索引读取完整行数据，这个动作叫回表。回表会增加IO，是常见性能损耗点。
二、索引相关术语（SQL优化最核心模块）
- 聚簇索引（主键索引）
InnoDB的底层索引，数据行本身和主键放在一起，索引叶子节点就是完整数据。一张表只能有一个聚簇索引。 - 二级索引（辅助索引）
除主键之外建立的索引，叶子节点保存索引字段+主键值。查询二级索引命中后，如果字段不全就会触发回表。 - 联合索引（复合索引）
多个字段一起建立的索引，遵循最左前缀原则。例如idx_a_b_c(a,b,c)。 - 最左前缀原则
联合索引生效规则，查询条件要匹配索引最左边连续字段。跳过最左列，索引失效。 - 索引选择性
索引区分度，唯一值数量/总记录数。选择性越高，索引价值越高。性别、状态这类低选择性字段，单独建索引收益很低。 - 索引下推 ICP
InnoDB优化机制，在索引扫描阶段，直接在索引层过滤条件，减少回表次数。MySQL5.6之后默认开启。 - 索引失效
写了索引，但SQL无法使用索引，变成全表扫描。常见诱因：函数操作、隐式类型转换、or、like %前缀、违反最左前缀。 - 冗余索引
已有联合索引，单独再建其中前缀字段索引，造成索引重复。多余索引会增加插入、更新、删除开销。 - 索引维护成本
表写入（insert/update/delete）时，数据库需要同步更新索引。索引不是越多越好，索引越多写越慢。
三、SQL语句写法优化术语
- 全表扫描 ALL
explain type=ALL，遍历整张表所有数据，性能最差，大数据量场景必须尽量规避。 - **避免select ***
不要查询表全部字段。只查业务需要的列：减少网络传输，增加覆盖索引机会。 - 隐式类型转换
字段类型和传入参数类型不一致，触发自动转换，导致索引失效。例：varchar字段用数字查询 where phone=138xxxx。 - 谓词下推
尽量把过滤条件放在表连接之前，提前过滤数据，减少参与join的数据量。 - in与exists
in适合子查询结果集小；exists适合外表数据量小。版本不同优化器策略会变化，不能死记公式，优先看explain。 - join表连接
多表关联查询。优化要点：小表驱动大表；关联字段必须建立索引；控制join数量，不建议超过3张表。 - 小表驱动大表
循环嵌套join的原则，用数据量小的表作为驱动表，减少循环次数。 - order by 排序优化
排序可以利用索引有序特性，避免filesort文件排序。索引字段顺序、排序方向要匹配。 - filesort 文件排序
无法使用索引有序性，MySQL需要额外内存/磁盘完成排序。消耗CPU，大数据排序性能差。 - group by 分组优化
分组同样尽量走索引，避免临时表。group by会默认排序，不需要排序加上order by null。 - 临时表 Using temporary
分组、去重等场景，MySQL创建临时表存放中间结果，开销较大，尽量消除。 - limit分页优化
大偏移分页 limit 10000,10，会扫描前面一万行数据。优化方案：主键书签定位（延迟关联）。 - or 条件陷阱
or连接不同字段，容易索引失效。可以改成union all拆分语句。union会自动去重，union all不去重，性能更高。
四、事务、锁与并发优化术语
- 行锁
InnoDB默认，命中索引时只锁定匹配的行，并发好。 - 表锁
锁住整张表，其他读写全部阻塞。索引失效会触发行锁升级为表锁，是线上重大坑。 - 间隙锁 Gap Lock
可重复读隔离级别下存在，锁住索引间隙，防止幻读；容易产生死锁、锁等待。 - 锁等待
事务需要的行被其他事务锁住，发生等待，SQL卡住超时。优化思路：缩短事务执行时间。 - 长事务
事务开启后长时间不提交，占用锁、占用undo日志，阻碍清理，影响数据库性能。SQL优化不只是单条语句，事务长短是关键。 - MVCC多版本并发控制
InnoDB依靠undo日志保存数据快照，实现不加锁读，是快照读的底层。快照读不会加行锁。
五、表结构优化术语
- 字段类型精简
优先使用更小字段类型，例如用tinyint代替int，varchar控制合理长度。减少磁盘存储，一页可以存放更多行，提升缓存命中率。 - 主键设计
主键尽量简短、有序自增。无序主键（UUID）会造成索引页分裂，写性能下降。 - 避免NULL
字段尽量not null，NULL会增加索引存储开销，影响索引效率，判断逻辑更复杂。 - 分库分表
单表数据量巨大（千万级以上），拆分成多张表/多个库，降低单表索引体积。分为水平分表、垂直分表。属于重量级方案，优先优化索引，最后才考虑分表。 - 垂直拆分
一张表大字段（文本、长描述）拆到单独附表，主表只保留高频查询字段。 - 水平拆分
按id、时间范围，把数据分到多张同结构表。
六、架构层优化术语（SQL优化到极限后的方案）
- 查询缓存（MySQL8.0移除）
旧版本缓存查询结果，8.0废弃，不再使用。 - 读写分离
主库负责写，从库承载查询。分担主库压力，读请求打到从库。 - 缓存层 Redis
热点查询结果放入Redis，直接返回，不访问MySQL，绕开数据库查询。属于应用层优化，减少SQL执行次数。 - 预计算、汇总表
报表统计类SQL实时查询代价极高，定时任务预先计算结果存入汇总表，业务直接查汇总表。 - 数据归档
历史冷数据迁移到归档表，业务表只保留近期热数据，减少表行数。
七、性能指标术语
- QPS 每秒查询数
数据库每秒执行SQL数量。 - TPS 每秒事务数
每秒成功提交事务数量，衡量写入压力。 - Buffer Pool 缓冲池
InnoDB核心内存，缓存数据页、索引页。热点数据放入内存，避免读磁盘。调大buffer pool是基础优化手段。 - 命中率
缓冲池命中比例。命中率低代表大量读取磁盘，性能差。
八、认知误区术语
- 误区：索引建的越多，查询越快
纠正：索引加速读，但严重降低写入性能。索引要按需建立，清理冗余索引。 - 误区：explain看到有索引，SQL就一定快
纠正：要看扫描行数。索引选择性差，扫描行数依然巨大，性能依旧差。 - 误区：优化只看单条SQL，不用考虑并发
纠正：单条SQL很快，但高并发下大量SQL同时执行，会引发锁竞争、IO打满。必须结合业务并发评估。 - 误区：分库分表是性能万能解药
纠正：分库分表会大幅增加业务复杂度，优先索引、SQL改写、归档，最后才考虑分表。 - 误区：like ‘%关键词%’ 可以用索引优化
纠正：前缀%无法走B+树索引；可以用全文索引或者搜索引擎。
九、类比串联
MySQL就像图书馆，B+树索引是目录。SQL就是查询指令。优化就是合理建目录、缩小检索范围，不要每次翻完整本书。索引维护就像新增书籍时同步更新目录，目录太多，新增书籍会变慢。explain就是查询前，查看图书管理员打算怎么检索。
庖丁解牛一句话总结
SQL优化以减少磁盘IO为核心，先用explain诊断执行计划；优先建立合适联合索引，利用覆盖索引减少回表，规避索引失效；改写SQL减少join、filesort、临时表；控制事务时长避免锁等待；优化字段与表结构；热点场景增加缓存、数据归档；分库分表是最后备选方案，优化需要同时兼顾读性能、写入开销与并发锁竞争。