MySQL数据库结构优化

数据库结构优化一般是在数据库设计之时应该充分考虑的,在项目上线后,数据库结构的变化会对项目有很大影响,因此在项目设计之初,要考虑数据库的扩展性。

一、数据库设计的原则

  • 遵循数据库设计三范式,尽量减少数据冗余
  • 尽量避免数据维护中出现插入、更新和删除异常
  • 尽量节约系统资源(主要是存储空间,包括内存与硬盘)
  • 为高效的查询或写操作提供良好的基础
  • 充分理解项目需求,考虑系统的扩展性,将系统的扩展性与高性能达到一个平衡

二、数据库设计的步骤

三、数据库字段数据类型的选择

选择数据类型的原则:

  • 数据类型越简单越好(从优先级考虑:数字型 > 日期时间 > 字符型)
  • 数据类型越小越好(要充分考虑存储的数据的长度和大小范围)
  • 尽量避免NULL
1、数字型
整型:
数据类型 长度(字节) 范围
tinyint 1 0-255(即2^8-1,有符号时是-128-127)
smallint 2 0-2^16-1
mediumint 3 0-2^24-1
int 4 0-2^32-1
bigint 8 0-2^64-1

注:整型的长度只按照其类型来决定的,因此int(2)和int(10)都是占用4个字节长度

浮点型:
数据类型 长度(字节) 精度 范围及适用范围
float 4 最多只能留小数点后7位 如下
      保留1-2位小数时:0-32767.99
      保留3位小数时:16383.999
      保留4位小数时:2047.9999
      保留5位小数时:127.99999
      保留6位小数时:15.999999
      保留7位小数时:0-0.9999999
double 8 可以保存精度要求较高但不确定精度的数据  
decimal(M,D) 3 由D的长度决定精度 精度要求要高的字段
2、字符型
数据类型 长度(字符) 占用空间 适用范围
varchar(n) 由n决定(最多能存65535个字符) 适合存储数据长度跨度较大的数据
    在小于255个字符时,占用n+1个字符长度  
    在小于255个字符时,占用n+2个字符长度  
char(n) 由n决定 占用n个长度 适合存储数据长度近似的数据(以空间换时间)
text 占用实际的字符长度 适合存储数据量大的数据

注:字符型中的长度的单位是指字符,而不是字节

如:varchar(10)可以存10个字母,也可以存10个汉字

3、枚举型 enum

适合存储取值范围固定的数据,如一篇文章的状态

  1. `status` enum('UNCHECKED','DELETED','DISABLED','ENABLED') DEFAULT 'UNCHECKED'
4、日期时间型
数据类型 占用空间(字节) 格式 取值范围
date 3 YYYY-MM-DD 1000-01-01~9999-12-31
time 3 HH:MM:SS -838:59:59~838:59:59
datetime 8 YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:01~9999-12-31 23:59:59
timestamp 4 YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01~2038-01-19 11:14:08
year 1 YYYY 1901-2155

关于日期时间数据的存储:

  • 根据时间范围来决定,如果timestamp可以满足条件,尽量使用timestamp来存储
5、其他常见数据的存储:
(1)IP数据:可以存储为int型,通过MySQL的系统函数INET_ATON(将IP字符串转化为整数)和INET_NTOA(将整数转换为字符串)
(2)日期时间:存储为timestamp,因为timestamp实际存储的是INT型,可以通过系统函数将字符串数字转化为TIMESTAMP类型,如unix_timestamp,from_unixtimestamp
(3)状态:可以保存为ENUM类型,可读性较强

总结:

  • 1、在满足需求的情况下,尽量选择数据类型简单的数据类型,如数字型(字符类型是比较复杂的),以达到更好的查询速度

  • 2、在满足需求的情况下,尽量选择占用存储空间较小的数据类型,以节约存储空间

  • 3、字段尽量不要为NULL,因为NULL没法索引

四、存储引擎的选择

1、InnoDB
  • 优点:支持事务,安全性较高(MySQL5.7以后支持全文索引和空间函数)

  • 建议:建议使用InnoDB,安全性高,在高版本的MySQL中它的性能不会比MyISAM差

    • ibdataN如ibdata1存储数据
    • *.frm存储表结构
  • 适合场景:适用于大多数场景(如并发量大,安全性要求较高的应用)

2、MyISAM
  • 优点:支持全文索引和空间函数,MySQL5.0以后默认的存储引擎(MySQL5.6以后是InnoDB为默认)

  • 缺点:不支持事务,安全性不高

    • *.frm存储表数构
    • *.MYD存储表数据
    • *.MYI存储索引数据

3、Memory
  • 特点:数据存储在内存中,文件中只存储表结构。

    • *.frm为表结构
    • 数据存储在内存中
  • 优点:数据存储在内存中,读写操作速度快

  • 缺点:重启后数据将不存在,不适合存储安全性要求较高或者数据量大的数据

  • 适合场景:存储一些需要频繁查询,但安全性要求不高的数据,如果数据丢失,可以从其他数据源查询得到的数据。

4、Archive
  • 优点:可以实现数据的压缩,占用更少的磁盘空间

  • 缺点:只支持insert和delete操作,不支持索引

  • 适合场景:存储数据量大但很少引用的历史 、归档数据,如日志