企业官网建设流程全解析

1. MySQL查询缓存为何被彻底移除

MySQL 8.0版本中一个重大变化就是完全移除了查询缓存(Query Cache)功能。这个曾经被广泛使用的特性，现在连配置参数都找不到了。作为从MySQL 5.7升级过来的用户，我第一次发现这个变化时也感到非常意外。

查询缓存的工作原理其实很简单：它会把SELECT语句及其结果存储在内存中，当完全相同的查询再次出现时，直接返回缓存结果。在理想情况下，这可以极大提升重复查询的性能。我在早期的Web应用中就经常依赖这个特性，特别是对那些内容更新不频繁的CMS系统。

但为什么MySQL团队最终决定放弃这个功能呢？经过多年实践，我们发现查询缓存存在几个致命缺陷：

1. 粒度问题：任何相关表的修改都会导致整个查询缓存失效。比如一个百万级用户表，只更新了其中一条记录，所有包含这个表的查询缓存都会被清空。在高并发写入场景下，缓存命中率可能低至1%以下。

2. 锁竞争：查询缓存使用一个全局互斥锁来保护。当缓存失效或更新时，所有查询都会被阻塞。我们曾经有个系统在高峰期因为这个锁导致整个数据库响应变慢。

3. 内存浪费：缓存以SQL文本作为key，这意味着SELECT * FROM users WHERE id=1和SELECT * FROM users WHERE id = 1(多了空格)会被视为不同的查询，导致内存重复占用。

实际经验：在MySQL 5.7时期，我们就建议在高并发写入场景关闭query_cache_type。测试显示，关闭后系统整体吞吐量反而提升了15-20%。

替代方案方面，现在更推荐使用应用层缓存(如Redis)或者ProxySQL这类中间件的查询缓存。它们可以提供更灵活的缓存策略，比如：

• 按业务规则控制缓存粒度
• 支持主动失效机制
• 避免全局锁竞争

2. 子查询优化深分页的实战技巧

深分页是数据库查询中的经典难题。典型场景就是"获取第10000条开始的20条记录"。很多开发者第一反应会写出这样的SQL：

SELECT * FROM large_table ORDER BY id LIMIT 10000, 20;

这个查询在数据量小的时候没问题，但当表达到百万级时，性能会急剧下降。原因是MySQL实际会先读取10020条记录，然后丢弃前10000条。

2.1 子查询优化方案

经过多次实战，我发现最有效的优化方式是使用子查询：

SELECT * FROM large_table WHERE id >= (SELECT id FROM large_table ORDER BY id LIMIT 10000, 1) LIMIT 20;

这个方案的原理是：

1. 子查询只返回id列，避免了全行数据的传输
2. 通过id直接定位到起始位置，然后获取后续记录
3. 利用了主键索引的有序性

在我的测试中，百万级数据表上，原始方法需要2.3秒，而优化后仅需0.02秒，性能提升超过100倍。

2.2 其他优化技巧

除了子查询，还有几种值得考虑的方案：

覆盖索引法：

SELECT * FROM large_table JOIN (SELECT id FROM large_table ORDER BY id LIMIT 10000, 20) AS tmp USING (id);

游标法（适合连续分页）：

SELECT * FROM large_table WHERE id > last_seen_id ORDER BY id LIMIT 20;

每种方案都有适用场景：

• 随机跳页：子查询法最优
• 连续翻页：游标法最佳
• 需要复杂排序：覆盖索引法更灵活

避坑提示：避免在WHERE子句中使用OFFSET，如WHERE id > 10000 LIMIT 20，这种写法会导致索引失效。

3. 自增主键溢出的预防与处理

自增主键(AUTO_INCREMENT)是MySQL最常用的特性之一，但很少有人注意到它可能溢出。我曾在生产环境遇到过INT自增主键即将用尽的情况，当时表已经积累了21亿条记录。

3.1 各整数类型的上限

3.2 溢出处理方案

当自增ID接近上限时，可以采取以下措施：

1. 修改列为BIGINT

ALTER TABLE large_table MODIFY id BIGINT UNSIGNED AUTO_INCREMENT;

注意：对大表执行此操作会锁表，建议在低峰期进行。

2. 重置自增值

ALTER TABLE large_table AUTO_INCREMENT = 1;

这可以循环利用已删除的ID空间，但需确保不会引起冲突。

3. 使用复合主键改用多列作为主键，如(shard_id, local_id)，可以极大扩展ID空间。

3.3 预防措施

• 新建表时预估数据量，大表直接使用BIGINT
• 定期监控自增值：SHOW TABLE STATUS LIKE 'table_name'
• 考虑使用UUID或雪花ID等分布式方案

血泪教训：曾经有个日志表使用INT自增，3年后达到上限，导致写入失败。最终只能新建表并迁移数据，过程非常痛苦。

4. 索引失效的常见陷阱与诊断

即使有经验的开发者也会不小心写出导致索引失效的查询。以下是我总结的最常见陷阱：

4.1 典型失效场景

1. 隐式类型转换

SELECT * FROM users WHERE phone = 13800138000;

如果phone是varchar类型，这会导致索引失效。应该使用字符串形式：

SELECT * FROM users WHERE phone = '13800138000';

2. 函数操作列

SELECT * FROM orders WHERE DATE_FORMAT(create_time,'%Y-%m') = '2023-01';

改为范围查询可走索引：

SELECT * FROM orders WHERE create_time >= '2023-01-01' AND create_time < '2023-02-01';

3. 前导通配符

SELECT * FROM products WHERE name LIKE '%手机%';

如果必须这样查询，考虑使用全文索引。

4.2 索引诊断方法

1. EXPLAIN分析

EXPLAIN SELECT * FROM users WHERE status = 1;

关注type列：

• const/system：最佳
• ref/range：良好
• index：全索引扫描
• ALL：全表扫描（最差）

2. 索引统计信息

SHOW INDEX FROM table_name;

关注Cardinality（基数），值越高索引选择性越好。

3. 优化器跟踪

SET optimizer_trace="enabled=on"; SELECT * FROM users WHERE...; SELECT * FROM information_schema.optimizer_trace;

4.3 复合索引优化

复合索引的顺序至关重要。遵循"最左前缀"原则：

好的设计：

ALTER TABLE orders ADD INDEX (user_id, status, create_time);

可以优化以下查询：

• WHERE user_id = 1
• WHERE user_id = 1 AND status = 2
• WHERE user_id = 1 AND status = 2 AND create_time > '2023-01-01'

差的设计：

ALTER TABLE orders ADD INDEX (status, user_id);

无法优化WHERE user_id = 1的查询。

实用技巧：使用FORCE INDEX可以强制使用特定索引，但应该作为最后手段。更好的方法是优化查询或索引结构。