mysql 单表 not in_【MySQL】大表优化

当MySQL单表记录数太多时，各项操作性能会明显下降。

相关优化策略有很多，但肯定不是所有策略都适合某个特定案例。也许在技术层面上，很多策略都能提高性能，但我们也得考虑成本和收益(ROI)。

另，是否有性能提升，或者说从整体业务角度而言综合性能表现是否更符合需求，需要经过实战测试才能确定。

此文仅提供几种常见优化方案。对于某些遗留项目【其它方案】中提及的途径可能更合适 —— 优化SQL语句和索引，甚至设计表结构。

1. 限制数据范围

通过限定数据操作的范围可以有效减少数据量。

现实业务中需要一次获取大量数据记录的场景其实是比较少的。如，对于查找账单的需求，可以按月份查找，而非一次性获取所有账单。

2. 缓存数据

缓存一般适合数据改动频率较低的场景。如果改动频繁，就没有缓存的意义，还会为了维护缓存而增加过多开销。

在具体业务场景链路中哪些位置设置缓存也需要认真考量。持久层框架(如，MyBatis)、应用服务层、Web页面、浏览器客户端都可以做缓存。

注意：MySQL的查询缓存从5.6开始默认是关闭的，在5.7.20中被“deprecated”，在8.0中被移除。因为MySQL的查询缓存实现中用到了全局互斥锁，所以会降低性能(无法有效发挥多核服务器的优势)。

未认真考量缓存对业务的影响，特别是缓存失效时如何更新数据，是大多数新手会犯的典型错误。这种情况在新手写的应用服务层代码中出现频率较高。

建议：永远不要单人作战引入缓存机制。必须向产品经理等领导详细推演展示缓存机制的影响，确保相关干系人心中有素。认真听取老手的建议，切不可犯蠢。

在实际项目中，拍板做决定的人理解相关细节的影响是非常重要的。我见过一个案例中，产品组内掌握话语权的人集体“犯蠢/宕机”，未听取专家建议，在没有任何缓存失效应对方案的情况下使用缓存，结果在用户环境暴露重大缺陷，最后彻底弃用缓存。“将熊熊一窝”、“统帅无能，累死三军”说的就是这类情况。

3. 读写分离

4. 表分区

表分区是一种特殊的 水平拆分 方式，也是一种特殊的 库内分表。

它可以 缓解 单一表数据量过大 的问题，

但它对于减轻数据库服务器压力的作用不大，所有请求还是在争用同一服务器的资源。

MySQL 的 InnoDB 分区表就是典型代表。其优缺点：

优点：

方便对数据分而治之

可以通过删除分区来删除无用的数据。

为新增数据新开分区可以加快执行效率。

可以对分区单独优化、检查、修复、备份、恢复。

提高数据查询效率

可通过查询条件排除不符合条件的分区，提高效率。

缺点：

InnoDB 分区表不支持外键。

分区表不能引用其它表中的列作为外键；

其它表不能引用分区表中的列作为外键。

不支持全文索引(Fulltext Index)。

不支持空间类型数据(如，Point、Geometry)。

分区索引不支持子查询。

注：垂直拆分后单表数据量未变，依然很大(需要水平拆分)。

垂直分库

优点：可以用 不同数据库 支撑不同的业务，降低单个数据库的压力。

缺点：

上层业务需额外处理不同的数据源；

可能存在数据一致性维护问题。

垂直分表

优点：

表结构简化，易于维护；

单行数据量变小，一个数据块可存放更多行数据，可减少IO次数。

缺点：

需管理额外的主键冗余列；

事务处理复杂；

需要额外的表连接操作(join)。可在业务服务器join，减少数据库压力。

优点：不存在单表数据量过大 和 请求高并发 的性能问题，提高了系统的负载能力 和 稳定性。

缺点：

数据分片的事务一致性难以解决。

跨节点 Join 的逻辑复杂，性能差。

数据多次扩展 的 难度 和 维护量 非常大。