优化 SQL 语句与索引

定义字段

  • 涉及计算/排序等消耗 CPU 的字段,尽量选用更迅速的字段
    • TIMESTAMP(4字节)优于 Datetime(8字节)
    • Integer 优于 Float
  • 变长字段使用 varchar,不要使用 char
  • 必须读到的表中字段必须有初始值,防止独处负或无穷大数据
  • 访问频率低的大字段可以拆分出数据表,避免IO资源的浪费

索引建立

  • 索引根据业务中的 where 条件来确定,不要建立业务上不需要的索引
  • 唯一索引
    • 唯一确定一条记录的一个字段/多个字段
    • 经常作为查询条件
    • 在查询条件中将该字段的条件置为第一位置
  • 对于经常查询的字段,其值不唯一,也应该考虑建立普通索引

SQL优化

  • 查询条件逻辑操作符合性能的顺序:AND > OR > IN
  • 只需要取某个字段尽量不要使用 select *
  • 使用最有效的过滤字段,where 中的过滤条件越少越好

合理缓存

把用户常访问的数据写入 Memcache/Redis中,让缓存承担一部分数据库访问的压力。缓存也做读写分离。

垂直拆分

按照业务对数据表进行分类,将不同的业务分布到不同的数据库中,这样就将压力分布到不同的库上了。即:根据模块的耦合度,将一个大的系统分为多个小的系统。

水平切分

垂直拆分是把不同的表拆到不同的数据库中,而水平拆分是把同一个表拆到不同的数据库中。

相对于垂直拆分,水平拆分不是将表的数据做分类,而是按照某个字段的某种规则来分散到多个库之中,每个表中包含一部分数据。简单来说,我们可以将数据的水平切分理解为是按照数据行的切分,就是将表中 的某些行切分到一个数据库,而另外的某些行又切分到其他的数据库中,主要有分表,分库两种模式。

选择合理的 Sharding key

单表数据如果超过 1000w 就会严重影响性能。

假设现在有 1000w 条用户信息,要拆分到 100 个数据表中,那么可以根据用户的 uid 尾号 2 位作为拆分依据(假设他合理的话😊),即 Sharding Key。

总结

总而言之,数据量增大后,首先要做的还是 MySQL 索引与 SQL 语句的优化,而后读写分离、增加缓存,最后再考虑拆分的问题。

参考资料