数据库优化

本文记录我目前所知道的一点数据库(单指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(其他字段)最低