提高mysql性能：设计阶段

合适的表设计

基本原则

避免太多的列

太多的列会导致mysql从行缓存中将编码过的列转换为行数据时花费大量大代价。

减少太多的关联

为减少太多的关联造成解析和查询的性能影响，应该将单表的关联控制在12个之内。

合理使用枚举

枚举只适用于值相对固定，且同时只有一个值的情况，修改枚举值会造成Alter table，阻塞用户操作

谨慎地使用NULL

NULL应尽量减少使用，使用时需要在减少性能影响、降低业务数据复杂度中做权衡选择

合理选择范式和反范式

范式

减少数据冗余，合理化数据结构的标准（数据库设计三大范式_2b勿扰的博客-CSDN博客_数据库设计三大范式）

优点

1. 数据分散到不同表：需要更新的表更少
2. 减少重复数据：查询时减少Distinct和Group by，修改时减少更新数据量

缺点

1. 增加表关联，造成部分索引失效、查询性能降低低

反范式

适当增加数据冗余，提高查询效率

优点

1. 提高数据查询性能

缺点

1. 增加修改操作的难度
2. 实现实时查询较为困难

取舍应用

• 增加冗余列，减少关联

为主表增加从表的列，更新时同步更新多个表的相同列。

• 缓存表和汇总表

汇总表：增加新表，保存实时统计需要的聚合值，以增加实时统计效率，更新数据时重新或者异步统计，查询时实时聚合处理结果。

例如：一个报表需要查询每个商品的指定时间段的销售金额、销售数量、退货金额、退货数量、毛利润、移动平均价等等统计，实时统计时带来大量的性能浪费，解决办法为创建汇总表，异步定时统计各项数据放置到新表，查询时如果要求实时，则需要根据时间重新聚合整理实时汇总数据和汇总表的数据，否则直接返回汇总表数据。

缓存表：增加新表保存来自多个表的字段，通常为快速响应业务需求，避免关联多个表的内容。

例如：一个报表需要查询展示订单的退款金额、欠款金额、还款金额、以及各种活动优惠金额，各种支付方式的付款金额时，数据分散到不同的表，为减少表关联，增加查询速度可以将这些字段单独提升到一张新表

• 物化视图

使用FlexViews实现物化视图,（生成新物理表，通过Binlog更新数据）

• 计数器表

类似汇总表，用于统计业务需要的数量，不同的是，计数器表为了避免事务造成锁问题，可以使用多行数据进行统计，不同线程更新时，将随机行的数据加1，查询时对所有行进行sum汇总。

合适的字段设计

基本原则

• 尽量极少数据大小

        可以减少存储、内存占用，增加数据查询速度

• 优先选择基础数据类型

        基础数据类型可以减少CPU占用

• 尽量减少NULL

        NULL值会造成索引统计和值比较困难、增加空间占用

整数类型

TINYINT:8位

SMALLINT:16位

MIDIUMINT:24位

INT：32位

BIGING:64位

数据范围：~ （n为位数）

• unsigned:无符号，可以将整数范围翻一倍
• 有符号和无符号性能和空间占用相同
• 整数的宽度对存储无效，仅为了数据展示工具显示字符个数

实数类型

double:4个字节

float:8个字节

decimal：16个字节

DECIMAL(20,2) 指的就是总共能存20位数字，末尾2位是小数（小数点不算在长度内）

• decimal 计算方式为mysql实现，非CPU支持，故计算速度较差
• 浮点类型比decimal占用空间更小。
• 精确计算时使用decimal,否则可以使用money或者BIGINT（右移小数点）来保存货币数据

字符串类型

varchar：最大占用空间65535（max row size），实际占用空间为：实际设定长度+1-2位长度值，varchar指定的为字符个数，非字节

char：最大占用空间255（32个字节），实际占用空间为指定字符个数，指定宽度，指的同为字符数。

char占用空间位指定长度，不够则使用空格填充，所有存储时会删除末尾空格。

1.char适用情况

•   长度固定或变化较小的字符串
• 需要频繁变更的数据，不会产生碎片
• 占用空间较小的情况（不需要特定数据长度位）

2. varchar适用情况：

• 数据最大长度大于平均长度较多
• 列更新较少，碎片较少
• 使用UTF字符集保存，每个字符占用的字节数不同
• varchar过长应该使用Blog类型

Blob和Text类型

Text类型为了保存文本数据

tinytext：最大长度255个字节

text：最大长度64K

mediumtext：最大长度16M

longtext：4GB

blob类型为保存二进制数据

tinyblob：最大长度255个字节

blob：最大长度64K

mediumblob：最大长度16M

longblob：4GB

•  blob和text数据在存储时仅保存1-4个字节的指针，指向外部存储的数据。
• blob保存为二进制数据，没有配需规则与字符集
• 排序时仅对系统中指定的max_sort_length的字节配需。如需指定长度，使用order by substring(column,length)
• 尽量避免使用，如无法避免，尽量单独的表来保存，避免查询时直接发生关系

枚举类型

enum:实质存储时为整数，长度为4个字节

• 存储值从1开始，NULL表示没有属性值，0为空字符串。。
• 保存时，如果sqlmode为严格模式，会直接报错，无法保存，否则保存成功，而数据为空字符串。
• 由于规则比较特殊在实际使用时，应该避免使用，使用tinyint来实现

日期时间类型

datetime：占用8个字节，时间范围0000-9999年

timastamp:占用4个字节，从1970年1月1日午夜到目前的毫秒数，时间范围只能从1970-2038

• timestamp类型不允许为空默认为服务器当前时间，且包含失去信息
• 如果需要保存精度更小的时间值。需要自定义longint来保存纳秒、微妙数据，或者double来存储为小数

位数据类型

bit:最大长度为64位

set:64个成员或者4个字节

• bit类型mysql存储时会保存为二进制字符串。
• bit类型在检索时会将数据转换为二进制字符串，在数字环境会返回对应二进制的ASCII码

如：‘00111001’ （十进制为57）存储为Bit（8）列a中：

select a,a+0 from XX
a=9,a+0=57

• 尽量避免使用bit，表达bool类型时使用Char(0)，存储null或者空字符串，或者tinyint(1)，1或则0
• 尽量避免使用set，可以使用tinyint来存储，如：tinyint(8)来存储8个数据位

处理特殊类型数据

IP地址应该用32位无符号数保存，mysql提供了INET_ATON()和INET_NTOA()对数据进行转换

合适的标识符设计

整数类型

• 满足条件下尽量选择最小的数据格式（横向分表的情况可以使用TINYINT）
• 优先使用整数类型，速度更快，可以发挥自增长的优势

字符串类型

• 尽量避免使用字符串，占用空间更大，速度更慢，排序困难
• UUID应该去除‘-’或者使用nanoID.
• UUID使用应该使用char(16)定长格式