企业官网建设流程全解析

📌今日关键词：MySQL进阶、窗口函数、CTE、JOIN优化、在线DDL、生成列

大家好，我是数据库小学妹 👋

之前写过几篇关于单个SQL技巧的文章——聊了窗口函数、CTE，拆了JSON类型。这些单点技巧都懂了，但写SQL的时候还是不知道什么时候该用什么。

今天把我觉得最实用的10个技巧按"解决什么问题"串起来。已覆盖过的内容简要提一下重点，重点展开那些之前没怎么讲过的。

一、先学会看：EXPLAIN

写SQL的第一步不是写，是看完执行计划再动手。

EXPLAINSELECT*FROMordersWHEREuser_id=10086ANDcreate_timeBETWEEN'2024-01-01'AND'2024-01-31'ORDERBYamountDESC;

输出里盯住三个字段就够：

• type：ALL是全表扫描，最差。至少要到ref或range级别
• Extra：出现Using filesort或Using temporary得优化
• rows：扫描行数越大越有问题

之前文章专门拆过EXPLAIN的6个关键字段，也讲了生产环境怎么看执行计划，这里不展开了。记住一句话：不看EXPLAIN就加索引，跟不看地图就出门一样。

-- 统计信息过时会导致优化器选错索引ANALYZETABLEorders;-- 执行完再跑一次EXPLAIN，看看执行计划有没有变

二、索引不只是建不建的问题

基础索引之前就讲过了，这里说三个进阶的。

覆盖索引：不回表就是快

如果查询的字段全在索引里，MySQL直接从索引返回数据，不用去聚簇索引捞——省了一次IO。

-- 常查这个SELECTname,emailFROMusersWHEREage>20;-- 建一个覆盖索引CREATEINDEXidx_age_name_emailONusers(age,name,email);-- age用于过滤，name和email在索引里直接返回-- Extra会显示Using index，说明走了覆盖索引

此前从B+树的角度解释过为什么覆盖索引能避免回表，不清楚不可以翻翻我之前的文章。

索引下推：5.6之后的隐藏优化

这个很少有人专门讲，但对复合索引的性能影响很大。

假设复合索引是(a, b)，查询是WHERE a = 10 AND b LIKE '%abc'。

5.6之前的做法：引擎用a = 10在索引里找到所有记录，然后全部回表，再由Server层过滤b LIKE '%abc'。

5.6之后有了索引下推（ICP）：b LIKE '%abc'这个条件下推到存储引擎层，在索引扫描时就直接过滤。不需要的记录根本不回表。

看EXPLAIN的Extra字段，出现Using index condition说明在用索引下推。这不是问题，是好事。

前缀索引：长文本字段的折中方案

VARCHAR(500)的字段建全字段索引太臃肿了。可以只对前N个字符建索引。

-- content字段太长，只取前100个字符CREATEINDEXidx_content_prefixONarticles(content(100));

怎么确定取多少字符？看区分度：

-- 测试不同前缀长度的区分度SELECTCOUNT(DISTINCTLEFT(content,50))/COUNT(*)ASsel_50,COUNT(DISTINCTLEFT(content,100))/COUNT(*)ASsel_100,COUNT(DISTINCTLEFT(content,150))/COUNT(*)ASsel_150,COUNT(DISTINCTcontent)/COUNT(*)ASsel_fullFROMarticles;

哪个长度的区分度接近sel_full，就取那个长度。一般到0.9以上就够了。

缺点：前缀索引不能用于ORDER BY和GROUP BY，也不能做覆盖索引。

三、窗口函数和CTE：SQL进阶的两道坎

窗口函数

跨行计算的利器。之前的文章讲了原理，也给了三个万能模板，这里不重复了。

一句话总结：需要"保留每一行的同时做分组计算"——排名、环比、累计求和、移动平均——用窗口函数。GROUP BY会丢行，窗口函数不会。

SELECTname,department,salary,RANK()OVER(PARTITIONBYdepartmentORDERBYsalaryDESC)ASdept_rankFROMemployees;

CTE（WITH子句）

复杂查询拆成逻辑清晰的步骤。

WITHhigh_valueAS(SELECTuser_id,SUM(amount)AStotalFROMordersWHEREstatus='completed'GROUPBYuser_idHAVINGtotal>10000),activeAS(SELECTDISTINCTuser_idFROMuser_logsWHERElast_active_date>DATE_SUB(NOW(),INTERVAL30DAY))SELECTu.name,h.totalFROMusers uJOINhigh_value hONu.id=h.user_idJOINactive aONu.id=a.user_id;

比嵌套子查询好读十倍。之前的文章专门讲了递归CTE查组织架构树的用法。

四、JSON和分区表

JSON类型

动态字段不想建子表就用JSON。->返回JSON类型，->>返回文本。关键字段可以建函数索引：

CREATEINDEXidx_colorONproducts((attributes->>'$.color'));

分区表

之前讲过基础用法，以及局部索引和全局索引的区别。大表按时间分区，历史数据清理直接ALTER TABLE sales DROP PARTITION p2023q1，比DELETE快得多。

五、JOIN优化——真正拉开差距的地方

从这里开始是之前没怎么讲过的内容。

三种JOIN算法

MySQL处理JOIN有三种方式，知道它们才能理解为什么有些JOIN快有些慢：

嵌套循环（Nested Loop Join）——最常见。外表取一行，去内表用索引找匹配。内表有索引就很快。

块嵌套循环（Block Nested Loop Join）——内表没索引时的退化方案。把外表的一批数据加载到join buffer里，然后跟内表逐行比。内表越大越慢。

Hash Join（8.0.18+）——针对没有索引的等值JOIN做了优化。把小表构建成哈希表放内存里，大表逐行去查。比BNL快不少。

怎么知道用了哪种？EXPLAIN看Extra：

• 什么都没说 → 大概率是NLJ
• Using join buffer (Block Nested Loop)→ BNL，该加索引了
• Using join buffer (hash join)→ Hash Join

四个JOIN实战技巧

技巧一：确保JOIN字段有索引

这条最基础，但线上还是经常看到没索引的JOIN。

-- order_items的order_id没索引？加！ALTERTABLEorder_itemsADDINDEXidx_order_id(order_id);

技巧二：小表驱动大表

MySQL优化器一般会自动选择，但偶尔会选错。

-- 强制small_table做驱动表SELECT*FROMsmall_table s STRAIGHT_JOIN large_table lONs.id=l.s_id;

怎么看谁是驱动表？EXPLAIN里，两行结果中第一行的就是驱动表。

技巧三：先过滤再JOIN

-- 不好：先JOIN两个大表，再过滤SELECT*FROMAJOINBONA.id=B.aidWHEREA.create_time>'2024-01-01';-- 好：先过滤再JOINSELECT*FROM(SELECT*FROMAWHEREcreate_time>'2024-01-01')filtered_AJOINBONfiltered_A.id=B.aid;

核心思路：尽早过滤，减少中间数据量。

技巧四：EXISTS比IN更高效

判断"是否存在"的场景，子查询结果集大的时候EXISTS通常比IN快——EXISTS找到第一个匹配就停，IN要把子查询结果全部算出来。

-- 慢：IN要先算出整个子查询结果集SELECT*FROMordersWHEREuser_idIN(SELECTidFROMusersWHEREstatus='vip');-- 快：EXISTS找到一个就停SELECT*FROMorders oWHEREEXISTS(SELECT1FROMusers uWHEREu.id=o.user_idANDu.status='vip');

不过8.0对IN子查询做了半连接优化，很多时候会自动转成EXISTS。保险起见写成JOIN更可控。

六、在线DDL——大表改结构不锁业务

7×24运行的系统，给大表加字段、改索引不能停服务。

MySQL自带的Online DDL

5.6开始支持，通过ALGORITHM和LOCK选项控制：

-- 加列：不锁表ALTERTABLEhuge_tableADDCOLUMNnew_colVARCHAR(100)DEFAULT'',ALGORITHM=INPLACE,LOCK=NONE;-- 改列类型：可能必须锁表ALTERTABLEhuge_tableMODIFYCOLUMNold_colBIGINT,ALGORITHM=COPY,LOCK=SHARED;

两种算法的区别：

执行前先用SHOW CREATE TABLE看看，或者在测试环境跑一下确认用的是哪种算法。

8.0的Instant DDL

8.0加了一个杀手级特性——Instant DDL。加列操作只改元数据，不碰数据文件，秒级完成：

-- 8.0+，加列用INSTANT算法ALTERTABLEhuge_tableADDCOLUMNnew_colVARCHAR(100)DEFAULT'',ALGORITHM=INSTANT;

我之前在一张3亿行的表上加列，Online DDL跑了40分钟，换成Instant不到1秒。但Instant有限制——只能加列、改列默认值这些轻量操作，改列类型还是得走INPLACE或COPY。

第三方工具

Online DDL对大表还是会占不少IO。更稳妥的选择是pt-online-schema-change或gh-ost：

• pt-osc：通过触发器实现，原表的增删改同步到临时表
• gh-ost：通过binlog实现，不依赖触发器，对主从复制更友好

七、用户自定义变量——8.0之前的黑魔法

这个技巧比较老派，8.0有了窗口函数之后用得少了。但了解一下没坏处，面试偶尔会问，而且有些老项目还是5.7。

模拟行号

-- 给每行打个序号SELECT@rownum:=@rownum+1ASrow_num,name,salaryFROMemployees,(SELECT@rownum:=0)initORDERBYsalaryDESC;

8.0直接用ROW_NUMBER() OVER (ORDER BY salary DESC)就行了。

计算行间差值

-- 日环比增长率SELECTsale_date,daily_amount,@prevASprev_day,ROUND((daily_amount-@prev)/@prev*100,2)ASgrowth_rate,@prev:=daily_amountFROMdaily_sales,(SELECT@prev:=0)initORDERBYsale_date;

8.0用LAG()窗口函数一行搞定。

用户变量的坑

用户变量不是SQL标准，有个很大的坑——执行顺序不确定。优化器可能按它自己的顺序处理，变量的值就乱了。

-- 这样写结果可能不对SELECT@a:=@a+1ASrn,nameFROMemployees,(SELECT@a:=0)init-- 不加ORDER BY的话，每次执行顺序可能不同-- 加了ORDER BY，@a的赋值顺序也可能不是你期望的

8.0环境下，老老实实用窗口函数。用户变量留着维护老代码就够了。

八、生成列和函数索引——索引的新玩法

这个之前完全没讲过，是我觉得特别实用的一个特性。

生成列

生成列的值不是你手动写入的，而是由其他列的表达式自动计算。

-- 经常需要按全名查询ALTERTABLEusersADDCOLUMNfull_nameVARCHAR(255)GENERATED ALWAYSAS(CONCAT(first_name,' ',last_name))STORED,ADDINDEXidx_full_name(full_name);-- 现在这个查询能走索引了SELECT*FROMusersWHEREfull_name='John Doe';

两种类型：

大多数情况用STORED，因为需要在上面建索引。VIRTUAL列不能建普通索引（8.0+可以建函数索引）。

函数索引

8.0开始支持直接在表达式上建索引，不用绕一圈建生成列了：

-- 8.0+ 直接建函数索引CREATEINDEXidx_lower_emailONusers((LOWER(email)));-- 这个查询能走索引了SELECT*FROMusersWHERELOWER(email)='test@example.com';

JSON字段的函数索引也是这个原理：

-- 在JSON字段的某个路径上建索引CREATEINDEXidx_json_colorONproducts((attributes->>'$.color'));-- 能走索引的查询SELECT*FROMproductsWHEREattributes->>'$.color'='red';

本质上生成列和函数索引解决的是同一个问题：怎么在"计算出来的值"上建索引。8.0之前只能用生成列绕路，8.0之后函数索引更简洁。

九、三个容易忽略的优化技巧

技巧一：深分页优化

跳过100万行取20行，MySQL要扫描100万+20行再扔掉前面的。

-- 问题写法SELECT*FROMordersORDERBYidDESCLIMIT1000000,20;-- 游标分页（推荐）SELECT*FROMordersWHEREid<1000020ORDERBYidDESCLIMIT20;-- 延迟关联（产品非要跳页时用）SELECTo.*FROMorders oINNERJOIN(SELECTidFROMordersORDERBYidDESCLIMIT1000000,20)tONo.id=t.id;

游标分页性能好，但前端得改成"加载更多"模式。微信朋友圈和Twitter就是这么做的。

技巧二：OR改写UNION

OR条件可能让MySQL放弃索引。

-- 可能全表扫描SELECT*FROMusersWHEREname='张三'ORemail='zhangsan@test.com'ORphone='13800138000';-- 改成UNION，每个分支走各自索引SELECT*FROMusersWHEREname='张三'UNIONSELECT*FROMusersWHEREemail='zhangsan@test.com'UNIONSELECT*FROMusersWHEREphone='13800138000';

前提是三个字段都有独立索引。

技巧三：优化器选错索引怎么办

有时候MySQL会选错索引。先ANALYZE TABLE更新统计信息，如果还不行，可以用FORCE INDEX：

SELECT*FROMordersFORCEINDEX(idx_user_time)WHEREuser_id=10086ANDcreate_time>'2024-01-01';

FORCE INDEX是最后手段。用了之后表结构或数据分布变了，这个hint可能反而有害。用之前先确认执行计划确实选错了。

避坑清单

总结

10个技巧按"解决什么问题"串一下：

看懂查询→ EXPLAIN（先看再动手）

索引进阶→ 覆盖索引（不回表）、索引下推（少回表）、前缀索引（长字段）

复杂查询→ 窗口函数（跨行计算）、CTE（拆解逻辑）、JSON（动态字段）

JOIN→ 确保有索引、小表驱动大表、先过滤再JOIN

表结构变更→ Online DDL、Instant DDL、pt-osc/gh-ost

冷门但好用→ 用户自定义变量（老代码维护）、生成列和函数索引（计算字段加索引）

SQL进阶不是一个点的突破，是工具箱越来越丰富。同样的查询需求，新手写出来全表扫描，老手三行搞定还走覆盖索引。差别不在智商，在于你知道多少种写法。

我是数据库小学妹，咱们下篇见 👋