数据库优化

本文记录我目前所知道的一点数据库(单指mysql)优化点. 现阶段的学习比较偏概念和理论. 对数据库和消息队列这类基础设施的学习并不深. 后续慢慢补充吧.

# 设计

# 字段设计

  • 数据设计依据三范式. 主键不可再拆分, 非主键字段要依赖主键, 不能存在间接依赖
  • 字段尽量使用整形而不是字符串
  • 货币字段尽量使用小单位大数额, 避免出现小数
  • 尽可能选择小的数据类型和短的数据长度

# 索引

  • order by字段要添加索引. 如果没有索引会全量取出数据排序
  • 索引覆盖. 如果select后的字段都建立了索引, 那么查询会直接在索引表中查询而不是原始数据
  • 符合索引. 符合索引全部命中时比单独建立索引的更高效. 单独索引会分别在索引表中二分查找出匹配记录, 再取交集
  • 枚举不容易使用到索引. 一个枚举值可能匹配大量记录, 这种情况mysql会认为利用索引比全表扫描效率低.

# 分区

只有检索字段为分区字段时效果才会明显.

分区算法:

  • hash. 仅适用于整形字段
  • key. 从字符串中计算出一个整形进行取模运算
  • range. partition by range (字段名) (分区名 values less than (指定值)) .只支持less than
  • list partition by list (字段名) (分区名 value in (1,20))

# 分表

  • 水平分隔. 多张相同表结构. 大部分都是水平分隔
  • 垂直分隔. 字段分别存储在不同表中

# 查询

  • 最左匹配原则. 必须按照符合索引的顺序写sql
  • 防止隐式转换. 比如有phone字段为字符串类型, 那么查询是不要出现phone = 111. 会导致mysql使用隐世替换, 导致索引失效.
  • 不要使用select * .
  • like %name. 不要把%写在前面, 无法命中索引
  • 不要在where左侧对索引字段使用运算符, 会导致索引失效
  • 以用limit 1 提高效率
  • or要求两边都有索引可用, 如果一遍有索引, 一遍无索引会导致SQL全表扫描
  • limit的分页过大. 比如limit 200000, 10.

可以使用子查询替代select id,name from table where id>=(select id from table order by id limit 200000,1) limit 10;

也可以使用延迟关联处理. select table.id, table.name from table t inner join (select id from table order by id limit 200000,5) as table_1 using(id);

  • count. count(*)效率与count(1)差不多, count(id)低一点, count(其他字段)最低
上次更新: 2022/3/11 15:12:48