文本将从数据库设计方面着手简单介绍怎样优化MySQL数据库性能。
数据类型尽量选择更简单或者占用空间更小的数据类型:
如果长度能够满足,整型尽量使用 tinyint、smallint、medium_int 而不是 int。
如果字符串长度确定,采用 char 类型,而不是 varchar。
如果 varchar 能够满足需求,不采用 text 类型。
如果精度要求较高的可使用 decimal 类型,也可以使用 bigint。比如精确两位小数就乘以 100 后保存,如:430.34 => 430.34*100 = 43034
如果要保存时间戳,尽量采用 timestamp 而非 datetime。如下图:
timestamp 比 datatime 占用更少的空间,以 UTC 的格式储存自动转换时区。
MySQL 中字段为 NULL 时依然占用空间,会使索引、索引统计更加复杂。从 NULL 值更新到非 NULL 无法做到原地更新,容易发生索引分裂影响性能。
因此尽可能将 NULL 值用有意义的值代替,也能避免 SQL 语句里面包含 is not null 的判断。
由于 Text 字段储存大量数据,表容量会很早涨上去,影响其他字段的查询性能。建议抽取出来放在子表里,用业务主键关联。
MySQL 数据库索引分类如下:
普通索引:最基本的索引。
组合索引:多个字段上建立的索引,能够加速复合查询条件的检索。
唯一索引:与普通索引类似,但索引列的值必须唯一,允许有空值。
组合唯一索引:列值的组合必须唯一。
主键索引:特殊的唯一索引,用于唯一标识数据表中的某一条记录,不允许有空值,一般用 primary key 约束。
全文索引:用于海量文本的查询,MySQL 5.6 之后的 InnoDB 和 MyISAM 均支持全文索引。由于查询精度以及扩展性不佳,更多的企业选择 Elasticsearch。
索引优化原则:
分页查询很重要,如果查询数据量超过 30%,MySQL 不会使用索引。
单表索引数不超过 5 个、单个索引字段数不超过 5 个。
字符串可使用前缀索引,前缀长度控制在 5-8 个字符。
字段唯一性太低,增加索引没有意义,如:是否删除、性别。