MySQL千万级大表优化，看这一篇就忘不掉了!

命名列表

索引规范：

索引建议命名规则：idx_col1_col2[_colN]、uniq_col1_col2[_colN](如果字段过长建议采用缩写)。

索引中的字段数建议不超过 5 个。

单张表的索引个数控制在 5 个以内。

InnoDB 表一般都建议有主键列，尤其在高可用集群方案中是作为必须项的。

建立复合索引时，优先将选择性高的字段放在前面。

UPDATE、DELETE 语句需要根据 WHERE 条件添加索引。

不建议使用 % 前缀模糊查询，例如 LIKE “%weibo”，无法用到索引，会导致全表扫描。

合理利用覆盖索引，例如：SELECT email，uid FROM user_email WHERE uid=xx，如果 uid 不是主键，可以创建覆盖索引 idx_uid_email(uid，email)来提高查询效率。

避免在索引字段上使用函数，否则会导致查询时索引失效。

确认索引是否需要变更时要联系 DBA。

应用规范：

避免使用存储过程、触发器、自定义函数等，容易将业务逻辑和DB耦合在一起，后期做分布式方案时会成为瓶颈。

考虑使用 UNION ALL，减少使用 UNION，因为 UNION ALL 不去重，而少了排序操作，速度相对比 UNION 要快，如果没有去重的需求，优先使用 UNION ALL。

考虑使用 limit N，少用 limit M，N，特别是大表或 M 比较大的时候。

减少或避免排序，如：group by 语句中如果不需要排序，可以增加 order by null。

统计表中记录数时使用 COUNT(*)，而不是 COUNT(primary_key) 和 COUNT(1)。

InnoDB 表避免使用 COUNT(*) 操作，计数统计实时要求较强可以使用 Memcache 或者 Redis，非实时统计可以使用单独统计表，定时更新。

做字段变更操作(modify column/change column)的时候必须加上原有的注释属性，否则修改后，注释会丢失。

使用 prepared statement 可以提高性能并且避免 SQL 注入。

SQL 语句中 IN 包含的值不应过多。

UPDATE、DELETE 语句一定要有明确的 WHERE 条件。

WHERE 条件中的字段值需要符合该字段的数据类型，避免 MySQL 进行隐式类型转化。

SELECT、INSERT 语句必须显式的指明字段名称，禁止使用 SELECT * 或是 INSERT INTO table_name values()。

INSERT 语句使用 batch 提交(INSERT INTO table_name VALUES(),(),()……)，values 的个数不应过多。

业务层优化

业务层优化应该是收益最高的优化方式了，而且对于业务层完全可见，主要有业务拆分，数据拆分和两类常见的优化场景(读多写少，读少写多)!

①业务拆分

业务拆分分为如下两个方面：

将混合业务拆分为独立业务

将状态和历史数据分离

业务拆分其实是把一个混合的业务剥离成为更加清晰的独立业务，这样业务 1，业务 2......独立的业务使得业务总量依旧很大，但是每个部分都是相对独立的，可靠性依然有保证。

对于状态和历史数据分离，我可以举一个例子来说明。

例如：我们有一张表 Account，假设用户余额为 100。

我们需要在发生数据变更后，能够追溯数据变更的历史信息，如果对账户更新状态数据，增加 100 的余额，这样余额为 200。

这个过程可能对应一条 update 语句，一条 insert 语句。对此我们可以改造为两个不同的数据源，account 和 account_hist。

在 account_hist 中就会是两条 insert 记录，如下：

（编辑：衡阳站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!