理解数据库原理

  • 存储管理
  • 索引结构
  • 数据结构
  • 查询执行
  • 查询编译器
  • 系统日志
  • 并发控制
  • 事务管理
  • 分布式数据库

数据库的设计阶段

  • 合理的字符集的类型设置
  • 合理的数据业务模型设计
  • 合理设计数据库的表名字
  • 合理设计数据表字段名字
  • 合理的数据字段类型选择
  • 合理的数据字段长度选择
  • 固定长度字符选定长类型
  • 避免设计较宽的数据库表
  • 避免用数据库内置关键字
  • 约定每张表都应存在主键
  • 用自增主键或无符号整数

合理的数据库的索引设计

  • 避免分离率低的字段做索引
  • 避免更新频繁的字段做索引
  • 组合索引应遵循左前缀法则
  • 组合索引等值比较优先靠左
  • 组合索引区分度高的字段靠左
  • 组合索引最好能做到索引覆盖
  • 排序字段和索引字段顺序一致
  • 数据库表的关联字段添加索引
  • 应避免创建冗余的数据库索引

索引失效的一些常见场景

  • 对索引列使用函数会导致索引失效
  • 对索引列进行计算会导致索引失效
  • 对索引列类型转换会导致索引失效
  • 对索引列左模糊匹配导致索引失效
  • 组合索引违反左前缀法则导致索引失效
  • 组合索引存在or操作会退化成单键索引
  • 对索引列!=,<>操作会导致全索引扫描
  • order by不走索引列时导致文件排序
  • group by不走索引列时导致文件排序
  • 当数据量较少时可能会采用全表扫描

数据库的开发阶段

  • 使用数据库连接池的驱动
  • 使用预编译的Statement
  • 显示的指定返回的字段集
  • 显示指定插入的字段名称
  • 合理的减少连接表的数量
  • 合理的进行批量插入数据
  • 合理的使用文本装载数据
  • 避免字段出现NULL悬空
  • 避免使用GUID作为主键
  • 避免重复查询相同的数据
  • 避免隐式的数据类型转换
  • 避免返回大量数据的查询
  • 避免不带条件的 Where
  • 使用Union all代替Union
  • 维护好清晰的数据库文档
  • 使用乐观锁来代替悲观锁
  • 使用连接查询替代子查询
    • 子查询会产生临时表
  • 优先优化调用频繁的查询
  • 基于业务的数据库微服务
  • 通常应该要避免全表扫描
  • 全表扫描有时候未必不好
    • 连续数据的读取全表扫描避免了随机读

  • 索引访问有时候未必就好

    • 大量的离散数据的回表操作造成随机读

      执行计划分析阶段

  • Type

    • const
    • eq_ref
    • ref
    • range
    • Full Index Scan
    • Full Table Scan

  • Extra

    • Using index 索引覆盖
    • Using where, Using index
      • 索引过滤数据
      • 索引返回数据
    • Using index condition 索引并回表
    • Using temporary 临时表
    • Using where 全表扫描
    • Using filesort 文件排序

      数据库的运维阶段

  • 尽可能大的内存缓存索引

  • 数据库的实时监控和预警
    • CPU使用率
      • 计算函数的压力
    • IOPS
      • 物理磁盘的压力
    • QPS/TPS
      • 业务访问量的压力
    • 会话数
      • 数据库连接池的压力
  • DLL数据库操作导致锁表
    • 添加索引
    • 删除索引
    • 改变索引
  • 数据库死锁与数据库阻塞
    • 数据库阻塞
      • 数据库阻塞不一定是死锁
      • 最后进入死锁的事务会自动回滚
    • 数据库死锁
      • 数据库的死锁会造成阻塞
      • 发生阻塞的事务则需要等待超时
  • 避免大或长的数据库事务
    • 问题
      • 导致事务发生死锁
      • 导致从库延迟回放
      • Undo列表会增长
    • 解决
      • 基于对复杂业务的拆分
      • 基于对复杂DDL的拆分
  • 如何删除表中的大量数据
    • 问题
      • 大量数据的删除会导致长事务
      • 会导致Undo&Binlog的增长
      • Optimize Table回收数据碎片
    • 解决
      • 基于Truncate做全表删除
      • 大量删除数据先删除索引
      • 分批次的逐步删除大数据

  • 如何定位数据库相关问题

    • 命令
      • explain sql
      • show variables like ‘optimizer_switch’
    • 系统表
      • sys_config
      • performance_schema
      • information_schema
      • Information_schema.innodb_trx - 查看事务
      • information_schema.innodb_locks - 查看事务死锁
      • information_schema.innodb_lock_waits - 查看事务等待
      • information_schema.processlist - 查看运行中的SQL语句
    • 参数
      • 避免采用两趟扫描
        • max_length_for_sort_data
      • 避免产生文件排序
        • sort_buffer_size

          数据集的外部优化

  • 基于缓存对数据库的减压

  • 缓存对热点数据的预加载

数据库反范式设计

  • 合理放弃数据库存储过程
  • 数据库功能上浮到应用层
  • 合理的添加数据冗余字段
  • 合理的舍弃数据库的约束
  • 合理的减少数据库的计算
  • 合理的舍弃数据库的外键
  • 合理的使用多态关联设计
  • 合理的纵向拆分不常用列
  • 合理的纵向拆分较大的列
  • 合理的水平拆分大的表格
  • 合理使用整数存储浮点数
  • 合理创建小的热点数据表
  • 合理的拆分大的复杂查询
  • 合理的数据翻页的设计
    • 基于limite翻页
    • 基于nextToken
    • 基于id回表机制
  • 合理的树形结构的设计
    • 父子关系结构
    • 路径前缀结构
    • 闭包关系结构

  • 合理的选择表继承方案

    • 单表继承
    • 实体表继承
    • 类表继承

    • 半结构化数据

      非关系数据库选择

    • 合理的引入键值型数据库弥补关系型数据库的不足

    • 合理的引入文档型数据库弥补关系型数据库的不足
    • 合理的引入列族型数据库弥补关系型数据库的不足
    • 合理的引入图结构数据库弥补关系型数据库的不足
    • 合理的引入全文搜索系统弥补关系型数据库的不足