提高mysql性能:设计阶段

article/2024/4/20 14:14:46

合适的表设计

基本原则

避免太多的列

太多的列会导致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位

数据范围:2^{n}~2^{n} (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)定长格式

http://www.ngui.cc/article/show-845661.html

相关文章

Lesson 4.3 梯度下降(Gradient Descent)基本原理与手动实现

文章目录一、梯度下降基本原理与学习率1. 数据背景与最小二乘法求解2. 一维梯度下降基本流程2.1 参数移动第一步2.2 梯度下降的多轮迭代3. 梯度下降算法特性与学习率二、梯度下降一般建模流程与多维梯度下降1. 多维梯度下降与损失函数可视化2. 梯度下降计算流程3. 二维梯度下降…

电子技术——MOS放大器基础

电子技术——MOS放大器基础 我们已经学过MOS可以当做一个压控流源,使用栅极电压 vGSv_{GS}vGS​ 控制漏极电流 iDi_DiD​ 。尽管两个量的关系不是线性的,稍后我们将会介绍偏置在线性区的工作方法。 构建压控压源放大器 现在,我们有了一个压…

GeoServer学习笔记-01GeoSever运行编译

一、运行1. 下载GeoServerGitHub仓库地址:https://github.com/geoserver/geoserver2.本地代码工具打开项目在idea里,文件->新建->来自现有的源代码项目,选择项目的pom文件加载项目。3.idea编译环境设置(1)设置jd…

电子技术——MOS管的CV特性

电子技术——MOS管的CV特性 MOS管是一种压控晶体管,本节我们学习MOS管的CV特性,即电压-电流特性。MOS管的特性曲线有两种,分别是伏安特性和传导特性。 iD−vDSi_D-v_{DS}iD​−vDS​ 特性曲线 为了测量MOS管的 iD−vDSi_D-v_{DS}iD​−vDS​…

微分先行PID控制算法及仿真

微分先行PID控制的结构如图1所示,其特点是只对输出量y(k)进行微分,而对给定值y(k)不作微分。这样,在改变给定值时,输出不会改变,而被控量的变化通常是比较缓和的。这种输出量先行微分控制适用于给定值yd(k)频繁升降的场…

代码随想录第九天(剑指offer 58II,有问题)

文章目录剑指 Offer 58 - II. 左旋转字符串嘿嘿,一次通过【补充1】利用内置函数切片【补充2】通过取余操作简化代码利用反转字符串的方法(有问题)注意总结剑指 Offer 58 - II. 左旋转字符串 嘿嘿,一次通过 public String revers…

Mysql,使用FIND_IN_SET()函数处理多表关联问题.

这里有 user表、teacher表,其中 teacher.user_ids 字段中的值是 user.id 值以英文半角逗号拼接而来。现在, 我们需要在查询 teacher 表数据时,将 user.name 的值也查询出来。使用以下的SQL语句,即可实现需求。SELECTGROUP_CONCAT(…

php伪协议

目录 一、伪协议介绍 1、php://协议 2、php://filter伪协议 3.php://input(读取POST数据) 4、file伪协议 5、phar://伪协议(读取压缩包文件内容) 6、压缩文件伪协议 6.1.zip://[压缩文件绝对路径]%23压缩文件内的子文件名 6.2.compress.bzip2://…

TOGAF鉴定级L2试题答案及解析(三)

【单选题】61 MDES Manufacturing是汽车行业的一家大型供应商,总部位于俄亥俄州克利夫兰,在美国芝加哥、巴西圣保罗、德国斯图加特、日本横滨和韩国首尔等地均有制造工厂。每个工厂均有各自的制造需求规划(MRPII)系统、生产调度系统以及定制开发的应用,从而驱动各工厂生产设…

Android源码笔记--电量(二)

这一节主要了解PowerManager,在Android系统中,电源管理是基于Linux电源管理的轻量级Android电源管理。电源管理主要是通过锁和定时器来切换系统的状态,使系统的功耗降至最低。Android系统的Framework层是接口层,为上面的应用层提供拿来即用的…