常用的数据库应用设计优化方法

常用的数据库应用设计优化方法

• 水平拆分，分库分表
• 增加缓存层，减少数据库的访问次数，大部分的查询访问ckv，更新操作异步更新到db
• 读写分离，实现在线访问和离线访问的隔离，避免相互影响，需要注意实例间同步时延的问题

表结构设计优化

• 主键设计：使用自增id主键
  • 推荐使用自增id主键的原因：
    • InnoDB数据是按照主键聚簇的，数据在物理上按照主键大小顺序存储，使用其他列或者组合无法保证顺序插入，随机IO导致插入性能下降
    • 所有二级索引都存储了主键的，采用二级索引查询，首先找到的主键，然后通过主键定位数据，如果直接使用组合字段作为主键，会导致二级索引占用空间较大
  • 顺序主键可能存在的问题：
    • 高并发插入的情况下，并发插入导致间隙锁竞争严重
    • 高并发插入的情况下，AUTO_INCREMENT锁机制，可能导致锁竞争情况比较严重
• 减少大字段的使用
  • 问题：每次update都需要记录所有字段的值，导致binlog占用大量空间
  • 解决方案：把长字段存储在对象存储系统中，关系数据库表中只记录字段在对象存储系统中的索引id

SQL优化

• 分页查询优化
  • 一般分页查询方式，mysql会读取跟偏移量相当的记录数，导致越往后翻页读取的记录数就越大，查询性能越差
  • 可以运用子查询+联合查询的方式优化sql，先确定查询页的主键id，在根据主键id回表查询所需要的记录。
• 隐式转换问题 mysql的隐式转换规则： a. 两个参数至少有一个是 NULL 时，比较的结果也是 NULL，例外是使用 <=> 对两个 NULL 做比较时会返回 1，这两种情况都不需要做类型转换 b. 两个参数都是字符串，会按照字符串来比较，不做类型转换 c. 两个参数都是整数，按照整数来比较，不做类型转换 d. 十六进制的值和非数字做比较时，会被当做二进制串 e. 有一个参数是 TIMESTAMP 或 DATETIME，并且另外一个参数是常量，常量会被转换为 timestamp f. 有一个参数是 decimal 类型，如果另外一个参数是 decimal 或者整数，会将整数转换为 decimal 后进行比较，如果另外一个参数是浮点数，则会把 decimal 转换为浮点数进行比较 g. 所有其他情况下，两个参数都会被转换为浮点数再进行比较
  • 隐式类型转换，可能导致索引失效，是否失效取决于转换后能否有效选择索引（类型转换导致精度损失的情况下会导致索引失效）
  • 为了避免踩坑进行sql查询是最好保持字段跟查询数值的类型一致
• 子查询优化
  • 老版本（5.6以前）mysql的处理逻辑是遍历表中的每一条记录，代入到子查询中进行查询，所以查询效率很低；
  • 优化方式：
    • 将子查询修改成关联的形式
    • 使用mysql5.6或更新的版本