MySQL优化系列之数据库schema设计优化

男娘i 2022-06-08 04:25 233阅读 0赞

# **前言** #

MySQL优化有两大层面，第一是数据库层面，第二是硬件层面。数据库层面可以细分为以下几个方面：

*  [数据库schema设计优化][schema]
 *  [SQL查询优化][SQL]
 *  锁策略优化
 *  存储引擎优化
 *  服务器配置优化
 *  主从，读写分离
 *  集群，负载均衡

本文讲解数据库schema设计优化

# **高效的模型设计** #

## **适度冗余（反范式）** ##

在很多人眼里，数据库schema满足的范式级别越高越好，目的是尽量减少数据的冗余，保持数据一致。但是适当的冗余能减少多表连接操作，这样一来冗余的字段就需要手动维护（比如触发器、消息监听字段变更），以保持数据一致性。如果牺牲一点维护成本能换来更大的性能提升，那么完全可以选择冗余

## **垂直拆分** ##

将字段从主表拆分出去放到另外的表中。适合垂直才拆分的字段有：

**1.内容很大，但访问频率低的字段**

存放内容较多，访问频率相对其他字段少很多的字段。由于内容较多，大部分情况占整条记录的80%以上，而数据库中数据在数据文件中的格式一般都是以一条一条记录为单位来存放。也就是说，如果我们要查询某些记录的某几个字段，数据库并不是只需要访问我们需要查询的哪几个字段，而是需要读取其他所有字段（可以在索引中完成整个查询的情况除外），也无法做到只读取我们需要的几个字段的数据。这样，我们就不得不读取包括大字段在内的很多并不相干的数据。而由于大字段所占的空间比例非常大，自然所浪费的IO 资源也就非常之大了。拆分大字段后需要join来查询它，所以还需要满足频率低才行

**2.频繁更新，导致缓存刷新，但访问频率低的字段**

比如，你有一个叫“last\_login”的字段，它会在每次用户登录时被更新。但是，每次更新时会导致该表的查询缓存被清空。所以，你可以把这个字段放到另一个表中，这样就不会影响你对用户ID，用户名，用户角色的不停地读取了，因为查询缓存会帮你增加很多性能。

## **水平拆分** ##

当单表的数据量过大时，可以按照某种规则把数据划分到不同的表或库中，比如订单表，可以按年份来划分，不同年份的订单数据可以存放到不同的地方；有比如用户表可以按ID取模、按性别来划分。

## **统计表准实时优化** ##

对于更新是非常频繁的，但并不要求实时准确的数据可能弄成准实时的，比如网站访问量、用户活跃数这类信息，我们可以通过定时任务，每隔一段时间去统计一次。

# **选择合适的数据类型** #

数据类型的选择有下面几个简单原则：

**1.更小的通常更好**

一般情况下应该选择正确存储数据的最小数据类型，因为它暂用更少的磁盘、内存和CPU缓存，并且处理的CPU周期更少

**2.简单就好**

简单数据类型的操作通常需要更少的CPU周期。例如，用整型代替字符型，因为字符集和校对规则（排序规则）使字符比较比整型更复杂；用内建类型而不是字符串来存储日期时间；整型存储IP地址

**3.尽量避免NULL**

NULL使索引、索引统计和值的比较都更复杂

## **整数类型** ##

可以使用的整数类型有：tinyint，smallint，mediumint，int，bigint。分别使用8,16,24,32,64位存储空间。值范围从-2^(N-1) 到 2^(N-1)-1，N为存储空间位数。

整数类型可选unsigned属性，表示不允许负值，正值范围将提高一倍，但是存储空间和性能和有符号是相同的。

另外，整数类型可以指定宽度，其实没有多大意义，比如int(1)和int(20)范围是相同的

## **实数类型** ##

实数是带小数部分的数字。然而，它们不只是为了存储小数部分；也可以用decimal存储比bigint还大的整数。

float和double类型支持使用标准的浮点运算进行近似计算，可能存在进度丢失

decimal用于存储精确的小数，支持精确计算

浮点类型在存储同样范围的值的时候，通常比decimal使用更少的空间，float4个，double8个字节。因为需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算的时候才使用decimal——比如财务数据，当然也可以将小数放大适当倍数转为整数用bigint来存储。

## **字符串类型** ##

**1.char和varchar**

varchar可变长度，需要1个或2个额外字节记录字符串长度，小于等于225字节是，使用1个字节，否则2个字节。比如varchar(10)需要11个字节的存储空间，varchar(1000)需要1002个。varchar节省了存储空间，所以对性能也有帮助。

char固定长度，适合存储很短的字符串，或者所有值都接近同一个长度。例如，char非常适合存储MD5值，因为它是一个定长的值。

**2.blog和text**

blog和text都是为存储很大的 数据而设计的字符串类型。blog采用二进制存储，没有排序规则或字符集，text采用字符方式存储，有排序规则和字符集

实际上，它们分别属于两组不同的数据类型家族：字符类型是tinytext，smalltext，text，mediumtext，longtext;对应的二进制类型tinyblog，smallblog，blog，mediumblog，longblog。blog和smallblog是同义词，text和smalltext是同义词。

MySQL对blog和text列进行排序与其他类型不同：它只对最前max\_sort\_lenght字节而不是整个字符串做排序。

**3.使用枚举enum代替字符串类型（慎用）**

有时候可以使用枚举类型代替常用的字符串类型，比如性别、国家、民族等。MySQL存储枚举是非常紧凑，会根据列值压缩到一个或者两个字节中MySQL在内部会将每个值在列表中的位置保持为整数，并且在的.frm文件中保存“数字-字符串”映射关系的“查找表”。

枚举最不好的地方是字符串列表是固定的，添加或删除字符串必须使用alter table，对于未来可能会改变的字符串，使用枚举不是个好主意

## **日期和时间类型** ##

**1.datetime**

能保存大范围的值，从1001到9999年，精度为秒，与时区无关，使用8字节存储空间

**1.timestamp**  
保存了从1970年1月1日午夜（格林尼治标准时间）以来的秒数，它和Unix时间戳相同。依赖时区，只使用4个字节的存储空间，因此范围比datetime小的多：1970年到2038年。与其他数据类型不一样，timestamp默认为not null

同时需要注意的是，由于在多个时区存储或访问数据造成时间不一致的问题。

# **关于alert table** #

alter table性能对于大表来说是个很大问题。MySQL执行大部分修改表结构的操作的方法是用新的结构创建一个空表，从旧表中查出所有数据插入新表，然后删除旧表。如果内存不足而表有很大，而且还有很多索引，alert table可能花数个小时甚至数天才能完成。解决这个问题有两个技巧：

1.先在一台不提供服务的机器上执行alter table操作，然后和提供服务的主库进行切换