企业官网建设流程全解析

1. 建表之前先想清楚：存储引擎、字符集和列类型往往决定了后续查询的基础

1.1 存储引擎的选择，不能只会“用默认”

很多教程直接说 InnoDB 是默认引擎，用它就行。这句话放在今天基本没毛病，但作为开发至少要明白为什么是 InnoDB：它支持事务、行级锁、外键约束和崩溃后的自动恢复。MyISAM 虽然读性能在某些场景下看起来不差，但不支持事务，表锁也会让并发写变得非常难受。我做过的某个老项目里有一张统计表用的还是 MyISAM，每到业务高峰期一执行 UPDATE，整张表被锁住，读请求全部排队，最后只能做迁移。

所以我的建议是：没有特殊理由，新表一律 InnoDB。如果你遇到某种纯读、不更新、数据量又很大的报表表，MyISAM 也不是不能用，但 MySQL 8.0 之后 MyISAM 的存在感越来越弱，与其纠结引擎不如考虑用 InnoDB 配合只读实例或者归档表。

CREATE TABLE `orders` ( `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT, `order_no` VARCHAR(32) NOT NULL, `user_id` INT UNSIGNED NOT NULL, `amount` DECIMAL(12,2) NOT NULL DEFAULT 0.00, `status` TINYINT NOT NULL DEFAULT 0, `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`), UNIQUE KEY `uk_order_no` (`order_no`), KEY `idx_user_id` (`user_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;

建表时除了引擎，我还习惯把ROW_FORMAT也确认一下。默认的DYNAMIC行格式对 VARCHAR 和 BLOB 更友好，8.0 里基本不需要手动指定。但如果你用的是 5.7 的老实例，又涉及大字段，建议关心一下行溢出问题，别等到插入大文本时才报错。

1.2 字符集与排序规则：utf8mb4 不是终点

字符集选错是最难事后补救的问题之一。早期很多系统用utf8，结果发现它根本不是真正的“全 Unicode”，一个汉字占 3 字节，且存不了 emoji。后来大家统一换成了utf8mb4，但换了字符集不代表排序规则也选对了。

常见排序规则有utf8mb4_general_ci、utf8mb4_unicode_ci和utf8mb4_0900_ai_ci。general_ci更快但排序精度低；unicode_ci在 5.7 时代是比较均衡的选择；8.0 默认的0900_ai_ci更符合现代 Unicode 排序规则。如果你用到中文排序、特殊字符比较，最好直接使用 8.0 的默认规则；如果是 5.7 迁移过来的旧库，也不要盲目一把梭，先看业务里有没有对排序规则敏感的地方。

还有一个经常被忽略的点：建库时设置了 utf8mb4，不代表建表时一定继承。有时候 DDL 脚本里单独写了CHARSET=gbk，上线后数据写入正常，但联表查询时两边字符集不一致，就会导致索引失效甚至报错。我习惯在建库、建表、连接参数三个层面都统一确认字符集，连接参数里写：

character_set_client=utf8mb4 character_set_connection=utf8mb4 character_set_results=utf8mb4

宁可多写几行，也不要留隐患。

1.3 列类型选不对，后面查询怎么优化都别扭

列类型靠的是经验积累。我见过有人把所有数值都存成 VARCHAR，理由是可扩展性更好，结果做范围查询时全表扫描，慢得离谱。正确的做法是：整数用INT或BIGINT，大金额用DECIMAL，定长短字符串可以考虑CHAR，变长字符串用VARCHAR，时间用DATETIME或TIMESTAMP。

关于时间类型多说一句：TIMESTAMP有 2038 年问题，而且会自动受时区影响；DATETIME是纯粹的日期时间，不涉及时区换算，8.0 里还能自定义精度。如果业务有跨时区需求，建议统一存 UTC 再在应用层转换，或者用DATETIME存与业务无关的时间点。最忌讳的是同一个系统里一部分表用INT存时间戳，一部分用VARCHAR存日期字符串，查询时互相转换，索引全废。

金额更不要图省事用FLOAT或DOUBLE，精度损失是实打实的，累计计算时差几毛钱的情况经常发生。DECIMAL(12,2)虽然消耗空间多一些，但钱相关的东西不能出半点差错。主键方面，线上业务我几乎都用BIGINT UNSIGNED AUTO_INCREMENT而不是INT，因为现代业务的数据增长比想象中快，INT 上限 21 亿，看着多，但分库分表、中间表、日志表一叠加，很容易提前撞墙。

2. 表结构变更里的明白账：ALTER、DROP 和 TRUNCATE 的使用边界

2.1 ALTER TABLE 不是瞬间完成的事

很多人以为执行ALTER TABLE就像跑一个普通查询，很快就能结束。实际上在大表上做 DDL，尤其是 5.7 及更早版本，很可能导致全表复制。比如给一张几百万行的表增加字段，如果用了旧算法，InnoDB 会重建整张表，期间短则几秒，长则几分钟甚至更久，应用侧写操作直接被阻塞。

MySQL 8.0 里的很多 DDL 支持ALGORITHM=INPLACE和LOCK=NONE，意思是尽量原地修改、不加锁。但这不是所有操作都支持的。给大表加字段时，我会写成：

ALTER TABLE `orders` ADD COLUMN `pay_time` DATETIME NULL DEFAULT NULL, ALGORITHM=INPLACE, LOCK=NONE;

这里LOCK=NONE表示允许 DML 并发，至少不会把线上读写完全堵死。即使在 8.0 里，某些操作比如修改主键、调整列顺序，依然可能需要重建表。所以上线前最好先在小表或者测试环境跑一遍EXPLAIN不适用，应该用SHOW PROCESSLIST观察实际耗时，或者干脆借助第三方工具在业务低峰期执行。

有一次我处理一张千万级流水表的索引调整，直接在白天执行ALTER TABLE ... ADD INDEX，虽然 InnoDB 支持在线加索引，但索引构建期间 IO 压力很大，主库延迟瞬间飙升，从库落后了几分钟，差点引发读写分离的数据不一致。从那以后，大表 DDL 我一律走流程：先估算数据量，再评估 IO 和主从延时，最后挑低峰期小步执行。

2.2 修改列类型和列名时，最容易踩兼容性坑

ALTER TABLE ... MODIFY COLUMN和ALTER TABLE ... CHANGE COLUMN都能改列，但 CHANGE 后面的写法必须完整，否则容易把其他属性改没。比如：

ALTER TABLE `user` MODIFY COLUMN `nickname` VARCHAR(128) NOT NULL DEFAULT '';

如果原来的列允许 NULL，这条语句会保留 NULL 属性；但有些操作会隐式调整默认值。我建议在修改关键列前，先SHOW CREATE TABLE把完整定义拿出来，再拼新的 DDL，不要凭印象写，否则上线后可能发现默认值丢了或者注释没了。

还有个容易忽略的地方：修改列类型后，索引是否还能继续使用。比如原来VARCHAR(20)存手机号，改成VARCHAR(64)通常没问题，但如果是将字符串列改成数值类型，或者反过来，就算数据能转过去，索引也大概率失效。修改列类型之前，我会先查一下这个列上有哪些索引，评估影响范围。

2.3 TRUNCATE、DROP、DELETE 的边界感

这三个操作杀伤力完全不同。DELETE走事务，可以逐行删除并触发触发器，速度最慢；TRUNCATE是快速清空表，但会隐式提交，无法回滚；DROP直接删除表结构。实际工作中最容易出事故的是把TRUNCATE当成DELETE用，特别是测试环境连着生产库的时候。

有一次运维不小心在生产库执行了TRUNCATE，当时没有开启 binlog 的完整备份，结果只能从前一天夜里恢复，丢失了当天的全部数据。做任何会改变数据结构的操作前，至少确认三件事：当前库环境、用户权限、是否有最近备份。控制台工具里尽量勾选“阻止危险操作”，连接字符串里也尽量不要用超级管理员账号执行业务 DDL。

3. SELECT 查询的编写顺序和执行顺序：理解它才算入门

3.1 写法顺序不等于执行顺序

很多人学 SQL 先背语法顺序：SELECT在最前面，FROM在中间，LIMIT在最后。但数据库真正执行的时候，顺序完全不同。一条完整查询的逻辑执行顺序是：先确定FROM和JOIN的源数据，然后WHERE过滤，再GROUP BY分组，接着HAVING过滤分组结果，之后才是SELECT投影，最后ORDER BY排序和LIMIT分页。

正是因为WHERE在SELECT之前执行，MySQL 不允许在WHERE里直接使用SELECT中定义的别名。而ORDER BY在SELECT之后执行，所以可以用别名排序。举个例子：

SELECT user_id, COUNT(*) AS order_cnt FROM orders WHERE status = 1 GROUP BY user_id HAVING COUNT(*) >= 10 ORDER BY order_cnt DESC LIMIT 20;

这段 SQL 中，HAVING不能写成HAVING order_cnt >= 10，但ORDER BY可以写order_cnt。如果改成HAVING order_cnt >= 10，有些版本会报错，有些版本虽然靠非标准扩展能跑，但行为不可预测。写代码图的是稳，不要依赖这种方言特性。

3.2 WHERE、HAVING 和 ON 的分工不一样

把过滤条件写错位置，结果可能不对，性能也可能下降。ON是连接时决定的，WHERE是连接完成后过滤的，HAVING是分组之后过滤的。内连接时ON和WHERE结果通常一样，但左连接时把它们换位置，含义就完全不同。

比如查用户和订单，想要“所有用户及其已支付订单数”，如果过滤条件status = 1写在WHERE里，左连接会被改写成内连接，没有订单的用户就查不出来了。正确写法是把订单的状态条件放到ON子句里，然后再对用户做左连接过滤。这种细节写错不会报错，但报表数据就是缺行，排查起来非常麻烦。

HAVING适合过滤聚合结果，比如只要订单数大于 10 的用户。但如果能在WHERE阶段就把普通列过滤掉，就不要把条件拖到HAVING，因为前者提前减少了分组数量，效率更好。把WHERE能干的活留给HAVING，是新手最常见的性能问题之一。

3.3 别把 COUNT(*) 和 COUNT(某列) 混为一谈

COUNT(*)统计的是结果集行数，会被优化器特殊处理，速度通常不慢；COUNT(某列)统计的是该列非 NULL 的行数。如果某列允许 NULL，两个函数结果完全不同。还有不少人喜欢用COUNT(DISTINCT column)，这个本身就是全列扫描加去重，数据量大时要谨慎，别在循环里反复查。

实际开发中我建议：需要行数就写COUNT(*)，不要为了“显得精确”去写COUNT(1)，两者没本质区别，但团队统一用COUNT(*)更省心。统计某个字段去重数时，先确认字段是否允许 NULL，NULL 不参与COUNT(DISTINCT)统计，容易导致数据偏小。

4. 多表连接的原理与聚合统计的正确打开方式

4.1 连接查询的本质是逐步缩小结果集

在关系型数据库里，多表 JOIN 本质上是对多个表的记录做匹配，匹配条件越充分，中间结果越小，查询越快。很多人一遇到连接查询，就先写 JOIN 然后 WHERE 里随便扔条件，也不管条件能不能用上索引，最终执行计划里显示全表扫描。

我常用的一个习惯是：写 JOIN 前先想连接列是否已有索引。例如订单表和用户表通过user_id关联，user_id在订单表上是普通索引，在用户表上是主键，这个连接通常很快。如果连接列没有任何索引，MySQL 只能做嵌套循环，每次拿外层表的行去内层表全表扫，数据量一大就是灾难。

4.2 左连接和内连接的选择要看业务语义

INNER JOIN取交集，LEFT JOIN保留左侧表的全部记录。业务上要“所有用户及其订单数”就必须用左连接，要“只有下过订单的用户”可以用内连接。优化器有时候会改写语句，但业务语义最好不要依赖优化器猜。

另外，多个条件同时存在时，能前置到ON的条件就放在ON里。比如只统计已支付订单：

SELECT u.id, COUNT(o.id) AS paid_order_cnt FROM user u LEFT JOIN orders o ON o.user_id = u.id AND o.status = 1 GROUP BY u.id, u.nickname;

这里的o.status = 1放在ON里，左侧用户不会因为没订单而丢失。如果把它放到WHERE，用户表里没下过单的行会被过滤掉，结果范围就变了。

4.3 GROUP BY 聚合统计中的几个硬性约束

MySQL 5.7 之后默认开启ONLY_FULL_GROUP_BY，意思是SELECT中出现的非聚合列，都必须包含在GROUP BY中。拿上面的例子说，u.nickname必须加进GROUP BY，否则报错。这是好事，能避免很多语义混乱。

但有些老项目关闭了这个模式，同一句 SQL 在不同环境可能得出不同结果。团队开发时，我建议建库时确认sql_mode里包含ONLY_FULL_GROUP_BY，不要让开发环境默许不合法写法。统计场景尽量简单直接：GROUP BY的列越少，分组性能越好；聚合函数不要套在函数里，比如DATE(created_at)做分组，会导致索引失效。

聚合之后想排序，ORDER BY只能基于分组列或聚合列。用别名排序虽然可以，但可读性差，我会在查询里先明确写出聚合列，再排序。

4.4 EXISTS 与 IN 的取舍不是玄学

当子查询结果集很小且外层表很大时，IN通常表现不错；当子查询结果集很大、外层表很小，EXISTS往往更合适。理由是IN会先执行子查询生成结果集合，再逐行匹配；EXISTS是外层表每行去探测是否存在，命中即停。

不过 MySQL 优化器在 5.6 之后做了很多改写，两者性能差距越来越小。我个人的习惯是：可读性优先，能用子查询就尽量用JOIN，能避免嵌套子查询就避免。过于关心IN和EXISTS微小的性能差异，不如先看执行计划确认是否走了索引。

5. 查询优化的完整链路：执行计划、索引失效与一次慢查询排查

5.1 EXPLAIN 输出到底要看哪几列

拿到一条慢 SQL，第一步永远是加EXPLAIN看执行计划。不要靠猜。重点看四个关键列：

• type：从好到差大致是const、eq_ref、ref、range、index、ALL。看到ALL基本就是全表扫描，必须警惕。
• key：实际使用的索引名。key为 NULL 说明没用到任何索引。
• rows：预估扫描行数。这个数字不是实际值，但量级能说明问题。
• Extra：里面出现Using filesort或Using temporary时，要重点分析排序和分组是否需要额外优化。

EXPLAIN SELECT user_id, COUNT(*) FROM orders WHERE status = 1 GROUP BY user_id;

如果结果里type是ALL，rows显示几十万，说明这条 SQL 扫描了整张表。这时候第一反应不是去改 SQL，而是看status上有没有索引，没有就加；如果走idx_user_id还能覆盖分组，效果更好。

5.2 索引失效的几个高频场景

很多人以为只要建了索引就一定能用上，实际上索引失效太常见了。最典型的五种场景：

1. 索引列参与函数运算。比如WHERE DATE(created_at) = '2024-01-01'，索引列被函数包裹，优化器很难使用索引。正确写法是范围条件：created_at >= '2024-01-01 00:00:00' AND created_at < '2024-01-02 00:00:00'。
2. 隐式类型转换。比如mobile是 VARCHAR 列，查询条件写成mobile = 13812345678，数字类型会转成字符串再比较，导致索引失效。手机号、订单号这类字段，查询条件必须带引号。
3. 前导模糊匹配。LIKE '%关键词'无法使用普通索引，LIKE '关键词%'可以。搜索场景优先考虑全文索引或搜索引擎，别硬扛。
4. OR 条件。多个条件用 OR 连接时，如果其中一个字段没有索引，整个查询可能退化。建议拆成两条 SQL 或改成 UNION。
5. 联合索引未满足最左前缀。比如联合索引(user_id, status)，查询条件只有status，索引无效；只有加上user_id才会走索引。

5.3 一次慢查询的完整排查过程

有一次业务反馈报表接口越来越慢，到了每秒请求量上去后直接卡死。我拿到 SQL 后第一件事是EXPLAIN，发现核心问题出在一张 200 万行的订单表上，查询条件：

SELECT order_no, amount, pay_time FROM orders WHERE status = 2 AND pay_time BETWEEN '2024-06-01' AND '2024-06-30' ORDER BY pay_time DESC LIMIT 50;

执行计划显示type=ALL，rows=2000000，Extra=Using filesort。这条 SQL 的过滤条件status和pay_time都没有合适的索引，排序也只能在内存里做。我当时加了联合索引：

ALTER TABLE `orders` ADD INDEX `idx_status_paytime` (`status`, `pay_time`);

加完后再次 EXPLAIN，type变成range，rows从 200 万降到几万，Using filesort消失。上线后接口耗时从秒级降到毫秒级。这个案例足够说明，写 SQL 时不看执行计划，就是拿生产环境做实验。

5.4 覆盖索引与回表

InnoDB 的二级索引叶子节点存的是索引列和主键值。查询时如果索引列覆盖了所有需要返回的字段，就不需要回表，速度极快。比如查询只需要user_id和status，而联合索引(user_id, status)刚好覆盖这两个字段，那么就属于覆盖索引扫描。

反过来，查询需要返回amount、pay_time，而索引只覆盖了user_id和status，那么每行都要回表查一次主表数据。数据量大时回表代价很高。设计索引时，如果某个查询反复出现，可以尝试把查询字段加入联合索引末尾，做成覆盖索引，但也要权衡写入开销，不能每个字段都加。

6. 日常使用中值得长期坚持的几个习惯

6.1 深分页问题远比想象中常见

LIMIT 100000, 20这种写法，前面的 10 万行也会被扫描一遍，数据越大越慢。正确做法是记住上一页最后一条记录的排序字段值，用大于或小于条件定位：

SELECT id, order_no FROM orders WHERE id > 123456 ORDER BY id LIMIT 20;

这样每次查询只扫描目标范围内的记录。如果业务必须从第 10 万页开始翻，那基本说明数据展示逻辑有问题，应该考虑加筛选条件让用户缩小范围。

6.2 NULL 值的处理要做成团队约定

NULL 是查询中非常容易翻车的话题。WHERE column = NULL永远查不到数据，必须写IS NULL。COUNT(column)不统计 NULL。索引虽然可以包含 NULL，但多个 NULL 在唯一约束里的行为比较复杂。业务表设计时，能不用 NULL 就不用 NULL，用默认值替代，查询逻辑会简单很多。

6.3 表结构即代码，变更必须走版本管理

在我工作过的团队里，表结构变更都要求写到迁移脚本中，和代码一起提交、评审、执行。线上库直接手敲 ALTER 虽然是某些公司的日常，但时间一长没人知道哪张表被改过，列 AA 和列 BB 对不上，索引冗余严重。把表结构变更纳入版本管理，至少能解决“为什么这个环境没有这个列”的经典问题。

6.4 慢查询日志和监控必须早做

慢查询日志不是等出故障才看，而是日常就要盯。开启slow_query_log后，定期把慢 SQL 捞出来分析，结合执行计划做优化。很多查询问题是可以提前发现的：深夜批处理跑一小时，新增业务功能上线前没有压测，这些都可以通过慢日志暴露。MySQL 8.0 的 Performance Schema 也能提供更细粒度的统计，但最直观的入口永远是慢查询日志。

最后再分享一个习惯：我每次写完一条稍微复杂的 SQL，都会问自己两个问题——它需要扫描多少行？它能不能少读点数据？这两个问题想清楚了，MySQL 的表操作和查询就不太可能给你“惊喜”。