01 B树定义B树是对应文件系统需要而产生的一种B-树的变型树。一棵m阶的B树和同阶的B树的差异在于有n棵子树的节点中有n个关键字所有的叶子节点中包含了全部关键字的信息以及指向这些关键字记录的指针且叶子节点本身依赖关键字的大小自小而大顺序连接所有的非终端节点可以看成是索引部分节点中仅含有其子树根节点中的最大或最小关键字。02 B树的核心思想以互联网主流关系型数据库Mysql为例。在b树中非叶子节点不含有该关键字对应记录的存储地址可以使得一个磁盘块包含更多的关键字使得b树的阶数更大树高更矮读磁盘次数更少查找更快。03 非终端节点存储非叶子节点只存主键值和子节点页指针指向下一层磁盘页不存具体数据行地址。这使得InnoDB默认页16KB能塞下更多的键值对通常可存上千个子节点指针从而大幅提升阶数扇出。具体数据3层树即可支撑千万级数据假设主键为BIGINT8字节 指针6字节 约14字节。16KB页可存约1170个索引条目。若叶子节点每行约1KB则2层约 1170 * 16 1.8万条3层约 1170 * 1170 * 16 2200万条因此等值或范围查询通常只需2~3次磁盘I/O速度极快。04 终端节点存储聚簇索引主键叶子节点存的是完整的行数据而非磁盘物理地址。二级索引普通索引叶子节点存的是主键值而非原数据地址。查询时先找到主键再回聚簇索引查数据回表。05电商订单系统主键设计B树的来源就是多叉排序树无序调整需要执行大量指针断连断连分裂/合并。不要用UUID或随机字符串做主键这会导致B树频繁页分裂造成插入性能雪崩。方案使用有序的雪花IDSnowflake或自增ID。理由B树是顺序插入数据页写满后只需开辟新页无需移动大量已有数据磁盘IO最小。有序的雪花IDSnowflake在这里插入代码片核心表结构设计遵循范式将一对多的数据拆成两张表订单主表order存订单号、用户ID、实付金额、状态、下单时间。注意金额用DECIMAL(10,2)避免浮点误差状态用TINYINT。订单明细表order_item存具体商品SKU、单价、数量。注意这里要冗余存储商品快照名称和价格下文解释。索引策略索引是设计的重中之重直接影响查询速度必加索引针对高频查询SELECT * FROM order WHERE user_id ? ORDER BY create_time DESC建立联合索引(user_id, create_time)。B树会先按用户ID排序再按时间排序查询时无需回表排序直接命中叶子节点。区分度低的字段像order_status只有0,1,2不建索引。因为B树扫描大量相同值效率极低不如全表扫。覆盖索引若只查订单号和金额可建(user_id, amount)数据直接从索引页返回彻底跳过回表减少2次磁盘IO。冷热数据分离控制树高B树3层支撑2000万数据。如果订单量过亿树高变4层查询变慢。设计只保留最近3个月的热数据在order主表。把3个月前的冷数据迁移到order_archive归档表或历史库确保活跃B树高度始终维持3层保证高峰期写入和查询稳定。06 SQL常见操作分组聚合GROUP BY HAVINGWHERE过滤原始行HAVING过滤分组后的结果。查“订单明细表”中下单次数超过3次的用户ID。SELECT user_id FROM order GROUP BY user_id HAVING COUNT(*) 3表连接JOININNER JOIN只返回两表匹配上的数据查现有订单详情。LEFT JOIN左表全返回右表无匹配则为NULL查“所有用户及其订单数”即使没下过单也要显示0。笛卡尔积风险忘了写ON条件和连接顺序优化小表驱动大表。窗口函数ROW_NUMBER / RANKRANK会跳号1,1,3DENSE_RANK不跳号1,1,2要根据“是否允许并列第一”选对函数。查询每个用户金额最大的那笔订单。ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY amount DESC) AS rn外层WHERE rn 1。4. 索引与执行计划EXPLAIN-盯紧typeALL全表扫→ref普通索引→range范围→eq_ref→const。要求你至少优化到**range级别。-盯紧Extra看见Using filesort文件排序或Using temporary临时表基本就是慢SQL元凶需要立马想到加联合索引**规避。三大“空值与去重”陷阱**COUNT(1)vsCOUNT(*)vsCOUNT(列名)COUNT(列名)会忽略NULL值这是高频挖坑点。聚合函数遇见NULLSUM、AVG会忽略NULL若字段全为NULLSUM返回NULL业务层需配合IFNULL处理。去重DISTINCT是全字段去重若要按某字段去重取最新记录必须用窗口函数或子查询别用GROUP BY硬凑。