企业官网建设流程全解析

1. 优化前的性能评估与基线建立

先说个真实场景。某天深夜，线上系统突然告警，接口平均响应时间从200ms飙到3秒以上。我登录数据库一看，CPU接近100%，磁盘IO打满，慢查询日志里全是同一个表的全表扫描。当时第一反应是"加索引"，但冷静下来想了想，如果不知道瓶颈到底在哪，加索引也像是蒙着眼睛开枪。

做数据库优化，我从来劝大家别上来就动SQL、动索引，先花一两个小时把基线建起来。基线是什么？就是当前系统的性能底数。包括核心接口的响应时间、数据库的连接数使用率、慢查询的数量和分布、QPS和TPS的波动范围。没有这些数据，你根本说不清楚优化前后到底提升了多少。

1.1 慢查询日志与性能基线

大多数MySQL实例默认开着慢查询日志，但很多人忽略了一个关键参数：long_query_time。默认值是10秒，说句难听的，10秒以上的查询才记录，这种系统基本上已经处于不可用状态了。在生产环境，我一般把这个值调到1秒，如果是核心交易链路，甚至调到0.5秒。

-- 查看当前慢查询配置 SHOW VARIABLES LIKE 'slow_query_log'; SHOW VARIABLES LIKE 'long_query_time'; -- 动态调整（重启失效，需要写进配置文件） SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 1;

日志记录只是第一步，关键在分析。我推荐直接用mysqldumpslow工具，它能按执行次数、耗时、锁等待时间排序，把慢查询日志里的SQL归类汇总，一眼就能看出哪些SQL是“罪魁祸首”。别对着日志文件手动翻，几千条SQL翻到天亮也看不完。

# 按平均查询时间排序，查看前10条 mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

建好基线之后，你要能回答这几个问题：高峰期QPS是多少，平均响应时间是多少，慢查询占比多少，哪些表是热点表。这些数字就是后续优化的“尺子”，每次改动上线后拿出来对比，客观判断改动到底有没有效果。

1.2 执行计划的基础解读

慢查询日志只告诉你“谁慢”，没说“为什么慢”。要查根因，就得靠执行计划，也就是EXPLAIN命令。我见过不少开发同学会敲EXPLAIN SELECT ...，但看执行计划只会看“有没有用索引”这一条，其实里面信息量很大。

EXPLAIN SELECT order_id, user_id, amount FROM orders WHERE user_id = 10086 ORDER BY created_at DESC LIMIT 20;

执行计划里面，我最关注这几个字段：

• type：访问类型，从好到差依次是const、eq_ref、ref、range、index、ALL。看到ALL就要警惕，这是全表扫描。看到index也别高兴太早，它遍历的是整个索引树，数据量大了同样要命。
• key：实际使用的索引名。如果为NULL，说明没走索引。
• rows：预估扫描行数。这个数字和实际返回行数差距越大，说明优化空间越大。
• Extra：这里藏了很多关键信息。看到Using filesort，说明排序没走索引，要在内存或磁盘里额外排序。看到Using temporary，说明用了临时表，通常伴随GROUP BY或者DISTINCT。看到Using index，这是好事，代表覆盖索引。

举一个我之前处理过的案例。某订单查询接口，SQL很简单，就是按用户ID查订单列表。执行计划显示type=ALL、rows=1200万，但表上明明有user_id的索引。后来查了表结构才发现，user_id字段是VARCHAR类型，SQL里传的是数字，MySQL做了隐式类型转换，索引直接失效。这类问题在业务代码里非常隐蔽，排查起来很费劲，后面我会专门写一节。

2. 索引设计实战：从单列到联合索引

索引是MySQL性能优化的核心手段，没有之一。但索引不是银弹，用得好是加速器，用不好就是负担。每个索引都占磁盘空间，每次INSERT、UPDATE、DELETE都要维护索引树。所以索引设计的原则就一句话：用最少的索引覆盖最多的查询场景。

2.1 索引失效的常见场景

先盘点一下我这些年在生产环境里踩过的索引失效坑，这些坑几乎每个都能写进面试题，但实际工作里照样有人踩。

第一，函数操作导致索引失效。比如WHERE DATE(created_at) = '2024-01-01'，看起来没毛病，但查询优化器没法直接利用created_at上的索引，因为它需要先对每一行计算DATE()函数，再和常量比较。正确写法是范围查询：WHERE created_at >= '2024-01-01' AND created_at < '2024-01-02'。

第二，隐式类型转换。开头提到的例子，字段是VARCHAR，条件里写数字，MySQL会先把字段转成数字再比较，索引就废了。反过来，字段是数字类型的，条件里写字符串，同样有问题。解决办法是统一类型，代码里传参和数据库字段类型保持一致。

第三，前导模糊查询。WHERE name LIKE '%张'，这种查询没法用索引，因为B+树是按前缀有序排列的，你想从中间开始匹配，树就不知道该往左走还是往右走。改成WHERE name LIKE '张%'就能走索引了。

第四，联合索引违反最左前缀原则。这个单独展开一下。

第五，OR条件跨字段。WHERE a = 1 OR b = 2，如果a和b各自有单列索引，优化器可能选择索引合并（index merge），但很多情况下它会放弃索引走全表扫描。优先改写成UNION ALL，或者让查询条件尽量收敛在同一个索引上。

2.2 联合索引的最左前缀原则

联合索引，也叫复合索引，是最容易用错、也最能体现设计功力的地方。它的底层原理就是按照字段顺序逐级排列，就像查字典，先按拼音首字母排，再按第二个字母排。最左前缀原则指的是：查询条件从索引的最左列开始连续匹配，才能用到这个索引。

举个例子，表结构里有个联合索引(user_id, status, created_at)，能走索引的查询组合有：

• WHERE user_id = 1
• WHERE user_id = 1 AND status = 0
• WHERE user_id = 1 AND status = 0 AND created_at > '2024-01-01'

但下面这些就发挥不出联合索引的优势了：

• WHERE status = 0：跳过了最左列user_id。
• WHERE created_at > '2024-01-01'：跳过前两列。
• WHERE user_id = 1 AND created_at > '2024-01-01'：中间跳过了status。

第三个情况有个细节很多人不知道：虽然联合索引的顺序是(user_id, status, created_at)，当你只用user_id和created_at查询时，MySQL其实会用user_id来定位，再对结果做created_at的过滤，但created_at没法用于索引排序。这时候如果数据量大，你会发现WHERE user_id = 1 AND created_at > '2024-01-01'的性能反而不如把索引设计成(user_id, created_at)的情况。

那联合索引的字段顺序怎么定？我的经验是两条原则：

1. 区分度高的放前面。区分度指的是字段值的离散程度，比如user_id通常比status区分度高，因为用户ID的值非常分散，而status可能只有0和1两个值。离散度高意味着索引树能快速筛掉大量数据。

2. 查询频率高的、等值条件多的放前面。如果业务里80%的查询都是WHERE user_id = ? AND status = ?，那把等值字段放前面，这样能最大程度利用索引的等值匹配特性。

2.3 覆盖索引与回表优化

InnoDB的索引结构分为聚簇索引和二级索引。聚簇索引就是主键索引，叶子节点存的是整行数据。二级索引的叶子节点存的是主键值，所以通过二级索引查数据时，先找到主键，再回聚簇索引查整行，这个过程叫回表。

回表不是每次都能避免，但能避免就避免，因为每次回表都是一次随机IO。怎么避免？靠覆盖索引。覆盖索引就是指查询的所有字段都能从二级索引里直接拿到，不需要回表。

打个比方，你要查某本书的价格，书的目录页就印着价格，你翻目录就能看到，不用把整本书拿下来翻。这就是覆盖索引的威力。

-- 假设有一个联合索引 (user_id, created_at) -- Q1: 需要回表 SELECT order_id, amount FROM orders WHERE user_id = 1 ORDER BY created_at DESC LIMIT 10; -- Q2: 覆盖索引，不需要回表 SELECT user_id, created_at FROM orders WHERE user_id = 1 ORDER BY created_at DESC LIMIT 10;

Q2查的user_id和created_at都在联合索引里，而且联合索引天然按(user_id, created_at)排序，连排序都直接走索引了，Extra里不会出现Using filesort。Q1要查order_id和amount，索引里没有，必须回表拿数据。

这里有个设计技巧：与其每次凑覆盖索引，不如在建索引时顺带把高频查询要返回的字段加进去。比如上面这个例子，如果很多查询都要返回order_id和amount，可以考虑把索引设计成(user_id, created_at, order_id, amount)。这就是“索引扩展”的思路，代价是索引体积变大、写入变慢，需要权衡。

注意：索引不是越多越好。一张表超过5到6个索引，写入性能就会明显下降。经常被忽略的是，每个索引在INSERT时都要同步维护，索引太多等于每次写入都背着好几个包袱。

3. 分库分表：时机、策略与落地

索引和SQL优化能解决的问题，本质上是把单库单表的性能压榨到极限。但业务一旦做大，数据量破亿或者QPS上千，再优秀的SQL也扛不住物理资源的边界。这时候就得认真考虑分库分表了。

3.1 分库分表的触发时机与误区

什么时候该分库分表？先说一个反模式：一上来就分，不管数据量多少。我见过不少团队，数据量才几百万，单表查询响应毫秒级，代码里已经写好了分库分表中间件的接入层，活活把系统复杂度提升了几个档次。

分库分表要考虑的触发条件主要有这几个：

注意上面的数字是经验值，不是绝对标准。SSD普及之后，单表能扛的数据量比机械硬盘时代大了很多；但业务的查询模式也很关键，如果全是主键点查，2000万行的表照样毫秒级。如果是大批量范围扫描加聚合，500万行就能把CPU打满。

分库分表和索引优化的优先级应该怎么排？我的建议非常明确：先把索引优化做到极致，再做SQL改写和缓存，最后才考虑分库分表。分库分表引入了分布式事务、跨库查询、全局主键、数据迁移等一系列复杂度，它是压轴手段，不是起手式。

3.2 垂直分库与水平分表的取舍

分库分表其实包含了两件事：分库和分表，又可以细分为垂直分库、水平分库、垂直分表、水平分表。很多人把它们混在一起说，我先把概念理清楚。

垂直分库，按业务模块拆。比如将用户、订单、结算放在不同的数据库实例。说白了就是让不同业务互不干扰，订单量大不影响用户登录。它解决的是“不同业务争抢同一份数据库资源”的问题。

垂直分表，按字段拆。把一张宽表拆成多张窄表，常用字段放一张，大字段（如TEXT、BLOB）和低频字段放另一张。它解决的是“单行数据太大、行数不多但表体积爆表”的问题。注意，垂直分表在同库内进行，操作起来最轻量，很多团队连这一步都不做就直奔水平分表，这是本末倒置。

水平分表，按数据行拆。把同一张表的数据按照分片键的规则，散落到多张结构相同的表里。比如订单表按用户ID哈希分到16张表，每张表的数据量只有原来的十六分之一。这是应对单表数据量过大的核心手段。

水平分库，则是把分片后的表再分布到不同的物理数据库实例上，解决的是“单实例CPU和内存款不够用”的问题。

怎么选？我的经验是：优先水平分表，因为成本最低、应用改动最小；如果单实例资源已经扛不住，再水平分库；业务模块之间耦合度低、各自访问流量差异悬殊，才考虑垂直分库。垂直分库一旦实施，跨库事务就来了，代价非常大。

3.3 分片键的选择与计算

分库分表成功与否，分片键占80%的决定权。分片键选错了，后患无穷。分片键怎么选，核心标准就三条：均匀性、查询亲和性、不可变性。

均匀性是指这个键的取值分布要均匀，能让数据平均散落到各分片。如果分片键只有0和1两个值，那分16张表，前两张表爆满，后十四张表空转，等于没分。

查询亲和性是指业务里最核心的查询维度要能在这个键上收敛。比如电商系统，用户的订单查询是高频场景，按user_id分片，查询某个用户的订单列表只需要访问一个分片，这就是亲和性。如果按order_id分片，查用户订单就得跨16个分片聚合，性能反而更差。

不可变性是指这个字段值一旦赋值就不怎么变。别拿地址、手机号这种会变的字段做分片键，改一次数据等于要迁移一次分片。

确定了分片键，接下来就是分片算法。常见的就三种：

1. 范围分片。比如按时间按月分表，按ID区间分库。优点是实现简单，方便按时间归档。缺点是可能数据倾斜，热点月写入量大，冷月几乎没人访问。

2. 哈希取模分片，也叫Hash分片。shard_id = hash(shard_key) % shard_count。优点是分布均匀。缺点是扩缩容时要迁移大量数据，因为shard_count变了，取模结果全变。

3. 一致性哈希分片。把分片节点分布在哈希环上，每个键顺时针找到最近的节点。优点是扩缩容只影响相邻节点的数据。缺点是查询时需要多一跳定位节点，实现复杂。

我个人在生产里用的最多的是哈希取模，因为它查询路径最短、性能最稳定。扩容问题靠“双倍扩容法”缓解：比如从8库扩到16库，只用将每个老分片的数据拆成两半，分别迁到新分片上，避免对所有数据重新取模。

3.4 分库分表后的数据迁移与双写方案

分库分表的改造最难的不是写代码，而是数据迁移过程中保证业务不中断、数据不丢失。这里我分享一个实操过多次的完整流程。

整体思路叫“双写迁移”，核心步骤分四步：

1. 存量数据全量同步。先把老库的全部数据，通过工具（比如mysqldump或者DataX）导入新的分片表。这一步可以离线做，业务无需暂停。

2. 增量数据双写。在代码层面，写操作同时写入老库和新库，用事务保证两边一致性，但以老库为准。这一步持续运行一段时间，让新库追上老库的延迟。

3. 数据校验与回放。通过对比工具检查老库和新库的数据差异，发现漏掉的增量数据就从binlog回放补上。这一步多花时间不要紧，数据一致性是底线。

4. 切换读流量。确认新库数据准确、性能达标后，把读流量切到新库，老库下线或者降级为只读备份。

这套方案看着不复杂，但实操中有几个坑必须提醒你。第一，双写不能简单在两个库里各写一条，必须先写老库拿主键，再拿着主键去写新库，避免两边主键不一致。第二，双向同步会出现循环同步，需要过滤掉对端同步过来的写入。第三，切换流量要分批灰度，先切5%的用户验证稳定性，再逐步放大。

4. 常见问题与排查技巧实录

做系统优化，方案设计好了只是开始，真正磨人的是上线后遇到的那些五花八门的问题。我把这些年踩过坑、填过坑的高频问题整理成一份速查手册，每一条都是真实生产环境验证过的。

4.1 索引优化后查询反而变慢

这种情况一般有两个原因。第一个是统计信息过旧，MySQL优化器根据错误的统计信息选择了错误的执行计划。解决办法是执行ANALYZE TABLE更新统计信息。

第二个是优化器选错了索引。有时候你建了联合索引，但MySQL觉得全表扫描更快，尤其是表比较小时。这时候可以用索引提示（Index Hint）强制走索引：

SELECT * FROM orders FORCE INDEX (idx_user_created) WHERE user_id = 1 ORDER BY created_at DESC LIMIT 20;

注意FORCE INDEX是权宜之计，如果业务SQL经常变，最好还是从索引结构本身找原因。

4.2 深分页查询性能崩塌

分页越往后翻越慢，这是MySQL用户最熟悉的痛点。LIMIT 100000, 20，MySQL先扫描到第100020行，再扔掉前面10万行。扫描的过程是全索引扫描，不是只拿20行那么简单。

一个屡试不爽的优化方案是“延迟关联”或者“书签定位”：

-- 原SQL（深分页很慢） SELECT * FROM orders ORDER BY id LIMIT 100000, 20; -- 优化后（先查主键，再关联回表） SELECT * FROM orders JOIN (SELECT id FROM orders ORDER BY id LIMIT 100000, 20) AS tmp ON orders.id = tmp.id;

原理很简单：子查询只扫描索引树，不访问数据行，扫描成本大幅降低；拿到20个主键后再回表取完整数据。我在一个5000万行的表上做过对比，优化前耗时2.3秒，优化后270毫秒，接近9倍的提升。

如果业务对分页有更高要求，还可以改成“基于游标”的方式：用WHERE id > ? ORDER BY id LIMIT 20代替OFFSET，每次把上次的最后一个ID作为下次查询的起点。缺点是失去了“跳页”能力，适合资讯流、文章列表这类业务。

4.3 分库分表后的全局唯一主键

单表时用AUTO_INCREMENT就行，分库分表后这个方案直接作废，因为每张分表的自增ID都会从1开始，肯定会冲突。全局唯一ID的选型，生产里最常用的是雪花算法（Snowflake ID）和号段模式。

雪花算法生成一个64位的长整型ID，结构大致是：1位符号位 + 41位时间戳 + 10位机器ID + 12位序列号。同一毫秒内同一台机器能生成4096个不重复ID，完全够用。优点是ID有趋势递增性，对索引友好；缺点是强依赖机器时钟，时钟回拨会生成重复ID，实现时要做回拨保护。

号段模式则是提前从数据库取一段ID区间，比如[100000, 100999]，应用本地负责分配这段区间内的值，用完再去数据库取下一段。优点是简单可靠，缺点是ID不连续，业务方如果对ID格式敏感就不适用。

注意：不管用哪种方案，千万别用UUID直接做主键。UUID是字符串，在InnoDB聚簇索引里B+树节点存储空间浪费严重，写入是随机插入，会造成页分裂，性能和存储双输。

4.4 分库分表后的跨库查询与分布式事务

分库分表之后，原来一条SQL能完成的事，现在可能要跨多个库。比如按用户ID分片后，运营后台要查“近30天所有用户的订单汇总”，就不得不扫全部分片再聚合。这类问题没有银弹，常用手段是建一张汇总宽表，用离线任务定期把各分片的数据汇聚过去，供后台查询使用。线上实时查询则严格按分片键收敛到单分片。

分布式事务则更棘手。原来在单库内，一个事务可以同时更新订单表和库存表，分库后这两张表在不同库，普通事务就管不了了。业界方案有两条路：

一条是强一致，基于两阶段提交的分布式事务框架（典型的如Seata的AT模式），实时性高但性能损失明显，不适合高频写入的路径。

另一条是最终一致性，用本地消息表加消息队列实现。核心思想是：业务操作和写消息表放在同一个本地事务里，然后通过MQ异步通知下游执行，下游通过幂等保证不重复处理。

我个人的经验是，核心交易链路尽量保持单库事务，不要为了水平扩展把强一致性的边界打破；非核心链路能异步就异步，把数据一致性变成最终一致。

5. 三个必须养成的优化习惯

写了这么多实战内容，最后分享几个我觉得比任何具体技巧都重要的习惯，这些习惯直接决定了你在数据库优化这条路上能走多远。

第一个习惯：每次优化只改一个变量。很多人喜欢“顺便”把SQL改了、索引加了、参数调了，然后回头看性能提升了50%，但根本说不清楚是哪一步起了作用。这等于没优化，因为下次出了问题你不知道去哪里回滚。我自己的做法是，一次改动一个点，改完跑一轮全量回归测试采集基线对比，确认有效再动下一个。

第二个习惯：把执行计划当成SQL的“体检报告”。每次写完一条有一点复杂度的SQL，习惯性先跑一遍EXPLAIN，看看type是不是ALL、rows有没有爆炸、Extra里有没有Using filesort和Using temporary。我在团队里定了一条规矩：线上代码合入前，涉及数据库查询的SQL必须附上执行计划截图评审。这条规矩看着繁琐，但真的拦住过好几次事故。

第三个习惯：定期清理无用索引。MySQL没有自动化索引使用统计的现成功能，但你可以通过performance_schema里的表来统计索引的实际使用频率，也可以用sys.schema_unused_indexes视图直接列出从未被使用的索引。我建议每季度做一次索引清理，因为那些躺在那里的索引不仅白白占用存储空间，每次写入还在默默增加负担。

优化数据库这件事，说到底不是炫技，而是用最简洁的方式让系统稳定高效地运行。有时候你花一整天调优，最后发现最有效的操作就是给一个高频查询加了一条五六个字的联合索引，或者把一条深分页SQL改写成了延迟关联。这种朴素但扎实的收益，才是我们做这件事的意义所在。