MySQL千万级大表优化，看这一篇就忘不掉了( 三 ) _MySQL

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

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 记录，如下：