前言

导致数据库慢的原因有呢些？

• 频繁的磁盘操作
• 数据量过大

针对原因我们可以选择优化的几个方面

• 设计数据库数据表时选择正确的字段以及存储引擎
• 利用好mysql服务器提供的功能（索引，分区等等）
• 横向扩展，负载均衡，读写分离

字段设计

结论：

• 越小越好，够用就好

更小的数据类型通常更快，因为他们占用更少的磁盘、内存和CPU缓存，并且处理时有需要的CPU周期也更少
如果无法确定哪个数据类型是最好的，就选择你认为不会超过范围的最小类型

• 简单就好，没null最好

简单数据类型的操作通常需要更少的CPU周期，例如整型比字符操作代价更低，因为字符集和校对规则使字符比较比整型比较更复杂
当可为null的列被索引时，每个索引记录需要一个额外的字节，会占用更多的存储空间

整数类型

• TINYINT:8位，范围-2^(8-1) ~ 2^(8-1)即-256~256
• SMALLINT:16位，范围-2^(16-1) ~ 2^(16-1)即-65536~65536
• MEDIUMINT:24位，范围-2^(24-1) ~ 2^(24-1)即-16,777,216‬~16,777,216‬
• INT:32位，范围-2^(32-1) ~ 2^(32-1)即-4,294,967,296‬~4,294,967,296‬
• BIGINT:64位，范围-2^(64-1) ~ 2^(64-1)即-18,446,744,073,709,551,616~18,446,744,073,709,551,616

注：整数类型有可选的UNSIGNED属性，表示不允许负值，可使正数上限提高一倍

尽量使用整形表示字符串（例如表示枚举），存储空间固定，占用空间少，运算速度快

实数类型

FLOAT(4个字节)和DOUBLE(8个字节)(定长型)支持使用标准的浮点运算进行近似计算，存在精度丢失问题,占据固定的存储空间，运算效率高，无论数据多大，占用的空间是固定的，所以当超过最大空间时会导致精度丢失
DECIMAL(变长型)用于存储精确地小数，不会导致数据丢失，占用的空间会随着数据的增大而增大
DECIMAL(M,D)依赖于M(小数点之前最大位数)和D(小数点之后最大位数)的值，如果M>D，为M+2否则为D+2

整数运算没有精度问题，小数运算存在精度问题，计算机无法将小数完全转为二进制，所以可以通过缩小单位通过整数运算体高精度

例：1.2万元>>>>>12000元

字符串类型

VARCHAR与CHAR

• VARCHAR类型用于存储可变长字符串，他比定长型更节省空间，因为他仅使用必要的空间，越短的字符串占用空间越少 ，比如varchar(10), 然后输入abc三个字符，那么实际存储大小为4个字节。
• VARCHAR需要需要使用1或2个额外字节记录字符串的长度，如果列的最大长度小于等于255，则使用一个字节表示，否则使用两个字节
• CHAR类型是定长的，即当定义的是char(10)，输入的是"abc"这三个字符时，它们占的空间一样是10个字节，包括7个空字节。当输入的字符长度超过指定的数时，char会截取超出的字符。而且，当存储char值时，MySQL是自动删除输入字符串末尾的空格。CHAR很适合存储短的字符串，或者所有的值都接近一个长度（比如账号密码姓名）。对于非常短的列，CHAR比VARCHAR在存储空间上更有效率。例如用CHAR(1)来存储只有Y和N的值，CHAR(1)只需要一个字节，VARCHAR(1)需要2个字节，需要额外一个字节记录长度
• 所以，从空间上考虑，varchar较合适；从效率上考虑，用char合适。二者之间需要权衡。

QUESTION:使用VARCHAR(5)和VARCHAR(200)存储'hello'的空间开销是一样的，那么使用短列更有优势吗？
是的！更长的列会消耗更多的内存，因为MYSQL通常会分配固定大小的内存块来保存内部值。
所以最好的策略就是分配真正需要的空间    

BLOB与TEXT

• 他们都是为了存储很大的数据而设计的字符串数据类型，比如文章，或者bpm中的xml,分别采用二进制和字符串方式存储
  • 字符类型：TINYTEXT,SMALLTEXT,TEXT,MEDIUMTEXT,LONGTEXT
  • 二进制类型：TINYBLOB,SMALLBLOB,BLOB,MEDIUMBLOB,LONGBLOB
• 因为BLOB类型存储的是二进制数据，所以没有排序规则和字符集，但可以存储图片，TEXT与VARCHAR类似，只能存储纯文本，只不过他能存储的字符数更多

TIPS:
字符集：字符集是针对不同语言的字符编码的集合，比如UTF-8字符集，GBK字符集，GB2312字符集等等，不同的字符集使用不同的规则给字符进行编码   
排序规则：是在特定字符集的基础上特定的字符排序方式，排序规则是基于字符集的，是对字符集在排序方式维度上的一个划分。排序规则是依赖于字符集的，一种字符集可以有多种排序规则，但是一种排序规则只能基于某一种字符集的

日期和时间类型

MYSQL存储的最小时间粒度为秒，但是他支持使用为微秒级别的粒度进行临时运算

• DATETIME类型:范围1001年到9999年，精度为秒。他把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间

• TIMESTAMP类型:保存了从1970年1月1日零点零分零秒以来的秒数。只使用4个字节的存储空间，因此它的范围比DATETIME小很多：只能表示从1970年到2038年，超出这个范围则值记录为’0000-00-00 00:00:00’

  • TIMESTAP显示的值也依赖于时区。因此存储值为0的TIMESTAP在美国东部时区显示为"1969-12-31 19:00:00".与格林尼治时间相差5个小时

• DATE类型:就是日期，用于具有日期部分但没有时间部分的值。date类型的字段，占用3个字节。在数据库中存储数据格式为：YYYY-MM-DD，它支持的范围为’1000-01-01’到’9999-12-31’，并且允许使用字符串或数字为此列赋值。

• TIME类型:time数据类型指的是具体的不包括日期的时间，占用3个字节。以’hh:mm:ss’格式（或 'hhh:mm:ss’大小时数格式）显示值 。TIME值的范围可以从 '-838:59:59’到 ‘838:59:59’。小时部分可能会很大，因为该TIME类型不仅可以用来表示一天中的某个时间（必须少于24小时），而且可以用来表示经过的时间或两个事件之间的时间间隔（可能远大于24小时，甚至是负的）。

  TIME值的范围 是 '-838:59:59.000000’至 ‘838:59:59.000000’。

• YEAR类型：YYYY类型用于表示年份值，占用1个字节。以YYYY格式显示值， 范围 1901为2155，和 0000。

位数据类型

MySQL有少数几种存储类型使用紧凑的位存储数据。不管底层存储格式如何处理，从技术上说都是字符型。

BIT：BIT(1)表示定义一个包含一位的长度，BIT(2)表示2个位，以此类推，BIT列最大长度是64位，MySQL把BIT当字符串处理，而不是数字，当检索BIT(1)的值时，结果是一个包含二进制0或1的字符串，而不是ASCII码的0或1，例如00111001，它的二进制等于57，检索它时得到是一个字符码为57的字符，也就是ASCII码57对应字符为9。

整合JPA

@Target({ElementType.METHOD, ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
public @interface Column {//定义了被标注字段在数据库表中所对应字段的名称；String name() default "";//表示该字段是否为唯一标识，默认为false。如果表中有一个字段需要唯一标识，则既可以使用该标记，也可以使用@Table标记中的UniqueConstraint。boolean unique() default false;//表示该字段是否可以为null值，默认为true。boolean nullable() default true;//表示在使用“INSERT”脚本插入数据时，是否需要插入该字段的值。(即插入时是否不忽略该字段)boolean insertable() default true;//表示在使用“UPDATE”脚本插入数据时，是否需要更新该字段的值。(即修改时是否不忽略该字段)boolean updatable() default true;//字段定义，一旦使用，jpa不会自动识别类型创建，注意sql语法String columnDefinition() default "";//表示当映射多个表时，指定表的表中的字段。默认值为主表的表名。String table() default "";//表示字段的长度，当字段的类型为varchar时，该属性才有效，默认为255个字符。int length() default 255;//precision属性和scale属性表示精度，当字段类型为double(或decimal)时，precision表示数值的总长度，scale表示小数点所占的位数。int precision() default 0;int scale() default 0;
}

@Getter
@Setter
@ToString
@Table(name = "audit_log", indexes = {@Index(columnList = "cost_time")})
@NoArgsConstructor
@AllArgsConstructor
@Entity
public class AuditLog extends BaseEntity {@Id@GeneratedValue(strategy = GenerationType.IDENTITY)@Column(name = "id", nullable = false)private Long id;@ApiModelProperty("用户id")@Column(name = "user_id", columnDefinition = "SMALLINT(15) UNSIGNED COMMENT '用户id'")private Integer userId;@ApiModelProperty("traceId")@Column(name = "trace_id", columnDefinition = "CHAR(20) COMMENT 'traceId'")private String uuid;@ApiModelProperty("服务端ip")@Column(name = "service_ip", columnDefinition = "CHAR(15) COMMENT '服务端ip'")private String serviceIp;@ApiModelProperty("用户姓名")@Column(name = "admin_user", columnDefinition = "CHAR(10) COMMENT '用户姓名'")private String adminUser;@ApiModelProperty("响应时间(毫秒)")@Column(name = "cost_time", columnDefinition = "SMALLINT(15) UNSIGNED COMMENT '响应时间(毫秒)'")private Integer costTime;@ApiModelProperty("请求地址")@Column(name = "uri", columnDefinition = "varchar(255) COMMENT '请求地址'")private String uri;@ApiModelProperty("入参")@Column(name = "param_json", columnDefinition = "varchar(255) COMMENT '入参'")private String paramJson;@ApiModelProperty("请求方式")@Column(name = "method", columnDefinition = "CHAR(8) COMMENT '请求方式'")private String method;@ApiModelProperty("用户地址")@Column(name = "ip", columnDefinition = "varchar(255) COMMENT '用户地址'")private String ip;@ApiModelProperty("事件时间")@Column(name = "event_time", columnDefinition = "DATETIME COMMENT '事件时间'")private Date eventTime;@ApiModelProperty("异常堆栈")@Column(name = "exception", columnDefinition = "varchar(255) COMMENT '异常堆栈'")private String exception;@ApiModelProperty("事件级别")@Column(name = "event_level", columnDefinition = "TINYINT(1) UNSIGNED COMMENT '事件级别'")private Integer eventLevel;@ApiModelProperty("事件类型")@Column(name = "event_type", columnDefinition = "TINYINT(1) UNSIGNED COMMENT '事件类型'")private Integer eventType;@ApiModelProperty("事件模块")@Column(name = "event_module", columnDefinition = "SMALLINT(10) UNSIGNED COMMENT '事件类型'")private Integer eventModule;@ApiModelProperty("事件描述")@Column(name = "event_description", columnDefinition = "varchar(255) COMMENT '事件描述'")private String eventDescription;@ApiModelProperty("出参结果")@Column(name = "result_json", columnDefinition = "varchar(500) COMMENT '出参结果'")private String resultJson;@ApiModelProperty("事件结果")@Column(name = "event_result")private Boolean eventResult;
}

越小越好，够用就好

越小越好，够用就好

越小越好，够用就好

参考文献

1.《高性能MYSQL》